Підраховуйте рядки, якщо вони відповідають кільком критеріям у 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.
Використана відносна функція:
- 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 років.
Вкладка Office - Увімкнути читання та редагування з вкладками в Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Приносить ефективні вкладки в Office (включаючи Excel), як і Chrome, Firefox та New Internet Explorer.
