SI anidado con Power Query

lunes, marzo 20, 2017

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




6 comments:

José Manuel Agundis 20 marzo, 2017 15:29  

Buenos días maestro, muy bueno su ejércicio, las nuevas herramientas de Excel nos han venido a hacer nuestros cálculos más fáciles e intuitivos no sin la ayuda de usted. Saludos y gracias por su colaboración.

Ricardo Reynoso 20 marzo, 2017 18:47  

Gracias Jorge por compartir con nosotros ese conocimiento. ¡¡¡Impresionante!!!

Anónimo,  21 junio, 2017 17:59  

Hola Jorge,

Se aprende mucho contigo, solo un "pero", me gusta practicar haciendo yo ejercicios y me gustaría tener las tablas (bases de datos) que pones como ejemplo..las puedes colgar o se pueden bajar de algún sitio?

gracias de antemano

Jorge Dunkelman 21 junio, 2017 19:40  

La tabla que uso en este ejemplo es la consulta (query) Invoices de la base de datos Northwind que puedes descargar aqui.

Anónimo,  21 junio, 2017 23:17  

Buenas noches (soy el que te pidió la base de datos): ya la conseguí aunque modifiqué la columna de fecha (que venía de 2006); hice este ejercicio y me plantea las siguientes dudas:
A) ¿En la columna condicional en la especificaciones es obligatario cubrir la casilla "otherwise", es un poco chocante esa casilla ya que todas las condiciones las puedes rellenar arriba?
B) Volqué la tabla del editor en la hoja de excel (cerrar y cargar) y los porcentajes de la comisión (que los tenía en el módulo Editar en %) ahora me aparecen en tanto por uno
C) No entiendo mucho eso que haces al final de guardar la conexión únicamente, ya que la misma solo se guarda en el libro que estás utilizando; probé abriendo otro libro de excel y en datos--conexiones, no me salía la creada.
D) Conoces algún curso/manual de Power Query

Un Saludo y que no me gusta la palabra crack, pero bueno que lo eres en esto de las hojas de cálculo

Jorge Dunkelman 22 junio, 2017 10:15  

Hola,

A - No necesariamente, si todas las condiciones fueron cubiertas.
B - Puedes dar formato de % a la columna en la tabla.
C - Las conexiones, como las tablas, pertenecen al cuaderno donde están guardadas.
D - Hay mucho material en la base de conocmientos de Microsoft, poe ejemplo esta página. Tambi[en puedes ver este webinar o tomar el curso que publicito en este bloog (ve el banner en la columna a la derecha).

pd: gracias por los cumplidos

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP