Hoje, uma técnica antiga chamada Consolidação de Dados. Se você tiver que combinar dados de várias planilhas ou pastas de trabalho, a consolidação pode lidar com o trabalho se seus dados estiverem em um formato específico. Aprenda mais sobre esta ferramenta ancestral.
Existem duas ferramentas de consolidação antigas no Excel.
Para entendê-los, diga que você tem três conjuntos de dados. Cada um tem nomes no lado esquerdo e meses na parte superior. Observe que os nomes são diferentes e há um número diferente de meses em cada conjunto de dados.
Você deseja combiná-los em um único conjunto de dados. Vire a página para uma discussão dos dois métodos.
A primeira ferramenta é o comando Consolidar na guia Dados. Escolha uma seção em branco da pasta de trabalho antes de iniciar o comando. Use o botão RefEdit para apontar para cada um dos seus conjuntos de dados e clique em Adicionar. No canto inferior esquerdo, escolha Linha superior e Coluna esquerda.
Ao clicar em OK, um superconjunto de todos os três conjuntos de dados é produzido. A primeira coluna contém qualquer nome em qualquer um dos três conjuntos de dados. A linha 1 contém qualquer mês em qualquer conjunto de dados.
Na figura acima, observe três incômodos. A célula A1 é sempre deixada em branco. Os dados em A não são classificados. Se uma pessoa estava faltando em um conjunto de dados, as células são deixadas vazias em vez de serem preenchidas com 0.
Preencher a célula A1 é bastante fácil. A classificação por nome envolve o uso do Flash Fill para obter o sobrenome na coluna N. Veja como preencher células em branco com 0:
- Selecione todas as células que devem ter números: B2: M11.
- Selecione Home, Find & Select, Go To Special.
- Escolha espaços em branco e clique em OK. Você ficará com todas as células em branco selecionadas.
-
Digite 0 e Ctrl + Enter.
Vá para o especial
O resultado: um relatório de resumo bem formatado.
Assistir vídeo
- Consolidar é um recurso antigo do Excel
- Especifique vários intervalos para consolidar
- Use rótulos na linha superior e coluna esquerda
- Aborrecimentos: A1 está sempre em branco, a coluna A não está classificada, espaços em branco nos dados
- Vá para Special, Blanks, 0, Ctrl + Enter
- Consolidar pode apontar para pastas de trabalho externas
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2046 - Consolidar planilhas!
Estarei fazendo um podcast com todas as minhas dicas deste livro, clique no “i” no canto superior direito para acessar a lista de reprodução!
Tudo bem, eu tenho um truque antigo incrível e incrível. Eu tenho três conjuntos de dados aqui, Q1, Q2, Q3, todos eles têm uma forma semelhante e têm nomes no lado esquerdo, meses passando no topo, mas não exatamente a mesma forma. O primeiro trimestre tem janeiro-fevereiro-março, o segundo trimestre tem 5 meses, acho que ficamos preguiçosos e não descobrimos como fazer isso no final de junho e alguém finalmente fez isso no final de agosto, e o quarto trimestre tem 4 meses. Tudo bem, o primeiro vai A: D, o segundo A: F, o terceiro A: E. Nomes diferentes, alguns dos nomes são iguais, como se Michael Seeley estivesse em todos eles, mas outros nomes vêm e vão. Certo, esse é um recurso incrível, existe desde sempre. Lembro-me de ter feito isso em 1995, até mesmo, Data, Consolidates!
Tudo bem, vamos usar a função SUM, nunca usei nenhuma das outras, mas acho que estão aí. Função SUM, a primeira coisa que faremos é voltar para Q1 e apontar para este intervalo, essas quatro colunas, clicar em Adicionar e ir para Q2, selecionar essas colunas, clicar em Adicionar e, em seguida, Q4, selecionar essas colunas . Tudo bem, marque esta caixa para Usar rótulos na linha superior e na coluna Esquerda; o botão Procurar significa que esses conjuntos de dados podem estar em pastas de trabalho diferentes! Crie links para dados de origem, vamos falar sobre isso no final. Quando eu clicar em OK, eles obterão todos os nomes que estão em qualquer uma das 3 listas, os meses lá em qualquer uma das 3 listas, e agora temos este superconjunto incrível, certo, aborrecimentos!
Este é um ótimo recurso, mas aqui estão as coisas que me incomodaram. Eles não me dão o rótulo em A1, eles não se preocupam em ordenar os dados que estão caindo, e se alguém não tinha um registro no primeiro, eles me dão espaços em branco em vez de zeros. Tudo bem, para preencher os espaços em branco com zeros, Home, Find and Select, Go To Special, escolha os Blanks, clique em OK, digite um zero, Ctrl + Enter irá preenchê-los. Fácil o suficiente para classificar os dados, Data, AZ e vai classificar os dados, certo. Criar links, caramba, nunca dá certo, tudo bem, criar links, para o Criar Links funcionar, tem que estar em uma pasta de trabalho externa. Tudo bem, então clicarei em Procurar aqui, criei uma pasta de trabalho chamada OtherWorkbook e os dados estão em A1: D7, clique em Adicionar, certo, e aí está o primeiro. Os próximos dados estão em G1: L8, então irei Procurar no OutroWorkbook, G1: L8,clique em Adicionar, certo. Portanto, agora tenho duas referências a outras pastas de trabalho, linha superior, coluna esquerda, criar links para dados de origem. A ajuda do Excel diz que, depois de usar Criar links para os dados de origem, você nunca mais poderá editar esses intervalos, clique novamente em OK e aqui está o que obtemos.
Tudo bem, em primeiro lugar, parece que nos deu os resultados, deu-nos os resultados, mas há uma coluna B extra aqui e temos Grupo e Esboço. E quando vamos para a visualização número 2, ah. Então, idealmente, o que você teria é, você teria uma pasta de trabalho chamada janeiro e outra pasta de trabalho chamada fevereiro, e isso vai mostrar aqui está janeiro, aqui é fevereiro. Aqui está o total para Mike Seeley, essas são fórmulas que apontam para essas vendas, e aqui está a soma dessas duas, certo, é estranho.
Se você realmente usa isso o tempo todo, quero ouvir de você nos comentários do YouTube, tenho certeza de que há uma maneira mais fácil de fazer isso para mim, nunca fiz isso na minha vida, bem, uma vez antes de hoje e depois hoje, só para que eu pudesse explicar, certo. Consolide, porém, quando consolidarmos as planilhas da pasta de trabalho atual, truque incrível. Amanhã vamos comparar o intervalo de consolidação múltipla, tabelas dinâmicas, mas todos esses truques estão no livro, clique no “i” no canto superior direito para chegar a esse livro.
Consolidar, antigo, antigo recurso no Excel, você especifica vários intervalos para consolidar, sempre marquei a caixa para a linha superior e coluna esquerda. Os resultados são excelentes, mas A1 está em branco, a coluna A não está classificada e há espaços em branco nos dados, use Ir para espaços em branco especiais, digite 0, Ctrl + Enter para preencher esses espaços. E então aquele exemplo final, consolidar pode apontar para pastas de trabalho externas, pode ser útil!
Certo ei, quero agradecer pela visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2046.xlsm
