Cálculo de fecha de finalización de curso

Supongamos que programamos un curso de programación dividido en 10 encuentros/clases. Las clases se dictarán todos los lunes, miércoles y viernes. La pregunta es: ¿cuál es la fecha del último encuentro?

Podemos resolver este problema con facilidad usando la función DIA.LAB.INTL (Excel 2010 y versiones posteriores) pero tomando en cuenta un parámetro oscuramente explicado en la ayuda en línea de la función.

Empezamos por montar nuestro modelo

En las columnas D a H marcamos con el 0 (cero) los días en que se dictarán las clases y con 1 los días de la semana en los que no se dictarán las clases del curso. Ésto parece ir contra la intuición ya que, siendo usuarios consuetudinarios de Excel, asociamos el 0 con el valor FALSO y el 1 con el valor VERDADERO. Pero, como veremos más adelante, la función DIA.LAB.INTL requiere que usemos estos valores.

Como podemos ver, el curso de Programación comprende 10 encuentros (columna C) y se dicta los días Martes y Jueves.

Ahora agregaremos la columna con el cálculo de la fecha de cierre del curso

La fórmula en la celda J3 es =DIA.LAB.INTL(I3,C3-1,CONCAT(D3:H3)&"11")

Antes de seguir adelante aclaremos que la función CONCAT está disponible solamente para los usuarios de Excel 2016 (en Office 365). Usuarios de versiones aneriores deberán usar la función CONCATENAR.

Expliquemos la fórmula

El primer argumento es la fecha inicial; el segundo es el número de días que en nuestro caso es el número de encuentros. El tercer argumento es un valor textual compuesto de 1 y 0 que indican cuales son los días laborales de la semana y cuales no. Este valor textual debe tener siempre siete símbolos (uno por cada día siguiendo el orden de los días de la semana). 

El "truco" en esta fórmula es definir sólo los días en que se dicta el curso como días laborales. 

Opcionalmente podemos agregar una lista de feriados, el cuarto argumento, en caso de ser necesario.

También podemos cambiar el formato de las fechas para que muestren el día de la semana 

Otra mejora posible es usae Formato Numérico Personalizado para mostrar "si" en lugar de 0 y "no" en lugar de 1

con esta definición

o mejor aún con Formato Condicional

definido de esta manera

Cálculo de tiempo acumulado con Power Query

La principal dificultad del usuario Excel experimentado al comenzar a trabajar con Power Query no es tanto acostumbrarse al nuevo entorno, comprender los nuevos objetos y el nuevo idioma (M) o dominar las nuevas funciones. El principal obstáculo a vencer es dejar de “pensar Excel”.

Supongamos (en mi caso más que suposición era parte de un proyecto) que tenemos una serie de mediciones en una tabla; en una columna tenemos el momento de la medición (fecha y hora) y en otra tabla el valor medido.

La tarea a realizar es calcular el tiempo acumulado transcurrido desde la primera medición hasta la última, para cada medición. Con Excel la tarea es sencilla: en la primer celda de la columna C de la tabla ponemos es fórmula =A2-$A$2 y la copiamos a lo largo de la columna

Sólo tenemos que asegurarnos de “anclar” el sustraendo (=A2-$A$2). 

Si es tan fácil hacerlo con fórmulas de Excel, ¿por qué hacerlo con Power Query? Hay muchos motivos (supongamos por ejemplo una tabla con dos millones de filas), pero en mi caso se trataba de un proyecto totalmente desarrollado con Power Query.

En Power Query no existe el concepto de celda tal como lo conocemos en Excel, por lo que no podemos replicar el cálculo que he mostrado más arriba. A continuación mostraré los pasos.

Empezamos por cargar la tabla de datos a la ventana de Power Query 

Luego selecccionamos el primer valor del campo Registro en la ventana del Power Query y aplicamos Drill Down

con lo que obtenemos esta situación

Como pueden apreciar, el menú en la cinta a cambiado a "Text Tools"; en la ventanilla de la propiedades cambiamos el nombre (Name) a "varComienzo" (la partículo "var" la agrego como método de identificar que se trata de una variable).

Terminamos el proceso cargando la consulta como "sólo conexión" (Home-Close and Load-Connection Only). Nuestra hoja se ve ahora así

Ahora vamos a cargar la tabla y vamos a agregar una columna que llamaremos "Comienzo"; esta columna contendrá el valor de la variable (fecha y hora de la primera medición). Luego creamos la columna "Tiempo Acumulado" restando la columna Registro de la columna Comienzo. Finalmente eliminamos la columna Comienzo, que ya no necesitamos, y cargamos la tabla a una hoja de Excel. Todo el proceso lo muestro en este video.

Un detalle a tomar en cuenta es el tipo de variable que usamos para el tiempo acumulado: "Duration".

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