miércoles, 16 de agosto de 2017

Power Query: Texto en Filas

Sabemos la constante mejora a la que somete Microsoft a esta herramienta de Obtener y transformar (Power Query), y loq ue veremos hoy es una más de las funcionalidades disponibles de esta buena herramienta de BI:
Dividir columnas por filas.


El ejemplo que veremos a continuación se basa en una primera tabla donde alguien ha registrado por días diferentes tareas, controlando en una sola celda los empleados que han desarrollado dichas tareas (TblServicios).
Por otra parte tenemos un listado simple con los códigos de empleados y sus nombres (TblEmpleados).

Nuestro objetivo es combinar ambas tablas para identificar por cada día y servicio los nombres de los empleados...

Power Query: Texto en Filas



Nuestro primer paso sería cargar (solo como conexión!!) la Tabla con el listado de empleados. Lo haremos seleccionando la tabla y desde la ficha Datos > grupo Obtener y Transformar > botón Desde una Tabla.

La segunda Tabla la cargaremos a nuestras Consultas repitiendo el proceso anterior... pero en este caso realizaremos la acción requerida.
Dentro del Editor de consultas, con el campo llamado 'cod empleados' iremos a la ficha Transformar > grupo Columna de texto > botón Dividir columnas > desplegable por Separador

Power Query: Texto en Filas



Se abrirá la ventana diálogo de Dividir columna por delimitador, donde seleccionaremos el delimitador como Personalizado indicando en el campo editable del separador la coma.
Obviamente también podemos seleccionar directamente del desplegable la 'coma'.
Lo importante aparece al mostrar las Opciones avanzadas, donde marcaremos la opción Dividir por Filas

Power Query: Texto en Filas



Al Aceptar se transformará nuestra tabla cargada, mostrando separados por filas cada uno de los items del campo 'cod empleados'

Power Query: Texto en Filas



Terminamos esta parte del proceso Cerrando y cargando como conexión! esta segunda consulta.

El último paso será Combinar nuestras dos consultas.
Yo optaré por crear una tercera consulta resultante de la combinación de las dos anteriores. Para ello desde la hoja de cálculo en la ficha Datos > grupo Obtener y Transformar > Nueva consulta > Combinar consultas > botón Combinar.

Power Query: Texto en Filas


Esto nos mostrará el Editor de consultas y la ventana de Combinar, desde donde indicaremos la relación que necesitamos entre nuestras dos consultas previas ya cargadas, y en concreto:
campo 'cod empleados' de la TblServicios con el campo 'codEmpl' de la TblEmpleados
marcando el tipo de relación: Externa izquierda

Power Query: Texto en Filas



El penúltimo paso consiste en desplegar la Nueva columna incorporada tras la combinación, donde solo mostraremos el campo 'nombre' de la TblEmpleados:

Power Query: Texto en Filas



El último paso consiste en Cerrar y cargar la consulta, y esta vez sí, devolverla a una hoja de cálculo de nuestro libro, con el siguiente resultado:

Power Query: Texto en Filas

miércoles, 9 de agosto de 2017

Completar celdas hasta llegar a un acumulado

En el post de hoy conseguiremos, mediante fórmulas y también con programación, conseguir rellenar unas celdas con un valor parcial hasta conseguir que su suma acumulada llegue a una cantidad total, pero sin rebasarla.

La cuestión planteada por el usuario:
[...]Tengo en una columna el valor de la cifra acumulada de una sumatoria de valores iguales, conozco el valor de este número también y quiero que el resto de celdas se rellene automáticamente hasta llegar a sumar el valor acumulado y si la última cifra no puede ser igual al valor de la serie, entonces debe ajustarse hasta conseguir que la sumatoria sea igual al acumulado. Algo así: 9 (acumulado)  2 2 2 2 1 (último valor ajustado para que la suma sea igual a 9)
(Los valores de 9 el acumulado y el primer 2 de la serie ya se conocen)

Otro ejemplo
16.3    1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.5  1.3
(16.3 y 1.5 son valores que ya se conocen)[...]

