Esta pergunta surge uma vez a cada década: você pode fazer uma mediana em uma tabela dinâmica. Tradicionalmente, a resposta era Não. Lembro-me em 2000, quando contratei o MVP do Excel Juan Pablo Gonzalez para escrever uma macro incrível que criava relatórios que pareciam tabelas dinâmicas, mas tinham medianas.
Então, quando Alex em meu seminário Houston Power Excel perguntou sobre a criação de medianas, eu rapidamente disse "Não". Mas então … Eu me perguntei se o DAX tinha uma função mediana. Uma rápida pesquisa no Google e sim! Existe uma função DAX para Median.
O que é necessário para usar o DAX em uma tabela dinâmica? Você precisa da versão Windows do Excel executando o Excel 2013 ou mais recente.
Aqui está meu conjunto de dados muito simples que usarei para testar como as medianas da tabela dinâmica são calculadas. Você pode ver que a mediana para Chicago deve ser 5.000 e a mediana para Cleveland deve ser 17.000. No caso de Chicago, há cinco valores, então a mediana será o terceiro maior valor. Mas, para todo o conjunto de dados, existem 12 valores. Isso significa que a mediana está em algum lugar entre 11.000 e 13.000. O Excel calcula a média desses dois valores para retornar 12.000.
Para começar, selecione uma célula em seus dados e pressione Ctrl + T para formatar os dados como uma tabela.
Em seguida, escolha Inserir, Tabela Dinâmica. Na caixa de diálogo Inserir Tabela Dinâmica, escolha a caixa para Adicionar esses dados ao modelo de dados.
Nos Campos da Tabela Dinâmica, escolha Região e Distrito. Mas não escolha Vendas. Em vez disso, clique com o botão direito do mouse no título da Tabela e escolha Nova Medida. "Medida" é um nome sofisticado para um campo calculado. As medidas são mais poderosas do que os campos calculados em tabelas dinâmicas regulares.
Na caixa de diálogo Definir medida, preencha as quatro entradas mostradas abaixo:
- Nome da medida: mediana de vendas
- Fórmula
=MEDIAN((Sales)) - Formato numérico: Número
- Casas decimais: 0
Depois de criar a medida, ela é adicionada à lista de campos, mas você deve escolher a entrada para adicioná-la à área Valores da tabela dinâmica. Como você pode ver abaixo, a tabela dinâmica está calculando corretamente as medianas.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2197: Mediana em uma Tabela Dinâmica.
Devo dizer que estou extremamente animado com isso. quando eu estava em Houston dando um seminário Power Excel lá, e Alex levantou a mão e disse: "Ei, há alguma maneira de fazer uma mediana em uma tabela dinâmica?" Não, você não pode fazer uma mediana em uma tabela dinâmica. Lembro-me de 25 anos atrás, meu bom amigo Juan Pablo Gonzales, na verdade trouxe uma macro para fazer o equivalente à mediana, sem usar uma tabela dinâmica - simplesmente não é possível.
Mas eu tive uma faísca. Eu disse, "Espere um segundo", abro um navegador e procuro por "DAX median" e, com certeza, há uma função MEDIAN no DAX, o que significa que podemos resolver esse problema.
Tudo bem, então, para usar o DAX, você precisa estar no Excel 2013 ou Excel 2016, ou no Excel 2010, e ter o suplemento Power Pivot; você não precisa ter a guia Power Pivot, só precisamos ter o Modelo de Dados. Tudo bem? Então, vou escolher esses dados, vou transformá-los em uma tabela com Ctrl + T, e eles dão a eles um nome nada criativo de "Tabela 4". No seu caso, pode ser a "Tabela 1". E vamos inserir uma Tabela Dinâmica, adicionar esses dados ao Modelo de Dados, clicar em OK e vamos escolher Regional no lado esquerdo, mas não Vendas. Em vez disso, quero criar uma nova medição calculada. Então, eu venho aqui para a mesa e clico com o botão direito e digo, Adicionar Medida. E essa medida vai se chamar "Mediana de Vendas", e a fórmula vai ser: = MEDIANA. Pressione a guia lá e escolha Vendas,fechando parênteses. Posso escolher isso como um número com zero casas decimais, usar um separador de milhar e clicar em OK. E, vamos ver, nosso novo campo é adicionado aqui, temos que marcá-lo para adicioná-lo e diz que a mediana para o meio-oeste é 12.000.
Agora vamos verificar isso. Então, aqui, todo o meio-oeste, a mediana de todos os conjuntos de dados entre 11.000 e 13.000. Portanto, tem uma média de 11 e 13, que é exatamente o que a mediana faria no Excel normal. Agora vamos adicionar o distrito e diz que a mediana de 5.000 de Chicago, a mediana de Cleveland é de 17.000; Centro-oeste total e total geral 12.000. Tudo isso está correto. Quão incrível é isso? Por fim, mediana em uma tabela dinâmica, graças a um campo calculado, ou medida, como é chamado, e ao Modelo de Dados.
Agora, muitas das minhas dicas favoritas - as dicas para meu seminário ao vivo sobre Power Excel - neste livro LIVe, as 54 melhores dicas de todos os tempos. Clique no "I" no canto superior direito para ler mais sobre o livro.
Tudo bem. Conclusão: você pode fazer uma mediana em uma tabela dinâmica? Oh não, sim, sim, você pode, graças ao Modelo de Dados. Você pode criar uma mediana; Ctrl + T para transformar seus dados em uma tabela; Inserir Tabela Dinâmica; e marque essa caixa, Adicionar esses dados ao modelo de dados; clique com o botão direito no nome da tabela e escolha a nova medida, e a medida será = MEDIAN ((Vendas)). É incrivel.
Agradeço a Alex por aparecer no meu seminário e fazer essa pergunta, obrigado pela visita. Vejo você na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: pivot-table-median.xlsx
