Tutorial: análise de dados da Tabela Dinâmica usando um Modelo de Dados no Excel 2013

Em menos de uma hora, você poderá criar um relatório da Tabela Dinâmica no Excel que combina os dados de várias tabelas. A primeira parte deste tutorial o conduz através da importação e da exploração dos dados. Na segunda parte, você usará o suplemento Power Pivot para refinar o modelo de dados que reside por trás do relatório, aprendendo a adicionar cálculos, hierarquias e otimizações para os relatórios do Power View.

Importar alguns dados

Antes de criar uma Tabela Dinâmica, você precisará de alguns dados. Iremos obter alguns dados de vendas no banco de dados de exemplo que você baixou.

  1. Baixe os dados de exemplo (ContosoV2) para esse tutorial. Consulte Obter dados de exemplo para tutoriais do Modelo de dados e DAX para obter detalhes. Extraia e salve os arquivos de dados em um local que seja facilmente acessível, como Downloads ou Meus documentos.

  2. No Excel, abra uma pasta de trabalho em branco.

  3. Clique em Dados > Obter Dados Externos > Do Access.

  4. Vá para a pasta que contém os arquivos de dados de exemplo e selecione ContosoSales.

  5. Clique em Abrir. Devido ao fato de você estar se conectando a um arquivo do tipo banco de dados que contém várias tabelas, o diálogo Selecionar Tabela aparece para que você possa escolher as tabelas a serem importadas.

    caixa de diálogo Selecionar Tabela

  6. Em Selecionar Tabela, marque Habilitar seleção de várias tabelas.

  7. Escolha todas as tabelas e clique em OK.

  8. Em Importar Dados, clique em Relatório de Tabela Dinâmica e clique em OK.

O que aconteceu?

Você pode não ter percebido ainda, mas acabou de criar um modelo de dados. Ele é criado automaticamente quando você importa ou trabalha com várias tabelas simultaneamente no mesmo relatório da Tabela Dinâmica. O modelo é, em grande parte, transparente no Excel, mas é possível visualizá-lo e modificá-lo diretamente usando o suplemento Power Pivot. No Excel, a presença de um modelo de dados é evidente quando você vê um conjunto de tabelas na lista Campos da Tabela Dinâmica. Há várias maneiras de criar um modelo. Consulte Criar um modelo de dados no Excel para obter detalhes.

Explorar dados usando uma Tabela Dinâmica

Explorar dados é fácil: basta arrastar campos para as áreas Valores, Colunas e Linhas na lista de campos de Tabela Dinâmica.

  1. Na lista de campos, role até encontrar a tabela FactSales.

  2. Clique em SalesAmount. Como estes dados são numéricos, o Excel coloca automaticamente SalesAmount na área Valores.

  3. Em DimDate, arraste CalendarYear para Colunas.

  4. Em DimProductSubcategory, arraste ProductSubcategoryName para Linhas.

  5. Em DimProduct, arraste BrandName para Linhas, colocando-o abaixo da subcategoria.

A sua Tabela Dinâmica deverá ter um aspecto semelhante ao da tela abaixo.

Tabela Dinâmica com dados de exemplo

Com um esforço mínimo, você agora tem uma Tabela Dinâmica básica que inclui campos de quatro diferentes tabelas. O que tornou essa tarefa tão fácil foram as relações preexistentes entre as tabelas. Pelo fato de que as relações entre as tabelas existiam na fonte, e pelo fasto de você ter importado todas as tabelas com uma única operação, o Excel pôde recriar essas relações no modelo.

Mas, e se os seus dados provierem de diversas fontes ou forem importados em um momento posterior? Geralmente, é possível incorporar novos dados criando relações baseadas em colunas correspondentes. Na próxima etapa, você importará mais tabelas e verá quais são os requisitos e as etapas a realizar para criar novas relações.

Adicionar mais tabelas

Para aprender a estabelecer relações entre tabelas, é necessário ter algumas tabelas não conectadas adicionais com as quais trabalhar. Nesta etapa, você obterá os dados restantes usados neste tutorial importando um arquivo adiciona do tipo banco de dados e colando os dados de duas outras pastas de trabalho.

