Funciones Matriciales en Excel

De Gestor de Publicaciones FAUSAC

ESTA PÁGINA ESTÁ BAJO REVISIÓN DEBIDO A PROBLEMAS DE CITAS Y REDACCIÓN

Contenido

¿Qué es una función matricial?

El término matriz se refiere a una colección de datos que se encuentra en una columna o fila de excel. Una fórmula de matriz permitirá realizar cálculos sobre varios de esos elementos pertenecientes a esa matriz. (González 2015)

Una función matricial es aquella que no trabaja con un solo dato, sino que trabaja en conjunto con una serie de datos al mismo tiempo. Las funciones matriciales son de importancia para realizar algunas operaciones utilizando fórmulas normales lo cual permite realizar cálculos muy interesantes y proporcionan a la hoja de calculo una enorme potencia. Una función matricial puede ejecutar varias operaciones y devolver un único resultado o varios resultados y actúan en dos o mas conjuntos denominados argumentos matriciales y cada argumento tiene el mismo numero de filas y columnas

Las funciones matriciales generalmente abarcan varias celdas, aunque en algunos casos se centran en una celda, una fórmula matricial se identifica por que se encuentran encerradas en corchetes {}. Para utilizar las funciones se ingresa la instrucción que se necesite y para validarla no se utiliza ENTER sino que se usa la combinación de tres teclas: CTRL + SHIFT + ENTER. Con esto se indica que se usarán funciones matriciales.

Las funciones matriciales a diferencia de las funciones tradicionales, pueden hacer varios cálculos a la vez y pueden proporcionarnos datos únicos o múltiples, otra ventaja de las funciones matriciales es que podemos evaluar varias condiciones a la vez, por otro lado con las funciones tradicionales solo podemos evaluar una condición a la vez.

Concepto de fórmula matricial en Excel

Una Fórmula matricial en Excel es aquella fórmula que trabaja con matrices de datos. Las fórmulas matriciales no trabajan con un solo valor sino con una serie de datos. Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcional a la hoja de cálculo una enorme potencia. Una fórmula matricial puede ejecutar varias operaciones y devolver un único resultado o varios resultados. Las fórmulas matriciales actúan en dos o más conjuntos de valores denominados argumentos matriciales. Cada argumento matricial posee el mismo número de filas y columnas. 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 operaciones y devolver un único valor en la celda donde se la introduce. • Ejecutar varias operaciones 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.

PPP.png


Las fórmulas matriciales se crean del mismo modo que las demás fórmulas , excepto que se deben presionar la combinación de las teclas: CTRL(Control)+SHIFT(Mayús)+INTRO(Entrar) Así se añaden un conjunto de corchetes a la fórmula {} que la identifican como matricial. Por ejemplo: en el siguiente caso se calcula el valor total de una matriz de precios de cotización y acciones sin utilizar sin utilizar una fila de celdas para calcular y mostrar los valores individuales de cada cotización.

Para generar la fórmula matricial es sencillo:


1. Situados en la celda deseada escribimos la fórmula deseada, por ejemplo SUMA(B3:C3;B4:C4)


2. Se convierte en matricial con la combinación de las siguientes teclas de forma simultanea: CTRL+SHIFT+INTRO


De esta forma cuando se escribe la fórmula ={SUMA(B3:C3;B4:C4)} como fórmula matricial, multiplica las acciones y el precio correspondiente a cada cotización y luego suma los resultados de estos cálculos. Este tipo de fórmulas deberá utilizarse cuando no existe alguna otra fórmula en excel que pudiéramos utilizar, la razón de esto es que las fórmulas matriciales consumen altos recursos de nuestro ordenador, al usarlas con moderación estas fórmulas ayudan cuando se desea gestionar y analizar información. (González 2015)

Tipos de fórmulas matriciales

Las formulas se pueden catalogar en dos tipos:

  • Fórmulas que trabajan con una serie de datos, pero dan como resultado únicamente un solo valor.
  • Fórmulas que trabajan con una serie de datos, pero el resultado es colocado en 2 celdas o mas.


Fórmulas matriciales combinado con otras funciones usuales

Una de las grandes ventajas de las formulas matriciales es que no sólo permite hacer sumas complejas sino que admite otros tipos de funciones como:


  • mínimo
  • máximo
  • contar
  • suma
  • media
  • Promedio.SI : esta formula matricial da el promedio de un conjunto de valores utilizando una condición.
  • buscar : Función que permite buscar un valor dado de una determinada celda en un rango especifico de celdas, la cual retorna el contenido en el número de columnas a su derecha. Cuando se trata de una matriz, es el rango de la tabla que contiene el dato a buscar.
  • frecuencia : Para datos se utiliza una matriz de una referencia a un conjunto de valores se desea contar. Si los datos no contiene ningún valor, la Frecuencia devuelve una matriz de ceros, sin embargo si son grupos este debe utilizar argumentos para agrupar valores, pero sis este no contiene ningún valor, la Frecuencia devuelve el número de elementos contenidos en datos.

Estas son las típicas fórmulas matriciales de Excel, las cuales consisten en aplicar una serie de condiciones para obtener una suma final, pero se puede hacer con otras funciones con las que cuenta Excel.

Fórmulas matriciales con condicionales y operadores básicos

Las condiciones utilizadas comúnmente al emplear formulas matriciales son:

mayor que : >
menor que : <
igual que : <=>
menor o igual que : <=
diferente que : <>

Operadores más utilizados

* Operador Y
+ Operador o
{} Como matriz de condiciones "O" (como separador de columnas y filas)

Fórmula matricial con dos condiciones: Se coloca los corchetes afuera de la formula, para indicar los condicional de separador de columnas, para indicar que existen dos condicionales en este ejemplo se separa con punto y coma, utilizando la función SI ( )

{=SUMA(SI(B7:B23="A4";SI(D7:D23="rojo";E7:E23)))}

La anterior fórmula matricial es una típica formula que usa condicionales. La cual se lee de la siguiente manera: Suma los valores del rango E7:E23 que, en la misma fila, tenga A4 en la columna B i "rojo" en la columna D. Se escribe la fórmula pues se quiere evaluar sin llaves y dense coloca las condicionales simples ("SI"). Las condicionales y operaciones lógicas se pueden jerarquizar, hasta lograr fórmulas complejas. Por ésta razón son importantes los condicionales.

Sintaxis de Funciones Matriciales

Definición de las funciones matriciales en Excel:

  • MDETERM (Matriz): esta función matricial devuelve la determinante de una matriz cuadrada. En donde la misma se puede clasificar por rango de celdas, ejemplo: A4:C6. o como una contaste (1,2,3,4,5,6,7).
  • MINVERSA (Matriz): este tipo de matriz devuelve la inversa de cualquier matriz dada.
  • SUMA (B;C): Devuelve una matriz entre B y C.
  • MMULT (B;C): Devuelve el producto de la matriz entre B y C.
  • TRANSPONER (A): Devuelve una matriz transpuesta en A.
  • SUMAPRODUCTO (A; B; C;...): Este tipo de función multiplica los componentes que corresponden de hasta 30 matrices con una dimensión igual devolviendo así la suma de tales productos.
  • SUMAX2MENOSY2 (A,B): Regresa lo que es el sumatoria de la diferencia de cuadrados entre valores correspondientes de las matrices A y B.
  • SUMAX2MASY2 (A,B): Este tipo de función regresa lo que es la sumatoria de la suma de cuadrados entre los valores de las matrices A y B.
  • SUMAXMENOSY2 (A,B): Regresa lo que es el sumatoria de los cuadrados en la diferencia entre los valores de las matrices correspondientes (A,B).
  • FRECUENCIA (A,B): Regresa el número de un conjunto de datos, con mayor repetición
  • PROMEDIO.SI : combina la fórmula promedio y la SI, la cual da el promedio de un conjunto de datos.

