Excel Power Pivot - Guia rápido

O Excel Power Pivot é uma ferramenta eficiente e poderosa que vem com o Excel como um suplemento. Com o Power Pivot, você pode carregar centenas de milhões de linhas de dados de fontes externas e gerenciar os dados de forma eficaz com seu poderoso mecanismo xVelocity em uma forma altamente compactada. Isso possibilita realizar cálculos, analisar dados e chegar a um relatório para tirar conclusões e tomar decisões. Assim, seria possível para uma pessoa com experiência prática com o Excel, realizar a análise de dados de ponta e tomada de decisão em questão de poucos minutos.

Este tutorial cobrirá o seguinte -

Recursos do Power Pivot

O que torna o Power Pivot uma ferramenta forte é o conjunto de seus recursos. Você aprenderá os vários recursos do Power Pivot no capítulo - Recursos do Power Pivot.

Dados do Power Pivot de várias fontes

O Power Pivot pode agrupar dados de várias fontes de dados para realizar os cálculos necessários. Você aprenderá como inserir dados no Power Pivot, no capítulo - Carregando dados no Power Pivot.

Modelo de dados Power Pivot

O poder do Power Pivot reside em seu modelo de banco de dados. Os dados são armazenados na forma de tabelas de dados no Modelo de Dados. Você pode criar relacionamentos entre as tabelas de dados para combinar os dados de diferentes tabelas de dados para análise e relatório. O capítulo - Compreendendo o modelo de dados (Power Pivot Database) fornece os detalhes sobre o modelo de dados.

Gerenciando Modelo de Dados e Relacionamentos

Você precisa saber como gerenciar as tabelas de dados no Modelo de Dados e as relações entre elas. Você obterá os detalhes disso no capítulo - Gerenciando o modelo de dados do Power Pivot.

Criação de Power Pivot Tables e Power Pivot Charts

Power PivotTables e Power Pivot Charts fornecem uma maneira de analisar os dados para chegar a conclusões e / ou decisões.

Você aprenderá como criar tabelas dinâmicas do Power nos capítulos - Criando uma tabela dinâmica do Power e tabelas dinâmicas planificadas.

Você aprenderá como criar Power PivotCharts no capítulo - Power PivotCharts.

DAX Basics

DAX é a linguagem usada no Power Pivot para realizar cálculos. As fórmulas no DAX são semelhantes às fórmulas do Excel, com uma diferença - enquanto as fórmulas do Excel são baseadas em células individuais, as fórmulas DAX são baseadas em colunas (campos).

Você entenderá os fundamentos do DAX no capítulo - Noções básicas do DAX.

Explorando e relatando dados do Power Pivot

Você pode explorar os dados do Power Pivot que estão no modelo de dados com as tabelas e gráficos do Power Pivot. Você aprenderá como explorar e relatar dados ao longo deste tutorial.

Hierarquias

Você pode definir hierarquias de dados em uma tabela de dados para que seja fácil lidar com campos de dados relacionados juntos em Power PivotTables. Você aprenderá os detalhes da criação e do uso de Hierarquias no capítulo - Hierarquias no Power Pivot.

Relatórios Estéticos

Você pode criar relatórios estéticos de sua análise de dados com Power Pivot Charts e / ou Power Pivot Charts. Você tem várias opções de formatação disponíveis para destacar os dados significativos nos relatórios. Os relatórios são de natureza interativa, permitindo que a pessoa que olha o relatório compacto visualize qualquer um dos detalhes necessários de forma rápida e fácil.

Você aprenderá esses detalhes no capítulo - Relatórios estéticos com dados do Power Pivot.

O Power Pivot no Excel fornece um Modelo de Dados conectando várias fontes de dados diferentes com base nas quais os dados podem ser analisados, visualizados e explorados. A interface fácil de usar fornecida pelo Power Pivot permite que uma pessoa com experiência prática no Excel carregue dados sem esforço, gerencie os dados como tabelas de dados, crie relacionamentos entre as tabelas de dados e execute os cálculos necessários para chegar a um relatório .

Neste capítulo, você aprenderá o que torna o Power Pivot uma ferramenta forte e procurada por analistas e tomadores de decisão.

Power Pivot na fita

A primeira etapa para prosseguir com o Power Pivot é garantir que a guia POWERPIVOT esteja disponível na faixa de opções. Se você tiver o Excel 2013 ou versões posteriores, a guia POWERPIVOT aparecerá na faixa de opções.

Se você tiver o Excel 2010, POWERPIVOT A guia pode não aparecer na faixa de opções se você ainda não habilitou o suplemento Power Pivot.

Suplemento Power Pivot

O Power Pivot Add-in é um suplemento COM que precisa ser habilitado para obter os recursos completos do Power Pivot no Excel. Mesmo quando a guia POWERPIVOT aparece na faixa de opções, você precisa garantir que o add-in está habilitado para acessar todos os recursos do Power Pivot.

Step 1 - Clique na guia ARQUIVO na faixa de opções.

Step 2- Clique em Opções na lista suspensa. A caixa de diálogo Opções do Excel é exibida.

Step 3 - Siga as instruções a seguir.

• Clique em Add-Ins.

• Na caixa Gerenciar, selecione Suplementos de COM na lista suspensa.

• Clique no botão Go. A caixa de diálogo Suplementos COM é exibida.

• Verifique o Power Pivot e clique em OK.

O que é Power Pivot?

Excel Power Pivot é uma ferramenta para integrar e manipular grandes volumes de dados. Com o Power Pivot, você pode facilmente carregar, classificar e filtrar conjuntos de dados que contêm milhões de linhas e realizar os cálculos necessários. Você pode utilizar o Power Pivot como uma solução ad hoc de relatórios e análises.

A faixa de opções do Power Pivot, conforme mostrado abaixo, tem vários comandos, que vão desde o gerenciamento do modelo de dados à criação de relatórios.

A janela do Power Pivot terá a faixa de opções conforme mostrado abaixo -

Por que o Power Pivot é uma ferramenta forte?

Quando você invoca o Power Pivot, o Power Pivot cria definições de dados e conexões que são armazenadas com seu arquivo do Excel em um formato compactado. Quando os dados na fonte são atualizados, eles são atualizados automaticamente em seu arquivo Excel. Isso facilita o uso dos dados mantidos em outro lugar, mas é necessário para estudar o estudo de vez em quando e chegar a decisões. Os dados de origem podem estar em qualquer formato - desde um arquivo de texto ou uma página da web até os diferentes bancos de dados relacionais.

A interface amigável do Power Pivot na janela do PowerPivot permite que você execute operações de dados sem o conhecimento de qualquer linguagem de consulta de banco de dados. Você pode então criar um relatório de sua análise em poucos segundos. Os relatórios são versáteis, dinâmicos e interativos e permitem que você investigue ainda mais os dados para obter insights e chegar a conclusões / decisões.

Os dados com os quais você trabalha no Excel e na janela do Power Pivot são armazenados em um banco de dados analítico dentro da pasta de trabalho do Excel e um poderoso mecanismo local carrega, consulta e atualiza os dados nesse banco de dados. Como os dados estão no Excel, eles estão imediatamente disponíveis para Tabelas Dinâmicas, Gráficos Dinâmicos, Power View e outros recursos do Excel que você usa para agregar e interagir com os dados. A apresentação e interatividade dos dados são fornecidas pelo Excel e os dados e os objetos de apresentação do Excel estão contidos no mesmo arquivo de pasta de trabalho. O Power Pivot oferece suporte a arquivos de até 2 GB e permite que você trabalhe com até 4 GB de dados na memória.

Recursos Power para Excel com Power Pivot

Os recursos do Power Pivot são gratuitos com o Excel. O Power Pivot aprimorou o desempenho do Excel com recursos avançados que incluem o seguinte -

• Capacidade de lidar com grandes volumes de dados, compactados em pequenos arquivos, com velocidade incrível.

• Filtre dados e renomeie colunas e tabelas durante a importação.

• Organize as tabelas em páginas com guias individuais na janela do Power Pivot em comparação com as tabelas do Excel distribuídas por toda a pasta de trabalho ou várias tabelas na mesma planilha.

• Crie relacionamentos entre as tabelas, de modo a analisar os dados nas tabelas coletivamente. Antes do Power Pivot, era necessário usar muito a função VLOOKUP para combinar os dados em uma única tabela antes dessa análise. Isso costumava ser trabalhoso e sujeito a erros.

• Adicione poder à tabela dinâmica simples com muitos recursos adicionais.

• Fornece linguagem DAX (Data Analysis Expressions) para escrever fórmulas avançadas.

• Adicione campos calculados e colunas calculadas às tabelas de dados.

• Crie KPIs para usar em tabelas dinâmicas e relatórios do Power View.

Você entenderá os recursos do Power Pivot em detalhes no próximo capítulo.

Usos do Power Pivot

Você pode usar o Power Pivot para o seguinte -

• Para realizar análises de dados poderosas e criar modelos de dados sofisticados.

• Para mash-up grandes volumes de dados de várias fontes diferentes rapidamente.

• Para realizar a análise de informações e compartilhar os insights de forma interativa.

• Para escrever fórmulas avançadas com a linguagem Data Analysis Expressions (DAX).

• Para criar indicadores-chave de desempenho (KPIs).

Modelagem de Dados com Power Pivot

O Power Pivot fornece recursos avançados de modelagem de dados no Excel. Os dados no Power Pivot são gerenciados no Modelo de Dados, também conhecido como banco de dados Power Pivot. Você pode usar o Power Pivot para ajudá-lo a obter novos insights sobre seus dados.

Você pode criar relacionamentos entre tabelas de dados para que possa realizar análises de dados nas tabelas coletivamente. Com DAX, você pode escrever fórmulas avançadas. Você pode criar campos calculados e colunas calculadas nas tabelas de dados no Modelo de Dados.

Você pode definir hierarquias nos dados para usar em qualquer lugar na pasta de trabalho, incluindo Power View. Você pode criar KPIs para usar em tabelas dinâmicas e relatórios do Power View para mostrar rapidamente se o desempenho está dentro ou fora do alvo para uma ou mais métricas.

Business Intelligence com Power Pivot

Business intelligence (BI) é essencialmente o conjunto de ferramentas e processos que as pessoas usam para coletar dados, transformá-los em informações significativas e, então, tomar melhores decisões. Os recursos de BI do Power Pivot no Excel permitem que você reúna dados, visualize dados e compartilhe informações com pessoas em sua organização em vários dispositivos.

Você pode compartilhar sua pasta de trabalho em um ambiente do SharePoint que tenha os Serviços do Excel habilitados. No servidor do SharePoint, o Serviços do Excel processa e renderiza os dados em uma janela do navegador onde outras pessoas podem analisar os dados.

O recurso mais importante e poderoso do Power Pivot é seu banco de dados - Modelo de Dados. O próximo recurso significativo é o mecanismo analítico in-memory xVelocity que torna possível trabalhar em grandes bancos de dados múltiplos em questão de poucos minutos. Existem alguns recursos mais importantes que vêm com o suplemento PowerPivot.

Neste capítulo, você terá uma breve visão geral dos recursos do Power Pivot, que são ilustrados em detalhes posteriormente.

Carregando dados de fontes externas

Você pode carregar dados no Modelo de Dados de fontes externas de duas maneiras -

• Carregue dados no Excel e crie um modelo de dados Power Pivot.

• Carregue dados diretamente no Power Pivot Data Model.

A segunda maneira é mais eficiente devido à maneira eficiente como o Power Pivot lida com os dados na memória.

Para obter mais detalhes, consulte o capítulo - Carregando dados no Power Pivot.

Janela do Excel e janela Power Pivot

Quando você começa a trabalhar com o Power Pivot, duas janelas são abertas simultaneamente - a janela do Excel e a janela do Power Pivot. É por meio da janela PowerPivot que você pode carregar dados diretamente no Modelo de Dados, exibir os dados em Exibição de Dados e Exibição de Diagrama, Criar relacionamentos entre tabelas, gerenciar os relacionamentos e criar os relatórios de Tabela Dinâmica do Power e / ou Gráfico do PowerPivot.

