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
ejemplo 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: Fórmulas Matriciales, Constantes Matriciales
Tengo una consulta con las matrices.
ResponderBorrarSupongamos 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.
Hola
ResponderBorrarla 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
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.
ResponderBorrarGracias
Hola Gabriel, sin ser exaustivo, he tratado el tema en algunas notas.
ResponderBorrarLas 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).
Hola Jorge, gracias por seguir culturizandome excelisticamente!.
ResponderBorrarTe 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,
Hola Diego,
ResponderBorrartodas 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.
hola jorge
ResponderBorrarMi 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
Si tienes que contar valores numéricos, tienes que usar BDCONTAR. Como estás usando BDCONTARA, también valores texto son tomados en cuenta.
ResponderBorrarSi 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.
Hola Jorge:
ResponderBorrarLo 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
Hola Oscar,
ResponderBorrarsi, tienes que usar la función INDIRECTO. Por ejemplo
{=SUMA(SI(Meses="Enero";INDIRECTO(A1)))}
Es cierto. No había caído. Había utilizado INDIRECTO pero nunca con una matricial.
ResponderBorrarMuchas gracias Jorge.
Una saludo, Oscar.
Hola Jorge,
ResponderBorrarcomo soy aficionado al ajedrez quisiera saber si puedo utilizar Excel para anotar las jugadas con el sistema algebraico.
Saludos,
Marcos
Supongo que si (tengo una idea muygeneral sobre el sistema algebraico para anotar jugadas de ajedrez). ¿Has intentado hacer una búsqueda en Google?
ResponderBorrarHola Jorge:
ResponderBorrarMe 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.
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 "<="
ResponderBorrarHola Jorge
ResponderBorrarHasta 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
Te sugiero filtro avanzado.
ResponderBorrarHola Jorge:
ResponderBorrarTengo 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
¿Dónde se encuentran los caracteres que hay que resaltar? ¿En otras celdas de la hoja, en parte del contenido de la celdas,...?
ResponderBorrarDe 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.
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.
ResponderBorrarMuchas gracias por tu tiempo dedicado a mi persona.
Un abrazo
José
La fórmula es muy sencilla. Creo que el problema está en la forma de aplicarla.
ResponderBorrarEmpieza 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.
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.
ResponderBorrarNo 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.
ResponderBorrarBuenas 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í..
ResponderBorrarLas 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.
ResponderBorrarPodrías hacerlo con una macro.
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
ResponderBorrarHola Jorge,
ResponderBorrarDespué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.
David,
ResponderBorrartendrí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.
El archivo contiene datos confidenciales y no puedo enviarlo, pero a ver si te puedo aclarar la idea.
ResponderBorrarHay 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.
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.
ResponderBorrarLo 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.
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
ResponderBorrarEste es el código Range("A1").FormulaLocal = "=MiFórmula"
Muchas gracias. Rubén
Acabo de probar tu fórmula con 10000 filas y funciona sin problemas. Supongo que las cuestión es que hay alguna celda con error en alguna de los dos rangos. ¿Qué error te dá?
ResponderBorrar