Както споменах в много от моите блогове, че SUMPRODUCT е много гъвкава функция и може да се използва за множество цели. В тази статия ще видим как можем да използваме тази функция за преброяване на стойности с множество критерии ИЛИ.
Обща формула на SUMPRODUCT за броене с множество или критерии
| = SUMPRODUCT (-(((критерии 1)+(критерии 2)+… )>0) |
Критерии 1: Това е всеки критерий, който връща масив от TRUE и FALSE.
Критерии 2: Това е следващият критерий, който искате да проверите. По същия начин можете да имате толкова критерии, колкото искате.
Горната обща формула често се променя, за да отговаря на изискванията за броене с множество критерии ИЛИ. Но основната формула е следната. Първо ще видим как това работи чрез пример и след това ще обсъдим други сценарии, при които ще трябва да промените тази формула малко.
Пример: Брой потребители, ако е код на дилър или годинаСъвпадения Използване на SUMPRODUCT
Тук имаме набор от данни за продавачи. Данните съдържат много колони. Това, което трябва да направим, е да преброим броя на потребителите, които имат код „INKA“ или годината е „2016“. Уверете се, че ако някой има и двете (код като "inka" и 2016 година), той трябва да се брои като 1.
И така, тук имаме два критерия. Използваме гореспоменатата формула на СУМПРОДУКТ:
| = SUMPRODUCT (-(((Код = I3)+(Година = K3))> 0)) |
Тук кодът и годината са именувани диапазони.
Това връща 7.
В данните имаме 5 записа на INKA код и 4 записа на 2016 г. Но 2 записа имат както „INKA“, така и 2016 като код и съответно година. Тези записи се броят като 1. И така получаваме 7.
Как работи?
Така че нека да разгледаме как формулата се решава стъпка по стъпка, след което ще обсъдя как работи.
| =СУМПРОДУКТ(-(((Код = I3)+(Година = K3))> 0)) |
| 1=>СУМПРОДУКТ(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>СУМПРОДУКТ(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>СУМПРОДУКТ(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>СУМПРОДУКТ({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
В първата стъпка стойността на I3 ("INKA") се сравнява с всяка клетка в кодовия диапазон. Това връща масив от TRUE и FALSE. ИСТИНА за всеки мач. За да спестя място, не съм показал всички TRUE-FALSE. По същия начин стойността на K3 (2016) се съпоставя с всяка клетка в годишния диапазон.
В следващата стъпка добавяме тези два масива, които водят до нов масив от числови стойности. Както може би знаете, TRUE се третира като 1 и FALSE като 0 в Excel. Така че, когато се добавят TRUE и TRUE, получаваме 2, а останалото можете да разберете.
В следващата стъпка проверяваме коя стойност е по -голяма от 0 в масива. Това отново превръща масива в истински невярен масив. За всяка стойност 0, която получаваме, False и rest се преобразуват като true. Сега броят на TRUE стойностите в масива е нашият отговор. Но как да ги преброим? Ето как.
Двойните отрицателни (-) знаци се използват за преобразуване на булевите стойности в 1s и 0s. Така всяка TRUE стойност в масива се преобразува в 1 и FALSE в 0.
В последната стъпка SUMPRODUCT обобщава този масив и получаваме отговора си като 7.
Добавяне на още или критерии за броене с помощта на SUMPRODUCT
Така че, ако трябва да добавите още или критерии за броене, можете просто да добавите критерии, използвайки знак + към функцията.
Например, ако искате да добавите друг критерий към горната формула, така че да добави броя на служителите, които са продали повече от 5 продукта. Формулата SUMPRODUCT просто ще изглежда така:
| = SUMPRODUCT (-(((Код = I3)+(Година = K3)+(Продажби> 5))> 0)) |
Просто! нали?
Но да предположим, че искате да имате два критерия Код диапазон. Да предположим, че искате да преброите "INKB". И така, как го правите? Един метод използва горната техника, но това би било повтарящо се. Да речем, че искам да добавя още 10 критерия от същия диапазон. В такива случаи тази техника не е толкова умна за броене с SUMPRODUCT.
Да приемем, че имаме подредени по този начин данни.
Кодовете на критериите са в един ред I2: J2. Подреждането на данните е важно тук. Формулата SUMPRODUCT за 3 настройки за броене на критерии ИЛИ ще бъде:
| = SUMPRODUCT (-(((Код = I2: J2)+(Година = I3: J3))> 0)) |
Това е формулата SUMPRODUCT за отчитане с множество критерии, когато множество критерии от един диапазон са написани в един ред.
Това връща верния отговор, който е 10.
Ако въведете всяка година в J3, формулата ще добави и този брой.
Това се използва, когато критериите са в един ред. Ще работи ли, когато критериите са в една колона за същия диапазон? Не. Няма да стане.
В този пример имаме множество кодове за преброяване, но тези кодове са написани в една колона. Когато използваме горната формула SUMPRODUCT, получаваме Ans #N/A грешка. Няма да навлизаме в появата на тази грешка, тъй като това ще направи тази статия твърде дълга. Нека да видим как можем да накараме тази работа да работи.
За да работи тази формула, трябва да увиете критериите на кода във функцията TRANSPOSE. Така формулата ще работи.
| = SUMPRODUCT (-(((Код = TRANSPOSE (H3: H4))+(Година = TRANSPOSE (I3: I4)))> 0)) |
Това е формулата за преброяване с множество или условия в същия диапазон, когато критериите са изброени в колона.
Така че да колега, надявам се да съм бил достатъчно ясен и да е имал смисъл. Надявам се да служи на вашата цел да бъдете тук. Ако тази формула не реши проблема ви, кажете ми вашите изисквания в секцията за коментари по -долу. Ще се радвам да ви помогна по всякакъв начин. Можете да споменете всякакви съмнения, свързани с Excel/VBA. Дотогава продължете да се учите, продължете да превъзхождате.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel. Тази функция може да се използва за извършване на множество задачи. Това е една от най -универсалните функции.
COUNTIFS с динамичен диапазон от критерии : За да броим с диапазон от динамични критерии, просто използваме функцията INDIRECT. Тази функция може
ИЗЧИСЛЕНИЯ С ИЛИ За множество критерии : Брой клетки, които имат множество критерии, които съвпадат с помощта на функцията ИЛИ. За да поставите логика ИЛИ във функция COUNTIFS, няма да е необходимо да използвате функцията ИЛИ.
Използване на IF с AND / OR функции в Microsoft Excel : Тези логически функции се използват за изчисляване на множество критерии. С IF функциите OR и AND се използват за включване или изключване на съвпадения.
Как да използвате функцията ИЛИ в Microsoft Excel : Функцията се използва за включване на всички TRUE стойности в множество критерии.
Как да броите клетки, които съдържат това или онова в Excel в Excel : Към клетки, които съдържат това или онова, можем да използваме функцията SUMPRODUCT. Ето как правите тези изчисления.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате функцията Excel VLOOKUP| Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате Excel COUNTIF функция| Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
