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