¿Cómo calcular el promedio según el día de la semana en Excel?
En Excel, a menudo se presentan escenarios en los que necesita calcular el valor promedio de una lista de números en función del Semana asociado a cada entrada. Por ejemplo, podría querer analizar datos de ventas para hallar el número promedio de pedidos los lunes, en días laborables o en fines de semana. Este requisito es habitual en informes de ventas, seguimiento del rendimiento o cualquier situación que implique análisis basado en el tiempo. A continuación encontrará varias soluciones prácticas para calcular promedios por Semana mediante fórmulas, código VBA y Tabla Dinámica. Estas soluciones le ayudan a obtener información útil y comprender mejor sus Distribución de datos según los patrones del calendario.
↳ Día de la semana específico (por ejemplo, lunes)
↳ Días laborables (lun.–vie.)
↳ Fines de semana (sáb.–dom.)
➤ Automatizar el cálculo del promedio por Día de la semana con macro VBA
➤ Tabla Dinámica: Agrupar y calcular el promedio por día de la semana sin fórmulas
Calcular el promedio según Día de la semana con fórmulas
Calcular el promedio según un Día de la semana específico
Para calcular el promedio de los valores correspondientes a un día específico de la semana, como todos los lunes, puede utilizar fórmulas matriciales de Excel o la función SUMAPRODUCTO. Esta técnica resulta especialmente útil para resumir tendencias diarias y analizar patrones de eventos. Por ejemplo, si necesita obtener el promedio de pedidos exclusivamente para los lunes en su conjunto de datos, siga este método:
Introduzca la siguiente fórmula en una celda vacía:
=AVERAGE(IF(WEEKDAY(D2:D15)=2,E2:E15))A continuación, pulse simultáneamente Ctrl + Mayús + Entrar. Esto indica que la fórmula es matricial, lo que permite a Excel procesar cada fila de forma individual y obtener así el resultado correcto.
Notas y explicación:
- D2:D15 es su lista de fechas. Asegúrese de que contengan valores de fecha válidos en Excel.
- 2 representa el lunes. Los números para la semana son: domingo = 1, lunes = 2, martes = 3, miércoles = 4, jueves = 5, viernes = 6 y sábado = 7.
- E2:E15 es el rango de números que desea promediar, como recuentos de pedidos, ventas u otras métricas similares.
Consejos:
- Si su versión de Excel admite fórmulas de matriz dinámica (Office 365 o posterior), puede introducir la fórmula directamente sin necesidad de usar Ctrl + Mayús + Entrar.
- Compruebe que no haya celdas vacías ni con valores que no sean fechas para evitar errores en la fórmula.
Como alternativa más flexible, puede utilizar SUMAPRODUCTO para obtener el mismo resultado. Este método no requiere introducir una fórmula matricial y es ideal para conjuntos de datos grandes:
=SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)=1)*1)Después de introducir esta fórmula en una celda, pulse Entrar. Aquí, D2:D15 es su rango de fechas, E2:E15 es su rango de datos y 1 representa el lunes (cuando se usa el segundo argumento de WEEKDAY como 2, lo que hace que lunes = 1, martes = 2, ..., domingo = 7).
Calcular el promedio según los días laborables
Para calcular el valor promedio correspondiente a los días laborables (de lunes a viernes) en sus datos, puede usar la siguiente fórmula matricial:
=AVERAGE(IF(WEEKDAY(D2:D15,2)={1,2,3,4,5},E2:E15))Introduzca esta fórmula en una celda vacía y confírmela pulsando Ctrl + Mayús + Entrar.
Notas y explicación:
- Este cálculo obtiene el promedio exclusivamente de las filas cuya fecha corresponde a un día laborable, es decir, de lunes a viernes.
- Asegúrese de que los valores de fecha en D2:D15 sean válidos; de lo contrario, WEEKDAY podría no devolver los resultados esperados.
Consejos:
- Si prefiere evitar la introducción de fórmulas matriciales, utilice la alternativa con SUMAPRODUCTO que se muestra a continuación.
- Asegúrese de que la columna de fechas contenga fechas reales de Excel (no texto).
Otra forma de lograrlo es mediante una fórmula de SUMAPRODUCTO:
=SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)<6)*1)Simplemente escriba esta fórmula y pulse Entrar. ¡Promediará los valores cuyos días de la semana sean anteriores al sábado, es decir, de lunes a viernes!
Calcular el promedio según los fines de semana
Para calcular únicamente el promedio de los valores correspondientes a fines de semana (sábado y domingo), use la siguiente fórmula matricial:
=AVERAGE(IF(WEEKDAY(D2:D15,2)={6,7},E2:E15))Introdúzcala en una celda vacía y confírmela con Ctrl + Mayús + Entrar.
Notas y explicación:
- Esta fórmula selecciona las fechas en las que WEEKDAY devuelve 6 o 7 (sábado o domingo) cuando el segundo argumento es 2.
Consejos:
- Para conjuntos de datos grandes, la alternativa con SUMAPRODUCTO que se muestra a continuación puede resultar más rápida y evita la necesidad de introducir fórmulas matriciales.
- Confirme que los valores de filas en blanco o las fechas no válidas se gestionen correctamente para evitar promedios inesperados.
Una opción más rápida con SUMAPRODUCTO, que funciona sin entrada matricial:
=SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*E2:E15)/SUMPRODUCT((WEEKDAY(D2:D15,2)>5)*1)Como siempre, verifique los valores de fecha y asegúrese de que no haya celdas vacías para garantizar la precisión de sus resultados.
Código VBA – Automatice el cálculo de promedios por Día de la semana con una macro
Para usuarios que busquen un enfoque totalmente automatizado, especialmente aquellos con grandes volúmenes de datos o que requieran actualizaciones frecuentes, puede utilizar VBA para recorrer sus datos, agrupar las entradas por día de la semana y calcular los promedios correspondientes a cada uno. Este método es ideal si desea eliminar ajustes manuales de fórmulas y generar de forma rápida un resumen por día de la semana.
Ventajas: Elimina pasos manuales, genera un resumen completo y se puede personalizar para un procesamiento adicional.
Inconvenientes: Requiere habilitar macros y tener conocimientos básicos de VBA; puede no ser adecuado para hojas altamente dinámicas o basadas en la nube.
Operación:
1. Haga clic en Herramientas para desarrolladores > Visual Basic para abrir el editor de VBA. En la ventana, seleccione Insertar > Módulo y pegue el siguiente código en el nuevo módulo:
Sub AverageOrdersByWeekday()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Dim cell As Range
Dim ws As Worksheet
Dim datesRange As Range, valuesRange As Range
Dim i As Long, dayKey As String
Dim sumArr(1 To 7) As Double
Dim countArr(1 To 7) As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = Application.ActiveSheet
Set datesRange = Application.InputBox("Select the date range", xTitleId, Selection.Address, Type:=8)
Set valuesRange = Application.InputBox("Select the corresponding values range", xTitleId, "", Type:=8)
For i = 1 To datesRange.Count
If IsDate(datesRange.Cells(i).Value) Then
Dim wd As Integer
wd = Weekday(datesRange.Cells(i).Value, 2)
sumArr(wd) = sumArr(wd) + valuesRange.Cells(i).Value
countArr(wd) = countArr(wd) + 1
End If
Next i
Dim resWs As Worksheet
Set resWs = Worksheets.Add
resWs.Name = "Weekday Averages"
resWs.Cells(1, 1).Value = "Weekday"
resWs.Cells(1, 2).Value = "Average"
Dim dayNames As Variant
dayNames = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
For i = 1 To 7
resWs.Cells(i + 1, 1).Value = dayNames(i - 1)
If countArr(i) > 0 Then
resWs.Cells(i + 1, 2).Value = sumArr(i) / countArr(i)
Else
resWs.Cells(i + 1, 2).Value = "No data"
End If
Next i
End Sub2. Para ejecutar la macro, haga clic en el botón 
o pulse F5. A continuación, aparecerá un cuadro de diálogo para que seleccione el rango de fechas (por ejemplo, D2:D15) y el rango de valores correspondiente (por ejemplo, E2:E15).
La macro crea una hoja de cálculo nueva que resume el promedio para cada día de la semana: se calculará el «Promedio» de lunes a domingo y, si algún día no tiene datos asociados, se mostrará «Sin datos».
Precauciones y consejos:
- Asegúrese de que ambos rangos —fechas y valores— tengan la misma longitud y estén perfectamente alineados fila por fila.
- MacroPara ejecutarla, debes guardar el libro como un archivo habilitado para macros (*.xlsm).
- Si encuentra un error, asegúrese de que sus rangos no incluyan celdas vacías o datos no válidos.
- Puede modificar el código para incluir un filtro de días de la semana específicos o ampliar el resumen.
Tabla Dinámica – Use Tabla Dinámica para agrupar fechas por día de la semana y calcular promedios sin fórmulas
Otro método para analizar y calcular el promedio de sus datos por semana consiste en usar una tabla dinámica. Este enfoque es fácil de usar y no requiere fórmulas manuales ni programación. Las tablas dinámicas le permiten agrupar datos dinámicamente, calcular promedios y actualizar rápidamente los resultados cuando sus datos cambien.
Ventajas: Configuración rápida, compatibilidad con grandes conjuntos de datos, actualización automática al añadir nuevos datos y soporte para análisis avanzados (por ejemplo, filtrado y ordenación).
Inconvenientes: Requiere que los datos estén organizados en una tabla de Excel o en un rango estructurado y ofrece una personalización más limitada en comparación con las soluciones de VBA.
Pasos de la operación:
1.Añada una columna auxiliar con los nombres de los días de la semana:
En una columna vacía (por ejemplo,)F), introduzca en F2:
=TEXT(D2,"dddd")Copie la fórmula hacia abajo para que coincida con sus filas de datos. (Supone que las fechas están en)D2:D15.)
2.Seleccione su rango de origen, incluida la columna auxiliar (por ejemplo,)D2:F15). Para obtener los mejores resultados, conviértalo en una tabla de Excel (Ctrl+T) y mantenga la selección activa.
3. Vaya a Insertar > Tabla dinámica. En el cuadro de diálogo Crear tabla dinámica, elija dónde colocarla (se recomienda)Hoja de cálculo nueva) y haga clic en Aceptar.
4. En el panel Campos de la tabla dinámica:
— Arrastre el campo auxiliar Día de la semana (columna F) al área de Filas.
— Arrastre su campo numérico (por ejemplo,)Pedidos de la columna E) al área de Valores.
5. Cambie la agregación a promedio:
Haga clic en la flecha desplegable del área de Valores > Configuración de campo de valores > elija Promedio > Aceptar.
6. (Opcional) Ordene los días de la semana de lunes a domingo:
Haga clic con el botón derecho en cualquier etiqueta de día de la semana > Ordenar > Más opciones de ordenación, o añada un pequeño auxiliar de orden personalizado (1–7) y ordénelo por este. También puede dar formato a los números mediante Configuración de campo de valores > Formato de número.
7. Actualice cuando cambien los datos:
Tras modificar la tabla de origen, haga clic en cualquier celda de la tabla dinámica y seleccione Actualizar(o vaya a)Datos > Actualizar todo).
Consejos y solución de problemas:
- Asegúrese de que la columna de fechas contenga fechas válidas de Excel (no texto); de lo contrario, la fórmula de día de la semana podría fallar.
- Si los promedios parecen incorrectos, verifique que el campo Valores esté configurado en Promedio, no en Suma.
- Después de modificar o agregar una fila en la tabla de origen, use Actualizar para que la tabla dinámica se vuelva a calcular.
- En configuraciones regionales que utilizan el punto y coma, introduzca
=TEXT(D2;"dddd")en su lugar.
Utilizar una tabla dinámica para el análisis por día de la semana simplifica el proceso y le permite crear informes interactivos, especialmente útiles en presentaciones o al compartir conclusiones con otras personas.
Descubra la magia de Excel con KUTOOLS AI
- Ejecución inteligente: Realice operaciones en celdas, analice datos y cree gráficos con comandos sencillos.
- fórmulas personalizadas: Cree fórmulas a medida para optimizar sus flujos de trabajo.
- Programación en VBA: Escriba e implemente código VBA con facilidad.
- Interpretación de fórmulas: Entienda las fórmulas complejas con facilidad.
- Traducción de texto: Rompa las barreras del idioma directamente en sus hojas de cálculo.
Artículos relacionados:
¿Cómo calcular el promedio entre dos fechas en Excel?
¿Cómo calcular el promedio de celdas según varios criterios en Excel?
¿Cómo calcular el promedio de los 3 valores superiores o inferiores en Excel?
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