Функции рабочих листов сводной таблицы Excel 2010
Можно применять любую функцию рабочего листа, не использующую в качестве аргументов ссылки на ячейки или именованные объекты. На самом деле можно использовать любую функцию рабочего листа, не требующую обязательного использования ссылок на ячейки или именованные объекты. В эту категорию попадают такие функции, как СЧЁТ (COUNT), СРЗНАЧ (AVERAGE), ЕСЛИ (IF), И (AND), НЕТ (NOT) и ИЛИ (OR).
Константы
В вычислениях, производимых в сводных таблицах, можно использовать любые константы. Константы представляют собой статические значения, которые не изменяются во времени. Например, в следующей формуле число 5 является константой: [Продано единиц]*5. Хотя значение Продано единиц может изменяться на основе доступных данных, число 5 всегда будет сохраняться.
Ссылки на итоговые суммы
Формулы, с помощью которых выполняются вычисления, не могут ссылаться на промежуточные суммы сводной таблицы или итоговые значения. Другими словами, нельзя использовать результат вычисления промежуточной или итоговой суммы в качестве переменной или аргумента в вычисляемом поле.
Специальные правила для вычисляемых полей
Расчеты в вычисляемых полях всегда выполняются по отношению к итоговым суммам, а не к индивидуальным элементам данных. Выражаясь проще, программа Excel всегда вычисляет поля данных, промежуточные суммы и итоговые суммы перед оценкой вычисляемого поля. Это означает, что ваше вычисляемое поле всегда применяется к итогам для исходных данных. Пример на рис. 5.28 демонстрирует, как это может повлиять на анализ данных.
Рис. 5.28. Несмотря на то что вычисляемое поле справедливо для отдельных элементов данных, для промежуточных сумм его применять математически некорректно
В каждом квартале вам нужно получить итоговые объемы продаж для каждого товара, для чего количество проданных единиц товара умножается на цену единицы этого товара. Если вы вначале просмотрите данные за первый квартал, то сразу же определите проблему. Вместо расчета суммы 220+150+220+594, которая равна 1184, сумма количества единиц товара умножается на сумму цен на товары, в результате чего возвращается неверное значение. Как видно на рис. 5.29, причиной проблемы становится включение в анализ данных для целого года.
Рис. 5.29. Итоговая сумма для года в целом абсолютно неправильна
К сожалению, эту проблему решить невозможно, но ее можно обойти. В наихудшем варианте необходимо изменить настройки сводной таблицы для исключения промежуточных и итоговых сумм, а затем вычислить новые итоговые суммы. На рис. 5.30 показан пример реализации такого сценария.
Рис. 5.30. Расчет новой итоговой суммы поможет предотвратить появление некорректных данных в отчете