¿Cómo calcular rápidamente el percentil o cuartil en Excel ignorando los ceros?
Al aplicar las funciones PERCENTIL o CUARTIL en Excel, es habitual que los usuarios se encuentren con rangos de datos que contienen valores cero. De forma predeterminada, estas funciones incluyen dichos ceros en sus cálculos, lo que puede distorsionar significativamente los resultados al reducir los valores del percentil o cuartil, especialmente cuando el cero no representa información relevante en ese contexto. Para lograr un análisis estadístico más preciso, es posible que desee excluir por completo los valores cero al calcular percentiles o cuartiles. Este tutorial le presenta diversas técnicas prácticas para resolver este problema de manera eficiente en Excel, incluyendo enfoques con fórmulas nativas, soluciones mediante VBA y un análisis comparativo de escenarios que le ayudará a elegir el método más adecuado según sus necesidades.
PERCENTIL o CUARTIL ignorando ceros
PERCENTIL ignorando ceros (fórmula matricial)
Para calcular el percentil ignorando los ceros, utilice una fórmula matricial que considere exclusivamente los valores mayores que cero.
Seleccione una celda vacía donde desee mostrar el resultado e introduzca la siguiente fórmula:
=PERCENTILE(IF(A1:A13>0,A1:A13),0.3)Después de escribir la fórmula, debes pulsar Ctrl + Mayús + Intro (no solo Intro), ya que se trata de una fórmula matricial. Excel rodeará la fórmula con llaves { }, lo que indica que se ha introducido correctamente. En esta fórmula:
- A1:A13 es su rango de datos; ajústelo según sea necesario para su propia hoja.
- 0,3 especifica el 30 .ºpercentil. Puede cambiar este valor por el percentil que desee calcular (por ejemplo, 0,75 para el 75)º percentil).
Este método resulta especialmente útil cuando desea evitar que los ceros —como mediciones faltantes o nulas— influyan en los resultados estadísticos.
Tenga en cuenta que pulsar solo Intro no funcionará correctamente; debe usar Ctrl + Mayús + Intro. Además, las fórmulas con SI(...) dentro de funciones de agregación pueden resultar menos eficientes con conjuntos de datos grandes.
CUARTIL ignorando ceros (fórmula matricial)
Este enfoque es similar al de los cuartiles. Seleccione una celda para el resultado e introduzca:
=QUARTILE(IF(A1:A18>0,A1:A18),1)Después de introducir la fórmula, pulse Ctrl + Mayús + Intro para confirmarla como fórmula matricial.
- A1:A18 es el rango de datos de la muestra (cámbielo según sea necesario).
- 1significa que desea el primer cuartil (percentil 25)º). Puede usar 2 para la mediana o 3 para el tercer cuartil (percentil 75 º).
Asegúrese de que su rango de datos no contenga texto ni celdas con errores, ya que la fórmula solo funciona con valores numéricos. Esta solución es ideal para conjuntos de datos de tamaño moderado que necesiten un cálculo rápido sin recurrir a VBA ni complementos.
Macro de VBA para filtrar y calcular percentil/cuartil excluyendo ceros
También puede usar VBA (Visual Basic para Aplicaciones) para automatizar el filtrado de valores cero y, a continuación, calcular un percentil o cuartil con los datos restantes. Este enfoque resulta especialmente útil al trabajar con grandes volúmenes de datos o cuando necesita repetir el procedimiento con frecuencia sin tener que introducir fórmulas manualmente.
Escenarios aplicables: Ideal para usuarios avanzados, tareas repetitivas o rangos complejos. Al personalizar el código, podrás gestionar cualquier índice de percentil o cuartil y cualquier rango de datos.
1. Vaya a la pestaña Herramientas para desarrolladores en Excel. Si no está visible, haga clic con el botón derecho en la Cinta de opciones, elija Personalizar la Cinta de opciones y active la casilla Desarrollador. A continuación, haga clic en Herramientas para desarrolladores > Visual Basic.
2. En la ventana de Microsoft Visual Basic para Aplicaciones, haga clic en Insertar > Módulo.
3. Copie y pegue el siguiente código VBA en el módulo:
Sub FilterZeroAndPercentile()
Dim rng As Range
Dim ws As Worksheet
Dim arr As Variant
Dim filteredArr As Variant
Dim i As Long, count As Long
Dim percentileVal As Double
Dim quartileVal As Double
Dim pctl As Double
Dim quartIdx As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the data range (numbers only)", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
' Prompt for percentile value (e.g., 0.75 for 75th percentile)
pctl = Application.InputBox("Enter percentile value between 0 and 1 (e.g., 0.75 for 75th percentile)", xTitleId, "0.75", Type:=1)
' Prompt for quartile index (1, 2, 3, 4)
quartIdx = Application.InputBox("Enter quartile index (e.g., 1 for first quartile)", xTitleId, "1", Type:=1)
arr = rng.Value
count = 0
' Count non-zero numbers
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
End If
Next i
If count = 0 Then
MsgBox "No non-zero data found!", vbExclamation, xTitleId
Exit Sub
End If
ReDim filteredArr(1 To count)
count = 0
For i = 1 To UBound(arr, 1)
If arr(i, 1) > 0 Then
count = count + 1
filteredArr(count) = arr(i, 1)
End If
Next i
' Calculate percentile / quartile
percentileVal = Application.WorksheetFunction.Percentile(filteredArr, pctl)
quartileVal = Application.WorksheetFunction.Quartile(filteredArr, quartIdx)
MsgBox "Percentile (" & pctl & "): " & percentileVal & vbCrLf & _
"Quartile (" & quartIdx & "): " & quartileVal, vbInformation, xTitleId
End Sub4. Haga clic en el botón 
o pulse F5en la ventana de VBA para ejecutar la macro. Se le pedirá que seleccione su rango de datos (solo números), que especifique el percentil deseado (por ejemplo, 0,3 para el 30)º percentil) y el índice de cuartil (como 1 para el primer cuartil). La macro filtrará automáticamente los valores cero y mostrará los resultados en un cuadro de mensaje.
Ventajas: Gestiona rápidamente grandes volúmenes o datos irregulares, excluye por completo los valores cero y evita la introducción manual de fórmulas. Además, permite reutilización y personalización.
Inconvenientes: Requiere habilitar macros y cierta familiaridad con VBA. No es adecuado para fórmulas de hoja de cálculo a menos que se convierta en una función definida por el usuario (UDF).
Problemas habituales y solución de incidencias: si selecciona celdas no numéricas o que contengan errores, la macro podría omitirlas o mostrar un mensaje de error. Asegúrese de que el rango de datos incluya exclusivamente números con valores cero o positivos. En caso de que no se detecten datos distintos de cero, recibirá una notificación al respecto.
Consejos: puede personalizar aún más el código VBA para copiar el resultado en una celda específica de la hoja de cálculo, modificar las funciones de cálculo o automatizarlo en varios rangos. Guarde siempre su libro antes de ejecutar o editar macros para evitar la pérdida accidental de datos.
Si necesita ampliar esta solución a cálculos de percentiles o cuartiles en varias columnas, considere modificar la macro mediante bucles que recorran las columnas o los rangos correspondientes.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos