Перейти до основного матеріалу

Сума, якщо клітинки містять певний текст в іншому стовпці

Цей підручник покаже вам, як підсумувати значення, якщо клітинки містять конкретний або частковий текст в іншому стовпці. Візьміть наведений нижче діапазон даних як приклад, щоб отримати загальну кількість товарів, що містять текст «Футболка», як функція SUMIF і SUMPRODUCT можуть впоратися з цим завданням у Excel.


Підсумуйте значення, якщо клітинка містить конкретний або частковий текст із функцією SUMIF

Щоб підсумувати значення, якщо клітинка містить певний текст в іншому стовпці, ви можете використовувати функцію SUMIF із символом підстановки (*), загальний синтаксис:

Загальна формула з жорстко закодованим текстом:

=SUMIF(range,"*text*",sum_range)
  • range: діапазон даних, який потрібно оцінити за допомогою критеріїв;
  • *text*: критерії, на основі яких потрібно підсумувати значення. Тут символ * використовується для пошуку будь-якої кількості символів, щоб знайти відповідність усім елементам, які містять певний текст, помістити текст між двома символами *. (Зверніть увагу, що ви повинні взяти текст і підстановку в подвійні лапки.)
  • sum_range: діапазон комірок із відповідними числовими значеннями, які потрібно підсумувати.

Загальна формула з посиланням на клітинку:

=SUMIF(range,"*"&cell&"*",sum_range)
  • range: діапазон даних, який потрібно оцінити за допомогою критеріїв;
  • "*"&cell&"*": критерії, на основі яких потрібно підсумувати значення;
    • *: символ підстановки, який знаходить будь-яку кількість символів.
    • осередок: клітинка містить конкретний текст, який потрібно шукати.
    • &: Цей оператор конкатенації (&) використовується для з’єднання посилання на клітинку із зірочками.
  • sum_range: діапазон комірок із відповідними числовими значеннями, які потрібно підсумувати.

Дізнавшись основні принципи роботи функції, скористайтеся будь-якою з наведених нижче формул, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання результату:

=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)

примітки: Ця функція SUMIF не чутлива до регістру.


Сума значень, якщо клітинка містить конкретний або частковий текст із функцією SUMPRODUCT

Функція SUMPRODUCT також може допомогти вам завершити цю роботу без використання символів підстановки. Тут ви повинні обернути функції ISNUMBER і SEARCH всередині функції SUMPRODUCT, загальний синтаксис:

=SUMPRODUCT(sum_range *(ISNUMBER(SEARCH(criteria,range))))
  • sum_range: діапазон комірок із відповідними числовими значеннями, які потрібно підсумувати;
  • criteria: критерії, на основі яких потрібно підсумувати значення. Це може бути посилання на клітинку або певний визначений вами текст;
  • range: діапазон даних, який потрібно оцінити за допомогою критеріїв;

Будь ласка, використовуйте будь-яку з наведених нижче формул у порожню клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ для повернення результату:

=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)


Пояснення цієї формули:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(ПОШУК("Футболка",$A$2:$A$12))))

  • ПОШУК("Футболка",$A$2:$A$12): Функція SEARCH повертає місцезнаходження конкретного тексту «T-Shirt» з діапазону даних A2:A12, отже, ви отримаєте такий масив: {5;#VALUE!;#VALUE!;7;#VALUE! ;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}.
  • ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): Ця функція ISNUMBER використовується для перевірки числових значень і повертає новий масив: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
  • $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}: Щоб помножити ці два масиви, математична операція встановлює значення TRUE і FALSE до 1s і 0s. Отже, множення двох масивів буде таким: {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}):Нарешті, функція SUMPRODUCT підсумовує всі значення в масиві, щоб отримати результат:1605.

Використана відносна функція:

  • SUMIF:
  • Функція SUMIF може допомогти підсумувати клітинки на основі одного критерію.
  • SUMPRODUCT:
  • Функцію SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім отримати суму добутків.
  • ISNUMBER:
  • Функція Excel ISNUMBER повертає TRUE, якщо клітинка містить число, і FALSE, якщо ні.
  • ПОШУК:
  • Функція ПОШУК може допомогти вам знайти позицію певного символу або підрядка з даного текстового рядка

Більше статей:

  • Сума найменших або найнижчих N значень
  • У Excel нам легко підсумувати діапазон комірок за допомогою функції SUM. Іноді вам може знадобитися підсумувати найменші чи нижчі 3, 5 чи n чисел у діапазоні даних, як показано нижче. У цьому випадку SUMPRODUCT разом із функцією SMALL можуть допомогти вам вирішити цю проблему в Excel.

Найкращі інструменти для підвищення продуктивності офісу

Kutools для Excel - допомагає виділитися з натовпу

Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер VLookup: Кілька критеріїв  |  Множинне значення  |  На кількох аркушах  |  Нечіткий пошук...
Adv. Випадаючий список: Легкий спадний список  |  Залежний спадний список  |  Виберіть розкривний список, що вибирається...
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  Порівняти стовпці з Виберіть однакові та різні клітини ...
Особливості: Фокус сітки  |  Перегляд дизайну  |  Велика панель формул  |  Диспетчер робочих книг і аркушів | Бібліотека ресурсів (автотекст)  |  Вибір дати  |  Об’єднайте робочі аркуші  |  Шифрування/розшифрування клітинок  |  Надсилайте листи за списком  |  Супер фільтр  |  Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи ...)  |  50 + Графік типи (діаграма Ганта ...)  |  40+ Практичний Формули (Розрахуйте вік на основі дня народження ...)  |  19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху ...)  |  12 Перетворення Tools (Числа до слів, Валютна конверсія ...)  |  7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділити клітинки 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