Если в диапазоне суммирования встречается значение ошибки #Н/Д (значение недоступно), то функция
СУММ()
также вернет ошибку. Используем функцию
СУММЕСЛИ()
для обработки таких ситуаций.
Некоторые функции
ПРОСМОТР()
,
ПОИСКПОЗ()
или
ВПР()
могут вернуть ошибку #Н/Д (значение недоступно).
При сложении значений из диапазона, содержащих ошибку #Н/Д, функцией
СУММ()
, получим ошибку #Н/Д.
Простейшим решением является использование функции
СУММЕСЛИ()
, например, так
=СУММЕСЛИ(E4:E8;»<>#Н/Д»)
. Предполагается, что данные находятся в диапазоне
E4:E8
.
Другой вариант –
формула массива
=СУММ(ЕСЛИ(ЕНД(
E4:E8
);0;
E4:E8
))
После ввода формулы, нужно вместо
ENTER
нажать
CTRL
+
SHIFT
+
ENTER
.
Еще один вариант с применением
формулы массива
=СУММ(ЕСЛИ(ЕЧИСЛО(
E4:E8
);
E4:E8
))
|
Не учитывать ошибку в сумме ячеек |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
При суммировании диапазона чисел, содержащих некоторые значения ошибок, обычная функция СУММ не будет работать правильно. Чтобы суммировать только числа и пропустить значения ошибок, функция СЛОЖЕНИЕ или СУММ вместе с функциями ЕСЛИОШИБКА могут оказать вам услугу.
- Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
- Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
- Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ
Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
В Excel функция АГРЕГАТ может помочь суммировать диапазон ячеек и игнорировать все виды ошибок, общий синтаксис:
=AGGREGATE(9,6,range)
- 9: Это аргумент — номер функции в АГРЕГАТЕ, он указывает на функцию суммы;
- 6: Это еще один аргумент — параметры в АГРЕГАТЕ, он используется для игнорирования значений ошибок;
- range: Диапазон суммируемых ячеек.
Примените приведенную ниже формулу в пустую ячейку и нажмите Enter ключ для суммирования:
=AGGREGATE(9,6,C2:C10)
Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
Вы также можете объединить функции СУММ и ЕСЛИОШИБКА вместе, чтобы создать формулу массива для решения этой задачи, общий синтаксис:
{=SUM(IFERROR(range,0))}
Array formula, should press Ctrl + Shift + Enter keys together.
- range: Диапазон ячеек, которые вы хотите суммировать, без учета ошибок.
Введите следующую формулу в пустую ячейку и нажмите Shift + Ctrl + Enter ключи, чтобы получить правильный результат, см. снимок экрана:
=SUM(IFERROR(C2:C10,0))
Пояснение к формуле:
= СУММ (ЕСЛИОШИБКА (C2: C10,0))
- ЕСЛИ ОШИБКА (C2: C10,0): Эта функция ЕСЛИОШИБКА вернет 0 при обнаружении значений ошибки. Итак, вы получите следующий список массивов: {1200; 0; 1500; 0; 1005; 900; 0; 2005; 1690}.
- SUM(IFERROR(C2:C10,0))=SUM({1200;0;1500;0;1005;900;0;2005;1690}): Функция СУММ суммирует все числа в списке массивов, чтобы получить окончательный результат: 8300.
Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ
Вышеупомянутые два метода суммируют диапазон ячеек, игнорируя все виды ошибок. Если ваши данные содержат только один тип ошибки, например ошибку # Н / Д, чтобы игнорировать эту конкретную ошибку при суммировании, вам может помочь функция СУММЕСЛИ. Общий синтаксис:
=SUMIF(range,»<>error_type»)
- range: Диапазон ячеек, которые вы хотите суммировать;
- error_type: Тип ошибки, которую вы хотите игнорировать при суммировании, например # N / A, #REF! …
В пустой ячейке введите приведенную ниже формулу и нажмите Enter ключ для получения нужного вам результата:
=SUMIF(C2:C10,»<>#N/A»)
Внимание: При применении этой формулы, если в списке данных возникают другие типы ошибок, эта функция СУММЕСЛИ возвращает результат ошибки.
Используемая относительная функция:
- SUMIF:
- Функция СУММЕСЛИ может помочь суммировать ячейки на основе одного критерия.
- IFERROR:
- Функция ЕСЛИОШИБКА используется для возврата пользовательского результата, когда формула оценивает ошибку, и возврата нормального результата, если ошибки не возникает.
- АГРЕГАТ:
- Функция АГРЕГАТ может применять различные агрегатные функции к списку или базе данных с возможностью игнорировать скрытые строки и значения ошибок.
Другие статьи:
- Сумма наименьших или нижних значений N
- В Excel легко суммировать диапазон ячеек с помощью функции СУММ. Иногда вам может потребоваться суммировать наименьшие или нижние 3, 5 или n чисел в диапазоне данных, как показано ниже. В этом случае СУММПРОИЗВ вместе с функцией МАЛЕНЬКИЙ могут помочь вам решить эту проблему в Excel.
- Суммирование наименьших или нижних значений N на основе критериев
- В предыдущем руководстве мы обсудили, как суммировать наименьшие n значений в диапазоне данных. В этой статье мы выполним дополнительную расширенную операцию — суммируем наименьшие n значений на основе одного или нескольких критериев в Excel.
- Итоговая сумма счета по возрасту
- Суммирование сумм счетов на основе возраста, как показано на скриншоте ниже, может быть обычной задачей в Excel, в этом руководстве будет показано, как вычислить промежуточные суммы счетов по возрасту с помощью обычной функции СУММЕСЛИ.
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.
- Разработан для 1500 рабочих сценариев, помогает решить 80% проблем с Excel.
- Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
- Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
- 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Office Tab — Включить чтение и редактирование с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Комментарии (0)
Оценок пока нет. Оцените первым!
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Если ячейки, содержащие #VALUE, ссылаются на ячейки с #VALUE! в формулах будет #VALUE! ошибку «#ВЫЧИС!».
Чтобы не учитывать ошибки, мы создадим формулу, пропускающую ошибочные значения в диапазоне и рассчитывающую среднее для остальных значений.
Чтобы решить эту проблему, мы используем функцию СРЗНАЧ в сочетании с функциями ЕСЛИ и ЕОШИБКА для определения наличия ошибок в указанном диапазоне. В данном конкретном случае требуется формула массива:
=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B2:D2);»»;B2:D2))
Это формула массива. Поэтому вам нужно нажать ввод (только если вы используете Microsoft 365 ) или CTRL+SHIFT+ВВОД в формуле.
Примечание: Если у вас есть текущая версия Microsoft 365 ,можно просто ввести формулу в ячейку вывода, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как формулу массива устаревшей. Для этого сначала выберем выходную ячейку, введите формулу в нее и нажимая для подтверждения CTRL+SHIFT+ВВОД. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Примечание: Приведенная выше формула будет работать не только в случае ошибок #ЗНАЧ!, но и в случае ошибок #Н/Д, #ПУСТО!, #ДЕЛ/0! и других.
Таким же образом можно использовать функцию СУММ:
=СУММ(ЕСЛИ(ЕОШИБКА(B2:D2);»»;B2:D2))
У вас есть вопрос об определенной функции?
Задать вопрос на форуме сообщества, посвященном Excel
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Исправление ошибки #ЗНАЧ! #BUSY!
СРЗНАЧ
СУММ
Полные сведения о формулах в Excel
Обнаружение ошибок в формулах
Все функции Excel (по алфавиту)
Функции Excel (по категориям)
Нужна дополнительная помощь?
Нужны дополнительные параметры?
Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.
В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.
|
Как в Экселе посчитать сумму, если в некоторых ячейках содержатся ошибки? Как в Excel при вычислении суммы игнорировать ошибки в ячейках диапазона?
Чтобы игнорировать ячейки ошибки при расчете суммы диапазона, потребуется все ячейки с ошибками рассматривать как ячейки с нулевым значением. Это делается с использованием формулы массива (Что такое формула массива в Excel? ), которая заменяет каждое ошибочное значение на ноль. Например так: =СУММ( ЕСЛИОШИБКА( A1:A100 ; 0)) A1:A100 диапазон суммирования ячеек Ввод формулы массива осуществляется нажатием комбинации клавиш Ctrl+Shift+Enter
vdtest 3 года назад В Эксель при вычислении суммы можно считать только ячейки содержащие число, а остальные считать со значением 0; это достигается использованием следующей формулы массива: диапазона ячеек A1:A20 формула выглядит так: =СУММ( ЕСЛИ( ЕЧИСЛО( A1:A20); A1:A20; 0)) подробнее о формулах массива Знаете ответ? |
