Conteúdo
Temos duas tabelas (por exemplo, as versões antiga e nova da lista de preços), que precisamos comparar e encontrar rapidamente as diferenças:
É imediatamente claro que algo foi adicionado à nova lista de preços (tâmaras, alho …), algo desapareceu (amoras, framboesas …), os preços mudaram para alguns bens (figos, melões …). Você precisa encontrar e exibir rapidamente todas essas alterações.
Para qualquer tarefa no Excel, quase sempre há mais de uma solução (geralmente 4-5). Para o nosso problema, muitas abordagens diferentes podem ser usadas:
- função VPR (PROCV) — procure nomes de produtos da nova lista de preços na antiga e exiba o preço antigo ao lado do novo e, em seguida, veja as diferenças
- mesclar duas listas em uma e, em seguida, construir uma tabela dinâmica com base nela, onde as diferenças serão claramente visíveis
- usar o suplemento do Power Query para Excel
Vamos colocá-los todos em ordem.
Método 1. Comparando tabelas com a função PROCV
Se você não estiver familiarizado com esse recurso maravilhoso, primeiro olhe aqui e leia ou assista a um tutorial em vídeo sobre ele - salve-se alguns anos de vida.
Normalmente, essa função é usada para extrair dados de uma tabela para outra, correspondendo a algum parâmetro comum. Nesse caso, vamos usá-lo para empurrar os preços antigos para o novo preço:
Esses produtos, contra os quais o erro #N/A resultou, não estão na lista antiga, ou seja, foram adicionados. As mudanças de preço também são claramente visíveis.
Prós este método: simples e claro, “clássico do gênero”, como se costuma dizer. Funciona em qualquer versão do Excel.
Desvantagens também está lá. Para pesquisar produtos adicionados à nova tabela de preços, você terá que fazer o mesmo procedimento no sentido contrário, ou seja, puxar novos preços para o preço antigo com a ajuda de VLOOKUP. Se os tamanhos das tabelas mudarem amanhã, as fórmulas terão que ser ajustadas. Bem, e em mesas muito grandes (> 100 mil linhas), toda essa felicidade diminuirá decentemente.
Método 2: comparando tabelas usando um pivô
Vamos copiar nossas tabelas uma por baixo da outra, adicionando uma coluna com o nome da lista de preços, para que depois você possa entender de qual lista qual linha:
Agora, com base na tabela criada, vamos criar um resumo através Inserir – Tabela Dinâmica (Inserir - Tabela Dinâmica). Vamos jogar um campo Produto para a área de linhas, campo Preço para a área e o campo da coluna Цena no intervalo:
Como você pode ver, a tabela dinâmica gerará automaticamente uma lista geral de todos os produtos das listas de preços antigas e novas (sem repetições!) e classificará os produtos em ordem alfabética. Você pode ver claramente os produtos adicionados (eles não têm o preço antigo), os produtos removidos (eles não têm o novo preço) e as alterações de preço, se houver.
Os totais gerais em tal tabela não fazem sentido e podem ser desativados na guia Construtor – totais gerais – desabilitar para linhas e colunas (Projeto - Totais Gerais).
Se os preços mudarem (mas não a quantidade de mercadorias!), basta atualizar o resumo criado clicando com o botão direito do mouse nele – revisar.
Prós: essa abordagem é uma ordem de magnitude mais rápida com tabelas grandes do que VLOOKUP.
Desvantagens: você precisa copiar manualmente os dados um do outro e adicionar uma coluna com o nome da lista de preços. Se os tamanhos das tabelas mudarem, você terá que fazer tudo de novo.
Método 3: comparando tabelas com o Power Query
O Power Query é um suplemento gratuito para o Microsoft Excel que permite carregar dados no Excel de praticamente qualquer fonte e, em seguida, transformar esses dados da maneira desejada. No Excel 2016, este suplemento já está incorporado por padrão na guia Data (Dados), e para o Excel 2010-2013 você precisa baixá-lo separadamente do site da Microsoft e instalá-lo - obtenha uma nova guia Consulta de energia.
Antes de carregar nossas listas de preços no Power Query, elas devem primeiro ser convertidas em tabelas inteligentes. Para fazer isso, selecione o intervalo com dados e pressione a combinação no teclado Ctrl+T ou selecione a guia na faixa de opções Home – Formatar como uma tabela (Início — Formatar como Tabela). Os nomes das tabelas criadas podem ser corrigidos na aba Construtor (vou deixar o padrão tabela 1 и tabela 2, que são obtidos por padrão).
Carregue o preço antigo no Power Query usando o botão Da tabela/intervalo (Da tabela/intervalo) da guia Data (Encontro: Data) ou da aba Consulta de energia (dependendo da versão do Excel). Após o carregamento, retornaremos ao Excel do Power Query com o comando Fechar e carregar – Fechar e carregar… (Fechar e carregar — Fechar e carregar para…):
… e na janela que aparece, selecione Basta criar uma conexão (Somente conexão).
Repita o mesmo com a nova lista de preços.
Agora vamos criar uma terceira consulta que irá combinar e comparar os dados das duas anteriores. Para fazer isso, selecione no Excel na guia Dados – Obter Dados – Combinar Solicitações – Combinar (Dados — obter dados — mesclar consultas — mesclar) ou pressione o botão Combinar (Mesclar) aba Consulta de energia.
Na janela de junção, selecione nossas tabelas nas listas suspensas, selecione as colunas com os nomes das mercadorias e, na parte inferior, defina o método de junção - Externo completo (Completo Externo):
Depois de clicar em OK uma tabela de três colunas deve aparecer, onde na terceira coluna você precisa expandir o conteúdo das tabelas aninhadas usando a seta dupla no cabeçalho:
Como resultado, obtemos a mesclagem de dados de ambas as tabelas:
É melhor, é claro, renomear os nomes das colunas no cabeçalho clicando duas vezes nas mais compreensíveis:
E agora o mais interessante. Ir para a aba Adicionar coluna (Adicionar coluna) e clique no botão Coluna condicional (Coluna Condicional). E então, na janela que se abre, insira várias condições de teste com seus valores de saída correspondentes:
Resta clicar em OK e carregue o relatório resultante para o Excel usando o mesmo botão fechar e baixar (Fechar e carregar) aba INÍCIO (Home):
Beleza.
Além disso, se ocorrerem alterações nas listas de preços no futuro (linhas são adicionadas ou excluídas, os preços mudam, etc.), basta atualizar nossas solicitações com um atalho de teclado Ctrl+outro+F5 ou por botão Atualize tudo (Atualize tudo) aba Data (Encontro: Data).
Prós: Talvez a maneira mais bonita e conveniente de todas. Funciona de forma inteligente com mesas grandes. Não requer edições manuais ao redimensionar tabelas.
Desvantagens: requer que o suplemento Power Query (no Excel 2010-2013) ou Excel 2016 seja instalado. Os nomes das colunas nos dados de origem não devem ser alterados, caso contrário, obteremos o erro “Coluna tal e tal não foi encontrada!” ao tentar atualizar a consulta.
- Como coletar dados de todos os arquivos do Excel em uma determinada pasta usando o Power Query
- Como encontrar correspondências entre duas listas no Excel
- Mesclando duas listas sem duplicatas