В тази статия ще научим как да броим редове с критерии SUMPRODUCT в Excel.
Сценарий:
С прости думи, докато работим с данни, понякога трябва да преброим клетките, където 2 диапазона отговарят на критериите. Условното броене може да се извърши с помощта на функцията COUNTIF, но това не е единствената функция, която може да го направи. Функцията SUMPRODUCT е универсална функция, която може да се използва за преброяване с критерии, понякога дори по -добри от функцията Counti.
Как да решим проблема?
За тази статия ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от тези функции. Тук ни се дават данни за два диапазона и трябва да преброим стойностите, при които формулата отговаря на критериите.
Обща формула:
= SUMPRODUCT ( - (диапазон1 оператор диапазон2))
диапазон 1 : 1 -ви диапазон
диапазон2 : 2 -ри обхват
оператор: критерии, условие, дадено като оператор между 2 диапазона
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук имаме рекорда за последните 7 големи мача на фен клубовете от Висшата лига.
Имаме рекорд за последните 7 мача с домакински отбор, срещу отбор, домакин и голове на противника.
Първо, трябва да намерим броя на мачовете, спечелени у дома от всеки отбор.
Сега ще използваме следната формула, за да получим броя на игрите, които са спечелили у дома.
Използвайте формулата:
= СУМПРОДУКТ ( - (E5: E11> F5: F11))
E5: E11 : 1 -ва гама, голове, отбелязани у дома
F5: F11 : 2 -ри диапазон, противникът вкарва голове
>: критерии, условие, дадено като по -голямо от оператора
Обяснение:
- Условието тук, което трябва да бъде изпълнено, е, че головете, отбелязани у дома, трябва да бъдат по -големи от головете, отбелязани от противника.
- E5: E11 (диапазон1) трябва да бъде по -голям от F5: F11 (диапазон2).
- -- оператор, използван за преобразуване на TRUE стойност в 1 & False стойност в 0.
- Функцията SUMPRODUCT получава сумата от 1s във върнатия масив, което ще бъде броят на необходимите печалби.
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите броя.
Както можете да видите, общите мачове, при които домакините имаха предимство и спечелиха, са 4 мача.
Горният пример беше лесен. Така че, за да стане интересно, ще преброим колко мача един отбор спечели в общите данни.
Първо, трябва да намерим броя на мачовете, спечелени у дома от всеки отбор.
Сега ще използваме следната формула, за да получим броя на игрите, Човече. Юнайтед спечели.
Използвайте формулата:
= СУМПРОДУКТ ( - (C5: C11 = G5) * (E5: E11> F5: F11) + (D5: D11 = G5) * (E5: E11 <F5: F11))
Обяснение:
- (C5: C11 = G5) * (E5: E11> F5: F11) проверява отбора домакин като човек. Юнайтед и отбеляза повече голове от противниковия отбор.
- (D5: D11 = G5) * (E5: E11 <F5: F11) проверява отбора като човек. Юнайтед и отбеляза повече голове от домакините.
- Функцията SUMPRODUCT получава сумата от 1s във върнатия масив, което ще бъде броят на необходимите победи от Man. Обединени.
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите броя.
Както можете да видите общите съвпадения, където човекът. Отборът на Юнайтед спечели 2 мача.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа.
- Формулата работи само когато няма дубликати в таблицата за търсене
- Функцията SUMPRODUCT разглежда нечисловите стойности като 0s.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина като функцията
Надявам се тази статия за това как да върнете броя, ако с SUMPRODUCT в Excel е обяснително. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
COUNTIFS с динамичен диапазон от критерии : Брой клетки в зависимост от други стойности на клетки в Excel.
COUNTIFS Две съвпадения на критерии: Пребройте клетките, отговарящи на два различни критерия в списъка в Excel.
ИЗЧИСЛЕНИЯ С ИЛИ За множество критерии : Брой клетки, които имат множество критерии, които съвпадат с помощта на функцията ИЛИ.
Функцията COUNTIFS в Excel : Преброяване на клетки в зависимост от други стойности на клетките.
Как да използвате Countif във VBA в Microsoft Excel : Пребройте клетките, като използвате кода на Visual Basic за приложения.
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Конвертирайте инчове в крака и инчове в Excel 2016
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В