Búsquedas con COINCIDIR en varias columnas

miércoles, septiembre 26, 2012

A partir de mañana y por los próximos diez estaré ocupado en una investigación sobre el tema “Los mejores platos y vinos en restoranes no turísticos de Toscana” (es decir, me tomo vacaciones en esa bellísima zona de Italia).

Mientras tanto veamos un tema sobre el cual he recibido varias consultas últimamente: realizar búsqueda usando COINCIDIR a través de varias columnas.

Para calcular qué posición ocupa un elemento determinado en un rango, Excel nos provee con la función COINCIDIR. Pero si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.

Para el caso supongamos que tenemos una serie de valores (números o texto) en el rango D1:E10. Para saber que posición ocupa el valor “14” nos veremos tentados a usar la fórmula

=COINCIDIR(B2,D1:E10,0)

donde B2 contiene el valor de búsqueda. Si bien 14 ocupa el quinto lugar en la segunda columna, el resultado es #N/A



La función COINCIDIR funciona sólo con rangos de búsqueda (matrices) de una única columna o fila.

Para hacer la búsqueda a través de varias columnas podemos combinar COINCIDIR con las funciones SI y ESERROR o, si usamos Excel 2007 o 2010, la nueva función SI.ERROR de esta manera

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),"inexistente"))



Si la búsqueda debe hacerse en tres columnas agregamos otra función SI.ERROR

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),SI.ERROR(COINCIDIR(B2,F1:F10,0),"inexistente")))

En Excel Clásico (97-2003) puede hacerse combinando Si con ESERROR, pero la nueva función SI.ERROR nos permite crear una fórmula mucho más compacta.

Uno de los inconvenientes de esta solución es que no nos dice en qué columna se encuentra el valor. Además, nos da la ubicación relativa del elemento en la matriz de búsqueda, pero por lo general queremos saber en qué fila se encuentra el elemento.

Para calcular la dirección de la celda que contiene el elemento buscado podemos usar DIRECCION combinada con la solución anterior

=SI.ERROR(DIRECCION(COINCIDIR(B2,D1:D10,0),4),SI.ERROR(DIRECCION(COINCIDIR(B2,E1:E10,0),5),"inexistente"))



Otra alternativa es crear una UDF (función definida por el usuario) como ésta

Function direccion_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
   
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            direccion_celda = rngCell.Address
            Exit Function
        Else
            direccion_celda = "inexistente"
        End If
    Next rngCell

End Function

21 comments:

salakazam 26 septiembre, 2012 12:29  

Bueno, pues perdón por el off-topic, pero espero que disfrutes de esas vacaciones.
Buenísima elección de lugar, igual, si la investigación es fructuosa (cosa que no dudo), puedas hacer un post al respecto, jejeje.
Un saludo.

Jota 26 septiembre, 2012 16:04  

Nuevamente muchas gracias por compartir tus conocimientos de excel... un saludo desde Colombia!
Disfruta de esas merecidas vacaciones!

Anónimo,  26 septiembre, 2012 18:12  

Gracias por este Tip tan interesante y util, sigo pendiente de tus publicaciones y te deseo una reconfortantes vacaciones.

Natxo G 26 septiembre, 2012 19:04  

Hola, Jorge. Sólo como curiosidad aporto una fórmula que devuelve la dirección del valor buscado sea cual sea cualquier número de columnas. Eso sí, tiene una gran pega: devuelve resultados erróneos con valores repetidos. Igual algún otro lector es capaz de perfeccionarla, yo no doy más de sí :-)

{=DIRECCION(SUMAPRODUCTO((SI(C4:D13=B4;1;0));SI(NO(ESERROR(C4:D13));FILA(C4:D13);0));SUMAPRODUCTO((SI(C4:D13=B4;1;0));SI(NO(ESERROR(C4:D13));COLUMNA(C4:D13);0)))}

El uso de NO(ESERROR) es para que la función FILA me devuelva valores para ambas columnas y poder efectuar la operación.

Que disfrutes de tus vacaciones. Gracias por tu estupendo blog y un saludo desde Bilbao.

Natxo

POSEID0 27 septiembre, 2012 04:42  

Buen dia
Es la primera vez que comento en el blog pero de igual forma eh estado viendo algunos post que son muy buenos, excelente blog la verdad y quisiera una ayuda yo quiero eliminar mediante formulas filas en blanco pero la verdad no se me ocurre nada es mas que todo un reto... igual si me pudieran proporcionar alguna solucion se los agradecere bastante.
Saludos y feliz dia

JMarc 27 septiembre, 2012 18:28  

Hola Jorge,
En primer lugar, disculpa mi español. Soy canadiense y mi lengua materna es el francés.

Aquí una fórmula que devuelve la dirección del primer valor de un rango de valores con nombre "Rng". Si hay valores repetidos, solomente el primer valor buscado es devuelve.
Si el valor buscado no existe en el rango, devuelve "N/A"
No importa el nombre de filas o columnas en el rango.

Fórmula:
=SIERREUR(ADRESSE(1/SOMMEPROD(MAX(((Rng)=B2)*1/LIGNE(Rng),0)),1/SOMMEPROD(MAX(((Rng)=B2)*1/COLONNE(Rng),0))),"N/A")

o

=SI.ERROR(DIRRECCION(1/SUMAPRODUCTO(MAX(((Rng)=B2)*1/FILA(Rng),0)),1/SUMAPRODUCTO(MAX(((Rng)=B2)*1/COLUMNA(Rng),0))),"N/A")

Disfruta de tus vacaciones

Jorge L. Dunkelman 05 octubre, 2012 11:24  

Hola,
no se puede cambiar la estructura de la hoja (eliminar filas o columnas, ocultarlas, etc.) con fórmulas. Las fórmulas saben hacer una única cosa, que hacen muy bien, calcular.

Anónimo,  12 octubre, 2012 00:21  

Excelente blog, sr. Dunkelman. Siempre algo nuevo para aprender. En este caso, cuando se busca la "dirección" de un valor, considero que lo mejor es que se puedan encontrar todas las localizaciones del valor, si está repetido:

For Each myCell In Matriz
If myCell = Criterio Then
Res = Res & myCell.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " "
End If
Next myCell

Y más o menos así se tendrían todas las ubicaciones de un valor. Saludos!

Anónimo,  07 junio, 2013 12:43  

EXCELENTE TUTORIAL, PERO LA FORMULA NO ME FUNCIONA, TENGO UN GRAN INTERES POR ESTA FORMULA, DE ANTEMANO LE DOY LAS GRACIAS Y LE FELICITO POR SU EXTRAORDINARIO BLOG.
JUANA GARCIA DESDE ESPAÑA

Jorge Dunkelman 07 junio, 2013 15:38  

Te sugiero que leas lo que pongo en el enlace "Ayuda" y me mandes el archivo para que pueda ver por qué no te funciona.

LUIS CARDENAS CADAVID 17 junio, 2014 19:58  

La idea es: tengo una matriz con las columnas nombradas ORIGEN DESTINO Y FLETE, en origen se repiten varias veces pues de ahí salen servicios para todo el país, lo mismo pasa en destino pues llegan de todo el país, los fletes también se repiten pues los valores de transporte son iguales para algunos recorridos, lo que quiero que me ayuden a realizar es buscar en esta matriz los valores que en otra hoja de cálculo tengo sin el flete, por ende buscar origen y destino y que me arroje el valor del flete, dejo ejemplo de la matriz, aunque en realidad son 200 filas con información.
BASE VILLAVICENCIO POZO LLANOS 58 $ 500.000
BASE VILLAVICENCIO POZO LLANOS 58 $ 500.000
BASE VILLAVICENCIO POZO OCELOTE SW 25H $ 650.000
BASE VILLAVICENCIO POZO VENUS 4 $ 220.000
HOTEL HACARITAMA VILLAVICENCIO POZO PASTINACA 4 $ 200.000
BASE VILLAVICENCIO CASTILLA $ 110.000
BASE VILLAVICENCIO BOGOTA $ 250.000

Jorge Dunkelman 18 junio, 2014 08:08  

Luis, por favor fijate en lo que pongo en el enlace Ayuda (en la parte superior de la plantilla del blog) y ponte en contacto conmigo por mail privado.

Hugo Oscar Paz 26 agosto, 2014 22:53  

Hola Jorge, estaba leyendo este posteo, la función definida para usuario, la quiero mejorar para cuando el valor se repite más de una vez pero no me funciona. la queria usar en una tabla de alumnos, meses, para ver por ejemplo los alumnos que tuvieron un 3 y en que mes. voy a seguir intentando sino te consulto de nuevo.

en 2º lugar, si bien pasó casi dos años de tu publicación, no se si conoces Argentina, aqui los vinos son excelentes y ni hablar de sus carnes, si vienes el Asado y los vinos corren por mi cuenta.

