O que você fará se precisar criar uma tabela dinâmica e seus dados estiverem em planilhas diferentes? Com o Excel 2013 instalado, você tem uma maneira fácil de fazer isso. Existe uma tecnologia chamada Modelo de Dados e ela usa relacionamentos de dados da mesma forma que um banco de dados.

Neste tutorial, vou mostrar tudo para criar uma Tabela Dinâmica no Excel 2013 a partir de dados em várias planilhas usando o Modelo de Dados.

Videoclipe

Se quiser seguir o tutorial usando seu próprio arquivo Excel, você pode fazer isso. Ou baixe o arquivo zip para este tutorial, que contém um livro de amostra Pivot Consolidate.xlsx.

Exploração de dados

Existem três planilhas neste livro: informações do cliente, informações do pedido e informações de pagamento.

Clique na folha Informação do Cliente e certifique-se de que contém os números do pedido, o nome e o status dos clientes.

Folha de informações do cliente

Clique na folha Informações do pedido e veja se ele contém os números do pedido, bem como os campos do mês, os itens pedidos e se esses produtos são orgânicos.

Folha de informações do pedido

Vá para a guia Informações de pagamento e verifique se ela contém os números dos pedidos, o valor em dólares de cada venda, o método de pagamento e como um cliente novo ou existente faz um pedido.

Folha de informações de pagamento

Ao conectar todas essas planilhas no painel de tarefas da Tabela Dinâmica, podemos selecionar os dados de cada planilha. Como os números de pedido existem em todas as três folhas, eles se tornarão pontos de conexão. Isso é o que o banco de dados chama chave primária. Atenção: não é necessário ter uma chave primária, mas reduz a chance de erro.

Criação de tabelas nomeadas

Antes de criar uma tabela dinâmica, vamos criar uma tabela de cada folha.

Clique de volta para Tabela do Cliente e clique em qualquer lugar da área de dados. Vá para a guia Inserir na barra de fita e clique no ícone Tabela.

Converta os dados na planilha, escolhendo Inserir> Tabela

A caixa de diálogo Criar Tabela identifica corretamente a área da tabela. A caixa de seleção na parte inferior também deve identificar que a primeira linha da tabela é para cabeçalhos. (Caso contrário, escolha esta opção.)

A caixa de diálogo Criar Tabela deve adivinhar corretamente a área de dados

Clique em OK e agora você tem uma tabela com movimentos alternados e botões de filtro. Você pode clicar dentro para desmarcá-lo se quiser ver melhor (apenas não clique fora da tabela). O painel da faixa de opções também exibe uma guia Projeto para a mesa. No lado esquerdo da fita na caixa Nome da tabela o nome temporário Table1 é exibido. Exclua isso e nomeie-o Informação do Cliente(use um sublinhado em vez de um espaço). Clique em Digitar.

Aplique o nome a cada tabela

Repita essas etapas para as planilhas Informações do pedido e Informações sobre pagamento. Nomeie as tabelas Order_Info e Informação de pagamento.

Agora estamos prontos para inserir a Tabela Dinâmica.

Inserindo uma Tabela Dinâmica

Certifique-se de que o cursor esteja em algum lugar da tabela na planilha de Informações de pagamento. Volte para a guia Inserir fita e clique no ícone Tabela Dinâmica(este é o primeiro ícone).

Use o cursor dentro de uma das tabelas para selecionar Inserir> Tabela Dinâmica

Na caixa de diálogo que aparece, você precisa definir corretamente a tabela e selecionar para que a Tabela Dinâmica vá para uma nova planilha. Marque a caixa na parte inferior Adicione esses dados ao modelo de dados... Clique em OK.

Adicionar dados ao modelo de dados é o que faz as conexões funcionarem

Agora você terá uma Tabela Dinâmica em uma nova planilha, a barra de tarefas aparecerá no lado direito da tela e a guia Analisar aparecerá na faixa de opções.

A barra de tarefas exibe a tabela e os campos apenas da planilha ativa, então clique TUDO para ver todas as tabelas que você criou. Mas antes de podermos usá-los, temos que ligá-los um ao outro, o que significa criar um relacionamento. Clique no botão Relacionamentos no painel da faixa de opções.

Configurando relacionamentos de mesa

Clicar neste botão exibe a caixa de diálogo Gerenciar Relacionamentos. Clique no botão Novo e a caixa de diálogo Criar Relacionamento é exibida. Criaremos dois relacionamentos usando o campo Order # como um conector.

Nas listas suspensas, selecione Informação de pagamento para a mesa e, ao lado dela, selecione Pedido # na lista suspensa Coluna. Na segunda linha, selecione Informação do Cliente na lista suspensa Tabela Relacionada e, ao lado dela, selecione Pedido # na lista suspensa Coluna relacionada.

Existem três tabelas, então crie dois relacionamentos

Isso significa que as tabelas Payment_Info e Customer_Info estão relacionadas entre si pelo mesmo número de pedido.

Clique no botão OK e veremos esses relacionamentos listados na janela Gerenciar relacionamentos.

Repita esse processo para criar um relacionamento entre Payment_Info e Order_Info, também usando o campo Order #. Agora, a janela Gerenciar relacionamentos tem a seguinte aparência:

A caixa de diálogo Gerenciar relacionamentos exibe os relacionamentos que você está criando

Observe que não há necessidade de criar um relacionamento entre as tabelas Order_Info e Customer_Info, pois elas são unidas automaticamente por meio da tabela Payment_Info.

Clique no botão Perto na parte inferior da janela. Agora podemos finalmente arrastar os campos para a Tabela Dinâmica.

Inserindo campos na tabela dinâmica

Na seção TODOS da barra de tarefas, clique nas pequenas setas para expandir as três tabelas e ver seus campos. Arraste os campos na área da Tabela Dinâmica da seguinte forma:

• Estado e Mês em linhas
• produtos em colunas
• $ Venda em valores
• Status em filtros

Arraste os campos de cada uma das três tabelas para a Tabela Dinâmica

Agora você pode usá-lo e modificá-lo como qualquer outra tabela dinâmica.

Conclusão

Usando nova função Modelo de dados de objeto no Excel 2013, você pode selecionar caixas rosa de várias planilhas para criar uma única tabela dinâmica. Lembre-se de que as linhas de cada tabela devem estar relacionadas entre si de alguma forma. É mais provável que você tenha sucesso quando as tabelas compartilham um campo comum com valores exclusivos.

Se você está procurando boas maneiras envio de seus dados, Envato Market tem uma boa escolha bem como para transformar Dados do Excel para formatos da web e vice-versa.

Este artigo mostrará como combinar rapidamente dados de duas tabelas do Excel quando colunas chave sem correspondências exatas. Por exemplo, quando o identificador exclusivo da primeira tabela são os primeiros cinco caracteres do identificador da segunda tabela. Todas as soluções propostas neste artigo foram testadas por mim no Excel 2013, 2010 e 2007.

Portanto, há duas planilhas do Excel que precisam ser combinadas para posterior análise de dados. Suponha que uma tabela contenha preços (a coluna Preço) e descrições das mercadorias (a coluna Cerveja) que você está vendendo, e a segunda contém dados sobre a disponibilidade de mercadorias no depósito (a coluna Em estoque). Se você ou seus colegas compilaram as duas tabelas no catálogo, ambas devem conter pelo menos uma coluna-chave com identificadores exclusivos de mercadorias. A descrição ou o preço do produto podem mudar, mas o identificador exclusivo sempre permanece o mesmo.

As dificuldades começam quando você pega algumas tabelas do fabricante ou de outros departamentos da empresa. O assunto pode se tornar ainda mais complicado se for introduzido repentinamente novo formato identificadores exclusivos ou as menores alterações de SKU. E você se depara com a tarefa de unir em Excel novo e a velha tabela de dados. De uma forma ou de outra, surge uma situação quando nas colunas-chave há apenas uma correspondência parcial de registros, por exemplo, “ 12345 " e " 12345-new_suffix“. Está claro para você que este é o mesmo SKU, mas o computador não é tão perspicaz! Esta não é uma correspondência exata torna impossível o uso regular Fórmulas do Excel para combinar dados de duas tabelas.

E o que é realmente ruim - as correspondências podem ser completamente confusas, e “ Alguma empresa"Em uma mesa pode se tornar" CJSC "Alguma Empresa""Em outra mesa, e" Nova empresa(anteriormente Some Company)" e " Empresa antiga”Também será um registro sobre a mesma empresa. Você sabe disso, mas como o Excel pode explicar isso?

Comente: As soluções descritas neste artigo são universais. Você pode adaptá-los para uso futuro com quaisquer fórmulas padrão, como PROCV(PROCV), PROCURAR(PARTIDA), GPR(HLOOKUP) e assim por diante.

Selecione o exemplo apropriado para ir diretamente para a solução desejada:

Uma coluna chave em uma das tabelas contém caracteres adicionais

Considere duas tabelas. As colunas da primeira tabela contêm o SKU, o nome da cerveja (Cerveja) e seu preço (Preço). A segunda tabela contém o SKU e o número de garrafas em estoque (em estoque). Em vez de cerveja, pode haver qualquer produto e o número de colunas em Vida real poderia haver muito mais.

Crie uma coluna auxiliar na tabela com caracteres adicionais. Você pode adicioná-lo ao final da tabela, mas é melhor inseri-lo próximo à direita após a coluna-chave para que fique visível.

A chave na tabela em nosso exemplo é a coluna UMA com os dados SKU, e você precisa extrair os primeiros 5 caracteres deles. Vamos adicionar uma coluna auxiliar e nomeá-la Auxiliar SKU:

Preparar! Agora temos colunas-chave de correspondência exata - coluna Auxiliar SKU na tabela principal e coluna SKU na tabela onde será realizada a pesquisa.

Agora usando a função PROCV(PROCV) obtemos o resultado desejado:

Outras fórmulas

• Extraia o primeiro NS caracteres à direita: por exemplo, 6 caracteres à direita de “DSFH-164900”. A fórmula será semelhante a esta:

  DIREITO (A2, 6)
  = DIREITO (A2,6)

• Pule o primeiro NS caracteres, extraia o seguinte Y caracteres: por exemplo, você precisa extrair “0123” do registro “PREFIX_0123_SUFF”. Aqui, precisamos pular os primeiros 8 caracteres e extrair os próximos 4 caracteres. A fórmula será semelhante a esta:

  PSTR (A2; 8; 4)
  = MID (A2,8,4)

• Extraia todos os caracteres até o delimitador, o comprimento da sequência resultante pode ser diferente. Por exemplo, você precisa extrair “123456” e “0123” das entradas “123456-sufixo” e “0123-sufixo”, respectivamente. A fórmula será semelhante a esta:

  ESQUERDA (A2; ENCONTRAR ("-"; A2) -1)
  = ESQUERDA (A2, ENCONTRAR ("-", A2) -1)

Em suma, você pode usar tais Funções do Excel, Como as LEVSIMV(DEIXOU), CERTO(CERTO), PSTR(MID), ENCONTRAR(FIND) para recuperar qualquer parte do índice composto. Se você tiver alguma dificuldade com isso, faremos o possível para ajudá-lo.

Os dados da coluna-chave na primeira tabela são divididos em duas ou mais colunas na segunda tabela

Suponha que a tabela que está sendo pesquisada contenha uma coluna com identificadores. As células desta coluna contêm registros do formulário XXXX-YYYY, Onde XXXXÉ a designação do código de um grupo de bens ( Celulares, TVs, filmadoras, câmeras) e AAAA- Este é o código do produto dentro do grupo. A tabela principal consiste em duas colunas: uma contém os códigos do grupo de produtos (Grupo), a segunda contém os códigos dos produtos (ID). Não podemos simplesmente descartar os códigos de grupos de produtos, uma vez que o mesmo código de produto pode ser repetido em grupos diferentes.

Em uma cela C2 escrevemos a seguinte fórmula:

CONCATENAR (A2; "-"; B2)
= CONCATENAR (A2, "-", B2)

Aqui A2- este é o endereço da célula que contém o código do grupo; símbolo " – ”É um separador; B2É o endereço da célula que contém o código do produto. Vamos copiar a fórmula para o resto das linhas.

Agora é fácil combinar dados de nossas duas tabelas. Iremos combinar a coluna ID completa primeira tabela com coluna EU IA segunda mesa. Se uma correspondência for encontrada, as entradas das colunas Descrição e Preço a segunda tabela será adicionada à primeira tabela.

Os dados nas colunas-chave não correspondem

Aqui está um exemplo: você é o proprietário de uma pequena loja, recebendo mercadorias de um ou mais fornecedores. Cada um deles possui uma nomenclatura própria, que difere da sua. Como resultado, há situações em que seu registro “Case-Ip4S-01” corresponde ao registro “SPK-A1403” em Arquivo Excel recebido do fornecedor. Essas discrepâncias surgem aleatoriamente e não há regra geral para converter automaticamente “SPK-A1403” em “Case-Ip4S-01”.

Más notícias: Os dados contidos nestes dois Planilhas do Excel, terá que ser processado manualmente para que seja possível combiná-los no futuro.

Boas notícias: Você só precisa fazer isso uma vez, e a tabela auxiliar resultante pode ser salva para uso futuro. Então você pode combinar essas tabelas automaticamente e, assim, economizar muito tempo.

1. Crie uma tabela de pesquisa auxiliar.

Crie um novo Planilha de Excel e chamá-lo Conversor SKU... Copie a coluna inteira Our.SKU da folha Armazenar para uma nova página, remova duplicatas e deixe apenas valores exclusivos nela.

Adicione uma coluna ao lado dela Supp.SKU e procurando manualmente por correspondências entre os valores da coluna Our.SKU e Supp.SKU(as descrições da coluna nos ajudarão com isso. Descrição) Este é um trabalho enfadonho, pense que só tem que ser feito uma vez lhe agrada :-).

Como resultado, temos a seguinte tabela:

2. Atualize a tabela principal com dados da tabela de pesquisa.

Insira uma nova coluna na tabela principal (folha de armazenamento) Supp.SKU.

Além disso, usando a função PROCV(VLOOKUP) comparar planilhas Armazenar e Conversor SKU Our.SKU, e para dados atualizados - a coluna Supp.SKU.

Coluna Supp.SKU preenchido com os códigos originais do fabricante.

Comente: Se a coluna Supp.SKU existem células vazias, então você precisa pegar todos os códigos SKU correspondendo a estes células vazias, adicione-os à mesa Conversor SKU e encontre o código correspondente na tabela de fornecedores. Em seguida, repetimos a etapa 2.

3. Transferimos dados da tabela de pesquisa para a tabela principal

Nossa tabela principal possui uma coluna-chave com uma correspondência exata aos elementos da tabela de consulta, portanto, agora essa tarefa não será difícil.

Usando a função PROCV(VLOOKUP) mesclar dados da planilha Armazenar com dados da folha Fornecedor de atacado 1 usando a coluna para encontrar correspondências Supp.SKU.

Aqui está um exemplo dos dados atualizados em uma coluna Preço por atacado:

É simples, não é? Tire suas dúvidas nos comentários ao artigo, tentarei responder o mais rápido possível.

As tabelas dinâmicas são úteis para analisar dados e criar relatórios com eles. E se forem dados relacionais (ou seja, dados que são armazenados em tabelas separadas, mas podem ser combinados devido a valores comuns), você pode criar uma tabela dinâmica como esta em apenas alguns minutos:

Como esta tabela dinâmica é diferente? Observe que a lista de campos à direita não exibe uma única tabela, mas todo um conjunto de tabelas. Cada uma dessas tabelas contém campos que podem ser combinados em uma tabela dinâmica para obter diferentes fatias dos dados. Nenhuma formatação manual e preparação de dados necessária. Imediatamente após importar os dados, você pode criar uma tabela dinâmica com base nas tabelas relacionadas.

Para combinar várias tabelas em uma Lista de Campos da Tabela Dinâmica:

Abaixo está o procedimento para importar várias tabelas de um banco de dados Dados SQL Servidor.

Observação: a lista de campos contém várias tabelas. Estas são todas as tabelas que você selecionou durante a importação. Cada tabela pode ser expandida e reduzida para visualizar seus campos. Como as tabelas estão vinculadas, você pode criar uma tabela dinâmica arrastando os campos de qualquer tabela para a área VALORES, LINHA ou COLUNAS.

Importando tabelas de outras fontes

Além do SQL Server, você pode importar tabelas de vários outros bancos de dados relacionais.

Os bancos de dados relacionais não são a única fonte de dados que oferece suporte a várias tabelas em uma Lista de Campos da Tabela Dinâmica. Você pode usar tabelas em sua pasta de trabalho ou importar feeds de dados e integrá-los a outras tabelas de dados em sua pasta de trabalho. Para que todos esses dados não relacionados funcionem juntos, você precisa adicionar cada tabela ao modelo de dados e, em seguida, criar relacionamentos entre eles usando os valores de campo correspondentes.

Usando o modelo de dados para criar uma nova tabela dinâmica

Você pode ter criado relacionamentos entre tabelas no Modelo de Dados e agora está pronto para usar esses dados para análise. O seguinte descreve como criar uma nova Tabela Dinâmica ou Gráfico Dinâmico usando um modelo de dados em uma pasta de trabalho.

Muitas vezes, ao gerar relatórios (análises) com base em grandes quantidades de dados, você deve resumir as informações de várias planilhas da pasta de trabalho do Excel em uma tabela.
Idealmente, para coletar e analisar informações de diferentes planilhas do Excel, é desejável fazer uma tabela dinâmica que levará dados de várias fontes (planilhas de livros).

Essa tabela pode ser feita de duas maneiras diferentes.

Vamos considerar a primeira maneira.

Crie uma tabela dinâmica a partir de diferentes planilhas usando recursos e ferramentas padrão.

Passo um.

Você precisa adicionar um botão do Assistente de Tabela Dinâmica e Gráfico à barra de ferramentas.

Para fazer isso, clique com o botão direito na faixa de opções (barra de ferramentas) e selecione o item "Personalizar Faixa de Opções" no menu suspenso

ou entre na guia

Arquivo => Opções => Personalizar Faixa de Opções.

Em seguida, nas configurações da faixa de opções, na lista suspensa sob o rótulo "Selecionar comandos", selecione o item "Todos os comandos". A janela abaixo exibirá todos os comandos possíveis que podem ser colocados no painel, em ordem alfabética.

Na lista, selecione "Assistente de tabelas e gráficos dinâmicos"

Na janela certa, usando o botão "Criar grupo", crie novo grupo Ferramentas. Para o grupo, você pode escolher um nome conveniente para você. Por exemplo, "Grupo próprio". Você pode escolher em qual guia o grupo será criado. No meu exemplo, selecionei a guia Home.

Quando o grupo for criado, selecione-o com o cursor, destaque o "Assistente de Tabela Dinâmica e Gráfico" na janela à esquerda e clique no botão "Adicionar >>".

Em seguida, clique em "OK".

O Assistente de Tabela Dinâmica e Gráfico está agora na guia principal da barra de ferramentas.

Passo dois. Construindo uma tabela dinâmica a partir de várias fontes de dados.

Segunda via.

Criar uma tabela que obtém dados de várias planilhas usando uma consulta gerada no suplemento Power Query.

Este métodoé usar uma consulta de suplemento do Power Query.

Crie um Power Query para nivelar várias páginas de uma pasta de trabalho em uma única tabela.

Passo um.

Você precisa criar duas consultas, cada uma das quais obterá informações de uma tabela separada.

Passo dois.

Para fazer isso, na guia Power Query, você precisa clicar no botão "Da Tabela" e especificar na janela que aparece, o intervalo - a fonte de dados. Em seguida, clique em "OK".

Passo três.

Quando a segunda consulta é criada, você precisa clicar no botão "Merge Queries" na guia Power Query e configurar a aparência da tabela geral resultante na janela que aparece.

A fonte de dados para uma tabela dinâmica é uma lista de dados, onde normalmente cada coluna atua como um campo na tabela dinâmica. Mas e se uma tabela que apenas se parece com uma tabela dinâmica vier até você (está formatada e se parece com isso, mas é impossível usar as ferramentas para trabalhar com tabelas dinâmicas). E você precisa transformá-lo em uma lista de dados, ou seja, execute a operação reversa. Nesta postagem, você aprenderá como converter uma tabela dinâmica com duas variáveis ​​em uma lista de dados.

A figura mostra o princípio que descrevi. Aqueles. o intervalo A2: E5 é a tabela dinâmica original que é convertida em uma lista de dados (intervalo H2: J14). A segunda tabela representa o mesmo conjunto de dados, apenas em uma perspectiva diferente. Cada valor na Tabela Dinâmica original aparece como uma string, consistindo em um item de campo de linha, um campo de coluna e seu valor correspondente. Essa exibição de dados é útil quando você precisa classificar e manipular dados de outras maneiras.

Para implementar a capacidade de criar tal lista, usaremos as ferramentas da tabela dinâmica. Vamos adicionar um botão Assistente de Tabela Dinâmica ao painel de acesso rápido, que não está disponível para nós na fita, mas permaneceu como um rudimento de mais primeiras versões Excel.

Vá para a guia Arquivo -> Opções... Na caixa de diálogo que aparece ParâmetrosExcel, na guia Painel acesso rápido na margem esquerda, encontre o item Assistente de Tabela Dinâmica e Gráfico e adicione-o ao caminho certo. Clique OK.

Agora você tem um novo ícone na Barra de Ferramentas de Acesso Rápido.

Clique nesta guia para executar Assistente de tabela dinâmica.

Na primeira etapa do assistente, você deve selecionar o tipo de fonte de dados da tabela dinâmica. Instalando o switch Em vários intervalos de consolidação e clique Mais distante.

Na etapa 2a, especifique como deseja criar as margens da página. Coloque a chave Criar campos de página -> Avançar.

Na etapa 2b, no campo Alcance selecione o intervalo que contém os dados e clique Adicionar. Em nosso caso, essa será a localização da tabela dinâmica A1: E4 original.

Na terceira etapa, você precisa decidir onde deseja colocar a tabela dinâmica e clicar no botão Preparar.

O Excel criará uma tabela dinâmica com dados. No lado esquerdo da tela, você verá a área Lista de campos da tabela dinâmica. Remova todos os itens dos campos de linha e coluna. Escrevi com mais detalhes sobre isso no artigo anterior.

Você acabará com uma pequena tabela dinâmica de uma célula que contém a soma de todos os valores da tabela original.

Clique duas vezes nesta célula. O Excel criará uma nova planilha que conterá uma tabela com uma lista de valores.

Os títulos desta tabela representam informações gerais você provavelmente deseja torná-los mais informativos.