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.
Observação: Certifique-se de que a funcionalidade está ativada antes de seguir os passos neste artigo. Para obter mais informações, aceda a Iniciar o suplemento Power Pivot para Excel.
Quando importa tabelas relacionadas a partir de uma base de dados relacional, o Excel pode muitas vezes criar essas relações no Modelo de Dados que está a criar em segundo plano. Para todos os outros casos, terá de 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.
-
Efetue um dos seguintes procedimentos: formatar os dados como uma tabela ou Importar dados externos como uma tabela numa nova folha de cálculo.
-
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
-
Saberá se existe uma relação quando arrasta campos de tabelas diferentes para a lista Campos da Tabela Dinâmica. Se não lhe for pedido para criar uma relação, 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 muitos-para-muitos resultam em erros de dependência circular, como "Foi detetada 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 as relacionar, precisamos de colunas compatíveis em cada tabela.
-
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. Nos próximos passos, irá utilizar estas 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 estiver a utilizar uma tabela de análise de tempo diferente, o seu passo será diferente.
"Podem ser necessárias relações entre tabelas"
À medida que adiciona campos a uma tabela dinâmica, será informado se é necessária uma relação de tabela para compreender os campos que selecionou na Tabela Dinâmica.
Embora o Excel possa indicar-lhe quando é necessária uma relação, não lhe pode dizer que tabelas e colunas deve utilizar ou se uma relação de tabela é mesmo 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 utilizadas num relatório de Tabela Dinâmica ou do Power View. Suponha que todas as tabelas estão relacionadas de alguma forma com outras tabelas no modelo, mas quando tenta combinar campos de tabelas diferentes, obtém a mensagem "Podem ser necessárias relações entre tabelas". A causa mais provável é ter tido uma relação 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 identificar que tabela está desligada do resto do modelo, reveja as respetivas colunas para determinar se outra coluna, noutro local 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, terá de encontrar uma coluna numa das tabelas demográficas que corresponde a uma que já esteja a utilizar. 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. Por outras palavras, a coluna não pode conter duplicados. 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. Isto significa que não pode ter vários nulos 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.
