Conforme se aproxima de Setembro, posso contar com uma série de perguntas planilha. Um dos mais populares do Excel pedidos tutorial é como você olhar para um valor em um Excel planilha e usá-lo em outro Excel planilha. Por exemplo, você precisa traduzir um número de produto em nome de um produto. Uma das minhas favoritas Excel funções é a função PROCV e pode ajudar com esta tarefa.

Um caso recente envolveu alguns dados de recenseamento eleitoral que precisava analisar. Em um Excel folha de cálculo, título de eleitor, a parte foi listada como um valor alfanumérico chamado "Pcode" e não a partido político. Essa codificação não era intuitivo. Por exemplo, "D" foi para "American Independent Party", mas alguns pensaram que significa "Partido Democrata".

Como se pode agarrar o seu valor a partir de um separado Excel lista e inseri-lo em seu cálculo atual? VLOOKUP ou HLOOKUP é a resposta principal Excel .

Nós vamos mostrar-lhe Vlookup já que é o mais comumente usado dos dois.

Comece com uma tabela de referência ( Excel chama essa matriz_tabela a) - este é apenas uma pequena mesa (ou parte de uma planilha ou em uma planilha separada), que tem uma lista de valores emparelhados. Por exemplo:

• Os objectivos de vendas combinado com taxas de comissão.
• Quantidade comprados com taxa de desconto
• Estado com taxa de imposto de vendas
• Milhas voadas com os pontos ganhos de passageiro frequente.

Uma vez que você tem (relativamente) informações fixas, você pode usar VLOOKUP () para localizar a parte relevante da tabela e obter o valor que você precisa:

• total de vendas de pesquisa e obter as comissões a pagar
• Pesquisa quantidade comprada e obter algum desconto que se aplica
• Localizar estado do cliente e do exercício fiscal de venda, se é exigível
• Do ar milhas trabalho quantos pontos você obterá.

A função VLOOKUP é um membro do grupo de pesquisa e as funções de referência disponível no Excel . É uma função muito útil especialmente quando você tem uma lista de dados e informações adicionais podem ser obtidas a partir dessa lista, por referência a uma tabela de pesquisa. Os exemplos a seguir dar um exemplo prático de como Vlookup pode ser usado.

Visão global

VLOOKUP (valor_procurado, matriz_tabela, núm_índice_coluna, range_lookup) tem 4 argumentos:

• Valor_procurado é o valor que você fornecer na sua lista, por exemplo, vendas .. Vlookup compara o valor de pesquisa com os valores na coluna 1 da matriz tabela. Lookup valor é também chamado de "busca" de valor.
• Matriz_tabela é o endereço da tabela de pesquisa em si, por exemplo, E $ 2: $ F $ 5, (eu uso o endereçamento absoluto assim quando eu preencher para baixo a fórmula PROCV, o endereço da tabela não se move - a criação de respostas erradas).
• Núm_índice_coluna especifica qual coluna da tabela de pesquisa contém os dados para uso em nosso cálculo, por exemplo, Comm.Rate%, o que é coluna 2 da tabela.
• Finalmente, o valor range_lookup é um valor lógico (true / false), que determina se PROCV retornará uma correspondência exacta ou uma correspondência aproximada. Mais detalhes sobre o valor de pesquisa mais ampla sobre

O primeiro exemplo usa alguns dados Northwind familiar e mostra um exemplo simples de calcular as comissões de vendas com base nas vendas do empregado. Vlookup dá a taxa de comissão a ser aplicado aos resultados do empregado vendas.

A tabela de pesquisa é a pequena lista à direita - e Min.Sales Comm.Rate. Vlookup sempre usa a primeira coluna da tabela de pesquisa como referência para o cálculo, isto é, a partir das vendas como o valor de pesquisa que compara as vendas com Min.Sales e decide que Comm.Rate se aplica.

A fórmula na célula C2 Vlookup parecido com este:

= Vlookup (B2, $ E $ 2: $ F $ 5,2, TRUE) * B2

A fórmula divide da seguinte forma:

• usa B2 (vendas) como o valor de pesquisa, (bastante simples),
• queremos calcular comissões de vendas para que os nossos matriz tabela de referência $ E $ 2: $ F $ 5 é a base para isso.
• queremos agarrar a taxa de comissão de vendas e que na segunda coluna, por isso usamos o número índice da coluna 2, que significa que o PROCV olhar na coluna 2 da matriz da tabela para retornar a taxa de comissão correspondente.
• Queremos encontrar o valor das vendas, e procurar_intervalo for TRUE. Vlookup pesquisas coluna 1 da matriz tabela com o valor de pesquisa. O objectivo é encontrar o maior valor de uma coluna que é menor ou igual ao valor de pesquisa, ou seja, de vendas na coluna B.

Esse último parâmetro, a pesquisa de Gama, pode ser complicado. Existem três possibilidades para este valor.

• 1. significa TRUE PROCV procurar uma correspondência aproximada
  2. FALSE significa PROCV procurar uma correspondência exata
  3. valor omitido PROCV significa olhar para uma correspondência aproximada.

O jogo é inteligente para usar explicitamente TRUE ou FALSE, até que são usadas para como ele funciona. Como funcionam estes valores?

• FALSE, significa uma correspondência exata, usado principalmente com valores de texto. Vlookup simplesmente olha para baixo na coluna 1 e procura uma correspondência exata, se não encontrar um que retorna # N / A.
• TRUE instrui VLOOKUP para encontrar uma correspondência aproximada (que também está OK, se ele encontrar uma correspondência exacta). Vlookup olha para o valor mais elevado que seja igual ou inferior ao valor de pesquisa.

IMPORTANTE: Se a pesquisa intervalo é TRUE primeira coluna da matriz tabela deve ser classificada em ordem crescente, de pequeno a grande porte. (Caso contrário Vlookup pode retornar resultados inesperados).

Em nosso exemplo de vendas para Steven Buchanan é de R $ 22.000. Vlookup tem esse valor e vai para baixo "Min.Sales" procurando uma correspondência exata, se não houver nenhuma correspondência exata (o que não existe) Vlookup olha para o maior valor que é menos que (ou igual a) o valor de pesquisa. Neste caso célula E3 é o maior valor (US $ 15.000), que é inferior ao valor de pesquisa ($ 22.000), pois a função retorna Vlookup F3, que é uma Comm.Rate "de 10% . Observe que o último termo em nossa fórmula é B2 *. Isto multiplica as vendas de cada pessoa pela taxa de comissão encontrados pelos VLOOKUP. Assim, a comissão de Steven B é $ 22.000 * 10% = $ 2.200. Se usar a alça de preenchimento para copiar a fórmula para baixo a célula C10, vamos ter calculado as comissões para todos os funcionários Northwind.

Copiando a função PROCV para outras células

Não faz sentido usar VLOOKUP para uma célula no seu Excel planilha. Em vez disso, eu quero copiar a função para outras células na mesma coluna.

Para copiar células da coluna para PROCV outros,

1. Clique na célula que contém os argumentos VLOOKUP. No nosso exemplo, isso seria D2.

2. Pegue o celular que exibe alça no canto inferior direito.

3. À esquerda, clique e arraste a célula identificador para cobrir o intervalo da coluna.

Nota: Se eu não tivesse mudado a referência absoluta, como mencionado no artigo 3 º, eu vi minha mesa mudança entrada array em uma célula como arrastado por outras células.

PROCV é um poderoso Excel função que pode alavancar os dados da planilha de outras fontes. Há muitas maneiras que você pode se beneficiar dessa função. Neste exemplo, usei a tradução do código de 1:1, mas você também pode usá-lo para trabalhos de grupo. Por exemplo, eu poderia atribuir códigos de estado para uma região como a CT, VT e MA para uma região chamada de "Nova Inglaterra". E para os mais aventureiros, você pode usar PROCV em suas fórmulas.