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

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

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

Як підсумувати, якщо клітинки містять 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 для 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