Кажете, че искате да можете да броите уникални елементи от списък, но с обрат. И да кажем, че работите с този работен лист:
Колона D отчита броя на редовете във всяка от секциите от колона B, а колона C отчита броя на уникалните секции въз основа на първите пет знака от колона A за тази секция. Клетки B2: B11 съдържат ARG и можете да преброите осем уникални елемента в първите пет знака на A2: A11, защото A7: A9 всяка съдържа 11158, така че двата дубликата не се броят. По същия начин 5 в D12 ви казва, че има пет реда за BRD, но в редове 12:16 има три уникални елемента от първите пет знака, тъй като 11145 се повтаря и 11173 се повтаря.
Но как да кажете на Excel да направи това? И каква формула бихте могли да използвате в C2, която може да бъде копирана в C12 и C17?
Простата формула за броене в D2, =COUNTIF(B:B,B2)отчита броя на случаите, когато B2 (ARG) съществува в колона B.
Използвате помощна колона, за да изолирате първите пет знака от колона А, както е на тази фигура:
След това трябва по някакъв начин да посочите, че за ARG се интересувате само от клетки F2: F11, за да намерите броя на уникалните елементи. По принцип бихте намерили тази стойност, като използвате формулата на масива, показана на тази фигура:
Временно използвате клетка C3, само за да покажете формулата; можете да видите, че не присъства в C3 в предишните фигури. (Ще научите скоро как работи тази формула.)
И така, каква е формулата в C2, C12 и C17? Изненадващият (и страхотен) отговор е показан на тази фигура:
Уау! Как работи това?
Погледнете Answer в дефинираните имена на тази фигура:
Това е същата формула от по-ранна фигура, но вместо да използва диапазона F2: F11, той използва диапазон, наречен Rg. Също така, формулата е формула на масив, но именуваните формули се третират така, сякаш са формули на масив! Тоест =Answerне се въвежда с Ctrl + Shift + Enter, а просто се въвежда както обикновено.
И така, как се определя Rg? Ако е избрана клетка C1 (което е важна стъпка за разбиране на този трик), тогава тя е определена както на тази фигура:
Това е =OFFSET(Loan_Details!$F$1,MATCH(Loan_Details!$B1,Loan_Details!$B:$B,0)-1,0,COUNTIF(Loan_Details!$B:$B,Loan_Details!$B1),1).
Loan_Details е името на листа, но можете да разгледате тази формула без дългото име на листа. Лесен начин да направите това е временно да наименувате листа на нещо просто, като x и след това да погледнете отново дефинираното име:
Тази формула е по-лесна за четене!
Можете да видите, че тази формула съвпада с $ B1 (обърнете внимание на относителната препратка към текущия ред) спрямо цялата колона B и изважда 1. Изваждате 1, защото използвате OFFSET от F1. След като вече знаете за формулата за C, разгледайте тази за C2:
В MATCH($B2,$B:$B,0)част от формулата е 2, така формула (без позоваване на името на лист) е:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
или:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
или:
=OFFSET($F$1,1,0,10,1)
Тъй като COUNTIF($B:$B,$B2)е 10, има 10 ARG. Това е обхват F2: F11. Всъщност, ако е избрана клетка C2 и натиснете F5, за да преминете към Rg, ще видите това:
Ако началната клетка е C12, натискането на F5 за преминаване към Rg води до това:
Така че сега, с Отговор, дефиниран като =SUM(1/COUNTIF(rg,rg)), всичко е готово!
Нека разгледаме по-отблизо как работи тази формула, като използваме много по-опростен пример. Обикновено синтаксисът за COUNTIF е =COUNTIF(range,criteria), както е =COUNTIF(C1:C10, "b")на тази фигура:
Това ще даде 2 като брой b в диапазона. Но предаването на самия диапазон като критерии използва всеки елемент в диапазона като критерии. Ако маркирате тази част от формулата:
и натиснете F9, виждате:
Всеки елемент в диапазона се оценява и тази поредица от числа означава, че има едно a и има две b, три c и четири d. Тези числа са разделени на 1, давайки 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, както можете да видите тук:
Така че имате 2 половинки, 3 трети, 4 четвърти и 1 цяло, а събирането им дава 4. Ако даден елемент се повтори 7 пъти, тогава ще имате 7 седми и така нататък. Много готино! (Шапка на Дейвид Хейгър за откриването / изобретяването на тази формула.)
Но изчакайте минута. В настоящия си вид трябва да въведете тази формула само в C2, C12 и C17. Не би ли било по-добре, ако можете да го въведете в C2 и да попълните и да го покажете само в правилните клетки? Всъщност можете да направите това. Можете да модифицирате формулата в C2 да бъде =IF(B1B2,Answer,"")и когато попълните това, тя свършва работата:
Но защо да спрем тук? Защо не превърнете формулата в именувана формула, както е показано тук:
За да работи това, клетка C2 трябва да е активната клетка (или формулата трябва да е различна). Сега можете да замените формулите на колона C със =Answer2:
Можете да видите, че C3 има =Answer2, както и всички клетки в колона C. Защо не продължите това в колона D? Формулата в D2, след прилагане на сравнението и към B1 и B2, е показана тук:
Така че, ако държите клетката D2 избрана и дефинирате друга формула, кажете Answer3:
след това можете да въведете =Answer3в клетка D2 и да попълните:
Ето горната част на работния лист с показани формули, последвани от същата екранна снимка със стойности, показващи:
Когато други хора се опитат да разберат това, в началото може да си почешат главата!
Тази статия за гости е от Excel MVP Боб Умлас. Това е от книгата „Още Excel извън кутията“. За да видите останалите теми в книгата, щракнете тук.
