Excel находит среднее значение столбца для каждой X строк данных

Question

У меня есть большой набор данных, заполненный несколькими точками данных. Я включил картинку, которая состоит из следующего:

    column A = IDs
    column B = Colors 
    column C = Data points need to average 

Мне нужно среднее значение только для первых 2 строк данных (столбец C), где идентификатор (столбец A) = 1 и цвет (столбец B) = красный. Тогда мне понадобится среднее значение для следующих 2 строк данных с теми же критериями. Тогда я бы сделал то же самое, чтобы найти среднее значение, где ID = 1 и Color = Blue. Тогда я бы сделал то же самое, где ID = 2 и Color = Red.

Обычно я делаю формулу среднего значения, но, поскольку мне нужно усреднить только первые X строк данных, я не уверен, как решить эту проблему. Еще одно предостережение: не каждый идентификатор имеет одинаковое количество точек данных. Таким образом, ID 1 Color Red может иметь 4 точки данных, а ID 2 Color Blue может иметь 6 точек данных. Любая помощь будет оценена.

Answer 1

Спасибо, Илиана, за разъяснения. Ключом к решению вашей проблемы является (образно) разделение ваших данных на группы по четыре строки. Тогда формула может усреднить два значения для каждого цвета в этой группе.

Вы объяснили, что данный идентификатор может содержать от 7 до 10 строк данных. Поэтому не все группы будут иметь четыре ряда. В следующем подходе используется вспомогательный столбец для назначения групп из четырех (или менее) строк для каждого идентификатора.

Таблица ниже содержит 7,8,9 и 10 строк, соответственно, для четырех указанных идентификаторов, и эта формула, заполненная с D2 в приведенной ниже таблице, делит данные для каждого идентификатора на группы по 4 или менее и обозначает группы с номером от 0 до 2:

=IF(COUNTIF(A$2:A2,A2)>8,2,IF(COUNTIF(A$2:A2,A2)>4,1,0))

Эта формула подсчитывает количество вхождений каждого идентификатора по мере его заполнения. Если счет> 8, он устанавливает значение помощника 2. Если счет> 4, помощник устанавливается на 1. Если число равно 4 или меньше, помощник устанавливается на 0.

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

=AVERAGEIFS(C$2:C$35,A$2:A$35,A2,B$2:B$35,B2,D$2:D$35,D2)

Наконец, дублирующиеся значения могут быть устранены с небольшим дополнительным усилием. Эта формула подсчитывает количество комбинаций ID/Helper в диапазоне, который "увеличивается" по мере заполнения формулы. Если число равно 3 или более, вместо среднего возвращается пустое значение.

=IF(COUNTIFS(D$2:D2,D2,A$2:A2,A2)<3, AVERAGEIFS(C$2:C$35,A$2:A$35,A2,B$2:B$35,B2,D$2:D$35,D2),"")

Еще раз спасибо за четкое объяснение того, что вы пытаетесь достичь. Я надеюсь, что вы найдете это полезным. Удачи.

Answer 2

Вы можете использовать комбинацию функций SUMIFS и COUNTIFS для вычисления AVERAGE для пар ID и цвета с точки зрения создания точек данных (Match Rows), где COUNTIFS создает точку данных

=SUMIFS(C2:C17,A2:A17,"1",B2:B17,"RED")/COUNTIFS(A2:A17,"1",B2:B17,"RED")


=SUMIFS(C2:C17,A2:A17,"1",B2:B17,"BLUE")/COUNTIFS(A2:A17,"1",B2:B17,"BLUE")


=SUMIFS(C2:C17,A2:A17,"2",B2:B17,"RED")/COUNTIFS(A2:A17,"2",B2:B17,"RED")

Я надеюсь, что это поможет вам.