Řekněme, že chcete počítat jedinečné položky ze seznamu, ale s obratem. A řekněme, že pracujete s tímto listem:
Sloupec D spočítá počet řádků v každé sekci ze sloupce B a sloupec C spočítá počet jedinečných sekcí na základě prvních pěti znaků ve sloupci A pro danou sekci. Buňky B2: B11 obsahují ARG a v prvních pěti znacích A2: A11 můžete počítat osm jedinečných položek, protože A7: A9 obsahuje 11158, takže dva duplikáty se nepočítají. Podobně vám 5 v D12 říká, že pro BRD existuje pět řádků, ale v řádcích 12:16 existují tři jedinečné položky prvních pěti znaků, protože 11145 se opakuje a 11173 se opakuje.
Jak ale řeknete Excelu, aby to udělal? A jaký vzorec můžete použít v C2, který lze zkopírovat do C12 a C17?
Jednoduchý vzorec počítání v D2, =COUNTIF(B:B,B2)spočítá, kolikrát existuje B2 (ARG) ve sloupci B.
Pomocí pomocného sloupce izolujete prvních pět znaků ve sloupci A, jako na tomto obrázku:
Dále musíte nějak naznačit, že pro ARG vás zajímají pouze buňky F2: F11, abyste našli počet jedinečných položek. Obecně byste tuto hodnotu našli pomocí vzorce pole zobrazeného na tomto obrázku:
Buňku C3 dočasně použijete jen pro zobrazení vzorce; na předchozích obrázcích vidíte, že v C3 není. (Krátce se dozvíte, jak tento vzorec funguje.)
Jaký je vzorec v C2, C12 a C17? Překvapivá (a skvělá) odpověď je znázorněna na tomto obrázku:
Whoa! Jak tohle funguje?
Podívejte se na odpověď v definovaných jménech na tomto obrázku:
Je to stejný vzorec z dřívějšího obrázku, ale místo použití rozsahu F2: F11 používá rozsah s názvem Rg. Vzorec byl také maticový vzorec, ale s pojmenovanými vzorci se zachází, jako by šlo o maticové vzorce! To znamená, že =Answerse nezadává pomocí Ctrl + Shift + Enter, ale jednoduše se zadává jako obvykle.
Jak je tedy definováno Rg? Pokud je vybrána buňka C1 (což je důležitý krok k pochopení tohoto triku), je definována jako 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ázev listu, ale můžete se podívat na tento vzorec bez dlouhého názvu listu. Snadný způsob, jak to udělat, je dočasně pojmenovat list něčím jednoduchým, například x, a poté se znovu podívat na definovaný název:
Tento vzorec je čitelnější!
Vidíte, že tento vzorec odpovídá $ B1 (všimněte si relativního odkazu na aktuální řádek) proti všem sloupcům B a odečte 1. Odečtete 1, protože používáte OFSET z F1. Nyní, když víte o vzorci pro C, podívejte se na vzorec pro C2:
MATCH($B2,$B:$B,0)Část vzorce je 2, takže vzorec (bez odkazu na název listu) je:
=OFFSET($F$1,2-1,0,COUNTIF($B:$B,$B2),1)
nebo:
=OFFSET($F$1,1,0,COUNTIF($B:$B,$B2),1)
nebo:
=OFFSET($F$1,1,0,10,1)
Protože COUNTIF($B:$B,$B2)je 10, existuje 10 ARG. Toto je rozsah F2: F11. Ve skutečnosti, pokud je vybrána buňka C2 a stisknutím klávesy F5 přejdete na Rg, uvidíte toto:
Pokud byla počáteční buňka C12, stisknutím klávesy F5 přejdete na Rg se vytvoří toto:
Takže teď, s odpovědí definovanou jako =SUM(1/COUNTIF(rg,rg)), jste hotovi!
Podívejme se blíže na to, jak tento vzorec funguje, na mnohem jednodušším příkladu. Normálně je syntaxe pro COUNTIF =COUNTIF(range,criteria), například =COUNTIF(C1:C10, "b")na tomto obrázku:
To by dalo 2 jako počet b v rozsahu. Ale předání samotného rozsahu jako kritéria použije každou položku v rozsahu jako kritéria. Pokud zvýrazníte tuto část vzorce:
a stiskněte F9, uvidíte:
Každá položka v rozsahu je vyhodnocena a tato řada čísel znamená, že existuje jedna a a existují dvě b, tři c a čtyři d. Tato čísla jsou rozdělena na 1, takže 1, ½, ½, ⅓, ⅓, ⅓, ¼, ¼, ¼, ¼, jak vidíte zde:
Takže máte 2 poloviny, 3 třetiny, 4 čtvrtiny a 1 celek a jejich sečtením získáte výnosy 4. Pokud by se položka opakovala 7krát, pak byste měli 7 sedmin atd. Docela v pohodě! (Klobouk dolů před Davidem Hagerem za objevení / vymýšlení tohoto vzorce.)
Ale počkej minutu. V současné době musíte tento vzorec zadat pouze v C2, C12 a C17. Nebylo by lepší, kdybyste jej mohli zadat do C2 a vyplnit a zobrazit pouze ve správných buňkách? Ve skutečnosti to můžete udělat. Vzorec v C2 můžete upravit tak, aby byl =IF(B1B2,Answer,""), a když ho vyplníte, provede úlohu:
Ale proč tady zastavit? Proč neudělat vzorec do pojmenovaného vzorce, jak je znázorněno zde:
Aby to fungovalo, musí být buňka C2 aktivní buňkou (nebo by se vzorec musel lišit). Nyní můžete nahradit vzorce sloupce C takto =Answer2:
Vidíte, že C3 má =Answer2, stejně jako všechny buňky ve sloupci C. Proč to nepokračovat ve sloupci D? Zde je zobrazen vzorec v D2 po uplatnění srovnání na B1 a B2:
Pokud tedy ponecháte buňku D2 vybranou a definujete jiný vzorec, řekněte odpověď 3:
pak můžete vstoupit =Answer3do buňky D2 a vyplnit:
Tady je horní část listu se zobrazením vzorců, následovaný stejným snímkem obrazovky s hodnotami zobrazenými:
Když se to ostatní pokusí zjistit, mohli by si nejprve poškrábat hlavu!
Tento článek pro hosta je od Excel MVP Boba Umlasa. Je to z knihy Další Excel mimo krabici. Chcete-li zobrazit další témata v knize, klikněte sem.
