Recimo, da želite šteti unikatne predmete s seznama, vendar z zvihom. Recimo, da delate s tem delovnim listom:
Stolpec D šteje število vrstic v vsakem odsekih iz stolpca B, stolpec C pa število enoličnih odsekov, ki temelji na prvih petih znakih stolpca A za ta odsek. Celice B2: B11 vsebujejo ARG, v prvih petih znakih A2: A11 pa lahko štejete osem unikatnih elementov, ker A7: A9 vsebuje po 11158, zato se dva dvojnika ne štejeta. Podobno petica v D12 pove, da je za BRD pet vrstic, v vrsticah 12:16 pa so trije edinstveni elementi iz prvih petih znakov, saj se ponovi 11145 in ponovi 11173.
Kako pa naročite Excelu, naj to stori? In katero formulo bi lahko uporabili v C2, ki bi jo lahko kopirali v C12 in C17?
Preprosta formula štetja v D2, =COUNTIF(B:B,B2)šteje, kolikokrat B2 (ARG) obstaja v stolpcu B.
S pomočjo pomožnega stolpca izolirate prvih pet znakov stolpca A, kot je na tej sliki:
Nato morate nekako navesti, da vas za ARG zanimajo samo celice F2: F11, da poiščete število unikatnih elementov. Na splošno bi to vrednost našli z uporabo matrične formule, prikazane na tej sliki:
Celico C3 začasno uporabljate samo za prikaz formule; lahko vidite, da v prejšnjih slikah ni v C3. (Kmalu boste izvedeli, kako deluje ta formula.)
Kakšna je torej formula v C2, C12 in C17? Presenetljiv (in kul) odgovor je prikazan na tej sliki:
Joj! Kako to deluje?
Oglejte si odgovor v definiranih imenih na tej sliki:
To je enaka formula s prejšnje slike, vendar namesto da uporablja obseg F2: F11, uporablja obseg z imenom Rg. Formula je bila tudi matrična formula, vendar so imenovane formule obravnavane, kot da so matrične formule! To pomeni, da =Answerse ne vnese s Ctrl + Shift + Enter, ampak se preprosto vnese kot običajno.
Kako je torej definiran Rg? Če je izbrana celica C1 (kar je pomemben korak za razumevanje tega trika), je definirana kot na tej sliki:
To je =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, vendar lahko to formulo pogledate brez dolgega imena lista. Enostaven način za to je, da listu začasno poimenujete nekaj preprostega, na primer x, in nato znova pogledate določeno ime:
To formulo je lažje prebrati!
Vidite lahko, da se ta formula ujema z $ B1 (upoštevajte relativno sklicevanje na trenutno vrstico) v primerjavi z vsem stolpcem B in odšteje 1. Odštejete 1, ker uporabljate OFFSET od F1. Zdaj, ko poznate formulo za C, si oglejte še formulo za C2:
MATCH($B2,$B:$B,0)Del s formulo 2, da je formula (brez sklicevanja na ime stanja):
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
ali:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
ali:
=OFFSET($F$1,1,0,10,1)
Ker COUNTIF($B:$B,$B2)je 10, obstaja 10 ARG-jev. To je območje F2: F11. Pravzaprav, če je izbrana celica C2 in pritisnete F5 za prehod na Rg, vidite to:
Če bi bila začetna celica C12, pritisk F5 za prehod na Rg povzroči to:
Zdaj, ko je odgovor definiran kot =SUM(1/COUNTIF(rg,rg)), ste končali!
Poglejmo natančneje, kako deluje ta formula, na veliko enostavnejšem primeru. Sintaksa za COUNTIF je običajno =COUNTIF(range,criteria)takšna, kot je =COUNTIF(C1:C10, "b")na tej sliki:
To bi dalo 2 kot število b-jev v območju. Toda posredovanje samega obsega kot merila uporablja vsak element v obsegu kot merila. Če označite ta del formule:
in pritisnite F9, vidite:
Vsak element v obsegu se oceni in ta niz številk pomeni, da je en a, dva b, tri c in štirje d. Te številke so razdeljene na 1, kar daje 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, kot lahko vidite tukaj:
Torej imate 2 polovici, 3 tretjine, 4 četrtine in 1 celoto, njihovo seštevanje pa daje 4. Če bi element ponovili 7-krat, bi imeli 7 sedmin in tako naprej. Precej kul! (Kapo dol Davidu Hagerju, ker je odkril / izumil to formulo.)
A počakajte minuto. V sedanji obliki morate to formulo vnesti samo v C2, C12 in C17. Ali ne bi bilo bolje, če bi ga lahko vnesli v C2 in izpolnili ter prikazali samo v pravilnih celicah? Pravzaprav lahko to storite. Formulo v C2 lahko spremenite v =IF(B1B2,Answer,""), in ko jo izpolnite, opravi svoje delo:
Zakaj pa se ustaviti tukaj? Zakaj ne bi formule naredili v imenovano formulo, kot je prikazano tukaj:
Da bi to delovalo, mora biti celica C2 aktivna celica (ali formula bi morala biti drugačna). Zdaj lahko formule stolpca C zamenjate z =Answer2:
Vidite lahko, da ima C3 =Answer2, tako kot vse celice v stolpcu C. Zakaj tega ne bi nadaljevali v stolpcu D? Formula v D2 je po uporabi primerjave z B1 in B2 prikazana tukaj:
Torej, če obdržite celico D2 izbrano in določite drugo formulo, recite Answer3:
potem lahko vnesete =Answer3v celico D2 in izpolnite:
Tu je zgornji del delovnega lista s prikazanimi formulami, čemur sledi isti posnetek zaslona z prikazanimi vrednostmi:
Ko drugi ljudje poskušajo to ugotoviti, se bodo morda sprva opraskali po glavi!
Ta gostujoči članek je iz Excelovega MVP-ja Bob Umlas. Nahaja se v knjigi Več Excel zunaj škatle. Če si želite ogledati druge teme v knjigi, kliknite tukaj.
