Számolja meg az eltérő értékeket az Excel kimutatásban (egyszerű, lépésről lépésre szóló útmutató)

Az Excel Pivot táblázatok csodálatosak (tudom, hogy ezt minden alkalommal megemlítem, amikor a Pivot táblákról írok, de ez igaz).

Alapvető ismeretekkel és egy kis húzással néhány másodperc alatt elvégezheti a vödörnyi munkát.

Bár néhány kattintással sok mindent meg lehet tenni a kimutatástáblákban, vannak olyan dolgok, amelyekhez néhány további lépésre vagy némi munkára van szükség.

Az egyik ilyen dolog az, hogy különböző értékeket számolunk egy kimutatástáblában.

Ebben az oktatóanyagban megmutatom, hogyan kell megszámolni a különböző értékeket, valamint az egyedi értékeket egy Excel Pivot táblázatban.

Mielőtt azonban belekezdenék a különböző értékek számolásába, fontos megérteni a különbséget a „különböző szám” és az „egyedi szám” között.

Distinct Count Vs Egyedi gróf

Bár ezek ugyanannak tűnhetnek, ez nem.

Az alábbiakban egy példa látható, ahol létezik egy nevek adathalmaza, és külön soroltam az egyedi és elkülönülő neveket.

Egyedi értékek/nevek azok, amelyek csak egyszer fordulnak elő. Ez azt jelenti, hogy az összes ismétlődő és ismétlődő név nem egyedi. Az egyedi nevek a fenti adatkészlet C oszlopában vannak felsorolva

Külön értékek/nevek azok, amelyek legalább egyszer előfordulnak az adathalmazban. Tehát ha egy név háromszor jelenik meg, akkor is egy külön névnek számít. Ezt úgy érheti el, hogy eltávolítja az ismétlődő értékeket/neveket, és megtartja az összes megkülönböztetett értéket. A fenti nevek B oszlopában különálló nevek szerepelnek.

A látottak alapján a legtöbb esetben, amikor az emberek azt mondják, hogy a Pivot táblázatban meg akarják szerezni az egyedi számot, valójában megkülönböztetett számot jelentenek, erre az oktatóanyagban kitérek.

Számolja meg a különböző értékeket az Excel Pivot táblázatban

Tegyük fel, hogy megvannak az alábbi értékesítési adatok:

Kattintson ide a mintafájl letöltéséhez, és kövesse

Tegyük fel, hogy a fenti adatkészlettel szeretné megtalálni a választ a következő kérdésekre:

1. Hány értékesítési képviselő van az egyes régiókban (ami nem más, mint az egyes régiókban az értékesítési képviselők eltérő száma)?
2. Hány értékesítési képviselő értékesítette a nyomtatót 2021–2022 között?

Míg a kimutatástáblák néhány kattintással azonnal össze tudják foglalni az adatokat, a különböző értékek számának kiszámításához további lépéseket kell tennie.

Ha használ Excel 2013 vagy az azt követő verziók, van egy beépített funkció a Pivot táblázatban, amely gyorsan megadja a megkülönböztethető számokat. És ha használ Excel 2010 vagy az azt megelőző verziók, módosítania kell a forrásadatokat segítő oszlop hozzáadásával.

Ebben az oktatóanyagban a következő két módszer szerepel:

• Segédoszlop hozzáadása az eredeti adatkészlethez az egyedi értékek számításához (minden verzióban működik).
• Az adatok hozzáadása egy adatmodellhez és a Megkülönböztetett szám opció használata (elérhető az Excel 2013 -ban és az azt követő verziókban).

Van egy harmadik módszer, amelyet Roger mutat be ebben a cikkben (amelyet a Pivot a Pivot Table módszernek nevez).

Kezdjük el!

Segítő oszlop hozzáadása az adatkészlethez

Megjegyzés: Ha Excel 2013 és újabb verziókat használ, hagyja ki ezt a módszert, és lépjen a következő módszerre (mivel beépített kimutatástábla -funkciót használ - Különálló gróf).

Ez egy egyszerű módszer a különböző értékek számítására a kimutatástáblában, mivel csak egy segédoszlopot kell hozzáadnia a forrásadatokhoz. Miután hozzáadott egy segítő oszlopot, ezt az új adathalmazt használhatja a különálló szám kiszámításához.

Bár ez egy egyszerű megoldás, ennek a módszernek vannak hátrányai (ebben az oktatóanyagban később tárgyaljuk).

Először hadd mutassam meg, hogyan adhat hozzá segítő oszlopot, és hogyan kaphat egyértelmű számot.

Tegyük fel, hogy rendelkezem az alábbi adatkészlettel:

Adja hozzá a következő képletet az F oszlophoz, és alkalmazza azt az összes cellára, amely a szomszédos oszlopokban adatokat tartalmaz.

= HA (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

A fenti képlet a COUNTIFS függvény segítségével számolja meg, hányszor jelenik meg egy név az adott régióban. Ezenkívül vegye figyelembe, hogy a feltételtartomány $ C $ 2: C2 és $ B $ 2: B2. Ez azt jelenti, hogy folyamatosan bővül, ahogy halad az oszlopon.

Például az E2 cellában a feltételtartományok $ C $ 2: C2 és $ B $ 2: B2, az E3 cellában pedig ezek a tartományok kibővülnek $ C $ 2: C3 és $ B $ 2: B3 értékekre.

Ez biztosítja, hogy a COUNTIFS függvény a név első példányát 1 -nek, a név második példányát 2 -nek számolja, és így tovább.

Mivel csak a megkülönböztető neveket szeretnénk megkapni, az IF függvényt használjuk, amely 1 -et ad vissza, amikor egy név először megjelenik egy régióban, és 0 -t ad vissza, amikor újra megjelenik. Ez biztosítja, hogy csak a különböző neveket számolja a rendszer, és ne az ismétléseket.

Az alábbiakban bemutatjuk, hogyan nézne ki az adatkészlete, amikor hozzáadta a segédoszlopot.

Most, hogy módosítottuk a forrásadatokat, ennek segítségével létrehozhatunk egy kimutatástáblát, és a segítő oszlop segítségével megkaphatjuk az egyes régiók értékesítési képviselőinek számát.

Ennek lépései az alábbiakban találhatók:

1. Válassza ki az adathalmaz bármely celláját.
2. Kattintson a Beszúrás fülre.
3. Kattintson a Pivot Table elemre (vagy használja a billentyűparancsot - ALT + N + V)
4. A Pivot tábla létrehozása párbeszédpanelen győződjön meg arról, hogy a Táblázat/tartomány megfelelő (és tartalmazza a segédoszlopot), és az „Új munkalap” van kiválasztva.
5. Kattintson az OK gombra.

A fenti lépések beillesztenének egy új lapot, amely tartalmazza a Pivot táblát.

Húzza a „Régió” mezőt a Sorok területen, és a „D szám” mezőt az Értékek területen.

Kapsz egy pivot táblázatot az alábbiak szerint:

Most megváltoztathatja az oszlopfejlécet „D szám összege” értékről „Értékesítési képviselő” értékre.

A segítő oszlop használatának hátrányai:

Bár ez a módszer meglehetősen egyszerű, ki kell emelnem néhány hátrányt, amelyek a forrásadatok módosításával járnak a Pivot táblázatban:

• A segítő oszlopot tartalmazó adatforrás nem olyan dinamikus, mint a pivot tábla. Bár a Pivot tábla segítségével tetszőleges módon feldarabolhatja és feldarabolhatja az adatokat, segítő oszlop használata során elveszíti ennek a képességnek egy részét. Tegyük fel, hogy egy segítő oszlopot ad hozzá, hogy megkapja az egyes régiókban lévő értékesítési képviselők számát. Mi van akkor, ha Ön is szeretné megkapni az értékesítési nyomtatók értékesítési számát. Vissza kell térnie a forrásadatokhoz, és módosítania kell a segítő oszlop képletét (vagy új segédoszlopot kell hozzáadnia).
• Mivel további adatokat ad hozzá a kimutatástábla forrásához (amely szintén hozzáadódik a Pivot gyorsítótárhoz), ez nagyobb méretű Excel -fájlhoz vezethet.
• Mivel Excel képletet használunk, ez lelassíthatja az Excel munkafüzetet, ha több ezer adatsort tartalmaz.

Adatok hozzáadása az adatmodellhez és összegzés a Distinct Count használatával

A Pivot Table új funkcióval bővítette az Excel 2013 programot, amely lehetővé teszi a különálló számok lekérését az adathalmaz összegzése közben.

Ha korábbi verziót használ, akkor nem fogja tudni használni ezt a módszert (ahogyan a segítő oszlopot is meg kell adnia a fenti módszer szerint).

Tegyük fel, hogy rendelkezik az alább látható adatkészlettel, és szeretné megkapni az egyedi értékesítési képviselők számát minden régióban.

Az alábbiakban bemutatjuk azokat a lépéseket, amelyekkel a kimutatási táblázatban megkülönböztethető számértéket kaphat:

1. Válassza ki az adathalmaz bármely celláját.
2. Kattintson a Beszúrás fülre.
3. Kattintson a Pivot Table elemre (vagy használja a billentyűparancsot - ALT + N + V)
4. A Pivot tábla létrehozása párbeszédpanelen győződjön meg arról, hogy a Táblázat/tartomány helyes, és Új munkalap van a Kijelölt elemben.
5. Jelölje be a négyzetet: „Adja hozzá ezeket az adatokat az adatmodellhez”
6. Kattintson az OK gombra.

A fenti lépések beillesztenének egy új lapot, amely tartalmazza az új kimutatástáblát.

Húzza a régiót a Sorok területen, és az értékesítési képviselőt az Értékek területen. Kapsz egy pivot táblázatot az alábbiak szerint:

A fenti kimutatástábla az értékesítési képviselők összes számát adja meg az egyes régiókban (és nem a különálló számokat).

Ha meg szeretné tekinteni a kimutatást a kimutatási táblázatban, kövesse az alábbi lépéseket:

1. Kattintson a jobb gombbal az „Értékesítési képviselők száma” oszlop bármely cellájára.
2. Kattintson az Értékmező beállításai elemre
3. Az Értékmező beállításai párbeszédpanelen válassza a „Megkülönböztetett szám” lehetőséget számítás típusaként (előfordulhat, hogy le kell görgetnie a listát, hogy megtalálja).
4. Kattintson az OK gombra.

Észre fogja venni, hogy az oszlop neve „Értékesítési képviselők száma” -ról „Értékes értékesítési képviselők száma” -ra változik. Bármire megváltoztathatja, amit akar.

Néhány dolog, amit tud, amikor hozzáadja adatait az adatmodellhez:

• Ha az adatokat az adatmodellbe menti, majd az Excel egy régebbi verziójában nyitja meg, figyelmeztetést jelenít meg: „Néhány kimutatástábla -funkció nem kerül mentésre”. Előfordulhat, hogy nem látja a különálló számot (és az adatmodellt), ha egy régebbi verzióban nyitja meg, amely nem támogatja azt.
• Amikor hozzáadja adatait egy adatmodellhez, és létrehoz egy kimutatástáblát, az nem jeleníti meg a számított mezők és számított oszlopok hozzáadásának lehetőségeit.

Kattintson ide a mintafájl letöltéséhez

Mi van, ha egyedi értékeket (és nem különálló értékeket) szeretne számolni?

Ha egyedi értékeket szeretne számolni, akkor nincs beépített funkciója a kimutatástáblában, és csak a segítő oszlopokra kell hagyatkoznia.

Ne feledje - az egyedi értékek és a különböző értékek nem ugyanazok. Kattintson ide a különbség megismeréséhez.

Ilyen például az, ha rendelkezik az alábbi adatkészlettel, és szeretné megtudni, hogy hány értékesítési képviselő egyedi az egyes régiókban. Ez azt jelenti, hogy csak egy adott régióban működnek, a többiek nem.

Ilyen esetekben több segítő oszlop közül egyet kell létrehoznia.

Ebben az esetben az alábbi képlet segít:

= HA (HA (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

A fenti képlet ellenőrzi, hogy az értékesítési képviselő neve csak egy régióban vagy több régióban fordul elő. Ez úgy történik, hogy megszámolja a név előfordulásának számát egy régióban, és elosztja azt a név előfordulásainak teljes számával. Ha az érték kisebb, mint 1, ez azt jelzi, hogy a név két vagy több régióban fordul elő.

Abban az esetben, ha a név egynél több régióban fordul elő, akkor 0 -t ad vissza, egy pedig egyet.

A képlet azt is ellenőrzi, hogy a név megismétlődik -e ugyanabban a régióban. Ha a név megismétlődik, csak a név első példánya adja vissza az 1 értéket, a többi példány pedig 0 értéket.

Ez kissé bonyolultnak tűnhet, de ismét attól függ, hogy mit szeretne elérni.

Tehát, ha egyedi értékeket szeretne számolni egy kimutatástáblában, akkor használjon segítő oszlopokat, és ha különböző értékeket szeretne számolni, akkor használja a beépített funkciót (az Excel 2013 és újabb verzióiban), vagy használhat segítő oszlopot.

Kattintson ide a mintafájl letöltéséhez

A következő Pivot Table oktatóanyagok is tetszhetnek:

• Hogyan lehet szűrni adatokat az Excel pivot táblájában?
• A dátumok csoportosítása az Excel pivot tábláiban
• Számok csoportosítása az Excel kimutatásában
• Feltételes formázás alkalmazása pivot táblázatban az Excelben
• Szeletelők az Excel pivot táblájában
• A pivot táblázat frissítése az Excelben
• Törölje a pivot táblázatot az Excelben