Você já usou o VLOOKUP para transportar uma coluna de uma tabela para outra? Agora que o Excel tem um Modelo de Dados interno, o VLOOKUP ficou obsoleto. Você pode criar uma relação entre duas tabelas de dados, com base em dados correspondentes de cada tabela. Em seguida, é possível criar planilhas do Power View e construir Tabelas Dinâmicas e outros relatórios com campos de cada tabela, mesmo quando as tabelas são provenientes de fontes diferentes. Por exemplo, se você tiver dados de vendas de cliente, pode ser conveniente importar e relacionar dados de inteligência temporais para analisar padrões de vendas por ano e por mês.
Todas as tabelas em uma pasta de trabalho são listadas nas Listas de campos da Tabela Dinâmica e do Power View.
Quando você importa tabelas relacionadas de um banco de dados relacional, o Excel geralmente cria essas relações no Modelo de dados que está criando nos bastidores. Para todos os outros casos, você precisará criar relações manualmente.
-
Verifique se a pasta de trabalho contém no mínimo duas tabelas, e se cada tabela tem uma coluna que pode ser mapeada para uma coluna em outra tabela.
-
Faça um dos seguintes procedimentos: formate os dados como uma tabela ou Importe dados externos como uma tabela em uma nova planilha.
-
Dê a cada tabela um nome significativo: Nas Ferramentas de Tabela, clique em Design > Nome da Tabela > insira um nome.
-
Verifique se a coluna em uma das tabelas tem valores de dados exclusivos sem duplicações. O Excel só pode criar a relação, se uma coluna contiver valores exclusivos.
Por exemplo, para relacionar as vendas dos clientes com a inteligência de dados temporais, ambas as tabelas devem incluir dados no mesmo formato (por exemplo, 1/1/2012) e, pelo menos, uma tabela (inteligência de dados temporais) deve listar cada data apenas uma vez na coluna.
-
Clique em Dados> Relações.
Se Relações estiver esmaecido, isso significa que a sua pasta de trabalho contém apenas uma tabela.
-
Na caixa Gerenciar Relações, clique em Nova.
-
Na caixa Criar Relação, clique na seta de Tabela e selecione uma tabela na lista. Em uma relação de muitos-para-um, essa tabela deve estar no lado muitos. Usando nosso exemplo de cliente e inteligência de dados temporais, você escolheria a tabela de vendas dos clientes primeiro, porque é provável que ocorram muitas vendas em um determinado dia.
-
Para Coluna (Estrangeira), selecione a coluna que contém os dados relacionados a Coluna Relacionada (Principal). Por exemplo, se você tinha uma coluna de datas em ambas as tabelas, agora você escolheria essa coluna.
-
Para Tabela Relacionada, selecione uma tabela que tenha pelo menos uma coluna de dados relacionada à tabela que você acabou de selecionar para Tabela.
-
Para Coluna Relacionada (Primária), selecione uma coluna que tenha valores exclusivos correspondentes aos valores da coluna selecionada para Coluna.
-
Clique em OK.
Mais informações sobre relações entre tabelas no Excel
Notas sobre relações
-
Você saberá se existe uma relação ao arrastar campos de tabelas diferentes na lista de Campos da Tabela Dinâmica. Se você não receber uma solicitação para criar uma relação, isso significará que o Excel já tem as informações de relação necessárias para relacionar os dados.
-
Criar relações é semelhante a usar VLOOKUPs: você precisa de colunas que contêm dados correspondentes de forma que o Excel possa fazer a referência cruzada das linhas em uma tabela com as de outra. No exemplo de inteligência de tempo, a tabela Cliente precisaria ter valores de datas que também existissem na tabela de inteligência de tempo.
-
Em um modelo de dados, as relações entre tabelas podem ser de um para um (cada passageiro tem um cartão de embarque) ou de um para muitos (cada voo tem muitos passageiros), mas não de muitos para muitos. As relações de muitos para muitos resultam em erros de dependência circular, como “Foi detectada uma dependência circular.” Esse erro ocorrerá se você fizer uma conexão direta entre duas tabelas do tipo muitos para muitos, ou conexões indiretas (uma cadeia de relações entre tabelas que são de um para muitos no âmbito de cada uma das relações, mas cujos extremos formam uma relação de muitos para muitos). Leia mais sobre Relações entre tabelas em um Modelo de dados.
-
Os tipos de dados nas duas colunas devem ser compatíveis. Veja Tipos de dados no Modelos de Dados do Excel para obter detalhes.
-
Outras formas de criar relações podem ser mais intuitivas, principalmente se você não souber ao certo quais colunas usar. Veja Criar uma relação no Modo de Exibição de Diagrama no Power Pivot.
Exemplo: Relação entre dados de inteligência de tempo e dados de voos de companhias aéreas
Saiba mais sobre relações entre tabelas e sobre inteligência de tempo usando dados gratuitos disponíveis no Microsoft Azure Marketplace. Alguns desses conjuntos de dados são muito grandes, sendo, portanto, necessária uma conexão rápida à Internet para que o download dos dados seja efetuado em um período de tempo razoável.
-
Iniciar o Power Pivot no suplemento Microsoft Excel e abrir a janela do Power Pivot.
-
Clique em Obter Dados Externos > Do Serviço de Dados > Do Microsoft Azure Marketplace. A página inicial do Microsoft Azure Marketplace é exibida no Assistente de Importação de Tabela.
-
Em Preço, clique em Grátis.
-
Em Categoria, clique em Ciências & Estatística.
-
Localize DateStream e clique em Assinar.
-
Acesse a sua conta da Microsoft e clique em Entrar. Na janela deverá aparecer uma visualização dos dados.
-
Role até a parte inferior e clique em Selecionar Consulta.
-
Clique em Avançar .
-
Selecione BasicCalendarUS e em seguida clique em Concluir para importar os dados. Usando-se uma conexão rápida à Internet, a importação deveria demorar aproximadamente um minuto. Uma vez concluída, você deverá ver um relatório de status de 73.414 linhas transferidas. Clique em Fechar .
-
Clique em Obter Dados Externos > Do Serviço de Dados > Do Microsoft Azure Marketplace para importar um segundo conjunto de dados.
-
Em Tipo, clique em Dados.
-
Em Preço, clique em Grátis.
-
Localize Atrasos de Voos de Companhias Aéreas dos EUA e clique em Selecionar.
-
Role até a parte inferior e clique em Selecionar Consulta.
-
Clique em Avançar .
-
Clique em Concluir para importar os dados. Usando-se uma conexão rápida à Internet, a importação pode demorar aproximadamente 15 minutos. Uma vez concluída, você deverá ver um relatório de status de 2.427.284 linhas transferidas. Clique em Fechar. Agora você deverá ter duas tabelas no modelo de dados. Para relacioná-las, precisaremos ter colunas compatíveis em cada uma das tabelas.
-
Observe que a DateKey em BasicCalendarUS está no formato 1/1/2012 12:00:00 AM. A tabela On_Time_Performance também tem uma coluna datetime, FlightDate, cujos valores são especificados no mesmo formato: 1/1/2012 12:00:00 AM. As duas colunas contêm dados correspondentes, do mesmo tipo, e pelo menos uma das colunas (DateKey) contém somente dados unívocos. Nas etapas que se seguem, você usará essas colunas para relacionar as tabelas.
-
Na janela do Power Pivot, clique em Tabela Dinâmica para criar uma Tabela Dinâmica em uma planilha nova ou existente.
-
Na Lista de Campos, expanda On_Time_Performance e clique em ArrDelayMinutes para adicioná-lo à área Valores. Na Tabela Dinâmica, você irá ver o valor total do tempo de atraso dos voos, medido em minutos.
-
Expanda BasicCalendarUS e clique em MonthInCalendar para adicioná-lo à área Linhas.
-
Observe que a Tabela Dinâmica contém agora uma lista de meses, mas a soma total de minutos é a mesma para cada mês. A presença de valores idênticos/repetitivos indica que é necessária uma relação.
-
Na Lista de Campos, em “Podem ser necessárias relações entre tabelas”, clique em Criar.
-
Em Tabela Relacionada, selecione On_Time_Performance e em Coluna Relacionada (Principal) selecione FlightDate.
-
Em Tabela, selecione BasicCalendarUS e em Coluna (Estrangeira) selecione DateKey. Clique em OK para criar a relação.
-
Observe que agora a soma de minutos de atraso varia para cada mês.
-
Em BasicCalendarUS clique e arraste YearKey para a área Linhas, acima de MonthInCalendar.
Agora você pode subdividir os atrasos das chegadas por ano e por mês, ou por outros valores do calendário.
Dicas: Por padrão, os meses são listados em ordem alfabética. Usando o suplemento Power Pivot, você pode alterar essa classificação de maneira que os meses sejam listados em ordem cronológica.
-
Certifique-se de que a tabela BasicCalendarUS está aberta na janela Power Pivot.
-
Na tabela Home, clique em Classificar por Coluna.
-
Em Classificar, selecione MonthInCalendar
-
Em Por, selecione MonthOfYear.
Agora a Tabela Dinâmica classifica cada combinação mês-ano (Outubro de 2011, Novembro de 2011) pelo número do mês dentro do ano (10, 11). Alterar a ordem da classificação é fácil, porque o feed DateStream fornece todas as colunas necessárias para que essa situação funcione. Se você utilizar uma tabela de inteligência de tempo diferente, a etapa a efetuar será diferente.
“As relações entre tabelas poderão ser necessárias”
À medida que você adicionar campos a uma Tabela Dinâmica, você será informado se uma relação entre tabelas é necessária para que os campos que você selecionou na Tabela Dinâmica façam sentido.
Embora o Excel possa comunicar quando é necessária uma relação, ele não pode comunicar quais tabelas e colunas devem ser utilizadas, ou até mesmo se uma relação entre as tabelas é possível. Experimente efetuar as etapas seguintes para obter as respostas de que você precisa.
Etapa 1: Determine quais são as tabelas a serem especificadas na relação
Se o seu modelo contém poucas tabelas, poderá ser óbvio quais delas você deve usar. Mas para modelos maiores, você poderá precisar de uma ajuda. Uma possível abordagem é usar o Modo de Exibição de Diagrama no suplemento Power Pivot. A exibição de diagrama proporciona uma representação visual de todas as tabelas do modelo de dados. Usando a exibição de diagrama, você pode rapidamente determinar quais tabelas são diferentes do resto do modelo.
Observação: É possível criar relações ambíguas que são inválidas quando usadas em uma Tabela Dinâmica ou em um relatório do Power View. Suponhamos que todas as suas tabelas estejam relacionadas de alguma forma com outras tabelas do modelo, mas que quando você tenta combinar campos de diferentes tabelas, apareça a mensagem "Podem ser necessárias relações entre as tabelas". A causa mais provável para isso é que você tenha se deparado com uma relação de muitos para muitos. Se você seguir a cadeia de relações que conectam as tabelas que você quer usar, você vai provavelmente descobrir a presença de duas ou mais relações entre tabelas do tipo um para muitos. Não existe uma solução simples que funcione para todas as situações, mas você pode experimentar criar colunas calculadas para consolidar as colunas que você quer usar em uma única tabela.
Etapa 2: Localize as colunas que podem ser utilizadas para criar um caminho de uma tabela para a seguinte
Depois de ter identificado a tabela que está desconectada do resto do modelo, examine as suas colunas para determinar se alguma coluna em algum outro lugar do modelo contém valores correspondentes.
Por exemplo, suponhamos que você tenha um modelo que contém vendas de produtos por território, e que subsequentemente você importe dados demográficos para apurar se existe uma correlação entre as vendas e as tendências demográficas de cada território. Como os dados demográficos provêm de uma fonte de dados diferente, as suas tabelas estão inicialmente isoladas do resto do modelo. Para integrar os dados demográficos com o resto do modelo, você precisará localizar uma coluna em uma das tabelas de dados demográficos que corresponda a uma que você já esteja utilizando. Por exemplo, se os dados demográficos estão organizados por região, e os dados das vendas especificam a região em que a venda ocorreu, é possível relacionar os dois conjuntos de dados localizando uma coluna em comum, como Estado, CEP ou Região, para providenciar a pesquisa.
Além dos valores correspondentes, existem alguns requisitos adicionais para se criar uma relação:
-
Os valores dos dados da coluna de pesquisa devem ser unívocos. Em outras palavras, a coluna não pode conter duplicações. Em um Modelo de Dados, as cadeias de caracteres nulas e vazias equivalem a um espaço em branco, que é um valor de dados distinto. Isso significa que você não pode ter mais de um valor nulo na coluna de pesquisa.
-
Os tipos de dados na coluna fonte e na coluna de pesquisa devem ser compatíveis. Para obter mais informações sobre tipos de dados, veja Tipos de dados em modelos de dados.
Para saber mais sobre relações entre tabelas, veja Relações entre tabelas em um Modelo de Dados.
