Суміф із кількома критеріями на основі логіки АБО та І
Під час роботи з аркушами Excel вам може знадобитися підсумовувати значення на основі кількох критеріїв. Іноді кілька критеріїв походять з одного стовпця (логіка АБО), але іноді з різних стовпців (логіка І). Як у цьому випадку впоратися з цим завданням у Excel?
- Суміф із кількома критеріями на основі логіки АБО
- Sumif з кількома критеріями на основі логіки І за допомогою функції SUMIFS
Суміф із кількома критеріями на основі логіки АБО
Припустимо, що у вас є список продуктів, який містить постачальника та кількість, як показано на скріншоті нижче, тепер ви хочете отримати всі загальні кількості, які постачають постачальник Джон і Девід. Тут я наведу для вас дві прості формули.
За допомогою формули SUMIF + SUMIF +…
Якщо ви хочете підсумувати числа, які відповідають одному з критеріїв (логіка АБО) з кількох критеріїв, ви можете додати кілька функцій SUMIF в одну формулу, загальний синтаксис:
- criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
- criteria1: Перший критерій, який використовується для визначення клітинок, які потрібно підсумувати;
- criteria2: Другий критерій, який використовується для визначення клітинок, які потрібно підсумувати;
- sum_range: діапазон комірок, з яких потрібно підсумувати.
Тепер скопіюйте або введіть будь-яку з наведених нижче формул у порожню клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ для отримання результату:
=SUMIF(B4:B14,E4,C4:C14) + SUMIF(B4:B14,E5,C4:C14) (Use a cell reference)
Пояснення формули:
=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)
- Перший SUMIF(B4:B14,"Іван",C4:C14) знаходить рядки Івана і підсумовує загальні кількості;
- Друга SUMIF(B4:B14,"Давид",C4:C14) знаходить рядки Давида і підсумовує загальні величини;
- Потім складіть ці дві формули SUMIF, щоб отримати всі загальні кількості, надані Джоном і Девідом.
За допомогою функцій SUM та SUMIF
Наведена вище формула дуже проста у використанні, якщо існує лише пара критеріїв, але якщо ви хочете підсумувати значення з кількома умовами АБО, наведена вище формула може бути зайвою. У цьому випадку краща формула, створена на основі функцій SUM і SUMIF, може зробити вам послугу. Загальні синтаксиси:
Загальна формула з жорстко закодованим текстом:
- criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
- criteria1: Перший критерій, який використовується для визначення клітинок, які потрібно підсумувати;
- criteria2: Другий критерій, який використовується для визначення клітинок, які потрібно підсумувати;
- sum_range: діапазон комірок, з яких потрібно підсумувати.
Загальна формула з посиланнями на клітинки:
Array formula, should press Ctrl + Shift + Enter keys together.
- criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
- criteria_cells: клітинки, які містять критерії, які потрібно використовувати;
- sum_range: діапазон комірок, з яких потрібно підсумувати.
Будь ласка, введіть або скопіюйте будь-яку з наведених нижче формул у порожню клітинку, а потім отримайте результат:
=SUM(SUMIF(B4:B14, E4:E5, C4:C14)) (Use cell references, array formula, should press Ctrl + Shift + Enter keys)
Пояснення формули:
=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))
> SUMIF(B4:B14, {"John","David"}, C4:C14):
- {"Джон","Девід"}: константа масиву, яка являє собою набір кількох критеріїв, наданих у фігурних дужках.
- SUMIF(B4:B14,"Давид",C4:C14) Константа масиву, що використовує логіку АБО, змушує функцію SUMIF сумувати числа в C4:C14 на основі будь-якого з множинних критеріїв («Джон» і «Девід»), і вона поверне два окремі результати: {410,1520}.
> SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))=SUM({410,1520}): Нарешті, ця функція SUM підсумовує ці результати масиву, щоб повернути результат: 1930.
Sumif з кількома критеріями на основі логіки І за допомогою функції SUMIFS
Якщо ви хочете підсумувати значення з кількома критеріями в різних стовпцях, ви можете використовувати функцію SUMIF для швидкого вирішення цього завдання. Загальний синтаксис:
- sum_range: діапазон комірок, з яких потрібно підсумувати;
- criteria_range1: Діапазон, де застосовується criteira1;
- criteria1: Перший критерій, який перевіряється щодо діапазону_критеріїв1 і визначає, які клітинки додати; (Типом критеріїв може бути: число, логічний вираз, посилання на клітинку, текст, дата або інша функція Excel.)
- criteria_range2, criteria2…: Інші додаткові діапазони та пов'язані з ними критерії. (Ви можете встановити 127 пар критеріїв діапазону та критеріїв у формулі SUMIFS.)
Скажімо, у мене є таблиця зі стовпцями Продукт, Постачальник, Кількість і Ціна, як показано нижче. Тепер я хочу дізнатися суму загальної ціни продукту Apple і кількості, що більше або дорівнює 200.
Будь ласка, застосуйте будь-яку з наведених нижче формул у порожню клітинку та натисніть Що натомість? Створіть віртуальну версію себе у ключ для повернення результату:
=SUMIFS(C4:C14,A4:A14,G3,C4:C14,G4) (Use cell references)
Пояснення формули:
=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")
- Діапазон A4:A14 перевіряє продукт «Apple», а діапазон C4:C14 витягує всі кількості, що перевищують або дорівнюють 200, тоді функція SUMIFS підсумовує лише відповідні клітинки з діапазону D4:D14.
Використана відносна функція:
- SUMIF:
- Функція SUMIF може допомогти підсумувати клітинки на основі одного критерію.
- SUMIFS:
- Функція SUMIFS в Excel може допомогти підсумувати значення клітинок на основі кількох критеріїв.
Більше статей:
- Сума, якщо починається або закінчується певним текстом або символами
- Щоб підсумувати значення, якщо відповідні клітинки починаються або закінчуються певним значенням, ви можете застосувати функцію SUMIF із символом підстановки (*). У цій статті буде детально описано, як використовувати формулу.
- Сума, якщо клітинки містять певний текст в іншому стовпці
- Цей підручник покаже вам, як підсумувати значення, якщо клітинки містять конкретний або частковий текст в іншому стовпці. Візьміть наведений нижче діапазон даних як приклад, щоб отримати загальну кількість товарів, що містять текст «Футболка», як функція SUMIF і SUMPRODUCT можуть впоратися з цим завданням у Excel.
- Сума найменших або найнижчих N значень в Excel
- У Excel нам легко підсумувати діапазон комірок за допомогою функції SUM. Іноді вам може знадобитися підсумувати найменші чи нижчі 3, 5 чи n чисел у діапазоні даних, як показано нижче. У цьому випадку SUMPRODUCT разом із функцією SMALL можуть допомогти вам вирішити цю проблему в Excel.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
