Как найти ошибку в формуле Excel
Обновлено: 04.07.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)))
Как создать формулу массива
- Скопируйте (Ctrl + c) и вставьте (Ctrl + v) формулу массива в строку формул. См. рисунок ниже.
- Нажмите и удерживайте Ctrl + Shift.
- Нажмите Enter один раз.
- Отпустить все клавиши.
Как скопировать формулу массива
- Копировать (Ctrl + C) ячейку D3.
- Вставьте (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 имеет отличные встроенные функции. Следующий позволяет искать формулы, которые возвращают ошибку, на всем листе.
Инструкции:
- Перейти на вкладку "Главная"
- Нажмите левой кнопкой мыши на "Найти и выделить"
- Нажмите левой кнопкой мыши на "Перейти к специальному".
- Нажмите левой кнопкой мыши на "Формулы"
- Включить "Ошибки"
- Нажмите левой кнопкой мыши на OK!
Если есть какие-либо ошибки в формуле, они будут выбраны. На рисунке ниже показано выделение ошибочных ячеек.
Еженедельный блог 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, формула будет работать нормально и вернет "Джо", "Сэм" и "Мэри".
Если формула настроена для фильтрации по группе "А", формула будет работать нормально:
Читайте также: