Alguma vez utilizou a fórmula PROCV para transferir uma coluna de uma tabela para outra? Agora que o Excel dispõe de um Modelo de Dados incorporado, esta fórmula é obsoleta. Pode criar uma relação entre duas tabelas de dados, baseada em dados correspondentes em cada tabela. Depois, pode criar folhas do Power View e criar tabelas dinâmicas e outros relatórios com campos de cada tabela, mesmo quando as tabelas são provenientes de diferentes origens. Por exemplo, se tiver dados de vendas de clientes, poderá querer importar e relacionar dados da análise de tempo para analisar os padrões de vendas consoante o ano e o mês.
Todas as tabelas de um livro estão listadas na Tabela Dinâmica e nas listas de Campos do Power View.
Ao importar tabelas relacionadas a partir de uma base de dados relacional, o Excel pode frequentemente criar essas relações no Modelo de Dados que está a criar em segundo plano. Em todos os outros casos, terá de criar relações manualmente.
-
Certifique-se de que o livro contém pelo menos duas tabelas e que cada tabela contém uma coluna que pode ser mapeada para uma coluna noutra 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.
-
Atribua a cada tabela um nome significativo: Em Ferramentas de Tabela, clique em Estrutura > Nome da Tabela > introduza um nome.
-
Verifique se a coluna numa das tabelas tem valores de dados exclusivos, sem duplicados. O Excel só pode criar a relação se a coluna contiver valores exclusivos.
Por exemplo, para relacionar as vendas a clientes com a análise de tempo, ambas as tabelas têm de incluir datas no mesmo formato (por exemplo, 1/1/2012) e pelo menos uma das tabelas (análise de tempo) apresente cada data apenas uma vez dentro da coluna.
-
Clique em Dados> Relações.
Se Relações estiver indisponível, isso significa que o seu livro contém apenas uma tabela.
-
Na caixa Gerir Relações, clique em Nova.
-
Na caixa Criar Relação, clique na seta correspondente a Tabela e selecione uma tabela na lista. Numa relação um-para-muitos, esta tabela deverá estar no lado muitos. Utilizando o nosso exemplo de clientes e análise de tempo, o utilizador seleciona primeiro a tabela de vendas a clientes, visto que muitas vendas podem ocorrer num determinado dia.
-
Em Coluna (Externa), selecione a coluna que contém os dados relacionados com a Coluna Relacionada (Primária). Por exemplo, se tivesse uma coluna de data em ambas as tabelas, iria escolher essa coluna agora.
-
Em Tabela Relacionada, selecione uma tabela que tenha pelo menos uma coluna de dados relacionada com a tabela que acabou de selecionar para Tabela.
-
Em Coluna Relacionada (Primária), selecione uma coluna com valores exclusivos que correspondam aos valores na coluna selecionada para Coluna.
-
Clique em OK.
Mais sobre relações entre tabelas no Excel
Acerca das relações
-
Saberá se uma relação existe ao arrastar campos de diferentes tabelas para a lista de Campos de Tabela Dinâmica. Se não lhe for pedido para criar uma relação, o Excel já tem as informações de relação de que necessita para relacionar os dados.
-
A criação de relações é semelhante à utilização do comando PROCV: necessita de colunas que contenham dados correspondentes, para que o Excel possa efetuar a referência cruzada entre as linhas existentes numa tabela com as existentes noutra tabela. No exemplo da análise de tempo, a tabela Clientes teria de possuir valores de data que também existissem numa tabela de análise de tempo.
-
Num modelo de dados, as relações de tabelas podem ser de um-para-um (cada passageiro tem um bilhete 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 originam erros de dependência circulares, tais como “Foi detetada uma dependência circular". Este erro ocorrerá se fizer uma ligação direta entre duas tabelas que sejam ligações de muitos-para-muitos ou ligações indiretas (uma série de relações de tabelas que sejam um-para-muitos dentro de cada relação, mas de muitos-para-muitos quando vistas de ponta a ponta). Mais informações sobre Relações entre tabelas num Modelo de Dados.
-
Os tipos de dados nas duas colunas têm de ser compatíveis. Consulte o artigo Tipos de dados em Modelos de Dados do Excel para obter detalhes.
-
Existem outros modos de criar relações que poderão ser mais intuitivos, especialmente se não tiver a certeza das colunas que deve utilizar. Consulte o artigo Criar uma relação na Vista de Diagrama no Power Pivot.
Exemplo: relacionar dados de análise de tempo com dados de voos de uma companhia aérea
Pode saber mais sobre as relações de tabelas e a análise de tempo utilizando dados gratuitos no Microsoft AzureMarketplace. Algumas destas folhas de cálculo são muito grandes, exigindo uma ligação de Internet rápida para concluir a transferência de dados num período de tempo razoável.
-
Inicie o suplemento Power Pivot no Microsoft Excel e abra a janela do Power Pivot.
-
Clique em Obter Dados Externos > Do Serviço de Dados > Do Microsoft AzureMarketplace. A Home Page doMicrosoft Azure Marketplace é aberta no Assistente de Importação de Tabelas.
-
Em Price (Preço), clique em ree (Gratuito).
-
Em Category (Categoria), clique em Science & Statistics (Ciências e Estatística).
-
Localizar DateStream e clique em Subscrever.
-
Introduza a sua conta Microsoft e clique em Sign in (Iniciar sessão). Deverá aparecer uma pré-visualização dos dados na janela.
-
Desloque para o fundo e clique em Selecionar Consulta.
-
Clique em Seguinte.
-
Selecione BasicCalendarUS (CalendárioBásicoEUA) e, em seguida, clique em Concluir para importar os dados. Através de uma ligação de Internet rápida, a importação deverá levar cerca de um minuto. Após concluída, deverá ver um relatório de estado 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 Type (Tipo), clique em Data (Dados).
-
Em Price (Preço), clique em Free (Gratuito).
-
Localize US Air Carrier Flight Delays (Atrasos de Voos de Companhias Aéreas dos EUA) e clique em Select (Selecionar).
-
Desloque para o fundo e clique em Selecionar Consulta.
-
Clique em Seguinte.
-
Clique em Concluir para importar os dados. Através de uma ligação de Internet rápida, pode demorar 15 minutos a importar. Após concluído, deverá ver um relatório de estado de 2.427.284 linhas transferidas. Clique em Fechar. Deverá ter agora duas tabelas no modelo de dados. Para as relacionar, necessitamos de colunas compatíveis em cada tabela.
-
Lembre-se de que a ChaveDeData no BasicCalendarUS (CalendárioBásicoEUA) se encontra no formato 1/1/2012 00:00:00. A tabela On_Time_Performance (Desempenho_Horas) também tem uma coluna data/hora, FlightDate (DataDoVoo), cujos valores são especificados no mesmo formato: 1/1/2012 00:00:00. As duas colunas têm dados correspondentes, do mesmo tipo de dados e, pelo menos, uma das colunas (ChaveDeData) tem apenas valores únicos. Nos vários passos seguintes, irá utilizar estas colunas para relacionar as tabelas.
-
Na janela do Power Pivot, clique em Tabela Dinâmica para criar uma tabela dinâmica numa folha de cálculo nova ou existente.
-
Na Lista de Campos, expanda On_Time_Performance (Desempenho_Horas) e clique em ArrDelayMinutes (MinutosAtraso) para adicioná-lo à área Valores. Na Tabela Dinâmica, deverá ver a quantidade total de horários de voo atrasados, medido em minutos.
-
Expanda BasicCalendarUS (CalendárioBásicoEUA) e clique em MonthInCalendar (MêsNoCalendário) para adicioná-lo à área Linhas.
-
Repare que a Tabela Dinâmica agora apresenta meses, mas o total da soma dos minutos é o mesmo para todos os meses. Valores idênticos e repetidos indicam que é necessário criar uma relação.
-
Na Lista de Campos, em “Poderão ser necessárias relações entre tabelas”, clique em Criar.
-
Em Tabela Relacionada, selecione On_Time_Performance (Desempenho_Horas) e em Coluna Relacionada (Primária) selecione FlightDate (DataDoVoo).
-
Em Tabela, selecione BasicCalendarUS (CalendárioBásicoEUA) e em Coluna (Externa) selecione ChaveDeData. Clique em OK para criar a relação.
-
Lembre-se de que a soma dos minutos em atraso agora varia para cada mês.
-
Em BasicCalendarUS (CalendárioBásicoEUA) e arraste ChaveDeAno para a área Linhas, acima de MonthInCalendar (MêsNoCalendário).
Agora pode dividir os atrasos das chegadas por ano e mês ou por outros valores no calendário.
Sugestões: Por predefinição, os meses são apresentados por ordem alfabética. Ao utilizar o suplemento Power Pivot, pode alterar a ordem de modo a que os meses apareçam por ordem cronológica.
-
Certifique-se de que a tabela BasicCalendarUS (CalendárioBásicoEUA) está aberta na janela do Power Pivot.
-
Na tabela Home (Base), clique em Ordenar por Coluna.
-
Em Ordenar, selecione MonthInCalendar (MêsNoCalendário)
-
Em Por, selecione MonthInCalendar (MêsNoCalendário).
A Tabela Dinâmica ordena agora cada combinação por mês/ano (outubro 2011, novembro 2011) pelo número do mês num ano (10, 11). É fácil alterar a ordem, porque o feed DateStream fornece todas as colunas necessárias para que este cenário 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 à Tabela Dinâmica, será informado se é necessária uma relação de tabela para que os campos que selecionou na Tabela Dinâmica façam sentido.
Embora o Excel possa indicar-lhe quando é necessária uma relação, não consegue indicar-lhe que tabelas e colunas utilizar ou se uma relação de tabela é realmente possível. Tente seguir estes passos para obter as informações de que precisa.
Passo 1: determine que tabelas pretende especificar na relação
Se o modelo tiver apenas algumas tabelas, à partida será óbvio quais delas terá de utilizar. No entanto, poderá precisar de alguma ajuda para modelos maiores. Uma abordagem é utilizar a Vista de Diagrama no suplemento Power Pivot. A Vista de Diagrama proporciona uma representação visual de todas as tabelas no Modelo de Dados. Ao utilizar a Vista de Diagrama, pode determinar rapidamente que tabelas estão separadas do resto do modelo.
Nota: É possível criar relações ambíguas que sejam inválidas quando utilizadas num relatório de Tabela Dinâmica ou Vista Avançada. Imagine que todas as suas tabelas se relacionam de alguma forma com outras tabelas no modelo, mas quando tenta combinar campos de tabelas diferentes, recebe a mensagem “Podem ser necessárias relações entre tabelas”. A causa mais provável é ter criado uma relação muitos-para-muitos. Se seguir a série de relações de tabelas que ligam às tabelas que pretende utilizar, provavelmente irá descobrir que tem duas ou mais relações de tabelas um-para-muitos. Não existe uma solução fácil que funcione em todas as situações, mas pode tentar criar colunas calculadas para consolidar as colunas que pretende utilizar numa tabela.
Passo 2: localizar colunas que podem ser utilizadas para criar um caminho de uma tabela para outra
Após ter identificado qual das tabelas está desligada do resto do modelo, reveja as respetivas colunas para determinar se outra coluna algures no modelo tem valores correspondentes.
Por exemplo, suponha que tem um modelo que tem vendas de produtos por território e que importa subsequentemente dados demográficos para descobrir se há uma correlação entre vendas e tendências demográficas em cada território. Uma vez que os dados demográficos vêm de uma fonte de dados diferente, as suas tabelas são inicialmente isoladas do resto do modelo. Para integrar os dados demográficos com o resto do seu modelo, terá de encontrar uma coluna numa das tabelas demográficas que corresponda a uma que já esteja a usar. Por exemplo, se os dados demográficos estiverem organizados por região e os seus dados de vendas especificarem em que região a venda ocorreu, pode relacionar os dois conjuntos de dados localizando uma coluna comum, como uma cidade, um código postal ou uma região, para proporcionar a pesquisa.
Além dos valores correspondentes, existem alguns requisitos adicionais para criar uma relação:
-
Os valores de dados na coluna de pesquisa têm de ser exclusivos. Por outras palavras, a coluna não pode conter duplicados. Num Modelos de Dados, os nulos e as cadeias vazias são equivalente a um valor em branco, que é um valor de dados distinto. Isto significa que não pode ter vários valores nulos na coluna de referência.
-
os tipos de dados da coluna de origem e da coluna de pesquisa têm de ser compatíveis. Para mais informações sobre os tipos de dados, consulte o artigo Tipos de dados em modelos de dados.
Para saber mais sobre relações de tabela, consulte o artigo Relações entre tabelas num Modelo de Dados.
