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

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

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

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

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

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

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

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

примітки: У наведеній вище формулі, 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.

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

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