Baixe a apostila de exemplo
Este tutorial irá demonstrar como calcular "percentil if", recuperando o kº percentil em uma série de valores com critérios.
Função PERCENTILE
A função PERCENTILE é usada para calcular o kº percentil de valores em um intervalo onde k é o valor do percentil entre 0 e 1, inclusive.
= PERCENTIL ($ D $ 2: $ D $ 10,0,75)
No entanto, isso leva o percentil de todo o intervalo de valores. Em vez disso, para criar um “Percentil If”, usaremos a função PERCENTILE junto com a função IF em uma fórmula de matriz.
PERCENTIL SE
Combinando PERCENTILE e IF em uma fórmula de matriz, podemos essencialmente criar uma função “PERCENTLE IF” que funciona de maneira semelhante à função incorporada AVERAGEIF. Vejamos um exemplo.
Temos uma lista de pontuações alcançadas por alunos em duas disciplinas diferentes:
Suponha que sejamos solicitados a encontrar os 75º percentis de pontuações alcançadas para cada assunto, como:
Para fazer isso, podemos aninhar uma função IF com o sujeito como nossos critérios dentro da função PERCENTILE assim:
= PERCENTIL (SE (=,),)
= PERCENTIL (SE ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Ao usar o Excel 2022 e anteriores, você deve inserir a fórmula de matriz pressionando CTRL + SHIFT + ENTER (ao invés de DIGITAR), informando ao Excel que a fórmula em uma fórmula de matriz. Você saberá que é uma fórmula de matriz pelas chaves que aparecem ao redor da fórmula (veja a imagem superior).
Como funciona a fórmula?
A Função If avalia cada célula em nosso intervalo de critérios como TRUE ou FALSE, criando duas matrizes:
= PERCENTIL (SE ({VERDADEIRO; FALSO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO; VERDADEIRO; FALSO}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
Em seguida, a função IF cria uma única matriz, substituindo cada valor por FALSE se sua condição não for atendida.
= PERCENTIL ({0,81; FALSO; FALSO; 0,42; FALSO; 0,63; FALSO; 0,58; FALSO}, 0,75)
Agora a função PERCENTILE ignora os valores FALSE e calcula os 75º percentil dos valores restantes (0,72 é o 75º percentil).
PERCENTIL SE com múltiplos critérios
Para calcular PERCENTILE IF com vários critérios (semelhante a como funciona a função AVERAGEIFS integrada), você pode simplesmente multiplicar os critérios juntos:
= PERCENTIL (SE ((=) * (=),),)
= PERCENTIL (SE (($ D $ 2: $ D $ 10 = $ H2) * ($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Outra maneira de incluir vários critérios é aninhar mais instruções IF na fórmula.
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 você estiver usando o Excel 2022 ou mais recente, poderá inserir a fórmula sem CTRL + SHIFT + ENTER.
- Para recuperar os nomes dos alunos que obtiveram as melhores notas, combine com INDEX MATCH.
PERCENTIL SE no Google Sheets
A função PERCENTILE IF funciona exatamente da mesma forma no Planilhas Google e no Excel:
