Öelge, et soovite loendist unikaalseid üksusi loendada, kuid keerutades seda. Ja öelge, et töötate selle töölehega:
Veerg D loendab ridade arvu veerus B igas jaotises ja veerus C loendatakse selle jaotise veeru A esimese viie märgi põhjal ainulaadsete sektsioonide arv. Lahtrid B2: B11 sisaldavad ARG-d ja A2: A11 viie esimese märgi sisse võite lugeda kaheksa unikaalset üksust, kuna A7: A9 sisaldavad kumbki 11158, seega kahte duplikaati ei arvestata. Samamoodi ütleb 5 D12-s teile, et BRD-l on viis rida, kuid ridades 12:16 on kolm esimest viiest märgist ainulaadset üksust, kuna 11145 kordub ja 11173 kordub.
Kuidas aga Exceli käskida seda teha? Ja millist valemit saaksite C2-s kasutada, mida saaks kopeerida C12-sse ja C17-sse?
D2-s olev lihtne loendusvalem =COUNTIF(B:B,B2)loendab veerus B B2 (ARG) esinemiskordade arvu.
Veerg A A viie esimese märgi eraldamiseks kasutate abistaja veergu, nagu sellel joonisel:
Järgmisena peate kuidagi märkima, et ARG-i jaoks on unikaalsete üksuste arvu leidmiseks huvitatud ainult lahtritest F2: F11. Üldiselt leiate selle väärtuse, kasutades sellel joonisel näidatud massiivi valemit:
Lahtrit C3 kasutate ajutiselt ainult valemi kuvamiseks; näete, et seda ei ole eelmistel joonistel C3-s. (Saate varsti teada, kuidas see valem töötab.)
Mis on valem C2, C12 ja C17? Üllatav (ja lahe) vastus on näidatud sellel joonisel:
Ohoo! Kuidas see töötab?
Heitke pilk vastusele selles joonisel määratletud nimedes:
See on sama valem varasemast joonisest, kuid selle asemel, et kasutada vahemikku F2: F11, kasutab see vahemikku nimega Rg. Samuti oli valem massiivivalem, kuid nimetatud valemeid käsitletakse nii, nagu oleksid need massiivivalemid! See tähendab, et =Answerseda ei sisestata klahvikombinatsiooniga Ctrl + Tõst + Enter, vaid sisestatakse lihtsalt nagu tavaliselt.
Kuidas siis Rg defineeritakse? Kui valitud on lahter C1 (mis on selle triki mõistmiseks oluline samm), siis on see määratletud järgmiselt:
See on =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).
Laenu_andmed on lehe nimi, kuid seda valemit saate vaadata ilma pika lehe nimeta. Lihtne viis seda teha on nimetada lehele ajutiselt midagi lihtsat, näiteks x, ja seejärel vaadata uuesti määratletud nime:
Seda valemit on lihtsam lugeda!
Näete, et see valem sobib dollariga B1 (pange tähele praeguse rea suhteline viide) kogu veeru B ja lahutamiste arvuga 1. Lahutate 1, kuna kasutate F1-st OFFSET. Nüüd, kui teate C valemist, vaadake C2 valemit:
MATCH($B2,$B:$B,0)Osa valemist on 2, seega valemiga (ilma viide lehenimi) on:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
või:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
või:
=OFFSET($F$1,1,0,10,1)
Kuna COUNTIF($B:$B,$B2)on 10, on 10 ARG-d. See on vahemik F2: F11. Tegelikult, kui valitud on lahter C2 ja vajutate Rg-le liikumiseks klahvi F5, näete seda:
Kui algrakk oli C12, siis Rg-i minemiseks vajutage F5:
Nii et nüüd, kui vastus on määratletud järgmiselt =SUM(1/COUNTIF(rg,rg)), olete kõik valmis!
Vaatame lähemalt, kuidas see valem töötab, kasutades palju lihtsamat näidet. Tavaliselt on COUNTIF-i süntaks =COUNTIF(range,criteria), nagu =COUNTIF(C1:C10, "b")sellel joonisel:
See annaks vahemikus b olevate arvude arvuks 2. Kuid kriteeriumina vahemiku enda läbimine kasutab kriteeriumina vahemiku iga üksust. Kui tõstate selle valemi osa esile:
ja vajutage F9, näete:
Iga vahemiku üksust hinnatakse ja see arvude rida tähendab, et seal on üks a ja kaks b, kolm c ja neli d. Need arvud on jagatud 1-ks, andes 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, nagu näete siin:
Nii et teil on 2 poolikut, 3 kolmandikku, 4 neljandikku ja 1 tervik ning nende liitmisel saadakse 4. Kui eset korratakse 7 korda, siis on teil seitse seitsmendat ja nii edasi. Päris lahe! (Müts maha David Hageri ees selle valemi avastamise / leiutamise eest.)
Kuid hoidke minut. Praegusel kujul peate selle valemi sisestama ainult C2, C12 ja C17. Kas poleks parem, kui saaksite selle sisestada C2-sse ja täita ning näidata ainult õigetes lahtrites? Tegelikult saate seda teha. C2-s olevat valemit saab muuta =IF(B1B2,Answer,"")ja selle täitmisel täidab see tööd:
Aga miks siin peatuda? Miks mitte teha valemist nimeline valem, nagu siin näidatud:
Selle toimimiseks peab lahter C2 olema aktiivne lahter (või valem peaks olema erinev). Nüüd saate veeru C valemid asendada järgmisega =Answer2:
Näete, et C3-l on =Answer2nagu kõigil veerus C olevatel lahtritel. Miks mitte jätkata seda veerus D? Pärast võrdluse rakendamist B1 ja B2-le kuvatakse D2 valem siin:
Nii et kui hoiate lahtrit D2 valitud ja määrate mõne muu valemi, öelge vastus3:
siis saate =Answer3lahtrisse D2 sisestada ja täita:
Siin on töölehe ülemine osa koos näidatud valemitega, millele järgneb sama ekraanipilt väärtustega:
Kui teised inimesed üritavad seda aru saada, võivad nad esialgu oma pead kratsida!
See külalisartikkel pärineb Exceli MVP Bob Umlaselt. See pärineb raamatust More Exceli väljaspool kasti. Teiste raamatu teemade nägemiseks klõpsake siin.
