Abstrata: Este é o primeiro tutorial de uma série projetada para que você se familiarize e se sinta confortável usando o Excel e seus recursos internos de análise e mash-up de dados. Estes tutoriais criam e refinam uma pasta de trabalho do Excel desde o início, criam um modelo de dados e relatórios interativos incríveis usando o Power View. Os tutoriais foram projetados para demonstrar recursos e recursos do Microsoft Business Intelligence no Excel, Tabela Dinâmica, Power Pivot e Power View.
Nestes tutoriais, você aprenderá a importar e explorar dados no Excel, criar e refinar um modelo de dados usando o Power Pivot e criar relatórios interativos com o Power View que você pode publicar, proteger e compartilhar.
Nesta série, os tutoriais são os seguintes:
- Importar dados para Excel 2016 e criar um modelo de dados
- Estender relações de modelo de dados usando Excel, Power Pivot e DAX
- Criar relatórios do Power View baseados em mapas
- Incorporar dados da Internet e definir padrões para os relatórios do Power View
- Ajuda do Power Pivot
- Criar relatórios incríveis do Power View - Parte 2
Neste tutorial, você começa com uma pasta de trabalho do Excel em branco.
Veja as seções neste tutorial:
- Importar dados de um banco de dados
- Importar dados de uma planilha
- Importar dados usando copiar e colar
- Criar uma relação entre os dados importados
- Ponto de verificação e questionário
No final deste tutorial, você encontrará um questionário que pode ser usado para testar seu aprendizado.
Esta série de tutoriais utiliza dados que descrevem as Medalhas Olímpicas, os países anfitriões e vários eventos olímpicos. Sugerimos que você veja cada tutorial na ordem.
Importar dados de um banco de dados
Vamos começar este tutorial com uma pasta de trabalho em branco. O objetivo desta seção é se conectar a uma fonte de dados externa e importar esses dados no Excel para análise posterior.
Vamos começar baixando alguns dados da Internet. Os dados descrevem as Medalhas Olímpicas e são de um banco de dados do Microsoft Access.
Clique nos links a seguir para baixar os arquivos que usamos durante esta série de tutoriais. Baixe cada um dos quatro arquivos para um local facilmente acessível, como Downloads ou Meus Documentos, ou para uma nova pasta que você cria:
> Banco de dados olympicmedals.accdb Access
> OlympicSports.xlsx pasta de trabalho do Excel
> Population.xlsx pasta de trabalho do Excel
> DiscImage_table.xlsx pasta de trabalho do ExcelNo Excel, abra uma pasta de trabalho em branco.
Clique em Obter > dados do banco de dados >> do Banco de Dados do Microsoft Access. A faixa de opções se ajusta dinamicamente com base na largura da pasta de trabalho, de modo que os comandos na faixa de opções podem parecer ligeiramente diferentes da tela a seguir.
Selecione o arquivo OlympicMedals.accdb que você baixou e clique em Importar. A janela Navegador a seguir é exibida, exibindo as tabelas encontradas no banco de dados. As tabelas em um banco de dados são parecidas com planilhas ou tabelas no Excel. Marque a caixa Selecionar várias tabelas e selecione todas as tabelas. Em seguida, clique em Carregar > Carregar para.
A janela Importar Dados é exibida.
Observação
Observe a caixa de seleção na parte inferior da janela que permite adicionar esses dados ao Modelo de Dados, mostrado na tela a seguir. Um Modelo de Dados é criado automaticamente quando você importa ou trabalha com duas ou mais tabelas simultaneamente. Um Modelo de Dados integra as tabelas, permitindo uma análise extensiva usando Tabelas Dinâmicas, Power Pivot e Power View. Quando você importa tabelas de um banco de dados, as relações de banco de dados existentes entre essas tabelas são usadas para criar o Modelo de Dados no Excel. O Modelo de Dados é transparente no Excel, mas você pode exibi-lo e modificá-lo diretamente usando o suplemento do Power Pivot. O Modelo de Dados é discutido com mais detalhes mais adiante neste tutorial.
Selecione a opção Relatório de Tabela Dinâmica, que importa as tabelas no Excel e prepara uma Tabela dinâmica para análise das tabelas importadas e clique em OK.
Após a importação dos dados, é criada uma tabela dinâmica baseada nas tabelas importadas.
Com os dados importados para o Excel e o modelo de dados criado automaticamente, você está pronto para explorar os dados.
Explorar dados usando uma tabela dinâmica
A exploração de dados importados é fácil com uma Tabela dinâmica. Em uma Tabela dinâmica, você arrasta campos (semelhantes às colunas no Excel) de tabelas (como as tabelas que você acabou de importar do banco de dados do Access) para áreas diferentes da Tabela dinâmica a fim de ajustar o modo como apresenta seus dados. Uma Tabela dinâmica tem quatro áreas: FILTROS, COLUNAS, LINHAS e VALORES.
Talvez sejam necessários alguns experimentos para determinar para qual área um campo deve ser arrastado. Você pode arrastar quantos campos quiser de suas tabelas, até que a Tabela dinâmica apresente seus dados da forma como você deseja vê-los. Sinta-se livre para explorar, arrastando campos para áreas diferentes da Tabela dinâmica; os dados subjacentes não são afetados quando você organiza os campos em uma Tabela dinâmica.
Vamos explorar os dados de Medalhas Olímpicas na Tabela dinâmica, começando com os medalhistas olímpicos organizados por disciplina, tipo de medalha e país ou região do atleta.
Em Campos da Tabela Dinâmica, expanda a tabela Medalhas clicando na seta ao lado dela. Localize o campo NOC_PaísRegião na tabela Medalhas expandida e arraste-o até a área COLUNAS. NOC significa Comitês Olímpicos Nacionais, que é a unidade organizacional de um país ou região.
Em seguida, na tabela Disciplinas, arraste Disciplina para a área LINHAS.
Vamos filtrar Disciplinas para exibir somente cinco esportes: Tiro com arco, Mergulho, Esgrima, Patinação artística e Patinação de velocidade. Você pode fazer isso na área Campos da Tabela Dinâmica ou do filtro Rótulos de Linha na própria Tabela Dinâmica.
- Clique em qualquer lugar na Tabela Dinâmica para garantir que a Tabela Dinâmica do Excel esteja selecionada. Na lista Campos de Tabela Dinâmica , em que a tabela Disciplinas é expandida, passe o mouse sobre o campo Disciplina e uma seta suspensa aparece à direita do campo. Clique na lista suspensa, clique em **(Selecione Tudo)**para remover todas as seleções e, em seguida, role para baixo e selecione Tiro com Arco, Mergulho, Esgrima, Patinação Artística e Patinação de Velocidade. Clique em OK.
- Ou, na seção Rótulos de Linha da Tabela Dinâmica, clique na seta suspensa ao lado de Rótulos de Linha na Tabela Dinâmica, clique em (Selecionar Tudo) para remover todas as seleções e, em seguida, role para baixo e selecione Tiro com arco, Mergulho, Esgrima, Patinação artística e Patinação de velocidade. Clique em OK.
Em Campos da Tabela Dinâmica, na tabela Medalhas, arraste Medalha até a área VALORES. Como os valores devem ser numéricos, o Excel altera automaticamente Medalha para ontagem de Medalhas.
Na tabela Medalhas, selecione Medalha novamente e arraste-a para a área FILTROS.
Vamos filtrar a Tabela dinâmica para exibir apenas os países ou regiões com mais de 90 medalhas no total. Veja como.
- Na Tabela dinâmica, clique em lista suspensa à direita de Rótulos de Coluna.
- Selecione Filtros de Valor e selecione É Maior do que….
- Digite 90 no último campo (à direita). Clique em OK.
Sua Tabela dinâmica se parece com a tela a seguir.
Com pouco esforço, agora você tem uma Tabela dinâmica básica que inclui campos de três tabelas diferentes. O que tornou essa tarefa tão simples foram as relações preexistentes entre as tabelas. Pelo fato de que as relações entre as tabelas existirem no banco de dados de origem, e pelo fasto de você ter importado todas as tabelas com uma única operação, o Excel conseguiu recriar essas relações de tabelas em seu Modelo de dados.
Mas e se os seus dados provierem de fontes diferentes ou forem importados em um momento posterior? Geralmente, é possível criar relações com novos dados baseadas em colunas correspondentes. Na próxima etapa, você importará outras tabelas e aprenderá a criar novas relações.
Importar dados de uma planilha
Agora vamos importar dados de outra fonte, desta vez de uma pasta de trabalho existente e especificar as relações entre os dados existentes e os novos dados. Os relacionamentos permitem a análise de conjuntos de dados no Excel e a criação de visualizações interessantes e envolventes a partir dos dados que você importa.
Vamos começar criando uma planilha em branco e, em seguida, importar dados de uma pasta de trabalho do Excel.
Insira uma nova planilha do Excel e chame-a de Esportes.
Navegue até a pasta que contém os arquivos de dados de exemplo baixados e abra OlympicSports.xlsx.
Selecione e copie os dados de Plan1. Se você selecionar uma célula com dados, como a célula A1, será possível pressionar Ctrl + A para selecionar todos os dados adjacentes. Feche a pasta de trabalho OlympicSports.xlsx.
Na planilha Esportes, coloque seu cursor na célula A1 e cole os dados.
Com os dados ainda realçados, pressione Ctrl + T para formatar os dados como uma tabela. Você também pode formatar os dados como uma tabela na faixa de opções selecionando Formato HOME > como Tabela. Como os dados têm cabeçalhos, selecione Minha tabela tem cabeçalhos na janela Criar Tabela exibida, conforme exibido aqui.
Formatar os dados como uma tabela tem muitas vantagens. Você pode atribuir um nome a uma tabela, o que facilita a identificação. Você também pode estabelecer relações entre tabelas, permitindo exploração e análise em Tabelas Dinâmicas, Power Pivot e Power View.
Dê um nome à tabela. Em Propriedades DE DESIGN > DE TABELA, localize o campo Nome da Tabela e digite Esportes. A pasta de trabalho se parece com a seguinte tela.
Salve a pasta de trabalho.
Importar dados usando copiar e colar
Agora que importamos os dados de uma pasta de trabalho do Excel, vamos importar dados de uma tabela que encontramos em uma página da Web ou qualquer outra fonte da qual nós podemos copiar e colar no Excel. Nas etapas a seguir, você adicionará as cidades-sede das Olimpíadas de uma tabela.
- Insira uma nova planilha do Excel e chame-a de Cidades-sede.
- Selecione e copie a tabela a seguir, incluindo os cabeçalhos de tabela.
- No Excel, coloque seu cursor na célula A1 da planilha Cidades-sede e cole os dados.
- Formate os dados como uma tabela. Conforme descrito anteriormente neste tutorial, você pressiona Ctrl + T para formatar os dados como uma tabela ou do Formato HOME > como Tabela. Como os dados têm cabeçalhos, selecione Minha tabela tem cabeçalhos na janela Criar Tabela que aparece.
- Dê um nome à tabela. Em PROPRIEDADES DE DESIGN > DE TABELA , localize o campo Nome da Tabela e digite Hosts.
- Selecione a coluna Edição e na guia PÁGINA INICIAL, formate-a como Número com 0 casas decimais.
- Salve sua pasta de trabalho. Sua pasta de trabalho parece com a tela a seguir.
Agora que você tem uma pasta de trabalho do Excel com tabelas, poderá criar relações entre elas. A criação de relações entre as tabelas permite que você combine os dados das duas tabelas.
Criar uma relação entre os dados importados
Você pode começar imediatamente a usar os campos em sua Tabela dinâmica das tabelas importadas. Se o Excel não puder determinar como incorporar um campo à Tabela dinâmica, será necessário estabelecer uma relação com o Modelo de dados existente. Nas etapas a seguir, você aprenderá a criar uma relação entre os dados importados de fontes diferentes.
Na Planilha1, na parte superior dosCampos de Tabela Dinâmica, clique emTodos para exibir a lista completa de tabelas disponíveis, conforme mostrado na tela a seguir.
Percorra a lista para ver as novas tabelas que você acabou de adicionar.
Expanda Esportes e selecione Esporte para adicioná-lo à Tabela dinâmica. Observe que o Excel solicita a criação de uma relação, como mostra a tela a seguir.
Essa notificação ocorre porque você usou os campos de uma tabela que não faz parte do Modelo de dados subjacente. Uma maneira de adicionar uma tabela ao Modelo de dados é criar uma relação com uma tabela que já esteja no Modelo de dados. Para criar a relação, uma das tabelas deve ter uma coluna de valores exclusivos e não repetidos. No exemplo de dados, a tabela Disciplinas importada do banco de dados contém um campo com códigos de esportes, chamados de IDdeEsportes. Esses mesmos códigos de esportes estão presentes como um campo nos dados do Excel que importamos. Vamos criar a relação.Clique em CRIAR... na área Campos da Tabela Dinâmica realçada a fim de abrir a caixa de diálogo Criar Relação, conforme exibido na tela a seguir.
Em Tabela, escolha Tabela de Modelo de Dados: Disciplinas na lista suspensa.
Em Coluna (Estrangeira), escolha IDdeEsportes.
Em Tabela Relacionada, escolha Tabela de Modelo de Dados: Esportes.
Em Coluna Relacionada (Primária), escolha IDdeEsportes.
Clique em OK.
As alterações da Tabela dinâmica refletem na nova relação. Mas a Tabela dinâmica não está certa ainda, devido a ordenação dos campos na área LINHAS. Disciplina é uma subcategoria de um determinado esporte, mas como organizamos Disciplina acima do Esporte na área LINHAS, ela não está organizada adequadamente. A tela a seguir mostra essa ordenação indesejada.
Na área LINHAS, mova Esporte acima de Disciplina. Assim é muito melhor, e a Tabela dinâmica exibe os dados do modo como você quer vê-los, conforme exibido na imagem a seguir.
Nos bastidores, o Excel está criando um Modelo de Dados que pode ser usado em toda a pasta de trabalho, em qualquer Tabela Dinâmica, Gráfico Dinâmico, no Power Pivot ou em qualquer relatório do Power View. As relações de tabela são a base de um Modelo de dados e o que determina os caminhos de navegação e cálculo.
No próximo tutorial, Estenda as relações de Modelo de Dados usando Excel, Power Pivot**e DAX**, você baseia-se no que aprendeu aqui e passo a passo pela extensão do Modelo de Dados usando um suplemento poderoso e visual do Excel chamado Power Pivot. Você também aprenderá a calcular colunas em uma tabela e usar essa coluna calculada para que uma tabela não relacionada possa ser adicionada ao modelo de dados.
Ponto de verificação e questionário
Revise o que você aprendeu
Agora você tem uma pasta de trabalho do Excel que inclui uma Tabela dinâmica acessando dados em várias tabelas, diversas delas importadas separadamente. Você aprendeu a importar de um banco de dados, de outra pasta de trabalho do Excel e copiando e colando dados no Excel.
Para que esses dados funcionem juntos, foi necessário criar uma relação entre tabelas que o Excel usa para correlacionar as linhas. Você também aprendeu que ter colunas em uma tabela que correlaciona dados em outra tabela é essencial para a criação de relações e para procurar linhas relacionadas.
Você está pronto para o próximo tutorial desta série. Aqui está um link:
Tutorial: Estender relacionamentos de Modelo de Dados usando o Excel, o Power Pivot e DAX
QUESTIONÁRIO
Quer ver o quanto você se lembra do que aprendeu? Aqui está sua chance. O questionário a seguir destaca recursos, capacidades ou requisitos sobre os quais você aprendeu neste tutorial. Na parte inferior da página, você encontrará as respostas. Boa sorte!
Pergunta 1: Por que é importante converter dados importados em tabelas?
R: Não é necessário convertê-los em tabelas, pois todos os dados importados são automaticamente transformados em tabelas.
B: Se você converter dados importados em tabelas, eles serão excluídos do Modelo de dados. Somente quando eles são excluídos do Modelo de Dados estão disponíveis em Tabelas Dinâmicas, Power Pivot e Power View.
C: se você converter dados importados em tabelas, eles poderão ser incluídos no Modelo de Dados e serão disponibilizados para Tabelas Dinâmicas, Power Pivot e Power View.
D: Não é possível converter dados importados em tabelas.
Pergunta 2: Qual das seguintes fontes de dados podem ser importadas no Excel e incluídas no Modelo de dados?
R: Bancos de dados do Access e muitos outros bancos de dados também.
B: Arquivos do Excel existentes.
C: Tudo o que você pode copiar e colar no Excel e formatar como tabela, incluindo tabelas de dados em sites, documentos ou qualquer outra coisa que possa ser colada no Excel.
D: Todas as anteriores
Pergunta 3: Em uma Tabela dinâmica, o que acontece quando você reorganiza os campos nas quatro áreas dos Campos da Tabela Dinâmica?
R: Nada. Você não pode reorganizar os campos depois de colocá-los nas áreas dos Campos da Tabela Dinâmica.
B: O formato da Tabela dinâmica é alterado a fim de refletir o layout, mas os dados subjacentes não são afetados.
C: O formato da Tabela dinâmica é alterado a fim de refletir o layout e todos os dados subjacentes são alterados permanentemente.
D: Os dados subjacentes são alterados, resultando em novos conjuntos de dados.
Pergunta 4: Ao criar uma relação entre tabelas, o que é necessário?
R: Nenhuma tabela pode ter qualquer coluna que contenha valores exclusivos e não repetidos.
B: Uma tabela não deve fazer parte da pasta de trabalho do Excel.
C: As colunas não devem ser convertidas em tabelas.
D: Nenhuma das anteriores é correta.
Respostas do Questionário
- Resposta correta: C
- Resposta correta: D
- Resposta correta: B
- Resposta correta: D
Observação
Os dados e imagens nesta série de tutoriais têm base no seguinte:
- Conjunto de dados sobre Olimpíadas do Guardian News & Media Ltd.
- Imagens de bandeiras da CIA Factbook (cia.gov)
- Dados de população do Banco Mundial (worldbank.org)
- Pictogramas de esporte olímpico por Thadius856 e Parutakupiu