Conteúdo
Slicers em tabelas dinâmicas podem ser usados não apenas da maneira clássica – para filtrar os dados de origem, mas também para alternar entre diferentes tipos de cálculos na área de valor:
Implementar isso é muito fácil - tudo que você precisa é de algumas fórmulas e uma tabela auxiliar. Bem, faremos tudo isso não no resumo usual, mas no resumo construído de acordo com o Modelo de Dados do Power Pivot.
Etapa 1. Conectando o suplemento Power Pivot
Se as guias do suplemento Power Pivot não estiverem visíveis em seu Excel, primeiro você precisará habilitá-lo. Existem duas opções para isso:
- Aba revelador - botão suplementos COM (Desenvolvedor — Suplementos COM)
- Arquivo – Opções – Suplementos – Suplementos COM – Ir (Arquivo — Opções — Suplementos — Suplementos COM — Ir para)
Se isso não ajudar, tente reiniciar o Microsoft Excel.
Etapa 2: carregar dados no modelo de dados do Power Pivot
Teremos duas tabelas como dados iniciais:
A primeira é uma tabela com vendas, segundo a qual construiremos posteriormente um resumo. A segunda é uma tabela auxiliar, onde são inseridos os nomes dos botões da fatia futura.
Ambas as tabelas precisam ser convertidas para “inteligentes” (dinâmicas) com um atalho de teclado Ctrl+T ou equipe Home – Formatar como uma tabela (Início — Formatar como Tabela) e é desejável dar-lhes nomes sensatos na guia Construtor (Desenhar). Seja, por exemplo, Vendas и Serviços.
Depois disso, cada tabela, por sua vez, precisa ser carregada no Modelo de Dados – para isso usamos na guia pivô botão Adicionar ao modelo de dados (Adicionar ao modelo de dados).
Os campos calculados em uma tabela dinâmica por modelo de dados são chamados medidas. Vamos criar uma medida que exibirá o nome do botão pressionado na fatia futura. Para fazer isso, em qualquer uma de nossas tabelas, selecione qualquer célula vazia no painel de cálculo inferior e insira a seguinte construção na barra de fórmulas:
Aqui, o nome da medida vem primeiro (Botão pressionado), e depois de dois pontos e um sinal de igual, uma fórmula para calculá-lo usando a função VALORES DAX integrado ao Power Pivot.
Se você repetir isso não no Power Pivot, mas no Power BI, os dois pontos não serão necessários e, em vez disso, VALORES você pode usar sua contraparte mais moderna - a função VALOR SELECIONADO.
Não prestamos atenção aos erros na parte inferior da janela que aparecem após inserir a fórmula – eles surgem, porque ainda não temos um resumo e uma fatia em que algo é clicado.
O próximo passo é criar uma medida para diferentes opções de cálculo dependendo do valor da medida anterior Botão pressionado. Aqui a fórmula é um pouco mais complicada:
Vamos dividir por partes:
- função BOTÃO – um análogo de IF aninhado – verifica o cumprimento das condições especificadas e retorna valores diferentes dependendo do cumprimento de algumas delas.
- função VERDADE() – fornece um “verdadeiro” lógico para que as condições verificadas posteriormente pela função SWITCH funcionem apenas se forem atendidas, ou seja, verdadeiras.
- Em seguida, verificamos o valor da medida do botão pressionado e calculamos o resultado final para três opções diferentes – como a soma do custo, o cheque médio e o número de usuários únicos. Para contar valores únicos, use a função DISTINCTCOUNT, e para arredondamento – ROUND.
- Se nenhuma das três condições acima for atendida, o último argumento da função SWITCH será exibido - nós a configuramos como fictícia usando a função EM BRANCO().
Etapa 5. Construindo um resumo e adicionando uma fatia
Resta retornar do Power Pivot para o Excel e criar uma tabela dinâmica para todos os nossos dados e medidas. Para fazer isso, na janela Power Pivot em O principal comando de seleção de guia tabela de resumo (Início - Tabela Dinâmica).
Então:
- Nós jogamos o campo Produto da mesa Vendas para a área Linhas (Linhas).
- Jogando um campo lá Resultado da mesa Serviços.
- Clique com o botão direito no campo Resultadoe escolha uma equipe Adicionar como fatia (Adicionar como fatiador).
- Jogando a segunda medida Conclusão da mesa Serviços para a área Valores (Valores).
Aqui, de fato, estão todos os truques. Agora você pode clicar nos botões de segmentação – e os totais na tabela dinâmica mudarão para a função que você precisa.
Beleza 🙂
- Benefícios do pivô por modelo de dados
- Análise de fatos planejados em uma tabela dinâmica no Power Pivot
- Criar um banco de dados no Excel usando o suplemento Power Pivot