¿Cómo calcular la media por día, mes, trimestre u hora con una tabla dinámica en Excel?

La función Tabla dinámica de Excel es una herramienta práctica para resumir y analizar datos, especialmente cuando necesita calcular promedios de forma rápida en periodos temporales discretos como días, meses, trimestres u horas. El enfoque que se describe a continuación elimina la necesidad de filtrado manual y cálculos repetitivos, ofreciéndole un resumen interactivo que puede ajustar fácilmente si sus datos cambian.

1. Seleccione toda su tabla de datos original (incluidos los encabezados) y, a continuación, haga clic en la pestaña Insertar > Tabla dinámica.

2. En el cuadro de diálogo Crear tabla dinámica que aparece, elija Hoja de cálculo existente si desea colocar su resumen en la hoja activa. Establezca la Ubicación seleccionando una celda donde desee que aparezca la tabla dinámica y, a continuación, haga clic en Aceptar.
Nota: para mostrar la tabla dinámica en una hoja de cálculo nueva, seleccione la opción Hoja de cálculo nueva. Asegúrese de que la ubicación elegida no se superponga con datos existentes para evitar advertencias de sobrescritura.

3. En el panel Lista de campos de tabla dinámica (normalmente aparece a la derecha), arrastre su columna de Fecha/Hora al área de Filasy la columna de datos numéricos ()Importe) al área de Valores. Esta configuración inicial agrega sus datos por cada marca de tiempo registrada.

4. Para organizar sus resultados por periodos específicos, haga clic con el botón derecho en cualquier entrada de fecha de la Tabla Dinámica y seleccione Agrupar en el menú contextual. Esta función le permite consolidar los datos en intervalos como días, meses, trimestres u horas.

5. En el cuadro de diálogo Agrupar, elija su periodo de agrupación preferido marcando la casilla correspondiente en el campo Por(por ejemplo,)Meses). Haga clic en Aceptar para aplicar. A continuación, haga clic con el botón derecho en el valor Suma del importe, seleccione Resumir valores por > Promedio. La Tabla Dinámica muestra ahora el valor promedio para cada grupo de periodo temporal, lo que permite una comparación y análisis inmediatos.

Si necesita actualizar el promedio basado en periodos con nuevos datos, simplemente actualice la tabla dinámica desde la pestaña Analizar > Actualizar. En resumen, las tablas dinámicas ofrecen una forma eficiente y visual de calcular y revisar promedios en intervalos temporales definidos, aunque pueden requerir una reconfiguración manual si cambia su estructura de agrupación o si el formato de sus datos no es coherente.

Si con frecuencia necesita calcular promedios para periodos específicos —como diarios, semanales, mensuales o anuales—, especialmente a partir de conjuntos de datos horarios detallados, el proceso manual de agrupación y cálculo se vuelve repetitivo y propenso a errores. Kutools para Excel ofrece utilidades especializadas que simplifican esta tarea, como las funciones A valor real y Combinar filas avanzado, que optimizan el formato de fechas y permiten la agregación por lotes, ahorrándole una gran cantidad de tiempo.

1. Seleccione las celdas que contienen fecha y hora y, a continuación, aplíqueles el formato correspondiente al periodo deseado. Por ejemplo, para obtener promedios diarios, seleccione sus datos y vaya a Inicio > Formato de número > Fecha corta. Esta acción de conversión de tiempo transforma las marcas de tiempo en valores de fecha únicamente.

Nota: para calcular promedios por semana, mes o año, Kutools para Excel ofrece Aplicar formato de fecha y A valor real para convertir marcas de tiempo al formato necesario con solo unos clics. Así garantizas una agrupación coherente y un cálculo preciso.

¡Obtenga ahora una prueba gratuita de 30 días con todas las funciones!

2. Seleccione todo su conjunto de datos (incluidas las fechas formateadas y los valores) y, desde la Cinta de opciones de Excel, vaya a Kutools > Contenido > Combinar filas avanzado.

3. En el cuadro de diálogo que se abre, seleccione su columna Fecha/Hora en la lista, márquela como Clave principal, luego elija su columna de valores (por ejemplo, Importe) y configúrela para que Calcule > Promedio. Confirme haciendo clic en Aceptar y Kutools calculará al instante los promedios para cada fecha distinta.

Los promedios para los periodos temporales especificados se calculan al instante, lo que agiliza el análisis. Si su agrupación de fechas muestra meses o años en lugar de días, los resultados se agregarán en consecuencia. También puede volver a formatear fechas mediante Kutools > Formato > Aplicar formato de fecha y finalizar con Kutools > A valor real.

