Ebben az oktatóanyagban megtudhatja, hogyan számolhat egyedi értékeket Excelben képletek segítségével (COUNTIF és SUMPRODUCT függvények).
Hogyan kell megszámolni az egyedi értékeket az Excelben?
Tegyük fel, hogy van egy adathalmazunk az alábbiak szerint:
Ennek az oktatóanyagnak a céljaira az A2: A10 tartományt NEVEKKÉNT nevezem. A továbbiakban ezt a megnevezett tartományt fogjuk használni a képletekben.
Lásd még: Elnevezett tartományok létrehozása az Excelben.
Ebben az adathalmazban ismétlés található a NAMES tartományban. Az egyedi nevek számának kiszámításához ebből az adatkészletből (A2: A10) a COUNTIF és a SUMPRODUCT függvények kombinációját használhatjuk az alábbiak szerint:
= SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
Hogyan működik ez a képlet?
A jobb megértés érdekében bontsuk le ezt a képletet:
- COUNTIF (NAMES, NAMES)
- A képlet ezen része tömböt ad vissza. A fenti példában ez {2; 2; 3; 1; 3; 1; 2; 3; 2} lenne. Az itt szereplő számok azt jelzik, hogy egy adott cellatartományban hányszor fordul elő érték.
Például a név Bob, amely kétszer fordul elő a listában, ezért Bob 2 -es számát adja vissza. Hasonlóképpen, Steve háromszor fordul elő, és ezért 3 -at adnak vissza Steve -nek.
- A képlet ezen része tömböt ad vissza. A fenti példában ez {2; 2; 3; 1; 3; 1; 2; 3; 2} lenne. Az itt szereplő számok azt jelzik, hogy egy adott cellatartományban hányszor fordul elő érték.
- 1/COUNTIF (NAMES, NAMES)
- A képlet ezen része tömböt adna vissza - {0.5; 0.5; 0.333333333333333; 1; 0.333333333333333; 1; 0.5; 0.333333333333333; 0.5}
Mivel a tömböt elosztottuk 1 -gyel, ezért ezt a tömböt adja vissza.
Például a fentebb visszaadott tömb első eleme 2. Ha az 1 -et elosztjuk 2 -vel, akkor az .5 értéket adja vissza.
- A képlet ezen része tömböt adna vissza - {0.5; 0.5; 0.333333333333333; 1; 0.333333333333333; 1; 0.5; 0.333333333333333; 0.5}
- SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
- A SUMPRODUCT egyszerűen összeadja ezeket a számokat. Ne feledje, hogy ha Bob kétszer fordul elő a listában, akkor a fenti tömb .5 értéket ad vissza, bárhol is szerepel Bob név a listában. Hasonlóképpen, mivel Steve háromszor jelenik meg a listában, a tömb .3333333 értéket ad vissza, amikor Steve neve megjelenik. Ha minden egyes névhez hozzáadjuk a számokat, az mindig 1 -et ad vissza. Ha pedig az összes számot összeadjuk, akkor visszaadja a listában szereplő egyedi nevek teljes számát.
Ez a képlet jól működik, amíg nincsenek üres cellák a tartományban. De ha van üres cellája, az #DIV/0 értéket ad vissza! hiba.
Hogyan kell kezelni az üres cellákat?
Először is értsük meg, miért ad vissza hibát, ha üres cella van a tartományban. Tegyük fel, hogy rendelkezünk az alábbi adatkészlettel (az A3 cella üres):
Ha ugyanazt a képletet használjuk, mint a fentiekben, akkor a képlet COUNTIF része egy {2; 0; 3; 1; 3; 1; 2; 3; 1} tömböt ad vissza. Mivel az A3 cellában nincs szöveg, a szám 0 lesz.
És mivel ezzel az egész tömbrel osztunk 1 -et, az #DIV/0 értéket ad vissza! hiba.
Az üres cellák esetén történő osztási hiba kezeléséhez használja az alábbi képletet:
= SUMPRODUCT ((1/COUNTIF (NAMES, NAMES & ””)))
Ennek a képletnek az egyik módosítása a COUNTIF függvény kritérium része. A NAMES és a ”” neveket használtuk a NAMES helyett. Ezzel a képlet visszaadja az üres cellák számát (korábban 0, ahol üres cella volt).
MEGJEGYZÉS: Ez a képlet az üres cellákat egyedi értékként számolja, és visszaadja az eredményben.
A fenti példában az eredménynek 5 -nek kell lennie, de 6 -ot ad vissza, mivel az üres cella az egyedi értékek egyike.
Íme a képlet, amely gondoskodik az üres cellákról, és nem veszi figyelembe a végeredményben:
= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))
Ebben a képletben 1 helyett számlálóként NAMES ”” -et használtunk. Ez IGAZ és HAMIS értékek tömbjét adja vissza. FALSE értéket ad vissza, amikor üres cella van. Mivel a TRUE értéke 1, a FALSE pedig 0, a üres cellákat nem számítjuk, mivel a számláló 0 (HAMIS).
Most, hogy készen állunk a képlet alapvázára, egy lépéssel tovább léphetünk és számolhatunk különböző adattípusokat.
Hogyan lehet megszámolni a szöveges egyedi értékeket az Excelben
Ugyanazt a koncepciót fogjuk használni a fentiekben, hogy létrehozzuk azt a képletet, amely csak az egyedi szövegértékeket számolja.
Íme a képlet, amely számolni fogja az Excel egyedi szövegértékeit:
= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))
Csak az ISTEXT (NAMES) képletet használtuk számlálóként. IGAZ értéket ad vissza, ha a cella szöveget tartalmaz, és hamis, ha nem. Nem számolja az üres cellákat, hanem azokat, amelyek üres karakterlánccal rendelkeznek („”).
Hogyan lehet megszámolni az Excelben a numerikus értékeket?
Itt található az a képlet, amely számolni fogja az Excel egyedi számértékeit
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))
Itt ISNUMBER -t (NAMES) használunk számlálóként. IGAZ értéket ad vissza, ha a cella numerikus adattípust tartalmaz, és hamis, ha nem. Nem veszi figyelembe az üres cellákat.
