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




10 comentarios:

  1. 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.

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

    ResponderBorrar
  3. 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

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

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. 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

    ResponderBorrar
  7. Buenas noches Maestro, nuevamente estupefacto ante tanto conocimiento compartido, me a sido de mucha ayuda, a partir de esta nota apliqué el procedimiento y me fue muy bien, sin embargo me era necesario que uno de los "SI" anidara un "Y" con el fin de comparar dos columnas para asignar el resultado, ya con los SI creados ingresé por en editor avanzado y allí manteniendo la estructura anidé un "AND" y el campo adicional a evaluar, funcionó perfectamente, sin embargo me pregunto si hay una mejor forma de hacerlo sin ingresar al editor.

    De antemano muchas gracias por toda su tiempo y atención maestro.

    Respetuoso saludo.

    ResponderBorrar
  8. Hola Nelson,

    hasta donde dan mis conocimientos la única forma de hacerlo es en el editor.

    Gracias por los cálildos (y exagerados) conceptos.

    ResponderBorrar
  9. Hola,en el caso de querer que el condicional esté respecto al valor de una celda, y en caso de cumplirse se realiza el calculo sobre otra, ¿Como se haría la consulta?. Por ejemplo si la columna A, tiene valor "N", entonces, multiplica el valor de la columna B por 2,5.

    Gracias por el fantástico aporte realizado.

    ResponderBorrar
  10. Creando una columna personalizada tal como muestro en esta nota. Tenés que tener en cuenta que Power Query trabaja a nivel de columnas y filas (en Power Query no existe el concepto de celda tal como lo conocemos en Excel).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.