Fórmulas matriciales Vamos a hablar de una de las funcionalidades de Excel más potentes i seguramente una de las más desconocidas: las fórmulas matriciales.La potencia de las fórmulas matriciales es que nos permiten analizar matrices de datos aplicando filtros complejos por columnas o filas. La fórmula más simple y parecida es el “SUMAR.SI” que actúa como una fórmula matricial con una sola condición. Una típica fórmula matricial es aplicar una serie de condiciones para obtener una suma final, pero se puede hacer con otras funciones como “PROMEDIO”, “MEDIA”, “MAX” o “MIN”.

Fórmula matricial con dos condiciones:

{=SUMA(SI(B7:B23="A4";SI(D7:D23="negro";E7:E23)))}Una fórmula matricial se marca poniéndola entre llaves. Esta se lee de la siguiente manera: suma los valores del rango E7:E23 que, en la misma fila, tengan A4 en la columna B i “negro” en la columna D. Se escribe la fórmula que queremos evaluar sin llaves y dentro ponemos condicionales simples (“SI”). Los condicionales y operaciones lógicas se pueden ir jerarquizando unas dentro de otras hasta hacer fórmulas extremadamente complejas.¡Atención! Una vez escrita la fórmula no se introduce con un simple [Enter] ya que no se evaluaría, tenemos que hacer [May.] + [Ctrl] + [Enter] y veremos como aparecen las llaves conteniendo la fórmula.Este tipo de fórmulas se debe usar con moderación ya que , cuando más compliquemos la fórmula, analicemos matrices muy grandes o tengamos muchas fórmulas matriciales en el mismo libro de Excel veremos como el rendimiento del programa disminuye mucho.Archivo de ejemplo y ejercicios (9kb).

Unidad 4.  Fórmulas y Funciones (VIII)Principio del formulario

Fórmulas Matriciales

Vamos a explicar primero el concepto de Matriz. Una matriz es un conjunto de datos organizados en filas y columnas, una hoja Excel tiene forma de una gran matriz porque está divida en filas y columnas.

Excel trabaja con matrices unidimensionales, es decir de una fila o de una columna, y matrices bidimensionales formadas por filas y

columnas. También existen matrices tridimensionales que están formadas por filas, columnas y profundidad, pero que Excel no entiende.

Un fórmula matricial es una fórmula que se aplica a todas las celdas de una matriz. Las fórmulas matriciales permiten un ahorro de trabajo.

Veamos pues cómo crear y utilizar las fórmulas matriciales. Y veámoslo como mejor se ven estas cosas, con un ejemplo.

 

Ejemplo: Tenemos una lista de la compra bastante extensa de la ferretería, y sabemos que normalmente se suelen comprar varias cantidades de los productos de la ferretería.

Para calcular el importe de cada producto podemos definir la fórmula para calcular el total del primer producto y copiarla al resto de filas de la columna E.

Pero un forma más inmediata es utilizando una fórmulas matricial:

- Seleccionamos las celdas donde van a ir los resultados desde la E2 a la E6.

- Escribimos en la zona de fórmulas la fórmula =Moneda(C2:C6*D2:D6) (Moneda es para que transforme el resultado en formato moneda para identificar mejor el tipo de dato del resultado).

- Y en vez de pulsar Intro, pulsamos Ctrl+Shift+Intro para que la coja como fórmula matricial. De un golpe calculará el total en cada fila.

Otra ventaja de las fórmulas matriciales es obtener sumatorios sin necesidad de tener los resultados parciales en una columna.

Podemos escribir en una celda la siguiente fórmula

=MONEDA(SUMA(C2:C6*D2:D6)) y pulsar Ctrl+Shift+Intro para que la coja como fórmula matricial. De este modo en una sola celda realizamos la suma total de los resultados parciales sin utilizar la columna E donde tenemos los totales parciales.

Otra aplicación de las fórmulas matriciales es la de crear una matriz de valores que vayamos a utilizar en varias hojas, y luego asignar esa matriz a un conjunto de celdas de forma inmediata.

Por ejemplo crear una matriz con los meses del año y guardarla como constante, para así escribir solo una vez los meses del año. Veamos cómo.

Para crear una matriz y guardarla como constante sólo tenemos que crear la constante como ya vimos.

Desde una celda cualquiera escogemos la opción del menú Insertar --> Nombre --> Definir...

Dentro del cuadro de diálogo Definir nombre en Nombres en el libro: escribimos Meses y en la parte de Se refiere a: escribimos la fórmula de los meses como vemos en la imagen. ={"Enero";"Febrero";....}.

De esta forma tenemos la constante Meses creada y podremos asignarla como forma matricial a cualquier conjunto de celdas.

 

 

Para seleccionar toda la matriz podemos hacerlo como hemos venido haciéndolo hasta el momento seleccionando las celdas una por una. Pero existe otra forma de hacerlo:

- Nos situamos en una celda cualquiera de la matriz a seleccionar.

- Pulsamos la tecla F5 y nos aparece el cuadro de dialogo Ir a, hacemos clic sobre el botón Especial... y seleccionamos la opción

Matriz actual y pulsamos sobre Aceptar. Se seleccionará toda la matriz.

 

 

FORMULAS MATRICIALES

 

INTRODUCCION:

Con las fórmulas matriciales se pueden hacer muchas cosas, es una herramienta de gran potencia,  en general estas fórmulas o funciones se usan para hacer 2 tipos de cosas.:

Ejecutar varias operaciónes y devolver un único valor en la celda donde se la introduce. Ejecutar varias operaciónes y devolver múltiples valores en distintas celdas. Las fórmulas matriciales actúan en 2 o mas rangos de valores, los que se denominan, argumentos matriciales, los cuales tienen la característica de tener el mismo número de filas y de columnas, por ejemplo, podrían actuar sobre los rangos A1:A12  y BI:B12.Una fórmula matricial se introduce de la misma forma que la fórmula común, la diferencia es que luego de introducirla hay que apretar las teclas Control+shift+ENTER, con lo que automáticamente es rodeada por llaves y es por eso que se las conoce como fórmulas CSE. Para una formula matricial multiplicar 2 argumentos matriciales, como A1:A12 *BI:B12. significa multiplicar las celdas A1*B1, A2*B2, A3*B3......A12*B12 si quiero sumar estos resultados parciales uso la formula matricial {SUMA(A1:A12*B1:B12)}, para aclarar los conceptos vamos a tener que hacer mas de un ejemplo, Empecemos por un ejemplo del tipo 1-.

        El dueño de una mueblería quiere aumentar la variedad de los productos que vende para lo que decide comprara, parte de los tradicionales, muebles de computación, para lo que cuenta con la siguiente planilla

 

y quiere saber  cuanto tiene que gastar. Decide tomar el camino corto y usa una simple fórmula matricial, veamos lo que hizo

se ve que introdujo la fórmula matricial

de esta forma hizo 3 pasos en uno. Los 3 pasos hubieran sido:

          1- Introducir la fórmula =D2*E2 en la celda F2.

          2- Arrastrar esta fórmula hasta la celda F7

          3- Ubicarnos en la celda F8 y pulsar el icono

o ubicarnos en la celda D9( por ejemplo ) e introducir la función

=SUMA(D2*E2;D3*E3;D4*E4;D5*E5;D6*E6;D7*E7)

se ve que ambas maneras, si bien dan el mismo resultado, son mucho mas tediosas

Se puede aprovechar este mismo ejemplo para mostrar como usar las fórmulas matriciales que devuelven múltiples valores y así explicamos todo el proceso.Usando la misma tabla que al principio vamos a obtener todos los productos parciales

          1º  seleccionamos la columna donde queremos que aparezcan los valores

.

         2º introducimos la fórmula, seleccionando los rangos D2:D7 y  E2:E7 y multiplicándolos

        3º apretamos las teclas Control+shift+ENTER

y obteniendo los productos parciales y por lo tanto múltiples resultados como se ve en el recuadro rojo.

Fórmulas matriciales en Excel

Las herramientas y métodos de esta planilla electrónica para el uso de matrices. Un ejemplo práctico de su aplicaciónLunes 29 de junio de 1998 | Publicado en edición impresa 

Tweet

Compartir

Las matrices constituyen una herramienta fundamental para los programadores de aplicaciones. Microsoft Excel dispone de las

fórmulas matriciales para aprovechar las facilidades que brinda su empleo.

Una aplicación práctica y simple de una fórmula matricial es la siguiente. Escriba en las celdas A1, A2, A3 y A4 los valores 4, 6, 8 y 10, respectivamente.

Ubíquese luego en B1 y proceda a agregar en ella la función RAIZ(), que calcula en forma automática la raíz cuadrada de un número, mediante el comando Insertar/Función... Se abre el cuadro de diálogo Pegar función. Escoja Matemáticas y trigonométricas y en la lista Nombre, busque RAIZ, selecciónelo y presione el botón Aceptar. En la siguiente ventana, escriba A1 en el cuadro de texto y finalice con un clic en Aceptar. Luego, copie esta fórmula en B2, B3 y B4. Se obtiene, en cada una de ellas los resultados deseados.

Hasta aquí, éste es el procedimiento normal para generar y utilizar fórmulas estándar. Sin embargo, esto mismo puede realizarse usando una fórmula matricial.

Pinte el rango C1:C4. Posteriormente, tal como se hizo en forma estándar, ejecute el comando Insertar/Función..., seleccione RAIZ en el primer cuadro de diálogo y, en el segundo, en la caja de texto Número, escriba ahora A1:A4. Esta vez, y para que Excel reconozca que estamos ingresando una fórmula matricial y la trate como tal, pulse la combinación de teclas Control (o Ctrl)+Mayúsculas+Enter. En forma automática se cierra el asistente de pegar funciones y obtenemos, en el rango C1:C4, los mismos resultados que logramos con el método estándar.

Si observa cómo quedó escrita la fórmula, en las cuatro celdas del rango encontrará {=RAIZ(A1:A4)}. La utilización de llaves en la escritura de una fórmula denuncia que se trata de una del tipo matricial.

Si usted intenta editar en forma individual la fórmula matricial de cada celda, Excel le impedirá efectuar cambios. Para poder realizarlos, se debe resaltar las cuatro celdas y hacer un clic en la barra de fórmulas, para editar la ecuación genérica {=RAIZ(A1:A4)}. A modo de ejemplo, cambie A4 por A5, y termine, presionando la combinación Control+Mayúsculas+Enter. Este atajo de teclas es el método para indicarle a Excel el ingreso o modificación de una fórmula matricial.

Para proteger la integridad de éstas, Excel tampoco permite mover las celdas en forma individual (sí todo el rango), borrar alguna de ellas (sólo el rango completo) o insertar nuevas.

Sin intermediarios

Una de las mayores utilidades de las fórmulas matriciales, como ya se dijo, es la posibilidad de ahorrar el uso de muchas fórmulas estándar intermedias para alcanzar un resultado.

Tenemos, por ejemplo, las cotizaciones semanales de cinco empresas en la Bolsa, cuyos nombres ubicamos en el rango A1: A5. Escriba en estas celdas: Empresa A, Empresa B, y así sucesivamente. De B1 a B5 incorpore valores numéricos que signifiquen su cotización anterior. Por caso, 45, 78, 91, 62 y 81. En el rango C1:C5 agregue la cotización actual: 56, 73, 93, 68 y 85.

Para trabajar con fórmulas estándar necesitamos un cuarto grupo de celdas, D1:D5, para calcular la diferencia entre ambas cotizaciones. Entonces, en D1 escriba la resta =C1-B1, que debemos copiar en el resto de las celdas del rango. Luego, en D7, calcule el promedio de estas variaciones, introduciendo la fórmula =PROMEDIO(D1:D5).

Utilizando matrices, en cambio, no necesitaríamos calcular la diferencia entre las cotizaciones; por lo tanto, prescindimos del rango D1:D5.

Para realizar esta tarea con matrices, basta con ubicarse en la celda C7, por ejemplo, y escribir (o insertar la función, como vimos en el ejemplo precedente) la fórmula {=PROMEDIO(C1:C5-B1:B5)}. Como puede observarse, esta fórmula matricial permite, en forma directa, restar rangos completos (tomados como matrices) y calcular su media de variación.

Por Gabriel Tomich

Cálculos matriciales

    El concepto de Matriz viene de los lenguajes de programación y de la necesidad de trabajar con varios elementos de forma rápida y cómoda. Podríamos decir que una matriz es una serie de elementos formando filas (matriz bi-dimensional) o filas y columnas (matriz tri-dimensional).

    La siguiente tabla representa una matriz bidimensional: 

1 2 3 4 5

...ahora una matriz tridimensional: 

1,1 1,2 1,3 1,4 1,5

2,1 2,2 2,3 2,4 2,5

3,1 3,2 3,3 3,4 3,4

    Observa por ejemplo el nombre del elemento 3,4 que significa que está en la posición de fila 3, columna 4. En Excel, podemos tener un grupo de celdas en forma de matriz y aplicar una fórmula determinada en ellas de forma que tendremos un ahorro del tiempo de escritura de fórmulas.

    En Excel, las fórmulas que hacen referencia a matrices se encierran entre corchetes {}. Hay que tener en cuenta al trabajar con matrices lo siguiente:

No se puede cambiar el contenido de las celdas que componen la matriz No se puede eliminar o mover celdas que componen la matriz No se puede insertar nuevas celdas en el rango que compone la matriz

1. Crea la siguiente hoja:

    Si te sitúas en la celda B4, observarás que hemos hecho una simple multiplicación para calcular el precio total de las unidades. Lo mismo pasa con las demás fórmulas.

    En vez de esto, podríamos haber combinado todos los cálculos posibles en uno solo utilizando una fórmula matricial.

    Una fórmula matricial se tiene que aceptar utilizando la combinación de teclas CTRL+MYSC+Intro y Excel colocará los corchetes automáticamente.

2. Borra las celdas adecuadas para que quede la hoja de la siguiente forma:

3. Sitúa el cursor en la celda B7 e introduce la fórmula:

=SUMA(B3:E3*B4:E4)

4. Acepta la fórmula usando la combinación de teclas adecuada.

Observa cómo hemos obtenido el mismo resultado tan sólo con introducir una fórmula.

    Observa la misma en la barra de fórmulas. Ahora hay que tener cuidado en editar celdas que pertenezcan a una matriz, ya que no se pueden efectuar operaciones

que afecten sólo a un rango de datos. Cuando editamos una matriz, editamos todo el rango como si de una sola celda se tratase.

  Constantes matriciales

    Al igual que en las fórmulas normales podemos incluir referencias a datos fijos o constantes, en las fórmulas matriciales también podemos incluir datos constantes. A estos datos se les llama constantes matriciales y se debe incluir un separador

de columnas (símbolo ;) y un separador de filas (símbolo \).

    Por ejemplo, para incluir una matriz como constante matricial:

1. 25 2. 18

    Debemos escribir: {30;25\31;18}

1. Escribe estas celdas en la hoja2 2. Selecciona el rango C1:D2

3. Escribe la fórmula: =A1:B2*{10;20\30;40} 4. Acepta la fórmula con la combinación de teclas adecuada.

 

   

Observa que Excel ha ido multiplicando los valores de la matriz por los números introducidos en la fórmula:

Cuando trabajamos por fórmulas matriciales, cada uno de los elementos de la misma, debe tener idéntico número de filas y columnas, porque de lo

contrario, Excel expandiría las fórmulas matriciales. Por ejemplo:

={1;2;3}*{2\3} se convertiría en ={1;2;3\1;2;3}*{2;2;2\3;3;3}  

5. Selecciona el rango C4:E5 6. Introduce la fórmula: =A4:B4+{2;5;0\3;9;5} y acéptala.

Observemos que Excel devuelve un mensaje de error diciendo que el rango seleccionado es diferente al de la matriz original.