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

Суміф із кількома критеріями на основі логіки АБО та І

Під час роботи з аркушами Excel вам може знадобитися підсумовувати значення на основі кількох критеріїв. Іноді кілька критеріїв походять з одного стовпця (логіка АБО), але іноді з різних стовпців (логіка І). Як у цьому випадку впоратися з цим завданням у Excel?


Суміф із кількома критеріями на основі логіки АБО

Припустимо, що у вас є список продуктів, який містить постачальника та кількість, як показано на скріншоті нижче, тепер ви хочете отримати всі загальні кількості, які постачають постачальник Джон і Девід. Тут я наведу для вас дві прості формули.

За допомогою формули SUMIF + SUMIF +…

Якщо ви хочете підсумувати числа, які відповідають одному з критеріїв (логіка АБО) з кількох критеріїв, ви можете додати кілька функцій SUMIF в одну формулу, загальний синтаксис:

=SUMIF(criteria_range, criteria1, sum_range)+SUMIF(criteria_range, criteria2, sum_range)+…
  • criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
  • criteria1: Перший критерій, який використовується для визначення клітинок, які потрібно підсумувати;
  • criteria2: Другий критерій, який використовується для визначення клітинок, які потрібно підсумувати;
  • sum_range: діапазон комірок, з яких потрібно підсумувати.

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

=SUMIF(B4:B14,"John",C4:C14) + SUMIF(B4:B14,"David",C4:C14)           (Type the criteria manually)
=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, може зробити вам послугу. Загальні синтаксиси:

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

=SUM(SUMIF(criteria_range, {criteria1,criteria2,...}, sum_range))
  • criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
  • criteria1: Перший критерій, який використовується для визначення клітинок, які потрібно підсумувати;
  • criteria2: Другий критерій, який використовується для визначення клітинок, які потрібно підсумувати;
  • sum_range: діапазон комірок, з яких потрібно підсумувати.

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

{=SUM(SUMIF(criteria_range, criteria_cells, sum_range))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • criteria_range: діапазон комірок, яким потрібно відповідати критеріям;
  • criteria_cells: клітинки, які містять критерії, які потрібно використовувати;
  • sum_range: діапазон комірок, з яких потрібно підсумувати.

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

=SUM(SUMIF(B4:B14, {"John","David"}, C4:C14))           (Type the criteria manually)
=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 для швидкого вирішення цього завдання. Загальний синтаксис:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
  • sum_range: діапазон комірок, з яких потрібно підсумувати;
  • criteria_range1: Діапазон, де застосовується criteira1;
  • criteria1: Перший критерій, який перевіряється щодо діапазону_критеріїв1 і визначає, які клітинки додати; (Типом критеріїв може бути: число, логічний вираз, посилання на клітинку, текст, дата або інша функція Excel.)
  • criteria_range2, criteria2…: Інші додаткові діапазони та пов'язані з ними критерії. (Ви можете встановити 127 пар критеріїв діапазону та критеріїв у формулі SUMIFS.)

Скажімо, у мене є таблиця зі стовпцями Продукт, Постачальник, Кількість і Ціна, як показано нижче. Тепер я хочу дізнатися суму загальної ціни продукту Apple і кількості, що більше або дорівнює 200.

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

=SUMIFS(D4:D14,A4:A14,"Apple",C4:C14,">=200")           (Type the criteria manually)
=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 і SUMPRODUCT можуть впоратися з цим завданням у Excel.
  • Сума найменших або найнижчих N значень в Excel
  • У 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)
Коментарі (1)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
чи знаєте ви, хлопці, чи працює ця логіка в таблицях Google?
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

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

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