Сложение с одним критерием в 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))))

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

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

Задача

Критерий

Формула

Результат

Примечание

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

*яблоки

= СУММЕСЛИ($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.Условие И) .

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

Файл примера

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved