Перейти к основному содержанию

Суммировать, если ячейки содержат определенный текст в другом столбце

Из этого туториала Вы узнаете, как суммировать значения, если ячейки содержат определенный или частичный текст в другом столбце. В качестве примера возьмем диапазон данных ниже, чтобы получить общее количество продуктов, содержащих текст «Футболка», с этой задачей в Excel могут справиться как функция СУММЕСЛИ, так и функция СУММПРОИЗВ.


Сумма значений, если ячейка содержит определенный или частичный текст с функцией СУММЕСЛИ

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

Общая формула с жестко закодированным текстом:

=SUMIF(range,"*text*",sum_range)
  • range: Диапазон данных, который вы хотите оценить с помощью критериев;
  • *text*: Критерии, по которым вы хотите суммировать значения. Здесь подстановочный знак * используется для поиска любого количества символов, чтобы сопоставить все элементы, содержащие определенный текст, поместить текст между двумя символами *. (Обратите внимание, что вы должны заключить текст и подстановочный знак в двойные кавычки.)
  • sum_range: Диапазон ячеек с совпадающими числовыми значениями, которые вы хотите суммировать.

Общая формула со ссылкой на ячейку:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: Диапазон данных, который вы хотите оценить с помощью критериев;
  • "*"&cell&"*": Критерии, по которым вы хотите суммировать значения;
    • *: Подстановочный знак, который находит любое количество символов.
    • ячейка: Ячейка содержит конкретный текст, который нужно искать.
    • &: Этот оператор конкатенации (&) используется для соединения ссылки на ячейку со звездочками.
  • sum_range: Диапазон ячеек с совпадающими числовыми значениями, которые вы хотите суммировать.

Зная основные операции функции, используйте любую из следующих формул, которые вам нужны, а затем нажмите Enter ключ для получения результата:

=SUMIF($A$2:$A$12,"*T-shirt*",$B$2:$B$12)                     (Type the criteria manually)
=SUMIF($A$2:$A$12,"*"&D2&"*",$B$2:$B$12)                 
 (Use a cell reference)

Внимание: Эта функция СУММЕСЛИ не чувствительна к регистру.


Сумма значений, если ячейка содержит определенный или частичный текст с функцией СУММПРОИЗВ

Функция СУММПРОИЗВ также может помочь вам завершить эту работу без использования подстановочных знаков. Здесь вы должны заключить функции ISNUMBER и SEARCH в функцию SUMPRODUCT, общий синтаксис:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: Диапазон ячеек с совпадающими числовыми значениями, которые вы хотите суммировать;
  • criteria: Критерии, по которым вы хотите суммировать значения. Это может быть ссылка на ячейку или определенный вами текст;
  • range: Диапазон данных, который вы хотите оценить с помощью критериев;

Введите любую из приведенных ниже формул в пустую ячейку и нажмите Enter ключ для возврата результата:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))          (Type the criteria manually)
=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH(D2,$A$2:$A$12))))                   
(Use a cell reference)


Объяснение этой формулы:

= СУММПРОИЗВ ($ B $ 2: $ B $ 12 * (ISNUMBER (ПОИСК («Футболка»; $ A $ 2: $ A $ 12))))

  • ПОИСК («Футболка», 2 доллара: 12 австралийских долларов): Функция ПОИСК возвращает местоположение определенного текста «Футболка» из диапазона данных A2: A12, поэтому вы получите массив, подобный этому: {5; #VALUE!; # VALUE!; 7; #VALUE! ; 7; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; # ЗНАЧЕНИЕ!; 7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Эта функция ISNUMBER используется для проверки числовых значений и возвращает новый массив: {ИСТИНА; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА}.
  • $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: Чтобы умножить эти два массива, математическая операция устанавливает значения ИСТИНА и ЛОЖЬ в 1 и 0. Итак, умножение двух массивов будет таким: {347; 428; 398; 430; 228; 379; 412; 461; 316; 420; 449} * {1; 0; 0; 1; 0; 1; 0; 0; 0; 0; 1} = {347; 0; 0; 430; 0; 379; 0; 0; 0; 0; 449}.
  • SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): Наконец, функция СУММПРОИЗВ суммирует все значения в массиве, чтобы получить результат: 1605.

Используемая относительная функция:

  • SUMIF:
  • Функция СУММЕСЛИ может помочь суммировать ячейки на основе одного критерия.
  • SUMPRODUCT:
  • Функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений.
  • НОМЕР:
  • Функция Excel ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.
  • ПОИСК:
  • Функция ПОИСК может помочь вам найти позицию определенного символа или подстроки в заданной текстовой строке.

Другие статьи:

  • Сумма наименьших или нижних значений N
  • В Excel легко суммировать диапазон ячеек с помощью функции СУММ. Иногда вам может потребоваться суммировать наименьшие или нижние 3, 5 или n чисел в диапазоне данных, как показано ниже. В этом случае СУММПРОИЗВ вместе с функцией МАЛЕНЬКИЙ могут помочь вам решить эту проблему в Excel.

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations