Excel Avançado - Modelo de Dados

O Excel 2013 possui recursos poderosos de análise de dados. Você pode construir um modelo de dados e, em seguida, criar relatórios interativos incríveis usando o Power View. Você também pode usar os recursos e capacidades do Microsoft Business Intelligence no Excel, Tabelas Dinâmicas, Power Pivot e Power View.

Data Modelé usado para construir um modelo em que dados de várias fontes podem ser combinados criando relacionamentos entre as fontes de dados. Um modelo de dados integra as tabelas, permitindo uma análise abrangente usando tabelas dinâmicas, Power Pivot e Power View.

UMA Data Modelé criado automaticamente quando você importa duas ou mais tabelas simultaneamente de um banco de dados. Os relacionamentos de banco de dados existentes entre essas tabelas são usados ​​para criar o Modelo de Dados no Excel.

Step 1 - Abra uma nova pasta de trabalho em branco no Excel.

Step 2 - Clique no DATA aba.

Step 3 - no Get External Data grupo, clique na opção From Access. oSelect Data Source a caixa de diálogo é aberta.

Step 4 - Selecione Events.accdb, Arquivo de banco de dados de acesso a eventos.

Step 5 - o Select Table janela, exibindo todos os tables encontrado no banco de dados, aparece.

Step 6- As tabelas de um banco de dados são semelhantes às tabelas do Excel. Verifica a‘Enable selection of multiple tables’caixa e selecione todas as tabelas. Então cliqueOK.

Step 7 - o Import Datajanela aparece. Selecione osPivotTable Reportopção. Esta opção importa as tabelas para o Excel e prepara uma tabela dinâmica para analisar as tabelas importadas. Observe que a caixa de seleção na parte inferior da janela -‘Add this data to the Data Model’ é selecionado e desativado.

Step 8 - Os dados são importados e um PivotTable é criado usando as tabelas importadas.

Você importou os dados para o Excel e o Data Modelé criado automaticamente. Agora, você pode explorar os dados nas cinco tabelas, que possuem relacionamentos definidos entre elas.

Explorar dados usando tabela dinâmica

Step 1- Você sabe como adicionar campos à Tabela Dinâmica e arrastar campos entre áreas. Mesmo se você não tiver certeza do relatório final que deseja, pode brincar com os dados e escolher o relatório mais adequado.

No PivotTable Fields, clique na seta ao lado da mesa - Medalspara expandi-lo para mostrar os campos dessa tabela. Arrastar oNOC_CountryRegion campo no Medals mesa para o COLUMNS área.

Step 2 - Arraste Discipline de Disciplines mesa para o ROWS área.

Step 3 - Filtro Disciplinepara exibir apenas cinco esportes: tiro com arco, mergulho, esgrima, patinação artística e patinação de velocidade. Isso pode ser feito emPivotTable Fields área, ou da Row Labels filtro na própria tabela dinâmica.

Step 4 - em PivotTable Fields, de Medals mesa, arraste a medalha para o VALUES área.

Step 5 - Do Medals mesa, selecione Medal novamente e arraste-o para o FILTERS área.

Step 6 - Clique no botão da lista suspensa à direita do Column rótulos.

Step 7 - Selecione Value Filters e então selecione Greater Than

Step 8 - Clique OK.

O valor que Filters dialog caixa para contagem de medalhas is greater than parece.

Step 9 - Tipo 80 no Right Field.

Step 10 - Clique OK.

A tabela dinâmica exibe apenas essas regiões, que tem mais de 80 medalhas no total.

Você pode analisar seus dados de diferentes tabelas e chegar ao relatório específico que deseja em apenas algumas etapas. Isso foi possível devido aos relacionamentos pré-existentes entre as tabelas no banco de dados de origem. À medida que você importava todas as tabelas do banco de dados ao mesmo tempo, o Excel recriou os relacionamentos em seu Modelo de Dados.

Se você não importar as tabelas ao mesmo tempo, ou se os dados forem de fontes diferentes ou se você adicionar novas tabelas à sua pasta de trabalho, você deve criar o Relationships entre o Tables por você mesmo.

Criar relação entre tabelas

Os relacionamentos permitem que você analise suas coleções de dados no Excel e crie relatórios interessantes e estéticos a partir dos dados importados.

Step 1 - Insert uma nova planilha.

Step 2- Crie uma nova tabela com novos dados. Nomeie a nova tabela comoSports.

Step 3 - Agora você pode criar relacionamento entre esta nova tabela e as outras tabelas que já existem no Data Modelno Excel. Renomear a Folha1 comoMedals e Folha2 como Sports.

No Medals sheet, no PivotTable Fields List, clique All. Uma lista completa de tabelas disponíveis será exibida. A tabela recém-adicionada -Sports também será exibido.

Step 4 - Clique em Sports. Na lista expandida de campos, selecioneSports. O Excel envia uma mensagem para você criar um relacionamento entre as tabelas.

Step 5 - Clique em CREATE. oCreate Relationship a caixa de diálogo é aberta.

Step 6- Para criar o relacionamento, uma das tabelas deve ter uma coluna de valores exclusivos e não repetidos. NoDisciplines tabela, SportIDcoluna tem esses valores. A mesaSports que criamos também tem o SportIDcoluna. NoTable, selecione Disciplines.

Step 7 - em Column (Foreign), selecione SportID.

Step 8 - em Related Table, selecione Sports.

Step 9 - em Related Column (Primary), SportID é selecionado automaticamente. CliqueOK.

Step 10 - o PivotTable é modificado para refletir a adição do novo Data FieldEsporte. Ajuste a ordem dos campos na área de Linhas para manter oHierarchy. Nesse caso,Sport deve ser o primeiro e Discipline deve ser o próximo, como Discipline será aninhado em Sport como uma subcategoria.