В предыдущем уроке мы рассмотрели какие операторы и символы могут применяться в формулах Excel. Немалое количество, и при таком их количестве очень важно знать в каком порядке Excel вычисляет введенные формулы.
Excel вычисляет формулы в следующем порядке:
- Сперва проводит операции с диапазонами, причём в определённом порядке: двоеточия, пробелы и затем точки с запятыми.
- Затем преобразует значения в отрицательную форму (то есть если вы написали -5, то Excel необходимо преобразовать 5 в -5) и далее преобразует проценты (например 10% в 0,1).
- Далее следуют математические операции (возведение в степень, за которым следуют деление и умножение, затем сложение и вычитание).
- Конкатенация (объединение тестовых строк).
- И, наконец, операции сравнения (=, <, >, <=, >=, <>).
Для изменения порядка вычислений в формуле можно использовать круглые скобки. Теория теорией, но давайте теперь разберёмся на примерах.
Порядок операций с диапазоном
Представим, что у нас есть магазин одежды. И в таблице Excel мы ведем простую статистику продаж по категориям и месяцам. Вот эта таблица:
Вы можете попрактиковаться со своими данными или скачать эту таблицу по ссылке.
Предположим, что мы хотим суммировать количество проданных рубашек и свитеров только за апрель. Для суммирования данных используем функцию СУММ:
=СУММ(B3:F3;B5:F5 E2:E7)
Как вы можете видеть, мы использовали в формуле сразу два оператора: объединения (точка с запятой) и пересечения (пробел). Это было сделано для суммирования данных только в ячейках E3 и E5, поскольку эти ячейки находятся на пересечении строк 3 (рубашки) и 5 (свитера) и столбца E (апрель). Однако, мы получаем совсем не тот результат, который ожидали:
Достаточно бегло взгляда, чтобы понять, что сумма 210 и 153 не может равняться 1024. В чём же наша ошибка? Для ответа на этот вопрос надо пройтись по последовательности, с которой Excel вычисляет формулы.
Мы уже описывали последовательность выше под п. 1. Сначала вычисляется оператор диапазона (:). Далее следует операция пересечения(пробел). И затем операция объединения (;). Давайте разберёмся ещё более подробнее и пошагово:
- Вычисляются диапазоны: {146,100,248,210,167};{185,44,96,153,228} {209,210,104,153,290,224}.
- Находится пересечение двух массивов в строке 5 и столбце E {185,44,96,153,228} {209,210,104,153,290,224} = 153.
- Суммируется массив в строке 3 и результат пересечения массивов: СУММ({146,100,248,210,167};153) = 1024.
Как же нам "починить" формулу? Можно использовать круглые скобки для принудительного выполнения операции объединения перед пересечением и тогда мы получим правильный результат.
=СУММ((B3:F3;B5:F5) E2:E7)
210 + 153 = 363
Теперь маловероятно, что вы совершите такую ошибку. Однако, на её примере мы смогли наглядно увидеть порядок вычислений в операциях с диапазонами. Также она может объяснить неправильный результат, который вы можете получить, если случайно используете в формуле пробел.
Порядок математических операций
В математических операциях также существует свой порядок вычислений. Деление и умножение всегда вычисляются перед сложением и вычитанием.
Давайте рассмотрим простой пример:
= 3 + 10 * 5
Результат этой формулы равен 53, потому что умножение (10*5) происходит перед сложением (3+10). Итак, эта формула вычисляется как 10*5=50, а затем 3+50=53.
Если бы нам было нужно, чтобы сложение произошло первым, эта операция была бы заключена в круглые скобки, чтобы изменить порядок вычисления. Результат следующей формулы равен 65:
= (3 + 10) * 5
При написании сложных формул скобки не только меняют порядок вычислений, но и облегчают чтение и понимание формулы.
Хорошее оформление формул - это редко изучаемый и недооценённый навык. Он весьма важен, особенно если вы будете не единственным, кто будет использовать таблицу с введёнными вами формулами.
Информация, рассмотренная сегодня очень важна и фундаментальна для дальнейшего использования формул. Если у вас остались вопросы, смело задавайте их в комментариях. В следующей статье мы рассмотрим полезный инструмент имеющийся в Excel, который поможет вам с выбором и правильным написанием функций для ваших формул.
Не переключайтесь;)