Funciones Matriciales (Array Formulas) en Excel – Nota II

miércoles, abril 19, 2006

En una las primeras entradas que publiqué en este blog, di una explicación general sobre las fórmulas matriciales (array formulas) en Excel.
Esa entrada se limitaba a mostrar un aspecto del uso de las funciones matriciales. El objetivo de esta nota es ampliar la explicación sobre esta importante herramienta de Excel.
Una matriz es un conjunto ordenado de elementos. En términos de Excel, una matriz puede ser un rango contenido en una columna (o fila), que es una matriz unidimensional, o en un rango rectangular, lo que la convierte en una matriz bidimensional.
En el ejemplo de la explicación general sobre fórmulas matriciales, una matriz era el rango A2:A12 la otra el rango B2:B12. En ese ejemplo usamos la fórmula matricial para calcular un promedio ponderado. Este es uno de los usos posibles de las funciones matriciales, que llamaremos "unicelular", ya que le resultado de la fórmula aparece en una sola celda.
Las funciones matriciales de Excel también pueden usarse para obtener el resultado de un cálculo en varias celdas simultáneamente, lo que llamaremos fórmulas matriciales "multicelulares". Por ejemplo, podemos rescribir el ejemplo de la nota anterior de la siguiente manera:



Como pueden ver en el ejemplo (descargar el
form_matr_sp_01ejemplo de fórmulas y constantes matriciales aquí), la fórmula es la misma a lo largo del rango C6:C16, pero el resultado en cada celda es el producto de las celdas de la fila correspondiente.
Este tipo de fórmulas matriciales no parece tener ninguna ventaja sobre las fórmulas matriciales "unicelulares". Tal vez una de las ventajas sea que Excel no permite borrar parcialmente estas fórmulas, y por lo tanto es un buen método de proteger las fórmulas en las hojas de cálculo.

Un aspecto más interesante es la posibilidad de crear matrices constantes en Excel. Al crear una matriz de este tipo, los datos son almacenados en la memoria del computador, y no en rangos de la hoja de cálculo.
Para crear una matriz de constantes, escribimos los miembros de la matriz entre signos "{" y "}", separados por ";" (matriz horizontal) o por "\" (matriz vertical). Por ejemplo: {1;2;3;4;5} genera una matriz horizontal de cinco miembros



Aclaración: la ayuda de Excel en español sostiene que para generar una matriz orientada horizontalmente habrá que separar los miembros con comas; para generar una matriz vertical, la separación será hecha con punto y coma ";". En la versión de Excel de este ejemplo (XL 2002), los separadores al usar el lenguaje Español, son los indicados más arriba. Al abrir el mismo cuaderno con el lenguaje Inglés, Excel reemplaza los separadores por ";" y "\".

Una forma más cómoda de trabajar con matrices de constantes es asociarlas a un nombre, como he mostrado en mi entrada sobre uso de nombres en MS Excel. Por ejemplo, si creamos el nombre "Semana" que contenga una matriz con los días de la semana {"lunes","martes","miércoles","jueves", "viernes","sábado","domingo"}. Estas matrices pueden luego ser empleadas en fórmulas, tanto matriciales como fórmulas comunes.
Por ejemplo, si usamos el nombre "semana" que acabamos de definir en esta fórmula =INDEX(semana,2), obtenemos como resultado "martes".

Matrices, matrices nominadas y fórmulas matriciales, son elementos indispensables para convertirse en usuario avanzado de Excel.




Categorías: Formulas Matriciales_

Technorati Tags: ,

31 comments:

Mato 05 enero, 2007 04:16  

Tengo una consulta con las matrices.
Supongamos que tengo
a1: 10
a2: 1
a3: 1
a4: 1

Y en la una celda quiero obtener el resultado de "suma de abs(a1-a2)+abs(a1-a3)+abs(a1-a4)"

O sea, la suma de todas las discrepancias con respecto a A1.

Es posible hacerlo en forma matricial? Estuve intentando y no logré dar con la tecla.

Muchas gracias.

Jorge L. Dunkelman 05 enero, 2007 15:15  

Hola
la fórmula matricial que tienes que usar en tu ejemplo es
={SUMA(ABS(A1-(A2:A4)))}
que da como resultado 27.
Feliz año nuevo

GABRIEL,  17 septiembre, 2007 16:37  

Soy formador de Excel, creo tener bastante controlado el tema, pero se me escapa la utilidad exacta de las fórmulas matriciales, no comprendo en que casos deben aplicarse en sustitucion de otras formulas más simples y por qué razon deben aplicarse.
Gracias

Jorge L. Dunkelman 04 octubre, 2007 18:33  

Hola Gabriel, sin ser exaustivo, he tratado el tema en algunas notas.
Las fórmulas matriciales te permiten rrealizar operaciones en Excel que de otra manera serían muy engorrosas o imposibles de realizar.
En la red hay muchos recursos sobre el tema (puedes empezar buscando en los enlaces que aparecen en la columna izquierda del blog).

Diego 22 abril, 2008 17:42  

Hola Jorge, gracias por seguir culturizandome excelisticamente!.
Te cuento la duda de hoy...
Tengo una base de datos, con días y con horarios de visita segun cada supervisor... Y quiero armar una formula matricial que me devuelva la menor y la mayor hora en la que un supervisor trabajó durante cada día, para luego compararlas y saber la cantidad de horas de supervisión.
Te paso por mail el archivo para ser mas claro. En la primer hoja, esta la base. Y en la segunda, en el rango B3:C177 están las formulas. Lo raro es que para el Maximo me funciona a la perfeccion, pero no asi para el minimo. Ahi siempre me devuelve 0:00.
Espero puedas ayudarme.
Abrazo grande,
Diego,

Jorge L. Dunkelman 22 abril, 2008 22:58  

Hola Diego,
todas las consultas son bienvenidas, aún cuando no puedo responder a todas, pero tienes que mandarla directamente por mail.
El objetivo de los comentarios es reflejar cuestiones relacionadas con el contenido de la nota.

Anónimo,  09 junio, 2008 19:07  

hola jorge

Mi problema es el siguiente tengo una bd en una hoja excel en ella tengo una formula de bd la cual es
bdcontara en ella me cuenta los datos que le voy metiendo pero hay algunos que no me debe de contar por que son solo notas pero que quiero que se visualizen, espero haberme explicado
Saludos

Jorge L. Dunkelman 09 junio, 2008 21:01  

Si tienes que contar valores numéricos, tienes que usar BDCONTAR. Como estás usando BDCONTARA, también valores texto son tomados en cuenta.
Si tienes que contar valores alfanuméricos (y por eso usas BDCONTARA), puedes poner las notas como comentario y no como texto dentro de la celda.

Anónimo,  04 marzo, 2010 20:44  

Hola Jorge:

Lo primero darte la enhorabuena por el blog y el esfuerzo que seguramente te supone.

Me gustaría saber lo siguiente:

Quiero utilizar en una formula matricial el nombre de un rango definido previamente en Nombres, pero facilitándole el ese nombre a través de una celda.

Por ejemplo:

{=SUMA(SI(Meses="Enero";Datos1))}

teniendo Meses y Datos1 definidos como nombres con un rango en concreto. Por ejemplo:

Donde Meses es el rango D1:D12
Donde Datos1 es el rango E1:E12

Hasta aquí funciona sin problemas.

Imaginate ahora que en la celda A1 tengo escrito Datos1

Me gustaría que la formula se pudiese referenciar a esa celda:

{=SUMA(SI(Meses="Enero";A1))}

para que si tuviese diferentes rangos (nombres) definidos, por ejemplo, Datos2, Datos3, Datos3, ... pudiese arrastrar la fórmula, y sólo con cambiar el nombre en la celda la fórmula calculase los diferentes rangos matriciales.

¿Es posible?
Muchas gracias, Oscar

Jorge L. Dunkelman 05 marzo, 2010 00:12  

Hola Oscar,
si, tienes que usar la función INDIRECTO. Por ejemplo

{=SUMA(SI(Meses="Enero";INDIRECTO(A1)))}

Anónimo,  05 marzo, 2010 10:41  

Es cierto. No había caído. Había utilizado INDIRECTO pero nunca con una matricial.

