Нахождение среднего значения данных в Excel 2021 для аналогичных данных первого столбца

Question

У меня огромный объем данных, и следующее изображение является частью моих данных:

Первый столбец всегда является натуральным числом (не обязательно последовательным), и для строк, которые имеют одинаковые данные первого столбца, я хочу найти среднее значение каждого из его следующих столбцов. Например, первые три ячейки в первом столбце равны 142, и я хочу найти среднее значение следующих трех ячеек каждого из следующих столбцов. Поскольку у меня много данных, я хочу сделать этот процесс автоматическим. Я попробовал формулу "СРЗНАЧЕСЛИ", но безрезультатно. Можете ли вы мне помочь?

Answer 1

Учитывая, что вы предоставили свои данные только в виде изображения, я создал некоторые случайные данные в следующем диапазоне A1:E17:

А	Б	С	Д	Э
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

Теперь вы вводите, например, в ячейку G2и H2следующие формулы соответственно; AVERAGEIF()формулу можно повторить для дополнительных столбцов:

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

Теперь некоторые дополнительные пояснения; UNIQUE()функция может быть использована для извлечения уникальных идентификаторов. Эта функция возвращает массив, который затем может быть использован для того, AVERAGEIF()чтобы он автоматически выпадал. Функция SORT()используется для размещения значений идентификаторов в порядке возрастания. Для функции AVERAGEIF()вы сначала указываете диапазон, в котором вы хотите искать идентификатор, например, в вашем столбце A. Затем вы указываете критерии, которые являются вашим массивом, который мы получили с помощью, UNIQUE()и, наконец, вы указываете столбец, по которому вы хотите вычислить среднее значение.

Вывод будет выглядеть следующим образом:

ИДЕНТИФИКАТОР	Средн.
137	0,41
138	0,57
139	0,17
140	0,55
141	0,61
142	0,42

---

Расширение для доступа ко всем средним значениям как к одному массиву и размещения всех формул в одной ячейке:

В зависимости от варианта использования, вам может быть интересно получить доступ ко всем средним значениям как к одному массиву. Чтобы добиться этого, вы можете обернуть свои AVERAGEIF()функции в HSTACK(). Таким образом, формула простирается вниз из-за UNIQUE()функции и вправо из-за HSTACK()функции. Таким образом, вы также имеете все формулы в одной ячейке, что также может иногда быть проще в обслуживании:

=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)
)

Предполагая, что формула все еще находится в ячейке H2, вы можете получить доступ ко всему массиву следующим образом:

=SUM(H2#)

---

Расширение на основе комментария для включения динамического диапазона:

Чтобы добиться этого, вы можете использовать LET()следующее утверждение:

=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)
)

Сначала вы указываете свой "последний" столбец с данными. Количество строк автоматически вычисляется на основе столбца A, поэтому вам нужно убедиться, что у вас нет никаких данных ниже тех данных, которые вы хотите использовать. Все остальные аспекты аналогичны тем, что у нас были раньше, просто заключены в оператор LET(), который позволяет вам динамически получать доступ к диапазону данных через, INDEX()поскольку он может хранить промежуточные результаты.

Этот подход позволит достичь того, о чем вы просили. Однако, предостережение: он не обязательно хорошо масштабируется для многих столбцов, поскольку прямо сейчас он требует повторения формулы для каждого столбца. Теоретически, должно быть возможно сделать это измерение более динамичным, но это выходит за рамки этого расширения здесь.

Answer 2

В Ф2:=AVERAGE(IF($A$2:$A$17=$A2,B$2:B$17))

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

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

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

Примечание: Это формула массива. В более ранних версиях Excel CTRL+SHIFT+ENTERдля работы формулы необходимо нажать . В текущей версии Excel можно просто нажать Enter.