Excel – Usando o PROCV para comparar duas listas

Passo 1: Existem poucas regras, então verificamos que estes primeiros

O cliente havia enviado uma base muito planilha do Excel que mostrou uma lista dos seus registos de pagamentos pela ordem de compra, número da nota fiscal, o valor e a data.

the administrator (let’s call him Guy) then generated an Excel report out of his company’s accounts system. Em seguida, passamos a usar ambas as planilhas do Excel para fazer a nossa reconciliação.

no entanto, antes de podermos começar a usar a função VLOOKUP eu expliquei as complexidades de colocar os seus dados em um formato que irá garantir os trabalhos VLOOKUP.

Excel precisava do seguinte:

• dentro do livro de trabalho do cliente e do livro de trabalho do Guy precisava haver pelo menos um pedaço de Informação correspondente para cada registro. Por exemplo, ambos os workbooks tinham o número de fatura e o número de ordem de compra para cada registro de transação. Decidimos usar o número da factura no livro de trabalho do Guy para localizar uma correspondência com o mesmo número da factura no livro de trabalho do cliente. Esta informação é chamada de “valor de pesquisa”.
• o valor de pesquisa deve ser único, ou seja, não pode ser repetido na lista de clientes várias vezes. Felizmente para o Guy só havia um registo por cada factura. Em negócios fatura e números PO são quase sempre exclusivo para uma única transação, então eles são excelentes para usar como valores de pesquisa. Outros exemplos são códigos de produtos, números de cliente ou de conta, números de empregados ou códigos de departamento. É sempre uma boa prática verificar que não existem duplicados antes de realizar o seu VLOOKUP.
• o valor de pesquisa precisava ser localizado na primeira coluna da lista no manual do cliente. Infelizmente não foi. estava localizado na segunda coluna (coluna B), então cortamos e colamos a coluna na frente do p / o No. coluna. Esta é uma regra importante ao usar a função VLOOKUP. Se o valor da pesquisa não estiver na primeira coluna, a pesquisa não funcionará.

uma vez que tínhamos decidido sobre o nosso valor de pesquisa e reorganizado a lista no manual do cliente estávamos prontos para criar o nosso VLOOKUP.

Passo 2: Inserir a função VLOOKUP

o ‘V’ em VLOOKUP significa “vertical”. Uma pesquisa vertical é usada para procurar dados específicos na primeira coluna de uma tabela de dados. Uma vez que ele encontra a linha que contém os dados que você está procurando, em seguida, salta para outra coluna na mesma tabela de dados e retorna informações a partir dele.

decidimos inserir a função VLOOKUP no livro de trabalho de Guy. Dessa forma, poderíamos usar a função para referenciar o número de fatura de sua lista e verificar para o mesmo número de fatura na primeira coluna da lista de clientes. Se ele encontrou uma correspondência, então retornaria para a cela a quantidade que o cliente tinha contra o número da fatura. Nós acabaríamos então com a quantidade de Guy e a quantidade do cliente na mesma folha de trabalho. Poderíamos então facilmente comparar os dois e descobrir quais transações não coincidiam.

criámos uma nova coluna intitulada “registo do cliente”, colocando-a no fim da lista existente de Guy. Colocando o selector de células na célula E5, inserimos a função VLOOKUP.

para inserir a função clicámos no tab Formulas e, em seguida, a partir do Grupo biblioteca de funções clicámos no botão Inserir Função.

Tip: a função VLOOKUP também pode ser inserida clicando no botão Inserir Função na barra de fórmulas.

do select a category box we selected Lookup and Reference. Na lista de funções selecionamos o VLOOKUP e clicamos em OK.

neste momento, era necessário introduzir os valores apropriados nas caixas de argumentos de função. Passo-te cada uma das discussões.

• Lookup_value – este foi o número da fatura na célula C5. Uma vez que inserimos isso na caixa de valores de pesquisa, o Excel manteve-o na memória e esperou por nós para completar os argumentos restantes.

• Table_array-a tabela array é a lista que contém os detalhes da transação na planilha do cliente. O VLOOKUP irá procurar uma correspondência para o ‘valor de pesquisa’ na primeira coluna da lista de tabelas. O tipo navegou até à folha de trabalho do cliente e depois clicou e arrastou toda a área que continha todos os detalhes das transacções. Ele incluiu a coluna “Data”, mas A4: C26 teria sido bom também como a coluna “Quantidade” está dentro desta faixa. Por favor, note que Guy fez o intervalo absoluto para que a fórmula poderia facilmente ser copiada.

• Col_index_num – esta é a coluna a partir da qual o tipo queria obter o valor da factura do cliente. Esta era a coluna 3 da lista de transacções do cliente. As colunas são numeradas da esquerda para a direita, sendo a primeira coluna no intervalo table array a coluna 1. Tenha cuidado para não confundir os números das colunas e as letras das colunas. Mesmo que a quantidade da fatura seja mantida na coluna ‘C’ Excel só quer a localização do número de índice da coluna, não a letra da coluna.

• Range_lookup-a pesquisa do intervalo controla se está à procura de uma correspondência exacta com o seu ‘valor de pesquisa’. Eu expliquei a Guy que se ele entrou verdadeiro ou deixou a caixa vazio Excel iria procurar por seu número de fatura, mas se ele não conseguiu encontrar uma correspondência, ele retornaria o próximo maior valor que é menos do que o número da fatura. Por exemplo, se não conseguisse encontrar a factura 2466, encontraria o registo de 2465 e devolveria o montante por isso. Para isso, para trabalhar a lista de clientes precisava ser classificado pelo número de fatura em primeiro lugar. No entanto, se ‘FALSE’ for inserido na caixa, o Excel irá procurar uma correspondência exata para o valor de pesquisa. O tipo queria uma correspondência exacta para o número da factura, por isso inseriu falso na caixa. Eu também passei na dica que você pode inserir 0 (zero) no lugar da palavra ‘falso’ e o Excel fará a mesma coisa.

com todos os argumentos introduzidos Guy clicou OK.

Excel então passou por cada um dos valores fornecidos na caixa de argumentos. A função usou o número de fatura (valor de pesquisa) para procurar um número de fatura correspondente na coluna esquerda-a maioria dos registros do cliente (tabela array). Uma vez que o número da fatura foi encontrado, então pulou através do número especificado de colunas (número de índice da coluna) na mesma linha para devolver o montante da fatura.

felizmente, como pode ver abaixo, o cliente tinha o mesmo valor de factura que Guy.

Guy então copiou a função VLOOKUP para todas as transações da fatura.
dentro de segundos ele poderia ver três erros #N / A (valor não disponível erros). Isto mostrou onde o cliente não tinha um registro dessas faturas. Ele, então, rapidamente encontrou as diferenças nos montantes da fatura dele e do cliente. Imediatamente ele pôde ver que a fatura 2464 era curto $100,00, muito provavelmente um erro de Digitação.

Passo 3: o double-check

nós tínhamos agora comparado com sucesso os nossos registros com os do cliente. no entanto, como um double check nós decidimos então fazer uma comparação a partir dos registros do cliente, apenas no caso de termos perdido alguma coisa.

desta vez criamos o VLOOKUP na folha de trabalho do cliente. A primeira coisa que precisávamos fazer era mover a coluna de fatura para que fosse a primeira coluna em nossa lista de transações (tabela Array). Depois de fazer isso, inserimos a função VLOOKUP em uma nova coluna chamada “nosso registro”.

Guy copiou a função para baixo e, em seguida, criou a coluna “comparação”. O nosso duplo cheque confirmou que o cliente tinha pago 100 dólares na factura 2464. Nosso cheque também mostrou que o cliente tinha bastante possível registrado o número de fatura 2466 como 2646.

Conclusion

Guy could now go back to the customer to discuss the information that may have been keyed in error and to supply copies of the factures that the customer didn’t have a record of receiving.

Escusado será dizer que a função VLOOKUP salvou Guy, e seus colegas de trabalho, uma enorme quantidade de tempo. O Gerente de treinamento e desenvolvimento contactou-me após o treinamento para dizer que a eficiência melhorada no departamento tinha salvado a sua empresa 10s de 1000s de dólares.

cobrimos a função VLOOKUP em nossos cursos de Fase 3 do Excel. Se você está lidando com grandes dados este treinamento é definitivamente para você.