Fórmulas y Funciones Matriciales en Excel.

Operaciones matriciales de Tipo 2 junto con la función SI().

Fórmulas matriciales: Curso de Matrices en Excel para aprender a trabajar con rangos en vez de con celdas con la finalidad de trabajar con datos organizados en rangos más rápidamente.

Temario: trabajar con rangos matriciales: operaciones básicas y función SI(). Fórmulas de resumen de rangos matriciales: SUMA(), CONTAR(), PROMEDIO(). Uso de SUMA(SI()) para contar. CONTAR.SI() matricial. Funciones matriciales específicas: FRECUENCIA(), TENDENCIA() y SUMAPRODUCTO(). Funciones SI CONJUNTO. Lógica Matricial.
Descarga los ejemplos en: https://www.dropbox.com/s/s6o0zylb6thit0y/Excel-Matrices.zip?dl=1

Puedes ir al índice de contenidos para ver todos los Minicursos o a la primera entrega de este minicurso de Matrices en Excel.

Uso de las funciones matriciales de Tipo 2 anidadas: SUMA(SI(...))

Como hemos dicho, hemos creado un subapartado especial para la función matricial SUMA(SI(...)), pero sigue siendo una fórmula matricial de Tipo 2. Por tanto, seguimos en el apartado de las fórmulas matriciales de Tipo 2.

En la hoja "MATRICES" del archivo "MatricesTipo2.xls" podemos hacer en V21 una fórmula que sume solamente los números mayores o iguales que 50 del rango B2:J10. Sigue estos pasos:

Haz clic en V21
Teclea la fórmula =SUMA(SI(B2:J10>=50;B2:J10;0)), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

1435

Fíjate que la función de resumen es SUMA() y que la operación que hay dentro es una operación matricial SI(), que es la misma que hemos puesto en el tema anterior, en el archivo "MatricesTipo1.xls", en el rango M12:U20. Por tanto, podéis comprobar si está bien sumando este rango calculado en el tema anterior con una suma estándar.

En este caso concreto tenemos que es lo mismo:

poner en el rango M12:U20 la fórmula matricial =SI(B2:J10>=50;B2:J10;0), y sumar este rango con la función estándar =SUMA(M12:U20) acabada con ENTER, es lo mismo que...
usar directamente en una sola celda la función =SUMA(SI(B2:J10>=50;B2:J10;0)) y acabarla con CONTROL+MAYUSCULAS+ENTER.

Y también:

en el primer caso se ve la matriz con los cálculos intermedios en M12:U20 y se ven los elementos de esa matriz que luego sumaremos.
en el segundo caso no se ve la matriz, la pone Excel en la memoria de la computadora y hace la suma con esta matriz que no se ve.

La función SI(B2:J10>=50;B2:J10;0)) calcula del rango B2:J10 el valor correspondiente si es mayor que 50 y si no, devuelve un 0, Excel guarda la matriz resultante en la memoria interna de la computadora y la función de resumen SUMA() hace la suma de estos valores guardados en la memoria interna.

Vamos a ver un ejemplo más práctico. Seguimos en el archivo "MatricesTipo2.xls" con las pestañas de color naranja. Hacer clic en la hoja "Resúmenes". Debería aparecer esto:

Como veis, continuamos con el sistema europeo en euros (€).

En esta hoja, hay una serie de facturas de ciertos clientes con ciertos plazos de pago y la columna H me indica con una X roja si está impagada cada factura.

En las celdas que hay desde J1 hasta M9 quiero saber algunos datos del cliente C001: cuántas facturas tiene en total y cuántas a 30, 60 y 90 días y cuánto totalizan dichas facturas. También me gustaría desglosarlas en facturas pagadas e impagadas.

Vamos a empezar con algo fácil y luego lo complicaremos poco a poco.

Calcularemos en L2 la suma de las cantidades de todas las facturas del cliente C001. Para ello haz:

Clic en L2
Teclea =SUMA(SI(D2:D21="C001";E2:E21;0)), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
Y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado en L2 es:

6.222

La función SI() matricial devuelve los valores del rango E2:E21 que cumplan que la correspondiente fila del rango D2:D21 sea C001, igual que si se tratase de una fórmula matricial de Tipo 1, luego esos valores devueltos son sumados por la función de resumen SUMA(). Dicho de otra manera, me suma las cantidades facturadas por el cliente C001. En este caso concreto tenemos que hubiera sido lo mismo:

