Usando a función BUSCARV para substituír valores

Quen é moi preguiceiro ou non ten tempo para ler: mira o vídeo. Os detalles e os matices están no seguinte texto.

Formulación do problema

Entón, temos dúas táboas: táboa de pedidos и Lista de prezos:

A tarefa consiste en substituír os prezos da lista de prezos na táboa de pedidos automaticamente, centrándose no nome do produto para que posteriormente poida calcular o custo.

solución

No conxunto de funcións de Excel, baixo a categoría Referencias e matrices (Busca e referencia) hai unha función VPR (BÚSQUEDA V).Esta función busca un valor dado (no noso exemplo, esta é a palabra "Mazás") na columna máis á esquerda da táboa especificada (lista de prezos) movéndose de arriba a abaixo e, unha vez atopado, mostra o contido da cela adxacente (23 rublos). Esquemáticamente, o funcionamento desta función pódese representar Así:

Para facilitar o uso posterior da función, faga unha cousa á vez: dálle o seu propio nome ao rango de celas da lista de prezos. Para iso, seleccione todas as celas da lista de prezos, excepto a "cabeceira" (G3: H19), seleccione no menú Inserir - Nome - Asignar (Inserir — Nome — Definir) ou prema CTRL + F3 e introduza calquera nome (sen espazos) como prezo… Agora, no futuro, podes usar este nome para ligar á lista de prezos.

Agora usamos a función VPR… Seleccione a cela onde se introducirá (D3) e abra a pestana Fórmulas – Inserción de funcións (Fórmulas — Función de inserción)… Na categoría Referencias e matrices (Buscar e referencia) atopar a función VPR (BÚSQUEDA V) e prensa OK... Aparecerá unha xanela para introducir argumentos para a función:

Enchémolos á súa vez:

• Valor desexado (Valor de busca) – o nome do produto que a función debería atopar na columna da esquerda da lista de prezos. No noso caso, a palabra "Mazás" da cela B3.
• Táboa (Matriz de táboa) – unha táboa da que se tomaron os valores desexados uXNUMXbuXNUMXbare, é dicir, a nosa lista de prezos. Como referencia, usamos o noso propio nome "Prezo" indicado anteriormente. Se non deu un nome, pode simplemente seleccionar a táboa, pero non esquezas premer o botón F4para fixar a ligazón con signos de dólar, porque se non, desprazarase cara abaixo ao copiar a nosa fórmula ata o resto das celas da columna D3:D30.
• Número_columna (Número de índice da columna) – número de serie (non unha letra!) Da columna da lista de prezos da que tomaremos os valores dos prezos. A primeira columna da lista de prezos cos nomes está numerada 1, polo que necesitamos o prezo da columna numerada 2.
• intervalo_lookup (Busca de rango) – só se poden introducir dous valores neste campo: FALSO ou VERDADEIRO:
• Se se introduce un valor 0 or MENTIRO (FALSO), entón de feito isto significa que só se permite a busca coincidencia exacta, é dicir, se a función non atopa o artigo non estándar especificado na táboa de pedidos na lista de prezos (se se introduce "Coco", por exemplo), xerará o erro #N/A (sen datos).
• Se se introduce un valor 1 or TRUE (VERDADEIRO), entón isto significa que permite a busca non para o exacto, pero coincidencia aproximada, é dicir, no caso de “coco”, a función tentará buscar un produto cun nome que se aproxime o máis posible a “coco” e devolverá o prezo deste nome. Na maioría dos casos, unha substitución tan aproximada pode xogar un truco ao usuario ao substituír o valor do produto incorrecto que estaba realmente alí. Polo tanto, para a maioría dos problemas comerciais reais, é mellor non permitir a busca aproximada. A excepción é cando buscamos números e non texto, por exemplo, cando calculamos descontos por pasos.

Todo! Queda por presionar OK e copie a función introducida en toda a columna.

# N/A erros e a súa supresión

función VPR (BÚSQUEDA V) devolve un erro #N/A (#N / A) se a:

• Busca exacta activada (argumento Vista de intervalo = 0) e o nome desexado non está en Táboa.
• Busca grosa incluída (Vista de intervalo = 1), pero Táboa, no que se está a realizar a busca non está ordenado en orde ascendente de nomes.
• O formato da cela de onde procede o valor necesario do nome (por exemplo, B3 no noso caso) e o formato das celas da primeira columna (F3: F19) da táboa son diferentes (por exemplo, numérico e de texto). ). Este caso é especialmente típico cando se usan códigos numéricos (números de conta, identificadores, datas, etc.) en lugar de nomes de texto. Neste caso, pode usar as funcións Ч и TEXTO para converter formatos de datos. Será algo así:

  =BUSCARV(TEXTO(B3),prezo,0)

  Podes ler máis sobre isto aquí.
  

• A función non pode atopar o valor necesario porque o código contén espazos ou caracteres invisibles non imprimibles (saltos de liña, etc.). Neste caso, pode usar funcións de texto TRIM (TRIM) и Imprimir(LIMPAR) para eliminalos:

  =BUSCARV(ESPAZOS(LIMPO(B3)),prezo,0)

  =BUSCARV(RECORTAR(LIMPAR(B3));prezo;0)

Para suprimir a mensaxe de erro #N / A (#N / A) nos casos en que a función non pode atopar unha coincidencia exacta, pode usar a función SE ERRO (SE ERRO)… Así, por exemplo, esta construción intercepta os erros xerados pola BUSCAR V e substitúeos por ceros:

= SE ERRO (BUSCAR V (B3, prezo, 2, 0), 0)

= SE ERRO (BUSCAR V (B3; prezo; 2; 0); 0)

PS

Se precisas extraer non un valor, senón todo o conxunto á vez (se hai varios diferentes), entón terás que chamarizar coa fórmula matricial. ou use a nova función XLOOKUP de Office 365.

• Unha versión mellorada da función BUSCARV (BUSCARV 2).
• Cálculo rápido dos descontos por pasos (rango) usando a función BUSCAR V.
• Como facer unha "BUSCAR VOLVER á esquerda" usando as funcións INDEX e MATCH
• Como usar a función BUSCAR V para cubrir os formularios con datos da lista
• Como sacar non o primeiro, senón todos os valores da táboa á vez
• VLOOKUP2 e VLOOKUP3 funcións do complemento PLEX