¿Cómo se calcula la mediana en Excel ignorando los ceros o los errores?
En muchas tareas de análisis de datos en Excel, calcular la mediana con precisión es fundamental para entender la tendencia central de su conjunto de datos. Sin embargo, a veces ese conjunto incluye ceros o valores de error (como)#¡DIV/0!, #N/A, etc.), lo que puede interferir en un cálculo directo de la mediana. Por ejemplo, al usar la fórmula estándar =MEDIANA(rango), se incluyen los ceros en el cálculo y, si hay celdas no válidas en el rango, la fórmula devuelve un error, lo que podría generar resultados engañosos o interrumpir el cálculo, tal como se ilustra a continuación.
Para solucionarlo, dispone de varias opciones que le permiten calcular la mediana excluyendo ceros o errores, garantizando un análisis preciso y robusto. Estas soluciones se adaptan a diversos escenarios, como la limpieza de datos de encuestas, informes financieros o mediciones científicas, donde es necesario eliminar ceros o errores para obtener resultados significativos. A continuación, encontrará guías prácticas paso a paso para cada método disponible en Excel, desde fórmulas directas hasta técnicas avanzadas de automatización.
VBA: Mediana ignorando ceros y errores (UDF)
Power Query: Mediana tras filtrar ceros/errores
Mediana ignorando ceros
Cuando su rango incluya ceros que no desee tener en cuenta para el cálculo de la mediana —por ejemplo, valores ausentes representados como 0—, puede usar una fórmula matricial para excluílos. Esta opción resulta especialmente útil en conjuntos de datos donde los ceros indican datos no disponibles en lugar de mediciones reales.
Seleccione una celda en la que quiera mostrar la mediana (por ejemplo, C2) e introduzca la siguiente fórmula:
=MEDIAN(IF(A2:A17<>0,A2:A17))Después de introducir la fórmula, en lugar de pulsar solo Intro, pulse Ctrl + Mayús + Intro para convertirla en una fórmula matricial (verá llaves alrededor de la fórmula en la Barra de fórmulas). Así se garantiza que, en el cálculo de la mediana, solo se tengan en cuenta los valores distintos de cero del rango A2:A17. Vea la captura de pantalla:
Consejos:
- Si utiliza Excel 365, Excel 2021 o versiones posteriores, basta con pulsar Intro, gracias al soporte de matrices dinámicas.
- Asegúrese de que haya al menos un valor numérico distinto de cero en el rango; de lo contrario, la fórmula devolverá el error #¡NUM!.
- Esta solución es ideal para depurar respuestas de encuestas, informes de gastos o datos de ventas en los que los ceros deben excluirse del análisis.
Mediana ignorando errores
Valores de error como #N/A, #¡DIV/0! o #¡VALOR! pueden hacer que la función MEDIANA estándar devuelva un error, interrumpiendo su análisis de datos. Para calcular la mediana de forma segura excluyendo estos errores, use la siguiente fórmula matricial.
Seleccione cualquier celda en la que desee mostrar el resultado e introduzca la fórmula siguiente:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Después de introducir la fórmula, pulse Ctrl + Mayús + Intro (salvo que use Excel 365/Excel 2021 o versiones posteriores, que admiten matrices dinámicas). Esta fórmula incluye únicamente los valores en F2:F17 que sean números reales, ignorando por completo las celdas con errores.
Consejos y advertencias:
- Si todas las celdas contienen valores de error, el resultado devolverá un error #¡NUM!. ¡Asegúrese de que sus datos incluyan al menos un número válido!
- Puede combinar criterios de exclusión (por ejemplo, excluir tanto ceros como errores) anidando condiciones.
- Esta fórmula resulta especialmente útil al trabajar con datos importados, resultados de encuestas o estados financieros que puedan incluir cálculos parciales o fallidos.
VBA: Mediana ignorando ceros y errores (UDF)
En escenarios donde necesite calcular frecuentemente la mediana ignorando tanto ceros como errores, o requiera una solución que evite introducir manualmente fórmulas matriciales, puede utilizar una función personalizada de VBA (función definida por el usuario, UDF). Este enfoque aporta mayor flexibilidad, ya que la función personalizada puede encapsular todos los criterios de exclusión y utilizarse como cualquier fórmula integrada, lo que la convierte en la opción ideal para conjuntos de datos grandes o que se actualizan con frecuencia.
Cómo configurar la UDF:
- Haga clic en la pestaña Programador en Excel. Si no está visible, actívela desde Archivo > Opciones > Personalizar cinta de opciones.
- Haga clic en Visual Basic para abrir el editor de VBA.
- En el editor de VBA, haga clic en Insertar > Módulo para crear un módulo nuevo.
- Copie y pegue el siguiente código en el módulo:
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionCómo usar la UDF:
Al volver a Excel, simplemente introduzca la fórmula =MedianaIgnorarCeroError(A2:A17)en cualquier celda (sustituya)A2:A17 por su rango objetivo). A diferencia de las fórmulas matriciales, solo tiene que pulsar Intro: no es necesario usar Ctrl + Mayús + Intro.
- Este método funciona especialmente bien con conjuntos de datos muy grandes, evita las peculiaridades de las fórmulas matriciales y puede adaptarse fácilmente para ignorar otros valores no deseados mediante una ligera modificación del código.
- Si el rango contiene únicamente ceros o errores, el resultado mostrará #¡NUM!
- Si recibe un error #¿NOMBRE?, asegúrese de que la macro de VBA esté correctamente instalada y de que las macros estén habilitadas en la configuración de Excel.
Power Query: Mediana tras filtrar ceros/errores
Power Query es una herramienta potente en Excel para importar, transformar y analizar datos, especialmente cuando su objetivo es limpiar y preprocesar grandes conjuntos de datos antes de realizar cálculos como la mediana. Con Power Query, puede filtrar fácilmente ceros y errores, asegurando que solo permanezcan números válidos en sus cálculos. Este enfoque resulta especialmente beneficioso si sus datos de origen se actualizan regularmente o se importan desde sistemas externos.
Pasos para usar Power Query y calcular la mediana ignorando ceros y errores:
- Seleccione cualquier celda dentro de su rango de datos, vaya a la pestaña Datos y haga clic en Desde tabla o rango. Si sus datos aún no están en formato de tabla, Excel le pedirá que la cree: haga clic en Aceptar.
- Se abrirá la ventana del Editor de Power Query. Haga clic en la flecha desplegable de la columna correspondiente y desmarque 0para filtrar los valores cero. (Para filtrar errores, haga clic con el botón derecho en el encabezado de la columna y elija)Quitar errores.)
- Una vez aplicado el filtro, haga clic en Inicio > Cerrar y cargar para enviar los datos limpios de vuelta a su hoja de cálculo.
- Ahora, aplique la fórmula estándar
=MEDIANA()a la columna con los valores ya filtrados, ya que los datos excluyen ahora todos los elementos no deseados.
Este método garantiza que sus datos originales permanezcan intactos, ofrece una excelente repetibilidad con datos nuevos o actualizados y resulta especialmente eficaz para tareas recurrentes de generación de informes o al trabajar con conjuntos de datos grandes o externos. Los flujos de trabajo de Power Query se actualizan con un solo clic cada vez que cambian sus datos de origen, minimizando la intervención manual y el riesgo de errores.
- Power Query está disponible en Excel 2016 y versiones posteriores (o como complemento para Excel 2010 y 2013).
- Tras la transformación, los datos limpios resultantes permiten realizar cálculos con mayor fiabilidad para el análisis posterior.
Si obtiene resultados inesperados, revise detenidamente los pasos de filtrado en Power Query y asegúrese de que sus datos limpios contengan valores numéricos válidos.
En resumen, ya sea que prefiera usar fórmulas matriciales directamente, crear una solución personalizada en VBA para automatizar tareas o aprovechar Power Query para flujos de trabajo más amplios, Excel le ofrece múltiples opciones prácticas para calcular la mediana ignorando ceros y errores. Elija el método que mejor se adapte al tamaño de su conjunto de datos, la frecuencia con la que lo actualiza y sus preferencias de flujo de trabajo, y obtenga resultados fiables y precisos.
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