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

domingo, octubre 11, 2009

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)



Excel celda en matriz

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”

Excel celda en matriz

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

Excel celda en matriz

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
Excel celda en matriz

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)

Excel celda en matriz

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
Excel celda en matriz

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
Excel celda en matriz

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
Excel celda en matriz

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
Excel celda en matriz

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:

25 comments:

HARKi de VRIEs 12 octubre, 2009 14:58  

Impresionante ayuda. Encontré tu blog hace un par de días y és de los mejores blogs que he visto. Muchas felicidades

Felipe Binimelis 29 noviembre, 2010 15:57  

Jorge,

Existe una forma de incluir estas funciones en una formula que esté solo en una celda?

Saludos

Jorge L. Dunkelman 30 noviembre, 2010 11:29  

Felipe,

podrías combinar las fórmulas en una única formula matricial que sería
=DIRECCION(MAX((ventas=C8)*COLUMNA(ventas)),MAX((ventas=C8)*FILA(ventas)))

Sócrates 18 enero, 2011 06:20  

Esta excelente, el blog.
Una pregunta tiene ejemplos en donde combine las formulas indice, direccion, celda??

Muchas gracias por su atencion

Sócrates,

Jorge L. Dunkelman 18 enero, 2011 15:03  

No estoy seguro (llevo publicadas 462 entradas en el blog). ¿Qué problema estás tratando de resolver?

Sócrates 19 enero, 2011 07:56  

No, ningun problema solo quiero encontar ejemplos en donde combinen las formulas, celdas, direccion e indice y utilizar estas herramientas en mi trabajo.

Jorge L. Dunkelman 19 enero, 2011 12:17  

Vuelvo a insistirte que sería más fácil darte un ejemplo resolviendo un problema real.
Por ejemplo, podrías usar CELDA con el parámetro "tipo" para saber si la celda contiene un valor; en caso positivo, podrías crear una referencia a la misma celda pero en otra hoja usando DIRECCION y luego usar el valor de esa celda como referencia en INDICE (fila o columna).

Anónimo,  11 marzo, 2013 14:05  

Hola, ¿Cómo puedo acceder al contenido de una celda cuya columna es R, y la fila es el contenido de A2?
Gracias

Jorge L. Dunkelman 11 marzo, 2013 15:38  

Con esta fórmula

=INDIRECTO(DIRECCION(A2;18))

Anónimo,  20 marzo, 2013 09:51  

Muchas gracias, y ¿cómo puedo buscar una celda por su contenido y que me devuelva la letra de la columna en la que está dicha celda?. Ejemplo, si la celda es T6, que me devuelva T. Gracias

Jorge L. Dunkelman 23 marzo, 2013 18:17  

Siguiendo con el ejemplo de la nota, podemos usar esta fórmula

=EXTRAE(C11;2;LARGO(C11)-HALLAR("$";C11;2))

Jose Juan Lamas Tacoronte 21 setiembre, 2013 07:11  

amigo esa es la formulación correcta? me da "valor" tengo un problema parecido a ese

Jose Juan Lamas Tacoronte 21 setiembre, 2013 07:14  

=MAX((cordinf=R9)*FILA(cordinf)) asi lo tengo codificado

Jorge Dunkelman 21 setiembre, 2013 11:45  

Jose Juan, ¿estás introduciendo la fórmula como fórmula matricial (Ctrl-Mayusculas-Enter)?

Jose Juan Lamas Tacoronte 22 setiembre, 2013 20:57  

LE DOY Ctrl-Mayusculas-Enter COMO INDICAS PERO NO CAMBIA NAD

Jorge Dunkelman 22 setiembre, 2013 22:03  

Mandame el archivo (ver Ayuda, en la parte superior de la plantilla)

Jose Juan Lamas Tacoronte 25 setiembre, 2013 07:02  

amigoo muchas gracias lo solucione con otro planteamiento en este mismo bolg, de todas formas el Ctrl-Mayusculas+Enter que no me funcionaba lo remplace por f2 + control + shif + enter, saludos

Jorge Dunkelman 25 setiembre, 2013 07:57  

Jose Juan, es el mismo método. F2 hace que la celda esté en estado de edición, es decir, como si hubieras introducido la fórmula y antes de apretar Enter.

Carlos A. Reyes Causso 05 noviembre, 2013 22:20  

Excelente, me sirvió de mucho, todo me funciono!!!

Hugo Curiel Rincón 06 febrero, 2014 19:07  

Está erróneo, porque al momento de poner 3324 en el monto, la celda que te da es $F$3 y no $C$6 que originalmente está. ¿a qué se debe ese error?

Jorge Dunkelman 06 febrero, 2014 20:24  

Qué curioso, esta nota tiene más de cuatro años y nadie se había dado cuenta. Es un "error de imprenta"; la formula correcta es =DIRECCION(C10,C9).
Gracias por la observación.

Diana Garcia 27 mayo, 2014 22:20  

hola que tal yo tengo una pregunta....
tengo una base de datos de un almacen de refacciones para una empresa que fabrica autopartes aqui en Aguascalientes, México. Hice un formulario en vba para excel y ya me hace la captura de datos incluso si quiero volver a hacer la captura de un registro existente me lo maneja como entrada y no como registro nuevo. Bueno en una hoja de excel tengo mi base de datos que incluye la marca, codigo y cantidad, y en otra tengo mi base de entradas donde se pone la fecha del dia actual, marca, codigo la cantidad que se agrega al stock inicial, el stock inicial y el stock final despues de dar entrada. Bien estos datos si se suman por ejemplo si tengo de una refacción C6 un stock inicial de 2 y añado 1 mi stock final es de 3 pero este dato de 3 solo se guarda en la hoja de entradas y lo que yo quiero es que automaticamente me modifique la cantidad en la base de datos para en un futuro si se vuelve a añadir de esta misma refacción tome como stock inicial 3 y no 2.
Espero no sea mucho pedir muchas gracias!!!!

Jorge Dunkelman 28 mayo, 2014 06:47  

Diana,
tu comentario no está relacionado con el tema de la nota. Por favor, fijate lo que pongo en el enlace Ayuda (en la parte superior de la plantilla), en relación a los pedidos de ayuda.

Marysol 23 julio, 2014 01:37  

Hola Jorge, he aprendido mucho de tus posts, agradezco el tiempo y me atrevo a hacerte una consulta sobre este mismo, hice el ejercicio de hacer coincidir los valores para que empataran en el 2o y tercer producto más vendido en diferentes zonas, pero al momento de que encuentra la primera coincidencia pone los mismos valores para los demás, por ejemplo 4188 heladeras en norte y 4188 lavadoras en sur pone lo mismo para ambos casos, la pregunta es cómo le hago para decirle que una vez encontrado la primera coincidencia, en la siguiente búsqueda descarte el ya encontrado? Espero haber explicado bien el caso y ojalá puedas ayudarme, de antemano te agradezco la atención. Saludos.

Jorge Dunkelman 23 julio, 2014 11:17  

Hola Marysol,

por lo general ese tipo de tareas (cálculos con varios criterios) con SUMAPRODUCTO o SUMIFS. También puede ihcerse con tablas dinámicas. Todo depende de como estén organizados los datos. Te sugiero que me mandes el archivo, o un ejemplo, siguiendo las instrucciones que aparecen en el enlace Ayuda (en la parte superior de la plantilla).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP