Вопрос или проблема
Я пытался это сделать, но у меня не получилось. У меня есть дамп данных в таблице Excel (Excel 2010). В первом столбце указаны месяцы. Во втором – даты. В третьем столбце находятся числовые данные. Я применил фильтры к каждому столбцу, чтобы получить данные для сводной таблицы. Есть записи для каждой даты. Иногда бывают несколько записей для одной даты. Теперь, когда я запускаю сводную таблицу, я получаю правильную сумму, но когда я выбираю среднее, среднее значение является точным только для единичных записей, связанных с датой. Мне нужно вычислить среднее значение для нескольких записей данных за одну дату. Вот пример:
Ноябрь 11/1/13 30
Ноябрь 11/2/13 25
Ноябрь 11/3/13 20
Ноябрь 11/3/13 25
Теперь, когда я запускаю сводную таблицу и выбираю среднее значение данных, она вычисляет среднее как (30+25+20+25)/4 = 20 (поскольку есть 4 записи, сумма делится на 4)
Это нежелательно.
Мне нужно, чтобы расчет происходил так, чтобы 3-я и 4-я записи принимались за одну для даты 11/3/13. Таким образом, требуемый вывод: (30+25+(20+25))/3 = 33.33.
Надеюсь, этот пример дает четкое представление о требовании. Мне нужно решение для этого. Как мы можем изменить сводную таблицу так, чтобы среднее вычислялось не только на основе количества записей, а также среднее вычислялось по количеству дней.
Ваши предложения приветствуются.
Вам необходимо добавить столбец к исходным данным, который вычисляет уникальные даты для каждого руководителя. Введите эту формулу в ячейку G2:
=IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)
Скопируйте вниз. Назовите столбец DistinctDates. Перейдите в сводную таблицу и измените источник данных, чтобы включить столбец G. Обновите сводную таблицу. Это важно, поэтому я повторяю: обновите сводную таблицу!
Теперь создайте вычисляемое поле. В инструментах сводной таблицы нажмите на выпадающее меню для Полей, Элементов и Наборов и создайте вычисляемое поле.
Назовите поле MyAverage и введите формулу
='Products passed for packaging' /DistinctDates
Добавьте это поле в сводную таблицу как суммируемое поле. Строки теперь будут отображать итоги по элементам, но итоговая строка будет показывать средние значения на основе количества дней, а не на основе количества единиц.
На скриншоте я включил промежуточные итоги для руководителей, которые отображаются в той же строке.
Ответ или решение
Для того чтобы корректно вычислить среднее значение данных с несколькими записями для одной даты в сводной таблице Excel, необходимо внести небольшие изменения в исходные данные и использовать расчётные поля. Вот пошаговая инструкция, как это сделать:
Шаг 1: Добавление столбца для уникальных дат
-
Добавьте новый столбец в исходные данные, назовите его, например, "УникальныеДаты".
-
В ячейку G2 (или в первую ячейку нового столбца) введите следующую формулу:
=IF(SUMPRODUCT(($B$2:$B2=B2)*($C$2:$C2=C2))>1,0,1)Эта формула проверяет, является ли текущая запись первой для данной комбинации даты и другого критерия (например, продукта).
-
Скопируйте формулу вниз по столбцу, чтобы заполнить все строки в вашем наборе данных.
Шаг 2: Обновление источника данных в сводной таблице
- Выделите сводную таблицу и перейдите в Настройки сводной таблицы.
- Измените источник данных, чтобы включить новый столбец "УникальныеДаты".
- Обновите сводную таблицу. Это важно, чтобы изменения вступили в силу.
Шаг 3: Создание расчетного поля
-
В меню инструментов сводной таблицы выберите Поле, элементы и наборы и выберите Расчетное поле.
-
Назовите новое поле (например, "МоеСреднее") и введите следующую формулу:
='СуммарныеПродажи' / УникальныеДатыЗдесь ‘СуммарныеПродажи’ – это название поля с вашим числовым значением, которое нужно усреднить.
-
Добавьте это поле в сводную таблицу как сумму.
Шаг 4: Проверка и вывод результатов
- Теперь в строках сводной таблицы будут показываться итоговые значения, а в итоговой строке будут отображаться средние значения, рассчитанные по числу уникальных дат, а не по количеству записей.
- Убедитесь, что в сводной таблице включены подитоги, чтобы результаты были наглядны.
Пример:
Рассматривая ваш пример:
- Датa | Значение
- 11/1/13 | 30
- 11/2/13 | 25
- 11/3/13 | 20
- 11/3/13 | 25
После добавления столбца "УникальныеДаты" и выполнения вышеприведённых шагов, среднее значение будет рассчитано как:
[
(30 + 25 + (20 + 25)) / 3 = 33.33
]
Следуя этим шагам, вы сможете правильно вычислить среднее значение для нескольких записей на одну дату в Excel. Если возникнут дополнительные вопросы или потребуется помощь, не стесняйтесь обращаться.