Један проблем који се у Екцел-у често појављује је бројање или сумирање на основу више услова ИЛИ. На пример, можда треба да анализирате податке и пребројите поруџбине у Сијетлу или Денверу, за ставке које су црвене, плаве или зелене? Ово може бити изненађујуће запетљано, па природно представља добар изазов!
Изазов
Подаци у наставку представљају поруџбине, по једну у реду. Три су одвојена изазова.
Које формуле у Ф9, Г9 и Х9 ће тачно бројати поруџбине под следећим условима:
- Ф9 - Мајица или капуљача
- Г9 - (мајица са капуљачом) и (црвена, плава или зелена)
- Х9 - (мајица са капуљачом) и (црвена, плава или зелена) и (Денвер или Сијетл)
Зелено осенчење примењује се условним форматирањем и указује на одговарајуће вредности за сваки скуп ОР критеријума у свакој колони.
За вашу удобност доступни су следећи именовани опсези:
ставка = Б3: Б16
боја = Ц3: Ц16
град = Д3: Д16
Радни лист је у прилогу. Оставите своје одговоре испод као коментаре!
Одговор (кликните за проширење)Моје решење користи СУМПРОДУЦТ са ИСНУМБЕР и МАТЦХ овако:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Који ће бројати поруџбине где …
- Предмет је (мајица или капуљача) и
- Боја је (црвена, плава или зелена) и
- Град је (Денвер или Сијетл)
Неколико људи је такође предложило исти приступ. Свиђа ми се ова структура јер се лако скалира како би се могло носити са више критеријума, а такође ради и са референцама на ћелије (уместо са кодираним вредностима). Са референцама на ћелије, формула у Х9 је:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Кључ ове формуле је конструкција ИСУМБЕР + МАТЦХ. МАТЦХ се подешава „уназад“ - вредности претраживања долазе из података, а критеријуми се користе за низ. Резултат је низ појединачних ступаца сваки пут када се користи МАТЦХ. Овај низ садржи или # Н / А грешака (нема подударања) или бројева (подударање), па се ИСНУМБЕР користи за претварање у логичке вредности ТРУЕ и ФАЛСЕ. Операција множења низова присиљава вредности ТРУЕ ФАЛСЕ на 1с и 0с, а коначни низ унутар СУМПРОДУЦТ садржи 1с где редови испуњавају критеријуме. СУМПРОДУЦТ затим сумира низ и враћа резултат.
