Fórmulas matriciales i II

Ya había tratado antes el tema de las fórmulas matriciales, pero estaba contento de cómo lo había hecho. Además la sintaxis que expliqué no era la más comprensible visualmente, por lo tanto esta entrada es una aclaración y extensión de la primera.

Repasemos los aspectos clave:

  • Las fórmulas matriciales son muy potentes y permiten analizar tablas con condiciones complejas.
  • Las fórmulas matriciales consumen muchos recursos del ordenador, se deben usar con moderación y cuando no exista una fórmula nativa de Excel que sirva.
  • No se puede evaluar una columna entera tipo "A:B", se debe definir un rango con un límite definido de filas como "A1:B3000".
  • Las fórmulas matriciales pueden evaluar matrices verticales (fila a fila) o horizontales (columna a columna).
  • Para que una fórmula se evalúe de forma matricial se debe introducir apretando "Ctrl"+"Mayúsculas"+"Enter".
Bien, entremos un poco en detalle de como trabaja Excel con la fórmula. Tendremos una tabla que puede ser tan grande como queramos y le pondremos unas condiciones sobre algunas de las columnas o filas. Cada vez que introducimos una condición el Excel se hace un mapa de la columna tipo (verdadero, falso, verdadero, falso, falso, ...). Si queremos poner condiciones adicionales, Excel irá superponiendo estas matrices de condiciones.
Ejemplo: (verdadero, falso, falso, verdadero, falso) y (falso, verdadero, falso, verdadero, falso) = (falso, falso, falso, verdadero, falso)
Se debe resaltar que el orden de las condiciones es importante, ya que en una condición tipo "y" cada condición actúa como filtro de las siguientes.
Ejemplo: (verdadero, falso, falso, verdadero, falso) o (falso, verdadero, falso, verdadero, falso) = (verdadero, verdadero, falso, verdadero, falso)
Una condición tipo "o" hace que Excel realice una suma de las matrices.

Normalmente pondremos diversas condiciones comparándolas con una referencia:
  • > mayor que
  • <>
  • = igual que
  • >= mayor o igual que
  • <= menor o igual que
  • <> diferente que
Los operadores que podemos usar son:
  • * operador "y"
  • + operador "o"
  • { } como matriz de condiciones "o" (separador ; para filas y \ para columnas)
La sintaxis es la siguiente (recordad que las llaves las pone Excel):
{=FÓRMULA(SI(((condición 1a)+(condición 1b))*(condición 2);(rango valores)))}

{=FÓRMULA(SI((condición={1a; 1b})*(condición 2);(rango valores)))}
Se lee: si se cumple la condición 1a o la condición 1b y también se cumple la condición 2 entonces aplica la fórmula sobre el rango de valores. Las dos fórmulas son iguales si los datos están en columnas y se evalúan fila a fila.
Ejemplo real de fórmula:
{=SUMA(SI((B5:F5="b")*(B6:F6={"azul"\"rojo"});B8:F8))}
La ventaja de las fórmulas matriciales también reside en que no sólo permiten hacer sumas complejas sino que admiten otros tipos de funciones como promedio, todas las medianas, mínimo, máximo, contar, k ésimo mayor o menor, percentil y aún debo dejarme alguna.

Existe un caso particular para calcular sumas matriciales en el que nos podemos ahorrar el condicional:
{=SUMA((condición={1a; 1b})*(condición 2)*(rango de valores))}

{=SUMA((condición={1a; 1b})*(condición 2)*(rango de valores<>0))}
En la primera formula sumamos os valores del rango de valores y en la segunda contamos los valores diferentes de 0.

Fichero con ejemplos: Fórmulas matriciales II
----------------------------------------------

8 comentarios:

Anónimo dijo...

Hola.
Creo que en la primer frase, en lugar de decir:

...pero estaba contento...

querías decir:

...pero NO estaba contento...

Slds.

Carlos Arana Matus dijo...

Hola!
Prdona la molestia, traigo un analisis que quiero hacer con el excel que ninguno de mis conocidos ha podido resolver, por favor mira la siguiente tabla:

135 AA
135 BB
135 CC
135 DD
135 EE
200 FF
210 GG
2100 HH
2100 II

Lo que busco lograr es una formula que busque el dato de la primera columna y me entregue los datos de la segunda columna. Sin embargo, podrás darte cuenta que en algunos casos se repite varias veces el dato de la primera columna. En los casos donde un dato aparece mas de una vez en la matriz, busco que excel me regrese en una sola celda todas las coincidencias, separadas por una coma, por ejemplo, para el dato 135 debería resultarme (en un solo renglon):

