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
Excelente Jorge.
ResponderBorrarAlguna 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:
ResponderBorrarSobre
=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.
ResponderBorrarSeguí 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é
ResponderBorrarfijate 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.
ResponderBorrarExcelente semana.
Me resultó con la alternativa UDF, pero no con las funciones nativa, empiezo a tener problemas desde la función COINCIDIR.
ResponderBorrarIgual felicitaciones por el blog!
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.
ResponderBorrarSaludos. (Excelente Blog)
Alexis,
ResponderBorrares decir, ¿una máquia puede pertenecer a más de un centro?
Así es...como podría hacer eso sólo con funciones en la planilla excel?
ResponderBorrarCon fórmulas tendrías que crear primero una lista de máquinas donde cada máquina aparece el número de veces necesario.
ResponderBorrarEn forma dinámica sólo se podría hacer con macros.
Hola,
ResponderBorrarNecesito 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?
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.
ResponderBorrarPor 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).
La formula recomendada es la original, =INDICE($A$4:$A$9;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(E4;$B$4:$B$9));0))
ResponderBorrarImportante Para ejecutar la matricial tienen que precionar: Crtl+Shif+Enter para que funcione la fórmula
Esta fórmula =BUSCARV("*"&E4&"*";TEXTO($B$4:$C$9;0);2;0) NO la recomiendo porque en grandes bases de datos pone lento el archivo y no todos los resultados los da.
Adrián, veo que estás respondiendo al primer comentario, de setiembre del 2011. Bien, nunca es tarde... :)
ResponderBorrar