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

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

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

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

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

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

Проверяйте формулы в Excel

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

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

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

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

Проверяйте формулы в Excel

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

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

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

Проверяйте формулы в Excel

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

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

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

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

Проверяйте формулы в Excel

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

Проверяйте формулы в Excel

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

Проверяйте формулы в Excel

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

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

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

Проверяйте формулы в Excel

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

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

Проверяйте формулы в Excel

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

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

07.04.2015 21:40
2878

2 комментария

10.11.2015 18:39
Здравствуйте.

Подскажите пожалуйста, у меня формула не корректно считает данные.

Если вставить скопированные данные как значение, то видно что вместо 0,82 там видно 0,81999999999. Но при этом данные где изначально не было целых чисел, формула считает корректно, а где были целые числа то выдает такую ошибку.
Здравствуйте, Юлия!

По рисунку могу только гадать о вашей проблеме, но скорее всего проблема в так называемой «ошибке плавающей запятой».

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

Могу подсказать 2 способа решения этой проблемы:

  • Первый способ заключается в использовании функций округления — статья с примером.

  • Второй способ — включить параметр Задать указанную точность (Excel 2013). Найти его можно по адресу Файл > Параметры > Дополнительно в разделе При пересчете этой книги. Если включить этот параметр, то Excel (грубо говоря) будет использовать в расчетах (например, при сравнении) числа, которые показаны на экране. Используйте этот параметр с осторожностью, поскольку имеется множество нюансов!

Более подробно можно почитать здесь — https://support.microsoft.com/ru-ru/kb/78113