Ошибки в Excel, как их быстро и легко найти

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

Метод 1: CTRL + F

  1. Начнем с известного сочетания клавиш CTRL + F (Найти)
  2. Допустим, мы ищем ячейки с ошибкой # ССЫЛКА!. Затем в меню «Найти» напротив «Найти» введите #REF!

Как только мы найдем все ошибки типа #REF !, мы можем повторить процедуру для # DIV / 0 !, # N / A и т. Д. ошибки.

Что, если мы хотим найти все ошибки в файле, а не искать их по типу? Затем мы можем использовать поиск по шаблону. В таблице ниже перечислены наиболее распространенные ошибки в Excel и их причины..

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

Затем мы можем использовать FIND для поиска следующей строки: # ??? *

Как видите, Excel обнаружил все ячейки с ошибками..

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

Читать также:  Фундамент фундамента

Этот метод эффективен и прост, но есть один существенный недостаток — мы можем обходить ячейки одну за другой. Если их 5-10, как в файле-примере, это не проблема, а если их десятки и сотни — не будет удобно. Еще один недостаток заключается в том, что он пропускает ячейки, которые находятся в скрытых строках или столбцах..

Метод 2: CTRL + G

  1. В этом методе мы используем меню Go To, которое появляется после нажатия CTRL + G или F5.

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

С другой стороны, он работает только на одном листе и отмечает все ошибки независимо от их типа, т.е. мы не можем найти только ошибки #REF !. Либо все, либо ничего.

Метод 3: средство проверки ошибок

В Excel есть встроенный инструмент для поиска и исправления ошибок. Он находится в строке меню, а точнее Формулы, раздел Формула Аудит.

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

Это меню условно разделено на три раздела. Слева есть два информационных поля:

Вышеупомянутое говорит нам, в какой ячейке находится ошибка (в данном случае C6), и записывает формулу (в данном случае = A6 / # REF!).

В поле ниже Excel объясняет, что вызвало ошибку. Объяснения не сильно отличаются от приведенных в таблице ошибок Excel выше, но приятно, что нам не нужно их запоминать..

Читать также:  Как сделать график с округленными столбцами в Excel

Вторая часть меню проверки ошибок содержит несколько кнопок для дополнительных действий..

Первая кнопка — Помощь по этой ошибке. При нажатии Excel автоматически подключается к Интернету (если, конечно, у вас есть подключение) и открывает дополнительное окно с инструкциями по исправлению ошибки..

Вторая кнопка — Показать шаги расчета… Когда мы нажимаем на нее, открывается меню «Вычислить формулу»..

В нем, нажимая кнопку Evaluate, вы проходите все шаги, которые выполняет формула. В данном случае нам это не очень пригодится, потому что формула довольно простая — делить одно число на другое. Но если у нас есть сложная формула с вложенными функциями, выполняя формулу шаг за шагом, мы можем точно увидеть, где возникает ошибка (или, как мы ее называем, «формула ломается»)..

С Ignore Error мы просто пропускаем ошибку в этой ячейке и переходим к следующей. А с помощью Edit in Formula Bar мы переходим в поле ввода формулы, чтобы исправить ошибку вручную..

Третья часть меню содержит три кнопки навигации..

Параметры вызывают меню с параметрами формул (Файл — Параметры — Формулы), где мы можем ввести некоторые настройки формул..

И с помощью Previous и Next переходим к предыдущей или следующей ячейке с ошибкой в ​​прошивке..

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

Читать также:  5 причин изучить Power Query в 2020 году

Все, что нам нужно сделать, это нажать ОК и перейти к следующему листу..

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

Обратной стороной является то, что мы должны перебирать ошибки одну за другой, и мы не можем выбрать тип ошибки (например, только #REF! Или только # N / A).

Заключение

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

1. Они применяются, если потребитель обнаружил или обнаружил ошибку. Но если вы просто рассчитываете, что все будет хорошо, то ваши ошибки останутся незамеченными, а это может иметь довольно неприятные последствия..

 2. Они находят в формулах только технические ошибки. Например, формула суммирования может быть полностью правильной, но она не суммирует все ячейки, которые необходимо сложить вместе..

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

Подробнее об ошибках и работе с формулами читайте здесь.

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?