Рассчитать средний балл по категориям в Excel

Question

Предположим, у меня есть следующие 2 листа в книге Excel:

Листовые companies:

     A       B       C
1 COMPANY COUNTRY  SCORE
2 Foo     Greece    10
3 Bar     Greece     2
4 Baz     Greece     3
5 Foobar  Japan      8
6 Foobaz  Denmark    6
7 BazBar  Denmark    7

Листовые countries:

     A        B
1 COUNTRY AVG_SCORE
2 Denmark    6.5
3 Greece     5.0
4 Japan      8.0

Я подсчитал средний балл по стране (AVG_SCORE) вручную. Он рассчитывается путем суммирования всех баллов по странам и деления на количество наблюдений. Например, в Дании есть 2 компании, общая сумма баллов 6 + 7 , которые поделены на 2 - 6.5 .

Так как на первом листе у меня тысячи компаний, мне нужна формула для стран сотовой связи !B2 (и ниже) для расчета средних баллов по стране.

Учитывая, что данные компании находятся на листе, отличном от средних баллов, по какой формуле можно автоматически рассчитать AVG_SCORES ?

Answer 1

Если у вас Excel 2007+, вы можете использовать AVERAGEIF . Используя ваши данные в качестве шаблона, вы хотели бы поместить это в столбец B вкладки ваших countries :

=AVERAGEIF(companies!$B$2:$B$7,"=" & A2,companies!$C$1:$C$7)

Если у вас 2003 или более ранняя версия , вы можете использовать SUMPRODUCT:

=SUMPRODUCT((companies!$B$2:$B$7=A2)*companies!$C$2:$C$7)/SUMPRODUCT((companies!$B$2:$B$7=A2)*1)

---

РЕДАКТИРОВАТЬ в ответ на комментарий:

& Является объединением. то есть "a" & "b" == "ab" . В этом случае указанным критерием является строка, поэтому мы используем заключенный в кавычки "=" со страной, которую мы хотим найти. Значение находится в ячейке A2 , поэтому вместо того, чтобы вводить его, мы связываем "=" со значением ячейки с "=" & A2 , что совпадает с "=Denmark" .

Answer 2

Другим вариантом может быть создание сводной таблицы, выбор страны в поле строки и значение avg в качестве поля значения. Затем вы можете связать свой второй лист с созданной выше сводной таблицей.