Encontrar el promedio de los datos en Excel 2021 para datos similares de la primera columna

Question

Tengo una gran cantidad de datos y la siguiente imagen es parte de mis datos,

La primera columna es siempre un número natural (no necesariamente consecutivo) y para las filas que tienen los mismos datos de la primera columna quiero encontrar el promedio de cada uno de los datos de la siguiente columna. Por ejemplo, las primeras tres celdas de la primera columna son 142 y quiero encontrar el promedio de las tres celdas siguientes de cada columna siguiente. Como tengo muchos datos, quiero que este proceso sea automático. Probé la fórmula "PROMEDIOIF" pero sin resultado. ¿Podrías ayudarme?

Answer 1

Dado que proporcionaste tus datos solo como una imagen, creé algunos datos aleatorios de la siguiente manera en el rango A1:E17:

A	B	C	D	mi
142	0,02	0,19	0,83	0,14
142	0,65	0,61	0,38	0,82
142	0,60	0,34	0,36	0,37
141	0,98	0,95	0,23	0,97
141	0,83	0,24	0,50	0,67
141	0,02	0,14	0,33	0,23
140	0,33	0,14	0,85	0,64
140	0,78	0,09	0,17	0,06
139	0,08	0,70	0,26	0,26
139	0,25	0,16	0,35	0,67
138	0,52	0,44	0,18	0,44
138	0,21	0,93	0,04	0,23
138	0,66	0,33	0,72	0,39
138	0,91	0,91	0,51	0,30
137	0,39	0,60	0,28	0,46
137	0,43	0,81	0,41	0,14

Ahora, ingresa, por ejemplo, la celda G2y H2las siguientes fórmulas respectivamente; la AVERAGEIF()fórmula se puede repetir para las columnas adicionales:

=SORT(UNIQUE($A$2:$A$17))
=AVERAGEIF($A$2:$A$17,$G$2#,B2:B17)

Ahora una explicación adicional; la UNIQUE()función se puede utilizar para extraer las identificaciones únicas. Esta función devuelve una matriz, que luego se puede usar para AVERAGEIF()que se derrame automáticamente. La SORT()función se utiliza para poner los valores de ID en orden ascendente. Para la AVERAGEIF()función, primero especifica el rango donde desea buscar el ID, por ejemplo, en su columna A. A continuación, especifica el criterio, cuál es la matriz con la que obtuvimos UNIQUE()y, por último, especifica la columna sobre la que desea promediar.

La salida tendrá el siguiente aspecto:

IDENTIFICACIÓN	Promedio
137	0,41
138	0,57
139	0,17
140	0,55
141	0,61
142	0,42

---

Extensión para acceder a todos los promedios como una matriz y tener todas las fórmulas en una celda:

Dependiendo del caso de uso, es posible que le interese acceder a todos los promedios como una sola matriz. Para lograr esto, puede envolver sus AVERAGEIF()funciones dentro de un archivo HSTACK(). De esa manera, la fórmula se derrama hacia abajo debido a la UNIQUE()función y hacia la derecha debido a la HSTACK()función. De esa manera, también tendrá todas las fórmulas en una sola celda, lo que a veces también puede ser más fácil de mantener:

=HSTACK(
AVERAGEIF($A$2:$A$17,$G$2#,B2:B17),
AVERAGEIF($A$2:$A$17,$G$2#,C2:C17),
AVERAGEIF($A$2:$A$17,$G$2#,D2:D17),
AVERAGEIF($A$2:$A$17,$G$2#,E2:E17)
)

Suponiendo que la fórmula todavía está en la celda H2, puede acceder a la matriz completa de la siguiente manera:

=SUM(H2#)

---

Extensión basada en comentario para incluir rango dinámico:

Para lograr esto, podría utilizar una LET()declaración de la siguiente manera:

=LET(
    lastCol, "E",
    data, INDEX(A:A,2):INDEX(INDIRECT(CONCATENATE(lastCol, ":", lastCol)),
    MAX(IF(A:A<>"", ROW(A:A)))),
    id, SORT(UNIQUE(INDEX(data,2,1):INDEX(data,ROWS(data),1))),
    b, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,2)),
    c, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,3)),
    d, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,4)),
    e, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,5)),
    HSTACK(id,b,c,d,e)
)

Primero, especifica su "última" columna con datos. El número de filas se calcula automáticamente en función de la columna A, por lo que debe asegurarse de no tener ningún dato debajo de los datos que desea utilizar. Todos los demás aspectos son similares a los que teníamos antes, solo que están envueltos en una LET()declaración, que le permite acceder dinámicamente al rango de datos INDEX()porque puede almacenar resultados intermedios.

Este enfoque logrará lo que usted pidió. Sin embargo, la advertencia es que no necesariamente se adapta muy bien a muchas columnas, porque en este momento requiere que repita la fórmula para cada columna. En teoría, también debería ser posible dinamizar esta dimensión, pero esto va más allá del alcance de esta extensión.

Answer 2

En F2:=AVERAGE(IF($A$2:$A$17=$A2,B$2:B$17))

En G2:=AVERAGE(IF($A$2:$A$17=$A2,C$2:C$17))

En H2:=AVERAGE(IF($A$2:$A$17=$A2,D$2:D$17))

En I2:=AVERAGE(IF($A$2:$A$17=$A2,E$2:E$17))

Nota: Esta es una fórmula matricial. En versiones anteriores de Excel, debes presionar CTRL+SHIFT+ENTERpara que la fórmula funcione. La versión actual de Excel puede simplemente presionar Enter.