En el siguiente ejemplo, queremos calcular el total para cada centro comercial, pero de los últimos tres días de cada año.
Lo vamos a hacer en Excel, power Query.
Primero lo haremos en Excel.
Voy a ordenar las fechas de más reciente a más antiguo.
Ahora, ordeno la columna centro de la A a la Z.
Vemos que las tres últimas ventas para el centro comercial Nervión, corresponden a los días 18, 24, y, 25 de noviembre del año 2021.
Pues debemos de rescatar sus totales.
Vamos a crear una lista única de centros comerciales, para ello, seleccionamos la columna de centro, y, pulsamos CTRL mas C para copiar, nos colocamos en la celda E1, y, pulsamos CTRL más V para pegar.
Vamos a la pestaña de datos, dentro del grupo herramientas de datos, hacemos clic en quitar duplicados.
Se abre la ventana de quitar duplicados, simplemente, aceptamos.
Como queremos rescatar las ultimas fechas de venta para cada centro comercial, voy a rescatar la ultima tercera venta, para ello, pero antes, voy a mostrar un calculo de como encontrar las fechas para cada centro comercial, el primer centro comercial es centro comercial Nervión, si divido la cantidad de fechas, entre los centros que sean iguales a centro comercial Nervión, me devolverá las fechas que pertenecen a centro comercial Nervión.
=Ventas[Fecha]/(Ventas[Centro]=E2)
Obtengo una matriz desbordada, con las fechas que pertenecen al centro comercial Nervión.
Pero, donde no hay coincidencia, nos va a devolver un error, para obtener el tercer valor mayor, usare la función K.ESIMO.MAYOR, para como he dicho antes, vamos a tener errores, por lo que voy a usar la función AGREGAR, donde podemos elegir ignorar errores.
Escribo la función AGREGAR, y, abro un paréntesis.
En la ventana que se abre, seleccionamos K.ESIMO.MAYOR.
Punto y coma, en la siguiente ventana, seleccionamos omitir valores de error.
=AGREGAR(14;7
Como argumento matriz, ponemos la división que hicimos antes.
AGREGAR(14;7;Ventas[Fecha]/(Ventas[Centro]=E2)
Y, como argumento K, ponemos el numero 3, para que nos devuelva la tercera venta mayor.
Cerramos paréntesis, y, aceptamos.
AGREGAR(14;7;Ventas[Fecha]/(Ventas[Centro]=E2);3)
Ahora, voy a usar la función SUMAR.SI.CONJUNTO para calcular el total para las tres ultimas ventas del centro comercial Nervión.
Como argumento rango de suma, seleccionamos la columna de total.
=SUMAR.SI.CONJUNTO(Ventas[Total]
Como argumento rango de criterios1, seleccionamos la columna de fecha.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha]
Como argumento criterio1, entre comillas dobles, ponemos mayor o igual, y, lo concatenamos con la función AGREGAR.
Como con la función AGREGAR, hemos obtenido la tercera fecha mayor, quiere decir que mayor o igual a ella, solo habrá tres.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&AGREGAR(14;7;Ventas[Fecha]/(Ventas[Centro]=E2);3)
Como argumento rango de criterios2, seleccionamos la columna de centro.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&AGREGAR(14;7;Ventas[Fecha]/(Ventas[Centro]=E2);3);Ventas[Centro]
Y, como criterio2, seleccionamos la celda E2.
Cerramos paréntesis, y, aceptamos.
=SUMAR.SI.CONJUNTO(Ventas[Total];Ventas[Fecha];">="&AGREGAR(14;7;Ventas[Fecha]/(Ventas[Centro]=E2);3);Ventas[Centro];E2)
Tenemos el total de las ultimas tres ventas para el centro comercial Nervión.
Arrastramos y tenemos la suma de las ultimas tres ventas para cada centro comercial.
Ahora, vamos a hacerlo en Power Query, para ello, con una celda dentro del modelo, vamos a la pestaña de datos, dentro del grupo obtener y transformar, hacemos clic en de una tabla o rango.
Tenemos que agrupar por centro, para ello, teniendo la columna seleccionada, hacemos clic con botón alternativo de ratón, y, seleccionamos agrupar por.
Se abre la ventana de agrupar por, en la ventana nuevo nombre de columna, ponemos suma de total, en la ventana operación, seleccionamos suma, y, en la ventana columna, seleccionamos total.
Aceptamos.
Tenemos el total por centro.
Si nos fijamos en la barra de fórmulas, vemos Table.Group, que necesita una tabla, y, como tabla esta el último paso, después tenemos el nombre de la columna, y, después la acción a realizar.
= Table.Group(#"Filas ordenadas", {"Centro"}, {{"Suma de total", each List.Sum([Total]), type nullable number}})
En la acción a realizar, vemos List.Sum([Total]), pues, delante de la columna de total, voy a usar la función List.FirstN, ¿Por qué uso esta función, si estamos buscando las tres ultimas ventas?, pues porque tenemos las fechas ordenadas de manera descendente.
= Table.Group(#"Filas ordenadas", {"Centro"}, {{"Recuento", each List.Sum(List.FirstN([Total]
Coma, y, como elementos a devolver ponemos tres, y, cerramos paréntesis.
= Table.Group(#"Filas ordenadas", {"Centro"}, {{"Recuento", each List.Sum(List.FirstN([Total],3)), type nullable number}})
Aceptamos, y, vemos que tenemos el total para centro de las últimas tres ventas.
Cerramos y cargamos como una conexión.
Vamos a la ventana de consultas y conexiones, hacemos clic en la consulta de totales con botón alternativo de ratón, y, seleccionamos cargar en.
En la ventana que se abre seleccionamos tabla, y, hoja de cálculo existente, la colocamos al lado de los cálculos que hicimos anteriormente.
Podemos ver que hemos obtenido los mismos resultados.
コメント