Funciones matriciales de rango – Un ejemplo

jueves, diciembre 06, 2007

En el pasado en mis notas sobre fórmulas matriciales mencionaba que las funciones matriciales de Excel también pueden usarse para obtener el resultado de un cálculo en varias celdas simultáneamente, lo que llamamos fórmulas matriciales "multicelulares".
En una nota más reciente sobre cómo extraer elementos únicos de un rango en Excel mencionaba el uso de la función UNIQUEVALUES del complemento MoreFunc de Laurent Longre.

Menciono esto porque en esta nota mostraré cómo combinar todos estos ingredientes para resolver una consulta de uno de mis lectores.

La situación es la siguiente: en una CLIENTES_DE_EMPRESAS_Jorgehoja de Excel tenemos esta tabla



En una segunda tabla queremos poner debajo del nombre del cliente a que empresa pertenece



Como no puede ser de otra manera, queremos que esta segunda tabla sea dinámica. Es decir, si cambiamos el nombre del cliente en el encabezamiento, los valores deberán adaptarse automáticamente. Lo mismo si cambiamos la ubicación de las "X" en la primer tabla.

Nuestro primer paso consiste en crear una fórmula matricial que de como resultado un rango de valores.
Debemos prestar atención al hecho que en la primer tabla, los clientes están ordenados en columna y las empresas en filas, es decir, a la inversa de lo que queremos obtener en la segunda tabla.
Empezamos por crear la fórmula matricial que nos de los nombres de las empresas por cliente donde haya una X y si no la hay, un espacio en blanco. Tomando como ejemplo el primer nombre de la segunda tabla, LUIS, escribimos esta fórmula matricial

={SI(I5:N5="X";I2:N2;"")}

simultáneamente en el rango B3:E3, presionado al mismo tiempo Ctrl+Mayúsculas+Enter



Para "girar" el rango 90 grados, usamos la función TRANSPONER, que también es matricial

={TRANSPONER(SI(G5:L5="X";G2:L2;""))}



Nuestro próximo paso es convertir nuestro modelo en dinámico. Para esto creamos nombres que contengan los rangos de los clientes:

JUAN ='Hoja1 (3)'!$G$3:$L$3
LUCAS='Hoja1 (3)'!$G$8:$L$8
LUIS ='Hoja1 (3)'!$G$5:$L$5
MARIO='Hoja1 (3)'!$G$6:$L$6
PEDRO='Hoja1 (3)'!$G$7:$L$7
ROSA ='Hoja1 (3)'!$G$4:$L$4
SONIA='Hoja1 (3)'!$G$10:$L$10
VICTOR='Hoja1 (3)'!$G$9:$L$9

Estos nombres nos servirán para crear una referencia dinámica en nuestra fórmula con la ayuda de la función INDIRECTO. Reemplazamos nuestra fórmula anterior por

{=TRANSPONER(SI(INDIRECTO(B$2)="X";$G$2:$L$2;""))}



Ahora, si cambiamos el nombre en el encabezamiento, los resultados cambiarán automáticamente.

Nuestra solución es casi perfecta. Nos falta eliminar los espacios en blanco entre los nombres de las empresas. Para esta tarea usaremos la función UNIQUEVALUES del complemento MoreFunc ya mencionado. Esta función también es matricial y debe ser introducida pulsando al mismo tiempo Ctrl+Mayúsculas+Enter

{=UNIQUEVALUES(TRANSPONER(SI(INDIRECTO(B$2)="X";$G$2:$L$2;"")))}

Nuestro modelo se ve ahora así



que es lo que queríamos obtener.



Technorati Tags:

2 comments:

Juan Eduardo Gonzales Sandoval 12 diciembre, 2008 01:19  

Estimado Jorge
Primero para felicitarte por este excelente blog, en verdad siempre lo consulto cuando tengo dudas.
En este caso mi consulta es con respecto a la función TRANSPONER, cuando utilizo esta función, primero me coloco en una celda vacia y escribo la función con el respectivo rango, pero me sale como respuesta #¡VALOR!,que puede estar sucediendo? porque ya anteriormente utilizé esta función sin ningún problema.
De antemano te agradezco tu respuesta

Gracias

Jorge L. Dunkelman 12 diciembre, 2008 09:27  

Hola

fijate si alguna de las celdas de los rangos que aparecen en la fórmula contienen este error o si al tipo de argumento no corresponde a los aceptados por la función. TRANSPONER() es una función matricial y por lo tanto hay que introducirla apretando Ctrl+Mayúsculas+Enter.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP