Как рассчитать среднее значение на основе повторяющихся групп?

Question

У меня есть таблица, которая содержит данные, записанные в разное время. Данные должны были быть записаны с 5-минутными интервалами, но это не всегда было так.

Поэтому, чтобы попытаться очистить это:

• Я установил столбец Standard Time, который содержит фиксированные 5-минутные интервалы.
• Столбец Введенное время содержит время, в которое данные были записаны.

• Столбец Округленное время содержит Введенное время, округленное до ближайшего 5-минутного интервала (формула из ячейки C2):

  =MROUND(B2, "0:05")
  

• Столбец Count содержит количество вхождений, которое появляется в каждом округленном времени (формула из ячейки F2):

  =COUNTIF($C$1:$C$19,C2)
  

• Столбец Duplicate/Unique является просто столбцом Count, но представлен либо "Duplicate", либо "Unique" (формула из ячейки G2):

  =IF(F2>1, "Duplicate", "Unique")
  

• В столбце Среднее количество данных за округленное время у меня возникают проблемы, поскольку я хочу усреднить данные для каждого стандартного времени. Например, на 14/01/2014 12:40 есть 2 дубликата, поэтому я хочу усреднить данные из столбца данных и вставить их в ячейку H2 . Другими словами, H2 будет содержать 23.18 . Как я мог этого добиться?

Я рассмотрел, возможно, с помощью комбинации индекс / матч, но не могу понять это.

Answer 1

Используя функцию AVERAGEIFS вы можете указать Excel усреднить данные на основе удовлетворяемых критериев.

Поместите эту формулу в H2 и последующие ячейки ниже.

=AVERAGEIFS(E:E,C:C,C2)

Вы получите среднее значение данных из столбца E в каждой строке столбца H на основе среднего значения повторяющихся значений в столбце с Rounded Time .

Примечание: мой формат даты / времени может не совпадать, но это не имеет значения.