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

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

Приоритет выполнения операторов

Как и в электронных таблицах, в формулах вычисляемых элементов и полей можно использовать любой оператор, т.е. любой символ, представляющий выполняемое вычисление (+, *, /, %, Л и т.д.). Более того, как сообщает форум про excel, подобно электронным таблицам, вычисления в сводных таблицах выполняются в соответствии с приоритетом операторов.

При выполнении вычисления, в котором комбинируется несколько операторов, например (2+3)*4/50%, программа Excel оценивает выражение и проводит расчет в определенном порядке. Знание этого порядка убережет вас от многих ошибок. Итак, порядок выполнения операций в Excel следующий:

• обработка выражений в круглых скобках;
• обработка диапазонов (:);
• обработка пересечений (областей);
• обработка объединений (;);
• выполнение операции отрицания;
• преобразование процентных значений (например, 50% преобразуется в .50);
• возведение в степень (А);
• умножение (*) и деление (/) — эти операции имеют равный приоритет;
• сложение (+) и вычитание (-) — эти операции имеют равный приоритет;
• объединение текстовых данных (&) — эта операция называется конкатенацией;
• выполнение операций сравнения (=, <>, <=, >=).

Равнозначные операторы в одном выражении всегда выполняются в порядке следования (слева направо).

Рассмотрим простой пример. Как известно, выражение (2+3)*4 возвращает результат 20. Тем не менее, если вы удалите скобки и оставите выражение 2+3*4, то Excel выполнит следующие вычисления: 3*4=12+2=14. Порядок выполнения операторов требует, чтобы программа Excel умножала значения перед сложением. При вводе выражения 2+3*4 вы получите неверный результат. Поскольку программа Excel вначале оценивает и выполняет все вычисления в круглых скобках, только заключение выражения 2+3 в круглые скобки гарантирует получение правильного ответа.

Рассмотрим другой пример. Если вы введете в качестве формулы выражение 10А2, т.е. укажете возвести число 10 в квадрат, то программа Excel вернет в качестве ответа значение 100. Если же вы введете выражение -10 А2, то будете ожидать возврата значения -100. Однако Excel опять вернет значение 100. Причина в том, что программа выполняет операцию отрицания перед возведением в степень, т.е. значение 10 вначале преобразуется в -10, а результат выполнения выражения -10*-10 действительно равен 100. Использование круглых скобок в формуле — (10^2) гарантирует, что программа вначале выполнит возведение в степень, и только потом — операцию отрицания, возвратив ожидаемый результат -100.

Ссылки на ячейки и именованные диапазоны

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