Вычисляемое поле в сводной таблице разделить один столбец на другой EXCEL

Question

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

Когда я вставляю, я вставляю вычисляемое поле по следующей формуле, оно дает общую стоимость, а не среднее значение. Вы увидите, что поля являются промежуточными (стоимость) и WO#(рабочий заказ)

И вот как выглядит вывод в моей сводной таблице.

Answer 1

Когда вы добавляете вычисляемое поле в сводную таблицу, вам нужно добавить только ссылку, а не вычисление внутри нее, поэтому вам не нужно добавлять сумму или количество в ваше определение. Тем не менее, Excel работает с вычисляемыми полями очень раздражающим способом - сначала он добавляет ваши значения, а затем выполняет вычисления - если, например, у меня есть вычисляемое поле, которое просто field3=field2/field1, когда я хочу отобразить сумму этих значений, а не sum(field3), оно делает sum(field2)/sum(field1)

Я бы порекомендовал сделать этот расчет за пределами пивота.

Например, посмотрите мои результаты, когда у меня есть следующая таблица в качестве входных данных для сводной

Answer 2

У меня возникла та же проблема, и я нашел нужный мне ответ. Как и OP, я хочу вычислить среднее значение - SUM(поле 1), деленное на COUNT(поле 2), - но проблема в том, что в одной формуле есть две функции (SUM, деленная на COUNT). Как мы уже видели, использование нескольких функций в одном расчете приводит к непредвиденным результатам.

Ключ, который работал для меня, состоял в том, чтобы создать новое поле (поле 3) в исходных данных с формулой, которая присваивает 1 элементам, которые я хочу считать, и 0 элементам, которые я не хочу считать, поэтому count этого столбца просто сумма. Таким образом, я конвертирую COUNT(поле 2) в знаменателе в SUM (поле 3). Итак, теперь мне нужно получить результат SUM, деленный на SUM, одну и ту же функцию сверху и снизу, которую может обрабатывать Excel. И, к счастью для меня в этой ситуации, "бесстрастная манера" вычислений в Excel - именно то, чего я хочу.

Как сказал Фернандо, рассчитанное поле должно относиться только к самому полю; он не должен использовать SUM или COUNT или что-либо еще. Требуемая функция будет применена, когда вы добавите поле в сводную таблицу и выберете нужную функцию.

Я установил для своей вычисляемой функции значение [поле 1 / поле 3] с оператором IF, чтобы избежать деления на 0, и я использовал функцию SUM, когда помещал вычисляемое поле в сводную таблицу. Конечным результатом является SUM (поле 1) / SUM(поле 3), что равно SUM (поле 1) / COUNT(поле 2)

Резюме:

1. Переформулируйте свою формулу так, чтобы одна и та же функция использовалась во всех полях; например, найти способ пересчитать среднее (SUM/COUNT) значение SUM/SUM или COUNT/COUNT и т. д.
2. Добавьте поля к необработанным данным, которые помогут в пересмотренной формуле; например, если ваша пересчитанная формула использует СУММУ вместо СЧЕТА, создайте новое поле в необработанных данных, которое присваивает 1 и 0 так, чтобы сумма этого нового поля была равна количеству другого поля.
3. Создайте вычисляемое поле сводной таблицы, в котором используются поля, соответствующие пересчитанной формуле, включая новое поле, которое вы только что создали; не используйте SUM или COUNT на этом этапе.
4. Добавьте вычисляемое поле в область данных сводной таблицы и выберите нужную функцию; эта функция будет применяться к каждому полю, указанному в формуле вычисляемого поля.