¿Cómo calcular el promedio de celdas no adyacentes excluyendo los valores cero en Excel?
Al trabajar con datos en Excel, a menudo necesita calcular el promedio de un conjunto de números, pero las celdas relevantes no siempre son adyacentes y, además, desea excluir los valores cero (que pueden representar datos faltantes o entradas anuladas). Por ejemplo, en una hoja de ventas o inventario, ciertos artículos podrían tener stock cero o sin ventas en fechas específicas; en estos casos, un cálculo simple del promedio no reflejaría con precisión la tendencia real de los datos, ya que los ceros distorsionan el resultado. Al excluidos, podrá analizar únicamente los puntos de datos activos.
Supongamos que tiene una tabla de frutas como la que se muestra en la siguiente captura de pantalla y desea calcular el promedio de todas las celdas de la columna Cantidad, excluyendo los ceros. A continuación, le presentamos varios métodos prácticos para lograrlo en Excel, ofreciéndole flexibilidad tanto si sus datos están dispersos en celdas no adyacentes, distribuidos en múltiples filas o columnas, como si prefiere usar funciones integradas de Excel o fórmulas sencillas.
- Promediar celdas no adyacentes excluyendo ceros con una fórmula
- Promediar celdas no adyacentes excluyendo ceros con una macro VBA
- Promediar celdas no adyacentes excluyendo ceros mediante una función integrada de Excel
Promediar celdas no adyacentes excluyendo ceros con una fórmula
Este método emplea una fórmula matricial para calcular rápidamente el promedio de celdas no adyacentes —por ejemplo, cada celda alternativa en una fila o columna— excluyendo los ceros presentes entre las celdas seleccionadas. Es ideal cuando sus celdas objetivo siguen un patrón con intervalo fijo, y resulta especialmente eficaz para trabajar con datos periódicos, como valores de días alternos, o información distribuida a intervalos regulares aunque no esté directamente contigua.
Limitaciones: Este enfoque es ideal cuando las celdas no adyacentes siguen un intervalo constante. Para selecciones arbitrarias o manuales, consulte el método basado en fórmulas que aparece a continuación.
1. Seleccione una celda vacía donde desee que aparezca el resultado e introduzca la siguiente fórmula matricial:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Luego pulse las teclas Ctrl + Mayús + Introsimultáneamente (para versiones anteriores de Excel; en Excel 365 o 2019, basta con pulsar)Intro, ya que admiten matrices dinámicas).
Nota: En la fórmula, C2 y G2 representan la primera y la última celda del rango deseado. El número «2» indica el intervalo entre columnas; ajústelo si sus celdas no adyacentes están separadas por una cantidad distinta de columnas. Adapte el rango (C2:G2) y el intervalo (2) según la disposición de sus datos.
Consejos:
- Asegúrese de que el rango seleccionado incluya todas las celdas no contiguas que desea promediar.
- Si su selección de datos no se basa en intervalos regulares, utilice la siguiente solución basada en fórmulas para celdas arbitrarias.
- Esta fórmula ignora automáticamente las celdas vacías y los valores cero, ofreciéndole el promedio real de los puntos de datos significativos.
- Para evitar errores, asegúrese de que sus referencias de celda sean correctas y de haber pulsado la combinación de teclas adecuada para fórmulas matriciales en versiones anteriores de Excel.
Promediar celdas no adyacentes excluyendo ceros con una macro VBA
Si necesita calcular rápidamente el promedio de un grupo de celdas no adyacentes omitiendo los ceros, y su selección no sigue un patrón fijo —por ejemplo, desea elegir manualmente un conjunto específico de celdas distribuidas en distintas filas y columnas—, una macro VBA le brinda una solución flexible y potente. El código recorrerá las celdas seleccionadas y calculará el promedio excluyendo aquellas que contengan cero.
Este método es excelente para:
- Procese rápidamente grandes volúmenes de datos o selecciones irregulares.
- Guarde su lógica personalizada para reutilizarla en conjuntos de datos similares.
- Supera las limitaciones de las fórmulas tradicionales de hojas de cálculo en selecciones complejas.
1. Haga clic en Desarrollador > Visual Basic; en la ventana que se abre, haga clic en Insertar > Módulo y pegue el siguiente código en el módulo:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Haga clic en el 
botón Ejecutar o pulse la tecla F5en el editor de VBA. Aparecerá un cuadro de diálogo solicitándole que seleccione sus celdas. Utilice el ratón y la tecla Ctrlpara hacer clic y seleccionar las celdas no adyacentes que desee promediar. Haga clic en Aceptary la macro mostrará el resultado, excluyendo los ceros.
Promediar celdas no adyacentes excluyendo ceros mediante una función integrada de Excel
Excel también ofrece métodos integrados para realizar esta tarea con poca o ninguna intervención mediante fórmulas, ideales para cálculos rápidos o inspecciones visuales puntuales.
Utilice la Barra de estado:
- Mantenga pulsada la tecla Ctrl y use el ratón para seleccionar manualmente cada celda no adyacente que desee promediar, omitiendo aquellas que contengan valores cero.
- Una vez seleccionadas, observe la Barra de estado de Excel en la parte inferior derecha de la ventana: el valor del promedio se mostrará automáticamente si ha seleccionado más de una celda.
- Este método es ideal para comprobaciones visuales y conjuntos de datos pequeños, aunque no genera un resultado directamente en una celda de la hoja de cálculo.
Al elegir un método adecuado, tenga en cuenta el tamaño de sus datos, el grado de dispersión de las celdas seleccionadas y si necesita un resultado repetible mediante fórmula o simplemente una comprobación visual rápida. Si encuentra errores (como)#¡DIV/0!), asegúrese de que haya al menos un valor distinto de cero en sus selecciones. Si utiliza VBA, guarde siempre su libro de trabajo antes de ejecutar nuevas macros para evitar la pérdida accidental de datos.
Seleccionar rápidamente filas o columnas con intervalo (no adyacentes) en Rango limitado en Excel:
La función Kutools para Excel de Seleccionar Filas/Columnas a Intervalos le permite seleccionar fácilmente filas o columnas en intervalos dentro de un rango limitado en Excel, tal como se muestra en la siguiente captura de pantalla. ¡Resulta especialmente útil antes de aplicar fórmulas o macros, ya que le ayuda a seleccionar exactamente las celdas que desea procesar!
Kutools para Excel: potencie Excel con más de 300 herramientas esenciales, agilice y simplifique su trabajo, y aproveche las funciones de IA para un procesamiento de datos más inteligente y una mayor productividad.Consígalo ahora
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