¿Cómo calcular el promedio de cada 5 filas o columnas en Excel?
Al trabajar con grandes conjuntos de datos en Excel, es común necesitar cálculos de promedios para cada grupo de filas o columnas, como cada 5 filas o cada 5 columnas. Aunque podrías insertar manualmente fórmulas como =AVERAGE(A1:A5), =AVERAGE(A6:A10), =AVERAGE(A11:A15), y así sucesivamente, esto se vuelve rápidamente impráctico si tu lista contiene cientos o miles de celdas. Repetir estas operaciones manualmente es tanto tedioso como propenso a errores. Afortunadamente, Excel ofrece múltiples formas de automatizar esta tarea, haciendo que el análisis de datos sea mucho más eficiente y menos tedioso. Este artículo presentará varios métodos prácticos para calcular el promedio de cada 5 filas o columnas, incluidos enfoques basados en fórmulas, complementos de Excel, automatización VBA y técnicas de Tabla Dinámica, para ayudarte a elegir la mejor solución para tu escenario.
Promediar cada 5 filas o columnas con fórmulas
Promediar cada 5 filas con Kutools para Excel
Promediar cada 5 filas o columnas con código VBA
Promediar cada 5 filas con Tabla Dinámica
Promediar cada 5 filas o columnas con fórmulas
Si prefieres usar fórmulas estándar de Excel, puedes automatizar el cálculo para cada 5 filas o columnas sin necesidad de complementos o scripts. Este enfoque es particularmente adecuado para conjuntos de datos estáticos donde simplemente necesitas generar grupos de promedios para respaldar tu análisis. Sin embargo, ten cuidado al hacer referencia correctamente a tus datos y manejar cualquier intervalo vacío o irregular.
El siguiente ejemplo demuestra cómo calcular el promedio de cada 5 filas en una columna:
1. Introduce la siguiente fórmula en la primera celda donde deseas el resultado (por ejemplo, C2):
=AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,))Aquí, A2 es la celda inicial de tu columna de datos, C2 es la celda de salida de la fórmula y 5 es el intervalo (número de filas para promediar). Asegúrate de ajustar estas referencias según tu conjunto de datos real.
Después de introducir la fórmula, presiona Enter. Se mostrará el primer resultado promediado. Ver captura de pantalla:
2. Selecciona la celda con la fórmula, luego arrastra el controlador de relleno hacia abajo hasta que encuentres un valor de error (como #DIV/0! si hay menos de 5 valores en los datos restantes). Esto recuperará automáticamente los promedios para cada grupo de 5 filas. Ver captura de pantalla:
Consejos y notas: Puedes usar funciones de manejo de errores como IFERROR() para suprimir valores de error si tus datos no se ajustan a grupos perfectamente delimitados, por ejemplo:
=IFERROR(AVERAGE(OFFSET($A$2,(ROW()-ROW($C$2))*5,,5,)),"")Para calcular el promedio de cada 5 columnas en una fila, aplica la siguiente fórmula (coloca en A3 y arrastra hacia la derecha):
=AVERAGE(OFFSET($A$1,,(COLUMNS($A$3:A3)-1)*5,,5))Aquí, A1 es la celda inicial, A3 es la celda de salida de tu fórmula y 5 es el número de columnas en cada grupo. Ajusta las referencias de celdas según sea necesario para tu diseño de datos.
Después de introducir la fórmula y presionar Enter, arrastra el controlador de relleno hacia la derecha hasta llegar a un valor de error. Ver captura de pantalla:
Este método basado en fórmulas es ideal para cálculos rápidos únicos o cuando no quieres usar herramientas adicionales. Sin embargo, conforme cambia el tamaño o forma de tus datos, puede ser necesario ajustar las fórmulas o actualizar manualmente los rangos de celdas, y manejar grupos incompletos puede requerir cuidado extra.
Promediar cada 5 filas con Kutools para Excel
Kutools para Excel proporciona una solución gráfica conveniente si necesitas promediar grupos de filas con frecuencia sin manejar fórmulas complejas. A través de las funciones Insertar Saltos de Página Cada Fila e Informes de Subtotales, puedes segmentar rápidamente tus datos y calcular promedios por lotes en solo unos clics. Este método es especialmente útil si deseas aplicar promedios sobre intervalos repetibles y visualizar agrupamientos directamente en tu hoja de cálculo.
Después de descargar e instalar Kutools para Excel, sigue estos pasos:
1. Haz clic en Kutools Plus > Imprimir > Insertar Saltos de Página Cada Fila. Ver captura de pantalla:
2. En el cuadro de diálogo Insertar Saltos de Página Cada Fila, especifica el intervalo (por ejemplo, 5) para insertar un salto de página después de cada 5 filas. Esto permite que Kutools segmente automáticamente tus datos. Ver captura de pantalla:
3. Luego, haz clic en Kutools Plus > Imprimir > Estadísticas de la Página de Datos. Ver captura de pantalla:
4. En el cuadro de diálogo Estadísticas de la Página de Datos, selecciona la(s) columna(s) que deseas promediar, luego elige Promedio como método de cálculo. Ver captura de pantalla:
5. Haz clic en Aceptar, y Kutools insertará instantáneamente filas de subtotales con valores promedio en cada intervalo de 5 filas. Ver captura de pantalla:
¡Descarga y prueba gratis Kutools para Excel ahora!
Kutools hace que la agrupación y análisis repetitivo de datos sea fácil de completar sin necesidad de ajustar fórmulas ni escribir ningún script. Sin embargo, ten en cuenta que los saltos de página insertados pueden afectar el diseño de impresión y las vistas, así que puedes querer eliminarlos después de usarlos si no son necesarios para tu informe.
Promediar cada 5 filas o columnas con código VBA
Si necesitas calcular repetidamente el valor promedio para cada número fijo de filas o columnas en grandes conjuntos de datos o conjuntos de datos en constante cambio, automatizar este proceso con VBA puede ahorrarte una cantidad significativa de trabajo manual. VBA te permite recorrer tus datos, agruparlos según sea necesario y generar el resultado promedio para cada lote. Este método es especialmente adecuado para usuarios avanzados o aquellos que manejan bloques de datos dinámicos, y evita que las fórmulas congestionen tu hoja de cálculo. A continuación se muestra una macro VBA de propósito general que puedes adaptar fácilmente.
Automatizar el promedio de cada 5 filas:
1. Haz clic en Desarrollador > Visual Basic para abrir la ventana Microsoft Visual Basic para Aplicaciones. Luego, haz clic en Insertar > Módulo y pega el código a continuación en el módulo:
Sub AverageEvery5Rows()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Integer, i As Integer, j As Integer
Dim LastRow As Long, StartRow As Long
Dim SumValue As Double, CountValue As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set DataRange = Application.InputBox("Select the data range to average (single column)", xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output", xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
LastRow = DataRange.Rows.Count
StartRow = 1
i = 0
Do While StartRow <= LastRow
SumValue = 0
CountValue = 0
For j = 0 To GroupSize - 1
If (StartRow + j) <= LastRow Then
SumValue = SumValue + DataRange.Cells(StartRow + j, 1).Value
CountValue = CountValue + 1
End If
Next j
If CountValue > 0 Then
OutputCell.Offset(i, 0).Value = SumValue / CountValue
Else
OutputCell.Offset(i, 0).Value = ""
End If
StartRow = StartRow + GroupSize
i = i + 1
Loop
End Sub2. Para ejecutar el código, haz clic en el 
botón o presiona F5. Selecciona tu rango de datos (una columna), luego elige la celda inicial para la salida y especifica el tamaño del grupo (por ejemplo, 5). La macro generará el promedio para cada conjunto de 5 filas, uno debajo del otro en la columna de salida especificada.
Puedes usar una macro similar para promediar cada 5 columnas en una fila.
Automatizar el promedio de cada 5 columnas:
Sub AverageEveryNColumns()
Dim DataRange As Range
Dim OutputCell As Range
Dim GroupSize As Long
Dim totalCols As Long, totalRows As Long
Dim startCol As Long, endCol As Long, outCol As Long
Dim v As Variant
Dim r As Long, c As Long
Dim sumVal As Double, cntVal As Long
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set DataRange = Application.InputBox("Select the data range (single rows)", _
xTitleId, Selection.Address, Type:=8)
Set OutputCell = Application.InputBox("Select the first cell for output (results will spill to the right)", _
xTitleId, , Type:=8)
GroupSize = Application.InputBox("Enter group size (e.g. 5)", xTitleId, 5, Type:=1)
On Error GoTo 0
If DataRange Is Nothing Or OutputCell Is Nothing Then Exit Sub
If GroupSize < 1 Then
MsgBox "Group size must be >= 1.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
Application.EnableEvents = False
Dim prevCalc As XlCalculation
prevCalc = Application.Calculation
Application.Calculation = xlCalculationManual
totalCols = DataRange.Columns.Count
totalRows = DataRange.Rows.Count
v = DataRange.Value
outCol = 0
For startCol = 1 To totalCols Step GroupSize
endCol = startCol + GroupSize - 1
If endCol > totalCols Then endCol = totalCols
sumVal = 0
cntVal = 0
For r = 1 To totalRows
For c = startCol To endCol
If Not IsEmpty(v(r, c)) Then
If IsNumeric(v(r, c)) Then
sumVal = sumVal + CDbl(v(r, c))
cntVal = cntVal + 1
End If
End If
Next c
Next r
If cntVal > 0 Then
OutputCell.Offset(0, outCol).Value = sumVal / cntVal
Else
OutputCell.Offset(0, outCol).Value = ""
End If
outCol = outCol + 1
Next startCol
CleanExit:
Application.Calculation = prevCalc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Promediar cada 5 filas con Tabla Dinámica
Otra forma práctica de calcular promedios de grupo cada 5 filas es utilizando una Tabla Dinámica, combinándola con una numeración de secuencia o columna de índice para agrupar los datos. Este método es particularmente útil para usuarios que trabajan con datos estructurados en formato de tabla y necesitan un resumen rápido e interactivo sin escribir fórmulas ni usar complementos. Una Tabla Dinámica maneja dinámicamente los cambios de datos y admite agrupaciones flexibles, lo cual es excelente para grandes conjuntos de datos o tareas de informes regulares.
Aquí está cómo realizar esta operación usando una columna auxiliar y una Tabla Dinámica:
1. Agrega una columna "Índice" o "Grupo" junto a tus datos para marcar cada grupo de 5 filas. En la primera fila de datos (B2), introduce:
=INT((ROW()-ROW($A$2))/5)+1Esta fórmula etiqueta cada fila en secuencia, asignando el mismo número de grupo a cada 5 filas. Completa esta fórmula hacia abajo junto a tu conjunto de datos.
2. Selecciona tus datos y la nueva columna Índice, luego haz clic en Insertar > Tabla Dinámica. En el cuadro de diálogo de creación de Tabla Dinámica, confirma tu rango de datos y elige dónde colocar la Tabla Dinámica.
3. En la lista de campos de la nueva Tabla Dinámica creada, arrastra el campo "Grupo" al área de Filas, y tu campo de valor (por ejemplo, "Ventas") al área de Valores.
4. Haz clic en la lista desplegable en el área de Valores, elige Configuración de Campo de Valor y selecciona Promedio.
Ahora, tu Tabla Dinámica muestra el promedio para cada 5 filas de los datos originales, agrupados convenientemente por la columna auxiliar.
Los beneficios clave del método de Tabla Dinámica son su flexibilidad y actualizaciones fáciles cuando cambian tus datos de origen. Sin embargo, requiere agregar una columna auxiliar y puede no ser adecuado para situaciones donde los datos deben permanecer precisamente formateados o inalterados.
Artículos relacionados:
¿Cómo promediar los últimos 5 valores de una columna a medida que ingresan nuevos números?
¿Cómo promediar los 3 valores superiores o inferiores en Excel?
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos