Microsoft ha lanzado dos funciones para poder combinar de forma fácil matrices dinámicas, obteniendo los resultados en vertical u horizontal.
Recordar, que estas funciones nuevas están disponibles para usuarios que tienen la versión beta 2203 o posterior.
Puede que usted aun no disfrute de estas nuevas funciones matriciales.
Cuando hablamos de beta, queremos decir que estas funciones se lanzan para garantizar que funcionan correctamente, incluso si es insider puede que aun no le haya llegado, y, eso es porque no a todos le llegan las actualizaciones a la misma vez.
Estas funciones son:
· VSTACK / APILARV
· HSTACK / APILAH
Empezamos viendo la función matricial VSTACK, lo haremos con la versión es español.
Sintaxis
=APILARV(Matriz1,[Matriz2],...)
Si una matriz tiene menos columnas que el ancho máximo de las matrices seleccionadas, Excel devolverá un error #N/A en las columnas adicionales.
La función APILARV nos va a permitir unir dos o más matrices que estén separadas.
Por ejemplo, tenemos las siguientes matrices donde tenemos los totales en diferentes provincias en formato de moneda.
Vamos a unir matriz1 con matriz2.
Para ello, escribimos el signo igual seguido del nombre de la función, y, abrimos un paréntesis.
=APILARV(
Los únicos argumentos que tiene esta función son las matrices para unir o anexar.
Seleccionamos la primera matriz.
=APILARV(B2:C6
Punto y coma, seleccionamos la segunda matriz, cerramos paréntesis, y, aceptamos.
=APILARV(B2:C6; E2:F6)
Vemos como nos ha devuelto una matriz desbordada con la unión de los dos rangos, pero no se copian los formatos, por lo que somos nosotros los que debemos de ponerle el formato que le corresponda.
Lo siguiente que vamos a hacer es convertir ambos rangos en formato de tabla, para ello, con una celda dentro de la matriz desbordada, pulsamos CTRL mas T, que es el atajo de teclado de tabla.
Debemos de marcar, la tabla tiene encabezados.
Aceptamos.
Sabemos que las tablas trabajan por columnas, esto quiere decir, que cualquier elemento que añadamos automáticamente entra a formar parte de la tabla.
Pues al primer rango vamos a añadir un elemento nuevo.
Vemos como a la matriz desbordada resultante, se ha añadido el nuevo elemento, ahora, le añadimos un total.
También, tenemos la función matricial APILARH, la diferencia es que nos devuelve la matriz en horizontal, y, los argumentos son los mismos que para APILARV.
Para ello, primero, convertimos los modelos entabla, si queremos, el modelo matriz3, se llama tabla8, y, matriz4, se llama tabla9.
=APILARH(Tabla8; Tabla9)
Vemos que nos devuelve la matriz en la misma posición unidas, sin la columna de separación.
Veamos un ejemplo con APILARV.
En esta ocasión, tenemos dos rangos, pero con más líneas, donde hay provincias que se repiten.
Queremos saber el total de la provincia que seleccionemos.
Lo primero que vamos a hacer con APILARV es unir los dos rangos de las provincias, y, quedarnos con los valores únicos.
=UNICOS(APILARV(B2:B11; E2:E11))
Ahora, creamos una lista desplegable para seleccionar una provincia, mediante una lista desplegable a través de validación de datos.
Para ello, vamos a la pestaña datos, dentro del grupo nombres definidos, hacemos clic en validación de datos.
Desplegamos permitir, y, elegimos lista, en la ventana origen, seleccionamos la celda H2, junto con el operador de rango derramado (#).
Aceptamos.
Seleccionamos una provincia.
Seleccionamos una provincia.
Escribimos el signo igual seguido de la función FILTRAR, y, abrimos un paréntesis.
=FILTRAR(
Punto y coma, como argumento array, con la función APILARV unimos los valores numéricos.
=FILTRAR(APILARV (C2:C11; F2:F11)
Punto y coma, abrimos un paréntesis, para poner la condición, que es el argumento include de la función FILTRAR.
Con APILARV seleccionamos los rangos que contienen las provincias, es decir, los rangos a unir, ponemos el signo igual, y señalamos la celda que contiene la provincia a buscar.
=FILTRAR(APILARV(C2:C11; F2:F11);(APILARV (B2:B11; E2:E11) =J2))
Aceptamos, y, tenemos una matriz desbordada con los valores de la provincia seleccionada.
Pero, como queremos el total, pues delante usamos la función suma.
=SUMA(FILTRAR(APILARV(C2:C11; F2:F11);(APILARV (B2:B11; E2:E11) =J2)))
Y ya lo tenemos.
Este ejemplo, también, lo podemos resolver con el condicional SI, lo haremos en la celda M1, hay provincias que aparecen en las dos provincias.
Debemos de unir de las dos matrices, las provincias, cosa que haremos con APILARV, sería como sigue:
=APILARV(B2:B11;E2:E11)
Vemos que hay provincias que aparecen más de una vez.
Pues, en el argumento matriz1, vamos a preguntar si algún valor es igual a la provincia seleccionada.
=APILARV(SI(B2:B11=H1
En ese caso, que nos devuelva la misma posición, pero de la columna C.
=APILARV(SI(B2:B11=H1;C2:C11
En caso contrario, que nos devuelva un texto en blanco.
Cerramos paréntesis.
=APILARV(SI(B2:B11=H1;C2:C11;"")
Vamos al argumento matriz2, y, hacemos lo mismo para el rango E2:E11.
= APILARV(SI(B2:B11=H1;C2:C11;"");SI(E2:E11=H1;F2:F11;"")
Aceptamos, y, tenemos una matriz desbordada con los totales para la provincia seleccionada, y, blanco donde no hay coincidencia.
Sumamos los resultados.
=SUMA(APILARV(SI(B2:B11=H1;C2:C11;"");SI(E2:E11=H1;F2:F11;"")))
Tenemos el mismo total que el obtenido anteriormente.
Para el siguiente ejemplo, tenemos una serie de hojas, cada hoja es de una provincia, todas las hojas, tienen la misma estructura, es la venta de productos, en diferentes centros comerciales, y, diferentes fechas, en la provincia de la hoja.
Nos piden, calcular el total de todos los centros comerciales, lo haremos en una hoja nueva.
El problema que se presenta es que tenemos cada provincia en una hoja, lo cual, dificulta la tarea.
En el nivel básico, vimos como podíamos seleccionar varias hojas, que es seleccionado la primera hoja a seleccionar, dejar pulsada la tecla SHIFT, y, hacer clic en la última hoja a seleccionar, de esta manera, quedan seleccionadas la primera, y, ultima hoja, además de las hojas que entre medio de ellas.
Pues, es el sistema, que vamos a utilizar, para este ejemplo.
En la celda B3, escribimos el signo igual, seguido de la función APILARV, y, abrimos un paréntesis.
=APILARV(
El primer argumento es, matriz1, pues hacemos clic en la primera hoja, dejamos pulsada la tecla SHIFT, y, hacemos clic en la última hoja a seleccionar.
Vemos en la sintaxis, que aparece el nombre de la primera hoja, junto con los dos puntos, y, el nombre de la última hoja, seguido del signo de exclamación.
=APILARV(Albacete:Caceres!
Lo siguiente, es seleccionar el rango de rango de datos con el que vamos a trabajar, que es la columna de total, pues nos colocamos en la celda G2 de cualquier hoja, y, pulsamos CTRL más cursor abajo, voy a poner que llegue hasta la fila 100, porque ninguna provincia, llega a esa fila.
=APILARV(Albacete:Caceres!G2:G100
Cerramos paréntesis, y, aceptamos.
=APILARV(Albacete:Caceres!G2:G100)
Obtenemos una matriz desbordada, con la columna total de todas las hojas.
Pero, como queremos el total, después del signo igual, usamos la función SUMA, y, tenemos el total de todas las hojas.
=SUMA(APILARV(Albacete:Caceres!G2:G100))
Lo siguiente, que quiere este cliente, es un reporte, o, informe, de todas las provincias, pero, solo para el producto de aspiradora.
Lo, primero, que vamos a hacer, es con la función APILARV, traernos los datos completos de cada hoja, para ello, escribimos el signo igual, seguido del nombre de la función, y, abrimos un paréntesis.
=APILARV(
Como argumento, matriz1, seguimos el paso dado anteriormente, para traernos la columna de total de todas las hojas, aunque, debemos de cambiar el rango, como nos queremos traer todas las columnas, el rango debe de ser A2:A100.
=APILARV(Albacete:Caceres!A2:G100)
Tenemos una matriz desbordada, de momento, con todos los datos.
Si vamos bajando, veremos que después de acabar una provincia, aparecen ceros, porque le hemos dicho que vaya hasta la fila 100, donde no llegan las ventas, si queremos deshacernos de estos ceros, podemos usar la función FILRAR, donde como argumento array, es la función APILARV.
=FILTRAR(APILARV(Albacete:Caceres!A2:G100)
Punto y coma, como argumento include, usamos INDICE, donde como argumento matriz, es la expresión anterior, omitimos el argumento número de fila, y, como argumento numero de columna, ponemos 1, cerramos paréntesis de INDICE, y, lo comparamos siempre que sea diferente a cero.
=FILTRAR(APILARV(Albacete:Caceres!A2:G100);INDICE(APILARV(Albacete:Caceres!A2:G100);;1)<>0)
Aceptamos, y, si bajamos veremos que después de que termine una provincia, comienza la siguiente provincia.
Ya sabemos, que las matrices desbordadas, no copia los formatos.
Lo siguiente va a ser filtrar esta matriz desbordada, por el producto aspiradora, para ello, vamos a hacer uso de nuevo de la función FILTRAR, pero, como la expresión anterior, la vamos a usar más de una vez, usamos LET, creamos una variable, y, almacenamos la expresión anterior.
=LET(a;FILTRAR(APILARV(Albacete:Caceres!A2:G100);INDICE(APILARV(Albacete:Caceres!A2:G100);;1)<>0)
Punto y coma, como argumento include, filtramos la variable A, si, donde usamos INDICE para traernos la columna 4, que es la columna de los productos, es igual, entre comillas dobles, ponemos Aspiradora.
Cerramos paréntesis.
=LET(a;FILTRAR(APILARV(Albacete:Caceres!A2:G100);INDICE(APILARV(Albacete:Caceres!A2:G100);;1)<>0);FILTRAR(a;INDICE(a;;4)="Aspiradora"))
Aceptamos, y, ya tenemos el modelo filtrado por el producto Aspiradora.
Lo siguiente que nos piden, es cuantas aspiradoras se han vendido en total, entre todas las provincias, lo haremos en la celda B7, pues, solo debemos de hacer un recuento con la función CONTARA de cualquier columna obtenida en la mari desbordada anterior, la sintaxis quedaría:
=CONTARA(INDICE(E3#;;1))
Ya tenemos el recuento.
Para el siguiente ejemplo, seguimos trabajando con el modelo que suelo usar habitualmente, ventas de productos en diferentes centros comerciales, provincias, y, fechas.
El modelo está en formato de tabla, y, se llama Table1.
Lo primero, que vamos a hacer es crear una lista única de productos, para ello, vamos a hacer uso de la función UNICOS.
Lo vamos a hacer en una hoja nueva, para ello, en la celda B3, escribimos el signo igual, seguido de UNICOS, y, abrimos un paréntesis, seleccionamos la columna producto, cerramos paréntesis, y, aceptamos.
=UNICOS(Table1[Producto])
Ya tenemos los productos únicos.
Lo siguiente va a ser calcular el total para cada producto, para ello, vamos a usar la función SUMAR.SI, donde como argumento rango, va a ser la columna de producto.
=SUMAR.SI(Table1[Producto]
Punto y coma, como argumento criterio, va a ser los productos únicos, donde si seleccionamos el rango, vemos que se le añade el operador de rango derramado (#), también podemos escribir B3, y, manualmente añadir el operador de rango derramado.
=SUMAR.SI(Table1[Producto];B3#
Punto y coma, como argumento rango de suma, seleccionamos la columna de total.
Cerramos paréntesis.
Aceptamos, y, ya tenemos los totales por producto.
Ahora, queremos añadir una fila de total, después, de la última fila, podría poner después de la última fila ocupada, la palabra total, y, en la siguiente celda, calcular la suma, pero no sería dinámico, porque podemos añadir o quitar productos.
Debemos de trabajar con una sola matriz, una de las opciones es hacerlo con la función FILTRAR, donde vamos a filtrar estas dos matrices derramadas, y, como argumento include, vamos a usar una constante de matriz, donde ponemos dos 1, separado por la barra invertida, ya que están en columnas, por supuesto, entre llaves.
=FILTRAR(B3:C7;{1\1})
Ahora, las dos matrices, son una.
Lo siguiente va a ser traernos dentro de esta matriz desbordada, los encabezados, para ello, tenemos que hacer uso de la función APILARV.
Ya usamos la función FILTRAR, para unir ambas matrices desbordadas.
=FILTRAR(B3:C7;{1\1})
Ahora, después, del signo igual, ponemos la función APILARV, que nos va a unir diferentes matrices, en una sola, en vertical, pues para la primera matriz, señalamos los encabezados, y, para la segunda matriz, dejamos la función FILTRAR.
=APILARV(B2:C2;FILTRAR(B3:C7;{1\1}))
Vemos come se han añadido los encabezados.
Lo siguiente va a ser añadir la fila de total, en este caso, debemos de crear una matriz desbordada, con la palabra total, y, la suma de las cantidades, para ello, vamos a usar la función APILARH.
Hagámoslo en una celda, si escribo la siguiente sintaxis:
=APILARH("Total";SUMA(C3#))
Obtengo dicha matriz.
Pues, esta función, se lo vamos a añadir a la función APILARV, como ultimo argumento.
=APILARV(B2:C2;FILTRAR(B3:C7;{1\1});APILARH("total";SUMA(C3#)))
Ya tenemos nuestra matriz desbordada con los encabezados, y, la fila de total, que ya seria de forma dinámica.
Miguel Angel Franco Garcia
Comments