¿Cómo calcular el promedio de una columna en Excel según criterios establecidos en otra columna?
En muchos escenarios prácticos de Excel, suele ser necesario calcular el promedio de los valores de una columna agrupados o filtrados según coincidencias en otra columna. Por ejemplo, es posible que desee determinar las ventas medias por comercial o por región, tal como se muestra en la siguiente captura de pantalla. Este tipo de cálculo se utiliza comúnmente en informes resumen, análisis de rendimiento y revisiones de datos. En este artículo presentamos varios métodos eficaces para lograr este resultado, garantizando que pueda elegir el enfoque que mejor se adapte a sus necesidades y nivel de competencia.
Calcular el promedio en una columna en función del mismo valor en otra columna mediante fórmulas
Calcular el promedio en una columna en función del mismo valor en otra columna con Kutools para Excel
Calcular el promedio por grupo mediante Tabla Dinámica
Automatizar el cálculo de promedios agrupados con macro VBA
Calcular el promedio en una columna en función del mismo valor en otra columna mediante fórmulas
Uno de los métodos más sencillos para calcular el promedio de un grupo según los valores de otra columna en Excel es usar fórmulas condicionales, como PROMEDIO.SI o PROMEDIO.SI.CONJUNTO. Este enfoque resulta especialmente útil cuando necesitas obtener promedios específicos basados en ciertos criterios, como las ventas medias de una ciudad o comercial determinado.
1. Seleccione una celda vacía donde desee mostrar el resultado, introduzca la siguiente fórmula y pulse Intro:
=AVERAGEIF(B2:B13,E2,C2:C13)
Explicación de los parámetros: En la fórmula anterior, B2:B13 es el rango que contiene los criterios a comprobar (por ejemplo, la ciudad o el comercial), E2 es el valor específico con el que desea comparar (como «Owenton») y C2:C13 es el rango que contiene los valores numéricos cuyo promedio desea calcular.
Tras pulsar Intro, obtendrá de inmediato el promedio del grupo especificado en E2 (por ejemplo, las ventas medias de «Owenton»).
Si necesita calcular el promedio de cada valor único en la columna de criterios, simplemente modifique el valor en la celda de criterios (E2) según corresponda o copie la fórmula hacia abajo si cuenta con una lista de entradas únicas.
Consejo práctico: En conjuntos de datos grandes o con numerosos grupos únicos, combinar esta fórmula con una lista de valores únicos —obtenida mediante herramientas como «Eliminar duplicados» o la función ÚNICO de Excel en Office 365 y Excel 2021— puede acelerar el cálculo simultáneo de todos los promedios por grupo. Asegúrese siempre de que los rangos de su fórmula abarquen todos los datos deseados y permanezcan alineados al copiarla.
Errores habituales y solución de problemas:
- Si obtiene un error #¡DIV/0!, asegúrese de que el valor de sus criterios aparezca realmente en el rango seleccionado.
- Asegúrese de que su rango numérico contenga exclusivamente números válidos, ya que las celdas con texto o vacías podrían afectar el cálculo.
Calcular el promedio en una columna en función del mismo valor en otra columna con Kutools para Excel
Si desea calcular automáticamente el promedio de todos los valores únicos de una columna sin tener que introducir fórmulas repetidamente ni filtrar manualmente, Kutools para Excel le ofrece una solución optimizada. Ideal para listas extensas o conjuntos de datos complejos.
1. Seleccione todo el rango de datos que incluya tanto la columna de agrupación como la columna numérica cuyo promedio quiera calcular. A continuación, vaya a Kutools > Combinar y dividir > Combinar filas avanzado.
2. En el cuadro de diálogo Combinar filas basado en columna, proceda del siguiente modo:
- Seleccione la columna por la que desea agrupar (por ejemplo, Ciudad o Comercial) y haga clic en el botón Clave principal para definirla como campo de agrupación.
- Seleccione la columna numérica que desee promediar y, a continuación, haga clic en Calcular > Promedio.
Sugerencia: Para cualquier otra columna (como fechas), puede especificar cómo combinar sus valores (por ejemplo, unirlos con una coma). - Haga clic en Aceptar para procesar la operación.
Kutools agrupará al instante los datos según la clave seleccionada y mostrará el promedio de cada grupo en la columna numérica.
El procesamiento por lotes de Kutools es ideal para usuarios que analizan regularmente estadísticas agrupadas, como informes mensuales, resúmenes departamentales u otros cálculos multigrupo. Al ser un complemento, Kutools no altera la estructura original de sus datos, y su función de vista previa le permite revisar las agrupaciones antes de aplicar los cambios.
Notas y consejos:
- Asegúrese de que su rango seleccionado no contenga filas vacías antes de ejecutar la herramienta.
- Si necesita los promedios agrupados en otro lugar, use Copiar y Pegar para transferir los resultados una vez realizado el cálculo.
- En conjuntos de datos muy grandes, asegúrese de que las columnas de agrupación y las numéricas estén correctamente asignadas para evitar confusiones.
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
Calcular el promedio por grupo mediante Tabla Dinámica
Las tablas dinámicas ofrecen una forma potente y nativa de resumir, agrupar y analizar datos —incluido el cálculo de promedios por grupo— sin necesidad de fórmulas ni complementos de terceros. Esta técnica es ideal cuando se busca una vista interactiva de promedios y totales por distintas categorías, y resulta adecuada tanto para conjuntos de datos pequeños como para los más grandes.
Cómo configurar una Tabla Dinámica para calcular promedios por grupo:
- Seleccione cualquier celda de su conjunto de datos y, a continuación, vaya a Insertar > Tabla dinámica. En el cuadro de diálogo, elija dónde desea que aparezca la tabla dinámica (en una hoja nueva o en una hoja de cálculo existente) y haga clic en Aceptar.
- En el panel Campos de tabla dinámica, arrastre la columna por la que desea agrupar al área Filas (por ejemplo, «Ciudad» o «Comercial»).
- Arrastre la columna numérica cuyo promedio desee calcular (por ejemplo, «Ventas») al área Valores. De forma predeterminada, Excel podría calcular la suma; para cambiarlo, haga clic en el campo de valor, seleccione Configuración de campo de valores y elija Promedio.
Su tabla dinámica mostrará de inmediato el valor promedio de cada grupo. Podrá filtrar, ordenar y dar formato al informe rápidamente según sus necesidades. Este método es intuitivo y elimina el riesgo de errores en las fórmulas.
Ventajas: Es interactiva, gestiona grandes volúmenes de datos con elegancia y puede resumir múltiples estadísticas simultáneamente.
Inconvenientes: El resultado se muestra en formato de tabla dinámica, no como una lista sencilla; además, requiere actualizarse ocasionalmente si cambian los datos de origen.
Consejo: Al hacer doble clic en cualquier celda de resumen de la Tabla Dinámica, se abre una hoja de cálculo nueva con los datos subyacentes de ese grupo, lo que facilita auditar los detalles o resolver discrepancias.
Problemas habituales:
- Si no puede obtener el promedio, asegúrese de que el campo de valor esté configurado como «Promedio» en la Configuración de campo de valores.
- Revise sus datos de origen en busca de filas en blanco o columnas adicionales que puedan causar problemas de diseño en la tabla dinámica.
Automatizar el cálculo de promedios agrupados con macro VBA
Para usuarios que necesitan calcular con frecuencia promedios de múltiples grupos o desean generar resúmenes de forma automática, crear una macro en VBA puede suponer un ahorro considerable de esfuerzo manual. VBA resulta especialmente útil cuando se espera que la estructura de los datos permanezca constante o cuando se quieren generar informes resumen repetidamente con un solo clic.
Antes de comenzar, asegúrese de guardar su libro de trabajo y de habilitar las macros. Así es como puede empezar:
1. Haga clic en Herramientas para desarrolladores > Visual Basic para abrir el editor de VBA. En el editor, haga clic en Insertar > Módulo para crear un nuevo módulo de código. Pegue el siguiente código en el módulo:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Tras insertar el código, cierre el editor de VBA. Vuelva a Excel, pulse Alt+F8, seleccione GroupAverageSummary en la lista y haga clic en Ejecutar. La macro le pedirá que seleccione la columna de grupos (criterios) y la columna de valores (numérica). Una vez realizadas las selecciones, generará automáticamente una hoja de cálculo nueva denominada «Group Average Summary» con cada grupo único y sus valores promedio correspondientes.
Notas sobre parámetros y funcionamiento:
- Asegúrese de que sus columnas de grupo y de valores tengan la misma longitud y contengan datos válidos (es decir, evite selecciones parciales).
- Esta macro puede adaptarse para realizar agrupaciones más avanzadas o incluir estadísticas resumen adicionales según tus necesidades.
- Si su hoja ya incluye un resumen titulado «Resumen de promedio por grupo», la macro creará una hoja de cálculo con el nombre predeterminado «Nombre de la hoja de cálculo».
Resolución de problemas:
- Si recibe un mensaje como «subíndice fuera de intervalo» o similar, asegúrese de que sus rangos seleccionados coincidan correctamente y estén ubicados en la misma hoja de cálculo.
- Para obtener los mejores resultados, asegúrese de que la columna de valores contenga exclusivamente datos numéricos: la macro omitirá cualquier celda con texto o vacía dentro del rango numérico.
Esta macro es ideal para procesamiento por lotes, generación de informes automatizados o situaciones en las que deba resumir con frecuencia conjuntos de datos nuevos o actualizados.
Demostración: Calcular el promedio en una columna según un mismo valor en otra columna con Kutools para 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