Encontrar la dirección de una celda en una matriz a partir del valor

El tema no es nuevo en el blog. En el pasado mostré como determinar la dirección de una celda a partir de su valor usando funciones definidas por el usuario (UDF, macros).

En los últimos tiempos el tema, en distintas variantes, se ha repetido en varias consultas. Por ejemplo, en la nota sobre encontrar el encabezamiento en una matriz a partir del valor de una de las celdas.

Tanto si queremos encontrar el rótulo de columna o de fila correspondientes a un valor en una matriz tenemos que ser capaces de calcular su posición en la hoja.
El tema de esta nota es cómo hacerlo usando funciones (usando macros ya lo hemos mostrado).

Empecemos por plantear esta matriz que muestra las unidades vendidas de cada línea de productos en cada zona (norte, sur, este, oeste)





Nuestro objetivo es que dado un valor de la matriz podamos ubicar la celda en que se encuentra. Esto nos permitirá luego determinar a qué zona y a qué producto corresponde el monto.
Seleccionamos el rango de valores (C3:F6) y lo incluimos en el nombre “ventas”



En el rango B8:B11 ponemos los rótulos: Monto, Producto, Zona y Celda



En C8 ponemos el valor, en C9 obtenemos el número de fila, en C10 el de columna y en C11 calculamos la dirección de la celda.
Las fórmulas son

Producto (C9): ={MAX((ventas=C8)*COLUMNA(ventas))}

Zona (C10): ={MAX((ventas=C8)*FILA(ventas))}

Celda (C11): =DIRECCION(C10,C9)

Las dos primeras son fórmulas matriciales que introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter.

Como puede verse, obtenemos la dirección de la celda. ¿Cómo funcionan las fórmulas de las celdas C9 y C10?

La expresión “ventas=C8” crea una matriz de valores FALSO/VERDADERO


La expresión FILA(ventas) crea un vector {3;4;5;6}.
Al multiplicar ambos vectores entre sí obtenemos una matriz cuyos valores son 0 excepto el que representa la fila del valor buscado (4 en nuestro caso)



Finalmente, la función MAX extrae el máximo valor, obviamente el de la fila del valor buscado.
Aplicamos la misma técnica para calcular el número de columna


El paso final es usar la función DIRECCION con las celdas C9 y C10 como argumentos.

Ahora extenderemos nuestro ejercicio para que parezca útil en algo, por lo menos.
Queremos crear una lista de los tres productos más vendidos y en qué zona


Para encontrar los tres productos más vendidos usamos la función K.ESIMO.MAYOR (¿a quién se le ocurrió semejante nombre?). En C15 ponemos
=K.ESIMO.MAYOR(ventas,B15)

y la copiamos a C16:C17


En la celda D15 ponemos esta fórmula matricial

={INDICE($B$1:$B$6,MAX((ventas=$C15)*FILA(ventas)))}
y la copiamos al rango D16:D17

En la celda E15 ponemos la fórmula matricial
={INDICE($A$2:$F$2,,MAX((ventas=$C15)*COLUMNA(ventas)))}

y la copiamos al rango E16:E17


Con la primer función INDICE encontramos el producto buscando en el rango B1:B6. Importante: nótese que el rango comienza en la primer fila de la hoja. Esto se debe a que el vector creado por la función FILA empieza con el número de la primer fila de la matriz en la hoja. Lo mismo sucede con el vector creado por la función COLUMNA.


Technorati Tags: MS Excel