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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача

Критерий

Формула

Результат

Примечание

заканчиваются на слово яблоки, например, Свежие яблоки

*яблоки

=СУММЕСЛИ($A$3:$A$13; "*яблоки";B3)

11

Использован подстановочный знак * (перед значением)

начинаются на слово яблоки, например,  яблоки местные

яблоки*

=СУММЕСЛИ($A$3:$A$13; "яблоки*";B3)

20

Использован подстановочный знак * (после значения)

начинаются с гру и содержат ровно 6 букв

гру???

=СУММЕСЛИ($A$3:$A$13; "гру???";B3)

56

Использован подстановочный знак ?

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

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

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

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

Критерий

Формула

Результат

Примечание

в точности равны Яблоки с учетом регистра

Яблоки

=СУММ(ЕСЛИ(СОВПАД( "Яблоки";A3:A13);1;0)*B3:B13)

4

использована формула массива

содержат значение Яблоки в любом месте текстовой строки с учетом регистра 

Яблоки

=СУММ(ЕСЛИ( СОВПАД("Яблоки";A3:A13);1;0) *B3:B13)

15

использована формула массива

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (3 оценок)

Комментарии

ТАТЬЯНА (не проверено)

спасибо!!! то что и было нужно!!1 все получилось!!1