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

Підраховуйте рядки, якщо вони відповідають кільком критеріям у Excel

Порахуйте кількість рядків у діапазоні на основі кількох критеріїв, деякі з яких залежать від логічних тестів, які працюють на рівні рядків, функція SUMPRODUCT в Excel може вам допомогти.

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

Підраховуйте рядки, якщо вони відповідають кільком критеріям з функцією SUMPRODUCT


Підраховуйте рядки, якщо вони відповідають кільком критеріям з функцією SUMPRODUCT

Для підрахунку рядків, якщо вони відповідають кільком критеріям, за допомогою функції SUMPRODUCT в Excel загальний синтаксис такий:

=SUMPRODUCT((logical1)*(logical2))
  • logical1, logical2: Логічні вирази, що використовуються для порівняння значень.

1. Для підрахунку кількості рядків Apple, фактичний продаж яких перевищує запланований, будь ласка, застосуйте формулу нижче:

=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

примітки: У наведеній вище формулі, C2: C10> B2: B10 є першим логічним виразом, який порівнює значення у стовпці C зі значеннями у стовпці B; A2: A10 = E2 є другим логічним виразом, який перевіряє, чи існує комірка E2 у стовпці А.

2. Потім натисніть вводити ключ, щоб отримати потрібний результат, див. знімок екрана:


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

=SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))

  • $ C $ 2: $ C $ 10> $ B $ 2: $ B $ 10: Цей логічний вираз використовується для порівняння значень у стовпці C зі значеннями у стовпці B у кожному рядку, якщо значення у стовпці C більше, ніж значення у стовпці B, відображається TRUE, інакше відображається FALSE і повертається значення масиву: {TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
  • $ A $ 2: $ A $ 10 = E2: Цей логічний вираз використовується для перевірки наявності комірки E2 в діапазоні A2: A10. Отже, ви отримаєте такий результат: {TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE}.
  • ($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2): Операція множення використовується для множення цих двох масивів в один єдиний масив, щоб повернути результат таким чином: {1; 0; 1; 0; 0; 0; 0; 1; 0}.
  • SUMPRODUCT(($C$2:$C$10>$B$2:$B$10)*($A$2:$A$10=E2))= SUMPRODUCT({1;0;1;0;0;0;0;1;0}): Цей SUMPRODUCT додає числа в масиві та повертає результат: 3.

Використана відносна функція:

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

Більше статей:

  • Підрахувати рядки, якщо вони відповідають внутрішнім критеріям
  • Припустимо, у вас є звіт про реалізацію продукції за цей та минулий рік, а зараз вам може знадобитися підрахувати продукцію, де продажі в цьому році більші, ніж у минулому, або продажі в цьому році менші, ніж у минулому, як показано нижче показаний скріншот. Зазвичай, ви можете додати допоміжний стовпець для розрахунку різниці у продажу між двома роками, а потім використовувати COUNTIF для отримання результату. Але в цій статті я познайомлю функцію SUMPRODUCT, щоб отримати результат безпосередньо без стовпця -помічника.
  • Порахуйте відповідність між двома стовпцями
  • Наприклад, у мене є два списки даних у стовпці А та С, тепер я хочу порівняти ці два стовпці та порахувати, чи значення у стовпці А знайдено у стовпці С у тому ж рядку, що показано на скріншоті нижче. У цьому випадку функція SUMPRODUCT може бути найкращою функцією для вирішення цього завдання в Excel.
  • Кількість клітин дорівнює одному з багатьох значень
  • Припустимо, у мене є список продуктів у колонці А, тепер я хочу отримати загальну кількість конкретних продуктів Яблуко, Виноград та Лимон, які перераховані в діапазоні С4: С6 зі стовпця А, як показано на скріншоті нижче. Зазвичай у програмі Excel прості функції COUNTIF та COUNTIFS не працюватимуть у цьому сценарії. У цій статті я розповім про те, як швидко та легко вирішити цю роботу за допомогою комбінації функцій SUMPRODUCT та COUNTIF.

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

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)
Коментарі (2)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
=SUMPRODUCT({Array of True/False}) більше не підраховує значення True у масиві (як у формулах SUM або COUNT).
Але ви можете примусово перетворити True/False на 1 і 0, додавши оператор «--». безпосередньо перед масивом:
=SUMPRODUCT(--{Масив True/False}).
Ви також можете ввести цей оператор одразу після знака множення, вказавши дивний оператор «*--».

У цьому прикладі робочі формули будуть такими:
=SUMPRODUCT(--($C$2:$C$10>$B$2:$B$10)*--($A$2:$A$10=E2))
Цей коментар був мінімізований модератором на сайті
Привіт, професоре X,

Ви праві в одному сенсі. Подвійне від’ємне значення (--) є одним із кількох способів приведення значень ІСТИНА та ЛОЖЬ до їхніх числових еквівалентів, 1 та 0. Коли ми маємо 1 та 0, ми можемо виконувати різноманітні операції над масивами за допомогою логічної логіки.

Але нашій формулі не потрібен подвійний мінус (--), що робить формулу більш компактною. Це пояснюється тим, що математична операція множення (*) автоматично перетворює значення TRUE і FALSE на 1 і 0. Гарного дня.

З повагою,
Менді
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

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

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