Fórmulas y Funciones Matriciales en Excel.

Operaciones matriciales de Tipo 1 (Multicelda).

 

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 1 (Multicelda) las hemos definido antes como sigue:

"TIPO 1: Una misma fórmula en las celdas de un rango. Ocupan varias celdas y se introducen con CONTROL + MAYUSCULAS + ENTER. Para crear este tipo de fórmulas matriciales debes primero seleccionar un rango, teclear una fórmula y acabar con CONTROL + MAYUSCULAS + ENTER, entonces Excel rellena todo el rango con la misma fórmula automáticamente. Equivalen a una fórmula multicelda según la subdivisión de Microsoft. Se usan poco pero son necesarias para entender las fórmulas matriciales de Tipo 2."

 

Se las puede llamar operaciones matriciales multicelda o multicelulares. Si bien no se usan mucho, es necesario entender muy bien las de Tipo 1 para poder entender las de Tipo 2, que son las que realmente se usan.

 

Como hemos dicho, una fórmula matricial contiene los operadores normales +, -, *, / y contiene las funciones normales, como la función SI(), pero se introducen pulsando la combinación de teclas CONTROL + SHIFT (también llamada MAYUSCULAS) + ENTER en lugar de usar la tecla ENTER solamente como es normal. Además, en vez de operar con celdas operan con rangos.

 

Pasos para crear una fórmula matricial de Tipo 1 multicelda

Los pasos para crear una fórmula matricial de Tipo 1 multicelda son:

• Seleccionar el rango donde queremos que vaya el resultado (de ahí que se llame multicelda, porque se necesita un rango).
• Poner la fórmula solamente en la primera celda de ese rango y acabar con CONTROL+SHIFT+ENTER. Como resultado todo el rango seleccionado se llena con el resultado de la fórmula cada celda individualmente.

 

Ejemplo:

Recuerda que estamos viendo las matrices de Tipo 1: rellenamos rangos con valores sin usar ninguna función de resumen. Empezaremos con los operadores básicos +, -, *, /.

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

 

 

 

Como ves, hemos rellenado el rango desde A1 hasta J10 con unos números. Haz lo siguiente:

Primero: vamos a rellenar el rango desde B12 hasta J20 con los valores que hay en el rango desde B2 hasta J10 pero con cada valor dividido por 10. Haz lo siguiente:

• Selecciona desde B12 hasta J20:

 

 

• No hagas clic en ningún sitio, ya que desharía la selección, lo que teclees quedará en la celda en blanco (B12). Teclea lo siguiente:

=B2:J10/10

• y acaba con CONTROL+MAYUSCULAS+ENTER. El resultado:
  

 

Mira cómo se hace:

 

Observa que cada celda del rango de B12:J20 contiene el mismo valor que cada celda del rango B2:J10 (la celda que está en la misma posición dentro del rango) pero dividida por 10. A esto se lo denomina una operación matricial.

Por ejemplo, la celda C13 se corresponde con C3, por tanto C13 muestra el resultado de C3/10. La celda G18 se corresponde con G8, por tanto G18 muestra el resultado de G8/10, y así todas para todas las celdas del rango B12:J20.

Si haces clic en B12 verás la siguiente fórmula en la barra de fórmulas:

 

 

Que es la fórmula que has tecleado pero entre llaves { }. Y, de hecho, la fórmula es la misma para cualquier celda del rango desde B12 hasta J20. Por ejemplo, colócate en G16 y verás que es la misma fórmula, no cambia nada:

 

 

Modificar una fórmula matricial

Siguiendo con el mismo ejemplo, si intentas borrar el contenido de G18 con la tecla SUPR, verás que Excel no te lo permitirá. Una matriz de Excel, o se borra toda completa o no se puede borrar. Para seleccionar la matriz completa usa, en Excel 2003, el menú Edición → Ir a → Especial → Matriz actual y Aceptar. Para Excel 2007+ (versión de Excel 2007 o superior) usa la ficha Inicio → Buscar y seleccionar → Ir a Especial → Matriz actual. Entonces, verás que queda seleccionada la matriz completa y ya puedes usar la tecla SUPR para borrar toda la matriz.

Por ahora no borres la matriz o usa "Deshacer" si la has borrado, ya que vamos a ver que, una vez creada una matriz, podemos cambiar la fórmula matricial sin necesidad de seleccionar otra vez el rango. Simplemente, cambiaremos la primera celda del rango. Por ejemplo, vamos a obtener en el rango B12 hasta J20 lo que hay desde B2 hasta J10 pero multiplicado por 3:

• Haz clic en B12 sin seleccionar nada
• Teclea =B2:J10*3 y acabar con CONTROL+MAYUSCULAS+ENTER.

El resultado:

 

 

Igual que antes, toda la matriz del rango B2:J10 queda rellenada con la misma fórmula. Esta fórmula es:

 

Pero el resultado se muestra distinto para cada celda individualmente, por ejemplo, en B12 estaría el resultado de B2*3, en H15 está H5*3, etc.

Observa que estamos todo el rato trabajando con rangos, no con celdas. Las dos fórmulas matriciales tecleadas hasta ahora han sido =B2:J10/10 y =B2:J10*3, es decir, un rango dividido por 10 y un rango multiplicado por 3. Gracias a que hemos introducido la fórmula con CONTROL+MAYUSCULAS+ENTER, Excel la ha tratado como una fórmula matricial.

 

En las fórmulas matriciales de Tipo 1 no hace falta el uso de referencias absolutas

Una de las ventajas que tiene el uso de fórmulas matriciales de Tipo 1 es que no hace falta el uso de $. Así por ejemplo si, en este ejercicio, el valor 3 lo ponemos en una celda externa, no hará falta poner $ en la fórmula. Para comprobar esto, haz lo siguiente:

• Pon un 3 en A12
• Haz clic en B12 sin seleccionar nada
• Teclea =B2:J10*A12 y acaba con CONTROL+MAYUSCULAS+ENTER

El resultado es el mismo que antes, pero observa que, si hubiéses usado fórmulas estándar (no matriciales), habrías tenido que poner $A$12 para obtener el resultado correcto. Además, puedes ir cambiando este 3 por otros números y verás cómo cambia la matriz.

Ve cambiando valores en B2:J10 y mira cómo va cambiando la matriz de B12:J20.

 

Es evidente que para que la fórmula matricial funcione correctamente, los rangos han de ser todos iguales. Si fuesen más pequeños, presentarían resultados parciales y si fuesen más grandes darían resultados inválidos, el error #N/A. Pero Excel no mostraría ningún mensaje de advertencia o algo similar.

 

 

Ejemplos con varios rangos

Ahora vamos a multiplicar dos rangos:

• Selecciona el rango X12:AF20
• Teclea la fórmula matricial =B2:J10*B12:J20 acabando correctamente

Con esto multiplicamos cada número del rango B2:J10 con el correspondiente número en la misma posición del rango B12:J20. El resultado debería ser:

 

 

y si haces clic en X12 o en cualquier lugar de la matriz resultante, verás la fórmula:

 

 

Si no te ha salido bien el resultado, haz clic aquí.

 

Podemos pues usar las fórmulas que queramos con diversos rangos, por ejemplo, una fórmula matricial perfectamente válida podría ser:

=(A5:A10*C5:C10)/(M10:M15-7)

Con la condición de que introduzcamos la fórmula con CONTROL+MAYUSCULAS+ENTER, Excel operará siempre con los rangos tratándolos como matrices, o sea, celda por celda y teniendo siempre en cuenta la posición de cada dato dentro de los rangos, es decir, operará individualmente con la primera celda de todos los rangos, luego con la segunda celda de todos los rangos, luego con la tercera celda de todos los rangos, y así sucesivamente hasta abarcar todo el rango.

Insistimos pues, debes tener cuidado de que todos los varios rangos usados en una misma fórmula matricial tengan el mismo tamaño.

 

Seleccionar los rangos con el mouse en vez de teclearlos a mano

Hasta ahora te hemos dicho que teclees a mano las fórmulas matriciales, pero es importante saber que los rangos de cualquier función matricial de cualquier Tipo pueden seleccionarse con el mouse en vez de tener que teclearlos a mano. Ello nos puede facilitar la introducción de las fórmulas matriciales. Para verlo, vamos a hacer lo mismo que en el apartado anterior pero seleccionando los rangos con el mouse. Sigue estos pasos:

• Pon un 3 en A12
• Selecciona el rango X12:AF20
• Pulsa la tecla SUPR para borrar la matriz que ya hay
• Teclea un signo igual: =
• Selecciona con el mouse el rango B2:J10
• Teclea un asterisco para multiplicar: *
• Selecciona con el mouse el rango B12:J20
• Acaba con la combinación de teclas CONTROL+MAYUSCULAS+ENTER

El resultado ha de ser el mismo que antes:

 

 

y si haces clic en X12 o en cualquier lugar de la matriz resultante, verás la fórmula:

 

 

Mira cómo se hace:

 

Ejercicio más práctico

Hasta ahora todo ha sido muy teórico. Veamos un ejemplo más práctico. Usaremos el modelo europeo en euros (€) y con el impuesto denominado IVA (VAT en inglés). En la hoja "Cálculos" del mismo archivo "MatricesTipo1.xls", veréis una factura con el código "FACTURA 001", en la cual hemos ido anotando en cada fila estos datos: el código del producto vendido, la cantidad de unidades vendidas, el precio por unidad, el descuento y el impuesto IVA:

 

 

Necesito lo siguiente: en la columna F quiero calcular la cantidad × precio, sin tener en cuenta el IVA ni el descuento. Lo podríamos hacer con una multiplicación normal, pero vamos a hacerlo matricialmente ya que estamos en el tema:

• Selecciona el rango F3:F24
• Teclea la fórmula matricial =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 ha de ser:

 

 

Excel ha calculado en la columna F el valor obtenido al multiplicar cada valor de la columna B por el correspondiente valor en la misma posición de la columna C.

Guarda esta hoja ya que más adelante la necesitaremos.

Ahora hazlo tú:

Calcula matricialmente en la columna H el total de cada fila, pero ya con descuento e IVA, la fórmula normal habría de ser:

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

El resultado debería ser:

 

 

Te damos una pista:

Clic aquí para ver

Te damos la solución:

Clic aquí para ver

Guarda esta hoja ya que más adelante la necesitaremos.

 

La función SI() usada matricialmente

La función SI() puede comportarse matricialmente si usamos rangos en vez de celdas y la introducimos con CONTROL+MAYUSCULAS+ENTER. Es importante recordar que Excel admite usar la función SI() matricialmente, pero no lo admite para Y(), ni O() ni NO(). Si hay que poner varias pruebas lógicas en un SI() usado matricialmente, no podremos usar ni Y() ni O(), y tendríamos que usar funciones SI() anidadas. Pero aquí no tocaremos el tema de las funciones SI() anidadas porque hay otras maneras más sencillas de usar varias pruebas lógicas, según veremos más adelante.

Veremos ahora un ejemplo: regresar a la hoja "MATRICES" del archivo "MatricesTipo1.xls". En esta hoja vamos a hacer que en el rango M2:U10 me ponga una X cuando la celda correspondiente al rango B2:J10 sea mayor o igual que 50:

• Selecciona desde M2 hasta U10.
• No cliques en ningún sitio y solamente teclea =SI(B2:J10>=50;"X";"") puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse.
• Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

 

 

Efectivamente, solamente aparece una X en la parte derecha, si las correspondientes celdas de la parte izquierda que están en la misma posición, son iguales o mayores que 50. En cada celda del rango M2:U10 aparece la fórmula:

 

 

Observa que la fórmula SI() usa el rango B2:J10 para hacer la comparación: B2:J10>=50 y el resultado en el rango M2:U10 se presenta calculado celda por celda.

 

En las fórmulas matriciales, Excel se encarga de ejecutar la misma fórmula individualmente en cada celda de los rangos utilizados.

 

Recuerda que las fórmulas matriciales cambian al cambiar los valores de la hoja, igual que las fórmulas normales.

 

Ve cambiando valores de B2:J10 para ver cómo cambian las X de M2:U10.

 

Mira cómo se hace:

 

Ejemplo con varios rangos

Otro ejemplo de la función SI() usada matricialmente podría ser el siguiente: queremos que cada valor del rango B2:J10 aparezca repetido en el rango en el rango M12:U20, pero solamente si es mayor o igual que 50 y, en caso contrario, que aparezca un 0:

• Selecciona desde M12 hasta U20.
• Teclea =SI(B2:J10>=50;B2:J10;0) puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse.
• Acaba con CONTROL+MAYUSCULAS+ENTER.

El resultado debería ser:

 

 

Observa que, nuevamente, hemos cumplido las normas de una fórmula matricial: cuando yo le he dicho a Excel que quiero que me devuelva la celda original si es mayor o igual que 50, no he puesto ninguna celda, he puesto nuevamente el rango de las celdas a devolver, es decir:

=SI(B2:J10>=50;B2:J10;0)

Excel ya se encarga de recorrer el rango, ejecutar la operación celda por celda y devolver la celda correspondiente dentro del rango.

Vamos a ver un ejemplo en el que se usan 3 rangos. En el rango M22:U30 vamos a repetir el número que hay en el rango B12:J20 si el correspondiente número de M12:U20 es cero. El resultado debería ser:

 

 

Haz lo siguiente:

• Selecciona el rango M22:U30.
• Teclea la fórmula =SI(M12:U20=0;B12:J20;0) puedes hacerlo tecleando los rangos a mano o seleccionando los rangos con el mouse.
• Y acaba con CONTROL+MAYUSCULAS+ENTER.

Fíjate que en este caso la fórmula usa un rango para comparar, pero devuelve los valores de otro rango distinto. En total hemos necesitado 3 rangos: los rangos M12:U20 y B12:J20 que aparecen en la fórmula pero también el rango M22:U30 que hemos usado para poner la fórmula.

 

Ejercicio más práctico de la función SI()

Ahora hazlo tú:

Vé a la hoja "Descuentos" del archivo "MatricesTipo1.xls". Se trata de, en la columna E, calcular el total para cada fila simplemente multiplicando la cantidad por el precio, pero cuando la cantidad sea mayor o igual que 300, haz un descuento del 5%.

El resultado debería ser:

 

 

Te damos una pista:

Clic aquí para ver

Te damos la solución:

Clic aquí para ver

 

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