Máximo Se (Valor Máximo com Condição) – Excel e Google Sheets

Este tutorial demonstrará como encontrar o maior valor que atende a condições específicas no Excel e no Google Sheets.

Função MÁXIMOSES

Os usuários do Google Sheets e do Excel 2019 ou posterior podem usar a única função MÁXIMOSES.

Este exemplo usa as funções MÁXIMOSES e DATA para mostrar o maior Tamanho de Pedido de cada Nome de Loja para Datas de Pedido anteriores a 30/04/2021:

=MÁXIMOSES(D3:D8;B3:B8;"A";C3:C8;"<"&DATA(2021;4;30))

A função MÁXIMOSES não exige que o usuário pressione CTRL + SHIFT + ENTER ao inserir a fórmula.

Função Máximo Se em Matriz

Se você não tiver acesso à função MÁXIMOSES, poderá criar uma fórmula de matriz para simular a função MÁXIMOSES. Veremos isso a seguir.

A função MÁXIMO identifica o maior valor em uma série de números.

=MÁXIMO(B2:B11)

Podemos usar a função MÁXIMO combinada com uma função SE para identificar o maior valor que atende a uma condição especificada.

Este exemplo usará as funções MÁXIMO e SE em uma fórmula de matriz para identificar o maior Tamanho de Pedido para cada Nome de Loja.

{=MÁXIMO(SE(B3:B8="A";D3:D8))}

No Office 365 e nas versões do Excel posteriores a 2019, você pode simplesmente inserir a fórmula acima como faria normalmente (pressionando ENTER).

No entanto, no Excel 2019 e versões anteriores, você deve inserir a fórmula pressionando CTRL + SHIFT + ENTER. Depois de fazer isso, você notará que colchetes curvos (chaves) aparecem ao redor da fórmula.

Para mostrar como essa fórmula funciona, vamos dividi-la em etapas.

Esta é a nossa fórmula final (mostrada sem os colchetes de fórmula de matriz adicionados automaticamente):

=MÁXIMO(SE(B3:B8="A";D3:D8))

Primeiro, os valores do intervalo de células são adicionados à fórmula como matrizes:

=MÁXIMO(SE({"A"; "B"; "A"; "B"; "A"; "B"}="A";{500; 400; 300; 700; 600; 200}))

Em seguida, a condição Nome da Loja = “A” produz uma matriz de valores TRUE/FALSE:

=MÁXIMO(SE({VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO};{500;400;300;700;600;200}))

Em seguida, a função SE altera todos os valores VERDADEIRO para o Tamanho do Pedido correspondente:

=MÁXIMO({500; FALSO; 300; FALSO; 600; FALSO})

A função MÁXIMO identifica o maior número na matriz, ignorando os valores FALSO, para mostrar o maior Tamanho de Pedido para o Nome da Loja = “A”:

=600

Máximo Se – Vários Critérios

Também podemos identificar o maior valor com base em vários critérios usando a lógica booleana.

Este exemplo mostrará o maior Tamanho de Pedido para cada Nome de Loja, exceto para Datas de Pedido anteriores a 30/04/2021, usando as funções MÁXIMO, SE e DATA:

{=MÁXIMO(SE((B3:B8="A")*(C3:C8<DATA(2021;4;30));D3:D8))}

Observe que aqui multiplicamos dois conjuntos de critérios VERDADEIRO/FALSO:

(B3:B8="A")*(C3:C8<DATA(2021;4;30))

Se ambos os critérios forem VERDADEIROS, a condição total será calculada como VERDADEIRA, mas se um (ou mais) critérios forem FALSOS, ela será calculada como FALSA.

Usando esse método, é possível adicionar muitos critérios diferentes nessa fórmula.

Máximo Se – Vários Critérios com Referências de Células

Normalmente, não é uma boa prática codificar valores em fórmulas. Em vez disso, é mais flexível usar células separadas para definir os critérios.

Para fazer a correspondência entre o Nome da Loja e o valor mostrado na coluna F, podemos atualizar a fórmula para que seja:

{=MÁXIMO(SE((B3:B8=F3)*(C3:C8<DATA(2021;4;30));D3:D8))}

Bloqueio de referências de células

Para facilitar a leitura de nossas fórmulas, mostramos as fórmulas sem referências de células bloqueadas:

{=MÁXIMO(SE((B3:B8=F3)*(C3:C8<DATA(2021;4;30));D3:D8))}

Mas essas fórmulas não funcionarão corretamente quando forem copiadas e coladas em outro local do arquivo. Em vez disso, você deve usar referências de células bloqueadas como esta:

{=MÁXIMO(SE(($B$3:$B$8=F3)*($C$3:$C$8<DATA(2021;4;30));$D$3:$D$8))}

Leia nosso artigo sobre bloqueio de referências de células para saber mais.

 

Máximo Se (Valor Máximo com Condição) no Google Sheets

Os exemplos mostrados acima funcionam exatamente da mesma forma no Planilhas Google e no Excel, mas como a função MÁXIMOSES está disponível, é recomendável usar essa única função em vez de combinar as funções MÁXIMO e SE.

Se você quiser usar a fórmula de matriz MÁXIMO/SE, insira a fórmula na função ARRAYFORMULA (ou simplesmente use CTRL + SHIFT + ENTER e o Google Sheets adicionará essa função para você).

=ARRAYFORMULA(MÁXIMO(SE((B3:B8="A")*(C3:C8<DATA(2021;4;30));D3:D8)))