Сложение с одним критерием в EXCEL (Часть2.ТЕКСТовый критерий)

Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.

Пусть дана таблица с перечнем наименований фруктов и их количеством (см. файл примера ).

Если в качестве диапазона, к которому применяется критерий, выступает диапазон с текстовыми значениями, то можно рассмотреть несколько типов задач суммирования:

• суммирование значений, если соответствующие им ячейки в диапазоне поиска соответствуют критерию (простейший случай);
• в критерии применяются подстановочные знаки (*, ?) ;
• критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСтРА .

Рассмотрим эти задачи подробнее.

Значение соответствует критерию

Найдем количество всех значений "Яблоки" , т.е. просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты в точности равно "Яблоки" (без учета РЕГИСТРА) .

Для подсчета используем формулу =СУММЕСЛИ(A3:A13;"яблоки";B3:B13)

Критерий яблоки можно поместить в ячейку D 5 , тогда формулу можно переписать следующим образом: =СУММЕСЛИ(A3:A13;D5;B3 :B13 )

В качестве диапазона суммирования можно указать лишь первую ячейку диапазона - функция СУММЕСЛИ() просуммирует все правильно: =СУММЕСЛИ(A3:A13; D5 ;B3)

В критерии применяются подстановочные знаки (*, ?)

Просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты содержит слово Яблоки (без учета РЕгиСТРА) .

Для решения этой задачи используем подстановочные знаки (*, ?) . Подход заключается в том, что для отбора текстовых значений в качестве критерия задается лишь часть текстовой строки. Например, для отбора всех ячеек, содержащих слова яблоки ( свежие яблоки , яблоки местные и пр.) можно использовать критерии с подстановочным знаком * (звездочка). Для этого нужно использовать конструкцию * яблоки* .

Решение задачи выглядит следующим образом (учитываются значения содержащие слово яблоки в любом месте в диапазоне поиска): =СУММЕСЛИ($A$3:$A$13;"*яблоки*";B3)

Альтернативный вариант без использования подстановочных знаков выглядит более сложно: =СУММПРОИЗВ(B3:B13*НЕ(ЕОШ(ПОИСК("яблоки";A3:A13))))

Примеры, приведенные ниже, иллюстрируют другие применения подстановочных знаков.

Задача . Просуммировать значения, если соответствующие ячейки:

Критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСТРА

Учет РЕгиСТра приводит к необходимости создания более сложных формул. Чаще всего используются формулы на основе функций НАЙТИ() и СОВПАД() учитывающих регистр.

Ниже приведены формулы для суммирования чисел, если соответствующие значения совпадают с критерием с учетом регистра.

СОВЕТ: Для сложения с несколькими критериями воспользуйтесь статьей Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И) .

В статье Сложение по условию (один Числовой критерий) рассмотрен случай, когда критерий применяется к числовым значениям из диапазона, по которому производится суммирование.