Adicionar categorias de produtos

  1. Na pasta de trabalho, abra uma nova planilha. Você a usará para armazenar dados adicionais.

  2. Clique em Dados > Obter Dados Externos > Do Access.

  3. Vá para a pasta que contém os arquivos de dados de exemplo e selecione ProductCategories. Clique em Abrir.

  4. Em Importar dados, selecione Tabela e clique em OK.

Adicionar dados geográficos

  1. Insira outra planilha.

  2. Nos arquivos de dados de exemplo, abra Geografia.xlsx, coloque o cursor em A1, em seguida, pressione Ctrl+Shift+End para selecionar todos os dados.

  3. Copiar os dados para a área de transferência.

  4. Cole os dados na planilha vazia que você acabou de adicionar.

  5. Clique em Formatar como Tabela, escolhendo qualquer estilo. Formatar os dados como uma tabela permite nomeá-la, o que será útil quando você definir relações em uma etapa posterior.

  6. Em Formatar como Tabela, verifique se Minha tabela tem cabeçalhos está selecionada. Clique em OK.

  7. Nomeie a tabela como Geografia. Em Ferramentas da Tabela > Design, digite Geografia em Nome da Tabela.

  8. Feche Geografia.xlsx para removê-la de seu espaço de trabalho.

Adicionar dados da loja

  • Repita as etapas anteriores para o arquivo Lojas.xlsx, colando seu conteúdo em uma planilha vazia. Nomeie a tabela como Lojas.

Agora, você deve ter quatro planilhas. A Planilha1 contém a Tabela Dinâmica, a Planilha2 contém ProductCategories, a Planilha3 contém Geografia e a Planilha4 contém Lojas. Como você nomeou cada tabela, a próxima etapa, a criação de relacionamentos, será muito mais simples.

Use os campos das tabelas que você acaba de importar

Você pode começar imediatamente a usar campos das tabelas que acaba de importar. Se o Excel não conseguir determinar como incorporar um campo no relatório de Tabela Dinâmica, você será solicitado a criar uma relação entre tabelas que associe a nova tabela com uma que já faça parte do modelo.

  1. No início dos Campos de Tabela Dinâmica, clique em Todos para visualizar a lista completa das tabelas disponíveis.

  2. Role até o final da lista. É aí que se encontrarão as novas tabelas que você acabou de adicionar.

  3. Expanda Lojas.

  4. Arraste StoreName para a área Filtros.

  5. Observe que o Excel solicita que você crie uma relação. Essa notificação ocorre porque você usou campos de uma tabela que não está relacionada com o modelo.

  6. Clique em Criar para abrir o diálogo Criar Relação.

  7. Em Tabela, escolha FactSales. Nos dados de exemplo que você está usando, FactSales contém informações detalhadas de vendas e custos sobre o negócio da Contoso, assim como as chaves para outras tabelas, inclusive os códigos das lojas que também estão presentes no arquivo Lojas.xlsx importado na etapa anterior.

  8. Em Coluna (Estrangeira), escolha StoreKey.

  9. Em Tabela Relacionada, escolha Lojas.

  10. Em Coluna Relacionada (Primária), escolha StoreKey.

  11. Clique em OK.

Nos bastidores, o Excel está criando um Modelo de Dados que pode ser usado na pasta de trabalho em qualquer número de Tabelas Dinâmicas, Gráficos Dinâmicos ou relatórios do Power View. Fundamental para este modelo são as relações entre tabelas que determinam os caminhos de navegação e cálculo usados em um relatório de Tabela Dinâmica. Na próxima tarefa, você criará relações manualmente para conectar os dados que acabou de importar.

Adicionar relações

Você pode criar sistematicamente relações entre tabelas para todas as novas tabelas que importar. Se você estiver compartilhando a pasta com seus colegas, a presença de relações predefinidas será apreciada se eles não conhecerem os dados tão bem como você.