poner en un rango cualquiera rangoX la fórmula matricial =SI(D2:D21="C001";E2:E21;0), y sumar los elementos de este rangoX en L2 con la fórmula estándar =SUMA(rangoX) acabada con ENTER, hubiera sido lo mismo que...
lo que hemos hecho, es decir, usar directamente en una sola celda, en L2, la función =SUMA(SI(D2:D21="C001";E2:E21;0)) y acabarla con CONTROL+MAYUSCULAS+ENTER.

Y también:

en el primer caso se hubiera visto la matriz con los cálculos intermedios en el rangoX y se hubieran visto los elementos de esa matriz que luego sumaríamos.
en el segundo caso no se ve la matriz, la pone Excel en la memoria de la computadora y hace la suma con esta matriz que no se ve.

Se trata del primer ejemplo que hemos hecho directamente usando la función de resumen sin habernos entrenado antes en hacer la matriz intermedia de Tipo 1. A partir de ahora los haremos ya todos así, directamente usando la función de resumen matricial de Tipo 2 con la función matricial de Tipo 1 dentro. Ya no haremos previamente la de Tipo 1.

Sin embargo, solamente en este ejemplo y con la finalidad de que entiendas mejor el uso matricial de la función SI(), haremos el paso inverso, es decir, desde la función matricial de Tipo 2 hacia la función matricial de Tipo 1. Así verás cuál es la matriz que Excel mantiene en la memoria de la computadora (la que no se ve) y verás por qué al sumar los elementos de esa matriz que no se ve, obtenemos la suma de las cantidades solamente del cliente "C001". Usaremos como rango provisional las celdas desde I2 hasta I21. Recuerda que seguimos en el archivo "MatricesTipo2.xls" con las pestañas de color naranja en la hoja "Resúmenes". Sigue estos pasos:

Selecciona el rango I2:I21
Sin hacer clic en ningún sitio para que no se deshaga la selección, teclea la fórmula =SI(D2:D21="C001";E2:E21;0)
Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

Observa que en la columna I se repiten los valores de la columna E, pero solamente aquellos valores que en la columna D tienen el valor "C001" en la misma posición. Aquí los hemos marcado en colores:

Si sumas los valores de este rango con una suma convencional (no matricial) verás que el resultado es el mismo: 6222. Esta matriz que ves ahora en el rango I2:I21 es, pues, la matriz de Tipo 1 que Excel mantiene invisible en la memoria de la computadora y que sirve como parámetro para las fórmulas matriciales de Tipo 2.

Borra esta matriz dado que no nos es útil: selecciona desde I2 hasta I21 y pulsa la tecla SUPR.

Veamos un ejemplo más:

Vamos a sumar todas las facturas impagadas de todos los clientes. Hemos dicho que las impagadas son las facturas que tienen una X roja en la columna H. En este ejemplo usaremos una simplificación que se usa mucho en el mundillo de las matrices en Excel que consiste en no poner el cero del tercer parámetro a la función SI(). El resultado será el mismo que si se pusiese ese cero. Veamos:

Haz clic en L7
Teclea la fórmula matricial =SUMA(SI(H2:H21="X";E2:E21)), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse. Observa que la función SI() tiene solamente dos parámetros, no hemos puesto el "SI(....;0)" que hemos usado hasta ahora.
Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado en L7 es:

4.281

Observa que no cambia nada de lo que hemos dicho acerca de las funciones matriciales de Tipo 2: es una función de resumen SUMA() dentro de la cual colocamos la operación matricial de Tipo 1. En este ejemplo la fórmula matricial de Tipo 1 es la siguiente, resaltada en color:

=SUMA( SI(H2:H21="X";E2:E21) )

Como cualquier fórmula de Excel, podemos ir cambiando valores de la columna E y ver cómo cambian los resultados.

Esta fórmula es totalmente equivalente a la fórmula =SUMA(SI(H2:H21="X";E2:E21;0)).

El uso matricial de la función de resumen de Tipo 2 SUMA() dentro de la cual hay una función SI(), es un recurso muy usado dentro del mundillo de las matrices de Excel, de ahí que hayamos hecho este subapartado especial que hemos llamado "Uso de las funciones matriciales de Tipo 2 anidadas: SUMA(SI())".

Uso de las funciones matriciales de Tipo 2 anidadas: CONTAR(SI())

Como hemos dicho, hemos creado un subapartado para la función matricial CONTAR(SI(...)), pero sigue siendo una fórmula matricial de Tipo 2. Por tanto, seguimos en el apartado de las fórmulas matriciales de Tipo 2.

Al igual que un SUMA(SI()) matricial de Tipo 2 es muy usado en el mundillo de las matrices, la fórmula matricial CONTAR(SI()) es también muy usada y, por tanto, también le dedicaremos un subtema propio.

La función CONTAR() cuenta los números que hay en un rango. Recuerda que seguimos en el archivo "MatricesTipo2.xls" con las pestañas de color naranja en la hoja "Resúmenes". Vamos a contar las facturas de 30 días de todos los clientes. Provisionalmente haremos el cálculo para todos los clientes a pesar de que en la columna J ponga "C001":

Clic en M3
Teclea =CONTAR(SI(C2:C21=30;E2:E21))
Y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado en M3 debería ser:

13

Y la fórmula en M3 debería aparecer con las llaves { y }:

Puedes ver una vez más que el SI() devuelve las celdas del rango E2:E21 que están en la misma posición que las que tienen un 30 en el rango C2:C21, y la función CONTAR() va contando todos los elementos devueltos si son un número. Aquí te lo marcamos en color para que lo veas mejor. Puedes contar a ojo cuántos valores 30 hay:

En este caso concreto tenemos que hubiera sido lo mismo:

poner en un rango cualquiera rangoX la fórmula matricial =SI(C2:C21=30;E2:E21), y contar los elementos de este rangoX en M3 con una fórmula estándar =CONTAR(rangoX) acabada con ENTER, hubiera sido lo mismo que...
lo que hemos hecho, es decir, usar directamente en una sola celda, en M3, la función =CONTAR(SI(C2:C21=30;E2:E21)) y acabarla con CONTROL+MAYUSCULAS+ENTER.

Y también:

en el primer caso se hubiera visto la matriz con los valores intermedios en rangoX y se hubieran visto los elementos de esa matriz que luego sumaríamos.
en el segundo caso no se ve la matriz, la pone Excel en la memoria de la computadora y hace la suma con esta matriz que no se ve.

Como siempre que se usa la función CONTAR() hay que tener la precaución de que el rango a contar, en este caso E2:E21, ha de tener solamente números, no puede tener filas vacías ni tener texto, o no contará bien. Por ejemplo la fórmula =CONTAR(SI(C2:C21=30;D2:D21)) no daría un resultado correcto porque en D2:D21 hay textos.

Es por eso que en el mundillo de las matrices de Excel se prefiere usar la función SUMA() para... ¡contar! Veamos.

Uso de las funciones matriciales de Tipo 2 anidadas: SUMA(SI(...;1;0)) para contar datos no numéricos o de cualquier tipo

Como hemos dicho, hemos creado un subapartado para la función matricial SUMA(SI(...;1;0)), pero sigue siendo una fórmula matricial de Tipo 2. Por tanto, seguimos en el apartado de las fórmulas matriciales de Tipo 2.

Dadas las limitaciones de la función CONTAR(), existe, en el mundillo de las matrices de Excel, un truco para contar todo tipo de datos, sean números, textos, celdas vacías, celdas no vacías, etc. Este truco consiste en usar la función matricial =SUMA(SI(prueba lógica matricial;1;0)) de manera que la función SI() devuelve un 1 cuando se cumple la prueba lógica y devuelve un 0 cuando no se cumple. Entonces lo que hace la función de resumen SUMA() es ir sumando estos 1's y 0's devueltos. Por tanto, la suma de tantos 1's como veces se cumple la prueba lógica es equivalente a contar cuántas veces es cierta la prueba lógica y esto se puede usar para contar números, textos, celdas vacías, etc.

Este truco se denomina "el truco de contar con la función SUMA()".

Nota: Tal vez penséis que sería mejor usar la función CONTARA() que cuenta textos, pero resulta que Excel no permite usar todas las funciones como funciones de resumen matricial y, justamente, CONTARA() es de las funciones que Excel no permite usar como función matricial de resumen. Nunca usar CONTARA() en una fórmula matricial de ningún Tipo, ya que daría un resultado erróneo sin que Excel avisase del error.

Por ejemplo, vamos a calcular de nuevo la cantidad de facturas a 30 días, pero usando "el truco de contar con SUMA(SI(...;1;0))". Provisionalmente haremos el cálculo para todos los clientes a pesar de que en la columna J ponga "C001". Los pasos serían:

Clic en M3. Pulsa la tecla SUPR para borrar su contenido
Teclea =SUMA(SI(C2:C21=30;1;0))
Y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado en M3 será es mismo que antes:

13

Y la fórmula contenida en M3 aparecerá como ¡una suma!:

Nota: se puede simplificar la fórmula como =SUMA(SI(C2:C21=30;1)) y el resultado seguirá siendo 13:

Este truco de contar usando la función SUMA(SI(...;1)) se usa mucho en el mundillo de las matrices de Excel, por tanto, conviene saberlo cuando busques funciones matriciales por internet ya que, en muchos casos, cuando veas la función SUMA() matricial de Tipo 2, a lo mejor no están sumando ¡están contando!

Uso de otras funciones anidadas con SI() de Tipo 2

Otras funciones como PROMEDIO(SI(...)), MAX(SI(...)) y MIN(SI(...)) también pueden usarse como fórmula matricial de resumen de Tipo 2, pero debéis tener más cuidado al usarlas por el siguiente motivo:

Al hacer una operación de SUMA con las celdas de un rango, las celdas de dicho rango que contienen el valor cero no influyen en la suma. Sin embargo, para otras operaciones, como PROMEDIO, MÁXIMO, MÍNIMO o DESVIACIÓN ESTÁNDAR, las celdas que contienen el valor cero sí que influyen en el resultado. Cuando se usan dichas funciones en una operación matricial en Excel con un SI() anidado, es mejor poner una cadena vacía "" como tercer parámetros de la función SI() anidada o bien no poner nada.

Por ejemplo, continuando en el archivo "MatricesTipo2.xls" en la hoja "Resúmenes", suponer que alguien os solicita cuál es la factura con la cantidad más pequeña del cliente C001. Tendréis que buscar la factura con la cantidad más pequeña usando una función MIN(SI(...)) matricial, pero la fórmula correcta sería una de estas dos:

=MIN(SI(D2:D21="C001";E2:E21))

=MIN(SI(D2:D21="C001";E2:E21;""))

y nunca habrá que usar esta porque dará un resultado erróneo:

=MIN(SI(D2:D21="C001";E2:E21;0)) → ¡siempre dará resultado cero!

Uso de celdas externas en las fórmulas matriciales de Tipo 2

Vamos a complicar un poco el ejemplo, en realidad podemos sustituir el "C001" por la celda externa J2 que contiene C001, de manera que podemos obtener igualmente en L2 la suma de las cantidades de las facturas del cliente C001:

Haz lo siguiente:

Clic en L2. Pulsa la tecla SUPR para borrar su contenido
Teclea =SUMA(SI(D2:D21=J2;E2:E21))
Y acaba con CONTROL+MAYUSCULAS+ENTER.
Clic en M2
Teclea =CONTAR(SI(D2:D21=J2;E2:E21))
Y acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

Da el mismo resultado que antes 6.222. La diferencia es que ahora si cambio el valor de J2 por otro cliente, la suma también cambiará: C001 da 6.222, pero si ponemos en J2 C002 en L2 sale 2.460:

Si ponemos en J2 C003, en L2 sale 1.237:

... y así sucesivamente con todos los clientes.

Fíjate que también el conteo de facturas en M2 van cambiando.

También podríamos sustituir el 30 por una celda externa que contuviese el valor de 30 para contar las facturas a 30 días. En este caso podríamos usar la celda K3 y poner en M3 la fórmula matricial =SUMA(SI(C2:C21=K3;1)), observa que el cliente en J2 no influye en el resultado:

Si cambiáis el valor de la celda K3 por 60 o por 90 el valor en M3 también irá cambiando.

_{Continua en la siguiente entrega. Puedes también ir al Indice de los Minicursos o Volver arriba.}

Minicursos

blog de minicursos de ofimática y programación al precio de donación voluntaria