Aprenda como usar as funções Máximo SE e Mínimo com SE, ou seja Máximo e Mínimo com Condições.
É comum a necessidade de realizarmos somas e médias condicionais, mas também há várias outras situações que precisamos restringir os dados para aplicar a função do Excel.
Em versões recentes do Excel temos as funções Máximoses e Mínimoses que permitem condicionar o intervalo que será usado como retorno.
Mas como fazer em versões anteriores do Excel?
Funções Excel MáximoSes e MínimoSes
As funções Máximoses e Mínimoses estão disponíveis à partir da versão 2019 e 365 do Excel.
Estas funções tem por finalidade que sejam realizados retornos de máximo e mínimo valores conforme condições.
Por exemplo, você consultar a maior venda de um vendedor em determinado mês.
Para utilizar estas funções observe o exemplo abaixo.
Temos os dados das vendas acima e desejamos realizar consultas de valores máximo e mínimo no excel conforme filtros especificados na sessão abaixo.
Iremos realizar os cálculos de máximo e mínimo para uma determinada data e região, mas poderia adicionar mais condições caso desejasse.
Usamos então a função máximoses conforme abaixo.
=MÁXIMOSES(intervalo_máximo;intervalo_critérios1;critérios1)
- intervalo_máximo : intervalo de valores que iremos consultar o valor máximo.
- intervalo_critérios1 : intervalo de critérios, por exemplo, a região de venda.
- critérios1 : critério de intervalo, por exemplo a região.
Exemplo com nossos dados:
=MÁXIMOSES($E$10:$E$361;$C$10:$C$361;I4)
Como resultado temos então o seguinte valor.
Conforme o critério é alterado ou os dados das colunas o valor do retorno da função é igualmente alterado.
A função MínimoSes funciona de forma análoga a função MáximoSes, mas retorna o valor mínimo à partir de uma lista de valores.
Veja a fórmula que foi passada:
=MÍNIMOSES(intervalo_máximo;intervalo_critérios1;critérios1)
- intervalo_máximo : intervalo de valores que iremos consultar o valor mínimo.
- intervalo_critérios1 : intervalo de critérios, por exemplo, a região de venda.
- critérios1 : critério de intervalo, por exemplo a região.
Exemplo com nossos dados:
=MÍNIMOSES($E$10:$E$361;$C$10:$C$361;I4)
Caso queira adicionar novas condições nas funções máximoses e mínimoses basta adicionar ponto-e-vírgula e passar um novo intervalo e ponto-e-vírgula uma nova condição.
Máximo SE com Função Agregar
Uma opção ao uso da função MáximoSes e Mínimoses é a função Agregar.
Ela já está presente no Excel desde a versão 2010 e portanto a maioria dos computadores já utiliza esta versão ou superior.
A função Agregar permite que sejam realizadas 19 operações diferentes com condições aplicadas ao intervalo.
A função também possui um parâmetro com opções que permite filtrar células com erros, ocultas, agregadas ou de subtotal.
Para realizar o máximoses no Excel 2010 ou superior usando a função Agregar faça conforme abaixo.
Na função Agregar nós temos uma série de opções de soma, iremos usar a opção 14, que é a da função MAIOR.
=AGREGAR(núm_função;opções;matriz;[k])
- núm_função : número da função que será aplicada, para o MAIOR ou MÁXIMO, use a opção 14.
- opções : esta opção permite que consideramos no intervalo completo, desconsiderar erros, ocultas, agregadas ou subtotal.
- matriz : aqui temos o intervalo de dados que será considerado para o cálculo de Máximo condicional ou Maior condicional no Excel.
- [k] : Número do item na lista no caso da função MAIOR, colocando 1, terá o valor MÁXIMO da lista ou primeira posição.
Acima temos o exemplo das opções que podem ser passadas para a função Agregar.
No exemplo nós passamos a fórmula conforme abaixo:
=AGREGAR(14;4;($C$10:$C$361=I4)*($E$10:$E$361);1)
No exemplo nós temos o maior valor considerando apenas a data do mês de abril.
Para adicionar o filtro de dados no parâmetro Matriz, você deve seguir com operadores =><>>= ou <= sempre entre parênteses.
Coloque quantas condições houverem e faça a união das condições usando os operadores *(E) +(OU) e por último passe o intervalo de valores.
Isto fará com que você tenha no intervalo ($C$10:$C$361=I4) o retorno: {FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;…)
Ou seja, você terá 0=FALSO e 1=VERDADEIRO.
Teremos então (0;0;0;0;1…)*(9960;9922;9812;9526;9467…), ou seja, teremos então o retorno apenas dos valores que atenderem os parâmetros.
Então temos o filtro aplicado nos valores, caso queiramos colocar muitos, basta adicionar novas condições basta você adicionar no parâmetro de matriz novas condições passando *.
=AGREGAR(14;4;($C$10:$C$361=I4)*(D10:D361=I5)*($E$10:$E$361);1)
Acima temos as condições em azul e a matriz de valore sem vermelho.
Veja também que o último parâmetro [k] é 1, pois queremos que retorne o primeiro maior valor, caso quiséssemos outros valores bastaria alterar este parâmetro.
Mínimo SE com Função Agregar
Assim como a função MáximoSes, nós temos opção ao uso da função MínimoSes utilizando a função Agregar.
Para isso passamos o parâmetro de núm_função igual a 15, é a opção MENOR da função AGREGAR.
Mas há uma diferença entre o uso da opção MAIOR e MENOR com relação as condições aplicadas na matriz.
Veja no exemplo acima, aonde aplicamos os filtros de forma muito parecida.
Mas nela nós temos a passagem de outros parâmetros diferentes.
=AGREGAR(15;3;1/($C$10:$C$361=I4)*1/(D10:D361=I5)*($E$10:$E$361);1)
Nela nós temos os dados desta forma:
{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;…) e isso é proposital.
É proposital, porque se colocarmos diretamente sem dividir por 0, o menor valor será sempre 0 caso não tenhamos valores negativos na lista.
isso acontece por conta do retorno 0 quando é falso. 0*9950 = 0.
Dividimos o valor que é verificando, que retorna como visto na opção MAIOR, que retorna {0;0;0;0;0;1… }
Ou seja, temos então {0/0;0/0;0/0;0/0;1/0…}, e qualquer número dividido por 0 gera o erro de #DIV/0! no Excel.
Por isso usamos a o 1/ para gerar os erros.
Assim, passamos como segundo parâmetro a opção 3, pois este parâmetro é: Ignorar linhas ocultas, valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas.
Como resultado temos então o valor que atende a todas as condições.
Lembrando que para adicionar condições basta colocar entre parênteses novas condições e unir as condições por * para E e + para OU.
Se quiser também pode retornar outras posições da lista, o segundo maior valor, terceiro maior, quarto, etc.
Basta alterar o último parâmetro [k].
Download da Planilha Exemplo Máximo SE
Realize o download da planilha de exemplo Máximo Se no botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