Ao criar relações manualmente, você trabalhará com duas tabelas de cada vez. Para cada tabela, você escolherá colunas que dizem ao Excel como procurar linhas relacionadas em outra tabela.

Seu navegador não oferece suporte a vídeo. Instale o Microsoft Silverlight, o Adobe Flash Player ou o Internet Explorer 9.

Relacione ProductSubcategory a ProductCategory

  1. No Excel, clique em Dados > Relações > Novo.

  2. Em Tabela, escolha DimProductSubcategory.

  3. Em Coluna (Estrangeira), escolha ProductCategoryKey.

  4. Em Tabela Relacionada, escolha Table_ProductCategory.accdb.

  5. Em Coluna Relacionada (Primária), escolha ProductCategoryKey.

  6. Clique em OK.

  7. Feche a caixa de diálogo Gerenciar Relações.

Adicione categorias à Tabela Dinâmica.

Embora o Modelo de Dados tenha sido atualizado para incluir tabelas e relações adicionais, a Tabela Dinâmica ainda não as está usando. Nesta tarefa, você adicionará ProductCategory à lista Campos da Tabela Dinâmica.

  1. Em Campos da Tabela Dinâmica, clique em Todos para mostrar tabelas que existem no Modelo de Dados.

  2. Role até a parte inferior da lista.

  3. Na área Linhas, remova BrandName.

  4. Expanda Table_DimProductCategories.accdb.

  5. Arraste ProductCategoryName para a área Linhas, colocando-o acima de ProductSubcategory.

  6. Em Campos da Tabela Dinâmica, clique em Ativos para verificar se as tabelas que você acabou de usar são agora conhecidas para a Tabela Dinâmica.

Ponto de verificação: revise o que você aprendeu

Você agora tem uma Tabela Dinâmica que inclui dados de várias tabelas, várias das quais você importou em uma etapa subsequente. Para utilizar esses dados com eficácia, você teve que criar relacionamentos de tabelas que o Excel usa para correlacionar as linhas. Você aprendeu que ter colunas que fornecem dados correspondentes é essencial para procurar linhas relacionadas. Nos arquivos de dados de exemplo, todas as tabelas incluem uma coluna que pode ser usada para esta finalidade.

Embora a Tabela Dinâmica esteja funcional, você provavelmente observou vários itens que podem ser melhorados. A Lista de Campos da Tabela Dinâmica parece ter tabelas adicionais (DimEntity) e colunas (ETLLoadID) que não estão relacionadas com o negócio da Contoso. E nós ainda não integramos os dados de Geografia.

A seguir: visualize e estenda o seu modelo com o Power Pivot

Na próxima série de tarefas, você usará o suplemento Microsoft Office Power Pivot in Microsoft Excel 2013 para estender o modelo. Você descobrirá que pode criar relações com mais facilidade usando o Modo de Exibição de Diagrama que o suplemento fornece. Você também usará o suplemento para criar cálculos e hierarquias, ocultar os itens que não devem aparecer na lista de campos e otimizar os dados para relatórios adicionais.

Observação :  O suplemento Power Pivot in Microsoft Excel 2013 está disponível no Office Professional Plus. Consulte Suplemento Power Pivot no Microsoft Excel 2013 para obter mais informações.

Adicione Power Pivot à faixa de opções do Excel habilitando o suplemento Power Pivot.

  1. Vá para Arquivo > Opções > Suplementos.

  2. Na caixa Gerenciar, clique em Suplementos de COM> Ir.

  3. Marque a caixa Microsoft Office Power Pivot in Microsoft Excel 2013 e clique em OK.

A faixa de opções agora tem uma guia Power Pivot.

Adicionar uma relação usando o Modo de Exibição de Diagrama no Power Pivot

  1. No Excel, clique na Planilha3 para torná-la a planilha ativa. A Planilha3 contém a tabela Geografia que você importou anteriormente.

  2. Na faixa de opções clique em Power Pivot > Adicionar ao Modelo de Dados. Essa etapa adiciona a tabela Geografia ao modelo. Ela também abre o suplemento Power Pivot, que você vai usar para realizar as etapas restantes desta tarefa.

  3. Observe que a janela do Power Pivot exibe todas as tabelas do modelo, incluindo Geografia. Clique em algumas tabelas. No suplemento, você pode exibir todos os dados que seu modelo contém.

  4. Na janela do Power Pivot, na seção Modo de Exibição, clique em Modo de Exibição de Diagrama.

  5. Use a barra de controle deslizante para redimensionar o diagrama de forma que você possa ver todos os objetos no diagrama. Observe que duas tabelas não estão relacionadas com o resto do diagrama: DimEntity e Geografia.

  6. Clique com o botão direito em DimEntity e clique em Excluir. Essa tabela é um artefato do banco de dados original, não sendo necessária para o modelo.

  7. Amplie em Geografia para que você possa exibir todos os campos. Você pode usar o controle deslizante para aumentar o diagrama de tabela.

  8. Observe que Geografia tem GeographyKey. Essa coluna contém valores que identificam com exclusividade cada linha da tabela Geografia. Vejamos se outras tabelas do modelo também usam essa chave. Em caso afirmativo, poderemos criar uma relação que conecta Geografia ao resto do modelo.

  9. Clique em Localizar.

  10. Em Localizar metadados, digite GeographyKey.

  11. Clique em Localizar próximo várias vezes. Você verá que GeographyKey aparece na tabela Geografia e na tabela Lojas.

  12. Reposicione a tabela Geografia para que fique ao lado de Lojas.

  13. Arraste a coluna GeographyKey em Stores para a coluna GeographyKey em Geografia. O Power Pivot desenhará uma linha entre as duas colunas, indicando a relação.

Nesta tarefa, você aprendeu uma nova técnica para adicionar tabelas e criar relações. Você agora tem um modelo completamente integrado, com todas as tabelas conectadas e disponíveis para a Tabela Dinâmica em Planilha1.

Dica :  Na exibição de diagrama, vários diagramas de tabelas estão completamente estendidos, exibindo colunas como ETLLoadID, LoadDate e UpdateDate. Esses campos específicos são artefatos do depósito de dados original da Contoso, adicionados para auxiliar nas operações de extração e carregamento de dados. Eles não são necessários no seu modelo. Para descartá-los, realce e clique com o botão direito do mouse no campo em questão, e clique em Excluir.

Criar uma coluna calculada

No Power Pivot, é possível usar Expressões de Análise de Dados (DAX) para adicionar cálculos. Nesta tarefa, você calculará o lucro total e adicionará uma coluna calculada que referencia valores de dados de outras tabelas. Mais tarde você verá como usar colunas referenciadas para simplificar o seu modelo.

  1. Na janela do Power Pivot, volte para o Modo de Exibição de Dados.

  2. Renomeie a tabela Table_ProductCategories.accdb com um nome mais fácil de lembrar. Você fará referência a essa tabela nas próximas etapas e um nome mais curto facilitará a leitura dos cálculos. Clique com o botão direito no nome da tabela, clique em Renomear, digite ProductCategories e pressione Enter.

  3. Selecione a tabela FactSales.

  4. Clique em Design > Colunas > Adicionar.

  5. Na barra de fórmulas acima da tabela, digite a fórmula abaixo. O Preenchimento Automático ajuda a digitar os nomes plenamente qualificados de colunas e tabelas, e mostra uma lista das funções disponíveis. Também é possível simplesmente clicar na coluna, e o Power Pivot adiciona o nome da coluna à fórmula.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Quando terminar de criar a fórmula, pressione Enter para aceitá-la.

    Valores são populados para todas as linhas na coluna calculada. Se você rolar para baixo na tabela, verá que as linhas podem ter valores diferentes para essa coluna baseados nos dados que estão em cada linha.

  7. Renomeie a coluna clicando com o botão direito em CalculatedColumn1 e selecionando Renomear coluna. Digite Lucro e pressione Enter.

  8. Agora, selecione a tabela DimProduct.

  9. Clique em Design > Colunas > Adicionar.

  10. Na barra de fórmulas acima da tabela, digite a fórmula a seguir.

    = RELATED(ProductCategories[ProductCategoryName])

    A função RELATED retorna um valor de uma tabela relacionada. Neste caso, a tabela ProductCategories inclui os nomes das categorias de produtos, que serão úteis para a tabela DimProduct quando você construir uma hierarquia que inclua informações sobre as categorias. Para obter mais informações sobre essa função, consulte Função RELATED (DAX).

  11. Quando terminar de criar a fórmula, pressione Enter para aceitá-la.

    Os valores são populados para todas as linhas na coluna calculada. Se você rolar para baixo na tabela, verá que cada linha tem um Nome de Categoria de Produto.

  12. Renomeie a coluna clicando com o botão direito em CalculatedColumn1 e selecionando Renomear coluna. Digite ProductCategory e pressione Enter.

  13. Clique em Design > Colunas > Adicionar.

  14. Na barra de fórmulas acima da tabela, digite a fórmula abaixo e pressione Enter para aceitar a fórmula.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Renomeie a coluna clicando com o botão direito em CalculatedColumn1 e selecionando Renomear coluna. Digite ProductSubcategory e pressione Enter.

