Použití proměnných rozsahů pro jedinečné počty - tipy pro Excel

Obsah

Řekněme, že chcete počítat jedinečné položky ze seznamu, ale s obratem. A řekněme, že pracujete s tímto listem:

Ukázkový list

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:

Pomocný sloup

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:

Unikátní položky

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:

Překvapivá odpověď

Whoa! Jak tohle funguje?

Podívejte se na odpověď v definovaných jménech na tomto obrázku:

Definovaná jména ve Správci jmen

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:

Definice Rg

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:

Kratší vzorec

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:

Aktualizovaný vzorec Rg

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:

Přejít na dialog

Rg - zvolený rozsah

Pokud byla počáteční buňka C12, stisknutím klávesy F5 přejdete na Rg se vytvoří toto:

Počáteční buňka jako C12

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:

COUNTIF vzorec

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:

Zvýrazněte vzorec

a stiskněte F9, uvidíte:

Stisknutím klávesy F9

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:

alt

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:

Zkopírujte vzorec

Ale proč tady zastavit? Proč neudělat vzorec do pojmenovaného vzorce, jak je znázorněno zde:

Pojmenovaný vzorec

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:

Použijte pojmenovaný vzorec

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: