Получить среднее значение по группе

Question

Предположим, у меня есть следующие данные:

Id          SiteId          Converted
--          ------          ---------
1           1               TRUE
2           1               FALSE
3           1               TRUE
4           2               TRUE
5           3               FALSE
6           3               TRUE

Как я могу получить среднее количество конвертированных по SiteId

Количество на сайт будет:

SiteId          Count
------          -----
1               3
2               1
3               2

И конечный результат, который я хочу, это:

Converted   Average
---------   -------
TRUE        1.33 (4 converted / 3 sites)
FALSE       0.67 (2 not converted / 3 sites)

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

Answer 1

Разбейте его на две отдельные проблемы:

1. Сколько Converted значений ИСТИНА? Сколько ЛОЖЬ?
2. Сколько уникальных значений SiteId ?

Первая проблема проста:

Count of TRUE =COUNTIF(C:C,TRUE)
Count of FALSE =COUNTIF(C:C,FALSE)

Вторая проблема сложнее, но все же хорошо известна: (Это предполагает, что все ваши значения SiteId являются числовыми. Если это не так, обратитесь к этой странице справки Office для поиска альтернатив.)

Count of unique SiteID values =SUM(IF(FREQUENCY(B:B,B:B)>0,1))

Теперь все, что вам нужно сделать, это объединить их.

TRUE    =COUNTIF(C:C,TRUE)/SUM(IF(FREQUENCY(B:B,B:B)>0,1))
FALSE   =COUNTIF(C:C,FALSE)/SUM(IF(FREQUENCY(B:B,B:B)>0,1))