Muchas gracias Jorge.

Una saludo, Oscar.

Anónimo,  31 julio, 2010 01:15  

Hola Jorge,
como soy aficionado al ajedrez quisiera saber si puedo utilizar Excel para anotar las jugadas con el sistema algebraico.
Saludos,
Marcos

Jorge L. Dunkelman 31 julio, 2010 11:00  

Supongo que si (tengo una idea muygeneral sobre el sistema algebraico para anotar jugadas de ajedrez). ¿Has intentado hacer una búsqueda en Google?

Anónimo,  17 agosto, 2010 12:03  

Hola Jorge:

Me gustaría sumar el acumulado hasta un determinado mes. Ejemplo

A B
MESES DATOS
Ene 5
Feb 8
Mar 7
Abril 3
Mayo 2
.....

Se hacerlo si en A2:A13 tengo fechas:
=SUMAR.SI(A2:A13;"<="&D1;B2:B13) siendo el valor de D1 una fecha

Pero si son texto el valor de los meses ¿Se puede hacer? en función de la posición del mes, por ejemplo, abril, 4, sumar el acumulado hasta ese mes.

Gracias por anticipado,
Oscar.

Jorge L. Dunkelman 17 agosto, 2010 17:39  

Una solución sería usar fechas con formato "mmmm". De esa manera en la celda A2, por ejemplo, se puede ingresar el valor 31/01/10, pero lo que se ve en la celda es "enero". De hecho, el valor de la celda es una fecha, no texto, y así podés usar SUMAR.SI con un criterio "<="

Anónimo,  02 marzo, 2011 18:18  

Hola Jorge

Hasta ahora no he encontrado en la red solución con a mi caso.
Resulta que tengo una hoja que contiene en una columna el listado de productos y en las subsecuentes sus características. Necesito encontrar una función o combinación de funciones, para que me genere en otra hoja una lista de productos según la característica solicitada. Cada producto debe asignarse en filas individuales. he utilizado la función buscar pero solo funciona para una sola busqueda.

Si se puede evitar el uso de programación o macros mejor.


agradecido de antemano
Selbor

Jose Felix Espino Napan 07 junio, 2012 16:11  

Hola Jorge:
Tengo que hacer en excel lo siguiente:
tener una tabla con un caracter en cada celda, luego en otra parte de la hoja ingresar un caracter; al hacer esto se debe resaltar todos los caracteres que coincidan con el caracter ingresado.
He probado con formato condicional, buscar, coincidir y no lo puedo lograr.
Qué funciones puedo utilizar para lograr esto.
agradecido de antemano,
Un abrazo
Jose

Jorge L. Dunkelman 08 junio, 2012 12:49  

¿Dónde se encuentran los caracteres que hay que resaltar? ¿En otras celdas de la hoja, en parte del contenido de la celdas,...?
De todas maneras, la forma de hacerlo es con formato condicional usando una combinación de NO, ESERROR y COINCIDIR.
Si el caracter existe en la tabla, COINCIDIR da el número de orden del valor en el vector. Por lo que ESERROR da FALSO y NO lo convierte en VERDADERO, lo que hace que el formato condicional se aplique. Si el caracter no aparece en la tabla, CONCIDIR da error, ESERROR da VERDADERO y NO lo convierte en FALSO, por lo que en este caso el formato condicional no se aplica.

Jose Felix Espino Napan 08 junio, 2012 18:27  

Hola Jorge; los caracteres a resaltar se encuentran en el rango C3:F9 y el caracter a ingresar es en la celda A1; apreciaria mucho me indiques la formula exacta para esta tabla.
Muchas gracias por tu tiempo dedicado a mi persona.
Un abrazo
José

Jorge L. Dunkelman 15 junio, 2012 17:34  

La fórmula es muy sencilla. Creo que el problema está en la forma de aplicarla.
Empieza por seleccionar el rango C3:C9; luego en formato condicional-fórmula usas =C3=$A$1
Hay que tomar en cuenta los símbolos $ y asegurarse que la celda C3 sea la celda activa.

Fernando,  18 junio, 2012 18:58  

Muchas gracias por las explicaciones tan claras, corrigiendo las bobadas que dice el manual y la ayuda de Excel! parece increíble que se puedan editar-publicar errores tan garrafales: me refiero al separador de columnas y filas en las matrices de constantes.

Jorge L. Dunkelman 20 junio, 2012 20:59  

No me arriesgaría a llamarlos bobadas o errores garrafales. En definitiva, Excel es tal vez el mejor producto de Microsoft. Como en todo ordende la vida, hay errores y no siempre se corrigen.

Dana. 27 julio, 2012 06:23  

Buenas noches,estoy intentando realizar una matriz en excel, pero no he descubierto como realizarla. Lo que deseo hacer es generar una matriz de números a partir de una ecuación que me permita generarlos progresivamente,es decir, quiero con la matriz ahorrarme el trabajo de tener que arrastrar la fórmula de una celda. Es posiblerealizarlo? necesito una función que mecambie de posición en lacelda, o algo así..

Jorge L. Dunkelman 27 julio, 2012 08:05  

Las fórmulas no pueden cambiar al estructura de la hoja. Una fórmula muestra en la celda que ocupa el resultado del cálculo, pero no puede cambiar el contenido de otra celda.
Podrías hacerlo con una macro.

Jose Felix Espino Napan 15 agosto, 2012 16:22  

Mas vale tarde que nunca: Muchas gracias Jorge por tu ayuda, aplique la formula que me indicaste y salió Ok. te felicito por brindar tus conocimientos a los demás, Un abrazo

David 03 diciembre, 2012 21:13  

Hola Jorge,
Después de repasar varios posts, no encuentro la solución a mi problema, a ver si pudieras ayudarme.
Necesito hacer una suma de una celda concreta en varias hojas, pero condicionada, a que otra celda de la misma hoja sea igual a algún valor de una lista de valores. Seria algo asi:

SUMAR.SI(hoja1:hoja40!a1;lista;hoja1:hoja40!b2;"")

donde lista seria una matriz de valores.
Muchas gracias de antemano.

Jorge L. Dunkelman 04 diciembre, 2012 17:51  

David,

tendrías que enviarme el archivo. Así, "en el aire", es un poco difícil hacerse una idea (¿que valores hay en "lista", cuántos?)

Mi primera impresión es que tendrías que organizar tus datos de otra manera, para facilitar el cálculo.

David 05 diciembre, 2012 20:05  

El archivo contiene datos confidenciales y no puedo enviarlo, pero a ver si te puedo aclarar la idea.
Hay 40 hojas con datos de ventas de 40 comerciales.
Hay otra hoja con 3 coordinadores de ventas que tienen asignados varios comerciales.
Cada semana se asignan comerciales diferentes a cada coordinador.
La formula es para que sume las celdas de los comerciales asignados a cada coordinador de ventas, teniendo una lista con los comerciales asignados a cada coordinador y que es variable.

SUMAR.SI este comercial pertenece a este coordinador.

No se si me he explicado bien. Pero de todas formas gracias por el interes.

Jorge L. Dunkelman 07 diciembre, 2012 16:05  

David, mi sugerencia es que cambien la organización de los datos. No hay ninguna necesidad funcional de separar los datos en 40 (!!) hojas y además otras 3 hojas para los coordinadores.
Lo mejor es organizar los datos en una única base de datos/matriz donde cada fila (registro) contiene en las columnas (campos) los datos relevantes. Por ejemplo, las columnas serían: fecha,comercial, coordinador, ventas.
Con esta tabla, todas las consolidaciones que se requieran (por fecha, por comercial, por coordinador-fecha, etc.) se hacen en un santiamén usando tablas dinámicas. Estas son infinitamente más eficientes que las fórmulas matriciales y que el uso de SUMAR.SI.

Anónimo,  30 agosto, 2013 17:34  

Estimado Jorge, me gustaría poner en una celda una formula matricial con macro. Que debería agregar al siguiente código para que se 'convierta en patricial' es decir cual sería el código que correspondería cuando manualmente ingresamos ctrl+sifht+enter
Este es el código Range("A1").FormulaLocal = "=MiFórmula"
Muchas gracias. Rubén

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP