Recite da želite brojati jedinstvene stavke s popisa, ali s pomakom. I recimo da radite s ovim radnim listom:
Stupac D broji broj redaka u svakom odjeljku iz stupca B, a stupac C broj jedinstvenih odjeljaka na temelju prvih pet znakova stupca A za taj odjeljak. Stanice B2: B11 sadrže ARG, a u prvih pet znakova A2: A11 možete izbrojati osam jedinstvenih predmeta jer svaka A7: A9 sadrži 11158, tako da se dva duplikata ne broje. Slično tome, 5 u D12 govori vam da postoji pet redaka za BRD, ali unutar redaka 12:16 nalaze se tri jedinstvene stavke od prvih pet znakova, jer se ponavlja 11145 i ponavlja 11173.
Ali kako reći Excel-u da to učini? A koju biste formulu mogli koristiti u C2 koja bi se mogla kopirati na C12 i C17?
Jednostavna formula za brojanje u D2, =COUNTIF(B:B,B2)broji koliko puta B2 (ARG) postoji u stupcu B.
Pomoću pomoćnog stupca izolirate prvih pet znakova stupca A, kao na ovoj slici:
Dalje, trebate nekako naznačiti da vas za ARG zanimaju samo ćelije F2: F11 da biste pronašli broj jedinstvenih predmeta. Općenito, ovu biste vrijednost pronašli pomoću formule niza prikazane na ovoj slici:
Stanicu C3 koristite privremeno samo da biste prikazali formulu; možete vidjeti da nije prisutan u C3 na prethodnim slikama. (Uskoro ćete naučiti kako ova formula djeluje.)
Dakle, koja je formula u C2, C12 i C17? Iznenađujući (i cool) odgovor prikazan je na ovoj slici:
Joj! Kako ovo radi?
Pogledajte Answer u definiranim imenima na ovoj slici:
Ista je formula s ranije slike, ali umjesto da koristi raspon F2: F11, koristi raspon pod nazivom Rg. Također, formula je bila formula niza, ali imenovane formule tretiraju se kao da su formule niza! Odnosno, =Answerne unosi se Ctrl + Shift + Enter, već se jednostavno unosi kao i obično.
Pa, kako se definira Rg? Ako je odabrana ćelija C1 (što je važan korak za razumijevanje ovog trika), tada je definirana kao na ovoj slici:
To je to =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 je ime lista, ali ovu formulu možete pogledati bez dugog naziva lista. Jednostavan način da to učinite je da privremeno imenujete list nečim jednostavnim, poput x, a zatim ponovno pogledate definirano ime:
Ovu je formulu lakše čitati!
Možete vidjeti da se ova formula podudara s $ B1 (imajte na umu relativnu referencu na trenutni redak) u odnosu na sve stupce B i oduzima 1. Oduzimate 1 jer koristite OFFSET od F1. Sad kad znate za formulu za C, pogledajte onu za C2:
MATCH($B2,$B:$B,0)Dio formule 2, tako da je spoj formule (bez reference na ime lima) je:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
ili:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
ili:
=OFFSET($F$1,1,0,10,1)
Budući da COUNTIF($B:$B,$B2)je 10, postoji 10 ARG-ova. To je raspon F2: F11. Zapravo, ako je odabrana ćelija C2 i pritisnete F5 da biste prešli na Rg, vidjet ćete ovo:
Ako je početna ćelija bila C12, pritiskom na F5 za prelazak na Rg nastaje ovo:
Dakle, sada, kad je odgovor definiran kao =SUM(1/COUNTIF(rg,rg)), gotovi ste!
Pogledajmo pobliže kako funkcionira ova formula, koristeći puno jednostavniji primjer. Sintaksa za COUNTIF obično je =COUNTIF(range,criteria), kao =COUNTIF(C1:C10, "b")na ovoj slici:
To bi dalo 2 kao broj b u rasponu. Ali prosljeđivanje samog raspona kao kriterija koristi svaku stavku u rasponu kao kriterije. Ako istaknete ovaj dio formule:
i pritisnite F9, vidite:
Procjenjuje se svaka stavka u rasponu, a ovaj niz brojeva znači da postoje jedan a i dva b, tri c i četiri d. Ovi brojevi su podijeljeni u 1, dajući 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, kao što ovdje možete vidjeti:
Dakle, imate 2 polovice, 3 trećine, 4 četvrtine i 1 cijelu, a zbrajanje daje 4. Ako bi se stavka ponovila 7 puta, imali biste 7 sedmica i tako dalje. Baš super! (Kapa dolje Davidu Hageru zbog otkrića / izuma ove formule.)
Ali pričekaj minutu. Kako stoji, ovu formulu morate unijeti samo u C2, C12 i C17. Ne bi li bilo bolje da ga možete unijeti u C2 i popuniti i prikazati samo u ispravnim ćelijama? Zapravo to možete učiniti. Možete izmijeniti formulu u C2 da bude =IF(B1B2,Answer,""), a kad je popunite, to obavlja posao:
Ali zašto se ovdje zaustaviti? Zašto ne napraviti formulu u imenovanu formulu, kao što je prikazano ovdje:
Da bi ovo uspjelo, ćelija C2 mora biti aktivna ćelija (ili bi formula trebala biti drugačija). Sada formule stupca C možete zamijeniti sa =Answer2:
Možete vidjeti da C3 ima =Answer2, kao i sve stanice u stupcu C. Zašto to ne biste nastavili u stupcu D? Formula u D2, nakon što je također primijenjena usporedba na B1 i B2, prikazana je ovdje:
Dakle, ako zadržite odabranu ćeliju D2 i definirate drugu formulu, recite Answer3:
tada možete unijeti =Answer3u ćeliju D2 i popuniti:
Evo gornjeg dijela radnog lista s prikazanim formulama, nakon čega slijedi ista snimka zaslona s prikazanim vrijednostima:
Kad drugi ljudi pokušaju to shvatiti, isprva bi se mogli počešati po glavi!
Ovaj gost članak je iz Excela MVP-a Bob Umlas. Iz knjige je, Još Excel izvan okvira. Da biste pogledali ostale teme u knjizi, kliknite ovdje.
