Как пропустить ошибки в excel при суммировании

Обновлено: 06.01.2025

Каждый, кто работает в 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.

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

Лучшее решение

Решение:убедитесь в том, что искомые значения есть в исходных данных, или используйте в формуле обработчик ошибок, например ЕСЛИERROR. Например, =ЕСЛИERROR(ФОРМУЛА();0)

=ЕСЛИ(при вычислении формулы получается ошибка, то показать 0, в противном случае показать результат формулы)

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

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

Искомое значение и исходные данные относятся к разным типам. Например, вы пытаетесь использовать ссылку на функцию ВПР как число, а исходные данные сохранены как текст.

Решение:убедитесь, что типы данных одинаковы. Чтобы проверить формат ячеек, вы можете выбрать ячейку или диапазон ячеек, щелкнуть правой кнопкой мыши и выбрать формат ячеек > число (или нажмите CTRL+1) и при необходимости изменить числовом формате.

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

Начальные и конечные пробелы можно удалить с помощью функции СЖПРОБЕЛЫ. В приведенном ниже примере в функции ВПР используется вложенная функция СЖПРОБЕЛЫ для удаления начальных пробелов из имен в ячейках A2:A7 и возврата названия отдела.

Примечание: 24 сентября 2018 г. - Формулы динамического массива - Если у вас есть текущая версия Microsoft 365 и вы находитесь на канале быстрого выпуска Insiders, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона и нажать клавишу Enter, чтобы подтвердите формулу в виде формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Использование метода приблизительного или точного совпадения (ИСТИНА/ЛОЖЬ)

Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, задайте для аргумента тип_сопоставления значение 0 (ноль).

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

В данном примере ячейка E2 содержит ссылку на несовпадающие диапазоны:

Чтобы формула вычислялась правильно, необходимо изменить ее так, чтобы оба диапазона включали строки 2–11.

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

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

Excel автоматически запустит мастер.

Щелкните любой аргумент, и Excel покажет вам сведения о нем.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

При суммировании диапазона чисел, содержащих некоторые значения ошибок, обычная функция СУММ не будет работать правильно. Чтобы суммировать только числа и пропустить значения ошибок, функция СЛОЖЕНИЕ или СУММ вместе с функциями ЕСЛИОШИБКА могут оказать вам услугу.

Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ

Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ

В Excel функция АГРЕГАТ может помочь суммировать диапазон ячеек и игнорировать все виды ошибок, общий синтаксис:

9 : Это аргумент - номер функции в АГРЕГАТЕ, он указывает на функцию суммы;
6 : Это еще один аргумент - параметры в АГРЕГАТЕ, он используется для игнорирования значений ошибок;
range : Диапазон суммируемых ячеек.

Примените приведенную ниже формулу в пустую ячейку и нажмите Enter ключ для суммирования:

Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА

Вы также можете объединить функции СУММ и ЕСЛИОШИБКА вместе, чтобы создать формулу массива для решения этой задачи, общий синтаксис:

Array formula, should press Ctrl + Shift + Enter keys together.

range : Диапазон ячеек, которые вы хотите суммировать, без учета ошибок.

Введите следующую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи, чтобы получить правильный результат, см. снимок экрана:

Пояснение к формуле:

= СУММ (ЕСЛИОШИБКА (C2: C10,0))

ЕСЛИ ОШИБКА (C2: C10,0) : Эта функция ЕСЛИОШИБКА вернет 0 при обнаружении значений ошибки. Итак, вы получите следующий список массивов: .
SUM(IFERROR(C2:C10,0))=SUM() : Функция СУММ суммирует все числа в списке массивов, чтобы получить окончательный результат: 8300.

Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ

В пустой ячейке введите приведенную ниже формулу и нажмите Enter ключ для получения нужного вам результата:

Внимание: При применении этой формулы, если в списке данных возникают другие типы ошибок, эта функция СУММЕСЛИ возвращает результат ошибки.

Используемая относительная функция:

SUMIF:
Функция СУММЕСЛИ может помочь суммировать ячейки на основе одного критерия.

IFERROR:
Функция ЕСЛИОШИБКА используется для возврата пользовательского результата, когда формула оценивает ошибку, и возврата нормального результата, если ошибки не возникает.

АГРЕГАТ:
Функция АГРЕГАТ может применять различные агрегатные функции к списку или базе данных с возможностью игнорировать скрытые строки и значения ошибок.

Другие статьи:

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

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

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

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

1. В пустой ячейке введите эту формулу = СУММ (ЕСЛИ (ЕСТЬ ОШИБКА (A1: C6); ""; A1: C6)) , см. снимок экрана:

Внимание: в приведенной выше формуле, A1: C6 - это диапазон, который вы хотите суммировать, вы можете изменить его по своему усмотрению.

2, Затем нажмите Shift + Ctrl + Enter одновременно, чтобы закончить. И вы получите следующий результат:

Суммарный диапазон ячеек, игнорирующих ошибки с кодом VBA

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

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модульи вставьте следующий код в Окно модуля.

Код VBA: сумма ячеек с игнорированием ошибок

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

4. Затем нажмите OK, вы получите окно подсказки, чтобы показать результат следующим образом.

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