Criar uma hierarquia

A maioria dos modelos inclui dados que são inerentemente hierárquicos. Exemplos comuns incluem dados de calendário, dados geográficos e categorias de produtos. Criar hierarquias é útil porque você pode arrastar um item (a hierarquia) para um relatório em vez de ter que montar e ordenar os mesmos campos toda vez.

  1. No Power Pivot, alterne para Exibição de diagrama. Expanda a tabela DimDate para visualizar com maior facilidade todos os seus campos.

  2. Pressione e mantenha pressionado Ctrl, e clique nas colunas CalendarYear, CalendarQuarter e CalendarMonth (você precisará rolar a tabela para baixo).

  3. Com as três colunas selecionadas, clique com o botão direito em uma delas e clique em Criar Hierarquia. Um nó de hierarquia pai, Hierarquia 1, é criado na parte inferior da tabela e as colunas selecionadas são copiadas sob a hierarquia como nós filho.

  4. Digite Datas como o nome da sua nova hierarquia.

  5. Adicione a coluna FullDateLabel à hierarquia. Clique com o botão direito em FullDateLabel e selecione Adicionar à hierarquia. Escolha Data. FullDateLabel contém uma data completa, incluindo ano, mês e dia. Verifique se FullDateLabel aparece por último na hierarquia. Agora, você tem uma hierarquia de vários níveis, que inclui ano, trimestre, mês e dias individuais do calendário.

  6. Ainda no Modo de Exibição de Diagrama, aponte para a tabela DimProduct e clique no botão Criar hierarquia no cabeçalho da tabela. Um nó-pai vazio da hierarquia será exibido na parte inferior da tabela.

  7. Digite Categorias de Produtos como o nome da nova hierarquia.

  8. Para criar nós-filhos da hierarquia, arraste ProductCategory e ProductSubcategory para a hierarquia.

  9. Clique com o botão direito em ProductName e selecione Adicionar à hierarquia. Escolha Categorias de Produtos.

Agora que você conhece algumas maneiras diferentes de criar uma hierarquia, vamos usá-las na Tabela Dinâmica.

  1. Voltar para o Excel.

  2. Na Planilha1 (a planilha que contém a Tabela Dinâmica), remova os campos da área Linhas.

  3. Substitua pela nova hierarquia Categorias de Produtos em DimProduct.

  4. De maneira semelhante, substitua CalendarYear na área Colunas pela hierarquia Datas em DimDate.

Quando você explorar os dados agora, será fácil ver os benefícios de usar hierarquias. Você pode expandir e fechar áreas diferentes da Tabela Dinâmica de maneira independente, fornecendo mais controle sobre como o espaço disponível é usado. Além disso, ao adicionar uma única hierarquia a Linhas e Colunas, você obtém uma análise avançada e imediata, sem ter que empilhar vários campos para obter um efeito semelhante.

Ocultar colunas

Agora que você criou uma hierarquia Categorias de Produtos e colocou-a em DimProduct, já não precisa de DimProductCategory nem de DimProductSubcategory na lista Campos da Tabela Dinâmica. Nesta tarefa, você aprenderá a ocultar tabelas e colunas estranhas que estão ocupando espaço na lista Campos da Tabela Dinâmica. Ao ocultar as tabelas e as colunas, você melhora a experiência de relatório sem afetar o modelo que fornece relações de dados e cálculos.

Seu navegador não oferece suporte a vídeo. Instale o Microsoft Silverlight, o Adobe Flash Player ou o Internet Explorer 9.

Você pode ocultar colunas individuais, um intervalo de colunas ou uma tabela inteira. Os nomes de tabela e coluna são esmaecidos para refletir que ela está oculta para o relatório de clientes que consome o modelo. Colunas ocultas ficam esmaecidas no modelo indicar seu estado, mas permanecem visíveis na Exibição de Dados, de forma que você possa continuar a trabalhar com elas.

  1. No Power Pivot, verifique se o Modo de Exibição de Dados está selecionado.

  2. Nas guias da parte inferior, clique com o botão direito em DimProductSubcategory e selecione Ocultar das ferramentas do cliente.

  3. Repita para ProductCategories.

  4. Abra DimProduct.

  5. Clique com o botão direito nas seguintes colunas e clique em Ocultar das ferramentas do cliente:

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Selecione várias colunas adjacentes. Inicie com ClassID e continue até ProductSubcategory no final. Clique com o botão direito para ocultá-las.

  7. Repita para outras tabelas, removendo as IDs, chaves ou outros detalhes que você não usará neste relatório.

Volte para o Excel para a Planilha1, com a lista Campos da Tabela Dinâmica para ver a diferença. O número de tabelas é reduzido e DimProduct inclui somente os itens que você usará mais provavelmente ao analisar as vendas.

Crie um relatório do Power View

Os relatórios de Tabela Dinâmica não constituem o único tipo de relatório a se beneficiar com um Modelo de Dados. Usando o mesmo modelo que você acabou de criar, você poderá adicionar uma planilha do Power View para experimentar alguns dos layouts fornecidos.

  1. No Excel, clique em Inserir > Power View.

    Observação :  Se esta for a primeira vez que você usa o Power View neste computador, primeiro você será solicitado a habilitar o suplemento e a instalar o Silverlight.

  2. Em Campos do Power View, clique na seta ao lado da tabela FactSales e clique em SalesAmount.

  3. Expanda a tabela Geografia e clique em RegionCountryName.

  4. Na faixa de opções, clique em Mapa.

  5. Um relatório de mapa é exibido. Arraste um canto para redimensioná-lo. No mapa, círculos azuis de tamanhos variados indicam desempenho de vendas para diferentes países ou regiões.

Otimize para relatório do Power View

Algumas pequenas alterações no seu modelo resultarão em respostas mais intuitivas ao elaborar um relatório do Power View. Nesta tarefa, você adicionará as URLs de sites para diversos fabricantes e categorizará esses dados como uma URL da Web, de maneira que o endereço da URL seja exibido como um link.

A primeira etapa consiste em adicionar URLs à pasta de trabalho.

  1. No Excel, abra uma nova planilha e copie estes valores:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Empresa Aventura

http://www.fabrikam.com

Fabrikam, Inc.

  1. Formate as células como uma tabela e nomeie a tabela como URL.

  2. Crie uma relação entre a tabela e a URL que contém os nomes dos fabricantes, DimProduct:

    1. Clique em Dados > Relações. Aparecerá o diálogo Criar Relação.

    2. Clique em Novo.

    3. Em Tabela, selecione DimProduct.

    4. Em Coluna, selecione Fabricante.

    5. Em Tabela Relacionada, selecione URL.

    6. Em Tabela Relacionada (Principal), selecione ManufacturerID.

