Descarga el libro de trabajo de ejemplo
Este tutorial demostrará cómo calcular el "percentil si", recuperando el kth percentil en una rabia de valores con criterio.
Función PERCENTILE
La función PERCENTIL se utiliza para calcular el kth percentil de valores en un rango donde k es el valor del percentil entre 0 y 1 inclusive.
= PERCENTIL ($ D $ 2: $ D $ 10,0.75)
Sin embargo, esto toma el percentil de todo el rango de valores. En su lugar, para crear un "percentil si", usaremos la función PERCENTIL junto con la función SI en una fórmula de matriz.
PERCENTIL SI
Combinando PERCENTIL y SI en una fórmula de matriz, podemos esencialmente crear una función "PORCENTAJE SI" que funciona de manera similar a cómo funciona la función incorporada AVERAGEIF. Veamos un ejemplo.
Tenemos una lista de puntuaciones obtenidas por los estudiantes en dos materias diferentes:
Supongamos que se nos pide que encontremos los 75th percentiles de puntajes logrados para cada asignatura así:
Para lograr esto, podemos anidar una función SI con el tema como nuestros criterios dentro de la función PERCENTILE así:
= PERCENTIL (SI (=,),)
= PERCENTIL (SI ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), 0,75)
Cuando use Excel 2022 y versiones anteriores, debe ingresar la fórmula de matriz presionando CTRL + MAYÚS + ENTRAR (en lugar de INGRESAR), diciéndole a Excel que la fórmula en una fórmula de matriz. Sabrá que es una fórmula de matriz por las llaves que aparecen alrededor de la fórmula (vea la imagen superior).
¿Cómo funciona la fórmula?
La función If evalúa cada celda en nuestro rango de criterios como VERDADERO o FALSO, creando dos matrices:
= PERCENTIL (SI ({VERDADERO; FALSO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO; VERDADERO; FALSO}, {0,99; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 0,75)
A continuación, la función SI crea una única matriz, reemplazando cada valor con FALSO si no se cumple su condición.
= PERCENTIL ({0,81; FALSO; FALSO; 0,42; FALSO; 0,63; FALSO; 0,58; FALSO}, 0,75)
Ahora la función PERCENTIL omite los valores FALSOS y calcula los 75th percentil de los valores restantes (0,72 es el 75th percentil).
PERCENTILE SI con varios criterios
Para calcular PERCENTILE IF con varios criterios (similar a cómo funciona la función AVERAGEIFS incorporada), simplemente puede multiplicar los criterios juntos:
= PERCENTIL (SI ((=) * (=),),)
= PERCENTIL (SI (($ D $ 2: $ D $ 10 = $ H2) * ($ C $ 2: $ C $ 10 = $ G2), $ E $ 2: $ E $ 10), 0,75)
Otra forma de incluir varios criterios es anidar más declaraciones IF dentro de la fórmula.
Consejos y trucos:
- Siempre que sea posible, siempre haga referencia a la posición (k) de una celda auxiliar y bloquee la referencia (F4), ya que esto facilitará el llenado automático de fórmulas.
- Si usa Excel 2022 o una versión más reciente, puede ingresar la fórmula sin CTRL + MAYÚS + ENTRAR.
- Para recuperar los nombres de los estudiantes que obtuvieron las mejores calificaciones, combínelos con INDEX MATCH.
PERCENTIL SI en Hojas de cálculo de Google
La función PERCENTILE IF funciona exactamente igual en Google Sheets que en Excel:
