Neste artigo, aprenderemos como encontrar o valor do percentil com determinados critérios no Excel
Cenário:
Em palavras simples, ao trabalhar com um conjunto de dados muito disperso, às vezes precisamos encontrar o percentual de números com alguns critérios sobre ele. Por exemplo, encontrar o percentil de salários em um determinado departamento ou ter vários critérios sobre data, nomes, departamento ou pode até mesmo numerar dados como salários abaixo do valor ou quantidade acima do valor. Para isso, você deve extrair manualmente os números necessários e, em seguida, calcular o percentil de uma matriz com critérios. Uso da função IF com fórmulas de matriz.
Como resolver o problema?
Você deve estar pensando como isso é possível, para realizar operações lógicas em matrizes de tabela usando a função IF. A função IF no Excel é muito útil, ela o ajudará em algumas tarefas difíceis no Excel ou em qualquer outra linguagem de codificação. A função IF testa as condições na matriz correspondente aos valores exigidos e retorna o resultado como uma matriz correspondente às condições Verdadeiras como 1 e Falsa como 0
Para este problema, usaremos as seguintes funções:
- Função PERCENTIL
- Função IF
Estaremos exigindo as funções acima e algum senso básico de operação de dados. condições lógicas em matrizes podem ser aplicadas usando operadores lógicos. Esses operadores lógicos funcionam com texto e números. Abaixo aqui está a fórmula genérica. { } As chaves são a ferramenta mágica para realizar fórmulas de array com a função IF.
Fórmula genérica:
| { = PERCENTIL (SE ((intervalo op crit), percentile_array), k) } |
Nota: Para colchetes ( { } ) Usar Ctrl + Shift + Enter ao trabalhar com matrizes ou intervalos no Excel. Isso gerará colchetes ondulados na fórmula por padrão. NÃO tente codificar os caracteres das chaves.
range: array, onde a condição se aplica
op: operador, operação aplicada
crit: critérios aplicados na faixa
percentile_array: array, onde o percentil é necessário
k: percentil k (por exemplo 33% -> k = 0,33 valor como número)
Exemplo :
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo. Aqui temos dados de produtos recebidos de diferentes regiões, juntamente com a data, quantidade e preço correspondentes. Aqui temos os dados e precisamos encontrar o 25º percentil ou valor correspondente a 25% considerando os pedidos que são recebidos apenas da região "Leste". Agora você está pronto para obter o resultado desejado usando a fórmula abaixo.
Use a fórmula:
| { = PERCENTIL (SE (B2: B11 = "Leste", D2: D11), 0,25) } |
Explicação:
- Região B2: B11 = "Leste": verifica os valores na região da matriz para corresponder a "Leste".
- O operador lógico retorna True para o valor correspondido e False para o valor não correspondido.
- Agora a função IF retorna apenas os valores de preço correspondentes a 1s e False como está. Abaixo de a está o intervalo retornado pela função IF e recebido pela função PERCENTILE.
{FALSE; 303,63; FALSE; 153,34; FALSE; 95,58; FALSE; FALSE; 177; FALSE}
- A função PERCENTILE retorna o preço do percentil 25% (k = 0,25) para a matriz retornada.
Aqui, as matrizes são fornecidas usando a referência de célula. Agora, o preço correspondente a 25% da matriz é dado abaixo.
Como você pode ver, o preço chega a ser 138,9, das três ordens do "Leste" com valores de preço 303,63, 153,34 e 95,58. Agora obtenha o valor do preço com percentis diferentes apenas alterando o valor k para 0,5 para 50%, 0,75 para 75% e 1 para o valor do percentil 100%.
Como você pode ver, temos todos os valores percentuais correspondentes a determinados critérios. Agora use a fórmula com valor de data como critério.
percentil se com critérios de data no Excel:
Data como critério é uma coisa complicada no Excel. Como o Excel armazena valores de data como um número. Portanto, se o valor da data não for reconhecido pelo Excel, a fórmula retornará um erro. Aqui, precisamos encontrar o valor do percentil de 25% com pedidos recebidos após 15 de janeiro de 2019.
Use a fórmula:
| { = PERCENTIL (SE (A2: A11> H3, D2: D11), 0,25)} |
>: maior que o operador aplicado sobre a matriz de data.
Aqui, as matrizes são fornecidas usando a referência de célula. Agora, o preço correspondente a 25% da matriz é dado abaixo.
Como você pode ver, 90,27 é o valor do 25º percentil com data posterior a 15 de janeiro de 2019. Agora obtenha o percentil para o diferente valor k.
Aqui estão todos os valores percentuais como resultados
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas:
- O percentile_array na fórmula funciona apenas com números.
- NÃO codifique chaves com fórmula.
- Se a fórmula retornar o erro #VALUE, verifique se as chaves devem estar presentes na fórmula conforme mostrado nos exemplos do artigo.
- Operações como igual a ( = ), menor que igual a ( <= ), Maior que ( > ) ou diferente de () pode ser executado dentro de uma fórmula aplicada, apenas com números.
Espero que este artigo sobre Como encontrar o percentil se com critérios no Excel seja explicativo. Encontre mais artigos sobre fórmulas de soma aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva-nos no site de e-mail
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel.
SUM se a data estiver entre : Retorna a SOMA dos valores entre datas ou períodos fornecidos no Excel.
Soma se a data for maior que a data fornecida: Retorna a soma dos valores após a data ou período especificado no Excel.
2 maneiras de somar por mês no Excel: Retorna a SOMA dos valores em um determinado mês específico no Excel.
Como somar várias colunas com condição: Retorna a soma dos valores em várias colunas com condição no Excel.
Artigos populares :
50 Atalho do Excel para aumentar sua produtividade : Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar tFunção VLOOKUP no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.