Funciones Matriciales 6d1p44

Las funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes {}. Veamos algunas aplicaciones.

Hoja 'Formula' Las fórmulas matriciales pueden abarcar varias celdas o una sola. En esta hoja vamos a ver la potencias de cálculo de este tipo de expresiones cuando arrojan el resultado en una sola celda.

as funciones matriciales en Excel permiten efectuar cálculos muy interesantes y proporcionan a la hoja de cálculo una enorme potencia. Las funciones matriciales, en general, abarcan varias celdas, aunque en algunos casos únicamente se centran en una única celda. Las fórmulas matriciales se identifican ya que se encuentran entre corchetes {}. Veamos algunas aplicaciones.

Hoja 'Formula' Las fórmulas matriciales pueden abarcar varias celdas o una sola. En esta hoja vamos a ver la potencias de cálculo de este tipo de expresiones cuando arrojan el resultado en una sola celda.

Para introducir una fórmula matricial se han de seguir tres pasos: 1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda. 2. Escribir la fórmula propiamente dicha 3. Para validar no se ha de pulsar INTRO, sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL+MAYUSCULAS+INTRO Al ver una función matricial en Excel la verá ente llaves {}. Estas llaves no las debe introducir usted al escribir la fórmula, las introducirá Excel al validar la función pulsando CONTROL+SHIFT+INTRO.

Para introducir una fórmula matricial se han de seguir tres pasos: 1. Seleccionar el rango en el que la fórmula matricial dejará sus valores. Si la fórmula matricial no abarca más de una celda simplemente no situaremos en esa celda. 2. Escribir la fórmula propiamente dicha 3. Para validar no se ha de pulsar INTRO, sino que lo que hemos de pulsar es la combinación de tres teclas: CONTROL+MAYUSCULAS+INTRO Al ver una función matricial en Excel la verá ente llaves {}. Estas llaves no las debe introducir usted al escribir la fórmula, las introducirá Excel al validar la función pulsando CONTROL+SHIFT+INTRO. Recuerde que la tecla de MAYUSCULAS también se la llama SHIFT.

Formulas matriciales. En múltiples ocasiones deseamos que al aplicar una función Excel, se obtenga, no un resultado, sino un conjunto de resultados como consecuencia de aplicar la función sobre un conjunto de valores y no para obtener un solo resultado. En el caso de querer calcular el número de fila para cada uno de los valores de una matriz, hemos utilizado la función FILA para la primera celda de la matriz, y de forma relativa, hemos copiado esa fórmula hacia la derecha y hacia abajo, obteniendo una matriz resultante con los números de fila correspondientes a cada uno de los valores de otra matriz origen. Este procedimiento es correcto pero existe otro muy interesante que queremos estudiar. DEFINICIÓN: Una matriz es un conjunto de datos organizados en filas y columnas constituyendo en Excel un rango rectangular de celdas. Excel puede trabajar con matrices unidimensionales (A1:A10), bidimensionales (A1:C10) y tridimensionales (Hoja1:Hoja5!A1:A10) Para resolver este tipo de situaciones, Excel dispone de un mecanismo que consiste en generar como resultado otra matriz (una sola pieza) en una sola acción: Desarrollar matricialmente la fórmula. Para desarrollar matricialmente una fórmula seguiremos los siguientes pasos: 1.- Escribiremos la fórmula para la primera de las celdas de resultado, en la que se desea obtener el primero de los valores de resultado, implicando desde ella a todas las celdas origen involucradas. En este caso aplicamos la función fila, no solo para la celda C4, sino para todas las celdas de la matriz (o conjunto de datos origen). Por lo tanto, la función inicial será: FILA(C4:E7) y en cada posición de la matriz obtenida aparece el número de SU fila.

... Por lo tanto, la función será: FILA(C4:E7) y en cada posición de la matriz obtenida aparece el número de SU fila. 2.- Seleccionamos "a punta de ratón" el rango de celdas resultado sobre las cuales deseamos desarrollar matricialmente esa primera fórmula llamémosla "semilla":

3.- Con el rango seleccionado, nos situamos con el cursor en cualquier posición de esa función en la barra de fórmulas:

4.- Pulsamos la combinación de teclas que convierte esa fórmula, en fórmula matricial: MAY + ENTER...

CTRL +

Como se aprecia, la fórmula queda encerrada entre llaves { } y además se completa el rango seleccionado en la hoja con sus respectivas fórmulas y con los resultados correspondientes:

Ahora, toda la matriz resultado se comporta como un bloque, actualizado según cambien los datos origen pero no modificable ni borrable ninguno de ellos por separado. De necesitar borrar o cambiar alguno de ellos sería necesario borrar la matriz completa ya que no es posible eliminar un término aislado de una matriz: Es un todo.

Tenemos ahora la matriz que, para cada valor de los de la matriz origen, contiene el número de SU fila. Por lo tanto, la función será: FILA(C4:E7) y en cada posición de la matriz obtenida aparece el número de SU fila. Ejemplo-1: Dadas unas cantidades y los precios de unos artículos, calcular el total del pedido utilizando una sola fórmula desarrollándola matricialmente. Sabemos que será una fórmula "al estilo" normal pero involucrando a todas las celdas de la matriz (uni, bi o tridimensional):

Seleccionaremos todo el rango de celdas de resultado (en las que queremos completar el resultado), en esta caso E4:E7 y desde la barra de fórmulas con el cursor situado pulsaremos CTRL +MAY + ENTER.

El resultado es:

Si hubiéramos deseado obtener el total del pedido, ahora sumaríamos los totales (total de totales en la celda E8) pero sería posible haber aplicado una sola fórmula para obtener ese mismo resultado para el caso de que solo nos interesase ese dato final. La fórmula resultante sería la siguiente: {=SUMA(C4:C7*D4:D7)} Por lo tanto, otra ventaja de las fórmulas matriciales es la de poder obtener resultados finales sin necesidad de obtener resultados parciales en una columna para poder resumir posteriormente. Por otro lado comentar que obtener resultados parciales en una columna para obtener en otro paso el resultado final solicitado tampoco es un mal procedimiento solo que por necesitar un paso más se considera menos técnico.

FORMUALS MATRICIALES

PROMEDIO CON UNA CONDICION Excel 2007 tiene una función que nos da el promedio de un conjunto de valores con una condición, tal función se llama PROMEDIO.SI, con las fórmulas matriciales podemos hacer lo mismo. Haremos un ejemplo con ambas opciones, aclarando que estando la función PROMEDIO.SI, siempre es preferible usar esta alternativa debido a que utiliza menos recursos de nuestro equipo. Otra historia es para Excel 2003, puesto que en esta versión la única alternativa son las fórmulas matriciales, siendo la razón que PROMEDIO.SI directamente no existe. Supongamos que una fábrica de autos, lanzó un nuevo modelo en el mes de Enero y quiere saber cual fue el promedio de ventas de los 3 primeros días del mes en cada una de las zonas en las que esta divide al país. Las zonas son: Norte, Sur, Este, Oeste y centro. Para lograr su objetivo se vuelcan los datos de las ventas de esos días en una tabla, con un sector a la derecha para los resultados

la fórmula, para la zona Norte es:

en la figura se hace la comparación con la función PROMEDIO.SI y se ve que coinciden

FORMULAS MATRICIALES EN EXCEL

Una fórmula matricial (array formula) realiza varios cálculos en uno o más conjuntos de valores, y devuelve uno o varios resultados. No es mucha la información que puedes encontrar en la Ayuda de Excel sobre esta poderosa herramienta, poco conocida y poco usada. Una matriz de datos puede ser unidimensional (horizontal o vertical), o tener varias dimensiones:

Si tienes una matriz de cantidades y otra de precios, puedes obtener el total de ingresos por el método básico (multiplicar precios por cantidades y sumarlo), por el método avanzado (usando la función SUMAPRODUCTO, que también trabaja con matrices) o por el método experto (usando una fórmula matricial). Observa la notación de la fórmula matricial {=SUMA(C4:D7*F4:G7)}. Podrían usarse paréntesis para que quede un poco más claro, como {=SUMA((C4:D7)*(F4:G7))}:

La notación de una fórmula matricial puede expresarse como FORMULA (MATRIZ1 (operador) MATRIZ2), su destino puede ser una celda o un conjunto de ellas, y se introduce pulsando Ctrl+May+Enter. 

Fórmula puede ser alguna de las muchas funciones de Excel: SUMA, PROMEDIO, etc



El operador es la operación que quieres realizar. Para establecer condiciones se utilizan los operadores AND y OR (es decir Y y O), expresados respectivamente con el producto (*) o la suma (+).



El destino de la fórmula matricial puede ser una sola celda que ofrezca un único resultado, o un conjunto de celdas, que ofrezca una matriz de resultados.

Por ejemplo, una empresa tiene su matriz de cantidades vendidas (C4:D7), su matriz de costes (F4:G7) y su matriz de márgenes porcentuales (I4:J7):

Quiere hacer dos ejercicios: por un lado A) generar su matriz de precios por producto (añadiendo el margen a sus costes), y por otro B) obtener los valores totales de ingresos, gastos y beneficios.

A) Matriz de precios. En este caso queremos obtener como resultado no una celda sino un conjunto de valores. Para ello, seleccionamos las celdas objetivo (L4:M7), escribimos la fórmula que añade los márgenes a la matriz de costes {(F4:G7)*(1+(I4:J7))}, y pulsamos Ctrl+May+Enter. Veremos que las ocho celdas del rango elegido se han resuelto con esa única fórmula:

Observa la fórmula usada para añadir los márgenes en porcentaje a los costes, puede resumirse como COSTE x (1+MARGEN %), pero su potencia es que ite matrices de valores como argumentos. Si intentaras modificar una sola celda del rango resultante, por ejemplo la celda M5, Excel no te dejará porque solo te permitirá modificar el rango (matriz) completo, no una parte de él. Sirve como protección de tus fórmulas matriciales, ya que tienes que modificar todo o nada:

B) Valores de ingresos, costes y resultados: En esta segunda parte, queremos que el resultado aparezca en celdas individuales, no en matrices. Para ello introducimos tres fórmulas matriciales en las celdas G10 (costes), M10 (ingresos) y M12 (beneficio):

Es cierto, es una forma complicada de hacer algo que podría hacerse más fácil. Pero solo es el principio. Vamos a otro ejemplo, dadas dos series de datos, por ejemplo los precios por artículo, y el número de artículos que hemos comprado. Queremos obtener el precio medio de los 56 artículos comprados. Generalmente multiplicaremos las dos columnas (cantidad x precio), sumaremos el resultado y lo dividiremos entre el número de artículos (método 1). Alternativamente, conocemos la función SUMAPRODUCTO y la aplicamos a nuestros datos (método 2). Pero también podemos usar las capacidades de Excel de trabajar con matrices de datos, mediante funciones matriciales (método 3):

Para la fórmula matricial las funciones y sus argumentos son las normales en Excel, pero recordando que trabajamos con matrices de datos. Además como he dicho antes debemos indicar claramente que se trata de una operación con matrices; para

eso, en vez de pulsar Enter cuando hemos acabado de escribirla, usaremos Control+Mayúsculas+Enter. Eso añadirá llaves de apertura y cierre { } a nuestra expresión, lo cual sirve para avisar que se trata de una fórmula matricial. Nota: Incluso cuando edites de nuevo tu fórmula matricial en la barra de fórmulas, tendrás que validarla pulsando Ctrl+May+Enter o dará error. Recuerda que las llaves de apertura y cierre no hay que escribirlas. Sigues pensando: “Vaya cosa, una forma complicada de hacer algo que puedo hacer más fácil con lo que ya sabía”. Tienes razón, los problemas de trabajar con fórmulas matriciales no compensan su aparente utilidad en este ejemplo. Pero era solo otro ejemplo sencillo, vamos a ver ahora la potencia de esta herramienta. 1) USO PARA DISCRIMINAR ERRORES EN SERIES DE DATOS: Tengo una serie de 7 datos, pero uno muestra error en la hoja de cálculo. Sabemos que no puede calcularse el promedio de una serie si alguno da error, pero puedo usar la fórmula matricial para obtener el promedio de los datos:

Vemos que el PROMEDIO normal no ofrece ningún resultado, mientras que usando la fórmula matricial no importa que haya elementos en error. Lo que la función condicional SI(ESERROR()) de la fórmula matricial indica es que si algún elemento de la matriz da error, debe tomarse el 0 (cero) como valor en el primer caso [promedio de siete elementos 13, 18, 25, 17, 0, 15, 16], y como elemento

vacío (no computable) en el segundo [promedio de seis elementos 13, 18, 25, 17, 15, 16]. No se me ocurre una forma más fácil de evitar celdas en error, así que después de todo parece que las matriciales tienen alguna utilidad ¿eh?. Lo mismo sirve para otras funciones habituales con las que obtendrías errores:

2) APLICACION A RANGOS DE DATOS, USANDO CONDICIONES: Una empresa paga un bonus a sus agentes, del 10% si sus márgenes superan la media de la empresa, y del 5% si están por debajo de la media. Esto puede calcularse de la forma tradicional, sin complicaciones, pero también con fórmulas matriciales: 

Se han definido tres rangos (VENTAS, MARGEN, MGMEDIO). Los dos primeros se usarán como matrices de datos, y el tercero como valor de comparación.



