real time web analytics

Función BUSCAR en Excel – Xelplus

Trabajando con una lista de números de parte, necesitamos descubrir el número de parte que serviría como el padre número de pieza para todos sus niño números de parte

A continuación se muestra una lista de los números de pieza y sus correspondientes lista de materiales (Lista de materiales) Nivel.

Esta es una operación un poco complicada porque no queremos descubrir cada número de parte Nivel de lista de materiales valor, ya sabemos que más bien el Nivel de lista de materiales número de su padre. Una vez que ubiquemos a los padres Nivel de lista de materialespodemos descubrir el número de pieza asociado.

Por ejemplo, si estamos viendo un número de pieza con un Nivel de lista de materiales de 3necesitamos descubrir el número de pieza en Lista de materiales nivel 2 asociado con ese niño.

Como el número de pieza en Lista de materiales nivel 0 no tiene padre, lo omitiremos por ahora.

Comenzando en la celda C2escribimos la siguiente fórmula:

=LOOKUP( A3 – 1, $A$2:A3, $B$2:B3 )

Tenemos un par de tácticas interesantes y poco convencionales en esta fórmula.

  1. los valor de búsqueda esta teniendo 1 sustraído de él. Esto nos permite buscar el padre. Nivel de lista de materiales valor en lugar de la corriente Nivel de lista de materiales valor como lo es cada padre 1 menos que su hijo.
  2. Las referencias de rango tanto para el buscar_vector y el resultados_vector tener la primera porción fijada como una referencia absoluta (con los signos de dólar) mientras que la segunda parte se deja como referencia relativa (sin los signos de dólar).
    La idea aquí es que cuando la fórmula se llena hasta las filas adyacentes, los dos vectores se expandirán para examinar filas adicionales de contenido. Esto limita el rango de búsqueda a solo valores hasta e incluyendo la posición actual en la lista.

La segunda táctica es la más interesante y posiblemente la más desconcertante.

Si examinamos toda la gama de datos tanto para el buscar_vector y resultados_vectorlos resultados aparecerían de la siguiente manera.

Cada Lista de materiales nivel 3 el artículo está descubriendo “10800-1002” como su padre. Esto se debe a que el BUSCAR se detiene cuando se encuentra con el primero Lista de materiales nivel 2 entrada.

Concedido, el primero Lista de materiales nivel 2 la entrada es en la celda A4; tenemos una serie de “2valores de ” en las celdas A4 mediante A6. los BUSCAR La función se basa en la última entrada de la serie duplicada (célula A6). Por lo tanto, el número de parte en la celda B6 (“10800-1002”) se devuelve para todos Lista de materiales nivel 3 entradas.

Las referencias utilizadas anteriormente donde la primera parte se establece como absoluta y la segunda parte como relativa…

$A$2:A3 and $B$2:B3

… ayuda un poco a combatir este problema, pero no mucho.

Estos resultados incorrectos se deben a 2 factores:

  1. Nuestro Nivel de lista de materiales los valores no se ordenan en orden ascendente.
  2. la dirección que BUSCAR está realizando la búsqueda.

BUSCAR está buscando desde el DE ARRIBA HACIA ABAJO. Nosotros necesitamos BUSCAR para buscar desde el DE ABAJO HACIA ARRIBA.

“¡Pero espera!”, dices. “¿Eso no causará el problema opuesto?”

No, porque el rango de expansión siempre limitará nuestra búsqueda a solo la fila actual y las filas examinadas anteriormente.

No podemos ordenar la lista por Nivel de lista de materialesy el BUSCAR La función carece un poco de opciones configurables, como buscar de abajo hacia arriba. Necesitamos ser creativos con nuestra fórmula.

Para ayudar a resolver este problema, crearemos nuestro vector de búsqueda personalizado que consiste en “Verdadero/Falso”, entonces en última instancia “1s/errores”.

Primero, (ignorando el valor de búsqueda por el momento) modificamos el buscar_vector argumento para comparar cada entrada en el rango con el Nivel de lista de materiales menos 1.

Para la fórmula en la celda. C3, esto resolvería cada prueba como se muestra a continuación; verdadero y falso.

SUGERENCIA PRO: Para ver los resultados de cada prueba, resalte el argumento deseado y presione F9. Asegúrese de presionar Deshacer (CTRL-Z) para devolver la fórmula a su estado original antes de salir del modo de edición.

Necesitamos convertir estos resultados “verdadero/falso” en “1s/Errores”. Esto se hace fácilmente dividiendo cada uno de los resultados en 1.

Los resultados de nuestras nuevas pruebas están a continuación.

Esta forma de prueba será solo rendimiento 1s y errores.

Mirando la última entrada en la tabla (célula C17), la fórmula aparece así…

…y resuelve cada prueba de la siguiente manera.

Aquí es donde ocurre la magia

Dado que una serie de pruebas solo darán como resultado 1s y erroressolo necesitamos buscar cualquier valor mayor que 1me gusta 2.

BUSCAR buscará en todo el rango el número 2, no lo encuentre nunca, luego elija el último número que vio durante su búsqueda. Esta será la última”1” en los resultados de la prueba.

Dorar el lirio

Tener algo exhibido para cualquier Lista de materiales nivel 0 entradas, colocaremos la fórmula en la celda C2 que devuelve un error de dividir por cero porque no hay Nivel de lista de materiales -1. (La prueba devuelve un “FALSO” que es interpretado por Excel como un cero).

Suprimiremos este error y lo reemplazaremos con un guión usando el SI ERROR función.

=IFERROR(LOOKUP(2, 1/(A2-1=$A$2:A2), $B$2:B2), "-")

Leave a Comment