lunes, marzo 20, 2017

SI anidado con Power Query

Uno de los temas más frecuentes en las consultas que recibo es el de la función SI anidada. Es decir, todo tipo de ejercicios sobre como calcular un resultado bajo una serie de condiciones (por ejemplo, compras de hasta los 1000 pesos reciben un descuento del 5%; si superan los 5000, un descuento del 10%; compras de más de 10000, 7%, etc.). Estos ejercicios son muy populares en todo tipo de cursos Excel, inclusive en el sector académico, por algún motivo que escapa a mi modesto entendimiento. En lugar de complicarnos la vida armando una fórmula complicada , recordemos que Excel acepta hasta 64 niveles de SI en una fórmula, podemos usar una simple tabla y la función BUSCARV, tema que ya he tratado en esta prehistórica nota.


Siguiendo con el tenor de mis últimos posts, voy a mostrar cómo utilizar Power Query para solucionar cálculos con SI anidado con facilidad, sin dolores de cabeza y evitando, además, cargar nuestras hojas con muchas funciones SI.

Para nuestro ejemplo vamos usar esta tabla de ventas del año 2016 sobre la cual nos piden calcular las comisiones a pagar a los agentes. Por facturas superiores a 5000 les corresponde una comisión del 7.5%; por facturas que superen los 3000, 5%; facturas de más de 1500, 2.5% y las restantes sólo el 1%.


El primer problema con esta tabla es que las facturas aparecen en varias filas, una por cada producto. Por lo tanto tendremos que agrupar las ventas por factura.  En la época pre-Power Query lo hubiéramos hecho con una tabla dinámica, pero el Power Query nos ofrece otra alternativa: "Agrupar por" (Group by).

Empezamos por crear una conexión a la tabla (supongamos que se encuentra en una base de datos, no en una hoja de Excel) abriéndola en el editor de Power Query

De todas la columnas sólo necesitamos Agente, Nro. de Factura y Venta, pero no hace falta eliminar las restantes; Group by hará el trabajo por nosotros.

Abrimos el menú de Group By y hacemos las siguientes definiciones

Apretamos "Ok" y Power Query realiza la agrupación

Como puede apreciarse, nuestra tabla tiene ahora una fila por factura y agente con el total para cada factura.

Ahora vamos a calcular las comisiones agregando una columna condicional. Esta es una mejora agregada en una de las últimas actualizaciones del Power Query. Cuando activamos el menú de Add Column - Conditional Column, veeremos un formulario que nos permite crear todas las condiciones con facilidad


Elegimos la columna, el operador, el valor de la condición y el resultado para la primer condición; luego apretamos el botón "Add rule" para agregar las siguientes y finalmente ponemos el valor en la casilla "Otherwise" para la última condición (el resultado si todas las condiciones anteriores no se cumplen). Este es el resultado


Si observamos la nueva columna (mientras tanto lleva el nombre de "Custom"), veremos que los números están alineados a la izquierda. Esto nos indica que debemos transformarlos en números. Podemos hacerlo pulsando el "ABC123" en el ángulo izquierdo del encabezado


Ahora que los hemos convertido en números, podemos agregar una columna calculada con la comisión por factura


con este resultado

A esta altura de los acontecimientos podemos volcar los datos a una hoja de Excel, pero aquí vamos a hacer algo distinto. Vamos a guardar la tabla como conexión


Finalmente vamos a usar una tabla dinámica sobre esta conexión para crear nuestro reporte de comisiones. Empezamos con el menú Insertar-Tabla Dinámica con la opción "Utilice un fuente de datos externa"


Al apretar "Elegir conexión", la que acabamos de crear aparecerá en la parte superior del cuadro


Apretamos aceptar y veremos el familar cuadro de las tablas dinámicas

Todo lo que nos queda por hacer es arrastrar los campos requeridos a las áreas de filas, columnas y valores, según el reporte que queramos crear; por ejemplo




miércoles, marzo 15, 2017

Nuevo catálogo de imágenes con Excel

Han pasado casi siete años desde que publiqué el último post sobre el tema de catálogos de imágenes con Excel. Este tema se encuentra entre los más leídos por mis lectores (aunque debo señalar que no recomiendo crear este tipo de aplicaciones con Excel).

En el post mencionado uso Vba (macros) para incrustar las imágenes guardadas en una carpeta en la hoja. La técnica consiste en guardar las direcciones de las imágenes en una tabla de Excel. En otra hoja el usuario introduce un texto que hace referencia a la dirección de la imagen y de esta manera, con el código, introducimos la imagen en la hoja de Excel. Esto tiene muchas ventajas pero presenta la dificultad de tener que actualizar la lista de referencia-dirección de la imagen.

Usando Power Query podemos crear una consulta que mantenga actualizada la lista de direcciones, en lugar de tener que usar todo tipo de códigos complicados.
Supongamos que queremos construir un modelo donde al ingresar el usuario el nombre de un país en la celda contigua aparece la bandera del país elegido. En nuestro ejemplo tenemos todos los archivos de las imágenes de las banderas en la carpeta “Banderas”


El proceso lo muestro en este video



Una vez creada la conexión, solo tenemos que apretar el botón Actualizar para mantener la tabla de referencias actualizada


El próximo paso es crear el nombre definido "rngPaisDireccion" que se refiere a la tabla completa, sin los encabezados



En otra hoja creamos esta tabla, con los encabezados y una fila vacía


Creamos un nombre definido que se refiere a todas las celdas de la columna Pais ("PicList"), que luego usaremos en el código


Como ven, ya hemos agregado dos botones para activar las macros. Una para insertar las banderas correspondientes a los nombres de los países que ingresemos en la columna A y otro para remover los países que ingresamos y las imágenes de sus banderas.

El código para insertar las banderas es

 Sub insert_pic()  
   Dim strFileName As String  
   Dim iTop As Integer  
   Dim rngCellPic As Range  
   'comprobar que se introdujeron paises  
   If WorksheetFunction.CountA(Range("PicList")) < 1 Then  
     MsgBox "No se anotaron paises", vbCritical  
     Exit Sub  
   End If  
   Application.ScreenUpdating = False  
   For Each rngCellPic In Range("PicList")  
   'defiinir alto de fila a 60 y centrar  
   With rngCellPic  
     .RowHeight = 60  
     .VerticalAlignment = xlCenter  
   End With  
   'introducir la bandera  
   strFileName = WorksheetFunction.VLookup(rngCellPic, Range("rngPaisDireccion"), 2, 0)  
   ActiveSheet.Shapes.AddPicture Filename:=strFileName, _  
       linktofile:=msoFalse, _  
       savewithdocument:=msoCTrue, _  
       Left:=rngCellPic.Offset(0, 1).Left + 15, _  
       Top:=rngCellPic.Offset(0, 1).Top + 5, _  
       Width:=50, Height:=50  
   Next rngCellPic  
   Application.ScreenUpdating = True  
 End Sub  

El código para limpiar todas las filas de la tabla es el siguiente


 Sub clean_all()  
   Dim rngCell As Range  
   Dim shpImage As Shape  
   Application.ScreenUpdating = False  
   'delete pictures  
   For Each shpImage In shList.Shapes  
     If shpImage.AlternativeText <> "NoDelete" Then shpImage.Delete  
   Next shpImage  
   For Each rngCell In Range("PicLIst")  
     rngCell.RowHeight = 14.25  
   Next rngCell  
   On Error Resume Next  
   Range("PicList").EntireRow.Delete  
   On Error GoTo 0  
   Application.ScreenUpdating = True  
 End Sub  

El modelo en funcionamiento


jueves, marzo 02, 2017

Generar reportes con Power Query

Power Query, o Datos-Obtener y Transformar para los usuarios de Excel 2016, es más que una herramienta para obtener, transformar y cargar datos. También podemos usarlo como herramienta para generar informes. La clave reside en la herramienta “Agrupar por” del editor de Power Query.

Veamos un ejemplo, basado en la base de datos Northwind (como de costumbre). Nos piden un informe que el promedio de cada operación de venta del año 2015, por agente. Nuestra base de datos contiene las ventas por cliente, agente, orden de venta y fecha.

Creamos una consulta en el editor de Power Query que, después de hacer las operaciones "de limpieza" necesarias, se ve así



Creamos el reporte con los siguientes pasos:

Empezamos por abrir el menú “Agrupar por”


En el formulario que se abre definimos


  • en "Agrupar por" elegimos la columna "Agente";
  • en "Nuevo nombre de columna" cambiamos el nombre de la  casilla (al abrir el formulario sólo se ve una casilla) a "Operaciones" y en la casilla "Operación" elegimos "Contar filas distintas" (traducción poco exitosa, en mi opinión, de Distinct Count);
  • usamos el símbolo + (a la derecha de "Columna") para agregar otro campo y en el nombre ponemos "Ventas", en la operación "Suma" y elegimos la columna Ventas para la operación.
De esta manera creamos una tabla con tres columnas: Agente, Operaciones y Ventas


Ahora podemos crear una columna calculada para el promedio de venta por agente



Este video muestra el proyecto




Todo los que no queda por hacer ahora es cargar la consulta a una hoja de Excel, dar un buen formato a los números y..listo.