2 Maneiras de calcular a Contagem Distinta com Tabelas Pivot
Bottom Line: Aprenda duas formas de resolver o desafio da análise de dados, calculando a contagem distinta, com tabelas pivot.
Nível de morte: Intermediário
Video Tutorial
Download do ficheiro Excel
Incluí tanto o ficheiro original como o ficheiro de solução para descarregar aqui:
Desafio de análise de dados.xlsx (16.1 KB)
Desafio de Análise de Dados – Solução de Tabela Pivot FINAL.xlsx (118.4 KB)
Contagem de Linhas Únicas
Neste post, vamos analisar duas formas diferentes de fazer uma contagem distinta usando tabelas pivot. Estes dois métodos foram apresentados como soluções para o desafio da análise de dados que pode encontrar aqui:
Excel Data Analysis Challenge
Para resumir o desafio, queremos criar um relatório sumário da contagem das transacções por fase, mas existem múltiplas linhas por transacção nos dados CRM. Portanto, temos de encontrar uma forma de criar uma contagem distinta (contando linhas únicas) para cada negócio, de modo a podermos resumi-las.
A propósito, obrigado a todos os que submeteram uma solução para o desafio dos dados! Houve muitas grandes submissões.
Solução #1 – Usando uma Coluna Ajudante
O que é bom nesta solução é que ela pode ser usada em qualquer versão do Excel.
Comece por transformar os seus dados numa Tabela Excel. Para o fazer, basta seleccionar qualquer célula do conjunto de dados, e clicar em Formatar como Tabela no separador Início. Clique com o botão direito do rato sobre o formato de tabela que pretende e seleccione Aplicar e Limpar Formatação.
Hit OK quando a janela Formatar como Tabela aparecer.
Agora que os seus dados estão em formato de Tabela, adicione uma coluna de ajuda à direita da tabela e rotule-a Deal Count. Use a função COUNTIF, sendo o intervalo a coluna Deal ID, e o critério a célula na coluna Deal ID que corresponde à linha em que se encontra.
A fórmula retornará o número de linhas para cada número de Deal ID. Se dividirmos a fórmula no número 1, obteremos fracções em cada uma dessas células que, quando adicionadas juntas, contarão uma entrada para cada negócio.
A mudança para a fórmula pode ser vista a verde aqui:
=1/COUNTIF(,])
Agora que temos estas fracções que nos darão uma contagem distinta quando criarmos a nossa tabela pivot, podemos avançar e criar a tabela pivot escolhendo Tabela Pivot no separador Inserir.
Para criar o nosso relatório de resumo utilizando a nova tabela pivot, colocar o Estágio de Vendas na área de Linhas e a Contagem de Vendas na área de Soma de Valores.
Isto dar-nos-á o relatório de resumo que procuramos, com uma contagem de negócios em cada fase de venda.
O bom de usar uma tabela pivot é que à medida que adicionamos ou apagamos entradas de dados de origem, podemos actualizar a tabela pivot ( Alt + F5 ) para incluir essas alterações.
Solução # 2 – Usando Power Pivot
Esta solução só está disponível para versões do Excel que são 2013 ou posteriores para Windows.
Ainda queremos os nossos dados formatados como Tabela Excel, mas não precisamos de uma coluna de ajuda para esta solução.
Desta vez, quando criarmos a nossa tabela pivot, vamos marcar a caixa que diz Adicionar esta tabela ao Modelo de Dados. (Modelo de Dados é outro termo para PowerPivot.)
Quando construir a sua tabela pivot desta vez, vai arrastar o Deal ID para a área da Soma de Valores.
Isso dá-nos inicialmente números que não queremos no nosso relatório sumário. Para corrigir isto, queremos clicar com o botão direito do rato no cabeçalho da coluna Sum of Deal ID e seleccionar Value Field Settings. Isto irá abrir uma janela onde podemos escolher Contagem Distinta como tipo de cálculo.
A função de Contagem Distinta passa pela coluna ID de Negociação e dá-nos uma contagem dos valores únicos, pelo que o nosso relatório de resumo terá o mesmo aspecto que teve para a Solução #1.
Comparando as Duas Soluções
ambas estas soluções são óptimas porque podem ser actualizadas quando novos dados são adicionados à tabela de origem.
A vantagem da Solução #1 é que pode ser feita em qualquer versão do Excel. Dito isto, se estiver a correr 2013 ou mais tarde no Windows, a Solução #2 é a opção superior. Isto é porque a Solução #1 fica viciada quando se tenta filtrar os dados para baixo (digamos, para um determinado produto) ou usar cortadores para dissecar mais os dados.
Se quiser saber mais sobre a utilização das Tabelas Pivot, tenho um post de blogue separado que pode consultar aqui: Introdução às Tabelas Pivot e Painéis.
Outras Soluções
Foram apresentadas muitas outras grandes soluções para o desafio. Incluíram a utilização do Power Query e novas funções dinâmicas. Vamos dar uma vista de olhos a estas em futuros posts, mas eu queria começar com estas duas porque eram mais universais em termos de acesso à versão Excel.