Као што сам споменуо у многим својим блоговима да је СУМПРОДУЦТ врло свестрана функција и да се може користити у више сврха. У овом чланку ћемо видети како можемо користити ову функцију за бројање вредности са више критеријума ИЛИ.
Општа формула полупроизвода за рачунање са више или критеријумима
| = СУМПРОДУЦТ (-(((критеријуми1)+(критеријуми2)+… )>0) |
Критеријуми 1: Ово је било који критеријум који враћа низ ТРУЕ и ФАЛСЕ.
Критеријуми 2: Ово је следећи критеријум који желите да проверите. Слично, можете имати онолико критеријума колико желите.
Горе наведена генеричка формула се често модификује тако да одговара захтевима за бројање са више критеријума ИЛИ. Али основна формула је ова. Прво ћемо кроз пример видети како ово функционише, а затим ћемо разговарати о другим сценаријима у којима ћете морати мало да измените ову формулу.
Пример: Рачунајте кориснике ако шифра трговца или годинаПодударања Коришћење СУМПРОДУЦТ -а
Дакле, овде имамо скуп података продаваца. Подаци садрже много колона. Оно што треба да урадимо је да пребројимо број корисника који имају код „ИНКА“ или је година „2016“. Уверите се да ако неко има обоје (код као „инка“ и 2016. година), то треба рачунати као 1.
Дакле, овде имамо два критеријума. Користимо горенаведену формулу СУМПРОДУЦТ:
| = СУМПРОДУЦТ (-(((Код = И3)+(Година = К3))> 0)) |
Овде су код и година именовани распони.
Ово враћа 7.
У подацима имамо 5 записа ИНКА кода и 4 записа 2016. године. Али 2 записа имају "ИНКА" и 2016. као код и годину. Ови записи се рачунају као 1. И овако добијамо 7.
Како то функционише?
Па хајде да погледамо како се формула решава корак по корак, а затим ћу разговарати о томе како функционише.
| =СУМПРОДУЦТ(-(((Код = И3)+(Година = К3))> 0)) |
| 1=>СУМПРОДУЦТ(-(({ТРУЕ; ФАЛСЕ; ТРУЕ; ТРУЕ; ТРУЕ; ТРУЕ;…}+{ФАЛСЕ; ФАЛСЕ; ФАЛСЕ; ТРУЕ; ТРУЕ;…})> 0)) |
| 2=>СУМПРОДУЦТ(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>СУМПРОДУЦТ(-({ТРУЕ; ФАЛСЕ; ТРУЕ; ТРУЕ; ТРУЕ; ТРУЕ; ТРУЕ;…}) |
| 4=>СУМПРОДУЦТ({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
У првом кораку, вредност И3 ("ИНКА") се упоређује са сваком ћелијом у опсегу кодова. Ово враћа низ ТРУЕ и ФАЛСЕ. ИСТИНА за сваку утакмицу. Да бих уштедио простор, нисам приказао све ТРУЕ-ФАЛСЕ. Слично, вредност К3 (2016) је усклађена са сваком ћелијом у распону година.
У следећем кораку додајемо ова два низа који резултирају новим низом нумеричких вредности. Као што можда знате, ТРУЕ се у Екцелу третира као 1, а ФАЛСЕ као 0. Дакле, када се додају ТРУЕ и ТРУЕ, добијамо 2, а остало можете разумети.
У следећем кораку проверавамо која је вредност већа од 0 у низу. Ово поново претвара низ у прави лажни низ. За сваку вредност 0 коју добијемо, Фалсе и рест се претварају у труе. Сада је број ТРУЕ вредности у низу наш одговор. Али како их рачунамо? Ево како.
Двоструки негативни знакови (-) користе се за претварање логичких вредности у 1с и 0с. Дакле, свака ТРУЕ вредност у низу се конвертује у 1, а ФАЛСЕ у 0.
У последњем кораку СУМПРОДУЦТ сумира овај низ и добијамо наш одговор као 7.
Додавање више или критеријума за рачунање помоћу СУМПРОДУЦТ -а
Дакле, ако требате да додате још критеријума или критеријуме за бројање, можете само да додате критеријуме помоћу знака + у функцију.
На пример, ако желите да додате још један критеријум горњој формули тако да додаје број запослених који су продали више од 5 производа. Формула СУМПРОДУЦТ ће једноставно изгледати овако:
| = СУМПРОДУЦТ (-(((Код = И3)+(Година = К3)+(Продаја> 5))> 0)) |
Једноставно! зар не?
Али рецимо да желите да имате два критеријума Код домет. Рецимо да желите да бројите "ИНКБ". Па како то радите? Једна метода користи горњу технику, али то би се понављало. Рецимо да желим да додам још 10 критеријума из истог опсега. У таквим случајевима ова техника није толико паметна за бројање помоћу СУМПРОДУЦТ -а.
Рецимо да имамо податке уређене овако.
Кодови критеријума су у једном реду И2: Ј2. Распоред података је овде важан. Формула СУМПРОДУЦТ за подешавања броја 3 критеријума ИЛИ биће:
| = СУМПРОДУЦТ (-(((Код = И2: Ј2)+(Година = И3: Ј3))> 0)) |
Ово је СУМПРОДУЦТ формула за бројање са више критеријума када је више критеријума из једног опсега написано у низу.
Ово враћа тачан одговор који је 10.
Ако неку годину укуцате у Ј3, формула ће додати и тај број.
Ово се користи када су критеријуми у једном реду. Да ли ће радити ако су критеријуми у једној колони за исти опсег? Не. Неће.
У овом примеру имамо више кодова за бројање, али ти типови кодова су написани у једној колони. Када користимо горњу формулу СУМПРОДУЦТ, добијамо грешку #Н/А. Нећемо улазити у то како је дошло до ове грешке јер ће овај чланак бити предуг. Хајде да видимо како можемо да успемо.
Да би ова формула функционисала, морате да унесете критеријуме кода у функцију ТРАНСПОСЕ. Ово ће учинити да формула делује.
| = СУМПРОДУЦТ (-(((Код = ТРАНСПОЗА (Х3: Х4))+(Година = ТРАНСПОЗА (И3: И4)))> 0)) |
Ово је формула за бројање са вишеструким условима или условима у истом опсегу када су критеријуми наведени у колони.
Па да колега, надам се да сам био довољно јасан и да је имао смисла. Надам се да служи вашој сврси да будете овде. Ако ова формула није решила ваш проблем, јавите ми своје захтеве у одељку за коментаре испод. Бићу више него срећан што ћу вам помоћи на било који начин. Можете поменути све сумње везане за Екцел/ВБА. До тада наставите да учите, и даље напредујте.
Како се користи функција СУМПРОДУЦТ у програму Екцел: Враћа СУМ након множења вредности у више низа у Екцелу. Ова функција се може користити за обављање више задатака. Ово је једна од најсвестранијих функција.
ЦОУНТИФС са динамичким опсегом критеријума : За бројање са распоном динамичких критеријума једноставно користимо функцију ИНДИРЕЦТ. Ова функција може
Зброји са ИЛИ за више критеријума : Бројање ћелија које имају више критеријума који се подударају помоћу функције ОР. Да бисте ставили логику ИЛИ у функцију ЦОУНТИФС, нећете морати да користите функцију ОР.
Коришћење ИФ -а са функцијама АНД / ОР у програму Мицрософт Екцел : Ове логичке функције се користе за израчунавање више критеријума. Са ИФ функције ОР и АНД се користе за укључивање или искључивање подударања.
Како се користи функција ОР у програму Мицрософт Екцел : Функција се користи за укључивање свих ТРУЕ вредности у више критеријума.
Како пребројати ћелије које садрже ово или оно у Екцелу у Екцелу : За ћелије које садрже ово или оно, можемо користити функцију СУМПРОДУЦТ. Ево како радите те прорачуне.
Популарни чланци:
50 Екцел пречица за повећање продуктивности | Убрзајте свој задатак. Ових 50 пречица ће учинити да радите још брже на Екцелу.
Како се користи Екцел ВЛООКУП функција| Ово је једна од најчешће кориштених и популарних функција програма Екцел која се користи за тражење вриједности из различитих распона и листова.
Како се користи Екцел ЦОУНТИФ функција| Бројте вредности са условима користећи ову невероватну функцију. Не морате да филтрирате податке да бисте рачунали одређене вредности. Цоунтиф функција је неопходна за припрему ваше контролне табле.
Како се користи функција СУМИФ у програму Екцел | Ово је још једна битна функција контролне табле. Ово вам помаже да сумирате вредности под одређеним условима.