Para hacer que una fórmula matricial se aplique no a una solo celda, sino a una matriz de resultados (en este caso será el rango G3:G10, cada uno de los agentes), lo primero que hay que hacer es seleccionar la matriz de salida (G3:G10) y escribir la fórmula matricial en la primera celda del rango (G3). La fórmula elegida es una condición {=SI(MARGEN/VENTAS>MGMEDIO;MARGEN*10%;MARGEN*5%)} tal que para cada elemento de la matriz evalúe si el margen del agente es superior o inferior a la media, y en cada caso aplique el 10% o el 5% de bonus. Pulsamos Ctrl+May+Enter y el resto de celdas seleccionadas (G4 a G10) se rellenará con la misma fórmula matricial.

En este caso también es más fácil el método tradicional, y escribir una condición simple SI en la columna G para aplicar el porcentaje de bonus, pero solo quiero mostrar otra forma de hacerlo. 3) OTROS EJEMPLOS: ¿Aún no le ves la utilidad?. Supongamos que tienes una lista de miles de frases, y quieres saber cuál es la longitud en caracteres de la frase más larga. Habitualmente añadirías una columna a la derecha de esa lista de palabras, aplicarías la función =LARGO(celda) a cada una de las frases, y luego buscarías el mayor valor de esta nueva columna. Pues con una función matricial te bastaría con {=MAX(LARGO(rango)} para obtener el mismo resultado. Vamos a otro ejemplo. Dicen que las fórmulas matriciales deben emplearse sobre todo para evaluar (contar y sumar) celdas basándose en criterios múltiples. Una empresa vende tres productos y tiene tres vendedores. Cada día revisan el listado de ventas del día anterior, y quieren hacer una tabla resumen de unidades vendidas por producto y vendedor: 

Opción 1: hacen una tabla dinámica y se olvidan de preocupaciones.



Opción 2: son maestros de Excel, han leído este post y deciden aplicar fórmulas matriciales.

En este caso usaremos tres rangos de datos (PROD, VEND, UDS) y una única celda como matriz de salida (Q16). Luego llenaremos hacia abajo y hacia la derecha para completar la tabla. Nota: La fórmula SUMA((PROD=P16)*(VEND=Q15)*(UDS)) ofrecerá la suma de unidades que cumplen las condiciones de Producto y Vendedor establecidas.

El siguiente ejemplo lo he tomado libremente de da TAB.IS.ON, una web muy buena sobre Excel. Primero se obtienen mediante fórmulas matriciales los valores máximos y mínimos de ventas de acuerdo a ciertos criterios (por producto y región). Y luego se riza el rizo al obtener el Producto y la Región correspondientes a los valores de ventas máximas y mínimas respectivamente. La fórmula usada es fantástica:



Observa el uso de matrices en la función SUMAR.SI de las celdas F3 y F9, en modo que sus tres argumentos, tanto el rango de comparación, como el criterio, así como el rango de suma, son matrices.



Las fórmulas más largas son geniales. Lo que hace primero es comprobar SI la SUMA de ventas de un determinado Producto es igual al valor máximo que puede tener; si el resultado es cierto, devuelve el número de su fila. Usando la función MAX se halla la FILA donde el nombre del Producto aparece por última vez, y luego la función DESREF nos dará el nombre del Producto correspondiente a esa fila.

4) OTRAS OPERACIONES UTILES CON FORMULAS MATRICIALES: 4.1) Suma de resultados otras funciones Excel aplicadas sobre matrices: Por ejemplo sumas de rangos redondeados, de valores absolutos, etc.

4.3) Convertir todo una matriz de números en una nueva matriz de valores absolutos: Para ello elegimos como rango de salida una matriz del mismo tamaño que el rango origen, y escribimos la fórmula matricial {ABS(rango)}. Es un método mucho más rápido que aplicar condiciones.

4.4) Sumas o Recuentos basados en múltiples condiciones: Observa la última fórmula, y cómo obtiene el recuento final (también la he tomado de da TAB.IS.ON).

4.5) Transformaciones complejas de matrices de datos, basadas en múltiples condiciones:

Puedes anidar sucesivas condiciones SI para tratar cada valor de la matriz origen según reglas especiales. Recuerda que el asterisco (*) equivale al operador AND, es decir, exige que se cumplan ambas condiciones. Puedes asimismo usar el operador OR, con la suma (+).

4.6) Contar ocurrencias de una cadena de texto en listas de palabras, evaluar características:

4.7) Sumar una cadena de números a los que se ha dado formato de texto: Todos nos hemos encontrado con números que venían formateados como texto, y para los que las funciones normales no funcionarán hasta no transformar en números reales esos “falsos números”. Pues bien, con una fórmula matricial usando la función VALUE, puedes hacer estas operaciones sin ninguna otra transformación.

Esto no acaba aquí, seguiré poniendo ejemplos de esta poderosa herramienta de fórmulas matriciales.