Как проверить формулу в Excel на наличие ошибок
Обновлено: 06.07.2024
В этом видео мы рассмотрим параметры проверки ошибок для формул в Excel.
Если вы часто используете Excel, вы, вероятно, увидите ряд потенциальных ошибок, отмеченных на листе во время работы.
То, какие именно ошибки помечаются, зависит от того, какие правила проверки ошибок включены.
Для подтверждения перейдите к: параметры > формулы > правила проверки ошибок
Обратите внимание, что это глобальные настройки. Если вы отключите правило здесь, оно останется отключенным для всех рабочих листов, пока не будет снова включено.
Когда Excel отмечает ошибку на листе, она отображается в виде маленького треугольника в левом верхнем углу ячейки.
Важно понимать, что этот символ не обязательно означает наличие проблемы. Вместо этого этот символ означает, что может возникнуть проблема.
Нажав на одну из этих ячеек, вы увидите небольшой символ предупреждения, который называется "Смарт-тег". Этот значок представляет собой меню параметров и информации.
Первый пункт меню указывает, какой тип ошибки отмечен.
Второй элемент предлагает помощь по ошибке.
Следующий элемент открывает средство оценки формулы, которое можно использовать для пошаговой оценки формулы.
Следующий вариант — игнорировать эту ошибку. Если вы выберете эту опцию, вы сообщите Excel игнорировать эту ошибку только на этом листе. Этот параметр будет запомнен при сохранении и повторном открытии листа.
"Редактировать в строке формул" просто переводит вас в режим редактирования в строке формул, как если бы вы щелкнули строку формул вручную.
Используйте последний пункт меню, чтобы перейти непосредственно к параметрам проверки ошибок. Если вы сказали Excel игнорировать ошибку и хотите, чтобы Excel снова обратил на нее внимание, используйте кнопку «сбросить пропущенные ошибки».
Вы можете попросить Excel явно проверить наличие ошибок на вкладке "Формулы" на ленте.
При этом открывается окно проверки ошибок, позволяющее перемещаться по помеченным ошибкам. Excel выберет первую найденную ошибку и остановится. В окне отображается информация об ошибке и варианты устранения или игнорирования ошибки, аналогично тому, что мы видели со смарт-тегом.
Когда вы нажмете следующую кнопку ниже, Excel выберет следующую ячейку с ошибкой и обновит информацию об ошибке, если ошибка другая.
Как и раньше, если вы решите проигнорировать определенную ошибку, Excel не остановится на этой ошибке при следующей проверке на наличие ошибок. Если вы хотите, чтобы Excel снова пометил ошибку, используйте кнопку «Сбросить пропущенные ошибки», как и раньше.
Большинство книг Excel содержат ошибки, которые в некоторых случаях приводят к неприятным «сюрпризам». Ошибки электронных таблиц бывают разных видов: некоторые из них легко обнаружить, но другие гораздо более тонкие: например, когда вы забыли обновить внешний источник данных или когда вы копируете формулу из ячейки выше, а не из ячейки в ячейку. осталось. Или вы в конечном итоге считаете некоторые ячейки дважды и т. д. и т. д.
Поскольку ошибок очень много, в этой записи блога основное внимание уделяется ошибкам в формулах (поскольку их легко найти), а другие типы ошибок оставлены для будущих публикаций в блоге.
Содержание
Что такое ошибки в формулах?
Как следует из названия, ошибки формул возникают из-за того, что формулы или функции возвращают ошибку. Вот обзор:
Excel предлагает несколько встроенных способов поиска ошибок в формулах, давайте рассмотрим их один за другим:
Перейти к спецвыпуску
На вкладке "Главная" ленты выберите "Найти и выбрать" > "Перейти к специальному". (или через Ctrl-G и Alt-S ):
затем выберите «Формулы» и проверьте «Ошибки»:
Когда вы нажмете OK , Excel отформатирует ячейки с ошибками на активном листе серым цветом:
Проверка ошибок
Вы также можете просмотреть ошибки более удобным способом (вместо того, чтобы просто выделять их, как мы делали в предыдущем разделе): перейдите на вкладку «Формулы» на ленте и нажмите «Проверка ошибок» в разделе «Аудит формул». Откроется следующее всплывающее окно, в котором вы можете нажать «Далее», чтобы перейти к следующей ошибке:
Запросить надстройку
В более поздних версиях Excel Microsoft включила надстройку Inquire. Если на ленте нет вкладки «Запрос», выберите «Файл» > «Параметры» > «Надстройки». Затем внизу в разделе «Управление» выберите «Надстройки COM» и нажмите «Перейти». . Во всплывающем окне установите флажок рядом с Inquire . Теперь должна появиться вкладка на ленте.
После того как на ленте появится вкладка «Запрос», нажмите «Анализ рабочей книги», и вы получите подробный анализ содержимого вашей рабочей книги. В качестве примера вы также можете перечислить свои ошибки в формуле:
Автоматическая проверка ошибок
По умолчанию Excel показывает ошибки в формулах (и многие другие), выделяя ячейку зеленым треугольником в левом верхнем углу ячейки. Выберите ячейку и нажмите на появившуюся кнопку трассировки ошибок. Это объяснит ошибку, а также предложит помощь по ней. Если ошибка ожидается, вы также можете ее игнорировать:
Чтобы указать, какие ошибки будут отмечены этим зеленым треугольником, выберите "Файл" > "Параметры" > "Формулы":
Экспертная оценка
Эффективным способом уменьшения числа ошибок и хорошим дополнением к автоматической проверке ошибок являются экспертные проверки. Экспертная проверка – стандартная практика в разработке программного обеспечения (т. е. коллега просматривает ваши изменения до того, как они попадут в базу кода).
Для Excel это долгое время было сложной задачей, так как не было хороших решений для контроля версий и рецензирования книг Excel.
xltrail, решение, похожее на GitHub или SharePoint, делает задачу рецензирования изменений в книге Excel тривиальной: оно позволяет увидеть, что изменилось между двумя версиями файла, и внести изменения, которые могли произойти в скрытых листах или видимые столбцы:
Заключение
Мы рассмотрели несколько способов обнаружения ошибок в формулах в Microsoft Excel. Сообщите нам в комментариях ниже, какой метод вы предпочитаете или используете ли вы другой метод для обнаружения ошибок такого типа.
При использовании формул всегда следует проверять наличие ошибок.
Группа «Аудит формул» на вкладке «Формулы» содержит команды, помогающие выявлять и исправлять ошибки в формулах.
Самой полезной командой является раскрывающийся список «Проверка ошибок». вниз.
Проверка ошибок — отображает диалоговое окно проверки ошибок, если ваш рабочий лист содержит ошибки.
Ошибка трассировки. Однократное нажатие приведет к трассировке всех стрелок к ячейкам со значениями ошибки. Вы должны удалить все стрелки трассировки перед использованием.
Циклические ссылки — подменю, отображающее ячейки, содержащие циклические ссылки.
Диалоговое окно "Ошибка проверки"
Возможно, это диалоговое окно является самым быстрым способом поиска любых ячеек с ошибками на активном листе.
При отображении этого диалогового окна будет выбрана первая ячейка, содержащая ошибку.
Вы можете используйте это диалоговое окно, чтобы найти и исследовать любые ошибки одну за другой.
Это диалоговое окно полностью немодальное и может отображаться только в том случае, если активный рабочий лист содержит хотя бы одну ошибку.
Фактическое содержимое ячейки отображается в верхнем левом углу, а ниже приводится краткое описание ошибки, в данном случае это деление на ноль.
Справка по этой ошибке — отображает раздел справки, относящийся к определенному типу ошибки.
Показать этапы расчета — отображает диалоговое окно «Вычислить формулу».
Игнорировать ошибку. Это удалит зеленый индикатор из верхнего левого угла ячейки, содержащей текущую ошибку.
Редактировать в строке формул — позволяет редактировать формулу в строке формул. Нажмите «Возобновить», чтобы продолжить после внесения изменений.
Параметры – отображаются параметры Excel, вкладка "Формулы".
Вы можете использовать кнопки "Назад" и "Далее" для перемещения между ошибками на активном листе.
Эта опция доступна, даже если вы отключили проверка биографических данных.
Проверка ошибок в фоновом режиме
Excel автоматически выполнит фоновую проверку ошибок (по умолчанию).
Вы можете изменить этот параметр на вкладке "Параметры" на вкладке "Формулы".
При обнаружении ошибки в верхнем левом углу ячейки отображается маленький зеленый треугольник.
При выборе этой ячейки отображается всплывающая подсказка с кратким описанием, а также смарт-тег .
Игнорирование ошибок
Если в ваших формулах есть какие-либо ошибки, они будут обозначены смарт-тегом проверки ошибок.
Если правила проверки ошибок отключены, вместо этого вы можете использовать диалоговое окно проверки ошибок.
И то, и другое смарт-тег и диалоговое окно дают вам возможность «Игнорировать ошибку».
Если вы проигнорируете определенную ошибку, смарт-тег исчезнет, и ошибка больше не будет отображаться в диалоговом окне «Проверка ошибок».
>Можно сбросить все "пропущенные ошибки" с помощью кнопки на вкладке "Параметры" на вкладке "Формулы".
Проверка ошибок завершена
После проверки всего рабочего листа появится окно сообщения.
Важно
Вы можете не игнорировать какие-либо ошибки, открыв диалоговое окно "Параметры" и нажав "Сбросить пропущенные ошибки".
Если вы случайно выбрали игнорирование ошибки, нажмите F2 и Enter, чтобы снова отобразить индикатор ошибки.
Ошибки в формулах Excel обычно отображаются в виде маленького зеленого треугольника в левом верхнем углу ячейки. Если щелкнуть ячейку с ошибкой, откроется раскрывающийся список, из которого можно выбрать нужный вариант.
Если в Excel не включены параметры фоновой проверки ошибок, этот треугольник не будет отображаться, но ячейка все равно будет содержать значение ошибки.
Чтобы убедиться, что вы видите треугольник, который упрощает проверку ошибок, убедитесь, что фоновая проверка включена в параметрах Excel.
<р>1. На ленте выберите Файл. Затем выберите «Параметры» > «Формулы».
Как использовать проверку ошибок
<р>1. Открыв файл с ошибками в Excel, на ленте выберите Формула > Аудит формул > Проверка ошибок.
Нажмите на маленький зеленый треугольник слева от ячейки, содержащей ошибку, и выберите "Показать этапы расчета".
Ошибка отображается в формуле в диалоговом окне.
Если все ошибки на листе исправлены, проверка ошибок прекратится.
Ошибка проверки в Google Таблицах
Проверка ошибок в Google Таблицах выполняется автоматически. Как только у вас появится ячейка с ошибкой, она отобразится на экране с подробными сведениями об ошибке.
Читайте также: