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

Досліджуйте нове, Безкоштовна версія Kutools для Outlook сьогодні!

 

Сума, якщо клітинки містять або дорівнюють x або y в Excel

Автор: Силувій Остання зміна: 2021-11-23

Щоб підсумувати клітинки, якщо вони містять або дорівнюють тому чи іншому значенню, у цьому посібнику наведено дві докладні формули, які допоможуть вирішити проблеми.

Як підсумувати, якщо клітинки містять x або y в Excel?
Як підсумувати, якщо клітинки дорівнюють x або y в Excel?

Як підсумувати, якщо клітинки містять x або y в Excel?

Як показано на знімку екрана нижче, щоб підсумувати клітинки в стовпці D, якщо клітинки в стовпці C містять «яблуко» або «лічі», ви можете застосувати формулу на основі функцій SUMPRODUCT, ISNUMBER і SEARCH.

Загальні формули

=SUMPRODUCT(--((ISNUMBER(SEARCH("criteria1",rng1)) + ISNUMBER(SEARCH("criteria2",rng2)))>0),sum_rng)

Аргументи

Критерій_діапазону1: діапазон комірок, які потрібно оцінити за критеріями1;
Діапазон_критеріїв2: діапазон комірок, які потрібно оцінити за критеріями2;
Критерії1: критерії, які використовуються для визначення клітинок у Criteria_rang1;
Критерії2: критерії, які використовуються для визначення клітинок у Criteria_rang2.
Діапазон_суми: діапазон комірок для підсумовування;

Як користуватися цією формулою?

Виділіть порожню клітинку, скопіюйте або введіть формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=SUMPRODUCT(--((ISNUMBER(SEARCH(G4,C5:C12))+ISNUMBER(SEARCH(G5,C5:C12)))>0),D5:D12)

примітки: У цій формулі G4 і G5 є клітинками, що містять дві умови. Крім того, ви можете безпосередньо ввести фактичні текстові значення, узяті в подвійні лапки.

=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",C5:C12))+ISNUMBER(SEARCH("lychee",C5:C12)))>0),D5:D12)

Пояснення формули

=SUMPRODUCT(--((ISNUMBER(SEARCH("apple",C5:C12))+ISNUMBER(SEARCH("lychee",C5:C12)))>0),D5:D12)

ISNUMBER(ПОШУК("яблуко",C5:C12))
> ПОШУК("яблуко",C5:C12): функція SEARCH знаходить позицію першого символу «яблуко» у кожній клітинці діапазону C5:C12 і повертає такий масив {#VALUE!;#VALUE!;1;9;1;#VALUE!; #ЗНАЧЕННЯ!;18}. Значення #ЗНАЧЕННЯ! означають, що «яблуко» не знайдено у відповідній клітинці;
> ISNUMBER({#VALUE!;#VALUE!;1;9;1;#VALUE!;#VALUE!;18}): Тут функція ISNUMBER перевіряє, чи є посилання на значення в масиві числовим чи нечисловим. Якщо параметр значення є числовим, він повертає «TRUE», якщо ні, він повертає «FALSE». Тоді ви отримаєте новий масив {FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE}.
ISNUMBER(ПОШУК("лічі",C5:C12)): Пояснення цієї частини таке ж, як і вище. Тут функція ISNUMBER нарешті повертає такий масив {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE};
{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE} + {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE}: Потім ми додаємо ці масиви разом, і математична операція перетворює значення TRUE і FALSE в 1s і 0s. Нарешті ми отримуємо числовий масив {0;0;1;2;1;0;1;2};
({0;0;1;2;1;0;1;2}) > 0: Кожне число в масиві порівнюється з числом 0 і повертає масив TRUE та FALSE, наприклад {FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE};
---({FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE}): Ці два знаки мінус перетворюють «TRUE» на 1 і перетворюють «FALSE» на 0. Тут ви отримаєте новий масив як {0;0;1;1;1;0;1;1};
SUMPRODUCT({0;0;1;1;1;0;1;1},D5:D12) = SUMPRODUCT({0;0;1;1;1;0;1;1},{2000;1600;980;1200;600;900;150;300}): Функція SUMPRODUCT перемножує відповідні компоненти двох масивів і повертає масив як {0;0;980;1200;600;0;150;300}, потім підсумовує всі результати в масиві і нарешті повертає 3230.

Як підсумувати, якщо клітинки дорівнюють x або y в Excel?

У розділі вище ми ввели формулу для підсумовування клітинок, якщо вони містять те чи інше значення. У цьому розділі буде показано, як додати дві функції SUMIF разом, щоб підсумувати клітинки, якщо вони дорівнюють x або y.

У наведеному нижче прикладі підсумовуються продажі, якщо продукт дорівнює «яблуку» або «лічі».

Загальна формула

=SUMIF(criteria_range1,"criteria1",sum_range)+SUMIF(criteria_range2,"criteria2",sum_range)

Аргументи

Критерій_діапазону1: діапазон комірок, які потрібно оцінити за критеріями1;
Діапазон_критеріїв2: діапазон комірок, які потрібно оцінити за критеріями2;
Критерії1: критерії, які визначають, які клітинки в Criteria_rang1 будуть додані.
Критерії2: критерії, які визначають, які клітинки в Criteria_rang2 будуть додані.
Діапазон_суми: діапазон комірок для підсумовування;

Як користуватися цією формулою?

Виділіть порожню клітинку, скопіюйте або введіть в неї формулу нижче та натисніть кнопку Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=SUMIF(C5:C12,G4,D5:D12)+SUMIF(C5:C12,G5,D5:D12)

примітки: Ця формула посилається на дві клітинки G4 і G5, які містять дві умови. Ви можете безпосередньо ввести текстові значення, узяті в подвійні лапки, як критерії у формулі.

=SUMIF(C5:C12,"apple",D5:D12)+SUMIF(C5:C12,"lychee",D5:D12)

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

=SUMIF(C5:C12,"apple",D5:D12)+SUMIF(C5:C12,"lychee",D5:D12)

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

Супутні функції

Функція Excel SUMPRODUCT
Функцію Excel SUMPRODUCT можна використовувати для множення двох або більше стовпців або масивів разом, а потім для отримання суми продуктів.

Функція Excel ISNUMBER
Функція Excel ISNUMBER повертає TRUE, якщо клітинка містить число, і FALSE, якщо ні.

Функція ПОШУК в Excel
Функція ПОШУК Excel може допомогти вам знайти позицію певного символу або підрядка з заданого текстового рядка

Функція Excel SUMIFS
Функція Excel SUMIF може допомогти підсумувати значення клітинок на основі кількох критеріїв.

Пов’язані формули

Суму, якщо клітинки містять зірочку
Щоб підсумувати клітинки, що містять зірочку, яка вважається буквальним символом, а не символом підстановки, у цьому підручнику пояснюється формула на основі функції SUMIF та тильди (~).

Суму, якщо клітинки містять і x, і y
У цьому покроковому посібнику показано, як підсумувати діапазон комірок, якщо відповідні клітинки містять і x, і y за допомогою функції SUMIFS.

Сума, якщо дата знаходиться між двома датами
У цьому посібнику показано, як підсумувати значення в межах певного діапазону дат у Excel за допомогою формули на основі функції SUMIFS.

Суму, якщо клітинки рівні чи не дорівнюють певному значенню
У цьому покроковому посібнику пояснюються дві формули для підсумовування діапазону комірок, які дорівнюють чи не відповідають певному значенню на основі функції SUMIF.

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

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

🤖 Kutools AI Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати  |  Видалити порожні рядки  |  Об’єднайте стовпці або клітинки без втрати даних  |  Раунд без Формули ...
Супер 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.