Если в диапазоне суммирования встречается значение ошибки #Н/Д (значение недоступно), то функция
СУММ()
также вернет ошибку. Используем функцию
СУММЕСЛИ()
для обработки таких ситуаций.
Некоторые функции
ПРОСМОТР()
,
ПОИСКПОЗ()
или
ВПР()
могут вернуть ошибку #Н/Д (значение недоступно).
При сложении значений из диапазона, содержащих ошибку #Н/Д, функцией
СУММ()
, получим ошибку #Н/Д.
Простейшим решением является использование функции
СУММЕСЛИ()
, например, так
=СУММЕСЛИ(E4:E8;»<>#Н/Д»)
. Предполагается, что данные находятся в диапазоне
E4:E8
.
Другой вариант –
формула массива
=СУММ(ЕСЛИ(ЕНД(
E4:E8
);0;
E4:E8
))
После ввода формулы, нужно вместо
ENTER
нажать
CTRL
+
SHIFT
+
ENTER
.
Еще один вариант с применением
формулы массива
=СУММ(ЕСЛИ(ЕЧИСЛО(
E4:E8
);
E4:E8
))
Функция АГРЕГАТ в Excel предназначена для выполнения различных вычислений с использованием встроенных функций (определяются на основе одного из параметров) и возвращает агрегатный результат. Данная функция объединяет наиболее востребованные функции Excel, предназначенных для работы с массивами данных. Синтаксис рассматриваемой функции предусматривает возможность пропуска элементов массива со значениями ошибок или скрытых строк.
Примеры использования функции АГРЕГАТ в Excel
Пример 1. В классе провели контрольную по математике. Необходимо определить среднюю оценку для учеников, которые присутствовали на контрольной. Напротив некоторых фамилий оценка не указана по причине неn допуска к написанию или отсутствия.
Вид таблицы с данными:
Для расчета используем следующую формулу:
=АГРЕГАТ(1;3;B3:B13)
Описание параметров:
- 1 – число, соответствующее функции СРЗНАЧ;
- 3 – число, указывающее на способ расчета (не учитывать скрытые строки и коды ошибок);
- B3:B13 – диапазон ячеек с данными для определения среднего значения.
Полученный результат:
В результате формула вернула правильное число среднего значения в обход значениям с ошибками #Н/Д.
Как пропустить ошибки в ячейках при суммировании в Excel
Пример 2. В таблицу Excel были выгружены данные о работе двух счетчиков электроэнергии (записи по дням). Если в какой-либо из дней потребления электроэнергии не было, передаваемое значение из БД интерпретируется табличным процессором как ошибка #Н/Д. Определить, какой из счетчиков зафиксировал наибольшее общее потребление энергии за определенный период.
Вид таблицы с данными:
Для решения удобно использовать функцию АГРЕГАТ, поскольку она позволяет исключить коды ошибок из расчетов.
Определим показатели первого счетчика по формуле:
Описание параметров:
- 9 – указатель на функцию СУММ;
- 2 – указатель на способ расчета (игнорирование значений ошибок);
- B3:B13 – ссылка на диапазон со значениями кВт.
Произведем аналогичный расчет для второго счетчика, передав в качестве ссылки ячейки C3:C13. В результате получим следующие значения:
Например, для расчета этих значений с использованием обычной функции СУММ потребуется более сложная и громоздкая запись (формула массива – для выполнения нужно нажать ctrl+shift+enter):
Описание синтаксиса функции АГРЕГАТ в Excel
Рассматриваемая функция имеет две формы синтаксической записи:
Ссылочная:
=АГРЕГАТ(номер_функции;параметры;ссылка1;[ссылка2];…)
Форма массива:
=АГРЕГАТ(номер_функции;параметры;массив;[k])
Описание аргументов:
- номер_функции – обязательный для заполнения, принимает числовые значения от 1 до 19, где каждое значение соответствует определенной функции (например, 1 – СРЗНАЧ, 2 – СЧЁТ и т. д.) Полный перечень номеров и соответствующих им функций указан в справочной информации к функции АГРЕГАТ.
- параметры – обязательный для заполнения, принимает числовые значения из диапазона от 0 до 7, где:
- значения от 0 до 3 – пропуск вложенных функций АГРЕГАТ, промежуточные итоги (для всех случаев), а также скрытых строк (1), значений ошибок (2), скрытых строк и значений ошибок (3);
- число 4 – все значения учитываются;
- 5-7 – пропуск только скрытых строк, значений ошибок и скрытых строк и значений ошибок соответственно.
- ссылка1 – обязательный для заполнения, принимает ссылку на диапазон ячеек с данными, над которыми требуется выполнить вычисления.
- [ссылка2] – необязательный аргумент, принимает ссылку на диапазон ячеек с данными, требуемыми для вычислений некоторыми функциями (например, НАИБОЛЬШИЙ, КВАРТИЛЬ.ВКЛ и прочие).
- массив – обязательный аргумент функции АГРЕГАТ формы массива, принимающий диапазон вычисляемых данных.
- [k] – не обязательный для заполнения аргумент, принимающий числовое значение, указывающее на позицию в массиве для некоторых функций (например, НАИМЕНЬШЕЕ, НАИБОЛЬШЕЕ, ПРОЦЕНТИЛЬ.ВКЛ и прочие).
Примечания:
- Если [ссылка2] и последующие необязательные аргументы требуются для вычислений, но не указаны явно, функция АГРЕГАТ вернет код ошибки #ЧИСЛО!
- Если в качестве любого ссылочного параметра была передана трехмерная ссылка, результатом выполнения функции АГРЕГАТ будет код ошибки #ЧИСЛО!
- Функция АГРЕГАТ была добавлена в Excel начиная с версии 2010 года и предназначена для расширения функционала функций, определяемых первым аргументом (от 1 до 19), в частности для проведения расчета по видимым значениям, игнорирования возникающих ошибок.
Примечание: Если в качестве массива или ссылки на диапазон было передано выражение с промежуточными вычислениями (например, «B1:B10*(B1:B10<5)»), вложенные итоги, агрегаты или скрытые строки игнорироваться не будут.
При суммировании диапазона чисел, содержащих некоторые значения ошибок, обычная функция СУММ не будет работать правильно. Чтобы суммировать только числа и пропустить значения ошибок, функция СЛОЖЕНИЕ или СУММ вместе с функциями ЕСЛИОШИБКА могут оказать вам услугу.
- Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
- Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
- Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ
Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
В 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 — включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Комментарии (0)
Оценок пока нет. Оцените первым!
|
Не учитывать ошибку в сумме ячеек |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Ранее я описал, как использовать формулы массива:
- для транспонирования столбцов в строки;
- для вычисления стандартного отклонения для данных с тенденцией.
Кто не знаком с формулами массива, предлагаю сначала почитать Введение в формулы массива. Рекомендую также Джон Уокенбах «Формулы в 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 — пропускать значения ошибок.