Проверяйте созданные в Excel формулы дважды

Одним из самых мощных инструментов Excel является возможность создавать формулы. Вы можете использовать формулы для вычисления новых значений, анализа данных и для многих других задач. Но в работе с формулами есть и своя оборотная сторона – достаточно малейшей ошибки, чтобы формула дала неправильный результат.

Хуже всего то, что Excel не всегда сообщает об ошибке, допущенной в формуле. Как правило, такая формула продолжает работать и выполнять вычисления, давая ошибочный результат. Ответственность за то, что Вы лишний раз поленились проверить формулу, полностью ложится на Вас.

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

Проверьте ссылки

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

Поищите перестановки

Распространённая ошибка – использовать правильные ссылки на ячейки, но в неправильном порядке. Например, если необходимо вычесть C2 из C3, формула должна быть такой: =C3-C2, а не такой: =C2-C3.

Разберите на части

Если формула слишком сложна, чтобы её проверить, попробуйте разделить её на несколько простых формул. Таким образом, Вы сможете проверить точность каждой формулы, а если возникнут проблемы – будете точно знать, где именно.

Подумайте, каким должен быть результат

Вы можете использовать собственный опыт, критическое мышление и чутьё, чтобы определить, каким должен быть результат. Если результат в Excel намного больше или меньше ожидаемого, возможно, есть ошибки в формуле (или неверные данные в ячейках).

Например, если Вы вычисляете суммарную стоимость 8 единиц товара по 98 центов за каждую, результат должен быть чуть меньше $8. В примере ниже формула даёт ошибочный результат $784,00. Причина в том, что в ячейке A2 цена введена как 98, а должно быть 0,98. Как видите, маленькие детали могут давать огромные расхождения.

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

Проверьте аргументы

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

Подсказка бывает особенно полезна, когда Вы пытаетесь исправить функцию, которая не работает как нужно. Например, посмотрите на функцию ниже:

В примере на рисунке выше, функция NETWORKDAYS (ЧИСТРАБДНИ) возвращает ошибку. Если мы введём функцию NETWORKDAYS (ЧИСТРАБДНИ) в другую ячейку, причина становится очевидной:

Функция NETWORKDAYS (ЧИСТРАБДНИ) требует минимум два аргумента – start_date (нач_дата) и end_date (кон_дата). В предыдущем примере был задан только один аргумент, поэтому исправим функцию, добавив недостающий аргумент:

Теперь наша формула работает правильно!

Проверьте всю цепочку операций (последовательность)

Помните из школьной математики, что такое порядок математических операций? Если нет (или если хотите освежить память), можете изучить урок по построению сложных формул. В Excel всегда используется такой порядок, то есть операции не просто выполняются по очереди слева направо. В следующем примере первым делом выполняется умножение, и это не совсем то, что мы хотели. Исправим эту формулу, заключив D2+D3 в скобки:

Включите показ формул

Если на листе Excel много формул и функций, то, возможно, Вам будет удобнее переключиться в режим отображения формул, чтобы видеть все формулы одновременно. Для этого необходимо воспользоваться командой Formula view (Показать формулы), которая находится на вкладке Formulas (Формулы) в разделе Formula auditing (Зависимости формул).

Чтобы переключиться обратно к привычному виду, еще раз нажмите на эту команду.

Помните, мастерство в создании формул достигается через постоянную практику. Даже самые опытные пользователи Excel допускают ошибки в формулах. Если Ваша формула не работает или выдаёт не верное значение, не паникуйте! В большинстве случаев существует простое объяснение, почему формула работает с ошибкой. Как только Вы найдёте эту ошибку, то сможете заставить формулу работать правильно.