Cómo encontrar el percentil si con criterios en Excel

En este artículo, aprenderemos cómo encontrar el valor del percentil si con los criterios dados en Excel

Guión:

En palabras sencillas, cuando se trabaja con un conjunto de datos muy disperso, a veces necesitamos encontrar el percentil de números con algunos criterios sobre él. Por ejemplo, encontrar el percentil de los salarios en un departamento en particular o tener varios criterios sobre la fecha, los nombres, el departamento o incluso los datos de números como los salarios por debajo del valor o la cantidad por encima del valor. Para esto, debe extraer manualmente los números requeridos y luego calcular el percentil de una matriz con criterios. Uso de la función SI con fórmulas de matriz.

¿Como resolver el problema?

Debe estar pensando cómo es esto posible, para realizar operaciones lógicas sobre matrices de tablas usando la función SI. Si la función en Excel es muy útil, le ayudará a superar algunas tareas difíciles en Excel o en cualquier otro lenguaje de codificación. La función SI prueba las condiciones en la matriz correspondientes a los valores requeridos y devuelve el resultado como una matriz correspondiente a las condiciones verdaderas como 1 y Falso como 0

Para este problema, usaremos las siguientes funciones:

1. Función PERCENTIL
2. Función SI

Necesitaremos estas funciones anteriores y un sentido básico de operación de datos. Las condiciones lógicas en matrices se pueden aplicar utilizando operadores lógicos. Estos operadores lógicos funcionan tanto con texto como con números. A continuación, se muestra la fórmula genérica. { } llaves es la herramienta mágica para realizar fórmulas de matriz con la función SI.

Fórmula genérica:

{ = PERCENTIL (SI ((rango op crit), matriz_percentiles), k) }

Nota: para llaves ( { } ) Usar Ctrl + Mayús + Entrar al trabajar con matrices o rangos en Excel. Esto generará Curly Braces en la fórmula de forma predeterminada. NO intente codificar de forma rígida los caracteres de llaves.

rango: matriz, donde se aplica la condición

op: operador, operación aplicada

crit: criterios aplicados en el rango

percentile_array: matriz, donde se necesita percentil

k: percentil k (por ejemplo, 33% -> k = valor de 0,33 como número)

Ejemplo :

Todos estos pueden resultar confusos de entender. Entonces, probemos esta fórmula ejecutándola en el ejemplo que se muestra a continuación. Aquí tenemos datos de productos recibidos de diferentes regiones junto con la fecha, cantidad y precio correspondientes. Aquí tenemos los datos y necesitamos encontrar el percentil 25 o el valor correspondiente al 25% considerando los pedidos que solo se reciben de la región "Este". Ahora está listo para obtener el resultado deseado utilizando la siguiente fórmula.

Usa la fórmula:

{ = PERCENTIL (SI (B2: B11 = "Este", D2: D11), 0,25) }

Explicacion:

1. Región B2: B11 = "Este": comprueba los valores en la región de la matriz para que coincidan con "Este".
2. El operador lógico devuelve Verdadero para el valor coincidente y Falso para el valor no coincidente.
3. Ahora la función SI solo devuelve los valores de precio correspondientes a los 1 y Falso tal como está. A continuación se muestra el rango devuelto por la función IF y recibido por la función PERCENTILE.

{ FALSO; 303,63; FALSO ; 153,34; FALSO ; 95,58; FALSO ; FALSO ; 177; FALSO }

1. La función PERCENTIL devuelve el precio percentil del 25% (k = 0,25) para la matriz devuelta.

Aquí las matrices se dan usando la referencia de celda. Ahora el precio correspondiente al 25% de la matriz se da a continuación.

Como puede ver, el precio llega a ser 138,9, de los tres pedidos de "Este" que tienen valores de precio 303,63, 153,34 y 95,58. Ahora obtenga el valor del precio con un percentil diferente simplemente cambiando el valor k-ésimo a 0.5 para el 50%, 0.75 para el 75% y 1 para el 100% del valor del percentil.

Como puede ver, tenemos todos los valores de percentiles correspondientes a los criterios dados. Ahora use la fórmula con el valor de Fecha como criterio.

percentil si con criterios de fecha en Excel:

La fecha como criterio es algo complicado en Excel. Como Excel almacena los valores de fecha como un número. Entonces, si Excel no reconoce el valor de la fecha, la fórmula devuelve el error. Aquí necesitamos encontrar el valor del percentil del 25% con pedidos recibidos después del 15 de enero de 2019.

Usa la fórmula:

{ = PERCENTIL (SI (A2: A11> H3, D2: D11), 0.25)}

>: operador mayor que aplicado sobre la matriz de fechas.

Aquí las matrices se dan usando la referencia de celda. Ahora el precio correspondiente al 25% de la matriz se da a continuación.

Como puede ver, 90.27 es el valor del percentil 25 que tiene fecha posterior al 15 de enero de 2019. Ahora obtenga el percentil para el valor kth diferente.

Aquí están todos los valores de percentiles como resultados

Aquí están todas las notas de observación sobre el uso de la fórmula.

Notas:

1. El percentile_array en la fórmula solo funciona con números.
2. NO codifique las llaves con fórmula.
3. Si la fórmula devuelve el error #VALOR, verifique que las llaves deben estar presentes en la fórmula como se muestra en los ejemplos del artículo.
4. Operaciones como es igual a ( = ), menor que igual a ( <= ), mas grande que ( > ) o no igual a () se puede realizar dentro de una fórmula aplicada, solo con números.

Espero que este artículo sobre Cómo encontrar el percentil si con criterios en Excel sea explicativo. Encuentre más artículos sobre fórmulas de suma aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico

Cómo utilizar la función SUMPRODUCT en Excel: Devuelve la SUMA después de la multiplicación de valores en varias matrices en Excel.

SUM si la fecha está entre : Devuelve la SUMA de valores entre fechas o períodos dados en Excel.

Suma si la fecha es mayor que la fecha dada: Devuelve la SUMA de los valores posteriores a la fecha o período especificado en Excel.

2 formas de sumar por mes en Excel: Devuelve la SUMA de valores dentro de un mes específico dado en Excel.

Cómo sumar varias columnas con condición: Devuelve la SUMA de los valores en varias columnas con condición en Excel.

Articulos populares :

50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.

Cómo usar tLa función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.

Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.

Cómo usar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.