Abstrato: Este é o primeiro tutorial de uma série concebida para o familiarizar e sentir confortável com o Excel e as respetivas funcionalidades incorporadas de análise e mash-up de dados. Estes tutoriais criam e aperfeiçoam um livro do Excel a partir do zero, construindo um modelo de dados e criando fantásticos relatórios interativos com a ajuda do Power View. Os tutoriais foram concebidos para demonstrar funcionalidades e capacidades do Microsoft Business Intelligence no Excel, Tabelas Dinâmicas, Power Pivot e Power View.
Nestes tutoriais, vai aprender a importar e explorar dados no Excel, criar e refinar um modelo de dados com o Power Pivot e criar relatórios interativos com o Power View que pode publicar, proteger e partilhar.
Os tutoriais nesta série são os seguintes:
- Importar Dados para Excel 2016 e Criar um Modelo de Dados
- Expandir relações do Modelo de Dados com o Excel, o Power Pivot e o DAX
- Criar Relatórios do Power View Baseados em Mapas
- Incorporar Dados da Internet e Definir Predefinições de Relatórios do Power View
- Ajuda do Power Pivot
- Criar Relatórios Extraordinários do Power View – Parte 2
Neste tutorial, irá iniciar com um livro do Excel em branco.
As secções deste tutorial são as seguintes:
- Importar dados a partir de uma base de dados
- Importar dados a partir de uma folha de cálculo
- Importar dados utilizando a função copiar e colar
- Criar uma relação entre dados importados
- Ponto de Verificação e Questionário
No final deste tutorial encontrará um questionário que pode utilizar para testar a sua aprendizagem.
Esta série de tutoriais utiliza dados descritivos das Medalhas Olímpicas, países/regiões anfitriões e diversos eventos desportivos olímpicos. Recomendamos que respeite a ordem dos tutoriais.
Importar dados a partir de uma base de dados
Vamos começar este tutorial com um livro em branco. O objetivo nesta secção é efetuar uma ligação a uma origem de dados externa e importar os dados para o Excel para análise posterior.
Comecemos por transferir alguns dados da Internet. Os dados, inseridos numa base de dados do Microsoft Access, descrevem medalhas olímpicas.
Clique nas seguintes ligações para transferir os ficheiros que utilizaremos durante esta série de tutoriais. Transfira cada um dos quatro ficheiros para uma localização facilmente acessível, como Transferências ou Os Meus Documentos, ou para uma nova pasta que criar:
> Base de dados olympicMedals.accdb Access
> OlympicSports.xlsx livro do Excel
> Population.xlsx livro do Excel
> DiscImage_table.xlsx livro do ExcelNo Excel, abra um livro vazio.
Clique em Dados > Obter Dados da Base > de Dados > da Base de Dados do Microsoft Access. O friso ajusta-se dinamicamente com base na largura do seu livro, pelo que os comandos no friso poderão ter um aspeto ligeiramente diferente do ecrã seguinte.
Selecione o ficheiro OlympicMedals.accdb que transferiu e clique em Importar. É apresentada a seguinte janela Navegador, que apresenta as tabelas encontradas na base de dados. As tabelas numa base de dados são semelhantes às folhas de cálculo ou tabelas do Excel. Selecione a caixa Selecionar múltiplas tabelas e selecione todas as tabelas. Em seguida, clique em Carregar > Carregamento para.
É apresentada a janela Importar Dados.
Nota
Repare na caixa de verificação na parte inferior da janela que lhe permite Adicionar estes dados ao Modelo de Dados, apresentada no ecrã seguinte. Um Modelo de Dados é criado automaticamente quando importa ou trabalha com duas ou mais tabelas em simultâneo. Um Modelo de Dados integra as tabelas, permitindo uma análise extensa com tabelas dinâmicas, o Power Pivot e o Power View. Quando importa tabelas de uma base de dados, as relações de base de dados existentes entre essas tabelas são utilizadas para criar o Modelo de Dados no Excel. O Modelo de Dados é transparente no Excel, mas pode vê-lo e modificá-lo diretamente com o suplemento Power Pivot. O Modelo de Dados é abordado mais detalhadamente mais adiante neste tutorial.
Selecione a opção Relatório de Tabela Dinâmica, que importa as tabelas para o Excel e prepara uma tabela dinâmica para analisar as tabelas importadas e clique em OK.
Assim que os dados são importados, é criada uma Tabela Dinâmica utilizando as tabelas importadas.
Com os dados importados para o Excel e o Modelo de Dados criado automaticamente, está pronto para explorar os dados.
Explorar dados utilizando uma Tabela Dinâmica
É fácil explorar dados importados utilizando uma tabela dinâmica. Numa tabela dinâmica, arrasta campos (semelhantes às colunas no Excel) de tabelas (como as tabelas que acabou de importar da base de dados do Access) para diferentes áreas da Tabela Dinâmica, para ajustar a forma como os dados são apresentados. Uma Tabela Dinâmica tem quatro áreas: FILTROS, COLUNAS, LINHAS e VALORES.
Pode ter de fazer algumas tentativas para determinar qual a área para onde deve arrastar um campo. Pode arrastar quantos campos quiser das suas tabelas, até que a Tabela Dinâmica apresente os dados da forma que pretende. Sinta-se à vontade para explorar, arrastando campos para diferentes áreas da Tabela Dinâmica; os dados subjacentes não são afetados quando organiza campos numa Tabela Dinâmica.
Vamos explorar os dados de Medalhas Olímpicas na Tabela Dinâmica, começando pelos medalhistas olímpicos organizados por disciplina, tipo de medalha e país ou região do atleta.
Nos Campos da Tabela Dinâmica, expanda a tabela Medalhas clicando na seta ao lado. Localize o campo CON_PaísRegião na tabela Medalhas expandida e arraste-o para a área COLUNAS. CON significa Comité Olímpico Nacional, que é a unidade organizacional de um país ou região.
Em seguida, a partir da tabela Disciplinas, arraste a Disciplina para a área LINHAS.
Vamos filtrar as Disciplinas para exibir apenas cinco desportos: Tiro com arco, Mergulho, Esgrima, Patinagem Artística e Patinagem de Velocidade. Pode fazê-lo dentro da área dos Campos da Tabela Dinâmica ou com o filtro Rótulos de Linha na própria Tabela Dinâmica.
- Clique em qualquer parte da Tabela Dinâmica para garantir que a Tabela Dinâmica do Excel está selecionada. Na lista Campos da Tabela Dinâmica , onde a tabela Disciplinas é expandida, paire o cursor sobre o campo Disciplina e é apresentada uma seta pendente à direita do campo. Clique na lista pendente, clique em **(Selecionar Tudo)*** para remover todas as seleções e, em seguida, desloque-se para baixo e selecione Tiro com arco, Mergulho, Esgrima, Patinagem Artística e Patinagem de Velocidade. Clique em OK.
- Em alternativa, na secção Rótulos de Linha da Tabela Dinâmica, clique no menu pendente junto a Rótulos de Linha na Tabela Dinâmica, clique em (Selecionar Tudo) para remover todas as seleções e, em seguida, desloque-se para baixo e selecione Tiro com arco, Mergulho, Esgrima, Patinagem Artística e Patinagem rápida. Clique em OK.
Em Campos da Tabela Dinâmica, a partir da tabela Medalhas, arraste Medalha para a área VALORES. Uma vez que os valores devem ser numéricos, o Excel altera automaticamente Medalha para Contagem de Medalha.
A partir da tabela Medalhas, selecione novamente Medalha e arraste para a área FILTROS.
Vamos filtrar a Tabela Dinâmica para apresentar apenas os países ou regiões com mais de 90 medalhas no total. Eis como:
- Na Tabela Dinâmica, clique no menu pendente à direita de Rótulos de Coluna.
- Selecione Filtros de Valor e, em seguida, selecione Maior do Que...
- Escreva 90 no último campo (à direita). Clique em OK.
A sua Tabela Dinâmica terá o aspeto do ecrã seguinte.
Com pouco esforço, tem agora uma Tabela Dinâmica básica que inclui campos de três tabelas diferentes. O que tornou esta tarefa tão simples foram as relações previamente existentes entre as tabelas. Uma vez que as relações de tabela existiam na base de dados de origem e porque importou todas as tabelas numa única operação, foi possível ao Excel recriar essas relações no Modelo de Dados.
Mas e se os seus dados forem provenientes de diferentes origens ou tiverem sido importados posteriormente? Normalmente, pode criar relações com novos dados com base em colunas correspondentes. No passo seguinte, importará tabelas adicionais e aprenderá a criar novas relações.
Importar dados a partir de uma folha de cálculo
Agora, vamos importar dados de outra origem, desta vez a partir de um livro existente e, em seguida, especificar as relações entre os nossos dados existentes e os novos dados. As relações permitem-lhe analisar coleções de dados no Excel e criar visualizações interessantes e envolventes a partir dos dados que importa.
Vamos começar por criar uma folha de cálculo em branco e, em seguida, vamos importar dados a partir de um livro do Excel.
Insira uma nova folha de cálculo do Excel e atribua-lhe o nome Desportos.
Localize a pasta que contém os ficheiros de dados de exemplo transferidos e abra OlympicSports.xlsx.
Selecione e copie os dados em Folha1. Se selecionar uma célula com dados, tal como a célula A1, pode premir Ctrl + A para selecionar todos os dados adjacentes. Feche o livro OlympicSports.xlsx.
Na folha de cálculo Desportos, coloque o cursor na célula A1 e cole os dados.
Com os dados ainda realçados, prima Ctrl + T para formatar os dados como uma tabela. Também pode formatar os dados como uma tabela a partir do friso ao selecionar Formato BASE > como Tabela. Uma vez que os dados têm cabeçalhos, selecione A minha tabela tem cabeçalhos na janela Criar Tabela que aparece, tal como mostrado aqui.
Formatar os dados como uma tabela tem muitas vantagens. Pode atribuir um nome a uma tabela, facilitando a sua identificação. Também pode estabelecer relações entre tabelas, permitindo a exploração e análise em Tabelas Dinâmicas, Power Pivot e Power View.
Atribua um nome à tabela. Em Propriedades DE ESTRUTURA > DA TABELA, localize o campo Nome da Tabela e escreva Desportos. O livro tem o aspeto do ecrã seguinte.
Guarde o livro.
Importar dados utilizando a função copiar e colar
Depois de importarmos dados a partir de um livro do Excel, vamos importar dados de uma tabela que encontramos numa página Web ou qualquer outra origem a partir da qual podemos copiar e colar no Excel. Nos passos seguintes, iremos adicionar as cidades anfitriãs dos Jogos Olímpicos a partir de uma tabela.
- Insira uma nova folha de cálculo do Excel e atribua-lhe o nome Anfitriões.
- Selecione e copie a tabela seguinte, incluindo os cabeçalhos de tabela.
- No Excel, coloque o cursor na célula A1 da folha de cálculo Anfitriões e cole os dados.
- Formate os dados como uma tabela. Conforme descrito anteriormente neste tutorial, prima Ctrl + T para formatar os dados como uma tabela ou a partir do Formato BASE > como Tabela. Uma vez que os dados têm cabeçalhos, selecione A minha tabela tem cabeçalhos na janela Criar Tabela que é apresentada.
- Atribua um nome à tabela. Em PROPRIEDADES DE ESTRUTURA > DA TABELA , localize o campo Nome da Tabela e escreva Anfitriões.
- Selecione a coluna Edição e, no separador BASE, formate-a como Número com 0 casas decimais.
- Guarde o livro. O seu livro terá o aspeto do ecrã seguinte.
Agora que tem um livro do Excel com tabelas, pode criar relações entre elas. Criar relações entre tabelas permite processar os dados das duas tabelas.
Criar uma relação entre dados importados
Pode começar imediatamente a usar campos na sua Tabela Dinâmica a partir das tabelas importadas. Se o Excel não conseguir determinar como incorporar um campo na Tabela Dinâmica, deve ser estabelecida uma relação com o Modelo de Dados existente. Nos passos seguintes, irá aprender a criar uma relação entre os dados que importou de diferentes origens.
Na Folha1, na parte superior dosCampos da Tabela Dinâmica, clique emTodos para ver a lista completa de tabelas disponíveis, conforme mostrado no ecrã seguinte.
Percorra a lista para ver as novas tabelas que acabou de adicionar.
Expanda Desportos e selecione Desporto para o adicionar à Tabela Dinâmica. Repare que o Excel avisa-o para criar uma relação, tal como mostrado no ecrã seguinte.
Esta notificação ocorre porque usou campos de uma tabela que não faz parte do Modelo de Dados subjacente. Uma forma de adicionar uma tabela ao Modelo de Dados é criar uma relação com uma tabela que já se encontra no Modelo de Dados. Para criar a relação, uma das tabelas deve ter uma coluna de valores únicos, não repetidos. Nos dados de exemplo, a tabela Disciplinas importada da base de dados contém um campo com códigos de desportos, chamado IDDoDesporto. Esses mesmos códigos de desportos estão presentes como um campo nos dados do Excel que importámos. Vamos criar a relação.Clique em CRIAR... na área realçada dos Campos da Tabela Dinâmica para abrir a caixa de diálogo Criar Relação, tal como mostrado no ecrã seguinte.
Em Tabela, selecione Tabela de Modelo de Dados: Disciplinas na lista pendente.
Em Coluna (Externa), selecione IDDoDesporto.
Em Tabela Relacionada, selecione Tabela de Modelo de Dados: Desporto.
Em Coluna Relacionada (Principal), selecione IDDoDesporto.
Clique em OK.
As alterações da Tabela Dinâmica refletem a nova relação. No entanto, a Tabela Dinâmica ainda não está pronta, devido à ordem dos campos na área LINHAS. Disciplina é uma subcategoria de um determinado desporto mas, uma vez que colocámos Disciplina acima de Desporto na área LINHAS, não está corretamente organizada. O ecrã a seguir mostra essa ordem incorreta.
Na área LINHAS, mova Desporto para cima de Disciplina. Assim está melhor, e a Tabela Dinâmica exibe os dados da forma como pretende, tal como mostrado no ecrã seguinte.
Em segundo plano, o Excel está a criar um Modelo de Dados que pode ser utilizado em todo o livro, em qualquer Tabela Dinâmica, Gráfico Dinâmico, no Power Pivot ou em qualquer relatório do Power View. As relações entre tabelas são a base dos Modelos de Dados, e são aquilo que determina os caminhos de navegação e cálculo.
No próximo tutorial, expanda as relações do Modelo de Dados com o Excel, o Power Pivot**e o DAX**, baseia-se no que aprendeu aqui e explica como expandir o Modelo de Dados através de um suplemento avançado e visual do Excel chamado Power Pivot. Também vai aprender a calcular colunas numa tabela e a utilizar essa coluna calculada para que uma tabela não relacionada de outra forma possa ser adicionada ao seu Modelo de Dados.
Ponto de verificação e Questionário
Rever o Que Aprendeu
Agora tem um livro do Excel que inclui uma Tabela Dinâmica que acede a dados em várias tabelas, algumas das quais foram importadas em separado. Aprendeu a importar a partir de uma base de dados, de outro livro do Excel e com a função copiar e colar no Excel.
Para processar estes dados, teve de criar relações entre tabelas, que o Excel utiliza para combinar as linhas. Aprendeu igualmente que ter colunas numa tabela que se relaciona com dados noutra tabela é essencial para criar relações e para procurar linhas relacionadas.
Está pronto para o próximo tutorial desta série. Aqui está uma ligação:
Tutorial: Expandir relações de Modelos de Dados através do Excel, do Power Pivot e de DAX
QUESTIONÁRIO
Pretende ver se ainda se lembra do que aprendeu? Eis a sua oportunidade. O questionário seguinte destaca as funcionalidades, capacidades ou requisitos aprendidos neste tutorial. Encontrará as respostas na parte inferior da página. Boa sorte!
Pergunta 1: Porque é que é importante converter dados importados em tabelas?
A: Não é preciso convertê-los em tabelas, porque todos os dados importados são automaticamente transformados em tabelas.
B: Se converter dados importados em tabelas, estes serão excluídos do Modelo de Dados. Apenas quando são excluídos do Modelo de Dados estão disponíveis em Tabelas Dinâmicas, Power Pivot e Power View.
C: Se converter dados importados em tabelas, estes podem ser incluídos no Modelo de Dados e 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 origens de dados pode importar para o Excel e incluir no Modelo de Dados?
A: Bases de dados do Access e muitas outras bases de dados.
B: Ficheiros do Excel existentes.
C: Tudo o que puder copiar e colar no Excel e formatar como uma tabela, incluindo tabelas de dados em sites, documentos ou qualquer outro elemento que possa ser colado no Excel.
D: Todas as respostas acima
Pergunta 3: Numa Tabela Dinâmica, o que acontece quando reordena campos nas quatro áreas dos Campos de Tabela Dinâmica?
A: Nada – não pode reordenar campos depois de os colocar nas áreas dos Campos de Tabela Dinâmica.
B: O formato de Tabela Dinâmica é alterado para refletir o esquema, mas os dados subjacentes não são afetados.
C: O formato de Tabela Dinâmica é alterado para refletir o esquema, mas os dados subjacentes são permanentemente alterados.
D: Os dados subjacentes são alterados, o que resulta em novos conjuntos de dados.
Pergunta 4: Ao criar uma relação entre tabelas, o que é necessário?
A: Nenhuma tabela pode ter qualquer coluna que contenha valores exclusivos e não repetidos.
B: Uma tabela não deve fazer parte do livro do Excel.
C: As colunas não devem ser convertidas em tabelas.
D: Nenhuma das respostas anteriores.
Respostas do Questionário
- Resposta correta: C
- Resposta correta: D
- Resposta correta: B
- Resposta correta: D
Nota
Os dados e as imagens nestas séries de tutoriais são baseados no seguinte:
- Olympics Dataset do Guardian News & Media Ltd.
- Imagens de bandeiras do CIA Factbook (cia.gov)
- Dados de população do The World Bank (worldbank.org)
- Pictogramas Desportivos dos Jogos Olímpicos de Thadius856 e Parutakupiu