BUSCARV (VLOOKUP) con texto dentro de texto

miércoles, septiembre 28, 2011

Uno de mis clientes implementó una aplicación para la administración de la planta de producción. En la planta hay una cierta cantidad de máquinas, que fueron asignadas a "centros". Cada centro puede tener una o más máquinas. Para crear la base de datos el ingeniero de producción preparó una tabla con las máquinas asignadas a cada centro



El departamento de computación pidió recibir una tabla con una línea para cada máquina



por lo que hubo que transformar la tabla de nuestro buen ingeniero en la tabla requerida por el departamento de computación (en la realidad hay en la planta 75 máquinas asignadas a 15 centros).

No podemos usar BUSCARV o INDICE con COINCIDIR ya que en una misma celda de la primer tabla hay más de una máquina. De hecho tenemos que extraer el valor correspondiente a un "sub-texto" dentro del texto.

Podemos hacerlo usando funciones nativas de Excel o con una UDF (función definida por el usuario). Con funciones nativas de Excel usamos esta fórmula matricial

 =INDICE(Hoja1!$A$4:$A$9;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(E4;Hoja1!$B$4:$B$9));0))



Esta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

La función HALLAR busca el número de máquina (E4) en el rango de máquinas de la tabla de ingeniero (B4:B9). De hecho, HALLAR busca el "texto" 251 dentro del texto de las celdas del rango B4:B9. Si lo halla, el resultado es un número (la posición donde comienza el texto buscado dentro del texto de la celda de búsqueda).
La expresión ESNUMERO(HALLAR… genera un vector de resultados VERDADERO o FALSO. COINCIDIR calcula la posición del primer valor VERDADERO dentro de ese vector. Este resultado, a su vez, es utilizado en la función INDICE para calcular el centro correspondiente.

También podemos crear esta UDF, usando la función InStr de Vba. La función calcula la posición de la máquina en la lista, es decir, reemplaza la expresión

COINCIDIR(VERDADERO;ESNUMERO(HALLAR…

en la fórmula anterior. El resultado de la función lo usamos como argumento en la función INDICE, sin necesidad de usarla en forma matricial



 El código de la función match_sub_string es el siguiente:

 Function match_sub_string(SearchValue, rngSearchRange As Range)

    Dim lRangeRows As Long 'numero de filas en el rango
    Dim iR As Long
  
    lRangeRows = rngSearchRange.Cells.Count
  
    For iR = 1 To lRangeRows
        If InStr(rngSearchRange.Item(iR), SearchValue) > 0 Then
            match_sub_string = iR
            Exit Function
        Else
            'do nothing
        End If
    Next iR
  
       
End Function

12 comments:

Anónimo,  28 septiembre, 2011 16:03  

Excelente Jorge.

Alguna vez tuve un problema parecido, Si los datos de la columna A los copiáramos en C (En C4 la fórmula =A4 y lo mismo hacía abajo) serviría la fórmula:
=BUSCARV("*"&E4&"*";TEXTO($B$4:$C$9;0);2;0)
Con el uso de comodines.
Por otro lado te felicito por el contenido del blog.
Saludos: Alejandro Hernández

Anónimo,  28 septiembre, 2011 16:06  

PD:
Sobre
=BUSCARV("*"&E4&"*";TEXTO($B$4:$C$9;0);2;0)
La fórmula es matricial, si los valores de la columna "Máquinas" fueran todos en texto, se puede omitir la fórmula TEXTO y no hace falta que la fórmula sea matricial.
Saludos: Alejandro

JOSE PEDRO MUÑIZ VARGAS,  28 septiembre, 2011 19:21  

Hola, Jorge. Mil felicidades por tu EXCELENTÍSIMO blog.

Seguí con interés este ejercicio, sin embargo, al realizar el ejercicio con la alternativa UDF únicamente coincidí con el resultó del primer renglón (Tal cual como en la imagen desplegada en tu blog).Deseo verificar si algo hice mal para entender mejor la formula definida por el usuario y si acaso, algo hice erróneamente corregirse.

Jorge L. Dunkelman 28 septiembre, 2011 21:50  

José

fijate de usar correctamente las referencias en las fórmulas. El rango de búsqueda de INDICE es con referencias absolutas (con los símbolos $); lo mismo con el vector de búsqueda la UDF ($B$4:$B$9).
Hay un pequeño error en la imagen del ejemplo de la UDF. Debería verse

=INDICE($A$4:$A$9;match_sub_string(E4;$B$4:$B$9))

JOSE PEDRO MUÑIZ VARGAS,  29 septiembre, 2011 00:36  

Hola, de nuevo, Jorge, corregí mi error en el ejercicio (opción UDF) y resultó perfectamente resuelto con la observación que comentaste. Muy agradecido contigo y estaré pendiente de tus futuros aportes y, porque no, compartir lo poco que sé para todos este grupo de seguidores tuyo.

Excelente semana.

MACC 04 octubre, 2011 18:54  

Me resultó con la alternativa UDF, pero no con las funciones nativa, empiezo a tener problemas desde la función COINCIDIR.

Igual felicitaciones por el blog!

Alexis Gonzalez 08 febrero, 2013 21:07  

Hola Jorge; te cuento que tengo el mismo problema que el Ingeniero, pero el mio tiene una variable, ya que los valores de la columna B se repiten; como si en el ejemplo hubiesen maquinas 251 en el centro B100. Intento utilizar la formula y me aparece #NA# por lo que no entiendo que variación tendria que agregarle a la formula. Ayuda por favor.
Saludos. (Excelente Blog)

Jorge L. Dunkelman 09 febrero, 2013 17:31  

Alexis,
es decir, ¿una máquia puede pertenecer a más de un centro?

Alexis Gonzalez 11 febrero, 2013 14:29  

Así es...como podría hacer eso sólo con funciones en la planilla excel?

Jorge L. Dunkelman 13 febrero, 2013 20:00  

Con fórmulas tendrías que crear primero una lista de máquinas donde cada máquina aparece el número de veces necesario.
En forma dinámica sólo se podría hacer con macros.

Unknown 12 diciembre, 2016 14:33  

Hola,

Necesito hacer un buscarv pero en vez de identificar el texto completo de la celda, que sea con parte del texto. Me explico:
tengo celdas con un texto " un dia comi una manzana" otra "un dia comi una pera"... entonces quiero que al identificar la palabra manzana escriba en la columna derecha "rojo" y al identificar la palabra pera escriba en la columna derecha "verde". ¿Es esto posible?

Jorge Dunkelman 14 diciembre, 2016 18:18  

Si, es posible. Pero en lugar de BUSCARV tendrías que usar alguna sentencia lógica, es decir, una fórmula que de VERDADERO si la pakabra existe y FALSO en caso contrario.
Por ejemplo, podrías usar HALLAR para determinar si la palabra está contenida en el texto.
Esta solución sólo te serviría si se trata de dos o tres posibilidades (verde, rojo y amarillo, por ejemplo).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP