Fórmulas y Funciones Matriciales en Excel.

Operaciones matriciales de Tipo 2 (Unicelda).

 

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.

 

Las operaciones matriciales de Tipo 2 (Unicelda) las hemos definido antes como sigue:

"TIPO 2: Fórmulas de resumen. Ocupan una sola celda y se introducen con CONTROL + MAYUSCULAS + ENTER. Para crear este tipo de fórmulas matriciales debes hacer clic en una celda, teclear una fórmula y acabar con CONTROL + MAYUSCULAS + ENTER. La fórmula ha de contener una función de resumen, es decir, un conteo, una suma, un promedio... Equivalen a una fórmula unicelda según la subdivisión de Microsoft. Son las que más se usan y es necesario conocer las de Tipo 1 para entenderlas."

Se las puede llamar operaciones matriciales unicelda o unicelulares.

 

Requieren usar una función de resumen, como SUMA() por ejemplo y, dentro de ella, entre sus paréntesis, ponemos una función matricial de Tipo 1. No requieren seleccionar ningún rango: se pone la función de resumen en una celda, se coloca la función matricial de Tipo 1 entre los paréntesis de la función y se finaliza con CONTROL + MAYUSCULAS + ENTER. Estas funciones de resumen pueden ser SUMA(), CONTAR(), PROMEDIO(), MAX(), MIN() y otras. La función CONTARA() no puede usarse matricialmente. Veamos.

Si ya has descargado los ejemplos, abre el archivo "MatricesTipo2.xls" y haz clic en la hoja "MATRICES". Tiene que aparecer la hoja como sigue:

 

 

Vamos a calcular en una sola celda, la celda V11, la suma de todos los valores que hay en el rango desde B2 hasta J10, pero restando 3 a cada uno de los números del rango.

Por ser la primera fórmula matricial de Tipo 2 que haces vamos a seguir los pasos que mentalmente deberías hacer para crear una fórmula matricial de Tipo 2. Sigue estos pasos:

El primer paso es pensar cuál sería la fórmula matricial de Tipo 1 que usarías para crear una matriz que sea una copia del rango desde B2 hasta J10, pero restando 3 a cada número. Esta fórmula debería ser así:

• Selecciona un rango, como por ejemplo desde M2 hasta U10.
• Sin clicar en ningún sitio para no deshacer la selección, teclea =B2:J10-3
• Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

 

 

Observa que desde M2 hasta U10 están repetidos todos los valores del rango desde B2 hasta J10 pero restándoles 3. Si haces clic en cualquier celda del rango desde M2 hasta U10, la fórmula debería ser:

 

 

Hasta ahora, todo es lo mismo que hemos visto en el apartado de fórmulas matriciales de Tipo 1. Ahora haz lo que sigue:

• Haz clic en V10
• Teclea la fórmula estándar, no matricial, =SUMA(M2:U10) acabando con un ENTER normal.

El resultado en V10 debería ser:

2673

Toma mentalmente nota de que la fórmula matricial de Tipo 1 es: B2:J10-3

Una vez hemos tomado nota mental de que la fórmula de Tipo 1 es B2:J10-3, pasamos al segundo paso, que es crear la fórmula matricial de Tipo 2 en V11. Esta fórmula se hace con la función de resumen, que es =SUMA(...) en este caso, dentro de la cual como parámetro ponemos la fórmula matricial de Tipo 1, que es B2:J10-3 en este caso, y acabar con CONTROL+MAYUSCULAS+ENTER. Esto debería dar el mismo resultado que hemos obtenido en V10, pero con la ventaja de que se hace todo el cálculo con una sola fórmula y en una sola celda. Veamos:

• Haz clic en V11
• Teclea =SUMA(B2:J10-3), puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
• Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado que debe aparecer en la celda V11 es:

2673

Y la fórmula que aparece en la celda V11 es:
 

 

Es decir, la fórmula que hemos tecleado junto con las llaves { y } que pone Excel para indicar que es una fórmula matricial.

Como puedes ver, el resultado es exactamente el mismo en la celda V10 y en la celda V11.

 

Puedes ir cambiando valores del rango B2:J10 y verás cómo ambas fórmulas dan el mismo resultado.

 

 

Mira cómo se hace:

 

Por tanto, hemos de extraer esta conclusión. En las matrices de Excel, es lo mismo:

• opción 1: seleccionar un rango rangoX, poner la fórmula =B2:J10-3, acabar con CONTROL+MAYUSCULAS+ENTER y poner en otra celda la función estándar =SUMA(rangoX) acabada con ENTER, es lo mismo que...
• opción 2: usar directamente en una sola celda la función =SUMA(B2:J10-3) y acabarla con CONTROL+MAYUSCULAS+ENTER.

En ambos casos obtengo la suma de todos los elementos de una matriz que sería el resultado de restar 3 a cada valor del rango desde B2 hasta J10. La diferencia es que:

• en la opción 1, la matriz que tiene la copia del rango B2:J10 menos 3, está a la vista en la hoja, y ocupa el rangoX.
• en la opción 2, la matriz que tiene la copia del rango B2:J10 menos 3 no se ve, la calcula Excel y la pone en algún lugar de la memoria de la computadora sin que nosotros la veamos.

En nuestro caso concreto tenemos que es lo mismo:

• poner en el rango M2:U10 la fórmula matricial =B2:J10-3, y sumar este rango con la función estándar =SUMA(M2:U10) acabada con ENTER, es lo mismo que...
• usar directamente en una sola celda la función =SUMA(B2:J10-3) y acabarla con CONTROL+MAYUSCULAS+ENTER.

Y también:

• en el primer caso se ve la matriz con los cálculos intermedios en el rango M2:U10 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.

Así pues, esto es en lo que difieren las fórmulas matriciales de Tipo 1 y de Tipo 2:

 

Una fórmula matricial de Tipo 2 es un resumen (suma, contar, promedio, máximo, mínimo...) de una matriz que no está a la vista en la hoja de cálculo, pero que sería una matriz que estaría a la vista si hubiésemos usado una fórmula matricial de Tipo 1.

 

Nuevamente, insistimos, ha de quedar claro que hay que entender bien las fórmulas matriciales de Tipo 1 para poder entender las de Tipo 2, ya que una fórmula matricial de Tipo 2 no es más que una función de resumen junto con una función matricial de Tipo 1.

Para acabar de entender mejor lo que es una fórmula matricial de Tipo 2, haz lo siguiente:

• Selecciona el rango desde M2 hasta U10
• Pulsa la tecla SUPR para borrarlo
• La celda V10 pasa a mostrar el valor 0
• La celda V11 no cambia de valor ya el resultado que muestra esta celda no depende para nada de ninguna matriz visible en la hoja de cálculo, la matriz de la que obtiene la suma la fórmula en V11 está en algún lugar de la memoria de la computadora y nosotros no la vemos.
• Además, si sigues cambiando valores en el rango desde B2 hasta J10, la fórmula en V11 siempre mostrará el resultado correcto, ya que la matriz en la memoria de la computadora se va actualizando con cada cambio.

 

Ejemplo más práctico

Vamos a ver otro ejemplo, seguimos en el archivo "MatricesTipo2.xls". Haz clic en la hoja "Cálculos". Tiene que aparecer la hoja como sigue:

 

 

En esta hoja "Cálculos" deseo obtener, en una sola celda y usando una sola función matricial, la suma de cantidad × precio unitario de todas las filas. Usaremos la celda F27. Sigue estos pasos:

• Clic en F27
• Teclea la fórmula =SUMA(B3:B24*C3:C24) puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse
• Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado que debe aparecer en la celda F27 es:

233850

Y la fórmula que aparece en la celda F27 es:

 

Es decir, la fórmula que hemos tecleado junto con las llaves { y } que pone Excel para indicar que es una fórmula matricial.

¿Es correcta esa suma? ¿Cómo lo comprobamos?

Podemos comprobar que el resultado es correcto volviendo a la fórmula matricial de Tipo 1 que ya hicimos en el tema anterior y calcular la suma de la columna F. Por tanto , abre nuevamente el archivo del anterior "MatricesTipo1.xls" y ve a la hoja "Cálculos". Recuerda que dijimos que la guardases así que tienes que tener esto:

 

^{(Si no tienes esto selecciona el rango F3:F24 teclea la fórmula =B3:B24*C3:C24 y acaba con CONTROL+MAYUSCULAS+ENTER)}

 

Ahora haz clic en F25 y teclea la fórmula estándar =SUMA(F3:F24) acabada con ENTER. El resultado ha de ser igualmente 233850.

 

Puedes ir cambiando algunos valores de las columnas B o C en las hojas "Calculo" de ambos archivos "MatricesTipo1.xls" y "MatricesTipo2.xls" y verás cómo cambia el total y cómo el resultado es el mismo en ambas hojas.

 

Observa que este total de la fórmula matricial =SUMA(B3:B24*C3:C24) que hemos obtenido en el archivo "MatricesTipo2.xls", se ha obtenido en una sola celda y sin usar celdas con cálculos intermedios. La fórmula matricial que hemos usado en "MatricesTipo2.xls" es la misma fórmula del Tipo 1 usada en el archivo "MatricesTipo1.xls": B3:B24*C3:C24, pero con la diferencia de que se le ha añadido la función de resumen SUMA() y ,en este caso, Excel primero calcula la matriz B3:B24*C3:C24 y luego suma todos los elementos de esta matriz.

Por tanto, para usar una función matricial de resumen de Tipo 2, es conveniente pensar primero en la operación matricial del Tipo 1 que tendríamos que hacer, luego esta misma operación se pone como parámetro dentro de la función de resumen y se acaba con CONTROL+MAYUSCULAS+ENTER.

Es decir, en matrices, es lo mismo:

• opción 1: seleccionar un rango rangoX, poner la fórmula =rango1*rango2, acabar con CONTROL+MAYUSCULAS+ENTER y poner en otra celda la función estándar =SUMA(rangoX) acabada con ENTER, es lo mismo que...
• opción 2: usar directamente en una sola celda la función =SUMA(rango1*rango2) y acabarla con CONTROL+MAYUSCULAS+ENTER.

En ambos casos obtengo la suma de todos los elementos de una matriz que sería el resultado de multiplicar cada valor del rango1 por su correspondiente valor en la misma posición del rango2. La diferencia es que:

• en la opción 1, la matriz que tiene la multiplicación de matrices, está a la vista en la hoja, y ocupa el rangoX .
• en la opción 2, la matriz que tiene multiplicación de matrices no se ve, la calcula Excel y la pone en algún lugar de la memoria de la computadora sin que nosotros la veamos.

En nuestro caso concreto tenemos que es lo mismo:

• poner en el rango F3:F24 la fórmula matricial =B3:B24*C3:C24, y sumar este rango con la función estándar =SUMA(F3:F24) acabada con ENTER, es lo mismo que...
• usar directamente en una sola celda la función =SUMA(B3:B24*C3:C24) y acabarla con CONTROL+MAYUSCULAS+ENTER.

Y también:

• en el primer caso se ve la matriz con los cálculos intermedios en el rango F3:F24 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.

 

Por tanto, para entender bien las fórmulas matriciales de resumen del Tipo 2 hay que entender bien primero las del Tipo 1. Quizás las fórmulas matriciales de Tipo 1 no se usen mucho, o se puedan sustituir fácilmente por funciones normales, pero las fórmulas matriciales de Tipo 2 no pueden sustituirse por funciones normales y requieren haber entendido bien las de Tipo 1.

 

Ahora hazlo tú:

Obtén en la celda H27 de la hoja "Cálculos" del archivo "MatricesTipo2.xls", la suma del cálculo:

=cantidad*precio unitario*(1–descuento%)*(1+IVA%)

para todas las filas del rango B3:E24 usando una sola fórmula matricial en H27 sin usar celdas con cálculos intermedios. Usa SUMA() como función de resumen. El resultado debería ser:

239209,615

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Para calcular una fórmula de resumen matricial, Excel hace primero internamente los mismos cálculos que haríamos nosotros con una fórmula de Tipo 1, Excel memoriza el resultado en una matriz en la memoria de la computadora (nosotros no vemos esa matriz) y luego aplica la función de resumen a esa matriz (es decir, a los elementos de esa matriz) y muestra el resultado.

Esto hace que las fórmulas matriciales vayan más lentas que las demás, esto se nota mucho si los rangos con los que trabajamos son muy grandes, notaremos que Excel va más lento de lo normal.

 

Queda claro pues que, si no se ha entendido bien las matrices de Tipo 1, difícilmente se va a entender las de Tipo 2. Si es necesario, repasa el tema anterior hasta que lo entiendas bien.

 

Ahora hazlo tú:

En la hoja "MATRICES" del archivo "MatricesTipo2.xls" obtén en la celda J22 el promedio del rango B2:J10 pero multiplicando cada elemento de dicho rango por 3 y sin usar celdas con valores intermedios. El resultado en J22 debería ser (con los datos que había en el rango B2:J10 al abrir el archivo):

108

 

Te damos una pista:

Clic aquí para ver

 

Te damos la solución:

Clic aquí para ver

 

Referencias absolutas con las fórmulas matriciales de Tipo 2

Continuando con la fórmula anterior, al igual que las fórmulas matriciales de Tipo 1, si el 3 va en una celda externa, no será necesario ponerle $, así, la fórmula anterior es equivalente a:

=PROMEDIO(B2:J10*A12)

con la condición de que en A12 haya un 3.

 

Resúmenes de datos usando la función SI()

En el tema de las fórmulas matriciales de Tipo 1, vimos el uso matricial de la función SI(). La función SI() también puede usarse en las fórmulas matriciales de Tipo 2 pero dentro de alguna función de resumen como SUMA(), PROMEDIO(), CONTAR(), MAX(), MIN()... quedando pues las fórmulas SUMA(SI(...)), PROMEDIO(SI(...)), CONTAR(SI(...)), MAX(SI(...)), MIN(SI(...))... Se usan para sumar, contar, promediar, etc, datos que cumplan ciertas condiciones, por ejemplo, sumar unas cantidades solamente de un cliente.

 

Estas fórmulas SUMA(SI(...)), PROMEDIO(SI(...)), CONTAR(SI(...)), MAX(SI(...)), MIN(SI(...))... son fórmulas matriciales de Tipo 2, es decir, funciones de resumen que usan como parámetro una fórmula matricial de Tipo 1 hecha con un SI(). Pero, dado que estas fórmulas son muy usadas en el mundillo de las matrices de Excel, prácticamente se las considera fórmulas matriciales especiales que se denominan por su nombre tal cual: SUMA(SI(...)), CONTAR(SI(...)), PROMEDIO(SI(...)), etc.

 

Es por eso que trataremos la función SI() como un subtema especial en las matrices de Tipo 2 en la siguiente Entrega.

 

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