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
6 comments:
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
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
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.
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))
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.
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!
Publicar un comentario