¿Quién es demasiado perezoso o no tiene tiempo para leer? Mire el video. Los detalles y matices están en el texto a continuación.
Formulación del problema
Entonces, tenemos dos mesas: mesa de pedidos и lista de precios:
La tarea es sustituir los precios de la lista de precios en la tabla de pedidos automáticamente, enfocándose en el nombre del producto para que luego puedas calcular el costo.
Solución
En el conjunto de funciones de Excel, en la categoría Referencias y arreglos (Búsqueda y referencia) hay una función VPR (VLOOKUP).Esta función busca un valor dado (en nuestro ejemplo, esta es la palabra "Manzanas") en la columna más a la izquierda de la tabla especificada (lista de precios) moviéndose de arriba a abajo y, una vez encontrado, muestra el contenido de la celda adyacente (23 rublos). Esquemáticamente, la operación de esta función se puede representar Entonces:
Para facilitar el uso posterior de la función, haga una cosa a la vez: asigne su propio nombre al rango de celdas en la lista de precios. Para hacer esto, seleccione todas las celdas de la lista de precios excepto el "encabezado" (G3: H19), seleccione del menú Insertar – Nombre – Asignar (Insertar — Nombre — Definir) o pulse CTRL + F3 e ingrese cualquier nombre (sin espacios) como Precio… Ahora, en el futuro, puede usar este nombre para vincular a la lista de precios.
Ahora usamos la función VPR… Seleccione la celda donde se ingresará (D3) y abra la pestaña Fórmulas – Inserción de funciones (Fórmulas — Insertar función)… En la categoría Referencias y arreglos (Búsqueda y Referencia) encuentra la función VPR (VLOOKUP) y pulse OK… Aparecerá una ventana para ingresar argumentos para la función:
Los llenamos a su vez:
- Valor deseado (Valor de búsqueda) – el nombre del producto que la función debe encontrar en la columna más a la izquierda de la lista de precios. En nuestro caso, la palabra “Manzanas” de la celda B3.
- Mesa (Matriz de tabla) – una tabla de la que se toman los valores deseados uXNUMXbuXNUMXbare, es decir, nuestra lista de precios. Como referencia, usamos nuestro propio nombre "Precio" dado anteriormente. Si no dio un nombre, puede simplemente seleccionar la tabla, pero no olvides presionar el boton F4para anclar el enlace con signos de dólar, porque de lo contrario, se deslizará hacia abajo al copiar nuestra fórmula hacia abajo al resto de las celdas de la columna D3:D30.
- número_columna (Número de índice de columna) – número de serie (¡no una letra!) de la columna en la lista de precios de la que tomaremos valores de precios. La primera columna de la lista de precios con los nombres está numerada 1, por lo tanto, necesitamos el precio de la columna numerada 2.
- intervalo_búsqueda (Rango de búsqueda) – solo se pueden ingresar dos valores en este campo: FALSO o VERDADERO:
- Si se ingresa un valor 0 or MENTIRA (FALSO), entonces, de hecho, esto significa que solo se permite la búsqueda coincidencia exacta, es decir, si la función no encuentra el artículo no estándar especificado en la tabla de pedidos en la lista de precios (si se ingresa "Coco", por ejemplo), generará el error #N/A (sin datos).
- Si se ingresa un valor 1 or VERDADERO (CIERTO), entonces esto significa que usted permite la búsqueda no de la exacta, sino coincidencia aproximada, es decir, en el caso de “coco”, la función intentará encontrar un producto con un nombre lo más parecido posible a “coco” y devolverá el precio de este nombre. En la mayoría de los casos, una sustitución tan aproximada puede engañar al usuario al sustituir el valor del producto incorrecto que realmente estaba allí. Por lo tanto, para la mayoría de los problemas comerciales reales, es mejor no permitir la búsqueda aproximada. La excepción es cuando buscamos números y no texto, por ejemplo, al calcular descuentos por pasos.
¡Todo! Queda por presionar OK y copie la función ingresada a toda la columna.
# N/A errores y su supresión
Función VPR (VLOOKUP) devuelve el error #N/A (#N / A) si un:
- Búsqueda exacta habilitada (argumento Vista de intervalo = 0) y el nombre deseado no está en Mesa.
- Búsqueda gruesa incluida (Vista de intervalo = 1), Pero Mesa, en el que se está realizando la búsqueda no está ordenado en orden ascendente de nombres.
- El formato de la celda de donde proviene el valor requerido del nombre (por ejemplo, B3 en nuestro caso) y el formato de las celdas de la primera columna (F3:F19) de la tabla son diferentes (por ejemplo, numérico y de texto). ). Este caso es especialmente típico cuando se utilizan códigos numéricos (números de cuenta, identificadores, fechas, etc.) en lugar de nombres de texto. En este caso, puede utilizar las funciones Ч и TEXTO para convertir formatos de datos. Se verá algo como esto:
=BUSCARV(TEXTO(B3),precio,0)
Puedes leer más sobre esto aquí.
- La función no puede encontrar el valor requerido porque el código contiene espacios o caracteres invisibles no imprimibles (saltos de línea, etc.). En este caso, puede utilizar funciones de texto. TRIM (PODAR) и IMPRESIÓN(LIMPIO) para eliminarlos:
=BUSCARV(RECORTAR ESPACIOS(LIMPIAR(B3)),precio,0)
=BUSCARV(RECORTAR(LIMPIAR(B3));precio;0)
Para suprimir el mensaje de error #N / A (#N / A) en los casos en que la función no puede encontrar una coincidencia exacta, puede utilizar la función IFERROR (SI ERROR)… Entonces, por ejemplo, esta construcción intercepta cualquier error generado por VLOOKUP y los reemplaza con ceros:
= SI.ERROR (BUSCARV (B3, precio, 2, 0), 0)
= SI.ERROR (BUSCARV (B3; precio; 2; 0); 0)
PS
Si necesita extraer no un valor, sino todo el conjunto a la vez (si hay varios diferentes), entonces tendrá que chamanizar con la fórmula de matriz. o use la nueva función BUSCARX de Office 365.
- Una versión mejorada de la función BUSCARV (BUSCARV 2).
- Cálculo rápido de descuentos por pasos (rango) utilizando la función BUSCARV.
- Cómo hacer una "BUSCARV izquierda" usando las funciones ÍNDICE y COINCIDIR
- Cómo utilizar la función BUSCARV para rellenar formularios con datos del listado
- Cómo extraer no el primero, sino todos los valores de la tabla a la vez
- Funciones VLOOKUP2 y VLOOKUP3 del complemento PLEX