Para comparar os resultados de antes e depois, inicie um novo relatório do Power View e adicione FactSales | SalesAmount, dimProduct | Manufacturer, e URL | ManufacturerURL a um relatório. Observe que os URLs aparecem como texto estático.

Para renderizar uma URL como hiperlink ativo, é necessária uma categorização. Para categorizar uma coluna, use o Power Pivot.

  1. Em Power Pivot, abra a URL.

  2. Selecione ManufacturerURL.

  3. Clique em Avançado > Propriedades de Relatório > Categoria de Dados: Não Categorizado.

  4. Clique na seta para baixo.

  5. Selecione URL da Web.

  6. No Excel, clique em Inserir > Power View.

  7. Em Campos do Power View, selecione FactSales | SalesAmount, dimProduct | Manufacturer, e URL | ManufacturerURL. Desta vez, os URLs aparecerão como hiperlinks.

Outras otimizações do Power View incluem definir um campo padrão definido para cada tabela e configurar as propriedades que determinam se as linhas de dados repetidos são agregadas ou listadas independentemente. Consulte Configurar o conjunto de campos padrão para os relatórios do Power View e Configurar as propriedades de comportamento da tabela para os relatórios do Power View para obter mais informações.

Criar campos calculados

Na segunda tarefa, Explorar dados usando uma Tabela Dinâmica, você clicou no campo SalesAmount na Lista de Campos de Tabela Dinâmica. Sendo SalesAmount uma coluna numérica, ela foi automaticamente colocada na área Valores da Tabela Dinâmica. A soma de SalesAmount estava em seguida pronta para calcular os volumes de vendas para quaisquer filtros que fossem aplicados. Neste caso, inicialmente sem filtros, e em seguida com CalendarYear, ProductSubcategoryName e BrandName.

O que você realmente fez foi criar um campo calculado implícito, facilitando, assim, a análise dos volumes de vendas a partir da tabela FactSales em comparação com outros campos, como categoria do produto, região e datas. Os campos calculados implícitos são criados pelo Excel ao arrastar um campo para a área Valores ou ao clicar em um campo numérico, como você fez com SalesAmount. Os campos calculados implícitos são fórmulas que usam funções de agregação padrão, como SOMA, CONT.NÚM e MÉDIA, criadas automaticamente para você.

Existem também outros tipos de campos calculados. É possível criar campos calculados explícitos no Power Pivot. Diferentemente dos campos calculados implícitos, que só podem ser usados na Tabela Dinâmica onde foram criados, os campos calculados explícitos podem ser usados em qualquer Tabela Dinâmica da pasta de trabalho ou por qualquer relatório que use o Modelo de dados como uma fonte de dados. Com os campos calculados explícitos criados no Power Pivot, você pode usar a AutoSoma para criar automaticamente campos calculados usando agregações padrão ou criar seus próprios campos usando uma fórmula criada com DAX (Expressões de Análise de Dados).

Como você pode imaginar, a criação de campos calculados pode facilitar enormemente a análise de dados. Vamos então ver como criá-los.

Criar campos calculados no Power Pivot é fácil, se você usar AutoSoma.

  1. Na tabela FactSales, clique na coluna Lucro.

  2. Clique em Cálculos > AutoSum. Observe que um novo campo calculado denominado Soma do Lucro foi criado automaticamente na célula da Área de Cálculo diretamente embaixo da coluna Lucro.

  3. No Excel, na Planilha1, na lista de campos, em FactSales, clique em Soma do Lucro.

Pronto! Basta fazer isso para criar um campo calculado usando uma função de agregação padrão no Power Pivot. Como você pode ver, em apenas uns poucos minutos, você criou o campo calculado Soma de Lucros e o adicionou à Tabela dinâmica, facilitando a análise dos lucros conforme os filtros aplicados. Neste caso, a Soma dos Lucros está filtrada pelas hierarquias Categoria de Produto e Datas.

Mas o que acontece se você precisa fazer uma análise mais detalhada, como o número de vendas de um canal, produto ou categoria específicos? Para isso você precisará criar outro campo calculado que conte o número de linhas, uma para cada venda, na tabela FactSales, conforme os filtros aplicados.

  1. Na tabela FactSales, clique na coluna SalesKey.

  2. Em Cálculos, clique na seta para baixo em AutoSoma > Contagem.

  3. Renomeie o novo campo calculado clicando com o botão direito em Contagem de SalesKey na área de cálculo, em seguida, na barra de fórmula, altere Contagem de SalesKey para apenas Contagem e pressione Enter. Ao contrário das colunas calculadas, os nomes dos campos calculados são incluídos como parte da fórmula DAX.

  4. No Excel, na Planilha1, na lista de campos, em FactSales, clique em Contagem.

Observe que uma nova coluna, Contagem, é adicionada à Tabela Dinâmica, exibindo o número de vendas conforme os filtros aplicados. Exatamente como com o campo calculado Soma de Lucros, você vê o campo Contagem filtrado pelas hierarquias Categoria de Produto e Datas.

Vamos criar outro campo. Desta vez você criará um campo calculado que calcula a porcentagem das vendas totais para um contexto ou filtro específico. Todavia, diferentemente dos campos calculados anteriores que você criou usando AutoSum, desta vez você inserirá uma fórmula manualmente.

  1. Na tabela FactSales, na área de cálculo, clique em uma célula vazia. Dica: a célula superior esquerda é um ótimo lugar para começar a colocar os campos calculados. Isso faz com que seja mais fácil localizá-los. Você pode mover-se por qualquer campo calculado na Área de cálculo.

  2. Na barra de fórmulas, digite e use IntelliSense para criar a seguinte fórmula: Porcentagem de todos os produtos: =[CONT.NÚM]/CALCULAR([CONT.NÚM], TUDO(DimProduct))

  3. Pressione ENTER para aceitar a fórmula.

  4. No Excel, na Planilha1, na lista de campos, em FactSales, clique em Porcentagem de todos os produtos.

  5. Na Tabela Dinâmica, faça uma multisseleção das colunas Porcentagem de todos os produtos.

  6. Na guia Página Inicial, clique em Número > Porcentagem. Use duas casas decimais para formatar cada nova coluna.

O que esse novo campo calculado faz é calcular a porcentagem das vendas totais para um dado contexto de filtragem. Neste caso, o nosso contexto de filtragem ainda são as hierarquias Categoria de Produto e Datas. Você pode ver, por exemplo, que os computadores, como uma porcentagem das vendas totais dos produtos, cresceram ao longo dos anos.

Criar fórmulas para colunas calculadas e campos calculados será muito fácil para você, caso tenha familiaridade com a criação de fórmulas no Excel. Tendo ou não familiaridade com as fórmulas do Excel, um ótimo lugar para aprender as noções básicas das fórmulas DAX é seguindo as aulas em Início Rápido: aprenda as noções básicas de DAX em 30 minutos.

Salvar seu trabalho

Salve a sua pasta de trabalho, para poder usá-la com outros tutoriais ou para ulteriores explorações.

Próximas etapas

Embora você possa importar dados facilmente do Excel, é geralmente mais rápido e mais eficiente importar usando o suplemento Power Pivot. Você pode filtrar os dados que está importando, excluindo as colunas desnecessárias. Você pode também escolher entre usar um construtor de consultas ou um comando de consulta para recuperar os dados. Como uma próxima etapa, saiba mais sobre essas abordagens alternativas: Obter dados de um feed de dados no Power Pivot e Importar dados dos Serviços de análise ou Power Pivot.

O relatório do Power View é criado para trabalhar com Modelos de dados semelhantes ao que você acabou de criar. Leia mais para aprender sobre as visualizações avançadas que o Power View traz para o Excel: Iniciar o Power View no Excel 2013 e Power View: explore, visualize e apresente seus dados.

Experimente aprimorar seu modelo de dados para elaborar relatórios do Power View melhores seguindo este Tutorial: Otimizar seu modelo de dados para os relatórios do Power View

Expanda suas habilidades
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×