Note: The other languages of the website are Google-translated. Back to English

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

Цей підручник покаже вам, як підсумувати значення, якщо клітинки містять конкретний або частковий текст в іншому стовпці. Візьміть наведений нижче діапазон даних як приклад, щоб отримати загальну кількість товарів, що містять текст «Футболка», як функція 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 - допомагає виділитися з натовпу

Бажаєте виконувати щоденну роботу швидко та якісно? Kutools для Excel надає 300 потужних розширених функцій (об’єднання робочих книг, підсумовування за кольором, розділення вмісту клітинок, перетворення дати тощо) і заощаджує 80% вашого часу.

  • Розроблений для 1500 сценаріїв роботи, допомагає вирішити 80% проблем Excel.
  • Щодня зменшуйте тисячі клацань клавіатури та миші, знімайте втомлені очі та руки.
  • Станьте експертом Excel за 3 хвилини. Більше не потрібно згадувати будь-які болісні формули та коди VBA.
  • Безкоштовна 30-денна безкоштовна пробна версія. 60-денна гарантія повернення грошей. Безкоштовне оновлення та підтримка протягом 2 років.
Стрічка Excel (із встановленим Kutools для Excel)

Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)

  • Одна секунда для перемикання між десятками відкритих документів!
  • Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
  • Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
  • Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
Знімок екрана Excel (із встановленою вкладкою Office)
Коментарі (0)
Поки немає оцінок. Оцініть першим!
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL