При суммировании диапазона чисел, содержащих некоторые значения ошибок, обычная функция СУММ не будет работать правильно. Чтобы суммировать только числа и пропустить значения ошибок, функция СЛОЖЕНИЕ или СУММ вместе с функциями ЕСЛИОШИБКА могут оказать вам услугу.
- Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
- Суммировать числа без учета различных ошибок с помощью функций СУММ и ЕСЛИОШИБКА
- Суммировать числа без учета конкретной ошибки с функцией СУММЕСЛИ
Суммировать числа, игнорируя различные ошибки, с помощью функции АГРЕГАТ
В 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 — пропускать значения ошибок.
Skip to content
В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Хорошо знакомая нам функция ЕСЛИ позволяет производить вычисления в зависимости от выполнения условия. Функция СУММ позволяет складывать числовые значения. А что если нам нужна формула ЕСЛИ СУММ? Для этого случая в Excel имеется специальная функция СУММЕСЛИ.
Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif в английской версии) в таблицах Excel. Начнем с самых простых случаев, как можно использовать при этом знаки подстановки, назначить диапазон суммирования, работать с числами, текстом и датами. Особо остановимся на том, как использовать сразу несколько условий. И, конечно, мы применим новые знания на практике, рассмотрев несложные примеры.
- Как пользоваться СУММЕСЛИ в Excel – синтаксис
- Примеры использования функции СУММЕСЛИ в Excel
- Сумма если больше чем, меньше, или равно
- Критерии для текста.
- Подстановочные знаки для частичного совпадения.
- Точная дата либо диапазон дат.
- Сумма значений, соответствующих пустым либо непустым ячейкам
- Сумма по нескольким условиям.
- Почему СУММЕСЛИ у меня не работает?
Хорошо, что функция СУММЕСЛИ одинакова во всех версиях MS Excel. Еще одна приятная новость: если вы потратите некоторое время на ее изучение, вам потребуется совсем немного усилий, чтобы понять другие «ЕСЛИ»-функции, такие как СУММЕСЛИМН, СЧЕТЕСЛИ, СЧЕТЕСЛИМН и т.д.
Как пользоваться СУММЕСЛИ в Excel – синтаксис
Её назначение – найти итог значений, которые удовлетворяют определённым требованиям.
Синтаксис функции выглядит следующим образом:
=СУММЕСЛИ(диапазон, критерий, [диапазон_суммирования])
Диапазон – это область, которую мы исследуем на соответствие определённому значению.
Критерий – это значение или шаблон, по которому мы производим отбор чисел для суммирования.
Значение критерия может быть записано прямо в самой формуле. В этом случае не забывайте, что текст нужно обязательно заключать в двойные кавычки.
Также он может быть представлен в виде ссылки на ячейку таблицы, в которой будет указано требуемое ограничение. Безусловно, второй способ является более рациональным, поскольку позволяет гибко менять расчеты, не редактируя выражение.
Диапазон_суммирования — третий параметр, который является необязательным, однако он весьма полезен. Благодаря ему мы можем производить поиск в одной области, а суммировать значения из другой в соответствующих строках.
Итак, если он указан, то расчет идет именно по его данным. Если отсутствует, то складываются значения из той же области, где производился поиск.
Чтобы лучше понять это описание, рассмотрим несколько простых задач. Надеюсь, что они будут понятны не только «продвинутым» пользователям, но и подойдут для «чайников».
Примеры использования функции СУММЕСЛИ в Excel
Сумма если больше чем, меньше, или равно
Начнем с самого простого. Предположим, у нас есть данные о продажах шоколада. Рассчитаем различные варианты продаж.
В I3 записано:
=СУММЕСЛИ(D2:D21;I2)
D2:D21 – это координаты, в которых мы ищем значение.
I2 – ссылка на критерий отбора. Иначе говоря, мы ищем ячейки со значением 144 и складываем их.
Поскольку третий параметр функции не указан, то мы сразу складываем отобранные числа. Область поиска будет одновременно являться и диапазоном суммирования.
Кроме того, в качестве задания для отбора нужных значений можно указать текстовое выражение, состоящее из знаков >, <, <>, <= или >= и числа.
Можно указать его прямо в формуле, как это сделано в I13
=СУММЕСЛИ(D2:D21;«<144»)
То есть подытоживаем все заказы, в которых количество меньше 144.
Но, согласитесь, это не слишком удобно, поскольку нужно корректировать саму формулу, да и условие еще нужно не забыть заключить в кавычки.
В дальнейшем мы будем стараться использовать только ссылку на критерий, поскольку это значительно упрощает возможные корректировки.
Критерии для текста.
Гораздо чаще встречаются ситуации, когда поиск нужно проводить в одном месте, а в другом — суммировать данные, соответствующие найденному.
Чаще всего это необходимо, если необходимо использовать отбор по определённым словам. Ведь текстовые значения складывать нельзя, а вот соответствующие им числа – можно.
Как простой прием использования формулы СУММ ЕСЛИ в Эксель таблицах, рассчитаем итог по выполненным заказам.
В I3 запишем выражение:
=СУММЕСЛИ(F2:F21;I2;E2:Е21)
F2:F21 – это область, в которой мы отбираем подходящие значения.
I2 – здесь записано, что именно отбираем.
E2:E21 – складываем числа, соответствующие найденным совпадениям.
Конечно, можно указать параметр отбора прямо в выражении:
=СУММЕСЛИ(F2:F21;”Да”;E2:Е21)
Но мы уже договорились, что так делать не совсем рационально.
Важное замечание. Не забываем, что все текстовые значения необходимо заключать в кавычки.
Подстановочные знаки для частичного совпадения.
При работе с текстовыми данными часто приходится производить поиск по какой-то части слова или фразы.
Вернемся к нашему случаю. Определим, сколько всего было заказов на черный шоколад. В результате, у нас есть 2 подходящих наименования товара. Как учесть их оба? Для этого есть понятие неточного соответствия.
Мы можем производить поиск и подсчет значений, указывая не всё содержимое ячейки, а только её часть. Таким образом мы можем расширить границы поиска, применив знаки подстановки “?”, “*”.
Символ “?” позволяет заменить собой один любой символ.
Символ ”*” позволяет заменить собой не один, а любое количество символов (в том числе ноль).
Эти знаки можно применить в нашем случае двумя способами. Либо прямо вписать их в таблицу –
=СУММЕСЛИ(C2:C21;I2;E2:Е21) , где в E2 записано *[слово]*
либо
=СУММЕСЛИ(C2:C21;»*»&I2&»*»;E2:E21)
где * вставлены прямо в выражение и «склеены» с нужным текстом.
Давайте потренируемся:
- “*черный*” — мы ищем фразу, в которой встречается это выражение, а до него и после него – любые буквы, знаки и числа. В нашем случае этому соответствуют “Черный шоколад” и “Супер Черный шоколад”.
- “Д?” — необходимо слово из 2 букв, первая из которых “Д”, а вторая – любая. В нашем случае подойдет “Да”.
- “???” — найдем слово из любых 3 букв
=СУММЕСЛИ(F2:F21;”???”;E2:E21)
Этому требованию соответствует “Нет”.
- “???????*” — текст из любых 7 и более букв.
=СУММЕСЛИ(B2:B21;“???????*”;E8:E28)
Подойдет “Зеленый”, “Оранжевый”, “Серебряный”, “Голубой”, “Коричневый”, “Золотой”, “Розовый”.
- “З*” — мы выбираем фразу, первая буква которой “З”, а далее – любые буквы, знаки и числа. Это “Золотой” и “Зеленый”.
- “Черный*” — подходит фраза, которая начинаются именно с этого слова, а далее – любые буквы, знаки и числа. Подходит “Черный шоколад”.
Примечание. Если вам необходимо в качестве задания для поиска применять текст, который содержит в себе * и ?, то используйте знак тильда (~), поставив его перед этими символами. Тогда * и ? будут считаться обычными символами, а не шаблоном:
=СУММЕСЛИ(B2:B21;“*~?*”;E8:E28)
Важное замечание. Если в вашем тексте для поиска встречается несколько знаков * и ?, то тильду (~) нужно поставить перед каждым из них. К примеру, если мы будем искать текст, состоящий из трех звездочек, то формулу ЕСЛИ СУММ можно записать так:
=СУММЕСЛИ(B2:B21;“~*~*~*”;E8:E28)
А если текст просто содержит в себе 3 звездочки, то можно наше выражение переписать так:
=СУММЕСЛИ(B2:B21;“*~*~*~**”;E8:E28)
Точная дата либо диапазон дат.
Если нам нужно найти сумму чисел, соответствующих определённой дате, то проще всего в качестве критерия указать саму эту дату.
Примечание. При этом не забывайте, что формат указанной вами даты должен соответствовать региональным настройкам вашей таблицы!
Обратите внимание, что мы также можем здесь вписать ее прямо в формулу, а можем использовать ссылку.
Рассчитываем итог продаж за сегодняшний день – 04.02.2020г.
=СУММЕСЛИ(A2:A21;I1;E2:E21)
или же
=СУММЕСЛИ(A2:A21;СЕГОДНЯ();E2:E21)
Рассчитаем за вчерашний день.
=СУММЕСЛИ(A2:A21;СЕГОДНЯ()-1;E2:E21)
СЕГОДНЯ()-1 как раз и будет «вчера».
Складываем за даты, которые предшествовали 1 февраля.
=СУММЕСЛИ(A2:A21;»<«&»01.02.2020»;E2:E21)
После 1 февраля включительно:
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21)
А если нас интересует временной интервал «от-до»?
Мы можем рассчитать итоги за определённый период времени. Для этого применим маленькую хитрость: разность функций СУММЕСЛИ. Предположим, нам нужна выручка с 1 по 4 февраля включительно. Из продаж после 1 февраля вычитаем все, что реализовано после 4 февраля.
=СУММЕСЛИ(A2:A21;»>=»&»01.02.2020″;E2:E21) — СУММЕСЛИ(A2:A21;»>=»&»04.02.2020″;E2:E21)
Сумма значений, соответствующих пустым либо непустым ячейкам
Случается, что в качестве условия суммирования нужно использовать все непустые клетки, в которых есть хотя бы одна буква, цифра или символ.
Рассмотрим ещё один вариант использования формулы СУММ ЕСЛИ в таблице Excel, где нам необходимо подсчитать заказы, в которых нет отметки о выполнении, а также сколько было вообще обработанных заказов.
Если критерий указать просто “*”, то мы учитываем для подсчета непустые ячейки, в которых имеется хотя бы одна буква или символ (кроме пустых).
=СУММЕСЛИ(F2:F21;»*»;E2:E21)
Точно такой же результат даёт использование вместо звездочки пары знаков «больше» и «меньше» — <>.
=СУММЕСЛИ(F2:F21;»<>»;E2:E21)
Теперь рассмотрим, как можно находить сумму, соответствующую пустым ячейкам.
Для того, чтобы найти пустые, в которых нет ни букв, ни цифр, в качестве критерия поставьте парные одинарные кавычки ‘’, если значение критерия указано в ячейке, а формула ссылается на неё.
Если же указать на отбор только пустых ячеек в самой формуле СУММ ЕСЛИ, то впишите двойные кавычки.
=СУММЕСЛИ(F2:F21;«»;E2:E21)
Сумма по нескольким условиям.
Функция СУММЕСЛИ может работать только с одним условием, как мы это делали ранее. Но очень часто случается, что нужно найти совокупность данных, удовлетворяющих сразу нескольким требованиям. Сделать это можно как при помощи некоторых хитростей, так и с использованием других функций. Рассмотрим все по порядку.
Вновь вернемся к нашему случаю с заказами. Рассмотрим два условия и посчитаем, сколько всего сделано заказов черного и молочного шоколада.
1. СУММЕСЛИ + СУММЕСЛИ
Все просто:
=СУММЕСЛИ($C$2:$C$21;»*»&H3&»*»;$E$2:$E$21)+СУММЕСЛИ($C$2:$C$21;»*»&H4&»*»;$E$2:$E$21)
Находим сумму заказов по каждому виду товара, а затем просто их складываем. Думаю, с этим вы уже научились работать :).
Это самое простое решение, но не самое универсальное и далеко не единственное.
2. СУММ и СУММЕСЛИ с аргументами массива.
Вышеупомянутое решение очень простое и может выполнить работу быстро, когда критериев немного. Но если вы захотите работать с несколькими, то она станет просто огромной. В этом случае лучшим подходом является использование в качестве аргумента массива критериев. Давайте рассмотрим этот подход.
Вы можете начать с перечисления всех ваших условий, разделенных запятыми, а затем заключить итоговый список, разделенный точкой с запятой, в {фигурные скобки}, который технически называется массивом.
Если вы хотите найти покупки этих двух товаров, то ваши критерии в виде массива будут выглядеть так:
СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21)
Поскольку здесь использован массив критериев, то результатом вычислений также будет массив, состоящий из двух значений.
А теперь воспользуемся функцией СУММ, которая умеет работать с массивами данных, складывая их содержимое.
=СУММ(СУММЕСЛИ($C$2:$C$21;{«*черный*»;»*молочный*»};$E$2:$E$21))
Важно, что результаты вычислений в первом и втором случае совпадают.
3. СУММПРОИЗВ и СУММЕСЛИ.
А если вы предпочитаете перечислять критерии в какой-то специально отведенной для этого части таблицы? Можете использовать СУММЕСЛИ в сочетании с функцией СУММПРОИЗВ, которая умножает компоненты в заданных массивах и возвращает сумму этих произведений.
Вот как это будет выглядеть:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;H3:H4;E2:E21))
в H3 и H4 мы запишем критерии отбора.
Но, конечно, ничто не мешает вам перечислить значения в виде массива критериев:
=СУММПРОИЗВ(СУММЕСЛИ(C2:C21;{«*черный*»;»*молочный*»};E2:E21))
Результат, возвращаемый в обоих случаях, будет идентичен тому, что вы наблюдаете на скриншоте.
Важное замечание! Обратите внимание, что все перечисленные выше три способа производят расчет по логическому ИЛИ. То есть, нам нужны продажи шоколада, который будет или черным, или молочным.
Почему СУММЕСЛИ у меня не работает?
Этому может быть несколько причин. Иногда ваше выражение не возвращает того, что вы ожидаете, только потому, что тип данных в ячейке или в каком-либо аргументе не подходит для нее. Итак, вот что нужно проверить.
1. «Диапазон данных» и «диапазон суммирования» должны быть указаны ссылками, а не в виде массива.
Первый и третий атрибуты функции всегда должны быть ссылкой на область таблицы, например A1: A10. Если вы попытаетесь передать что-нибудь еще, например, массив {1,2,3}, Excel выдаст сообщение об ошибке.
Правильно: =СУММЕСЛИ(A1:A3, «цвет», C1:C3)
Неверно : =СУММЕСЛИ({1,2,3}, «цвет», C1:C3)
2. Ошибка при суммировании значений из других листов или рабочих книг.
Как и любая другая функция Excel, СУММЕСЛИ может ссылаться на другие листы и рабочие книги, если они в данный момент открыты.
Найдем сумму значений в F2: F9 на листе 1 книги 1, если соответствующие данные записаны в столбце A, и если среди них содержатся «яблоки»:
=СУММЕСЛИ([Книга1.xlsx]Лист1!$A$2:$A$9,»яблоки»,[Книга1.xlsx]Лист1!$F$2:$F$9)
Однако это перестанет работать, как только Книга1 будет закрыта. Это происходит потому, что области, на которые ссылаются формулы в закрытых книгах, преобразуются в массивы и хранятся в таком виде в текущей книге. А поскольку в аргументах 1 и 3 массивы не допускаются, то формула выдает ошибку #ЗНАЧ!.
3. Чтобы избежать проблем, убедитесь, что диапазоны данных и поиска имеют одинаковый размер.
Как отмечалось в начале этого руководства, в современных версиях Microsoft Excel они не обязательно должны иметь одинаковый размер. Но вот в Excel 2000 и более ранних версиях это может вызвать проблемы. Однако, даже в самых последних версиях Excel сложные выражения, в которых диапазон сложения имеет меньше строк и/или столбцов, чем диапазон поиска, являются капризными. Вот почему рекомендуется всегда иметь их одинакового размера и формы.
Примеры расчета суммы:
In this example, the goal is to create a formula that will sum values in a range that may contain errors. A common problem in Excel is that errors in data show up in the results of other formulas. For example, in the worksheet shown, the SUM function is used to sum the named range data (D5:D15) . Because the range D5:D15, the SUM function itself returns #N/A. The formula in cell F5 is:
=SUM(data) // returns #N/A
Ideally, the errors can be resolved by entering the missing data, and the SUM function will start working again. In fact, it is often helpful when summary calculations display errors, because it signals there are problems in the data that should be investigated. However, there are situations where you want to ignore errors and sum the available numbers. In this article, we look at three different formula options.
SUMIF
One option is to use the SUMIF function with the not equal to (<>) operator like this:
=SUMIF(data,"<>#N/A")
This is a relatively simple formula and it works fine as long as the range contains only #N/A errors. SUMIF returns the sum of values not equal to #N/A. However, if another type of error occurs, the SUMIF function will itself return an error. For example if the #DIV/0! error appears in the data, SUMIF will return #DIV/0!.
AGGREGATE
Another more robust option is to use the AGGREGATE function. In cell F7, AGGREGATE is configured to sum and ignore errors by setting function_num to 9, and options to 6:
=AGGREGATE(9,6,data) // sum and ignore errors
The AGGREGATE function is a multipurpose function that can run other functions like SUM, COUNT, AVERAGE, MAX, etc. with special behaviors. For example, AGGREGATE can optionally ignore errors, hidden rows, and even other calculations. This formula will ignore all errors that might appear in data, not just the #N/A error. AGGREGATE can run 19 functions total, see this page for a full explanation.
SUM and IFERROR
Finally, we can create a more literal array formula using the SUM function together with the IFERROR function. In cell F8, we nest the IFERROR function inside SUM like this:
=SUM(IFERROR(data,0)) // sum and ignore errors
Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365, where dynamic arrays are native.
In this formula, the IFERROR function is used to trap errors and convert them to zero. In the example shown, the named range data contains eleven cells, which can be represented as an array like this:
{20;21;10;39;#N/A;28.5;5.5;12.5;10;6;#N/A} // the range D5:D15
IFERROR converts the #N/A errors to zero:
=IFERROR(data,0)
=IFERROR({20;21;10;39;#N/A;28.5;5.5;12.5;10;6;#N/A},0)
={20;21;10;39;0;28.5;5.5;12.5;10;6;0}
The resulting array is returned directly to the SUM function:
=SUM({20;21;10;39;0;28.5;5.5;12.5;10;6;0}) // returns 152.5
and SUM returns 152.5 as the final result.
Note: Use caution when ignoring errors. Suppressing errors can be dangerous because it hides underlying problems.
Если в диапазоне суммирования встречается значение ошибки #Н/Д (значение недоступно), то функция
СУММ()
также вернет ошибку. Используем функцию
СУММЕСЛИ()
для обработки таких ситуаций.
Некоторые функции
ПРОСМОТР()
,
ПОИСКПОЗ()
или
ВПР()
могут вернуть ошибку #Н/Д (значение недоступно).
При сложении значений из диапазона, содержащих ошибку #Н/Д, функцией
СУММ()
, получим ошибку #Н/Д.
Простейшим решением является использование функции
СУММЕСЛИ()
, например, так
=СУММЕСЛИ(E4:E8;»<>#Н/Д»)
. Предполагается, что данные находятся в диапазоне
E4:E8
.
Другой вариант –
формула массива
=СУММ(ЕСЛИ(ЕНД(
E4:E8
);0;
E4:E8
))
После ввода формулы, нужно вместо
ENTER
нажать
CTRL
+
SHIFT
+
ENTER
.
Еще один вариант с применением
формулы массива
=СУММ(ЕСЛИ(ЕЧИСЛО(
E4:E8
);
E4:E8
))