Você não precisa ter os dados nas tabelas do Excel ao importar dados de fontes externas. Se você tiver dados como tabelas do Excel na pasta de trabalho, poderá adicioná-los ao Modelo de Dados, criando tabelas de dados no Modelo de Dados vinculadas às tabelas do Excel.

Quando você cria uma Tabela Dinâmica ou Gráfico Dinâmico na janela do Power Pivot, eles são criados na janela do Excel. No entanto, os dados ainda são gerenciados a partir do Modelo de Dados.

Você sempre pode alternar entre a janela do Excel e a janela do Power Pivot a qualquer momento, facilmente.

Modelo de dados

O Modelo de Dados é o recurso mais poderoso do Power Pivot. Os dados obtidos de várias fontes de dados são mantidos no Modelo de Dados como tabelas de dados. Você pode criar relacionamentos entre as tabelas de dados para que possa combinar os dados nas tabelas para análise e relatório.

Você aprenderá em detalhes sobre o Modelo de Dados no capítulo - Compreendendo o Modelo de Dados (Banco de Dados Power Pivot).

Otimização de Memória

O Power Pivot Data Model usa armazenamento xVelocity, que é altamente compactado quando os dados são carregados na memória, o que torna possível armazenar centenas de milhões de linhas na memória.

Portanto, se você carregar dados diretamente no Modelo de Dados, estará fazendo isso de forma eficiente e altamente compactada.

Tamanho de arquivo compacto

Se os dados forem carregados diretamente no Modelo de Dados, quando você salva o arquivo Excel, ele ocupa muito menos espaço no disco rígido. Você pode comparar os tamanhos dos arquivos do Excel, o primeiro com o carregamento de dados no Excel e a criação do Modelo de Dados e o segundo com o carregamento de dados diretamente no Modelo de Dados, pulando a primeira etapa. O segundo será até 10 vezes menor que o primeiro.

Power PivotTables

Você pode criar as tabelas do Power Pivot na janela do Power Pivot. As Tabelas Dinâmicas assim criadas são baseadas nas tabelas de dados do Modelo de Dados, tornando possível combinar dados das tabelas relacionadas para análise e relatório.

Power PivotCharts

Você pode criar os Power PivotCharts na janela do Power Pivot. Os gráficos dinâmicos assim criados são baseados nas tabelas de dados do Modelo de Dados, tornando possível combinar os dados das tabelas relacionadas para análise e relatório. Os Power PivotCharts possuem todos os recursos dos Excel PivotCharts e muitos mais, como botões de campo.

Você também pode ter combinações de Power PivotTable e Power PivotChart.

Idioma DAX

A força do Power Pivot vem da linguagem DAX que pode ser usada efetivamente no modelo de dados para realizar cálculos nos dados nas tabelas de dados. Você pode ter Colunas Calculadas e Campos Calculados definidos pelo DAX que podem ser usados ​​nas Tabelas Dinâmicas do Power e nos Gráficos Dinâmicos do Power.

Neste capítulo, aprenderemos a carregar dados no Power Pivot.

Você pode carregar dados no Power Pivot de duas maneiras -

• Carregue dados no Excel e adicione-os ao Modelo de Dados

• Carregue dados diretamente no PowerPivot, preenchendo o Modelo de Dados, que é o banco de dados PowerPivot.

Se você quer os dados para o Power Pivot, faça da segunda maneira, sem o Excel saber disso. Isso ocorre porque você carregará os dados apenas uma vez, em formato altamente compactado. Para entender a magnitude da diferença, suponha que você carregue os dados no Excel adicionando-os primeiro ao Modelo de Dados; o tamanho do arquivo é, digamos, 10 MB.

Se você carregar dados no PowerPivot e, portanto, no Modelo de Dados, ignorando a etapa extra do Excel, o tamanho do arquivo pode ser de apenas 1 MB.

Fontes de dados suportadas pelo Power Pivot

Você pode importar dados para o Modelo de Dados Power Pivot de várias fontes de dados ou estabelecer conexões e / ou usar as conexões existentes. O Power Pivot oferece suporte às seguintes fontes de dados -

• Banco de dados relacional SQL Server

• Banco de dados Microsoft Access

• SQL Server Analysis Services

• SQL Server Reporting Services (SQL 2008 R2)

• Feeds de dados ATOM

• Arquivos de texto

• Microsoft SQL Azure

• Oracle

• Teradata

• Sybase

• Informix

• IBM DB2

• Vinculação e incorporação de objetos de banco de dados / Conectividade de banco de dados aberto

• (OLEDB / ODBC) fontes

• Arquivo Microsoft Excel

• Arquivo de texto

Carregando dados diretamente no PowerPivot

Para carregar dados diretamente no Power Pivot, execute o seguinte -

• Abra uma nova pasta de trabalho.

• Clique na guia POWERPIVOT na faixa de opções.

• Clique em Gerenciar no grupo Modelo de Dados.

A janela PowerPivot é aberta. Agora você tem duas janelas - a janela da pasta de trabalho do Excel e a janela do PowerPivot para Excel que está conectada à sua pasta de trabalho.

• Clique no Home guia na janela PowerPivot.

• Clique From Database no grupo Obter dados externos.

• Selecione From Access.

O Assistente de importação de tabela é exibido.

• Navegue até o arquivo de banco de dados do Access.

• Forneça um nome de conexão amigável.

• Se o banco de dados for protegido por senha, preencha também esses detalhes.

Clique no Next→ botão. O Assistente de importação de tabela exibe as opções para escolher como importar dados.

Clique em Selecionar em uma lista de tabelas e visualizações para escolher os dados a serem importados.

Clique no Next→ botão. O Assistente de importação de tabela exibe as tabelas e exibições no banco de dados do Access que você selecionou.

Marque a caixa Medalhas.

Como você pode observar, você pode selecionar as tabelas marcando as caixas, visualizar e filtrar as tabelas antes de adicioná-las à Tabela Dinâmica e / ou selecionar as tabelas relacionadas.

Clique no Preview & Filter botão.

Como você pode ver, você pode selecionar colunas específicas marcando as caixas nos rótulos das colunas, filtrar as colunas clicando na seta suspensa no rótulo da coluna para selecionar os valores a serem incluídos.

• Clique OK.

• Clique no Select Related Tables botão.

• O Power Pivot verifica quais outras tabelas estão relacionadas à tabela de medalhas selecionada, se houver uma relação.

Você pode ver que o Power Pivot descobriu que as Disciplinas da tabela estão relacionadas às Medalhas da tabela e a selecionou. Clique em Concluir.

Telas do Assistente de Importação de Tabela - Importinge mostra o status da importação. Isso levará alguns minutos e você pode interromper a importação clicando noStop Import botão.

Uma vez que os dados são importados, o Assistente de Importação de Tabela exibe - Successe mostra os resultados da importação conforme mostrado na captura de tela abaixo. Clique em Fechar.

O Power Pivot exibe as duas tabelas importadas em duas guias.

Você pode percorrer os registros (linhas da tabela) usando o Record setas abaixo das guias.

Assistente de Importação de Tabela

Na seção anterior, você aprendeu como importar dados do Access por meio do Assistente de Importação de Tabela.

Observe que as opções do Assistente de importação de tabela mudam de acordo com a fonte de dados selecionada para conexão. Você pode querer saber quais fontes de dados pode escolher.

Clique From Other Sources na janela do Power Pivot.

O Assistente de Importação de Tabela - Connect to a Data Sourceparece. Você pode criar uma conexão com uma fonte de dados ou pode usar uma que já exista.

Você pode rolar pela lista de conexões no Assistente de Importação de Tabela para saber as conexões de dados compatíveis com o Power Pivot.

• Role para baixo até os arquivos de texto.

• Selecione Excel File.

• Clique no Next→ botão. O Assistente de importação de tabela é exibido - Conecte-se a um arquivo do Microsoft Excel.

• Navegue até o arquivo do Excel na caixa Caminho do arquivo do Excel.

• Verifica a caixa - Use first row as column headers.

• Clique no Next→ botão. O Assistente de Importação de Tabela exibe -Select Tables and Views.

• Verifica a caixa Product Catalog$. Clique noFinish botão.

Você verá o seguinte Successmensagem. Clique em Fechar.

Você importou uma tabela e também criou uma conexão com o arquivo Excel que contém várias outras tabelas.

Abrindo conexões existentes

Depois de estabelecer uma conexão com uma fonte de dados, você pode abri-la mais tarde.

Clique em Conexões existentes na janela do PowerPivot.

A caixa de diálogo Conexões existentes é exibida. Selecione Dados de vendas do Excel na lista.

Clique no botão Abrir. O Assistente de importação de tabela aparece exibindo as tabelas e visualizações.

Selecione as tabelas que deseja importar e clique em Finish.

As cinco tabelas selecionadas serão importadas. CliqueClose.

Você pode ver que as cinco tabelas são adicionadas ao Power Pivot, cada uma em uma nova guia.

Criação de tabelas vinculadas

As tabelas vinculadas são um link ativo entre a tabela no Excel e a tabela no Modelo de Dados. As atualizações na tabela no Excel atualizam automaticamente os dados na tabela de dados no modelo.

Você pode vincular a tabela do Excel ao Power Pivot em algumas etapas, como segue -

• Crie uma tabela do Excel com os dados.

• Clique na guia POWERPIVOT na faixa de opções.

• Clique Add to Data Model no grupo Tabelas.

A tabela do Excel está vinculada à Tabela de Dados correspondente no PowerPivot.

Você pode ver que as Ferramentas de Tabela com a guia - Tabela Vinculada é adicionada à janela do Power Pivot. Se você clicarGo to Excel Table, você mudará para a planilha do Excel. Se você clicarManage, você retornará à tabela vinculada na janela do Power Pivot.

Você pode atualizar a tabela vinculada automática ou manualmente.

Observe que você pode vincular uma tabela do Excel apenas se ela estiver presente na pasta de trabalho com o Power Pivot. Se você tiver tabelas do Excel em uma pasta de trabalho separada, precisará carregá-las conforme explicado na próxima seção.

Carregando arquivos do Excel

Se você deseja carregar os dados de pastas de trabalho do Excel, lembre-se do seguinte:

• O Power Pivot considera a outra pasta de trabalho do Excel como um banco de dados e apenas as planilhas são importadas.

• O Power Pivot carrega cada planilha como uma tabela.

• O Power Pivot não reconhece tabelas únicas. Portanto, o Power Pivot não consegue reconhecer se há várias tabelas em uma planilha.

• O Power Pivot não reconhece nenhuma informação adicional além da tabela em uma planilha.

Portanto, mantenha cada tabela em uma planilha separada.

Assim que seus dados na pasta de trabalho estiverem prontos, você pode importar os dados da seguinte maneira -

• Clique From Other Sources no grupo Obter Dados Externos na janela Power Pivot.

• Proceda conforme indicado na seção - Assistente de importação de tabela.

A seguir estão as diferenças entre tabelas vinculadas do Excel e tabelas importadas do Excel -

• As tabelas vinculadas precisam estar na mesma pasta de trabalho do Excel em que o banco de dados do Power Pivot está armazenado. Se os dados já existem em outras pastas de trabalho do Excel, não há motivo para usar esse recurso.

• O recurso de importação do Excel permite que você carregue dados de diferentes pastas de trabalho do Excel.

• Carregar dados de uma pasta de trabalho do Excel não cria um vínculo entre os dois arquivos. O Power Pivot cria apenas uma cópia dos dados, durante a importação.

• Quando o arquivo original do Excel é atualizado, os dados no Power Pivot não são atualizados. Você precisa definir o modo de atualização para automático ou atualizar os dados manualmente, na guia Tabela Vinculada da janela do Power Pivot.

Carregando de arquivos de texto

Um dos estilos de representação de dados populares é com o formato conhecido como valores separados por vírgula (csv). Cada linha / registro de dados é representado por uma linha de texto, em que as colunas / campos são separados por vírgulas. Muitos bancos de dados oferecem a opção de salvar em um arquivo de formato csv.

Se você deseja carregar um arquivo csv no Power Pivot, você deve usar a opção Arquivo de texto. Suponha que você tenha o seguinte arquivo de texto com formato csv -

• Clique na guia PowerPivot.

• Clique na guia Página inicial na janela do PowerPivot.

• Clique From Other Sourcesno grupo Obter dados externos. O Assistente de importação de tabela é exibido.

• Role para baixo até Arquivos de texto.

• Clique em Arquivo de texto.

• Clique no Next→ botão. O Assistente de importação de tabela aparece com o visor - Conectar ao arquivo simples.

• Navegue até o arquivo de texto na caixa Caminho do arquivo. Os arquivos csv geralmente têm a primeira linha representando os cabeçalhos das colunas.

• Marque a caixa Usar a primeira linha como cabeçalhos de coluna, se a primeira linha tiver cabeçalhos.

• Na caixa Separador de Colunas, o padrão é Vírgula (,), mas caso seu arquivo de texto tenha qualquer outro operador como Tab, Ponto-e-vírgula, Espaço, Dois Pontos ou Barra Vertical, escolha esse operador.

Como você pode observar, há uma prévia da sua tabela de dados. Clique em Concluir.

O Power Pivot cria a tabela de dados no Modelo de Dados.

Carregando da área de transferência

Suponha que você tenha dados em um aplicativo que não é reconhecido pelo Power Pivot como uma fonte de dados. Para carregar esses dados no Power Pivot, você tem duas opções -

• Copie os dados para um arquivo do Excel e use o arquivo do Excel como fonte de dados para o Power Pivot.

• Copie os dados para que fiquem na área de transferência e cole-os no Power Pivot.

Você já aprendeu a primeira opção em uma seção anterior. E esta é preferível à segunda opção, como você encontrará no final desta seção. No entanto, você deve saber como copiar dados da área de transferência para o Power Pivot.

Suponha que você tenha dados em um documento do Word da seguinte forma -

O Word não é uma fonte de dados para o Power Pivot. Portanto, execute o seguinte -

• Selecione a tabela no documento do Word.

• Copie e cole na janela do PowerPivot.

o Paste Preview a caixa de diálogo aparece.

• Dê o nome como Word-Employee table.

• Verifica a caixa Use first row as column headers e clique em OK.

Os dados copiados para a área de transferência serão colados em uma nova tabela de dados no Power Pivot, com a guia - Tabela Word-Employee.

Suponha que você queira substituir esta tabela por um novo conteúdo.

• Copie a tabela do Word.

• Clique em Colar Substituir.

A caixa de diálogo Colar visualização é exibida. Verifique o conteúdo que você está usando para substituição.

Clique OK.

Como você pode observar, o conteúdo da tabela de dados no Power Pivot é substituído pelo conteúdo da área de transferência.

Suponha que você queira adicionar duas novas linhas de dados a uma tabela de dados. Na tabela do documento do Word, você tem as duas linhas de notícias.

• Selecione as duas novas linhas.

• Clique em Copiar.

• Clique Paste Appendna janela do Power Pivot. A caixa de diálogo Colar visualização é exibida.

• Verifique o conteúdo que você está usando para anexar.

Clique em OK para prosseguir.

Como você pode observar, o conteúdo da tabela de dados no Power Pivot é anexado ao conteúdo da área de transferência.

No início desta seção, dissemos que copiar dados para um arquivo Excel e usar uma tabela vinculada é melhor do que copiar da área de transferência.

Isso ocorre pelos seguintes motivos -

• Se você usar uma tabela vinculada, você sabe a origem dos dados. Por outro lado, você não saberá a origem dos dados posteriormente ou se eles são usados ​​por outra pessoa.

• Você tem informações de rastreamento no arquivo do Word, como quando os dados são substituídos e quando os dados são acrescentados. No entanto, não há como copiar essas informações para o Power Pivot. Se você copiar os dados primeiro para um arquivo do Excel, poderá preservar essas informações para uso posterior.

• Ao copiar da área de transferência, se você quiser adicionar alguns comentários, não poderá fazê-lo. Se você copiar para o arquivo do Excel primeiro, poderá inserir comentários na tabela do Excel que serão vinculados ao Power Pivot.

• Não há como atualizar os dados copiados da área de transferência. Se os dados forem de uma tabela vinculada, você sempre pode garantir que os dados sejam atualizados.

Atualizando dados no Power Pivot

Você pode atualizar os dados importados de fontes de dados externas a qualquer momento.

Se você deseja atualizar apenas uma tabela de dados no Power Pivot, faça o seguinte -

• Clique na guia da tabela de dados.

• Clique em Atualizar.

• Selecione Atualizar na lista suspensa.

Se você deseja atualizar todas as tabelas de dados no Power Pivot, faça o seguinte -

• Clique no botão Atualizar.

• Selecione Atualizar tudo na lista suspensa.

Um modelo de dados é uma nova abordagem introduzida no Excel 2013 para integrar dados de várias tabelas, criando de forma eficaz uma fonte de dados relacionais dentro de uma pasta de trabalho do Excel. No Excel, o Modelo de Dados é usado de forma transparente, fornecendo dados tabulares usados ​​em Tabelas Dinâmicas e Gráficos Dinâmicos. No Excel, você pode acessar as tabelas e seus valores correspondentes por meio das listas de campos da tabela dinâmica / gráfico dinâmico que contêm os nomes das tabelas e os campos correspondentes.

O principal uso do Modelo de Dados no Excel é seu uso pelo Power Pivot. O Modelo de Dados pode ser considerado o banco de dados Power Pivot e todos os recursos de energia do Power Pivot são gerenciados com o Modelo de Dados. Todas as operações de dados com Power Pivot são explícitas por natureza e podem ser visualizadas no Modelo de Dados.

Neste capítulo, você entenderá o Modelo de Dados em detalhes.

Excel e modelo de dados

Haverá apenas um Modelo de Dados em uma pasta de trabalho do Excel. Quando você trabalha com o Excel, o uso do Modelo de Dados está implícito. Você não pode acessar diretamente o Modelo de Dados. Você só pode ver as várias tabelas no Modelo de Dados na lista Campos da Tabela Dinâmica ou Gráfico Dinâmico e usá-las. A criação do modelo de dados e a adição de dados também são feitas implicitamente no Excel, enquanto você obtém dados externos no Excel.

Se você quiser olhar para o modelo de dados, você pode fazer o seguinte -

• Clique na guia POWERPIVOT na faixa de opções.

• Clique em Gerenciar.

O Modelo de Dados, se existir na pasta de trabalho, será exibido como tabelas, cada uma com uma guia.

Note- Se você adicionar uma tabela do Excel ao Modelo de Dados, não transformará a tabela do Excel em uma tabela de dados. Uma cópia da tabela do Excel é adicionada como uma tabela de dados no Modelo de Dados e um link é criado entre os dois. Portanto, se alterações forem feitas na tabela do Excel, a tabela de dados também será atualizada. No entanto, do ponto de vista do armazenamento, existem duas tabelas.

Power Pivot e modelo de dados

O Modelo de Dados é inerentemente o banco de dados do Power Pivot. Mesmo quando você cria o Modelo de Dados do Excel, ele constrói apenas o banco de dados Power Pivot. A criação do Modelo de Dados e / ou adição de dados é feita explicitamente no Power Pivot.

Na verdade, você pode gerenciar o Modelo de Dados na janela do Power Pivot. Você pode adicionar dados ao Modelo de Dados, importar dados de diferentes fontes de dados, visualizar o Modelo de Dados, criar relacionamentos entre as tabelas, criar campos calculados e colunas calculadas, etc.

Criação de um modelo de dados

Você pode adicionar tabelas ao Modelo de Dados do Excel ou importar dados diretamente para o Power Pivot, criando assim as tabelas do Modelo de Dados do Power Pivot. Você pode visualizar o Modelo de Dados clicando em Gerenciar na janela do Power Pivot.

Você entenderá como adicionar tabelas do Excel ao modelo de dados no capítulo - Carregando dados através do Excel. Você entenderá como carregar dados no modelo de dados no capítulo - Carregando dados no Power Pivot.

Tabelas no modelo de dados

As tabelas no modelo de dados podem ser definidas como um conjunto de tabelas que mantêm relacionamentos entre elas. Os relacionamentos permitem combinar dados relacionados de diferentes tabelas para fins de análise e relatório.

As tabelas no modelo de dados são chamadas de tabelas de dados.

Uma tabela no Modelo de Dados é considerada um conjunto de registros (um registro é uma linha) composto de campos (um campo é uma coluna). Você não pode editar itens individuais em uma tabela de dados. No entanto, você pode anexar linhas ou adicionar colunas calculadas à tabela de dados.

Tabelas Excel e tabelas de dados

As tabelas do Excel são apenas uma coleção de tabelas separadas. Pode haver várias tabelas em uma planilha. Cada tabela pode ser acessada separadamente, mas não é possível acessar dados de mais de uma tabela Excel ao mesmo tempo. Este é o motivo pelo qual, quando você cria uma Tabela Dinâmica, ela se baseia em apenas uma tabela. Se você precisar usar os dados de duas tabelas do Excel coletivamente, primeiro será necessário mesclá-los em uma única tabela do Excel.

Uma tabela de dados, por outro lado, coexiste com outras tabelas de dados com relacionamentos, facilitando a combinação de dados de várias tabelas. As tabelas de dados são criadas quando você importa dados para o Power Pivot. Você também pode adicionar tabelas do Excel ao modelo de dados enquanto cria uma tabela dinâmica obtendo dados externos ou de várias tabelas.

As tabelas de dados no modelo de dados podem ser visualizadas de duas maneiras -

• Exibição de dados.

• Visualização do diagrama.

Visualização de dados do modelo de dados

Na visualização de dados do Modelo de Dados, cada tabela de dados existe em uma guia separada. As linhas da tabela de dados são os registros e as colunas representam os campos. As guias contêm os nomes das tabelas e os cabeçalhos das colunas são os campos dessa tabela. Você pode fazer cálculos na visualização de dados usando a linguagem Data Analysis Expressions (DAX).

Visão do Diagrama do Modelo de Dados

Na visualização do diagrama do Modelo de Dados, todas as tabelas de dados são representadas por caixas com os nomes das tabelas e contêm os campos da tabela. Você pode organizar as tabelas na visualização do diagrama apenas arrastando-as. Você pode ajustar o tamanho de uma tabela de dados para que todos os campos da tabela sejam exibidos.

Relacionamentos no modelo de dados

Você pode visualizar os relacionamentos na visualização do diagrama. Se duas tabelas tiverem um relacionamento definido entre elas, aparecerá uma seta conectando a tabela de origem à tabela de destino. Se você deseja saber quais campos são usados ​​no relacionamento, basta clicar duas vezes na seta. A seta e os dois campos nas duas tabelas são destacados.

Relacionamentos de tabela serão criados automaticamente se você importar tabelas relacionadas que possuem relacionamentos de chave primária e estrangeira. O Excel pode usar as informações de relacionamento importadas como base para relacionamentos de tabela no Modelo de Dados.

Você também pode criar relacionamentos explicitamente em qualquer uma das duas visualizações -

• Data View - Usando a caixa de diálogo Criar Relacionamento.

• Diagram View - Clicando e arrastando para conectar as duas mesas.

Create Relationship Dialog Box

Em um relacionamento, quatro entidades estão envolvidas -

• Table - A tabela de dados a partir da qual o relacionamento começa.

• Column - O campo na Tabela que também está presente na tabela relacionada.

• Related Table - A tabela de dados onde o relacionamento termina.