AA,BB,CC,DD,EE

Para el dato 200 el resultado sería FF

Para el dato 2100 el resultado sería HH,II

135 AA AA,BB,CC,DD,EE
135 BB AA,BB,CC,DD,EE
135 CC AA,BB,CC,DD,EE
135 DD AA,BB,CC,DD,EE
135 EE AA,BB,CC,DD,EE
200 FF FF
210 GG GG
2100 HH HH,II
2100 II HH,II

Yo pienso que tal vez podría lograrse con una fórmula matricial o con una macro, pero hasta donde me alcanza el conocimiento no he podido lograrlo.

Alguna idea?

Saludos.

Carlos Arana Matus dijo...

mmmmm ---
la tabla inicial es

135| AA
135| BB
135| CC
135| DD
135| EE
200| FF
210| GG
2100| HH
2100| II

La ultima tabla es
135| AA| AA,BB,CC,DD,EE
135| BB| AA,BB,CC,DD,EE
135| CC| AA,BB,CC,DD,EE
135| DD| AA,BB,CC,DD,EE
135| EE| AA,BB,CC,DD,EE
200| FF| FF
210| GG| GG
2100| HH| HH,II
2100| II| HH,II

Anónimo dijo...

Hola, muy buen blog. Felicitaciones

Anónimo dijo...

Pega en celda A1, lo siguiente:
Num Letras Col1 Col2
135 AA =SI(A2=A3;B2&","&C3;B2) =SI(A2=A1;D1;C2)
135 BB =SI(A3=A4;B3&","&C4;B3) =SI(A3=A2;D2;C3)
135 CC =SI(A4=A5;B4&","&C5;B4) =SI(A4=A3;D3;C4)
135 DD =SI(A5=A6;B5&","&C6;B5) =SI(A5=A4;D4;C5)
135 EE =SI(A6=A7;B6&","&C7;B6) =SI(A6=A5;D5;C6)
200 FF =SI(A7=A8;B7&","&C8;B7) =SI(A7=A6;D6;C7)
210 GG =SI(A8=A9;B8&","&C9;B8) =SI(A8=A7;D7;C8)
2100 HH =SI(A9=A10;B9&","&C10;B9) =SI(A9=A8;D8;C9)
2100 II =SI(A10=A11;B10&","&C11;B10) =SI(A10=A9;D9;C10)

Anónimo dijo...

En comentario anterior, se deben colocar los datos y fórmulas en cuatro columnas, así:
Num Letras Col1 Col2
135 AA =SI(A2=A3;B2&","&C3;B2) =SI(A2=A1;D1;C2)

Las fórmulas se pueden copiar hacia abajo.
Sin operaciones matriciales, sin macros VBA, pero con el poder y la eficiencia de las columnas auxiliares.
En todo caso, Las Operaciones Matriciales son una maravilla.
Atte. ATV-5243512

Angel Arturo Perez dijo...

Muchas Gracias Atv-5243512 me ayudo a comprender sin embargo si los datos se encuentran desordenados no da el mismo resultado, de cualquier forma muchas gracias y a seguir buscando :)

Anónimo dijo...

B1 C1 D1 E1 F1 1 2 3 4 5 6 7
6 1351 135 AA AA AA BB CC DD EE NN
6 1352 135 BB BB AA BB CC DD EE NN
6 1353 135 CC CC AA BB CC DD EE NN
6 1354 135 DD DD AA BB CC DD EE NN
6 1355 135 EE EE AA BB CC DD EE NN
4 2001 200 FF FF FF HH LL MM
2 2101 210 GG GG GG KK
4 2002 200 HH HH FF HH LL MM
2 21001 2100 II II II JJ
2 21002 2100 JJ JJ II JJ
2 2102 210 KK KK GG KK
4 2003 200 LL LL FF HH LL MM
4 2004 200 MM MM FF HH LL MM
6 1356 135 NN NN AA BB CC DD EE NN

B2.. =CONTAR.SI($D$2:$D$15;$D2)
C2… =$D2&CONTAR.SI($D$2:$D2;$D2)
D y E Datos
F2…. =$E2
G2 (1)… =SI(($B2=1)+(G$1>$B2);"";BUSCARV($D2&G$1;$C$2:$E$15;3;0))

Publicar un comentario

Gracias por dejar tu comentario.