Ventajas que ofrecen las funciones matriciales

Coherencia: Se hace clic en cualquiera de las celdas desde C10 hacia abajo, verá la misma fórmula. Esa coherencia garantiza una mayor precisión. No es posible sobre escribir un componente de una fórmula de matriz de varias celdas. Por ejemplo, haga clic en la celda C10 y presionar SUPR. Tendrá que seleccionar todo el rango de celdas de(C10 a E12) y modificar la fórmula de la matriz completa o dejar la matriz como está. Como medida de seguridad adicional, tiene que presionar CTRL + MAYÚS + ENTRAR para confirmar la modificación de la fórmula. (Office Support, 2015)

  1. Permiten hacer sumatorias complejas,además permite realizar otros tipos de operaciones como máximo, contar,mínimo, k esimo menor o mayor, todas las medianas, entre otras funciones que posee Excel. '(Fuente:Elaboración propia)

Las fórmulas matriciales combinado con otras funciones usuales

Definición del problema 1 a resolver

El dueño de una mueblería desea aumentar la variedad de los productos que vende,decide comprar parte de los tradicionales, muebles de computación, para lo que cuenta con la siguiente planilla. Necesita saber cuanto tiene que gastar en dinero y usa una simple fórmula matricial.


Sin título 1.jpg
                                                                                    Fuente: Elaboración propia


Sintaxis

  • La siguiente tabla ejemplifica el uso de la formula matricial.


Imagen 2.jpg
                                                                                     Fuente: Elaboración propia


  • Se observa que se introduce la siguiente formula matricial en Excel.


Formula matricial.jpg
                                                                                     Fuente: Elaboración propia


  • De esta manera se realizan 3 pasos en uno, para realizar más eficientemente y en menos tiempo el trabajo extenso.
  1. Introducir la formula =D2*E2 en la celda F2
  2. Arrastrar la formula hasta la celda F7
  3. Ubicarnos en la celda F8 y pulsar el icono de sumatoria o ubicarnos en la celda D9 (por ejemplo) e introducir la siguiente función:


Funcion.jpg
                                                                                      Fuente: Elaboración propia


Imagen 5.png
                                                                                      Fuente: Elaboración propia 


  • Al realizar de diversas formas la operación, se obtiene el mismo resultado, pero siguiendo los pasos uno por uno se vuelve una manera mas tediosa.

Se puede aprovechar este mismo ejemplo para mostrar el uso de las formulas matriciales que devuelven múltiples valores y así explicaremos todo el proceso a utilizar: Usando la misma tabla que al inicio y utilizando las formulas matriciales se obtendrán los productos parciales.


Pasos a realizar para formulas matriciales:


1. Seleccionar columna donde se desea colocar los valores.


Imagen 6.jpg
                                                                                       Fuente: Elaboración propia


2. En la barra de formulas introducir la formula obtenida mediante la selección de los rangos D2:D7 Y E2:E7 los cuales se multiplicaran entre si.


Imagen 7.jpg
                                                                                       Fuente: Elaboración propia


3. Por ultimo, presionar las teclas Control+ shift+ ENTER o Control+ Bloq Mayus+ ENTER


Imagen 8.jpg
                                                                                       Fuente: Elaboración propia     
                         
  • Se obtienen los productos parciales y por lo tanto múltiples resultados como se observa en el recuadro rojo. Pese a que las formulas matriciales son muy potentes y nos permiten analizar tablas las cuales tiene condiciones muy complejas consumiendo muchos recursos de la computadora, su uso debe hacerse siempre que no contenga una formula nativa que resuelva previamente el problema.

Ayuda en vídeo sobre funciones matriciales

En los 4 vídeos que se muestran a continuación se ejemplificará de forma didáctica el tema funciones matriciales, resolverá dudas posibles de la forma correcta de realizar cada operación.


1. Como usar fórmulas matriciales en Excel

2. Formulas Matriciales

3. Funciones Matriciales

4. Funciones Matriciales,Funcion SUMAPRODUCT

Definición del problema 2 a resolver

La formula matricial afecta a un rango de celdas en lugar de una sola celda.


Para el siguiente ejemplo se tiene los siguientes datos, en los cuales se obtendrán las unidades vendidas de los siguientes productos.


Sintaxis

Para obtener los valores que se necesitan saber, se realiza de la siguiente forma:


Sin título.png
                                                                                         Fuente: Elaboración propia                                   


  • Si se quiere hacer uso de una función matricial se pueden obtener múltiples valores en pocos pasos, como se vera en el siguiente ejemplo:


Paso 1.

Se seleccionan las celdas que se necesitan y se realiza la operación solicitada, en este caso una multiplicación.


Sin título2.png
                                                                                         Fuente: Elaboración propia


Paso 2.

Se presionan las teclas (Shift+ Alt+ Enter) para indicar que es una formula matricial, por lo que al presionar estas teclas se encerrara la formula entre llaves obteniendo así los resultados.


Sin título5.png
                                                                                          Fuente: Elaboración propia


  • De esta forma también se pueden obtener resultados de un valor en especifico, como se observa en el siguiente ejemplo:


Paso 1.

En este caso se utilizara la función INDICE en el cual se selecciona la matriz que serian todos los datos de la tabla, la fila donde se encuentran los valores y finalmente la columna donde se encuentran los datos que se desean obtener.


Sin título3.png
                                                                                          Fuente: Elaboración propia


Paso 2.

Se obtiene el producto deseado que en este caso seria Banano.


Sin título4.png
                                                                                          Fuente: Elaboración propia

Definición del problema 3 a resolver

En el siguiente ejercicio se necesita saber el promedio de unidades vendidas de un nuevo producto cosmético en las 5 diferentes zonas del país.


Sintaxis

Paso 1.

se tiene la base de datos las ventas realizadas en cada una de las zonas.


Ima 1.png
                                                                                          Fuente: Elaboración propia  


Paso 2.

se procede a introducir la formula. =PROMEDIO(D5:D9)


Imag2.png
                                                                                          Fuente: Elaboración propia 


Paso 3.

Se procede a presionar la tecla Enter para obtener el resultado y determinar las zonas que están por debajo del promedio de ventas.


Imagen 3.png
                                                                                          Fuente: Elaboración propia


Definición del Problema 4 a resolver

Fórmulas que trabajan con una serie de datos, pero el resultado es colocado en 2 celdas o mas: Es decir, devuelve una matriz de datos. Al emplear matrices se ahorra tiempo y facilita el análisis de los datos.


Sintaxis

Conocer cuál será el resultado de multiplicar matriz A por matriz B dejando la respuesta en otra matriz denominada: Matriz C.

Para ello se tiene las siguientes matrices A y B respectivamente.


MM1.jpg
                                                                                           Fuente: Elaboración propia  


Solución al Problema 4

  • Previo a realizar la operación, se debe seleccionar un lugar de salida de datos con el mismo ancho y largo de las matrices. En este caso, el lugar será iniciando en la celda C10 hasta la celda E12.


MM2.jpg
                                                                                           Fuente: Elaboración propia  
  • Posteriormente se inserta el operador de cálculo el signo (=) o (+) y se selecciona toda la matriz A por la matriz B


MM3.jpg
                                                                                            Fuente: Elaboración propia  


  • Para su validación como una función matricial se presionan el conjunto de teclas CTRL + SHIFT + ENTER


MM4.jpg
                                                                                            Fuente: Elaboración propia

Referencias bibliográficas


Para Mayor Información Consultar El Siguiente Material Audio Visual:


Herramientas personales