• Related Column- O campo na tabela relacionada que é igual ao campo representado por Coluna na Tabela. Observe que os valores da coluna relacionada devem ser exclusivos.

Na visualização do diagrama, você pode criar o relacionamento clicando no campo na tabela e arrastando para a tabela relacionada.

Você aprenderá mais sobre relacionamentos no capítulo - Gerenciando tabelas de dados e relacionamentos com o Power Pivot.

O principal uso do Power Pivot é sua capacidade de gerenciar as tabelas de dados e as relações entre elas, para facilitar a análise dos dados de várias tabelas. Você pode adicionar uma tabela do Excel ao Modelo de Dados enquanto cria uma Tabela Dinâmica ou diretamente da Faixa de Opções do PowerPivot.

Você pode analisar dados de várias tabelas apenas quando houver relacionamentos entre elas. Com o Power Pivot, você pode criar relacionamentos a partir da Exibição de dados ou Exibição de diagrama. Além disso, se você escolheu adicionar uma tabela ao Power Pivot, também precisa adicionar um relacionamento.

Adicionando tabelas do Excel ao modelo de dados com a tabela dinâmica

Quando você cria uma Tabela Dinâmica no Excel, ela se baseia apenas em uma única tabela / intervalo. Caso queira adicionar mais tabelas à Tabela Dinâmica, pode fazê-lo com o Modelo de Dados.

Suponha que você tenha duas planilhas em sua pasta de trabalho -

• Um contendo os dados dos vendedores e as regiões que eles representam, em uma tabela - Vendedor.

• Outro contendo os dados de vendas, por região e mês, em uma tabela - Vendas.

Você pode resumir as vendas - em termos de vendedor, conforme mostrado abaixo.

• Clique na tabela - Vendas.

• Clique na guia INSERIR na faixa de opções.

• Selecione PivotTable no grupo Tables.

Será criada uma Tabela Dinâmica vazia com os campos da tabela Vendas - Região, Mês e Valor do Pedido. Como você pode observar, há umMORE TABLES comando abaixo da lista de campos da tabela dinâmica.

• Clique em MAIS TABELAS.

o Create a New PivotTablea caixa de mensagem aparece. A mensagem exibida é- Para usar várias tabelas em sua análise, uma nova Tabela Dinâmica precisa ser criada usando o Modelo de Dados. Clique Sim

Uma nova tabela dinâmica será criada conforme mostrado abaixo -

Em Campos da Tabela Dinâmica, você pode observar que existem duas guias - ACTIVE e ALL.

• Clique na guia TODOS.

• Duas tabelas - Vendas e Vendedor, com os campos correspondentes, aparecem na lista Campos da Tabela Dinâmica.

• Clique no campo Vendedor na tabela Vendedor e arraste-o para a área ROWS.

• Clique no campo Mês na tabela Vendas e arraste-o para a área ROWS.

• Clique no campo Valor do pedido na tabela Vendas e arraste-o para a área ∑ VALORES.

A Tabela Dinâmica é criada. Uma mensagem aparece nos Campos da Tabela Dinâmica -Relationships between tables may be needed.

Clique no botão CRIAR próximo à mensagem. oCreate Relationship a caixa de diálogo aparece.

• Debaixo Table, selecione Vendas.

• Debaixo Column (Foreign) caixa, selecione Região.

• Debaixo Related Table, selecione Vendedor.

• Debaixo Related Column (Primary) caixa, selecione Região.

• Clique OK.

Sua Tabela Dinâmica das duas tabelas em duas planilhas está pronta.

Além disso, como o Excel afirmou ao adicionar a segunda tabela à Tabela Dinâmica, a Tabela Dinâmica foi criada com o Modelo de Dados. Para verificar, faça o seguinte -

• Clique na guia POWERPIVOT na faixa de opções.

• Clique Manageno grupo Modelo de Dados. A exibição de dados do Power Pivot é exibida.

Você pode observar que as duas tabelas do Excel usadas na criação da Tabela Dinâmica são convertidas em tabelas de dados no Modelo de Dados.

Adicionando tabelas do Excel de uma pasta de trabalho diferente ao modelo de dados

Suponha que as duas tabelas - Vendedor e Vendas estejam em duas pastas de trabalho diferentes.

Você pode adicionar a tabela do Excel de uma pasta de trabalho diferente ao modelo de dados da seguinte maneira -

• Clique na tabela Vendas.

• Clique na guia INSERIR.

• Clique em Tabela Dinâmica no grupo Tabelas. oCreate PivotTable a caixa de diálogo aparece.

• Na caixa Tabela / Intervalo, digite Vendas.

• Clique em Nova planilha.

• Marque a caixa Adicionar esses dados ao modelo de dados.

• Clique OK.

Você obterá uma Tabela Dinâmica vazia em uma nova planilha com apenas os campos correspondentes à tabela Vendas.

Você adicionou os dados da tabela Vendas ao Modelo de Dados. Em seguida, você deve obter os dados da tabela de vendedor também no modelo de dados da seguinte forma -

• Clique na planilha que contém a tabela Vendas.

• Clique na guia DADOS na faixa de opções.

• Clique em Conexões existentes no grupo Obter dados externos. A caixa de diálogo Conexões existentes é exibida.

• Clique na guia Tabelas.

Debaixo This Workbook Data Model, 1 tableé exibida (esta é a tabela Vendas que você adicionou anteriormente). Você também encontrará as duas pastas de trabalho exibindo as tabelas nelas.

• Clique em Vendedor em Vendedor.xlsx.

• Clique em Abrir. oImport Data a caixa de diálogo aparece.

• Clique em Relatório de tabela dinâmica.

• Clique em Nova planilha.

Você pode ver que a caixa - Add this data to the Data Modelestá marcada e inativa. Clique OK.

A tabela dinâmica será criada.

Como você pode observar, as duas tabelas estão no Modelo de Dados. Você pode ter que criar um relacionamento entre as duas tabelas, como na seção anterior.

Adicionando tabelas do Excel ao modelo de dados da faixa de opções do PowerPivot

Outra maneira de adicionar tabelas do Excel ao Modelo de Dados é fazendo so from the PowerPivot Ribbon.

Suponha que você tenha duas planilhas em sua pasta de trabalho -

• Um contendo os dados dos vendedores e as regiões que eles representam, em uma tabela - Vendedor.

• Outro contendo os dados de vendas, por região e mês, em uma tabela - Vendas.

Você pode adicionar essas tabelas do Excel ao modelo de dados primeiro, antes de fazer qualquer análise.

• Clique na tabela do Excel - Vendas.

• Clique na guia POWERPIVOT na faixa de opções.

• Clique em Adicionar ao modelo de dados no grupo Tabelas.

A janela do Power Pivot é exibida, com a tabela de dados Vendedor adicionada a ela. Além disso, uma guia - Tabela vinculada aparece na faixa de opções na janela do Power Pivot.

• Clique na guia Tabela vinculada na faixa de opções.

• Clique na Tabela do Excel: Vendedor.

Você pode descobrir que os nomes das duas tabelas presentes em sua pasta de trabalho são exibidos e o nome Vendedor está marcado. Isso significa que a tabela de dados Vendedor está vinculada à tabela do Excel Vendedor.

Clique Go to Excel Table.

A janela do Excel com a planilha contendo a tabela do vendedor é exibida.

• Clique na guia Planilha de vendas.

• Clique na tabela Vendas.

• Clique em Adicionar ao modelo de dados no grupo Tabelas na faixa de opções.

A tabela Vendas do Excel também é adicionada ao Modelo de Dados.

Se você quiser fazer análises com base nessas duas tabelas, como você sabe, precisa criar um relacionamento entre as duas tabelas de dados. No Power Pivot, você pode fazer isso de duas maneiras -

• Da Visualização de Dados

• Da visualização do diagrama

Criação de relacionamentos a partir da visão de dados

Como você sabe que na Visualização de Dados, você pode visualizar as tabelas de dados com registros como linhas e campos como colunas.

• Clique na guia Design na janela do Power Pivot.

• Clique em Criar Relacionamento no grupo Relacionamentos. oCreate Relationship a caixa de diálogo aparece.

• Clique em Vendas na caixa Tabela. Esta é a mesa onde o relacionamento começa. Como você sabe, Coluna deve ser o campo que está presente na tabela relacionada Vendedor que contém valores exclusivos.

• Clique em Região na caixa Coluna.

• Clique em Vendedor na caixa Tabela Relacionada.

A Coluna Vinculada Relacionada é preenchida automaticamente com Região.

Clique no botão Criar. O relacionamento é criado.

Criação de relacionamentos a partir da visualização do diagrama

Criar relacionamentos a partir da visualização do diagrama é relativamente mais fácil. Siga as etapas fornecidas.

• Clique na guia Página inicial na janela do Power Pivot.

• Clique em Exibir Diagrama no grupo Exibir.

A Visualização do Diagrama do Modelo de Dados aparece na janela do Power Pivot.

• Clique em Região na tabela Vendas. Região na tabela de vendas é destacado.

• Arraste para a região na tabela de vendedor. Região na tabela de vendedor também é destacada. Uma linha aparece na direção em que você arrastou.

• Uma linha aparece da tabela Vendas para a tabela Vendedor indicando o relacionamento.

Como você pode ver, uma linha aparece da tabela Vendas para a tabela Vendedor, indicando o relacionamento e a direção.

Se você deseja saber o campo que faz parte de um relacionamento, clique na linha de relacionamento. A linha e o campo em ambas as tabelas são destacados.

Gerenciando relacionamentos

Você pode editar ou excluir um relacionamento existente no Modelo de Dados.

• Clique na guia Design na janela Power Pivot.

• Clique em Gerenciar Relacionamentos no grupo Relacionamentos. A caixa de diálogo Gerenciar Relacionamentos é exibida.

Todos os relacionamentos existentes no Modelo de Dados são exibidos.

Para editar um relacionamento

• Clique em um relacionamento.

• Clique no Editbotão. oEdit Relationship a caixa de diálogo aparece.

• Faça as mudanças necessárias no relacionamento.

• Clique OK. As mudanças se refletem no relacionamento.

Para excluir um relacionamento

• Clique em um relacionamento.

• Clique no botão Excluir. Uma mensagem de aviso é exibida mostrando como as tabelas afetadas pela exclusão do relacionamento afetariam os relatórios.

• Clique em OK se tiver certeza de que deseja excluir. O relacionamento selecionado é excluído.

Atualização de dados do Power Pivot

Suponha que você modifique os dados na tabela do Excel. Você pode adicionar / alterar / excluir os dados na tabela do Excel.

Para atualizar os dados PowerPivot, faça o seguinte -

• Clique na guia Tabela vinculada na janela do Power Pivot.

• Clique em Atualizar tudo.

A tabela de dados é atualizada com as modificações feitas na tabela do Excel.

Como você pode observar, você não pode modificar os dados nas tabelas de dados diretamente. Portanto, é melhor manter seus dados em tabelas do Excel vinculadas às tabelas de dados ao adicioná-los ao Modelo de Dados. Isso facilita a atualização dos dados nas tabelas de dados à medida que você atualiza os dados nas tabelas do Excel.

O Power PivotTable é baseado no banco de dados Power Pivot, que é chamado de Modelo de Dados. Você já aprendeu os recursos poderosos do Modelo de Dados. O poder do Power Pivot está em sua capacidade de resumir dados do Modelo de Dados na Power PivotTable. Como você sabe, o Modelo de Dados pode lidar com dados enormes, abrangendo milhões de linhas e provenientes de diversas entradas. Isso permite que o Power PivotTable resuma os dados de qualquer lugar em questão de minutos.

O Power PivotTable é semelhante ao PivotTable em seu layout, com as seguintes diferenças -

• A Tabela Dinâmica é baseada em tabelas do Excel, enquanto a Tabela Dinâmica do Power é baseada em tabelas de dados que fazem parte do Modelo de Dados.

• A Tabela Dinâmica é baseada em uma única tabela do Excel ou intervalo de dados, enquanto a Tabela Dinâmica do Power pode ser baseada em várias tabelas de dados, desde que sejam adicionadas ao Modelo de Dados.

• A Tabela Dinâmica é criada na janela do Excel, enquanto o Power PivotTable é criado na janela do PowerPivot.

Criação de uma tabela dinâmica avançada

Suponha que você tenha duas tabelas de dados - Vendedor e Vendas no Modelo de Dados. Para criar uma Tabela PowerPivot a partir dessas duas tabelas de dados, proceda da seguinte forma -

• Clique na guia Início na Faixa de Opções na janela do PowerPivot.

• Clique em Tabela Dinâmica na Faixa de Opções.

• Selecione PivotTable na lista suspensa.

A caixa de diálogo Criar Tabela Dinâmica é exibida. Como você pode observar, esta é uma caixa de diálogo simples, sem nenhuma consulta aos dados. Isso porque o Power PivotTable é sempre baseado no Modelo de Dados, ou seja, as tabelas de dados com as relações definidas entre elas.

Selecione Nova planilha e clique em OK.

Uma nova planilha é criada na janela do Excel e uma Tabela Dinâmica vazia é exibida.

Como você pode observar, o layout da Tabela Dinâmica do Power é semelhante ao da Tabela Dinâmica. oPIVOTTABLE TOOLS aparecem na fita, com ANALYZE e DESIGN guias, idênticas à Tabela Dinâmica.

A Lista de Campos da Tabela Dinâmica aparece no lado direito da planilha. Aqui, você encontrará algumas diferenças da Tabela Dinâmica.

Campos da tabela dinâmica de poder

A lista Campos da Tabela Dinâmica possui duas guias - ATIVAS e TODAS que aparecem abaixo do título e acima da lista de campos. oALL guia é realçada.

Observe que o ALLA guia exibe todas as tabelas de dados no Modelo de Dados e a guia ATIVO exibe todas as tabelas de dados que são escolhidas para a Tabela Dinâmica do Power em questão. Como o Power PivotTable está vazio, isso significa que nenhuma tabela de dados foi selecionada ainda; portanto, por padrão, a guia ALL é selecionada e as duas tabelas que estão atualmente no Modelo de Dados são exibidas. Neste ponto, se você clicar noACTIVE guia, a lista de Campos ficaria vazia.

• Clique nos nomes das tabelas na lista Campos da Tabela Dinâmica em TODOS. Os campos correspondentes com caixas de seleção serão exibidos.

• Cada nome de mesa terá o símbolo

  
  no lado esquerdo.

• Se você colocar o cursor sobre este símbolo, a fonte de dados e o nome da tabela modelo dessa tabela de dados serão exibidos.

• Arraste Vendedor da tabela Vendedor para a área ROWS.

• Clique no ACTIVE aba.

Como você pode observar, o campo Vendedor aparece na Tabela Dinâmica e a tabela Vendedor aparece sob o ACTIVE guia conforme o esperado.

• Clique no ALL aba.

• Clique em Mês e Valor do Pedido na tabela Vendas.

Novamente, clique na guia ATIVO. Ambas as tabelas - Vendas e Vendedor aparecem sob oACTIVE aba.

• Arraste o mês para a área COLUNAS.

• Arraste Região para a área FILTROS.

• Clique na seta ao lado de TODOS na caixa de filtro Região.

• Clique em Selecionar vários itens.

• Selecione Norte e Sul e clique em OK.

Classifique os rótulos das colunas em ordem crescente.

O Power PivotTable pode ser modificado para explorar e relatar dados dinamicamente.

DAX (Data Analysis eXpression)linguagem é a linguagem do Power Pivot. DAX é usado pelo Power Pivot para modelagem de dados e é conveniente para você usar para BI de autoatendimento. DAX é baseado em tabelas de dados e colunas em tabelas de dados. Observe que não se baseia em células individuais na tabela, como é o caso das fórmulas e funções do Excel.

Você aprenderá os dois cálculos simples que existem no Modelo de Dados - Coluna Calculada e Campo Calculado neste capítulo.

Coluna Calculada

Coluna calculada é uma coluna no Modelo de Dados que é definida por um cálculo e que estende o conteúdo de uma tabela de dados. Pode ser visualizado como uma nova coluna em uma tabela Excel definida por uma fórmula.

Estendendo o modelo de dados usando colunas calculadas

Suponha que você tenha dados de vendas de produtos por região em tabelas de dados e também um Catálogo de Produtos no Modelo de Dados.

Crie um Power PivotTable com esses dados.

Como você pode observar, o Power PivotTable resumiu os dados de vendas de todas as regiões. Suponha que você queira saber o lucro bruto obtido em cada um dos produtos. Você sabe o preço de cada produto, o custo de venda e o número de unidades vendidas.

No entanto, se você precisar calcular o lucro bruto, precisará ter mais duas colunas em cada uma das tabelas de dados das regiões - Preço total do produto e Lucro bruto. Isso ocorre porque a Tabela Dinâmica requer colunas nas tabelas de dados para resumir os resultados.

Como você sabe, o Preço Total do Produto é o Preço do Produto * Nº de Unidades e o Lucro Bruto é o Valor Total - Preço Total do Produto.

Você precisa usar as expressões DAX para adicionar as colunas calculadas da seguinte forma -

• Clique na guia East_Sales na Exibição de Dados da janela Power Pivot para ver a Tabela de Dados East_Sales.

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar.

A coluna do lado direito com o cabeçalho - Adicionar coluna é destacada.

Tipo = [Product Price] * [No. of Units] na barra de fórmulas e pressione Enter.

Uma nova coluna com cabeçalho CalculatedColumn1 é inserido com os valores calculados pela fórmula que você inseriu.

• Clique duas vezes no cabeçalho da nova coluna calculada.

• Renomeie o cabeçalho como TotalProductPrice.

Adicione mais uma coluna calculada para o lucro bruto da seguinte forma -

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar.

• A coluna do lado direito com o cabeçalho - Adicionar coluna é destacada.

• Tipo = [TotalSalesAmount] − [TotaProductPrice] na barra de fórmulas.

• Pressione Enter.

Uma nova coluna com cabeçalho CalculatedColumn1 é inserido com os valores calculados pela fórmula que você inseriu.

• Clique duas vezes no cabeçalho da nova coluna calculada.

• Renomeie o cabeçalho como Lucro bruto.

Adicione as colunas calculadas no North_Salestabela de dados de forma semelhante. Consolidando todas as etapas, proceda da seguinte forma -

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar. A coluna do lado direito com o cabeçalho - Adicionar coluna é destacada.

• Tipo = [Product Price] * [No. of Units] na barra de fórmulas e pressione Enter.

• Uma nova coluna com cabeçalho CalculatedColumn1 é inserida com os valores calculados pela fórmula que você inseriu.

• Clique duas vezes no cabeçalho da nova coluna calculada.

• Renomeie o cabeçalho como TotalProductPrice.

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar. A coluna do lado direito com o cabeçalho - Adicionar coluna é destacada.

• Tipo = [TotalSalesAmount] − [TotaProductPrice]na barra de fórmulas e pressione Enter. Uma nova coluna com cabeçalhoCalculatedColumn1 é inserido com os valores calculados pela fórmula que você inseriu.

• Clique duas vezes no cabeçalho da nova coluna calculada.

• Renomeie o cabeçalho como Gross Profit.

Repita as etapas fornecidas acima para a tabela de dados South Sales e a tabela de dados West Sales.

Você tem as colunas necessárias para resumir o lucro bruto. Agora, crie a Tabela Dinâmica do Power.

Você é capaz de resumir o Gross Profit isso se tornou possível com as colunas calculadas no Power Pivot e tudo pode ser feito em apenas algumas etapas sem erros.

Você pode resumir regionalmente para os produtos conforme fornecido abaixo também -

Campo Calculado

Suponha que você queira calcular a porcentagem do lucro obtido por cada região em termos de produto. Você pode fazer isso adicionando um campo calculado à Tabela de dados.

• Clique abaixo da coluna Lucro bruto no East_Sales tabela na janela do Power Pivot.

• Tipo EastProfit: = SUM ([Gross Profit]) / sum ([TotalSalesAmount]) na barra de fórmulas.

• Pressione Enter.

O campo calculado EastProfit é inserido abaixo da coluna Lucro bruto.

• Clique com o botão direito no campo calculado - EastProfit.

• Selecione Format na lista suspensa.

A caixa de diálogo Formatação é exibida.

• Selecione Number na categoria.

• Na caixa Formato, selecione Porcentagem e clique em OK.

O campo calculado EastProfit é formatado em porcentagem.

Repita as etapas para inserir os seguintes campos calculados -

• NorthProfit na tabela de dados North_Sales.

• SouthProfit na tabela de dados South_Sales.

• WestProfit na tabela de dados West_Sales.

Note - Você não pode definir mais de um campo calculado com um determinado nome.

Clique em Power PivotTable. Você pode ver que os campos calculados aparecem nas tabelas.

• Selecione os campos - EastProfit, NorthProfit, SouthProfit e WestProfit das tabelas na lista Campos da Tabela Dinâmica.

• Organize os campos de forma que o lucro bruto e o lucro percentual apareçam juntos. A Power PivotTable tem a seguinte aparência -

Note - o Calculate Fields foram chamados Measures em versões anteriores do Excel.

No capítulo anterior, você aprendeu como criar uma Power PivotTable a partir de um conjunto normal de tabelas de dados. Neste capítulo, você aprenderá como explorar dados com o Power PivotTable, quando as tabelas de dados contêm milhares de linhas.

Para um melhor entendimento, importaremos os dados de um banco de dados de acesso, que você sabe que é um banco de dados relacional.

Carregando dados do banco de dados Access

Para carregar dados do banco de dados Access, siga as etapas fornecidas -

• Abra uma nova pasta de trabalho em branco no Excel.

• Clique em Gerenciar no grupo Modelo de Dados.

• Clique na guia POWERPIVOT na faixa de opções.

A janela do Power Pivot é exibida.

• Clique na guia Página inicial na janela do Power Pivot.

• Clique From Database no grupo Obter dados externos.

• Selecione From Access na lista suspensa.

O Assistente de importação de tabela é exibido.

• Providenciar Friendly connection nome.

• Navegue até o arquivo de banco de dados do Access, Events.accdb, o arquivo de banco de dados de eventos.

• Clique no botão Avançar>.

o Table Import assistente exibe opções para escolher como importar dados.

Clique Select from a list of tables and views to choose the data to import e clique Next.

o Table ImportO assistente exibe todas as tabelas do banco de dados Access que você selecionou. Marque todas as caixas para selecionar todas as tabelas e clique em Concluir.

o Table Import Visores do assistente - Importinge mostra o status da importação. Isso pode levar alguns minutos e você pode interromper a importação clicando noStop Import botão.

Assim que a importação de dados for concluída, o Assistente de importação de tabela exibirá - Successe mostra os resultados da importação. CliqueClose.

O Power Pivot exibe todas as tabelas importadas em guias diferentes na Exibição de Dados.

Clique em Exibir Diagrama.

Você pode observar que existe uma relação entre as tabelas - Disciplines and Medals. Isso ocorre porque, quando você importa dados de um banco de dados relacional, como o Access, os relacionamentos existentes no banco de dados também são importados para o Modelo de Dados no Power Pivot.

Criação de uma tabela dinâmica a partir do modelo de dados

Crie uma tabela dinâmica com as tabelas que você importou na seção anterior da seguinte maneira -

• Clique em Tabela Dinâmica na Faixa de Opções.

• Selecione PivotTable na lista suspensa.

• Selecione Nova Planilha na caixa de diálogo Criar Tabela Dinâmica que aparece e clique em OK.

Uma tabela dinâmica vazia é criada em uma nova planilha na janela do Excel.

Todas as tabelas importadas que fazem parte do Modelo de Dados do Power Pivot aparecem na lista Campos da Tabela Dinâmica.

• Arrastar o NOC_CountryRegion na tabela Medalhas para a área COLUNAS.

• Arraste Disciplina da tabela Disciplinas para a área LINHAS.

• Filtre a Disciplina para exibir apenas cinco esportes: Tiro com Arco, Mergulho, Esgrima, Patinação Artística e Patinação de Velocidade. Isso pode ser feito na área Campos da Tabela Dinâmica ou no filtro Rótulos de Linha na própria Tabela Dinâmica.

• Arraste a medalha da tabela Medalhas para a área VALORES.

• Selecione Medalha na tabela Medalhas novamente e arraste-a para a área FILTROS.

A tabela dinâmica é preenchida com os campos adicionados e no layout escolhido nas áreas.

Explorando Dados com Tabela Dinâmica

Você pode querer exibir apenas os valores com Contagem de medalhas> 80. Para fazer isso, siga as etapas fornecidas -

• Clique na seta à direita de Rótulos da coluna.

• Selecione Value Filters na lista suspensa.

• Selecione Greater Than…. na segunda lista suspensa.

• Clique OK.

o Value Filtera caixa de diálogo aparece. Digite 80 na caixa mais à direita e clique em OK.

A tabela dinâmica exibe apenas as regiões com número total de medalhas superior a 80.

Você pode chegar ao relatório específico que deseja das diferentes tabelas em apenas algumas etapas. Isso se tornou possível devido aos relacionamentos pré-existentes entre as tabelas no banco de dados do Access. Conforme você importava todas as tabelas do banco de dados juntas ao mesmo tempo, o Power Pivot recriou os relacionamentos em seu Modelo de Dados.

Resumindo dados de diferentes fontes no Power Pivot

Se você obtiver as tabelas de dados de fontes diferentes ou se não importar as tabelas de um banco de dados ao mesmo tempo, ou se criar novas tabelas do Excel em sua pasta de trabalho e adicioná-las ao Modelo de Dados, você deve criar as relações entre as tabelas que você deseja usar para sua análise e resumo na Tabela Dinâmica.

• Crie uma nova planilha na pasta de trabalho.

• Crie uma tabela Excel - Esportes.

Adicione a tabela Sports ao modelo de dados.

Crie uma relação entre as tabelas Disciplines and Sports com o campo SportID.

Adicione o campo Sport para a Tabela Dinâmica.

Misture os campos - Discipline and Sport na área ROWS.

Extensão da exploração de dados

Você pode pegar a mesa Events também em mais exploração de dados.

Crie uma relação entre as tabelas- Events e Medals com o campo DisciplineEvent.

Adicione uma mesa Hosts para a pasta de trabalho e o modelo de dados.

Estendendo o modelo de dados usando colunas calculadas

Para conectar a tabela Hosts a qualquer uma das outras tabelas, ela deve ter um campo com valores que identificam exclusivamente cada linha da tabela Hosts. Como esse campo não existe na tabela Host, você pode criar uma coluna calculada na tabela Hosts para que ela contenha valores exclusivos.

• Vá para a tabela Hosts na Exibição de Dados da janela PowerPivot.

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar.

A coluna mais à direita com o cabeçalho Adicionar coluna é destacada.

• Digite a seguinte fórmula DAX na barra de fórmulas = CONCATENATE ([Edition], [Season])

• Pressione Enter.

Uma nova coluna é criada com o cabeçalho CalculatedColumn1 e a coluna é preenchida pelos valores resultantes da fórmula DAX acima.

Clique com o botão direito do mouse na nova coluna e selecione Renomear coluna na lista suspensa.

Tipo EditionID no cabeçalho da nova coluna.

Como você pode ver, a coluna EditionID tem valores exclusivos na tabela Hosts.

Criando um relacionamento usando colunas calculadas

Se você tiver que criar um relacionamento entre o Hosts mesa e o Medals tabela, a coluna EditionIDtambém deve existir na tabela de medalhas. Crie uma coluna calculada na tabela de medalhas da seguinte maneira -

• Clique na tabela Medalhas na Visualização de Dados do Power Pivot.

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar.

Digite a fórmula DAX na barra de fórmulas = YEAR ([EDITION]) e pressione Enter.

Renomeie a nova coluna criada como Ano e clique em Add.

• Digite a seguinte fórmula DAX na barra de fórmulas = CONCATENATE ([Year], [Season])

• Renomeie a nova coluna criada como EditionID.

Como você pode observar, a coluna EditionID na tabela Medalhas tem valores idênticos aos da coluna EditionID na tabela Hosts. Portanto, você pode criar uma relação entre as tabelas - Medalhas e Esportes com o campo ID da edição.

• Mude para a exibição do diagrama na janela do PowerPivot.

• Crie uma relação entre as tabelas - Medalhas e Hosts com o campo que é obtido a partir da coluna calculada, ou seja EditionID.

Agora você pode adicionar campos da tabela Hosts à Power PivotTable.

Quando os dados têm muitos níveis, às vezes torna-se complicado ler o relatório de tabela dinâmica.

Por exemplo, considere o seguinte Modelo de Dados.

Criaremos uma Tabela Dinâmica Power e uma Tabela Dinâmica Power Flattened para obter uma compreensão dos layouts.

Criação de uma tabela dinâmica

Você pode criar uma Power PivotTable da seguinte maneira -

• Clique na guia Início na Faixa de Opções na janela do PowerPivot.

• Clique em Tabela Dinâmica.

• Selecione PivotTable na lista suspensa.

Uma tabela dinâmica vazia será criada.

• Arraste os campos - Vendedor, Região e Produto da lista Campos da Tabela Dinâmica para a área ROWS.

• Arraste o campo - TotalSalesAmount das Tabelas - Nascente, Norte, Sul e Poente à zona ∑ VALORES.

Como você pode ver, é um pouco complicado ler esse relatório. Se o número de entradas aumentar, mais difícil será.

O Power Pivot fornece uma solução para uma melhor representação dos dados com a Tabela Dinâmica Achatada.

Criação de uma tabela dinâmica achatada

Você pode criar uma Tabela Dinâmica Power Flattened da seguinte forma -

• Clique na guia Início na Faixa de Opções na janela do PowerPivot.

• Clique em Tabela Dinâmica.

• Selecione Flattened PivotTable na lista suspensa.

Create Flattened PivotTablea caixa de diálogo aparece. Selecione Nova planilha e clique em OK.

Como você pode observar, os dados são simplificados nesta Tabela Dinâmica.

Note- Neste caso, Vendedor, Região e Produto estão na área ROWS apenas como no caso anterior. No entanto, no layout de tabela dinâmica, esses três campos aparecem como três colunas.

Explorando Dados em Tabela Dinâmica Achatada

Suponha que você queira resumir os dados de vendas do produto - Ar Condicionado. Você pode fazer isso de maneira simples com a Tabela Dinâmica Achatada da seguinte maneira -

• Clique na seta ao lado do cabeçalho da coluna - Produto.

• Marque a caixa Ar condicionado e desmarque as outras caixas. Clique OK.

A Tabela Dinâmica Achatada é filtrada para os dados de vendas do Ar Condicionado.

Você pode fazer com que pareça mais achatado arrastando ∑ VALUES para a área ROWS da área COLUMNS.

Renomeie os nomes personalizados dos valores de soma no ∑ VALUES área para torná-los mais significativos da seguinte forma -

• Clique em um valor de soma, digamos, Soma de TotalSalesAmount para Leste.

• Selecione Configurações do campo de valor na lista suspensa.

• Altere o nome personalizado para East TotalSalesAmount.

• Repita as etapas para os outros três valores de soma.

Você também pode resumir o número de unidades vendidas.

• Arraste o nº de unidades para a área ∑ VALUES de cada uma das tabelas - East_Sales, North_Sales, South_Sales e West_Sales.

• Renomeie os valores para Nº total leste de unidades, Nº total norte de unidades, Nº total sul de unidades e Nº total oeste de unidades, respectivamente.

Como você pode observar, em ambas as tabelas acima, existem linhas com valores vazios, pois cada vendedor representa uma única região e cada região é representada apenas por um único vendedor.

• Selecione as linhas com valores vazios.

• Clique com o botão direito e clique em Ocultar na lista suspensa.

Todas as linhas com valores vazios serão ocultadas.

Como você pode observar, embora as linhas com valores vazios não sejam exibidas, as informações do Vendedor também foram ocultadas.

• Clique no cabeçalho da coluna - Vendedor.

• Clique na guia ANALISAR na Faixa de Opções.

• Clique em Configurações do campo. A caixa de diálogo Configurações do campo é exibida.

• Clique na guia Layout e impressão.

• Verifica a caixa - Repeat Item Labels.

• Clique OK.

Como você pode observar, as informações do vendedor são exibidas e as linhas com valores vazios são ocultadas. Além disso, a coluna Região no relatório é redundante, pois os valores na coluna Valores são autoexplicativos.

Arraste o campo Regiões fora da área.

Inverta a ordem dos campos - Vendedor e Produto na área ROWS.

Você chegou a um relatório conciso combinando dados de seis tabelas no Power Pivot.

Um gráfico dinâmico baseado no modelo de dados e criado a partir da janela do Power Pivot é um gráfico dinâmico do Power. Embora tenha alguns recursos semelhantes ao gráfico dinâmico do Excel, existem outros recursos que o tornam mais poderoso.

Neste capítulo, você aprenderá sobre Power PivotCharts. Doravante nos referimos a eles como gráficos dinâmicos, para simplificar.

Criação de um gráfico dinâmico

Suponha que você queira criar um gráfico dinâmico com base no seguinte modelo de dados.

• Clique na guia Página inicial na faixa de opções na janela do Power Pivot.

• Clique em Tabela Dinâmica.

• Selecione PivotChart na lista suspensa.

o Create PivotCharta caixa de diálogo aparece. Selecione Nova planilha e clique em OK.

Um gráfico dinâmico vazio é criado em uma nova planilha na janela do Excel.

Como você pode observar, todas as tabelas no modelo de dados são exibidas na lista Campos do gráfico dinâmico.

• Clique na tabela Vendedor na lista Campos do gráfico dinâmico.

• Arraste os campos - Vendedor e Região para a área AXIS.

Dois botões de campo para os dois campos selecionados aparecem no gráfico dinâmico. Estes são os botões de campo do eixo. O uso de botões de campo é para filtrar os dados exibidos no gráfico dinâmico.

Arrastar TotalSalesAmount de cada uma das quatro tabelas - East_Sales, North_Sales, South_Sales e West_Sales para a área de ∑ VALUES.

O seguinte aparece na planilha -

• No gráfico dinâmico, o gráfico de colunas é exibido por padrão.

• Na área LEGEND, ∑ VALORES são adicionados.

• Os valores aparecem na legenda no gráfico dinâmico, com o título Valores.

• Os botões do campo de valor aparecem no gráfico dinâmico. Você pode remover a legenda e os botões do campo de valor para obter uma aparência mais organizada do gráfico dinâmico.

• Clique no

  
  botão no canto superior direito do PivotChart. oChart Elements lista suspensa aparece.

Desmarque a caixa Legenda na lista Elementos do gráfico. A legenda é removida do gráfico dinâmico.

• Clique com o botão direito nos botões do campo de valor.

• Selecione Ocultar botões de campo de valor no gráfico na lista suspensa.

Os botões do campo de valor no gráfico são removidos.

Note- A exibição dos botões de campo e / ou legenda depende do contexto do gráfico dinâmico. Você precisa decidir o que deve ser exibido.

Lista de campos do gráfico dinâmico

Como no caso do Power PivotTable, a lista Power PivotChart Fields também contém duas guias - ACTIVE e ALL. Na guia TODOS, todas as tabelas de dados no Modelo de Dados do Power Pivot são exibidas. Na guia ATIVO, as tabelas a partir das quais os campos são adicionados ao gráfico dinâmico são exibidas.

Da mesma forma, as áreas são como no caso do Excel PivotChart. Existem quatro áreas são -

• AXIS (Categories)

• LEGEND (Series)

• ∑ VALUES

• FILTERS

Como você viu na seção anterior, a Legenda é preenchida com ∑ Valores. Além disso, os botões de campo são adicionados ao gráfico dinâmico para facilitar a filtragem dos dados que estão sendo exibidos.

Filtros no gráfico dinâmico

Você pode usar os botões de campo do eixo no gráfico para filtrar os dados exibidos. Clique na seta no botão do campo Eixo - Região.

A lista suspensa que aparece é a seguinte -

Você pode selecionar os valores que deseja exibir. Alternativamente, você pode colocar o campo na área FILTROS para filtrar os valores.

Arraste o campo Região para a área FILTROS. O botão Filtro de relatório - Região aparece no gráfico dinâmico.

Clique na seta no botão Filtro de relatório - Região. A lista suspensa que aparece é a seguinte -

Você pode selecionar os valores que deseja exibir.

Segmentadores de dados no gráfico dinâmico

Usar Slicers é outra opção para filtrar dados no Power PivotChart.

• Clique na guia ANALISAR em ferramentas PIVOTCHART na faixa de opções.

• Clique em Inserir Slicer no grupo Filtro. oInsert Slicer a caixa de diálogo aparece.

Todas as tabelas e os campos correspondentes aparecem na caixa de diálogo Inserir Segmentação de Dados.

Clique no campo Região na tabela Vendedor na caixa de diálogo Inserir Segmentação de Dados.

O Slicer para o campo Região aparece na planilha.

Como você pode observar, o campo Região ainda existe como um campo de Eixo. Você pode selecionar os valores que deseja exibir clicando nos botões do Slicer.

Lembre-se de que você pode fazer tudo isso em poucos minutos e também de forma dinâmica por causa do Power Pivot Data Model e dos relacionamentos definidos.

Ferramentas de gráfico dinâmico

No Power PivotChart, o PIVOTCHART TOOLS tem três guias na Faixa de Opções, em comparação com duas guias no Excel PivotChart -

• ANALYZE

• DESIGN

• FORMAT

A terceira guia - FORMAT é a guia adicional no Power PivotChart.

Clique na guia FORMATO na faixa de opções.

As opções na Faixa de Opções na guia FORMATO são todas para adicionar esplendor ao seu Gráfico Dinâmico. Você pode usar essas opções criteriosamente, sem ficar entediado.

O Power Pivot oferece diferentes combinações de Power PivotTable e Power PivotChart para exploração, visualização e relatórios de dados. Você aprendeu Tabelas Dinâmicas e Gráficos Dinâmicos nos capítulos anteriores.

Neste capítulo, você aprenderá a criar as combinações de Tabela e Gráfico a partir da janela do Power Pivot.

Considere o seguinte modelo de dados no Power Pivot que usaremos para ilustrações -

Gráfico e Tabela (Horizontal)

Com esta opção, você pode criar um Power PivotChart e uma Power PivotTable, um ao lado do outro horizontalmente na mesma planilha.

• Clique na guia Página inicial na janela do Power Pivot.

• Clique em Tabela Dinâmica.

• Selecione Gráfico e Tabela (Horizontal) na lista suspensa.

A caixa de diálogo Criar gráfico dinâmico e tabela dinâmica (horizontal) é exibida. Selecione Nova planilha e clique em OK.

Um gráfico dinâmico vazio e uma tabela dinâmica vazia aparecem em uma nova planilha.

• Clique em PivotChart.

• Arrastar NOC_CountryRegion da tabela de medalhas para a área AXIS.

• Arraste a medalha da tabela Medalhas para a área ∑ VALORES.

• Clique com o botão direito no gráfico e selecione Change Chart Type na lista suspensa.

• Selecione Gráfico de área.

• Altere o título do gráfico para Total No. of Medals − Country Wise.

Como você pode ver, os EUA têm o maior número de medalhas (> 4.500).

• Clique na Tabela Dinâmica.

• Arraste Sport da tabela Sports para a área ROWS.

• Arraste a medalha da tabela Medalhas para a área ∑ VALORES.

• Arrastar NOC_CountryRegion da tabela de medalhas para a área de FILTROS.

• Filtre o NOC_CountryRegion campo para o valor USA.

Mudar o PivotTable Report Layout para Outline Formato.

• Desmarque Esporte na tabela Esportes.

• Arraste Gênero da tabela Medalhas para a área LINHAS.

Gráfico e Tabela (Vertical)

Com esta opção, você pode criar um Power PivotChart e uma Power PivotTable, um abaixo do outro verticalmente na mesma planilha.

• Clique na guia Página inicial na janela do Power Pivot.

• Clique em Tabela Dinâmica.

• Selecione Gráfico e Tabela (Vertical) na lista suspensa.

o Create PivotChart and PivotTable (Vertical)a caixa de diálogo aparece. Selecione Nova planilha e clique em OK.

Um gráfico dinâmico vazio e uma tabela dinâmica vazia aparecem verticalmente em uma nova planilha.

• Clique em PivotChart.

• Arraste Ano da tabela Medalhas para a área AXIS.

• Arraste a medalha da tabela Medalhas para a área ∑ VALORES.

• Clique com o botão direito no gráfico e selecione Alterar tipo de gráfico na lista suspensa.

• Selecione Gráfico de linhas.

• Marque a caixa Rótulos de dados nos elementos do gráfico.

• Altere o título do gráfico para Total No. of Medals – Year Wise.

Como você pode observar, o ano de 2008 apresenta o maior número de medalhas (2.450).

• Clique na Tabela Dinâmica.

• Arraste Sport da tabela Sports para a área ROWS.

• Arraste Gênero da tabela Medalhas para a área LINHAS.

• Arraste a medalha da tabela Medalhas para a área ∑ VALORES.

• Arraste Ano da tabela Medalhas para a área FILTROS.

• Filtre o campo Ano com o valor 2008.

• Altere o layout do relatório de tabela dinâmica para formulário de estrutura de tópicos.

• Filtre o campo Esporte com Filtros de Valor para Maior ou igual a 80.

Uma hierarquia no Modelo de Dados é uma lista de colunas aninhadas em uma tabela de dados que são consideradas como um único item quando usadas em uma Tabela Dinâmica do Power. Por exemplo, se você tiver as colunas - País, Estado, Cidade em uma tabela de dados, uma hierarquia pode ser definida para combinar as três colunas em um campo.

Na lista Campos da Tabela Dinâmica do Power, a hierarquia aparece como um campo. Portanto, você pode adicionar apenas um campo à Tabela Dinâmica, em vez dos três campos na hierarquia. Além disso, permite que você mova para cima ou para baixo os níveis aninhados de uma forma significativa.

Considere o seguinte Modelo de Dados para ilustrações neste capítulo.

Criação de uma hierarquia

Você pode criar hierarquias na visualização do diagrama do Modelo de Dados. Observe que você pode criar uma hierarquia com base em apenas uma única tabela de dados.

• Clique nas colunas - Sport, DisciplineID e Event na tabela de dados Medalha, nessa ordem. Lembre-se de que a ordem é importante para criar uma hierarquia significativa.

• Clique com o botão direito na seleção.

• Selecione Criar Hierarquia na lista suspensa.

O campo de hierarquia com os três campos selecionados conforme os níveis filho são criados.

Renomeando uma Hierarquia

Para renomear o campo de hierarquia, faça o seguinte -

• Clique com o botão direito em Hierarquia1.

• Selecione Renomear na lista suspensa.

Tipo EventHierarchy.

Criando uma tabela dinâmica com uma hierarquia no modelo de dados

Você pode criar uma Tabela Dinâmica do Power usando a hierarquia que você criou no Modelo de Dados.

• Clique na guia Tabela Dinâmica na Faixa de Opções na janela do Power Pivot.

• Clique em Tabela Dinâmica na Faixa de Opções.

o Create PivotTablea caixa de diálogo aparece. Selecione Nova planilha e clique em OK.

Uma tabela dinâmica vazia é criada em uma nova planilha.

Na lista de Campos da Tabela Dinâmica, EventHierarchyaparece como um campo na tabela Medalhas. Os outros campos da tabela Medalhas são recolhidos e exibidos como Mais Campos.

• Clique na seta

  
  na frente de EventHierarchy.

• Clique na seta

  
  na frente de Mais Campos.

Os campos em EventHierarchy serão exibidos. Todos os campos da tabela Medalhas serão exibidos em Mais Campos.

Como você pode observar, os três campos que você adicionou à hierarquia também aparecem em More Fieldscom caixas de seleção. Se você não quiser que eles apareçam na lista Campos da Tabela Dinâmica emMore Fields, você deve ocultar as colunas na tabela de dados - Medalhas na exibição de dados na janela do Power Pivot. Você sempre pode exibi-los quando quiser.

Adicione campos à tabela dinâmica da seguinte maneira -

• Arrastar EventHierarchy para a área ROWS.

• Arraste a medalha para a área ∑ VALUES.

Os valores do campo Esporte aparecem na Tabela Dinâmica com um sinal + na frente deles. A contagem de medalhas para cada esporte é exibida.

• Clique no sinal + antes de Esportes Aquáticos. Os valores do campo DisciplineID em Aquatics serão exibidos.

• Clique no filho D22 que aparece. Os valores do campo Evento em D22 serão exibidos.

Como você pode observar, a contagem de medalhas é dada para os eventos, que são somados no nível dos pais - DisciplineID, que são resumidos ainda mais no nível dos pais - Esporte.

Criação de uma hierarquia baseada em várias tabelas

Suponha que você queira exibir as Disciplinas na Tabela Dinâmica em vez de DisciplineIDs para torná-lo um resumo mais legível e compreensível. Para isso, é necessário ter o campo Disciplina na tabela de Medalhas que como você sabe não é. O campo Disciplina está na tabela de dados Disciplinas, mas você não pode criar uma hierarquia com campos de mais de uma tabela. Porém, existe uma maneira de obter o campo obrigatório da outra tabela.

Como sabem, as tabelas - Medalhas e Disciplinas estão relacionadas. Você pode adicionar o campo Disciplina da tabela Disciplinas à tabela Medalhas, criando uma coluna usando o relacionamento com DAX.

• Clique na exibição de dados na janela do Power Pivot.

• Clique na guia Design na Faixa de Opções.

• Clique em Adicionar.

A coluna - Adicionar coluna no lado direito da tabela é destacada.

Tipo = RELATED (Disciplines [Discipline])na barra de fórmulas. Uma nova coluna -CalculatedColumn1 é criado com os valores como valores do campo Disciplina na tabela Disciplinas.

Renomeie a nova coluna assim obtida na tabela Medalhas como Disciplina. Em seguida, você deve remover DisciplineID da Hierarquia e adicionar Discipline, que você aprenderá nas seções a seguir.

Removendo um nível filho de uma hierarquia

Como você pode observar, a hierarquia é visível apenas na visualização do diagrama e não na visualização de dados. Portanto, você pode editar uma hierarquia apenas na visualização do diagrama.

• Clique na visualização do diagrama na janela do Power Pivot.

• Clique com o botão direito em DisciplineID em EventHierarchy.

• Selecione Remove from Hierarchy na lista suspensa.

A caixa de diálogo Confirmar é exibida. CliqueRemove from Hierarchy.

O campo DisciplineID é excluído da hierarquia. Lembre-se de que você removeu o campo da hierarquia, mas o campo de origem ainda existe na tabela de dados.

Em seguida, você precisa adicionar o campo Discipline a EventHierarchy.

Adicionando um Nível Filho a uma Hierarquia

Você pode adicionar o campo Discipline à hierarquia existente - EventHierarchy como segue -

• Clique no campo da tabela Medalhas.

• Arraste-o para o campo Eventos abaixo em EventHierarchy.

O campo Discipline é adicionado a EventHierarchy.

Como você pode observar, a ordem dos campos em EventHierarchy é Sport – Event – ​​Discipline. Mas, como você sabe, tem que ser um Esporte-Disciplina-Evento. Portanto, você precisa alterar a ordem dos campos.

Mudando a ordem de um nível filho em uma hierarquia

Para mover o campo Disciplina para a posição após o campo Esporte, faça o seguinte -

• Clique com o botão direito do mouse no campo Discipline em EventHierarchy.

• Selecione Mover para cima na lista suspensa.

A ordem dos campos muda para Sport-Discipline-Event.

Tabela Dinâmica com Mudanças na Hierarquia

Para exibir as alterações feitas em EventHierarchy na Tabela Dinâmica, você não precisa criar uma nova Tabela Dinâmica. Você pode visualizá-los na própria Tabela Dinâmica existente.

Clique na planilha com a Tabela Dinâmica na janela do Excel.

Como você pode observar, na lista Campos da Tabela Dinâmica, os níveis filho em EventHierarchy refletem as alterações feitas na Hierarquia no Modelo de Dados. As mesmas alterações também são refletidas na Tabela Dinâmica de acordo.

Clique no sinal + na frente de Aquatics na Tabela Dinâmica. Os níveis filho aparecem como valores do campo Disciplina.

Escondendo e mostrando hierarquias

Você pode escolher ocultar as Hierarquias e mostrá-las sempre que quiser.

• Desmarque a caixa Hierarquias no menu superior da visualização do diagrama para ocultar as hierarquias.

• Marque a caixa Hierarquias para mostrar as hierarquias.

Criando uma hierarquia de outras maneiras

Além da maneira como você criou a hierarquia nas seções anteriores, você pode criar uma hierarquia de outras duas maneiras.

1. Clique no botão Criar Hierarquia no canto superior direito da tabela de dados Medalhas na visualização do diagrama.

Uma nova hierarquia é criada na tabela sem nenhum campo nela.

Arraste os campos Ano e Estação, nessa ordem, para a nova hierarquia. A hierarquia mostra os níveis filho.

2. Outra maneira de criar a mesma hierarquia é a seguinte -

• Clique com o botão direito no campo Ano na tabela de dados das Medalhas na visualização do diagrama.

• Selecione Criar Hierarquia na lista suspensa.

Uma nova hierarquia é criada na tabela com Ano como um campo filho.

Arraste a temporada de campo para a hierarquia. A hierarquia mostra os níveis filho.

Excluindo uma Hierarquia

Você pode excluir uma hierarquia do Modelo de Dados da seguinte maneira -

• Clique com o botão direito na hierarquia.

• Selecione Excluir na lista suspensa.

o Confirma caixa de diálogo aparece. CliqueDelete from Model.

A hierarquia é excluída.

Cálculos usando hierarquia

Você pode criar cálculos usando uma hierarquia. Em EventsHierarchy, você pode exibir o número de medalhas em um nível infantil como uma porcentagem do número de medalhas em seu nível pai da seguinte forma -

• Clique com o botão direito no valor de Contagem de medalha de um evento.

• Selecione Configurações do campo de valor na lista suspensa.

A caixa de diálogo Configurações do campo de valor é exibida.

• Clique no Show Values As aba.

• Selecione% do total da linha pai na lista e clique em OK.

Os níveis filho são exibidos como a porcentagem dos Totais Pai. Você pode verificar isso somando os valores percentuais do nível filho de um pai. A soma seria 100%.

Detalhando e Detalhando uma Hierarquia

Você pode fazer drill up e drill down rapidamente nos níveis em uma hierarquia usando a ferramenta Quick Explore.

• Clique em um valor do campo Evento na Tabela Dinâmica.

• Clique na ferramenta Exploração rápida -

  
  que aparece no canto inferior direito da célula que contém o valor selecionado.

o Explore box with Drill Upopção aparece. Isso ocorre porque a partir de Evento, você só pode fazer drill up, pois não há níveis filho abaixo dele.

Clique Drill Up.

Os dados da Tabela Dinâmica são detalhados até a Disciplina.

Clique na ferramenta Quick Explore -

que aparece no canto inferior direito da célula que contém um valor.

A caixa Explorar aparece com as opções Drill Up e Drill Down exibidas. Isso ocorre porque a partir da Disciplina, você pode ir até o Esporte ou ir até o Evento.

Dessa forma, você pode mover-se rapidamente para cima e para baixo na hierarquia.

Você pode criar relatórios estéticos de sua análise de dados com dados do Power Pivot que estão no modelo de dados.

Os recursos importantes são -

• Você pode usar gráficos dinâmicos para produzir relatórios visuais de seus dados. Você pode usar layouts de relatório para estruturar suas tabelas dinâmicas para torná-las facilmente legíveis.

• Você pode inserir Segmentações de Dados para filtrar dados no relatório.

• Você pode usar um Slicer comum para o gráfico dinâmico e a tabela dinâmica que estão no mesmo relatório.

• Assim que seu relatório final estiver pronto, você pode optar por ocultar os Slicers da exibição.

Você aprenderá como obter relatórios com as opções disponíveis no Power Pivot neste capítulo.

Considere o seguinte Modelo de Dados para ilustrações neste capítulo.

Relatórios baseados em Power PivotChart

Crie um Power PivotChart da seguinte forma -

• Clique na guia Início na Faixa de Opções na janela do PowerPivot.

• Clique em Tabela Dinâmica.

• Selecione PivotChart na lista suspensa.

• Clique New Worksheet na caixa de diálogo Criar gráfico dinâmico.

Um gráfico dinâmico vazio é criado em uma nova planilha na janela do Excel.

• Arraste Esporte da tabela de medalhas para a área do eixo.

• Arraste a medalha da tabela de medalhas para a área ∑ VALORES.

• Clique na guia ANALISAR em FERRAMENTAS DE PIVOTA na faixa de opções.

• Clique em Inserir Slicer no Grupo de Filtros. A caixa de diálogo Inset Slicers é exibida.

• Clique no campo NOC_CountryRegion na tabela de medalhas.

• Clique OK.

O Slicer NOC_CountryRegion é exibido.

• Selecione EUA.

• Arraste Gênero da tabela Medalhas para a área GÊNERO.

• Clique com o botão direito em PivotChart.

• Selecione Alterar tipo de gráfico na lista suspensa.

A caixa de diálogo Alterar tipo de gráfico é exibida.

Clique em Stacked Column.

• Insira o Slicer para o campo Esporte.

• Arraste Disciplina da tabela de Disciplinas para a área AXIS.

• Remova o campo Esporte da área AXIS.

• Selecione Esportes aquáticos no Slicer - Esporte.

Layout de relatório

Crie uma tabela dinâmica da seguinte forma -

• Clique na guia Home na Faixa de Opções na janela PowerPivot.

• Clique em Tabela Dinâmica.

• Clique em Tabela Dinâmica na lista suspensa. A caixa de diálogo Criar Tabela Dinâmica é exibida.

• Clique em Nova planilha e clique em OK. Uma tabela dinâmica vazia é criada em uma nova planilha.

• Arraste NOC_CountryRegion da tabela Medalhas para a área AXIS.

• Arraste Esporte da tabela de Medalhas para a área COLUNAS.

• Arraste Disciplina da tabela de Disciplinas para a área COLUNAS.

• Arraste a medalha para a área ∑ VALUES.

Clique no botão de seta ao lado de Column Labels e selecione Aquatics.

• Clique no botão de seta próximo a Row Labels.

• Selecione Filtros de valor na lista suspensa.

• Selecione Maior que ou igual a na segunda lista suspensa.

Digite 80 na caixa ao lado de Contagem de medalha maior ou igual a na caixa de diálogo Filtro de valor.

• Clique na guia DESIGN em PIVOTTABLE TOOLS na faixa de opções.

• Clique em Subtotais.

• Selecione Do Not Show Subtotals na lista suspensa.

A coluna Subtotais - Total de itens aquáticos é removida.

Clique em Layout do relatório e selecione Show in Outline Form na lista suspensa.

Marque a caixa Banded Rows.

Os nomes dos campos aparecem no lugar de rótulos de linha e rótulos de coluna e o relatório parece autoexplicativo.

Usando um Slicer comum

Crie um gráfico dinâmico e uma tabela dinâmica lado a lado.

• Clique na guia Home na Faixa de Opções na guia PowerPivot.

• Clique em Tabela Dinâmica.

• Selecione Gráfico e Tabela (Horizontal) na lista suspensa.

A caixa de diálogo Criar gráfico dinâmico e tabela dinâmica (horizontal) é exibida.

Selecione Nova planilha e clique em OK. Um gráfico dinâmico vazio e uma tabela dinâmica vazia aparecem um ao lado do outro em uma nova planilha.

• Clique em Gráfico Dinâmico.

• Arraste Disciplina da tabela de Disciplinas para a área AXIS.

• Arraste a medalha da tabela de medalhas para a área ∑ VALUES.

• Clique em Tabela Dinâmica.

• Arraste a Disciplina da tabela de Disciplinas para a área ROWS.

• Arraste a medalha da tabela de medalhas para a área ∑ VALUES.

• Clique na guia ANALISAR em FERRAMENTAS DE PIVOTA na faixa de opções.

• Clique em Inserir Slicer. A caixa de diálogo Inserir Slicers é exibida.

• Clique na tabela NOC_CountryRegion e Sport nas medalhas.

• Clique OK.

Dois Slicers - NOC_CountryRegion e Sport aparecem. Organize-os e dimensione-os para alinhá-los corretamente ao lado da Tabela Dinâmica.

• Selecione USA no NOC_CountryRegion Slicer.

• Selecione Aquatics no Sport Slicer. A tabela dinâmica é filtrada para os valores selecionados.

Como você pode observar, o gráfico dinâmico não é filtrado. Para filtrar o gráfico dinâmico com os mesmos filtros, você não precisa inserir Slicers novamente para o gráfico dinâmico. Você pode usar os mesmos Segmentadores de Dados que usou para a Tabela Dinâmica.

• Clique em NOC_CountryRegion Slicer.

• Clique no OPTIONS guia em SLICER TOOLS na fita.

• Clique Report Connectionsno grupo Slicer. oReport Connections a caixa de diálogo aparece para o Slicer NOC_CountryRegion.

Você pode ver que todas as tabelas e gráficos dinâmicos na pasta de trabalho estão listados na caixa de diálogo.

• Clique no gráfico dinâmico que está na mesma planilha que a tabela dinâmica selecionada e clique em OK.

• Repita para o Sport Slicer.

O gráfico dinâmico também é filtrado para os valores selecionados nos dois Slicers.

Em seguida, você pode adicionar detalhes ao gráfico dinâmico e à tabela dinâmica.

• Clique no gráfico dinâmico.

• Arraste Gênero para a área LEGEND.

• Clique com o botão direito em PivotChart.

• Selecione Alterar tipo de gráfico.

• Selecione Coluna Empilhada na caixa de diálogo Alterar Tipo de Gráfico.

• Clique na Tabela Dinâmica.

• Arraste o evento para a área ROWS.

• Clique na guia DESIGN em PIVOTTABLE TOOLS na faixa de opções.

• Clique em Layout do relatório.

• Selecione Formulário de esboço na lista suspensa.

Seleção de objetos para exibição no relatório

Você pode optar por não exibir os Segmentadores de Dados no Relatório final.

• Clique no OPTIONS guia em SLICER TOOLS na fita.

• Clique em Painel de Seleção no grupo Organizar. O Painel de Seleção aparece no lado direito da janela.

Como você pode observar, o símbolo

aparece próximo aos objetos no Painel de Seleção. Isso significa que esses objetos são visíveis.

• Clique no

  
  símbolo ao lado de NOC_CountryRegion.

• Clique no

  
  símbolo ao lado de Esporte. O
  
  símbolo é alterado para
  
  para ambos. Isso significa que a visibilidade dos dois Slicers está desativada.

Feche o painel de seleção.

Você pode ver que os dois Slicers não estão visíveis no Relatório.