Dříve jsme se naučili počítat jedinečné hodnoty v rozsahu. Také jsme se naučili extrahovat jedinečné hodnoty z rozsahu. V tomto článku se naučíme, jak počítat jedinečnou hodnotu v rozsahu s podmínkou v Excelu.
Obecný vzorec
{= SUMA (-(FREKVENCE (IF (podmínka, MATCH (rozsah, rozsah, 0))), ŘÁDEK (rozsah) -ROW (první buňka v rozsahu) +1)> 0))}
Je to maticový vzorec, použijte CTRL+SHIFT+ENTER
Stav : Kritéria, podle kterých chcete získat jedinečné hodnoty.
Rozsah : rozsah, ve kterém chcete získat jedinečné hodnoty.
první buňka v dosahu: Jedná se o odkaz na první buňku v rozsah. Pokud je rozsah A2: A10, pak je to A2.
Příklad:
Tady mám tato data jmen. Odpovídající třídy jsou uvedeny v sousedním sloupci. Musíme počítat jedinečná jména v každé třídě.
Pomocí výše uvedeného obecného vzorce napište tento vzorec do E2
{= SUMA (-(FREKVENCE (IF (B2: B19 = "Třída 1", MATCH (A2: A19, A2: A19,0)) ))}
Výše uvedený vzorec vrací jedinečnou hodnotu v rozsahu Excel A2: A19 za podmínky B2: B19 = "Třída 1".
Chcete -li získat jedinečné hodnoty v různých třídách, změňte kritéria. Zde jsme to naprogramovali napevno, ale můžete také uvést odkaz na buňku. Pokud nechcete, aby se měnily, použijte pro rozsahy pojmenované rozsahy nebo absolutní odkazování.
Jak to funguje?
Pojďme to rozebrat zevnitř.
LI(B2: B19 = "Třída 1",ZÁPAS(A2: A19, A2: A19,0))
B2: B19 = "Třída 1": Tato část vrátí pole true a false. PRAVDA pro každý zápas.
{TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; FALSE….}
ZÁPAS(A2: A19, A2: A19,0): tato část vrátí první umístění každé hodnoty v rozsahu A2: A19 podle majetku MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Nyní pro každou hodnotu TRUE získáme pozici a pro hodnotu false dostaneme hodnotu FALSE. Takže za celé IF prohlášení dostaneme
{1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE}.
Dále přejdeme k frekvenční části.
FREKVENCE(LI(B2: B19 = "Třída 1",ZÁPAS(A2: A19, A2: A19,0)),ŘÁDEK(A2: A19)-ŘÁDEK(A2) +1)
ŘADA (A2: A19): Tím se vrátí číslo řádku každé buňky v rozsahu A2: A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
Řádek (A2: A19) -ROW (A2): Nyní odečteme číslo prvního řádku od čísla každého řádku. Tím se vrátí pole sériového čísla od 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Protože chceme mít pořadové číslo od 1, přidáme k němu 1.
ŘADA (A2: A19)-ŘADA (A2) +1. To nám dává řadu sériových čísel od 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
To nám pomůže získat jedinečný stav podle stavu.
Nyní máme: FREKVENCE({1; FALSE; 1; FALSE; 5; 4; FALSE; FALSE; FALSE; FALSE; 2; FALSE; FALSE; 5; FALSE; 1; 8; FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Vrací frekvenci každého čísla v daném poli. {3; 1; 0; 1; 2; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Zde každé kladné číslo indikovalo výskyt jedinečné hodnoty při splnění kritérií. V tomto poli musíme počítat hodnoty větší než 0. Za tímto účelem to kontrolujeme> 0. Tím se vrátí PRAVDA a NEPRAVDA. Konvertujeme true false pomocí - (dvojitý binární operátor).
SOUČET(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) to znamená SUM ({1; 1; 0; 1; 1; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0})
A nakonec získáme jedinečný počet jmen v rozsahu podle kritérií jako 5.
Vím, že je to trochu složité na pochopení, ale zkontrolujete to z možnosti vyhodnocení vzorce.
K počítání jedinečných hodnot s více kritérii můžeme použít logickou logiku:
Počítejte jedinečnou hodnotu s více kritérii s logikou
{= SUMA (-(FREKVENCE (IF (podmínka1 * Podmínka2, MATCH (rozsah, rozsah, 0)), Řádek (rozsah) -ROW (první buňka v rozsahu) +1)> 0))})}
Výše uvedený obecný vzorec může počítat jedinečné hodnoty za více podmínek a když jsou všechny pravdivé.
Počítejte jedinečnou hodnotu s více kritérii s logikou nebo
{= SUMA (-(FREKVENCE (IF (podmínka1 + podmínka2, MATCH (rozsah, rozsah, 0)), ŘÁDEK (rozsah)) -ROW (první buňka v rozsahu) +1)> 0))}
Tento obecný vzorec lze použít k počítání jedinečných hodnot pomocí logiky Or. To znamená, že se bude počítat, pokud je některá z podmínek pravdivá.
Takže jo, lidi, takto počítáte jedinečné hodnoty v rozsahu za více podmínek. Je to trochu složité, ale je to rychlé. Jakmile jej začnete používat, zjistíte, jak funguje.
Pokud máte nějaké pochybnosti týkající se tohoto článku o vzorci Excelu, dejte mi vědět v sekci komentáře níže.
Stáhnout soubor:
Jak počítat jedinečné hodnoty v aplikaci Excel s kritériiVzorec aplikace Excel k extrahování jedinečných hodnot ze seznamu
Počítejte jedinečné hodnoty v aplikaci Excel
Oblíbené články:
Funkce VLOOKUP v Excelu
COUNTIF v aplikaci Excel 2016
Jak používat funkci SUMIF v aplikaci Excel
