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 — пропускать значения ошибок.