Maior e Menor Valor COM CRITÉRIO no Excel [ funções SES ] e no Power Query

Aprenda como encontrar o maior e menor valor em um conjunto de dados com critério no Excel usando as funções MÁXIMOSES e MÍNIMOSES.
Além disso, vamos explorar as funções CONT.SES e SOMASES para contagem e soma com critérios específicos.

E no final, uma alusão à simplicidade do Power Query, onde basta usar o agrupamento de dados.

Preparado? Vamos lá!

Encontrando o Maior Valor com Critério: MÁXIMOSES

A função MÁXIMOSES retorna o maior valor em um intervalo que atende a um ou mais critérios.

Vamos imaginar que você tem uma planilha com vendas de diferentes produtos em várias regiões. E você quer saber qual foi a maior venda na região “Sul”.

Dada a tabela:

Agora vamos escolher alguma outra célula e iniciar nossos estudos, digitando a função a seguir:

-- como funciona a função
=MÁXIMOSES(intervalo_máximo, intervalo_critérios, critérios)

-- aplicação da função
=MÁXIMOSES(E2:E100, A2:A100, 1)

Aqui, E2:E100 é o intervalo onde estão os valores das vendas (coluna “ValorPago_R$”), A2:A100 é o intervalo onde estão os IDs dos produtos (coluna “ProdutoID”) e “1” é o critério.

Complemento: Filtrando por Mais de Um Critério

E se além do ProdutoID, você quiser filtrar também pelo PrecoID? Sem problemas! Você pode adicionar mais critérios à função MÁXIMOSES. Vamos ver como encontrar a maior venda do ProdutoID 1 e PrecoID 2.

=MÁXIMOSES(intervalo_máximo, intervalo_critérios1, critérios1, intervalo_critérios2, critérios2)
=MÁXIMOSES(E2:E100, A2:A100, 1, B2:B100, 2)

Aqui, além do intervalo e critério do ProdutoID (A2:A100, 1), adicionamos o intervalo e critério do PrecoID (B2:B100, 2). Assim, a função retorna a maior venda que atende a ambos os critérios.

Encontrando o Menor Valor, a SOMA, e a Contagem

Da mesma forma que o MÁXIMOSES, você ainda tem o MÍNIMOSES para retornar o menor valor do contexto.
Tem o CONT.SES para retornar a quantidade de linhas que estão naquele argumento, bem como o tradicional SOMASES que soma os valores.

Antigamente usávamos o SOMASE (sem o S no final) que cabia somente um argumento, e era comum, criarmos uma coluna de dados CONCATENADOS para poder fazer a aplicação da função com mais de um critério.

Filtrando com Data Maior que X e Menor que Y: SOMASES (por exemplo)

Escolhi aqui a função SOMASES, mas você pode escolher alguma outra agrupadora do grupo “SES”.
Nessa função você pode somar valores em um intervalo que atendem a um ou mais critérios.
Além de critérios simples como IDs de produtos, você também pode usar critérios baseados em datas.

Exemplo

Suponha que você quer somar o valor das vendas que ocorreram entre as datas 01/01/2021 e 31/12/2021.

Primeiro, certifique-se de que suas datas estão formatadas corretamente como datas no Excel. Em seguida, você pode usar a função SOMASES para definir critérios de data.

=SOMASES(intervalo_soma, intervalo_critérios1, critérios1, intervalo_critérios2, critérios2)
=SOMASES(E2:E100, D2:D100, ">=01/01/2021", D2:D100, "<=31/12/2021")

Aqui, E2:E100 é o intervalo onde estão os valores das vendas (coluna “ValorPago_R$”), D2:D100 é o intervalo onde estão as datas das vendas (coluna “DATA”), ">=01/01/2021" é o critério para a data inicial, e "< =31/12/2021" é o critério para a data final.

Dessa forma, a função SOMASES soma todos os valores das vendas que ocorreram entre 01/01/2021 e 31/12/2021.

Combinação com Outros Critérios

E se você quiser somar os valores das vendas de um produto específico nesse mesmo período? Vamos adicionar mais um critério ao exemplo.

=SOMASES(intervalo_soma, intervalo_critérios1, critérios1, intervalo_critérios2, critérios2, intervalo_critérios3, critérios3)
=SOMASES(E2:E100, D2:D100, ">=01/01/2021", D2:D100, "<=31/12/2021", A2:A100, 1)

Aqui, além dos critérios de data, adicionamos A2:A100 como o intervalo para os IDs dos produtos e “1” como o critério para o ProdutoID. Dessa forma, a função SOMASES soma os valores das vendas do ProdutoID 1 que ocorreram entre 01/01/2021 e 31/12/2021.

Simples assim, FERA! Você agora pode aplicar filtros de data junto com outros critérios para obter exatamente as informações que precisa.

Matriz Dinâmica e Resolvendo “MEDIANASES AINDA NÃO EXISTE”

Agora temos que falar sobre a poderosa função de Matriz Dinâmica no Excel.

E também vamos resolver um problema comum: a função MEDIANASES ainda não existe, mas com um truque esperto, a gente consegue calcular a mediana com critérios. Vamos lá!

Matriz Dinâmica no Excel

As Matrizes Dinâmicas no Excel permitem que você trabalhe com dados que se ajustam automaticamente quando novos valores são adicionados ou removidos. Uma das funções mais úteis para criar Matrizes Dinâmicas é a função FILTRO.

A função FILTRO permite que você extraia dados que atendem a um determinado critério e a use como uma matriz dinâmica.

Resolvendo o Problema da MEDIANASES

Infelizmente, o Excel ainda não possui uma função MEDIANASES embutida. Mas podemos contornar isso usando a função FILTRO combinada com a função MED. Vamos calcular a mediana dos valores pagos (coluna “ValorPago_R$”) para um ProdutoID específico.

Exemplo

Vamos encontrar a mediana dos valores pagos para o ProdutoID 1.

=MED(FILTRO(E2:E100, (A2:A100=1)))

Aqui, E2:E100 é o intervalo onde estão os valores das vendas (coluna “ValorPago_R$”) e A2:A100 é o intervalo onde estão os IDs dos produtos (coluna “ProdutoID”). A função FILTRO cria uma matriz dinâmica que contém apenas os valores pagos para o ProdutoID 1, e a função MED calcula a mediana desses valores.

Combinação com Outros Critérios

E se você quiser calcular a mediana para um ProdutoID e um PrecoID específicos? Sem problemas! Vamos adicionar mais um critério ao exemplo.

=MED(FILTRO(E2:E100, (A2:A100=1)*(B2:B100=2)))

Aqui, além do critério do ProdutoID (A2:A100=1), adicionamos o critério do PrecoID (B2:B100=2). A função FILTRO cria uma matriz dinâmica que contém apenas os valores pagos que atendem a ambos os critérios, e a função MED calcula a mediana desses valores.

Um Caminho Mais Simples: Usando o Mouse no Power Query

Se decorar todas essas funções do Excel está te dando um nó na cabeça, saiba que há um caminho mais simples e eficiente. Estou falando do uso do Power Query dentro do Excel (e também no Power BI).

Com o Power Query, você pode fazer transformações de dados complexas usando apenas o mouse, sem precisar decorar fórmulas complicadas.

Power Query: Transformações de Dados com Facilidade

O Power Query é uma ferramenta poderosa para importar, transformar e limpar dados. E o melhor de tudo é que você pode fazer isso com cliques do mouse. Vamos ver um exemplo prático de como usar o Power Query para agrupar e calcular vários valores estatísticos.

Se ainda não o conhece, CLIQUE AQUI para ler o artigo que te apresento o poder da ferramenta.

Exemplo Prático

Imagine que você tem uma tabela de vendas e quer calcular a quantidade de vendas, valor mínimo, valor máximo, valor médio, valor mediano e valor moda por ProdutoID e trimestre.

Passo a Passo

Importe sua tabela para o Power Query, e aqui no nosso exemplo vou usar o do Excel, mas você pode usar o do Power BI.

Carregando os dados para a ferramenta, vamos criar uma coluna para agrupar os dados, que nesse exemplo será o trimestre.
Selecione a coluna contendo, as datas, e depois inclua a informação que deseja:

Agora selecione as colunas que você deseja agrupar:

Agora só escolher os métodos de agrupamento, como mostro na imagem a seguir:

Só clicar em OK E pronto!
Tudo com o toque do mouse!
Depois só carregar para o Excel novamente.

Material de Apoio

Fala, FERA! Se você quiser acompanhar todos os exemplos que mostramos aqui, temos um material de apoio completo para você. Basta CLICAR AQUI e solicitar o material de apoio.

Você vai receber o link para uma pasta com meus materiais no Youtube, e basta você identificar o arquivo 20240620_MedianaSES.xlsx. Nesse arquivo, você vai encontrar todos os dados que usamos nos exemplos deste artigo.

Aula em Vídeo Completa

E tem mais! Este artigo conta com uma aula em vídeo completa, mostrando clique por clique como realizar cada uma das operações descritas aqui.

Nessa aula, você vai encontrar uma explicação detalhada de cada passo, desde a importação dos dados até a criação de cálculos avançados com Power Query.

Não perca essa oportunidade de dominar essas ferramentas incríveis e simplificar ainda mais seu trabalho com dados. Acesse agora e transforme seu jeito de trabalhar com análises e relatórios!
E se você quer se aprofundar ainda mais, conheça a nossa Formação Power Query 2.0. Com ela, você vai levar suas habilidades de análise de dados para o próximo nível.

Conclusão

Aprender a usar funções como MÁXIMOSES, MÍNIMOSES, CONT.SES e SOMASES no Excel é essencial para qualquer analista de dados, permitindo análises avançadas de forma eficiente e precisa.
Além disso, compreender o uso de matrizes dinâmicas e a função FILTRO para calcular medianas com critérios adiciona ainda mais flexibilidade às suas análises.

Para aqueles que desejam simplificar ainda mais o processo, o Power Query oferece uma maneira intuitiva de realizar transformações complexas de dados apenas com cliques do mouse. Essa ferramenta poderosa permite que você agrupe, filtre e calcule estatísticas sem precisar decorar fórmulas complicadas, tornando o trabalho com dados muito mais rápido e fácil.

Com esses conhecimentos, você estará bem equipado para transformar suas habilidades em análise de dados, tornando suas tarefas mais eficientes e precisas.

Por hoje é isso FERA!
Até a próxima