Cómo resaltar datos según el rango percentil en Excel

Un rango percentil indica la posición de un valor de datos particular en relación con otros valores de datos en una muestra. Los percentiles se utilizan sobre todo para determinar el rendimiento en pruebas estandarizadas. Si un niño obtiene una puntuación en el percentil 90 en una prueba estandarizada, esto significa que su puntuación es superior al 90 por ciento de los demás niños que toman la prueba.

Otra forma de verlo es decir que la puntuación del niño está en el 10 por ciento superior de todos los niños que tomaron la prueba.

Los percentiles se utilizan a menudo en el análisis de datos como un método para medir el desempeño de un sujeto en relación con el grupo en su conjunto, por ejemplo, para determinar la clasificación percentil de cada empleado en función de los ingresos anuales.

En Excel, puede obtener fácilmente rangos de percentiles clave utilizando la función PERCENTILE. Esta función requiere dos argumentos: un rango de datos y la puntuación percentil que desea ver.

En el ejemplo que se muestra, el valor de la celda D7 es el resultado de la siguiente fórmula, que extrae el percentil 75 según los datos del rango B3: B20:

= PERCENTIL ($ B $ 3: $ B $ 20,0.75)

Esta fórmula le dice que cualquier empleado con ingresos superiores a $ 52 651 se encuentra en el 75 por ciento de los mejores.

El valor de la celda D16 es el resultado de la siguiente fórmula, que extrae el percentil 25 según los datos del rango B3: B20:

= PERCENTIL ($ B $ 3: $ B $ 20,0.25)

Esta fórmula le dice que cualquier empleado con ingresos por debajo de $ 24,656 se encuentra en el 25 por ciento inferior de los artistas.

Utilizando estos marcadores de percentiles, este ejemplo aplica formato condicional de modo que cualquier valor en el percentil 75 será de color verde y cualquier valor en el percentil 25 será de color rojo.

Para crear esta regla de formato básica, siga estos pasos:

Seleccione las celdas de datos en su rango de destino (celdas B3: B20 en este ejemplo), haga clic en la pestaña Inicio de la cinta de Excel y luego seleccione Formato condicional → Nueva regla.

Esto abre el cuadro de diálogo Nueva regla de formato.

En el cuadro de lista en la parte superior del cuadro de diálogo, haga clic en la opción Usar una fórmula para determinar qué celdas formatear.

Esta selección evalúa los valores según una fórmula que especifique. Si un valor particular se evalúa como VERDADERO, el formato condicional se aplica a esa celda.

En el cuadro de entrada de la fórmula, ingrese la fórmula que se muestra con este paso.

En esta fórmula, evalúa si los datos de la celda objetivo (B3) están dentro del percentil 25. Si es así, se aplicará el formato condicional.

= B3 <>

Tenga en cuenta que en la fórmula, excluye los símbolos de dólares de referencia absolutos ($) para la celda de destino (B3). Si hace clic en la celda B3 en lugar de escribir la referencia de celda, Excel automáticamente hace que su referencia de celda sea absoluta. Es importante que no incluya los símbolos de dólar de referencia absoluta en su celda de destino porque necesita Excel para aplicar esta regla de formato en función del valor propio de cada celda.

Haga clic en el botón Formato.

Esto abre el cuadro de diálogo Formato de celdas, donde tiene un conjunto completo de opciones para formatear la fuente, el borde y el relleno de la celda de destino. Una vez que haya terminado de elegir las opciones de formato, haga clic en el botón Aceptar para confirmar los cambios y volver al cuadro de diálogo Nueva regla de formato.

De vuelta en el cuadro de diálogo Nueva regla de formato, haga clic en el botón Aceptar para confirmar su regla de formato.

En este punto, debería estar en el cuadro de diálogo Administrador de reglas de formato condicional.

Haga clic en el botón Nueva regla.

Esto abre el cuadro de diálogo Nueva regla de formato.

En el cuadro de lista en la parte superior del cuadro de diálogo, haga clic en la opción Usar una fórmula para determinar qué celdas formatear. Esta selección evalúa los valores según una fórmula que especifique. Si un valor en particular se evalúa como VERDADERO, entonces el formato condicional se aplica a esa celda.

En el cuadro de entrada de la fórmula, ingrese la fórmula que se muestra aquí.

En esta fórmula, está evaluando si los datos en la celda objetivo (B3) están dentro del percentil 75. Si es así, se aplicará el formato condicional.

= B3> = PERCENTIL ($ B $ 3: $ B $ 20,0.75)

Haga clic en el botón Formato.

Esto abre el cuadro de diálogo Formato de celdas, donde tiene un conjunto completo de opciones para formatear la fuente, el borde y el relleno de la celda de destino. Una vez que haya terminado de elegir las opciones de formato, haga clic en el botón Aceptar para confirmar los cambios y volver al cuadro de diálogo Nueva regla de formato.

De vuelta en el cuadro de diálogo Nueva regla de formato, haga clic en el botón Aceptar para confirmar su regla de formato.

Si necesita editar su regla de formato condicional, simplemente coloque el cursor en cualquiera de las celdas de datos dentro de su rango formateado y luego vaya a la pestaña Inicio y seleccione Formato condicional → Administrar reglas. Esto abre el cuadro de diálogo Administrador de reglas de formato condicional. Haga clic en la regla que desea editar y luego haga clic en el botón Editar regla.