Saludos

Jorge Dunkelman 27 agosto, 2014 09:42  

Hola Hugo, se puede modificar el código para que muestre todas las celdas que contienen el valor buscado; fijate en el comentario anónimo del 12/10/2012.
En cuanto a vinos y asado en Argentina los conozco muy bien, así que te aviso en mi próxima visita.

Hugo Oscar Paz 27 agosto, 2014 23:50  

Muchas gracias, como siempre por tu pronta respuesta.

Y será un gusto tenerte de visita.

Abrazo

Zulma Gineth Ramos Ramirez 02 octubre, 2014 00:51  

Buenas; me podrian ayudar con una formula que me encuentre la ultima posicion donde se encuentra un caracter.

Gracias

Jorge Dunkelman 02 octubre, 2014 13:45  

Hola Zulma, ¿podrías ser un poco más precisa? ¿Se trata de encontrar la opsición de un caracter en una cadena de texto dentro de una celda?

Anónimo,  28 noviembre, 2014 13:06  

Mucho más fácil hacer =INDICE(COINCIDIR;COINCIDIR)

Jorge Dunkelman 28 noviembre, 2014 13:41  

¿Podrías dar un ejemplo de cómo funcionaría? En tu propuesta =INDICE(COINCIDIR;COINCIDIR) el resultado del primer COINCIDIR daría el número de fila y el segundo COINCIDIR la columna, pero ambas funciones COINCIDIR sólo saben buscar en una sola columna (o fila). Así que no logro ver cómo funcionaría la fórmula.

Angel Arturo Perez Campero 17 abril, 2016 23:48  

muchas Gracias Ingeniero, no existe esta explicacion en ningun lado y usted la resolvio de la mejor manera, agradezco sus atenciones y espero que profesional y personalmente le vaya muy bien en su vida.

Rafael Palacios Velasco 20 julio, 2016 12:31  

El uso combinado de SI.ERROR (o el de SI con ESERROR) con COINCIDIR tiene el problema de que habría que añadir un “trozo” de fórmula por cada columna de la matriz, lo que impediría obtener el resultado automáticamente si se añaden o eliminan columnas. Creo que el problema de encontrar la posición (fila y columna) que ocupa un valor en una matriz también se puede resolver con relativa facilidad añadiendo una fila y una columna con una serie correlativa y combinando una función SI con un producto de matrices. Pongo un ejemplo:

Imaginemos que tenemos siete productos (A, B, …, G) que pueden tener tres tallas (S, M y L), y que la información sobre estos 21 artículos se tiene en tablas de 7 filas por 3 columnas. Imaginemos que la tabla de precios está en E4:G10. Imaginemos que en otra tabla en E17:G23 tenemos una tabla con sus referencias.

El problema que se pretende resolver es que al introducir en la celda C28 una referencia, la hoja haga dos cosas: a) calcule cuál es la posición de esa referencia en la segunda tabla, y b) nos dé en la celda D28 el precio de esa referencia tomándolo de la primera tabla. La cuestión b) es fácil de resolver mediante la función ÍNDICE, pero el problema está en encontrar la referencia en la segunda tabla, porque no sabemos a priori en cuál de las tres (o, en general, las que sean) columnas se va a encontrar.

Creo que el problema se resuelve con sencillez añadiendo una fila y una columna con números correlativos 1, 2, … a cualquiera de las tablas. Por ejemplo, añadiendo una fila con los números 1, 2, 3 en E3:G3 y una columna con 1, 2, … , 7 en D4:D10.

Para calcular en qué fila está la referencia introducida en C28, usaría esta fórmula matricial:

{=SUMA(MMULT(TRANSPONER($D$4:$D$10);SI($E$17:$G$23=C28;1;0)))}

Y para calcular la columna, ésta otra:

{=SUMA(MMULT(SI($E$17:$G$23=C28;1;0);TRANSPONER($E$3:$G$3)))}

La obtención del precio tomaría esos cálculos como argumentos de una función INDICE:

{=INDICE($E$4:$G$10;SUMA(MMULT(TRANSPONER($D$4:$D$10);SI($E$17:$G$23=C28;1;0)));SUMA(MMULT(SI($E$17:$G$23=C28;1;0);TRANSPONER($E$3:$G$3))))}

Espero que esta solución pueda resultar de interés a los lectores de este extraordinario blog, del que tanto se puede aprender, y por el que le quedo muy agradecido.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP