En el post anterior señalamos algunos de los escenarios en los cuales es preferible usar Power Query en lugar de BUSCARV.
Tratamos el primero de los tres escenarios: cuando el orden de las columnas en la tabla de búsqueda nos obliga a realizar al búsqueda de derecha a izquierda. Si bien podemos sobreponernos a este inconveniente usando una combinación de INDICE con COINCIDIR, la carga de fórmulas en el hoja puede volverla muy lenta. Power Query nos exime de estos problemas.
Otra situación es cuando debemos realizar la búsqueda en base a más de un criterio de búsqueda. Supongamos esta situación
En la tabla de la izquierda (tbl_Calificaciones) tenemos las notas de los alumnos; queremos transportar esta notas a la tabla de la derecha. El problema es que debemos usar dos criterios: nombre y apellido ya que tanto los nombres propios como los apellidos se repiten, pero BUSCARV funciona con un solo criterio de búsqueda.
Podemos superar el problema concatenando el nombre y el apellido en sendas columnas auxiliares en ambas tablas. Pero, como en el ejemplo anterior, ésto nos lleva a alterar nuestras fuentes de datos. También podríamos hacer una concatenación implícita (dentro de la fórmula) pero tendríamos que recurrir a fórmulas complicadas y "pesadas" desde el punto de vista del recálculo de la hoja. Nótese además, que el orden de las columnas es distinto en ambas tablas.
Veamos la solución con Power Query. Empezamos por crear una conexión a la tabla tbl_Calificaciones y las guardamos como "solo conexión"
Luego creamos una conexión a Tabla1 y en editor de Power Query elegimos la opción "Combinar consultas"
En la ventana del editor de Power Query usamos la opción "Combinar consultas" de esta manera
Primero hacemos un clic a la columna Apellido de Tabla1 e inmediatamente después a Apellido de tbl_Calificaciones. Luego manteniendo apretada la tecla Ctrl marcamos las columnas Nombre de ambas tablas (nótese el número 1 al lado derecho de Apellido y el número 2 al lado derecho de Nombre).
Como ya vimos en el caso anterior, Power Query agrega una columna a la Tabla1 con una doble flecha a la derecha. Esta doble flecha nos indica que la columna es de hecho una tabla que podemos expandir y elegir que columnas agregar
En este caso vamos a seleccionar solamente "Calificación"
Todo coacher que se precie nos conmina a "pensar fuera de la caja".Cuando de Excel se trata hay pensar fuera y dentro de la caja al mismo tiempo. O mejor dicho, en Excel pensar "fuera de la caja" es también ver qué hay "dentro de la caja", la caja de herramientas de Excel que se va enriqueciendo de versión en versión.
Pensar "fuera de la caja" quiere decir romper nuestros hábitos de pensamiento, recurrir siempre a lo que sabemos y no preguntarnos si habrá alguna otra forma de resolver el problema. Y aquí entra la cuestión de ver que hay dentro de la caja de herramientas de Excel. Power Query y PowerPivot son las herramientas más notables que Microsoft ha incorporado en los últimos años.
BUSCARV (VLOOKUP en inglés) es probablemente una de las funciones más usadas en Excel; en particular por usuarios de nivel básico o intermedio. Sin embargo, BUSCARV tiene varias limitaciones:
la búsqueda se realiza de izquierda a derecha, lo que nos en muchos casos a reorganizar el orden de las columnas en nuestra tabla de búsqueda o a utilizar una combinación de INDICE y COINCIDIR;
la búsqueda debe realizar en base a un único parámetro o utilizar alguna de las técnicas que muestro en el post "BUSCARV con dos parámetros" (si la dan un vistazo a la complejidad de las fórmulas, traten de imaginar una búsqueda en base a tres o más parámetros);
si hay valores repetidos en la columna de búsqueda, BUSCARV siempre dará el primer valor encontrado ignorando los restantes.
Todas estas limitaciones pueden ser superadas con facilidad usando Power Query en lugar de BUSCARV.
Una pequeña introducción al uso de Power Query para aquellos lectores que aún no estén familiarizados con esta herramienta.(quienes esté interesados en ampliar conocimientos recomiendo este curso).
Para usar Power Query, incorporado orgánicamente a Excel desde la versión 2013 o como complemento en Excel 2010, creamos una conexión a la fuente de datos. Estos datos pueden encontrarse en el mismo cuaderno como tabla o en cualquier fuente remota (cuaderno Excel, Access, página WEB, archivo de texto, etc.). Una vez creada la conexión podemos volcar los datos en una hoja de Excel o dejarlos como "solo conexión". Esta última opción nos permite superar la limitación de 1.48 millón filas por hoja.
Vamos ahora a ver el primer caso que menciono más arriba usando tablas extraídas de la base de datos Northwind. Los dos casos restantes los veremos en los próximos posts.
Aclaración: en un caso real sólo crearíamos conexiones a la base de datos sin necesidad de cargar los datos en hojas de Excel. A los efectos del ejemplo, vamos a suponer que los datos están en tablas en hojas de Excel.
Caso 1 - Búsqueda de derecha a izquierda.
Supongamos esta tabla de productos. El identificador de categoría (Category ID) está en la columna C.
En esta tabla de categorías el identficador está en la columna B (la segunda de la tabla) y la descripción en la columna A.
Queremos agregar la descripción de la categoría a la tabla de productos. Para hacerlo con BUSCARV tendríamos que reordenar la tabla de categorías o usar una combinación de INDICE y COINCIDIR.
Con Power Query hacemos lo siguiente:
a. Creamos una conexión a la tabla de productos
En la ventana del editor de Power Query seleccionamos la opción "Guardar como..." y "Sólo conexión"
b. Hacemos lo mismo con la tabla de categorías. En el panel de conexiones veremos ambas conexiones que acabamos de crear
c. Usamos la opción Datos-Obtener Datos-Combinar consultas
En el cuadro que se abre seleccionamos las consultas a combinar (Products y Categories), señalamos las columnas coincidentes de ambas tablas (CategoryID) y seleccionamos el tipo de combinación a "Extrema izquierda" (sin connotaciones políticas... esto quiere decir, traer todos los registros de la primer tablas y sólo los coincidentes de la segunda).
Apretamos "Aceptar" y veremos que se crea una nueva consulta con la tabla Productos a los que se le agregado una nueva columna, "Categories". En realidad esta columna contiene toda la tabla Categories y la doble flecha en el encabezamiento nos permite expandirla y elegir la o las columnas de Categories que queremos traer
Al expandir la columna, como dijimos, vemos todas la columnas de la tabla. En nuestro caso sólo elegiremos "Category Name"
Apretamos "Aceptar" y aquí tenemos el resultado
Todo los que nos queda por hacer es descargar esta nueva consulta a una hoja del cuaderno
Para quien no esté familiarizado con Power Query este procedimiento puede parecer complicado. ¿Qué más simple que pegar una fórmula con BUSCARV?. Sin embargo, el uso de Power Query tiene varias ventajas:
no hay necesidad de hacer cambios en la tabla de búsqueda;
en nuestro ejemplo hay sólo 77 filas. En casos con miles o decenas de miles de filas, el uso de BUSCARV hace que el cuaderno responda lentamente con cada cambio que conlleve recalcularlo. Por ejemplo, en una hoja con 100 mil filas (caso real de un colega), cada aplicación de Autofiltro puede llevar varios minutos.
En el próximo post veremos los dos restantes casos. usar BUSCARV con más de un criterio de búsqueda y BUSCARV con valores repetidos en la tabla de búsqueda.
Hace varios días que venía dándole vueltas a la idea de mostrar cómo crear una tabla de amortización de préstamos, como el modelo que publiqué en el pasado, pero usando Power Query en lugar de las funciones de Excel.
A diferencia de los ejemplos anteriores de Power Query que he mostrado en este blog, en este caso no partimos de una fuente de datos, de la hoja o remota, que cargamos a una consulta en forma de tabla. De hecho, tenemos que crear la tabla de amortización "de la nada". Es decir, una vez definidos los parámetros iniciales (monto del préstamo, tasa de interés, número de cuotas) tenemos que crear una tabla en Power Query que luego cargaremos a una hoja de Excel.
Para crear la tabla "de la nada" tendremos que usar funciones del lenguaje M (el lenguaje del Power Query). En este post no entraré en los detalles técnicos de M (objetos, funciones, etc.) y me limitaré a mostrar cómo fue construido el modelo.
Investigando las distintas funciones de Power Query llegué rápidamente a la conclusión que la función indicada era List.Generate. Lamentablemente la ayuda en la hoja de funciones de Microsoft es oscura (por lo menos para el usuario promedio). En mi ayuda vino este post publicado por Andrew Todd en PowerPivot(Pro).
Y ahora, manos a la obra! Empezamos por definir los tres parámetros iniciales: monto del préstamo (Capital), tasa de interés anual (Interes) y el número de pagos o cuotas (Pagos). Los ingresamos en celdas de una hoja y creamos nombres definidos que se refieran a esas celdas. La forma más rapida de hacerlo es seleccionando el rango y usando Fórmulas-Nombres definidos-Crear desde la selección:
Ahora creamos una consulta para cada parámetro. Como hemos creado nombres definido Power Query no carga el rango A1:B3 a la consulta, sino sólo la celda del nombre definido
Power Query cambia el tipo de datos s Entero automáticamente. Con un clic del botón derecho abrimos el menú contextual y usamos "Rastrear desagrupando datos" (Drill down)
Vamos a Inicio, cerramos y cargamos como "sólo conexión"
Hacemos lo mismo con los otros dos parámetros
Para crear la tabla de amortización empezamos por crear una consulta en blanco (Desde otras fuentes-Consulta en blanco). En la venta del editor pegamos este código, que paso a explicar
let
P=Capital,
i=Interes,
n=Pagos,
Mes=Table.ExpandRecordColumn(
Table.FromList(
List.Generate(
()=>[Counter=1],
each [Counter]<=n,
each [Counter=[Counter]+1]
),
Splitter.SplitByNothing(), {"Balance Inicial"},
null, ExtraValues.Error),"Balance Inicial", {"Counter"}, {"Mes"}),
BalanceInicial = Table.AddColumn(Mes, "Balance Inicial",
each P*(Number.Power(1+(i/12),n)-Number.Power(1+(i/12),[Mes]-1))/(Number.Power(1+(i/12),n)-1),
type number),
PagoMensual = Table.AddColumn(BalanceInicial, "Pago Mensual",
each P*((i/12)*Number.Power(1+(i/12),n))/(Number.Power(1+(i/12),n)-1), type number),
InteresMensual=Table.AddColumn(PagoMensual , "Interes Mensual",
each (i/12)*[Balance Inicial], type number),
CapitalMensual = Table.AddColumn(InteresMensual, "Capital Mensual",
each [Pago Mensual]-[Interes Mensual], type number),
BalanceFinal = Table.AddColumn(CapitalMensual, "Balance Final",
each [Balance Inicial]-[Capital Mensual], type number)
in
BalanceFinal
Las primeras tres líneas crean las variables que reciben el valor de las consultas de los parámetros. Luego creamos la columna "Mes" (el número de cuota) para lo cual anidamos tres funciones de M; de adentro hacia afuera:
List.Genetate: crea una lista (columna) con los números de mes; empieza con [Counter] = 1 y va agregando 1 hasta que el valor sea igual a n (Pagos);
Table.FromList: convierta la lista que acabamos de generar en Tabla de manera que podamos usarla en Power Query;
Table.ExpandRecordColumn: expandimos la tabla
A esta altura de los acontecimientos nuestra consulta ser verá así
En Power Query no disponemos de las funciones para calcular el pago fijo, el interés y la parte del capital en cada pago como tenemos en Excel (PAGO, PAGO.INT, PAGOPRIN), así que tendremos que hacer los cálculos con operadores. Por ejemplo, para calcular el balance inicial de cada período usamos
Para crear la columna podemos usar el menú en la interfaz del usuario (Agregar Columna-Columna Personalizada) o pegar el código en el editor. Lo mismo para las demás columnas. Al final de proceso tenemos la tabla
La descargamos en la hoja donde pusimos los parámetros y nuestro modelo está listo
Este modelo es absolutamente dinámico. Todo lo que hay que hacer es cambiar los parámetros y apretar Datos-Actualizar todo como puede verse en este video
En la primera nota vimos como calcular horas trabajadas por un empleado usando el Power Query. El objetivo del post era mostrar que lo que ya habíamos mostrado como hacerlo con Excel (fórmulas) podía hacerse también con Power Query.
En la segunda nota vimos las grandes ventajas que nos ofrece el uso de Power Query al hacer los cálculos combinando los informes de varios empleados, en forma automática y sin necesidad de recrear las fórmulas con cada nuevo informe.
En ambas notas mostramos el caso sencillo donde no hay que diferenciar las horas por turnos. En este post vamos a recrear el ejemplo del segundo pero agregando la necesidad de calcular cuántas de las horas trabajadas fueron en el turno diurno y cuántas en el turno nocturno.
De hecho vamos a estar recreando en Power Query el modelo que mostré en este post. La lógica aplicada es la misma pero "traducida al idioma" Power Query. Las ventajas, como ya expliqué en las notas anteriores, son:
la robustez del modelo: no hay fórmulas en celdas que puedan ser alteradas desapercibidamente;
la facilidad de combinar un gran número de fuentes de datos y crear informes;
actualización de los datos e informes con sólo apretar un botón.
Para este ejemplo vamos a usar los mismos archivos del segundo post (los informes de los empleados que guardamos en la carpeta Informes).
Al crear este nuevo modelo el primer paso es definir las horas de comienzo y finalización del turno noche. En una hoja de Excel ponemos esta sencilla tabla
Creamos una consulta a la tabla como "sólo conexión"
Podemos ver PASOS APLICADOS que Power Query ha cambiado el tipo de dato de las columnas Comienza y Termina a número decimal (identificado por 1.2 a la izquierda del encabezado de la columna). Ésto sirve a nuestros propósitos así que lo aceptamos.
A partir de esta consulta creamos una nueva con la opción Referencia
Abrimos la nueva conulta y cambiamos el nombre a "noche_comienzo".Con un clic del botón derecho del mouse y apuntando al valor en la fila Comienza y activamos la opción "Rastrear desagrupando datos" (en inglés sencillamente, drill down)
con este resultado
Ahora guardamos esta consulta también como "sólo conexión". Repetimos el proceso para crear una consulta para el valor de "noche_final", esta vez seleccionando el valor de la columna Termina.
Nuestra hoja se ve ahora así
Ahora vamos a aplicar los pasos que aplicamos en la segunda nota para crear una consulta que integre todos los informes (archivos xlsx) guardados en la carpeta Informes. Al final de proceso tenemos esta consulta en la ventana del editor de PQ
Aquí vamos a hacer un pequeño cambio a lo que mostramos en la segunda nota. Vamos a extraer de Entrada y Salida le fecha y dejar sólo las horas. Para hacerlo seleccionamos ambas columnas y aplicamos en Transformar-Columna de Fecha y Hora-Solo hora
ahora tendremos
La próxima transformación que hacemos es transformar las horas de Entrada y Salida al tipo de datos número decimal.
Ahora vamos a aplicar la misma lógica que usamos en la solución con Excel tradicional
# - calculamos las horas trabajadas con una columna personalizada con esta fórmula
=if [Salida]<[Entrada] then
1+[Salida]-[Entrada]
else
[Salida]-[Entrada]
# agregamos dos columnas auxiliares "noche-comienzo"
y "noche-final" de la misma manera . Cambiamos el tipo de datos a número decimal.
# creamos una columna personalizada que llamaremos "Mediana - Entrada". Ésta es una columna auxiliar (con el mismo objetivo de la correspondiente columna en el modelo en Excel ya mencionado). Seleccionamos la columna Entrada y las dos columnas que acabamos de crear y creamos una nueva columna con Agregar Columnas-Estadísticas-Mediana
La columna creada recibe, por defecto, el nombre "Median" que vamos a reemplazar con "Mediana-Entrada". Ésto podemos hacerlo agregando un paso o, como muestro en la imagen abajo, reemplazando el valor en la ventana de las fórmulas
Hacemos lo mismo con Salida-noche_comienzo y noche_final para crear la columna "Mediana-Salida".
# creamos una nueva columna auxiliar, "aux1" con esta fórmula condicional
# ahora calculamos las horas trabajadas en el turno noche con esta fórmula:
# todo lo que nos queda por hacer es calcular las horas diurnas que, lógicamente, son la diferencia entre las horas trabajadas y las horas nocturnas
# Finalmente eliminamos todas las columnas auxiliares que creamos y dejamos solamente aquellas que queremos que aparezcan en nuestro informe
Transformamos las columnas de horas al tipo de dato "Hora" y cargamos la consulta a una hoja del cuaderno
Para crear un informe que resuma los datos por empleados empezamos por crear una nueva consulta que cuyo origen es una referencia a la que acabamos de crear. Cambiamos el tipo de datos de las columnas de horas a número decimal y aplicamos "Agrupar por" de esta manera
Cargamos la consulta a una hoja del cuaderno y cambiamos el formato de las columnas de las horas al tipo personalizado [hh]:mm en el menú de formato de celdas de Excel (ver la explicación en la nota anterior).
Nuestro resumen se verá así
En la nota anterior mostramos como calcular las horas trabajadas por un empleado a partir de un informe en una hoja de Excel usando Power Query. En un caso sencillo como el del ejemplo la ventaja de usar Power Query en lugar de Excel tradicional (fórmulas) pasará desapercibida para la mayoría de los usuarios que aún no hayan adoptado las nuevas herramientas de Excel (Power Query, PowerPivot).
En este post mostraré un escenario más complejo (más real) donde nuestra misión es consolidar informes mensuales de varios empleados en un único informe. Y ésto debe suceder automáticamente y con sólo apretar un botón.
En este ejemplo cada empleado envía un informe semanal de las horas trabajadas. En este caso tenemos tres empleados pero, por supuesto, podrían ser cinco, veinte o cien.
La misión es:
calcular las horas trabajadas de cada empleado;
descontar media hora de descanso/comida cada día excepto que el empleado haya trabajado menos de siete horas;
crear un informe que totalice las horas netas trabajadas por cada empleado en la semana.
Empezamos por crear una carpeta para guardar los informes semanales de los empleados que recibimos en hojas de Excel, como ésta (al final del post hay un enlace al video que muestra todo el proceso)
Abrimos un cuaderno Excel y creamos una consulta que consolide todos los archivos (informes) guardados en la carpeta de los informes con Obtener Datos-Desde un archivo-Desde una Carpeta
Vamos a resistir la tentación de apretar el botón Combinar y en su lugar apretamos Editar
Eliminamos todas las columnas con excepción de Content y Name. Name contiene el nombre del archivo que es a su vez el nombre del empleado pero con la terminación .xlsx. Para quitarla y que nos nos quede sólo el nombre del empleado usamos Reemplazar
con este resultado
Ahora expandimos la columna Content (apretando la doble flecha a la derecha del encabezado) para combinar los archivos. En la ventana del editor veremos que la hoja contiene dos objetos
Elegimos "Tabla1" que es el rango que contiene la tabla de fechas y horas (más explicaciones en el video al final de este post donde muestro todo el proceso). Después de apretar Aceptar veremos esta tabla de datos combinados
¡La columna con los nombres de los empleados ha desaparecido! Para recuperarla hacemos un clic en el "engranaje" a la derecha del paso aplicado "Otras columnas quitadas1" y marcamos "Name"
y luego volvemos a seleccionar el último paso con un clic
Ahora nuestros datos están completos y podemos empezar a transformarlos. Empezamos por cambiar el nombre de la columna Name a Empleado. Como en el ejemplo del post anterior cambiamos el tipo de datos de Entrada y Salida a número decimal. Ésto nos permite realizar operaciones aritméticas sencillas con los datos evitando usar funciones DateTime y Time de Power Query con las que no estamos familiarizados.
y luego creamos la columna personalizada Horas Trabajadas
La columna personalizada aparece con el formato "Cualquiera" por lo que tenemos que transformarlo a número decimal. La forma más fácil es con el menú Transformar-Tipo de Datos-número decimal.
Ahora tenemos que descontar media hora de cada fila donde el trabajador trabajo siete horas o más. Nuevamente agregamos una columna calculada con una fórmula condicional
Ahora eliminamos las columnas Entrada y Salida y cambiamos el tipo de datos de Horas Trabajadas y Horas Trabajadas Neto a Hora
Cargamos esta consulta a una hoja de Excel y tendremos un reporte detallado. Seguidamente vamos a crear un reporte agrupado por empleado.
Empezamos por crear una consulta cuyo origen es la consulta anterior usando Referencia en la ventana de las consultas
En la nueva consulta transformamos las columnas de la horas al tipo de datos número decimal y luego agrupamos por Empleado creando dos columnas: Horas Trabajadas - bruto y Horas Trabajadas - neto
con este resultado
Para que nuestro informe sea legible queremos que los datos aparezcan como horas, no como números decimales (en el video de la nota agrego una explicación sobre el tipo de datos Duración de Power Query).
Para expresar los datos como horas acumuladas usamos el formato personalizado [hh:mm] en la tabla de la hoja
con el resultado esperado
Este video muestra todo el proceso
En el próximo post mostraré como hacer el cálculo tomando en dos turnos (diurno y nocturno).
