Excel Averageifs с несколькими условиями в одном столбце

Question

Я ищу некоторую помощь для того, чтобы привести в порядок вторую формулу, выделенную жирным шрифтом ниже. Я рассчитываю рассчитать среднее значение 'Pupil_Score' (это также моя первая попытка с именованными диапазонами) на основе расы, я хочу объединить несколько групп, в данном случае расы 1 и 2. Я хотел бы продолжать использовать формулу Averageifs, так как она кажется короче моего текущего решения, как видно из первой формулы. Позже я добавлю больше условий, которые только увеличат формулу в текущем формате SUM/COUNT, покажутся непрактичными.

= AVERAGEIFS(Pupil_Score, гонки, 3)

=(SUMIFS(Pupil_Score, Race, 1)+SUMIFS(Pupil_Score, Race, 2)) / (COUNTIFS (Race, 1)+COUNTIFS (Race, 2))

Я получаю разные результаты из ответов каждого, ниже приведены данные, с которыми я тестирую, может быть, это может помочь. Моя формула дала 99,632, затем другие дали ошибку #Value или 99,614 или 99,493.

Раса Оценка учеников 1 98,880 1 99,588 1 99,751 1 99,780 1 100,070 2 99,765 2 98,873 2 99,013 2 99,529 2 99,600 2 99,500 2 99,751 2 99,501 2 99,543 2 99,795 2 99,857 2 99,874 2 99,875 2 99,67 2 99,755 2 99,893 2 100,050

Answer 1

Критерии AVERAGEIFS как условия AND . OR условие ИЛИ, поскольку ни одна строка не может быть как 1, так и 2 .

        

Выше сумма сине-зеленых равна 23, а их количество равно 5, что составляет в среднем 4,6. Формула в E2:

=SUMPRODUCT((C2:C8)*(B2:B8=F1:G1))/SUMPRODUCT(--(B2:B8=F1:G1))

... или с цветами, закодированными как,

=SUMPRODUCT((C2:C8)*(B2:B8={"Blue","Green"}))/SUMPRODUCT(--(B2:B8={"Blue","Green"}))

Для ваших номеров, жестко закодированная версия будет выглядеть так:

=SUMPRODUCT((C2:C8)*(B2:B8={1, 2}))/SUMPRODUCT(--(B2:B8={1, 2}))

Answer 2

Единственный способ сохранить простую формулу - это найти критерии, которые не являются взаимоисключающими. Например,

=AVERAGEIFS(Pupil_Score,Race,">=2",Race,"<=3")

Это работает только в том случае, если номера рас соседствуют.

Другие предложили формулы массива (CSE), поэтому я не буду повторять это. Эти формулы имеют тенденцию быть довольно сложными. Если вы хотите, чтобы все было просто, вы можете попытаться создать столбец фильтра в ваших исходных данных, например =OR(race=1;race=3) и использовать его в своих ifs. Вы даже можете использовать их в сочетании с номерами рас, если критерии не являются взаимоисключающими.

Answer 3

Вы можете использовать формулу массива AVERAGE , например,

=AVERAGE(IF((Race=1)+(Race=2),Pupil_Score))

подтверждено с помощью CTRL+SHIFT+ENTER

«+» Фактически дает вам "ИЛИ"

Вы можете легко добавить дополнительные условия, добавив функции IF, например, предполагая, что "range1" должен быть "x"

=AVERAGE(IF((Race=1)+(Race=2),IF(range1="x",Pupil_Score)))

Смотрите пример здесь

Answer 4

=
sum( sumifs( columntoavg, criteriacolumn, {"1","2","3"}))
/
sum( countifs(  criteriacolumn, {"1","2","3"}))

Я думаю, что это самый чистый и гибкий способ решить эту проблему.