Completar celdas hasta llegar a un acumulado



Comenzaremos exponiendo la solución con fórmulas, partiendo de dos datos:
1-celda D1: valor total a alcanzar (en el ejemplo 16,3)
2-celda E1: valor parcial para completar x celdas (en el ejemplo 1.5)

La fórmula buscada, añadida en B1 y luego arrastrada hacia abajo hasta B14 (en realidad tantas celdas como necesitemos hasta conseguir alguna celda vacía...):
=SI($E$1*FILA()<$D$1;$E$1;SI($E$1-($E$1*FILA()-$D$1)<0;"";$E$1-($E$1*FILA()-$D$1)))


Con este condicional comparamos el valor parcial acumulado con el total a alcanzar, fila a fila.
Mientras no superemos el valor Total retornaremos el dato de E1 (parcial), y solo cuando superemos dicha cantidad, y por diferencias:
$E$1-($E$1*FILA()-$D$1)
completaremos el valor. Tal como pedía el lector.

Un fórmula relativamente sencilla de aplicar.. con el único inconveniente que tendremos que ser nosotros los que controlemos hasta donde debemos arrastrarla...


Para evitar el inconveniente anterior propondré una solución alternativa con macros.
Así pues abrimos la ventana de código de nuestro módulo estándar y añadimos el siguiente código:

Sub Repeticion()
Dim Total As Range, Parcial As Range
'seleccionas las celdas con los valores Total y Parcial...
Set Total = Application.InputBox("Valor a alcanzar", "Excelforo", Type:=8)
Set Parcial = Application.InputBox("Selecciona dónde se encuentra el valor parcial", "Excelforo", Type:=8)

Dim valor As Double, Tot As Double
valor = Parcial.Value
Tot = Total.Value
Dim Acum As Double
Acum = Parcial.Value

'comenzamos el inicio del bucle para completar la secuencia de valores
Parcial.Select
Do
    Acum = Acum + valor 'acumulamos valores parciales
    ActiveCell.Offset(1, 0).Select  'bajamos una celda abajo...
    'controlamos el último valor de la secuencia
    If Acum >= Tot Then
        ActiveCell.Value = valor - (Acum - Tot)
    Else
        ActiveCell.Value = valor
    End If
Loop Until Acum >= Tot  'salimos del bucle al superar el Total

End Sub



Al ejecutar nuestra macro nos abrirá dos InputBox pidiendonos que seleccionemos las celdas con los valores 'Parcial' y 'Total', completando hacia abajo, desde la celda 'Parcial' con la serie requerida.

miércoles, 2 de agosto de 2017

Comprimir Imágenes en Excel

Veremos en la entrada de hoy un truco muy simple para reducir el tamaño de nuestros ficheros:
Comprimir Imágenes en Excel.

Esta acción reducirá el peso de nuestros libros de trabajo cuando contengan un número elevado de imágenes...


Veamos un ejemplo de un libro de trabajo con tres imágenes 'pesadas', que nos lleva a comprobar (antes de nuestra acción) que tiene un peso de:
2076 Kb

Comprimir Imágenes en Excel



Esto puede resultar problemático cuando el número de imágenes es alto, ya que conlleva un peso elevado del libro de trabajo, dificultando la apertura y recálculo del resto de funcionalidades de Excel.
Una posible solución es 'Comprimir imágenes'.

Para ello solo tendremos que:
1 Guardar como...
2 Acceder al botón de Herramientas (buscar posición según versión de Windows.
3 Opción Comprimir imágenes...
4 En la ventana diálogo que se abrirá seleccionar= Correo electrónico (96 ppi):minimiza el tamaño de los documentos para compartir

Comprimir Imágenes en Excel



Tras guardar el fichero comprobamos el efecto en el peso de nuestro libro, comprobando que ha bajado hasta los 83 Kb !!

Comprimir Imágenes en Excel



Obviamente el truco es rebajar los puntos por pulgada (ppi) al mínimo, rebajando la calidad de nuestras imágenes...