Един проблем, който се появява много в Excel, е броенето или сумирането въз основа на множество условия ИЛИ. Например, може би трябва да анализирате данни и да преброите поръчки в Сиатъл или Денвър за елементи, които са червени, сини или зелени? Това може да бъде изненадващо сложно, така че естествено прави добро предизвикателство!
Предизвикателството
Данните по-долу представляват поръчки, по една поръчка на ред. Има три отделни предизвикателства.
Какви формули във F9, G9 и H9 ще отчитат правилно поръчките при следните условия:
- F9 - Тениска или качулка
- G9 - (тениска или качулка) и (червено, синьо или зелено)
- H9 - (тениска или качулка) и (червено, синьо или зелено) и (Денвър или Сиатъл)
Зеленото засенчване се прилага с условно форматиране и показва съответстващи стойности за всеки набор от критерии ИЛИ във всяка колона.
За ваше улеснение са налични следните именувани диапазони:
артикул = B3: B16
цвят = C3: C16
град = D3: D16
Работният лист е приложен. Оставете отговорите си по-долу като коментари!
Отговор (щракнете за разгъване)Моето решение използва SUMPRODUCT с ISNUMBER и MATCH по следния начин:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Което ще брои поръчки, където …
- Артикулът е (тениска или качулка) и
- Цветът е (червен, син или зелен) и
- Градът е (Денвър или Сиатъл)
Няколко души също предложиха същия подход. Харесва ми тази структура, защото тя се мащабира лесно, за да се справя с повече критерии, а също така работи и с препратки към клетки (вместо твърдо кодирани стойности). С препратки към клетки формулата в H9 е:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Ключът към тази формула е конструкцията ISNUMBER + MATCH. MATCH се настройва „назад“ - справочните стойности идват от данните и за масива се използват критерии. Резултатът е масив от една колона всеки път, когато се използва MATCH. Този масив съдържа или # N / A грешки (без съвпадение), или числа (съвпадение), така че ISNUMBER се използва за преобразуване в булевите стойности TRUE и FALSE. Операцията за умножаване на масивите заедно принуждава стойностите TRUE FALSE до 1s и 0s, а крайният масив в SUMPRODUCT съдържа 1s, където редовете отговарят на критериите. След това SUMPRODUCT сумира масива и връща резултата.
