Vamos a ver un ejemplo con las funciones SUMAR.SI, SUMAR.SI.CONJUNTO, TRANSPONER e INDICE con matrices dinámicas.
Tenemos el siguiente modelo de datos, con el que suelo trabajar, ventas de teléfonos en diferentes países.
El listado es algo más grande.
Vamos a crear una lista de países únicos ordenados, una lista de operadores también únicos, y, ordenados, después, vamos a calcular la cantidad vendida por cada país, y, después, la cantidad vendida por cada país y operador.
Lo primero, para conseguir los países únicos, vamos a usar la función UNICOS y ORDENAR, de la siguiente manera:
=ORDENAR(UNICOS(País))
El resultado:
Lo siguiente va a ser calcular la cantidad vendida por cada país, lo haremos con la función SUMAR.SI, pero de forma matricial.
En la barra de fórmulas, escribimos el signo igual, a continuación, escribirnos SUMAR.SI(, como rango de criterios, será la columna de país, como criterio, será, la columna de países únicos creadas ya no será una celda, y, como rango de suma va a ser el nombre de rango cantidad.
La función seria:
=SUMAR.SI(País;J2#;Cantidad)
Vemos como se usa el operador de rango derramado, J#, quiere decir que seleccione el valor de la celda J2, y, todas las celdas hacia abajo, en este caso, que hayan ocupadas.
Aceptamos, y, ya tenemos el resultado.
Si lo hiciéramos de forma no matricial, deberíamos de escribir la siguiente formula:
=SUMAR.SI(País;J2;Cantidad)
Fijamos J2, de forma absoluta, o sea, $J$2, y, copiamos hacia abajo.
Vemos que damos más de un paso, a diferencia, de la matriz derramada.
Lo siguiente que vamos a hacer, es crear un cuadro, donde en vertical están los países, y, en horizontal, los operadores.
Con la función INDICE, vamos a extraer cada país, de la columna J, donde están los países únicos, vamos a ver el uso de la función INDICE, para crearla de forma dinámica, también, podríamos rellenar la matriz como lo hemos hecho al principio, con la función UNICOS y ORDENAR, pero se trata de ver el uso de la función INDICE para que como resultado obtengamos una matriz derramada.
Si quiero extraer el primer país, usaría la siguiente sintaxis, donde como argumento array, seleccionamos la celda J2, seguido del operador de rango derramado (#), como argumento numero de fila, ponemos 1, y, como argumento numero de columna, también, ponemos 1, aunque, si omitimos el argumento numero de columna, de forma predeterminada, pondrá 1.
=INDICE(J2#;1;1)
Pero, queremos que genere toda la lista de países con la función INDICE.
Entonces, debemos de modificar la sintaxis anterior de la siguiente manera. ¿de cuántos valores va a estar formada?, esos valores lo vamos a sacar con la función CONTARA, que nos devuelve un recuento de celdas alfanuméricas dentro de un rango, entonces, si ponemos la siguiente sintaxis, CONTARA(J2#), nos devolverá el número de países que hay, en este caso, 24.
Este va a ser el argumento fila de la función SECUENCIA, en la función SECUENCIA, como argumento filas, usaremos la función CONTARA, como argumento columnas, usaremos el valor 1, como argumento paso, usamos 1, es decir, que empiece desde 1, y, como argumento paso, ponemos 1, es decir, que vaya incrementado cada número de uno en uno, quedando de la siguiente manera:
=INDICE(J2#;SECUENCIA(CONTARA(J2#);1;1;1);1)
Como resultado, tenemos la misma lista de países únicos.
Lo siguiente, va a ser poner de forma horizontal los operadores, para ello, en la celda O9, con la función UNICOS, voy a traerme los operadores únicos.
= UNICOS(Operador)
Estos operadores aparecen en vertical, pero lo queremos en horizontal, por lo que usamos la función TRANSPONER, que invierte un rango de vertical a horizontal, o, viceversa, lo haremos de la siguiente manera:
=TRANSPONER(UNICOS(Operador))
Ya tenemos creado el cuadro, lo siguiente va a ser a través de la función SUMAR.SI.CONJUNTO, calcular las cantidades vendidas por cada país y operador, donde como argumento rango de suma, usamos la columna cantidad, como rango de criterios1, en la columna de pais, como criterio1, es a partir de la celda J2 junto con el operador de rango derramado, como rango de criterios2, es la columna de operador, y, como criterios2, es a partir de la celda N9, junto con el operador de rango derramado, que es donde están los operadores, la sintaxis seria:
=SUMAR.SI.CONJUNTO(Cantidad;País;J2#;Operador;N9#)
Ya tenemos lo que estábamos buscando, damos por terminado este ejemplo.
Comments