Как рассчитать невзвешенные средние в сводной таблице Excel?

Question

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

Поскольку мои отдельные ячейки часто усредняются по разному количеству точек данных, строка итоговой итоговой суммы оказывается средневзвешенной, что меня не интересует. Поэтому я обычно делаю свою среднюю строку в нескольких строках над таблицей, чтобы использовать ее для своих графиков.

Это не слишком много работы, но есть другая проблема. Я часто добавляю данные из нескольких человек в источник сводных таблиц, а затем обновляю таблицы. Это означает, что моя средняя строка должна быть обновлена, чтобы охватить больше строк из сводной таблицы. С одной таблицей не так уж и много, но когда у меня их много на разных листах, мне нужно найти / заменить целую кучу формул.

Итак: есть ли способ автоматически получить невзвешенные средние значения столбца в сводной таблице, чтобы при обновлении таблицы средние значения

1. не меняйте места и
2. охватывать вновь добавленные (или удаленные) данные

Спасибо

Answer 1

Это немного глупо, но это работает. Если у вас есть одна «категория» (например, персона), вы можете просто использовать AverageIf в следующем примере. Если у вас есть несколько критериев (например, лицо, продукт или пол, или родной город), вам нужен AverageifS (множественное число - доступно только в Excel 2007).

Вам нужен вспомогательный столбец рядом с вашими данными. Здесь мы найдем:

• это первый случай этого человека (плюс второй, третий критерии)?
• если да, то каков средний показатель для этого человека?
• если нет, БЛАНК (не ноль).

Таким образом, если ваше имя находится в столбце A, ваше измеряемое значение находится в столбце B, строка 1 - заголовки, а строки от 2 до 1000 - данные, в ячейке C2 вам нужна формула, подобная этой:

=IF(COUNTIF($A$2:A2,A2)=1,AVERAGEIF($A$2:$A$1000,A2,$B$2:$B$1000),"")

Если у вас есть два критерия (например, человек и продукт, регион или что-то еще), расширьте его, используя COUNTIFS и AVERAGEIFS (ваше измеренное значение теперь находится в столбце C, и обратите внимание, что это становится первым аргументом среднего значения if, а не последним):

=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,AVERAGEIF($C$1:$C$1000,$A$2:$A$1000,A2,$B$2:$B$1000,B2),"")

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

Включите заголовок для этого дополнительного вспомогательного столбца ("Личное среднее"). Измените источник вашей сводной таблицы, чтобы включить эти дополнительные данные и обновить. Включите дополнительный столбец и убедитесь, что вы суммируете его по среднему, а не по сумме. Теперь ваши промежуточные итоги по категориям или общие итоги (если у вас нет категорий более высокого уровня) будут точными, поскольку в каждом случае среднее значение для каждого человека является просто средним для одного числа, которое уже рассчитано, поэтому вес был подавлено.

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