Quem tem preguiça ou não tem tempo para ler – assista ao vídeo. Detalhes e nuances estão no texto abaixo.
Formulação do problema
Então, temos duas tabelas - tabela de pedidos и lista de preços:
A tarefa é substituir os preços da tabela de preços na tabela de pedidos automaticamente, focando no nome do produto para que posteriormente você possa calcular o custo.
Solução
No conjunto de funções do Excel, na categoria Referências e matrizes (Pesquisa e referência) existe uma função VPR (PROCV).Esta função procura um determinado valor (no nosso exemplo, esta é a palavra “Maçãs”) na coluna mais à esquerda da tabela especificada (lista de preços) movendo-se de cima para baixo e, encontrando-o, exibe o conteúdo da célula adjacente (23 rublos) .Esquematicamente, a operação desta função pode ser representada assim:
Para facilitar o uso posterior da função, faça uma coisa de cada vez – dê o seu próprio nome ao intervalo de células na lista de preços. Para fazer isso, selecione todas as células da lista de preços, exceto o “cabeçalho” (G3: H19), selecione no menu Inserir - Nome - Atribuir (Inserir — Nome — Definir) ou pressione CTRL + F3 e digite qualquer nome (sem espaços) como Preço… Agora, no futuro, você pode usar esse nome para vincular à lista de preços.
Agora usamos a função VPR… Selecione a célula onde será inserido (D3) e abra a aba Fórmulas – Inserção de Função (Fórmulas — Inserir Função)… Na categoria Referências e matrizes (Pesquisa e Referência) encontre a função VPR (PROCV) e imprensa OK… Uma janela para inserir argumentos para a função aparecerá:
Nós os preenchemos por sua vez:
- Valor desejado (Valor de pesquisa) – o nome do produto que a função deve encontrar na coluna mais à esquerda da lista de preços. No nosso caso, a palavra “Maçãs” da célula B3.
- mesa (Matriz de Tabelas) – uma tabela da qual são retirados os valores desejados uXNUMXbuXNUMXbare, ou seja, nossa lista de preços. Para referência, usamos nosso próprio nome “Preço” fornecido anteriormente. Se você não deu um nome, você pode simplesmente selecionar a tabela, mas não esqueça de apertar o botão F4para fixar o link com cifrões, porque, caso contrário, ele deslizará para baixo ao copiar nossa fórmula para o restante das células na coluna D3:D30.
- Número_coluna (Número de índice da coluna) – número de série (não uma letra!) Da coluna na lista de preços da qual iremos obter os valores dos preços. A primeira coluna da lista de preços com os nomes é numerada 1, portanto, precisamos do preço da coluna numerada 2.
- pesquisa_intervalo (Pesquisa de alcance) – apenas dois valores podem ser inseridos neste campo: FALSE ou TRUE:
- Se um valor for inserido 0 or DEITADO (FALSO), então, na verdade, isso significa que apenas a pesquisa é permitida correspondência exata, ou seja, se a função não encontrar o item fora do padrão especificado na tabela de pedidos na lista de preços (se for inserido “Coco”, por exemplo), gerará o erro #N/A (sem dados).
- Se um valor for inserido 1 or VERDADEIRO (VERDADE), isso significa que você permite a pesquisa não pelo exato, mas correspondência aproximada, ou seja, no caso de “coco”, a função tentará encontrar um produto com um nome que seja o mais próximo possível de “coco” e retornará o preço para este nome. Na maioria dos casos, essa substituição aproximada pode enganar o usuário, substituindo o valor do produto errado que realmente estava lá! Portanto, para a maioria dos problemas reais de negócios, é melhor não permitir a pesquisa aproximada. A exceção é quando procuramos números e não texto – por exemplo, ao calcular descontos por etapas.
Tudo! Resta pressionar OK e copie a função inserida para a coluna inteira.
# N/A erros e sua supressão
função VPR (PROCV) retorna #N/D erro (#N / D) se um:
- Pesquisa exata habilitada (argumento Visualização de intervalo = 0) e o nome desejado não está em mesa.
- Pesquisa grosseira incluída (Visualização de intervalo = 1), Mas mesa, em que a pesquisa está ocorrendo não é classificada em ordem crescente de nomes.
- O formato da célula de onde vem o valor necessário do nome (por exemplo, B3 no nosso caso) e o formato das células da primeira coluna (F3: F19) da tabela são diferentes (por exemplo, numérico e texto ). Este caso é especialmente típico ao usar códigos numéricos (números de conta, identificadores, datas, etc.) em vez de nomes de texto. Neste caso, você pode usar as funções Ч и TEXTO para converter formatos de dados. Vai parecer algo assim:
=PROCV(TEXT(B3),preço,0)
Você pode ler mais sobre isso aqui.
- A função não pode encontrar o valor necessário porque o código contém espaços ou caracteres não imprimíveis invisíveis (quebras de linha, etc.). Neste caso, você pode usar funções de texto TRIM (APARAR) и IMPRESSÃO(LIMPAR) para removê-los:
=PROCV(TRIMESPAÇOS(LIMPAR(B3)),preço,0)
=PROCV(AJUSTAR(LIMPAR(B3));preço;0)
Para suprimir a mensagem de erro #N / D (#N / D) nos casos em que a função não pode encontrar uma correspondência exata, você pode usar a função SE ERRO (SE ERRO)… Então, por exemplo, esta construção intercepta quaisquer erros gerados pelo VLOOKUP e os substitui por zeros:
= SEERRO (PROCV (B3, preço, 2, 0), 0)
= SEERRO (PROCV (B3; preço; 2; 0); 0)
PS
Se você precisar extrair não um valor, mas todo o conjunto de uma só vez (se houver vários diferentes), terá que shamanizar com a fórmula de matriz. ou use o novo recurso XLOOKUP do Office 365.
- Uma versão melhorada da função VLOOKUP (VLOOKUP 2).
- Cálculo rápido de descontos por etapa (intervalo) usando a função PROCV.
- Como fazer um “VLOOKUP esquerdo” usando as funções INDEX e MATCH
- Como usar a função PROCV para preencher os formulários com dados da lista
- Como retirar não o primeiro, mas todos os valores da tabela de uma só vez
- Funções VLOOKUP2 e VLOOKUP3 do complemento PLEX