Benefícios do pivô por modelo de dados

Ao construir uma tabela dinâmica no Excel, na primeira caixa de diálogo, onde somos solicitados a definir o intervalo inicial e escolher um local para inserir a tabela dinâmica, há uma caixa de seleção discreta, mas muito importante abaixo – Adicione esses dados ao modelo de dados (Adicione estes dados para Modelo de Dados) e, um pouco mais alto, o interruptor Use o modelo de dados deste livro (Use o modelo de dados desta pasta de trabalho):

Infelizmente, muitos usuários que estão familiarizados com tabelas dinâmicas há muito tempo e as usam com sucesso em seu trabalho, às vezes não entendem realmente o significado dessas opções e nunca as usam. E em vão. Afinal, criar uma tabela dinâmica para o Modelo de Dados nos dá várias vantagens muito importantes em comparação com a tabela dinâmica clássica do Excel.

No entanto, antes de considerar esses “pãezinhos” de perto, vamos primeiro entender o que, de fato, é esse Modelo de Dados?

O que é um modelo de dados

Modelo de dados (abreviado como MD ou DM = Modelo de Dados) é uma área especial dentro de um arquivo Excel onde você pode armazenar dados tabulares – uma ou mais tabelas vinculadas, se desejar, umas às outras. Na verdade, trata-se de um pequeno banco de dados (cubo OLAP) incorporado a uma pasta de trabalho do Excel. Comparado ao armazenamento clássico de dados na forma de tabelas regulares (ou inteligentes) em planilhas do próprio Excel, o Modelo de Dados apresenta várias vantagens significativas:

• As mesas podem ter até 2 bilhões de linhas, e uma planilha do Excel pode caber um pouco mais de 1 milhão.
• Apesar do tamanho gigantesco, o processamento dessas tabelas (filtragem, classificação, cálculos sobre elas, resumo de construção etc.) Muito rápido Muito mais rápido que o próprio Excel.
• Com os dados no modelo, você pode realizar cálculos adicionais (se desejar, muito complexos) usando linguagem DAX integrada.
  
• Todas as informações carregadas no Modelo de Dados são muito fortemente comprimido usando um arquivador embutido especial e aumenta moderadamente o tamanho do arquivo original do Excel.
  

O modelo é gerenciado e calculado por um add-in especial embutido no Microsoft Excel – pivôsobre o qual já escrevi. Para habilitá-lo, na guia revelador clique suplementos COM (Desenvolvedor — Suplementos COM) e marque a caixa apropriada:

Se guias revelador (Desenvolvedor)você não pode vê-lo na faixa de opções, você pode ativá-lo através Arquivo – Opções – Configuração da fita (Arquivo — Opções — Personalizar Faixa de Opções). Se na janela mostrada acima na lista de suplementos COM você não possui o Power Pivot, ele não está incluído na sua versão do Microsoft Office 🙁

Na guia Power Pivot que aparece, haverá um grande botão verde claro Assistência Domiciliária (Gerenciar), clicando no qual abrirá a janela do Power Pivot na parte superior do Excel, onde veremos o conteúdo do Modelo de Dados do livro atual:

Uma observação importante ao longo do caminho: uma pasta de trabalho do Excel pode conter apenas um modelo de dados.

Carregar tabelas no modelo de dados

Para carregar dados no modelo, primeiro transformamos a tabela em um atalho de teclado “inteligente” dinâmico Ctrl+T e dê um nome amigável na guia Construtor (Desenhar). Esta é uma etapa necessária.

Então você pode usar qualquer um dos três métodos para escolher:

• Pressione o botão Adicionar ao modelo (Adicionar ao modelo de dados) aba pivô aba Início (Home).
  
• Escolhendo equipes Inserir – Tabela Dinâmica (Inserir - Tabela Dinâmica) e ative a caixa de seleção Adicione esses dados ao modelo de dados (Adicione esses dados ao modelo de dados). Neste caso, de acordo com os dados carregados no Modelo, uma tabela dinâmica também é construída imediatamente.
  
• Na guia Avançado Data (Encontro: Data) clique no botão Da tabela/intervalo (Da tabela/intervalo)para carregar nossa tabela no editor do Power Query. Este caminho é o mais longo, mas, se desejar, aqui você pode realizar limpeza de dados adicionais, edição e todo tipo de transformações, nas quais o Power Query é muito forte.

  Em seguida, os dados combinados são carregados no modelo pelo comando Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para…). Na janela que se abre, selecione a opção Basta criar uma conexão (Apenas criar conexão) e, o mais importante, marque Adicione esses dados ao modelo de dados (Adicione esses dados ao modelo de dados).
  

Construímos um resumo do Modelo de Dados

Para construir um modelo de dados resumido, você pode usar qualquer uma das três abordagens:

• Aperte o botão tabela de resumo (Tabela Dinâmica) na janela do Power Pivot.
• Selecionar comandos no Excel Inserir – Tabela Dinâmica e mude para o modo Use o modelo de dados deste livro (Inserir — Tabela Dinâmica — Use o Modelo de Dados desta pasta de trabalho).
• Escolhendo equipes Inserir – Tabela Dinâmica (Inserir - Tabela Dinâmica) e ative a caixa de seleção Adicione esses dados ao modelo de dados (Adicione esses dados ao modelo de dados). A tabela “inteligente” atual será carregada no Modelo e uma tabela de resumo será construída para todo o Modelo.

Agora que descobrimos como carregar dados no Modelo de Dados e construir um resumo sobre ele, vamos explorar os benefícios e vantagens que isso nos oferece.

Benefício 1: Relacionamentos entre tabelas sem usar fórmulas

Um resumo regular só pode ser criado usando dados de uma tabela de origem. Se você tiver vários deles, por exemplo, vendas, lista de preços, diretório de clientes, registro de contratos, etc., primeiro você terá que coletar dados de todas as tabelas em uma usando funções como PROCV (PROCV), ÍNDICE (ÍNDICE), MAIS EXPOSTO (COMBINE), SUMMESLIMN (SOMAS) e similar. Isso é longo, tedioso e leva seu Excel a um “pensamento” com uma grande quantidade de dados.

No caso de um resumo do Modelo de Dados, tudo é muito mais simples. Basta configurar as relações entre as tabelas uma vez na janela do Power Pivot – e pronto. Para isso, na aba pivô aperte o botão Assistência Domiciliária (Gerenciar) e depois na janela que aparece – o botão Visualização de gráfico (Visualização do Diagrama). Resta arrastar nomes de colunas (campos) comuns (chave) entre tabelas para criar links:

Depois disso, no resumo do Modelo de Dados, você pode jogar na área de resumo (linhas, colunas, filtros, valores) quaisquer campos de qualquer tabela relacionada – tudo será vinculado e calculado automaticamente:

Benefício 2: conte valores exclusivos

Uma tabela dinâmica regular nos dá a oportunidade de escolher uma das várias funções de cálculo incorporadas: soma, média, contagem, mínimo, máximo, etc. No resumo do Modelo de Dados, uma função muito útil é adicionada a esta lista padrão para contar os número de únicos (valores não repetidos). Com sua ajuda, por exemplo, você pode contar facilmente o número de itens exclusivos de mercadorias (gama) que vendemos em cada cidade.

Clique com o botão direito do mouse no campo - comando Opções do campo de valor e na aba Divisão de Escolha Número de elementos diferentes (Contagem distinta):

Benefício 3: Fórmulas DAX personalizadas

Às vezes, você precisa realizar vários cálculos adicionais em tabelas dinâmicas. Nos resumos regulares, isso é feito usando campos e objetos calculados, enquanto o resumo do modelo de dados usa medidas em uma linguagem DAX especial (DAX = Data Analysis Expressions).

Para criar uma medida, selecione na guia pivô Command Medidas - Criar Medida (Medidas - Nova medida) ou apenas clique com o botão direito na tabela na lista Pivot Fields e selecione Adicionar medida (Adicionar medida) no menu de contexto:

Na janela que se abre, defina:

• Nome da mesaonde a medida criada será armazenada.
• Nome da medida – qualquer nome que você entenda para o novo campo.
• Descrição – opcional.
• Fórmula – o mais importante, porque aqui entramos manualmente ou clicamos no botão fx e selecione uma função DAX da lista, que deve calcular o resultado quando lançarmos nossa medida na área de Valores.
• Na parte inferior da janela, você pode definir imediatamente o formato do número para a medida na lista Categoria.
  

A linguagem DAX nem sempre é fácil de entender porque opera não com valores individuais, mas com colunas e tabelas inteiras, ou seja, requer alguma reestruturação de pensamento após as fórmulas clássicas do Excel. No entanto, vale a pena, porque o poder de seus recursos no processamento de grandes quantidades de dados é difícil de superestimar.

Benefício 4: Hierarquias de campos personalizados

Muitas vezes, ao criar relatórios padrão, você precisa lançar as mesmas combinações de campos em tabelas dinâmicas em uma determinada sequência, por exemplo Ano-Trimestre-Mês-Diaou Categoria-Produtoou País-Cidade-Cliente etc. No resumo do modelo de dados, esse problema é facilmente resolvido criando seu próprio hierarquias — conjuntos de campos personalizados.

Na janela Power Pivot, alterne para o modo gráfico com o botão Visualização de gráfico aba Início (Início - Visualização de Diagrama), selecione com Ctrl campos desejados e clique com o botão direito sobre eles. O menu de contexto conterá o comando Criar hierarquia (Criar hierarquia):

A hierarquia criada pode ser renomeada e arrastada com o mouse para os campos obrigatórios, para que posteriormente em um movimento eles possam ser lançados no resumo:

Benefício 5: estênceis personalizados

Continuando a ideia do parágrafo anterior, no resumo do Modelo de Dados, você também pode criar seus próprios conjuntos de elementos para cada campo. Por exemplo, de toda a lista de cidades, você pode facilmente fazer um conjunto apenas daquelas que estão em sua área de responsabilidade. Ou colete apenas seus clientes, suas mercadorias, etc. em um conjunto especial.

Para isso, na aba Análise de tabela dinâmica na lista suspensa Campos, itens e conjuntos existem comandos correspondentes (Analisar - Fields, Items & Sets — Crie um conjunto baseado em itens de linha/coluna):

Na janela que se abre, você pode remover, adicionar ou alterar seletivamente a posição de qualquer elemento e salvar o conjunto resultante com um novo nome:

Todos os conjuntos criados serão exibidos no painel Campos da Tabela Dinâmica em uma pasta separada, de onde podem ser arrastados livremente para as áreas de linhas e colunas de qualquer nova Tabela Dinâmica:

Benefício 6: Ocultar seletivamente tabelas e colunas

Embora esta seja uma vantagem pequena, mas muito agradável em alguns casos. Ao clicar com o botão direito do mouse no nome do campo ou na guia da tabela na janela do Power Pivot, você pode selecionar o comando Ocultar do kit de ferramentas do cliente (Ocultar das Ferramentas do Cliente):

A coluna ou tabela oculta desaparecerá do painel Lista de Campos da Tabela Dinâmica. É muito conveniente se você precisar ocultar do usuário algumas colunas auxiliares (por exemplo, colunas calculadas ou com valores-chave para criar relacionamentos) ou até tabelas inteiras.

Benefício 7. Detalhamento avançado

Se você clicar duas vezes em qualquer célula na área de valor em uma tabela dinâmica normal, o Excel exibirá em uma planilha separada uma cópia do fragmento de dados de origem que foi envolvido no cálculo dessa célula. Isso é uma coisa muito útil, oficialmente chamada de Drill-down (geralmente dizem “falha”).

No resumo do Modelo de Dados, essa ferramenta útil funciona de forma mais sutil. Ao ficar em qualquer célula com o resultado que nos interessa, você pode clicar no ícone com uma lupa que aparece ao lado (chama-se Expressar tendências) e, em seguida, selecione qualquer campo de seu interesse em qualquer tabela relacionada:

Depois disso, o valor atual (Model = Explorer) irá para a área de filtro, e o resumo será construído por escritórios:

Obviamente, esse procedimento pode ser repetido muitas vezes, investigando consistentemente seus dados na direção em que você está interessado.

Benefício 8: Converter funções de pivô em cubo

Se você selecionar qualquer célula no resumo do Modelo de Dados e, em seguida, selecionar na guia Análise de tabela dinâmica Command Ferramentas OLAP – Converter em Fórmulas (Analisar — ​​Ferramentas OLAP — Converter em fórmulas), todo o resumo será convertido automaticamente em fórmulas. Agora os valores do campo na área linha-coluna e os resultados na área de valor serão recuperados do Modelo de Dados usando as funções especiais do cubo: CUBEVALUE e CUBEMEMBER:

Tecnicamente, isso significa que agora não estamos lidando com um resumo, mas sim com várias células com fórmulas, ou seja, podemos facilmente fazer qualquer transformação com nosso relatório que não esteja disponível no resumo, por exemplo, inserir novas linhas ou colunas no meio do relatório, fazer quaisquer cálculos adicionais dentro do resumo, organizá-los da maneira desejada, etc.

Ao mesmo tempo, a conexão com os dados de origem, é claro, permanece e, no futuro, essas fórmulas serão atualizadas quando as fontes mudarem. A beleza!

• Análise de fatos planejados em uma tabela dinâmica com Power Pivot e Power Query
• Tabela dinâmica com cabeçalho de várias linhas
• Criar um banco de dados no Excel usando o Power Pivot