Hoy explicaré un buen truco para incorporar una fila de totales a un rango desbordado, ejemplo versionado del gran Jon Acampora.
Los usuarios que disponemos de estas funciones desbordadas sabemos del problema de incluir al final del rango desbordado devuelto un subtotal, ya que a priori no es conocido el tamaño del rango retornado...
Con este truco sí será posible... pero requiere un rango auxiliar de trabajo que ocultaremos a la vista ;-)
Recuerda cambiar, por estética, el formato de las fechas...
El primer paso será construir la conocida función FILTRAR, así en H4 escribiremos:
=FILTRAR(Tabla1;Tabla1[País]=$M$1)
que retorna todos los campos de la 'Tabla1' pero solo los registros con el país coincidente con el desplegado en la celda M1.
El siguiente paso consiste en numerar los registros obtenidos, lo que conseguiremos con la función SECUENCIA. Por tanto en la celda F4 escribiremos:
=SECUENCIA(FILAS(H4#))
que devuelve una secuencia ordenada desde 1 hasta el total de filas obtenidas con FILTRAR, de ahí la referencia a H4#
Siguiente paso importante... en la fila 3, la inmediata superior a los rangos desbordados obtenidos!!
En la celda G3 añadimos
=MAX(G4#)+1
con lo que conseguimos un ordinal superior en uno al número máximo de filas recuperadas con FILTRAR.
Mientras en H3 escribimos el texto que queramos ver en la fila de totales final.
Un truco importante para evitar formateos posteriores es escribir en I3 un espacio en blanco, de no hacer esto, posteriormente en el rango desbordado final, aquí veríamos un cero, que tendríamos que 'eliminar' con algún formato condicional o quizá personalizado!!.
Y finalmente en J3 para obtener el parcial por país añadimos un SUMAR.SI
=SUMAR.SI(Tabla1[País];$M$1;Tabla1[Unidades])
O si eres un amante de las nuevas funciones desbordadas
=SUMA(INDICE(H4#;0;3))
que suma la tercera columna del rango devuelto por FILTRAR.
Hasta aquí la fase de trabajo 'dura', y que debemos ocultar posteriormente :-(
Desarrollaremos por último el rango a visualizar...
Para ello en la celda L4 añadiremos la función ORDENARPOR como sigue:
=ORDENARPOR(H4#:H3;G4#:G3;1)
y aquí esta el gran truco del post!!
Debemos fijarnos en cómo hemos alimentado los argumentos..., se observa que la matriz a ordenar se informa como:
H4#:H3
curioso cuanto menos... esta forma permite indicar a la función que el rango a ordenar se compone del rango desbordado de FILTRAR (en H4) pero además se incluye la celda H3; que era donde aparecía el texto 'Parcial por país'...
De forma similar, para indicar el segundo argumento de ORDENARPOR y referir cuál es el rango sobre el que ordenaremos, usamos
G4#:G3
para incluir los valores ordenados 1,2,3.... incluyendo el orden de la 'Fila de totales' !!
Ya podemos ocultar las columnas auxiliares de cálculo F:J, y listo...
Tenemos, a partir del país elegido en M1, un resultado de registros de dicho país incluyendo un Subtotal al final del rango!!
Los usuarios que disponemos de estas funciones desbordadas sabemos del problema de incluir al final del rango desbordado devuelto un subtotal, ya que a priori no es conocido el tamaño del rango retornado...
Con este truco sí será posible... pero requiere un rango auxiliar de trabajo que ocultaremos a la vista ;-)
Recuerda cambiar, por estética, el formato de las fechas...
El primer paso será construir la conocida función FILTRAR, así en H4 escribiremos:
=FILTRAR(Tabla1;Tabla1[País]=$M$1)
que retorna todos los campos de la 'Tabla1' pero solo los registros con el país coincidente con el desplegado en la celda M1.
El siguiente paso consiste en numerar los registros obtenidos, lo que conseguiremos con la función SECUENCIA. Por tanto en la celda F4 escribiremos:
=SECUENCIA(FILAS(H4#))
que devuelve una secuencia ordenada desde 1 hasta el total de filas obtenidas con FILTRAR, de ahí la referencia a H4#
Siguiente paso importante... en la fila 3, la inmediata superior a los rangos desbordados obtenidos!!
En la celda G3 añadimos
=MAX(G4#)+1
con lo que conseguimos un ordinal superior en uno al número máximo de filas recuperadas con FILTRAR.
Mientras en H3 escribimos el texto que queramos ver en la fila de totales final.
Un truco importante para evitar formateos posteriores es escribir en I3 un espacio en blanco, de no hacer esto, posteriormente en el rango desbordado final, aquí veríamos un cero, que tendríamos que 'eliminar' con algún formato condicional o quizá personalizado!!.
Y finalmente en J3 para obtener el parcial por país añadimos un SUMAR.SI
=SUMAR.SI(Tabla1[País];$M$1;Tabla1[Unidades])
O si eres un amante de las nuevas funciones desbordadas
=SUMA(INDICE(H4#;0;3))
que suma la tercera columna del rango devuelto por FILTRAR.
Hasta aquí la fase de trabajo 'dura', y que debemos ocultar posteriormente :-(
Desarrollaremos por último el rango a visualizar...
Para ello en la celda L4 añadiremos la función ORDENARPOR como sigue:
=ORDENARPOR(H4#:H3;G4#:G3;1)
y aquí esta el gran truco del post!!
Debemos fijarnos en cómo hemos alimentado los argumentos..., se observa que la matriz a ordenar se informa como:
H4#:H3
curioso cuanto menos... esta forma permite indicar a la función que el rango a ordenar se compone del rango desbordado de FILTRAR (en H4) pero además se incluye la celda H3; que era donde aparecía el texto 'Parcial por país'...
De forma similar, para indicar el segundo argumento de ORDENARPOR y referir cuál es el rango sobre el que ordenaremos, usamos
G4#:G3
para incluir los valores ordenados 1,2,3.... incluyendo el orden de la 'Fila de totales' !!
Ya podemos ocultar las columnas auxiliares de cálculo F:J, y listo...
Tenemos, a partir del país elegido en M1, un resultado de registros de dicho país incluyendo un Subtotal al final del rango!!
No hay comentarios:
Publicar un comentario
Nota: solo los miembros de este blog pueden publicar comentarios.