Para usuarios que prefieren realizar cálculos directos mediante fórmulas sin recurrir a tablas dinámicas ni complementos, las funciones integradas de Excel como PROMEDIO.SI.CONJUNTO, SUMAR.SI.CONJUNTO y CONTAR.SI.CONJUNTO ofrecen un enfoque flexible basado en celdas para calcular promedios en periodos específicos. Este método es ideal cuando necesitas cálculos personalizados, quieres evitar actualizaciones de tablas o prefieres que los resultados aparezcan directamente junto a tus datos.

A continuación se muestra un ejemplo de cómo usar fórmulas para calcular valores promedio por cada día:

1. Suponga que sus datos incluyen fechas en la columna A (A2:A100) y valores numéricos en la columna B (B2:B100). En una nueva columna (por ejemplo, en la celda C2), introduzca la siguiente fórmula para calcular el promedio correspondiente a un día concreto (por ejemplo, la fecha de la celda A2):

=AVERAGEIFS(B$2:B$100, A$2:A$100, A2)

2. Pulse Entrar para aplicar la fórmula. Para calcularla en todas las fechas, copie la fórmula hacia abajo junto a sus datos.
Consejo: si desea que los promedios diarios aparezcan solo una vez por fecha única, ordene o filtre primero sus datos para obtener fechas únicas y, a continuación, aplique la fórmula en consecuencia.

Para usuarios que procesan habitualmente conjuntos de datos muy grandes o necesitan repetir cálculos de promedio para distintos periodos, automatizar su flujo de trabajo con macros VBA puede aumentar considerablemente la coherencia y la eficiencia. Las macros pueden agrupar y calcular promedios por día, mes, trimestre u hora, eliminando completamente la repetición manual. Este enfoque es ideal para usuarios avanzados de Excel y escenarios en los que los cálculos deben ejecutarse frecuentemente o adaptarse a nuevas hojas.

1. Para empezar, abra el editor de VBA haciendo clic en Herramientas para desarrolladores > Visual Basic. Cuando aparezca la ventana de Microsoft Visual Basic para Aplicaciones, haga clic en Insertar > Módulo y copie el siguiente código en el módulo:

Sub AverageByPeriod()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim periodCol As String, valueCol As String
    Dim dict As Object
    Dim cell As Range
    Dim periodKey As String
    Dim i As Long, lastRow As Long
    Dim sumDict As Object, countDict As Object
    
    Set ws = ActiveSheet
    periodCol = "A" ' Date/Time column
    valueCol = "B" ' Value column
    lastRow = ws.Cells(ws.Rows.Count, periodCol).End(xlUp).Row
    
    Set dict = CreateObject("Scripting.Dictionary")
    Set sumDict = CreateObject("Scripting.Dictionary")
    Set countDict = CreateObject("Scripting.Dictionary")
    
    For i = 2 To lastRow
        ' Grouping by month example; change to format for day/hour/quarter if needed
        periodKey = Format(ws.Cells(i, periodCol).Value, "yyyy-mm")
        
        If Not dict.Exists(periodKey) Then
            dict.Add periodKey, dict.Count + 1
            sumDict.Add periodKey, ws.Cells(i, valueCol).Value
            countDict.Add periodKey, 1
        Else
            sumDict(periodKey) = sumDict(periodKey) + ws.Cells(i, valueCol).Value
            countDict(periodKey) = countDict(periodKey) + 1
        End If
    Next i
    
    ws.Cells(1, 4).Value = "Period"
    ws.Cells(1, 5).Value = "Average"
    
    i = 2
    Dim k As Variant
    For Each k In dict.Keys
        ws.Cells(i, 4).Value = k
        ws.Cells(i, 5).Value = sumDict(k) / countDict(k)
        i = i + 1
    Next k
End Sub

2. Tras pegar el código, haga clic en el botón para ejecutarlo. La macro leerá sus datos (de las columnas A y B, a partir de la fila 2), los agrupará según el período elegido —actualmente configurado por mes— y mostrará la media de cada grupo en las columnas D y E.

Consejos:

• Para agrupar por día, modifique la línea Formato(..., "aaaa-mm-dd").
• Para agrupar trimestralmente, utilice:clavePeriodo = "Q" & WorksheetFunction.RoundUp(Month(ws.Cells(i, periodCol).Value) /[[PH_45]]) & "-" & Año(ws.Celdas(i, colPeriodo).Valor)
• Asegúrese siempre de que sus columnas ()colPeriodo, colValor) coincidan con la estructura de sus datos.

Precauciones:

• Si obtiene un error o resultados en blanco, compruebe que no haya celdas vacías ni valores no válidos como fechas en su columna de agrupación.
• Ajuste la asignación de columnas según sea necesario: si sus datos comienzan en columnas distintas de A y B, actualice colPeriodo y colValor en consecuencia.
• Asegúrese de guardar su trabajo antes de ejecutar macros para evitar modificaciones no deseadas en los datos.

Media por año/mes/fecha en Excel

Media de marcas horarias por día en Excel

Media semanal en Excel