Povedzte, že chcete počítať jedinečné položky zo zoznamu, ale s obratom. A povedzme, že pracujete s týmto pracovným listom:
Stĺpec D počíta počet riadkov v každej z častí zo stĺpca B a stĺpec C počíta počet jedinečných častí na základe prvých piatich znakov stĺpca A pre túto časť. Bunky B2: B11 obsahujú ARG a v prvých piatich znakoch A2: A11 môžete počítať osem jedinečných položiek, pretože všetky A7: A9 obsahujú 11158, takže dva duplikáty sa nepočítajú. Podobne 5 v D12 vám hovorí, že pre BRD existuje päť riadkov, ale v riadkoch 12:16 sú tri jedinečné položky prvých piatich znakov, pretože 11145 sa opakuje a 11173 sa opakuje.
Ako však odkážete programu Excel, aby to urobil? A aký vzorec by ste mohli použiť v C2, ktorý by sa dal kopírovať do C12 a C17?
Jednoduchý vzorec na počítanie v D2, =COUNTIF(B:B,B2)počíta, koľkokrát B2 (ARG) existuje v stĺpci B.
Pomocou pomocného stĺpca izolujete prvých päť znakov stĺpca A, ako je to na tomto obrázku:
Ďalej musíte nejako naznačiť, že pre ARG vás zaujímajú iba bunky F2: F11, aby ste našli počet jedinečných položiek. Všeobecne by ste túto hodnotu našli pomocou vzorca poľa uvedeného na tomto obrázku:
Bunku C3 dočasne používate iba na zobrazenie vzorca; na predchádzajúcich obrázkoch môžete vidieť, že v C3 nie je. (Krátko sa dozviete, ako tento vzorec funguje.)
Aký je vzorec pre C2, C12 a C17? Prekvapujúca (a skvelá) odpoveď je uvedená na tomto obrázku:
Fúha! Ako to funguje?
Prezrite si odpoveď v definovaných menách na tomto obrázku:
Je to rovnaký vzorec z predchádzajúceho obrázku, ale namiesto rozsahu F2: F11 používa rozsah s názvom Rg. Vzorec bol tiež maticový vzorec, ale s pomenovanými vzorcami sa zaobchádza, akoby išlo o maticové vzorce! To znamená, že =Answersa nezadáva pomocou Ctrl + Shift + Enter, ale zadáva sa jednoducho ako obvykle.
Ako je teda definované Rg? Ak je vybratá bunka C1 (čo je dôležitý krok k pochopeniu tohto triku), potom je definovaná ako na tomto obrázku:
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 názov hárku, ale na tento vzorec sa môžete pozrieť bez dlhého názvu hárka. Jednoduchý spôsob, ako to urobiť, je dočasne pomenovať hárok niečím jednoduchým, napríklad x, a potom sa znova pozrieť na definovaný názov:
Tento vzorec je ľahšie čitateľný!
Vidíte, že tento vzorec zodpovedá $ B1 (všimnite si relatívny odkaz na aktuálny riadok) proti všetkým stĺpcom B a odčíta 1. Odčítate 1, pretože používate OFSET z F1. Teraz, keď viete o vzorci pre C, pozrite sa na vzorec pre C2:
MATCH($B2,$B:$B,0)Časť vzorca je 2, takže vzorec (bez odkazu na názov listu) je:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
alebo:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
alebo:
=OFFSET($F$1,1,0,10,1)
Pretože COUNTIF($B:$B,$B2)je 10, existuje 10 ARG. Toto je rozsah F2: F11. V skutočnosti, ak je vybratá bunka C2 a stlačením F5 prejdete na Rg, uvidíte toto:
Ak bola počiatočná bunka C12, stlačením F5 na prechod na Rg sa vytvorí toto:
Takže teraz, s odpoveďou definovanou ako =SUM(1/COUNTIF(rg,rg)), ste hotoví!
Pozrime sa podrobnejšie na to, ako tento vzorec funguje, na oveľa jednoduchšom príklade. Syntax pre COUNTIF je zvyčajne, =COUNTIF(range,criteria)napríklad =COUNTIF(C1:C10, "b")na tomto obrázku:
Takto by sme dostali 2 ako počet b v rozsahu. Samotné odovzdanie rozsahu ako kritérií však použije ako kritérium každú položku v rozsahu. Ak zvýrazníte túto časť vzorca:
a stlačte kláves F9, uvidíte:
Každá položka v rozsahu sa vyhodnotí a táto séria čísel znamená, že je tu jedna a sú dve b, tri c a štyri d. Tieto čísla sú rozdelené na 1 a dajú 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, ako vidíte tu:
Takže máte 2 polovice, 3 tretiny, 4 štvrtiny a 1 celú, a ich spočítaním získate výnosy 4. Ak by sa položka opakovala 7-krát, mali by ste 7 sedmín a tak ďalej. Celkom v pohode! (Klobúk dole pred Davidom Hagerom za objavenie / vymýšľanie tohto vzorca.)
Ale vydržte minútu. Súčasný vzorec musíte zadať iba v C2, C12 a C17. Nebolo by lepšie, keby ste to mohli zadať do C2 a vyplniť a zobraziť iba v správnych bunkách? V skutočnosti to môžete urobiť. Vzorec v C2 môžete upraviť tak, aby bol =IF(B1B2,Answer,""), a keď ho vyplníte, urobí úlohu:
Ale prečo sa tu zastaviť? Prečo neurobiť vzorec do pomenovaného vzorca, ako je to znázornené tu:
Aby to fungovalo, musí byť bunka C2 aktívnou bunkou (alebo by sa vzorec musel líšiť). Teraz môžete nahradiť vzorce stĺpca C nasledujúcimi =Answer2:
Vidíte, že C3 má =Answer2, rovnako ako všetky bunky v stĺpci C. Prečo to nepokračovať v stĺpci D? Vzorec v D2 sa po uplatnení porovnania aj na B1 a B2 zobrazuje tu:
Ak teda ponecháte bunku D2 vybratú a definujete iný vzorec, povedzte odpoveď 3:
potom môžete vstúpiť =Answer3do bunky D2 a vyplniť:
Tu je horná časť tabuľky so zobrazenými vzorcami, za ktorou nasleduje rovnaká snímka obrazovky s zobrazenými hodnotami:
Keď sa to pokúsia zistiť iní ľudia, mohli by si najskôr poškriabať hlavu!
Tento článok je hosťom programu Excel MVP Boba Umlasa. Je to z knihy More Excel Outside the Box. Ak si chcete pozrieť ďalšie témy v knihe, kliknite sem.
