Baixe a apostila de exemplo
Este tutorial demonstrará como encontrar o maior valor de célula que atenda a condições específicas no Excel e no Planilhas Google.
Função Max If Array
A função MAX identifica o maior valor em uma série de números.
Podemos usar a função MAX combinada com uma função IF para identificar o maior valor que atende a uma condição especificada.
Recomenda-se aos usuários do Planilhas Google e do Excel 2022 ou posterior que usem a função MAXIFS mais simples. Isso é explicado em uma seção posterior.
Este exemplo usará as funções MAX e IF em uma fórmula de matriz para identificar o maior Tamanho do pedido para cada Nome da loja
| 1 | {= MAX (IF (B3: B8 = "A", D3: D8))} |
No Office 365 e nas versões do Excel posteriores a 2022, você pode simplesmente inserir a fórmula acima como faria normalmente (pressionando ENTER).
No entanto, para Excel 2022 e anteriores, você deve inserir a fórmula pressionando CTRL + SHIFT + ENTER. Depois de fazer isso, você notará colchetes de matriz curvas aparecem ao redor da fórmula.
Para mostrar como essa fórmula funciona, vamos dividi-la em etapas.
Esta é nossa fórmula final (mostrada sem os colchetes da fórmula de matriz adicionados automaticamente):
| 1 | = MAX (IF (B3: B8 = "A", D3: D8)) |
Primeiro, os valores do intervalo de células são adicionados à fórmula como matrizes:
| 1 | = MAX (IF ({"A"; "B"; "A"; "B"; "A"; "B"} = "A", {500; 400; 300; 700; 600; 200})) |
Próximo a Nome da loja = Condição "A" produz uma matriz de valores VERDADEIRO / FALSO:
| 1 | = MAX (SE ({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {500; 400; 300; 700; 600; 200})) |
Em seguida, a função IF altera todos os valores TRUE para os valores relevantes Tamanho do pedido:
| 1 | = MAX ({500; FALSE; 300; FALSE; 600; FALSE}) |
A função MAX identifica o maior número na matriz, enquanto ignora quaisquer valores FALSE, para mostrar o maior Tamanho do pedido para o Nome da loja = “A”:
| 1 | =600 |
Max If - Multiple Criteria
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 do pedido para cada Nome da loja, mas pelo Datas de pedidos antes de 30/04/2021 usando as funções MAX, IF e DATE:
| 1 | {= MAX (IF ((B3: B8 = "A") * (C3: C8 |
Observe aqui que multiplicamos dois conjuntos de critérios VERDADEIRO / FALSO juntos:
| 1 | (B3: B8 = "A") * (C3: C8 |
Se ambos os critérios forem VERDADEIROS, a condição total será calculada como VERDADEIRA, mas se um (ou mais) critérios for FALSO, será calculado como FALSO.
Usando esta metodologia, é possível adicionar muitos critérios diferentes a esta fórmula.
Max If - Multiple Criteria with Cell References
Normalmente, não é uma boa prática codificar os valores em fórmulas. Em vez disso, é mais flexível usar células separadas para definir os critérios.
Para combinar com o Nome da loja para o valor mostrado na coluna F, podemos atualizar a fórmula para ser:
| 1 | {= MAX (IF ((B3: B8 = F3) * (C3: C8 |
Referências de Célula de Bloqueio
Para tornar nossas fórmulas mais fáceis de ler, mostramos as fórmulas sem referências de células bloqueadas:
| 1 | {= MAX (IF ((B3: B8 = F3) * (C3: C8 |
Mas essas fórmulas não funcionarão corretamente quando copiadas e coladas em outro lugar do arquivo. Em vez disso, você deve usar referências de células bloqueadas como este:
| 1 | {= MAX (SE (($ B $ 3: $ B $ 8 = F3) * ($ C $ 3: $ C $ 8 |
Leia nosso artigo sobre Bloqueio de referências de células para saber mais.
Função MAXIFS
Os usuários do Planilhas Google e do Excel 2022 ou posterior podem usar a única função MAXIFS para replicar o comportamento das funções MAX e IF mostradas nos exemplos anteriores.
Este próximo exemplo usa as funções MAXIFS e DATE para mostrar o maior Tamanho do pedido para cada Nome da loja para Datas de pedidos antes de 30/04/2021:
| 1 | = MAXIFS (D3: D8, B3: B8, "A", C3: C8, "<" & DATA (2021,4,30)) |
A função MAXIFS não exige que o usuário pressione CTRL + SHIFT + ENTER ao inserir a fórmula.
Max If (Max Value with Condition) no Google Sheets
Os exemplos mostrados acima funcionam exatamente da mesma forma no Planilhas Google e no Excel, mas como a função MAXIFS está disponível, é recomendado usar esta função única em vez de combinar as funções MAX e IF.
Se for necessário usar os exemplos que usam as funções MAX e IF, o Planilhas Google exige que você os insira como fórmulas de matriz. Em vez de mostrar a fórmula com colchetes de matriz do Excel {}, pressionar CTRL + SHIFT + ENTER adiciona automaticamente a função ARRAYFORMULA ao redor da fórmula:
| 1 | = ARRAYFORMULA (MAX (IF ((B3: B8 = "A") * (C3: C8 |
