Сообщения об ошибках в excel

Обновлено: 07.07.2024

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

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

Чтобы не заморачиваться с каждой ячейкой, зайдите в настройки Excel: Файл ► Параметры ► Формулы , раздел Проверка ошибок и отключите проверку ошибок или отключите причину, которая её вызывает в Правилах проверки ошибок :

Решить задачу, не забираясь каждый раз в параметры, можно используя VBA и свойства объекта ErrorCheckingOptions .

Чтобы задать или вернуть параметры проверки ошибок, объект ErrorCheckingOptions , имеет следующие основные свойства:

BackgroundChecking ( включает/отключает функцию проверки, значение параметра True-Включен/False-Выключен );
IndicatorColorIndex ( определяет цвет ярлычка ошибки, по умолчанию xlColorIndexAutomatic );
EvaluateToError ( отключает ошибки в формулах, например, деление на 0, True/False );
TextDate(дата в виде текста с двумя цифрами года, True/False );
NumberAsText ( числа отформатированные, как текст, True/False );
InconsistentFormula ( отключает проверку несогласованности формул, True/False ).

Перейдём к макросам, тут ничего сложного. Если параметр включен ( True), отключить ( False) , иначе ( Else) включить, получается замкнутый круг:

If Application.ErrorCheckingOptions.BackgroundChecking = True Then

Application.ErrorCheckingOptions.BackgroundChecking = False

Application.ErrorCheckingOptions.BackgroundChecking = True

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

If Application.ErrorCheckingOptions.IndicatorColorIndex = 2 Then

Application.ErrorCheckingOptions.IndicatorColorIndex = 2

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

Заключительный макрос, отключит ошибки для чисел введённых, как текст:

If Application.ErrorCheckingOptions.NumberAsText = True Then

Application.ErrorCheckingOptions.NumberAsText = False

Самостоятельно поэкспериментируйте с остальными свойствами.

Часто сталкиваетесь с ошибками в Excel? Пишите в комментариях. Подписывайтесь на канал, до новых встреч.

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

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

Чтобы скрыть значения ошибок, можно преобразовать их, например, в число 0, а затем применить условный формат, позволяющий скрыть значение.

Создание примера ошибки

Откройте чистый лист или создайте новый.

Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR(

Переместите курсор в конец формулы.

Введите ,0), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0).

Применение условного формата

Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.

Выберите команду Создать правило.

В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.

Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.

На вкладке Число в списке Категория выберите пункт (все форматы).

Скрытие значений ошибок путем изменения цвета текста на белый

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

Выделите диапазон ячеек, содержащих значение ошибки.

На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования.

Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования.

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

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

Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.

Описание функций

ЕСЛИERROR С помощью этой функции можно определить, содержит ли ячейка ошибку и возвращает ли ошибку формула.

Выберите отчет сводной таблицы.
Появится область "Инструменты для работы со pivottable".

Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.

Изменение способа отображения ошибок. В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.

Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Ячейка с ошибкой в формуле

В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы.

In Excel 2007: Click the Microsoft Office button > Excel Options >Formulas.

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

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

Выделим ячейку, в которой будем настраивать ограничение (в нашем примере это ячейка E6). Щелкаем по вкладке «Данные» -> «Проверка данных».

В открывшемся диалоговом окне проверки данных выбираем вкладку «Параметры». Здесь вы можете выбрать условие проверки. Это может быть целое число, десятичное, дата, время, длина текста и т.д.

В нашем случае необходимо установить ограничение по длине вводимых символов. Символов должно быть не больше 10. Из выпадающего списка выбираем «Длина текста». Если вы не хотите, чтобы ограничение распространялось на пустые ячейки, поставьте галочку в «Игнорировать пустые ячейки».

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

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

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

Несколько полезных приемов в Excel

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

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

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

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

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

Допустим, вы правильно написали формулу, но недостаточно информации, введенной в отдельные ее записи. Запись в массиве таблиц неполная. Требуется фактическое имя таблицы, чтобы узнать, где искать желаемое значение.

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

Как в Excel убрать решетки из ячейки?

Нажмите на правую границу заголовка столбца и увеличьте ширину столбца.

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

Лечится довольно просто. Измените значение на значение, не равное 0, или добавьте значение, если ваша ячейка была пустой.

Теперь, что на самом деле означает эта ошибка? Вы могли случайно удалить или вставить данные поверх ячейки, используемой формулой. Например, ячейка B16 содержит формулу =A14/F16/F17.

Здесь важно отметить, что не данные из ячейки удаляются, но сама строка или столбец.

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

Чтобы дать вам некоторый дополнительный контекст, вот как работают справочные операторы Excel:

Оператор диапазона (точка с запятой): определяет ссылки на диапазон ячеек.
Оператор объединения (запятая): объединяет две ссылки в одну ссылку.
Оператор пересечения (пробел): возвращает ссылку на пересечение двух диапазонов.

Прежде всего, убедитесь, что вы используете правильный синтаксис в формуле. Используйте двоеточие, чтобы отделить первую ячейку от последней, когда вы ссылаетесь на непрерывный диапазон ячеек в формуле. С другой стороны, использование запятой поможет правильно вывести формулу, если вы ссылаетесь на две ячейки, которые не пересекаются.

Как устранить эту ошибку

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

И еще, при вводе формулы, исключите такие значения, как $ 1000, в формате валюты. Вместо этого введите 1000, а затем отформатируйте ячейку с валютой и запятыми после вычисления формулы. Просто число, без знака $ (доллар).

Как устранить эту ошибку

Проверьте, не ввели ли вы какую-либо отформатированную валюту, дату или спецсимвол. Обязательно удалите их из формулы, сохранив только цифры.

Заключение

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

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