Baixe a apostila de exemplo
Este tutorial demonstrará como calcular “grande se” ou “pequeno se”, recuperando o enésimo maior (ou menor) valor com base em critérios.
Funções GRANDES e PEQUENAS
A função LARGE é usada para calcular o enésimo maior valor (k) em uma matriz, enquanto a função SMALL retorna o menor enésimo valor.
Para criar um “Large If”, usaremos a função LARGE junto com a função IF em uma fórmula de matriz.
GRANDE SE
Combinando LARGE (ou SMALL) e IF em uma fórmula de matriz, podemos essencialmente criar uma função “LARGE IF” que funciona de forma semelhante à fórmula SOMASE embutida. Vejamos um exemplo.
Temos uma lista de notas obtidas por alunos em duas disciplinas diferentes:
Suponha que sejamos solicitados a encontrar as três primeiras notas obtidas para cada disciplina, da seguinte forma:
Para fazer isso, podemos aninhar uma função IF com o sujeito como nossos critérios dentro da função LARGE assim:
= GRANDE (SE (=,),)
= GRANDE (SE ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Ao usar o Excel 2022 e anteriores, você deve inserir a fórmula pressionando CTRL + SHIFT + ENTER para obter as chaves em torno da fórmula.
Como funciona a fórmula?
A fórmula funciona avaliando cada célula em nosso intervalo de critérios como TRUE ou FALSE.
Encontrando o valor da nota máxima (k = 1) em Matemática:
= GRANDE (SE ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= GRANDE (SE ({VERDADEIRO; FALSO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Em seguida, a função IF substitui cada valor por FALSE se sua condição não for atendida.
= GRANDE ({0,81; FALSO; FALSO; 0,42; FALSO; 0,63; FALSO; 0,58; FALSO}, 1)
Agora a função LARGE ignora os valores FALSE e calcula o maior (k = 1) dos valores restantes (0,81 são os maiores valores entre 0,42 e 0,81).
PEQUENO SE
A mesma técnica também pode ser aplicada com a função SMALL.
= PEQUENO (SE ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
GRANDE IF com múltiplos critérios
Para usar LARGE IF com vários critérios (semelhante a como funciona a fórmula SOMASE incorporada), basta aninhar mais funções IF na função LARGE da seguinte maneira:
= GRANDE (SE (=, SE (=,)),)
= GRANDE (SE ($ D $ 2: $ D $ 18 = $ H3, SE ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Outra maneira de incluir vários critérios é multiplicar os critérios juntos, conforme mostrado neste artigo
Dicas e truques:
- Sempre que possível, sempre faça referência à posição (k) de uma célula auxiliar e bloqueie a referência (F4), pois isso tornará as fórmulas de preenchimento automático mais fácil.
- Se estiver usando o Excel 2022 ou mais recente, você pode inserir a fórmula sem Ctrl + Shift + Enter.
- Para recuperar os nomes dos alunos que alcançaram as melhores notas, combine com isso com INDEX MATCH
