Neste artigo aprenderá como fazer médiases no Power Query passo-a-passo.
1. MédiaSes no Power Query?
Além das somas condicionais (somas com critérios), comumente realizadas com as funções de planilhas SOMASE e SOMASES e das contagens condicionais (contagens com critérios), comumente realizadas com as funções de planilhas CONT.SE e CONT.SES, temos o cálculo dos valores médias condicionais (média com critérios), comumente realizado com a função de planilhas MÉDIASES.
O Power Query oferece opções de agrupamento (agregação) de dados caso seja necessário esse tipo de cálculo. Desta forma, podemos calcular o valor médio de acordo com 1 ou mais critérios (condicionalmente), de modo bem semelhante ao que fazemos frequentemente com a função de planilhas MÉDIASES.
Dado o exposto, este artigo objetiva apresentar os passos para calcularmos a média condicional no Power Query através do agrupamento de dados.
2. O agrupamento no Power Query
O recurso de Agrupar por é o objeto deste artigo. Ele é acessível de pelo menos 2 formas:
– Através do botão secundário no título de pelo menos uma coluna:
– Através da guia Página Inicial no grupo Transformar:
Dado o exposto, partiremos agora para demonstrações com os dados.
3. Calculando as médias com critérios
Tomaremos como base os dados amostrados a seguir, devidamente já carregados no Power Query, para demonstrar os cálculos.
Em nossos exemplos a coluna que terá o valor médio calculado será Valor, posicionada ao fim da tabela.
3.1 Com 1 critério (condição)
Desejamos calcular o valor médio vendido por cada Regional (1ª coluna dos dados). Como cada registro apresenta uma regional e um valor, precisamos calcular a média destes.
Clicamos com o botão secundário no cabeçalho da coluna desejada e acessamos a opção Agrupar por.
Na janela que será apresentada, definimos o nome da coluna a ser obtida apresentando a média de valores (nomeada como Média de Vendas na imagem seguinte), em Operação, escolhemos Média, dentre os vários tipos de agregação, além de escolher a coluna que possui valores dos quais o valor médio será calculado (Valor, em nosso caso).
Na imagem anterior a seta destaca a coluna que será resumida (aquela em que no início de todo o processo clicamos com o botão secundário em seu cabeçalho). Podemos trocar, escolhendo uma coluna diferente da selecionada.
Após confirmar em Ok a tabela de resumo será apresentada, exibindo o valor médio vendido por cada regional.
3.2 Com 2 ou mais critérios (condições)
A diferença em relação ao processo descrito no item anterior são os múltiplos critérios. Para o resumo de dados com múltiplos critérios basta selecionar as colunas desejadas e escolher uma delas para clicar com o botão secundário em seu cabeçalho e acessar a opção Agrupar por.
Desejamos desta vez calcular o valor médio das vendas de cada Categoria para cada Pagamento. Por isso, selecionamos ambas as colunas e acessamos a opção para agrupar.
A janela de agrupamento será exibida.
Note que a tratativa agora é apontada como avançada, estando as 2 colunas que selecionamos em destaque. Podemos editar à vontade quais e quantas colunas desejamos usar para resumir (explore o botão de Adicionar agrupamento), bem como a ordem em que elas serão apresentadas no resultado.
Da mesma forma que realizamos no exemplo anterior, definimos o nome da coluna a ser criada com a média de valores, escolhemos a operação Média e escolhemos a coluna a ser usada para a obtenção dos valores médios.
Ao confirmar as escolhas e definições, teremos o resultado, que resume e estratifica para cada Categoria e Pagamento os valores médios associados.
Com isso, são exibidas todas as combinações existentes e sem repetições entre categorias e pagamentos com os seus valores médios.
Pode ser conveniente melhorar a visualização com a classificação dos dados, exibindo, por exemplo, as Categorias em ordem alfabética, seguidas por cada uma das opções de Pagamento associadas.
Note na imagem anterior que a Categoria Carnes é exibida de forma agrupada, permitindo facilmente visualizar cada uma das suas formas de pagamento sem maiores dificuldades. Isso ocorre para todas as demais Categorias.
Bônus – simulando tabela dinâmica
O modo como os dados estão dispostos pode não ser o ideal e o desejado. Pode ser mais interessante para a visualização dos dados a disposição em matriz. Com isso, por exemplo, podemos ter para cada Categoria uma coluna com os valores médios de cada Pagamento, algo bem similar ao layout básico que exploramos ao usar tabelas dinâmicas. A boa notícia é que a transformação desejada é simples de se executar.
Partindo do resultado anterior apresentado, selecionamos a coluna Pagamento.
Esta seleção corresponde à base, que será usada para gerar novas colunas. Cada nova coluna será um valor existente na coluna selecionada, ou seja: cada diferente pagamento existente será o rótulo de uma nova coluna a ser criada. Com isso, teremos uma coluna nova para: Crédito, Pix, Débito e Dinheiro.
Após a seleção, acessamos a opção Coluna Dinâmica, na guia Transformar.
Será apresentada uma simples janela em que devemos escolher a coluna de valores (Média de Vendas, em nosso caso).
Ao confirmar em Ok já teremos o resultado desejado.
Download Planilha Médiases Power Query
Realize o download da planilha de cálculo de idade no Excel neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
