Как найти ошибку в формуле Excel

Обновлено: 21.11.2024

При использовании формул всегда следует проверять наличие ошибок.
Группа «Аудит формул» на вкладке «Формулы» содержит команды, помогающие выявлять и исправлять ошибки в формулах.
Самой полезной командой является раскрывающийся список «Проверка ошибок». вниз.

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

Диалоговое окно "Ошибка проверки"

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

Справка по этой ошибке — отображает раздел справки, относящийся к определенному типу ошибки.
Показать этапы расчета — отображает диалоговое окно «Вычислить формулу».
Игнорировать ошибку. Это удалит зеленый индикатор из верхнего левого угла ячейки, содержащей текущую ошибку.
Редактировать в строке формул — позволяет редактировать формулу в строке формул. Нажмите «Возобновить», чтобы продолжить после внесения изменений.
Параметры – отображаются параметры Excel, вкладка "Формулы".
Вы можете использовать кнопки "Назад" и "Далее" для перемещения между ошибками на активном листе.
Эта опция доступна, даже если вы отключили проверка биографических данных.

Проверка ошибок в фоновом режиме

Excel автоматически выполнит фоновую проверку ошибок (по умолчанию).
Вы можете изменить этот параметр на вкладке "Параметры" на вкладке "Формулы".

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

Игнорирование ошибок

Если в ваших формулах есть какие-либо ошибки, они будут обозначены смарт-тегом проверки ошибок.
Если правила проверки ошибок отключены, вместо этого вы можете использовать диалоговое окно проверки ошибок.
И то, и другое смарт-тег и диалоговое окно дают вам возможность «Игнорировать ошибку».
Если вы проигнорируете определенную ошибку, смарт-тег исчезнет, ​​и ошибка больше не будет отображаться в диалоговом окне «Проверка ошибок».
>Можно сбросить все "пропущенные ошибки" с помощью кнопки на вкладке "Параметры" на вкладке "Формулы".

Проверка ошибок завершена

После проверки всего рабочего листа появится окно сообщения.

Важно

Вы можете не игнорировать какие-либо ошибки, открыв диалоговое окно "Параметры" и нажав "Сбросить пропущенные ошибки".
Если вы случайно выбрали игнорирование ошибки, нажмите F2 и Enter, чтобы снова отобразить индикатор ошибки.

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

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

Содержание

Что такое ошибки в формулах?

Как следует из названия, ошибки формул возникают из-за того, что формулы или функции возвращают ошибку. Вот обзор:

Excel предлагает несколько встроенных способов поиска ошибок в формулах, давайте рассмотрим их один за другим:

Перейти к спецвыпуску

На вкладке "Главная" ленты выберите "Найти и выбрать" > "Перейти к специальному". (или через Ctrl-G и Alt-S ):

затем выберите «Формулы» и проверьте «Ошибки»:

Когда вы нажмете OK , Excel отформатирует ячейки с ошибками на активном листе серым цветом:

Проверка ошибок

Вы также можете просмотреть ошибки более удобным способом (вместо того, чтобы просто выделять их, как мы делали в предыдущем разделе): перейдите на вкладку «Формулы» на ленте и нажмите «Проверка ошибок» в разделе «Аудит формул». Откроется следующее всплывающее окно, в котором вы можете нажать «Далее», чтобы перейти к следующей ошибке:

Запросить надстройку

В более поздних версиях Excel Microsoft включила надстройку Inquire. Если на ленте нет вкладки «Запрос», выберите «Файл» > «Параметры» > «Надстройки». Затем внизу в разделе «Управление» выберите «Надстройки COM» и нажмите «Перейти». . Во всплывающем окне установите флажок рядом с Inquire . Теперь должна появиться вкладка на ленте.

После того как на ленте появится вкладка «Запрос», нажмите «Анализ рабочей книги», и вы получите подробный анализ содержимого вашей рабочей книги. В качестве примера вы также можете перечислить свои ошибки в формуле:

Автоматическая проверка ошибок

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

Чтобы указать, какие ошибки будут отмечены этим зеленым треугольником, выберите "Файл" > "Параметры" > "Формулы":

Экспертная оценка

Эффективным способом уменьшения числа ошибок и хорошим дополнением к автоматической проверке ошибок являются экспертные проверки. Экспертная проверка – стандартная практика в разработке программного обеспечения (т. е. коллега просматривает ваши изменения до того, как они попадут в базу кода).

Для Excel это долгое время было сложной задачей, так как не было хороших решений для контроля версий и рецензирования книг Excel.

xltrail, решение, похожее на GitHub или SharePoint, делает задачу рецензирования изменений в книге Excel тривиальной: оно позволяет увидеть, что изменилось между двумя версиями файла, и внести изменения, которые могли произойти в скрытых листах или видимые столбцы:

Заключение

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

Формула удаляет пустые ячейки и ячейки с ошибками. Неважно, содержат ли ячейки числа или текст, все они будут представлены в новом столбце.

Формула массива в ячейке D3:

=ИНДЕКС($B$3:$B$20, МАЛЕНЬКИЙ(ЕСЛИ(ЕСПУСТО($B$3:$B$20)+ЕОШИБКА($B$3:$B$20), "", ПОИСКПОЗ(СТРОКА($B$3) :$B$20),СТРОКА($B$3:$B$20))), СТРОКА($A$1:A1)))

Как создать формулу массива

  1. Скопируйте (Ctrl + c) и вставьте (Ctrl + v) формулу массива в строку формул. См. рисунок ниже.
  2. Нажмите и удерживайте Ctrl + Shift.
  3. Нажмите Enter один раз.
  4. Отпустить все клавиши.

Как скопировать формулу массива

  1. Копировать (Ctrl + C) ячейку D3.
  2. Вставьте (Ctrl + V) формулу массива в диапазон ячеек D3:D11.

Пояснение формулы в ячейке D3

Шаг 1. Определите пустые ячейки

Функция ЕПУСТО возвращает ИСТИНА, если ячейка пуста (пустая), и ЛОЖЬ, если нет.

Шаг 2. Выявление ошибок

Функция ЕОШИБКА возвращает ИСТИНА, если ячейка содержит ошибку, и ЛОЖЬ, если нет.

Шаг 3. Добавьте массивы

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

Логическое значение Логическое значение Умножить Добавить
ЛОЖЬ ЛОЖЬ 0 (ноль) 0 (ноль)
ЛОЖЬ ИСТИНА 0 (ноль) 1
ИСТИНА ИСТИНА 1 2

Шаг 4. Преобразование массива в номера строк

Функция ЕСЛИ позволяет использовать логическое выражение для определения возвращаемого значения (аргумента).

Шаг 5. Получение k-го наименьшего номера строки

Чтобы иметь возможность вернуть одно значение из массива, нам нужно использовать функцию НАИМЕНЬШИЙ для извлечения номера одной строки. Второй аргумент функции МАЛЕНЬКИЙ использует функцию СТРОКИ с расширяющейся ссылкой на ячейку для извлечения нового значения в каждой ячейке.

МАЛЕНЬКИЙ(ЕСЛИ(ЕСПУСТО($B$3:$B$20)+ЕОШИБКА($B$3:$B$20), "", MATCH(СТРОКА($B$3:$B$20),СТРОКА($B$3) :$B$20))), СТРОКИ($A$1:A1))

Шаг 6. Возврат значения на основе номера строки

Функция ИНДЕКС возвращает значение из диапазона ячеек на основе номера строки и столбца. Наш диапазон ячеек представляет собой один столбец, поэтому нам нужно указать только номер строки, чтобы получить правильное значение.

ИНДЕКС($B$3:$B$20, МАЛЕНЬКИЙ(ЕСЛИ(ЕСПУСТО($B$3:$B$20)+ЕОШИБКА($B$3:$B$20), "", ПОИСКПОЗ(СТРОКА($B$3: $B$20),СТРОКА($B$3:$B$20))), СТРОКА($A$1:A1)))

и возвращает 2 в ячейке D3.

Скачать Excel *.xls

2. Как найти ошибки на листе

Excel имеет отличные встроенные функции. Следующий позволяет искать формулы, которые возвращают ошибку, на всем листе.

Инструкции:

  1. Перейти на вкладку "Главная"
  2. Нажмите левой кнопкой мыши на "Найти и выделить"
  3. Нажмите левой кнопкой мыши на "Перейти к специальному".
  4. Нажмите левой кнопкой мыши на "Формулы"
  5. Включить "Ошибки"
  6. Нажмите левой кнопкой мыши на OK!

    7. Если есть какие-либо ошибки в формуле, они будут выбраны. На рисунке ниже показано выделение ошибочных ячеек.

    Еженедельный блог EMAIL

    [newsletter_signup_form Я Оскар и здесь, чтобы помочь вам.

    Не стесняйтесь оставлять комментарии и задавать вопросы по Excel.

    Подпишитесь на мою рассылку, чтобы не пропустить новые статьи блога.

    Статьи по теме

    В этом сообщении блога я предоставлю два решения о том, как удалить пустые ячейки, и решение о том, как […]

    Excel имеет отличные встроенные функции. Следующий позволяет искать формулы, которые возвращают ошибку, на всем листе. […]

    В этой статье я покажу вам два способа поиска пустых ячеек. Оба метода […]

    В этом меньшем примере столбец D (Категория) содержит пустые ячейки, как показано на рисунке выше. Если ваш столбец содержит тысячи […]

    2 ответа на вопрос «Удалить пробелы и ошибки в списке»

    Спасибо! Пока это моя любимая формула Excel.

    Здравствуйте, у меня возникли проблемы с корректной работой вашей формулы в моем листе. Ваша формула отлично работает на вашем листе, но когда я копирую ее в свою, я не могу заставить **** excel скопировать ее, увеличивая часть формулы row1: 1. Я могу одинаково скопировать первую ячейку во все ячейки столбца, но это бесполезно. Если я попытаюсь отредактировать его, я просто получу сообщение об ошибке, говорящее о том, что я не могу редактировать формулу массива. Если я попытаюсь использовать «заполнить», то первая ячейка будет работать нормально, но все остальные в столбце будут возвращать ошибки NUM или VAL! Как мне получить **** excel, чтобы скопировать формулу вниз по столбцу при увеличении ROW1: 1 до ROW2: 2 и т. Д.?
    Это сводит меня с ума!
    Запуск Windows 10 дома с любой версией Excel, с которой поставляется.
    Ваша формула делает именно то, что мне нужно, и это здорово, если я могу просто заставить ее увеличивать эти номера строк!
    Помощь с благодарностью принята!

    Оставить ответ

    Как комментировать

    Как добавить формулу в комментарий
    Вставьте сюда формулу.

    Преобразование знаков меньше и больше
    Использование символов HTML вместо знаков меньше и больше.
    становится >

    Как добавить код VBA в комментарий
    [vb 1="vbnet" language=","]
    Поместите здесь код VBA.
    [/vb]

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

    Чем больше формул вы пишете, тем больше ошибок вы столкнетесь :)

    Хотя ошибки в формулах и разочаровывают, они полезны, потому что они ясно говорят вам, что что-то не так. Это намного лучше, чем не знать. Самые серьезные ошибки Excel обычно происходят из-за нормально выглядящих формул, которые незаметно возвращают неверные результаты.

    Если вы столкнулись с ошибкой в ​​формуле, не паникуйте. Сохраняйте спокойствие и методично расследуйте, пока не найдете причину. Спросите себя: «О чем мне говорит эта ошибка?» Экспериментируйте методом проб и ошибок. По мере накопления опыта вы сможете избегать многих ошибок и быстрее исправлять возникающие ошибки.

    Исправление ошибок

    Ниже приведен базовый процесс исправления ошибок. Помните, что ошибки в формулах часто «каскадируют» по рабочему листу, когда одна ошибка вызывает другую. Когда вы находите и устраняете основную проблему, все быстро приходит в норму.

    <р>1. Найдите ошибки. Вы можете использовать «Перейти к специальному» > «Формула», как описано ниже.

    <р>2. Проследите ошибку до ее источника. Если это сложно, попробуйте функцию трассировки ошибок.

    <р>3. Выясните, что вызывает ошибку. При необходимости разбейте формулу на части.

    <р>4. Исправьте ошибку в источнике.

    Поиск всех ошибок

    Вы можете найти все ошибки сразу с помощью Go To Special. Используйте сочетание клавиш Control + G, затем нажмите кнопку «Специальная». Excel отобразит диалоговое окно со многими параметрами, показанными ниже. Чтобы выбрать только ошибки, выберите «Формулы + ошибки», затем нажмите «ОК»:

    Отслеживание ошибок

    Перехват ошибок — это способ "отлавливать" ошибки, чтобы они не появлялись в первую очередь. Это имеет смысл, когда вы знаете, что вероятны определенные ошибки, и хотите, чтобы сообщения об ошибках не появлялись. Существует два основных подхода:

    <р>2. Перехватите ошибку с помощью IFERROR или ISERROR. При таком подходе вы отслеживаете ошибку и предоставляете альтернативу при обнаружении ошибки. На этой странице показан пример ВПР.

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

    Коды ошибок Excel

    Есть 9 кодов ошибок, с которыми вы, вероятно, столкнетесь в какой-то момент при работе с формулами Excel. В этом разделе показаны примеры каждой ошибки формулы, а также информация и ссылки о том, как исправить ошибку.

    Например, на листе ниже ошибка DIV отображается в ячейке D4, потому что ячейка C4 пуста. Пустые ячейки оцениваются Excel как ноль, а ячейка B4 не может быть разделена на ноль:

    Если значение в E3 изменить на "кофе", "яйца" и т. д., функция ВПР будет работать нормально и получать стоимость товара.

    Ниже функция МЕСЯЦ не может извлечь значение месяца из "яблока", так как "яблоко" не является датой:

    Когда "яблоко" будет удалено из D5, формула будет работать нормально и вернет "Джо", "Сэм" и "Мэри".

    Если формула настроена для фильтрации по группе "А", формула будет работать нормально:

    Читайте также: