Перейти к содержимому

Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива

Ранее я описал, как использовать формулы массива:

Кто не знаком с формулами массива, предлагаю сначала почитать Введение в формулы массива. Рекомендую также Джон Уокенбах «Формулы в Microsoft Excel 2010», а именно часть IV. Формулы массивов (глава 14. Знакомство с массивами; глава 15. Магия формул массива).

Наверное, вы сталкивались с тем, что функция СУММ не работает, если ее применить к диапазону, в котором содержатся ошибки, например, #Н/Д, #ЗНАЧ! или #ДЕЛ/0!

Скачать заметку в формате Word, примеры в формате Excel

Допустим, данные организованы, как на рис. 1 (не самым удачным образом :)). Здесь в таблице «Продажи за период» данные из таблиц по месяцам собираются формулой ВПР, и, если продаж артикула в течение месяца не было, то формула ВПР возвращает значение ошибки #Н/Д.

Рис. 1. Объемы продаж по месяцам и за период с января по апрель

Суммирование по диапазону, часть ячеек в котором, содержат значение ошибки, также возвращает ошибку (рис. 2).

Рис. 2. Формулы СУММ в ячейках N24:Q24 дают ошибку

Мы могли бы исправить ситуацию, модернизировав формулы ВПР в ячейках N3:Q23, но тема нашей заметки иная, поэтому мы изменим формулы в ячейках N24:Q24, чтобы они суммировали числовые значения, игнорируя значения ошибок.

Для этого применим формулу массива (рис. 3)

Рис. 3. СУММ на основе формулы массива «справляется» со значениями ошибки в отдельных ячейках

Разберем, как работает формула массива: {=СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23))}

ЕОШИБКА(N3:N23) возвращает значение ошибка для соответствующих ячеек из диапазона N3:N23.

ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23)) возвращает

  • 0, если ЕОШИБКА(N3:N23) вернула ошибку
  • значение из диапазона N3:N23, если ЕОШИБКА(N3:N23) не вернула ошибку

Формула массива {=СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23))} суммирует все значения, полученные на предыдущем шаге, то есть все значения из диапазона N3:N23, при этом, заменяя значения ошибок на нули.

Конечно же, эту задачу можно было решить и без применения формул массива. Например, добавлением колонок, в которых делались промежуточные вычисления по формуле ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23). Более того, пока я не нашел ни одной задачи, которую без формул массива нельзя было бы решить, а с формулами массива – можно. Основная идея заключается в другом: формулы массива упрощают обработку данных, делают формулы проще и нагляднее (хотя кому, как… :))

Аналогичный подход можно применить для функций схожих с СУММ, например, СРЗНАЧ, МАКС, МИН. Используйте в этом случае, в формуле вместо нуля пустое значение, чтобы лишние нули не искажали результат:

Добавление от 30 июля 2013 г.

Начиная с версии Excel2010 гораздо проще использовать функцию АГРЕГАТ. Эта функция специально предназначена для выполнения операций с массивом (или ссылками), содержащим ошибочные и/или скрытые значения (рис. 4). Функция АГРЕГАТ (для массива) имеет три обязательных параметра и один необязательный:

  • тип исполняемой функции (всего их 16); среди них: суммирование, среднее значение, максимум, медиана, квартиль и др.;
  • метод обработки массива — какие ячейки пропускать, например, только содержащие ошибку или только скрытые ячейки, или и те и другие (всего 8 методов);
  • массив (он же диапазон ячеек)
  • необязательный параметр используется для некоторых типов функций, например, НАИБОЛЬШИЙ; в этом случае этот параметр указывает, какое наибольшее по счету значение взять (например, 2).

Рис. 4. Пример использования функции АГРЕГАТ; тип исполняемой функции — 9 — СУММ, параметр — 6 — пропускать значения ошибок.

6 комментариев для “Excel. Суммирование по диапазону, содержащему ошибку, с помощью формулы массива”

  1. Здравствуйте,
    Спасибо за классное описание функций!!! Я пробовал применить функцию СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23)) и функцию АГРЕГАТ в Google Docs Exel но они почему то там не работают. Мне надо посчитать суму в диапазоне ячеек, но в некоторых есть ошибка REF! И формула там не работает. Вы проверяли работает ли функция в Google Docs?
    Спасибо за ответ

  2. Работу функции АГРЕГАТ в Google Docs не проверял.

  3. Здравствуйте,
    Спасибо за ответ. Нашел в интернете функции
    АГРЕГАТ в Google Docs нету. По у меня также не работает и ета формула в Google Docs СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23))
    Что скажете?

  4. Спасибо вам огромное!!!
    АГРЕГАТ идеально работает 😉

  5. Владимир

    Добрый день!
    Большое спасибо за помощь! Офис 2010. СУММ(ЕСЛИ(ЕОШИБКА(N3:N23);0;N3:N23)) почему-то не сработала. В моем случае формула имела вид {=СУММ(ЕСЛИ(ЕОШИБКА(F6:F22);0;F6:F22))}. Вроде написана правильно. В диапазоне значений ошибки типа #Н/Д. Агрегат отлично справился с задачей. Еще раз спасибо!

  6. Агрегат классная функция. Большое Спасибо!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *