Loe erinevad väärtused Exceli liigendtabelis (lihtne samm-sammuline juhend)

Exceli liigendtabelid on hämmastavad (ma tean, et mainin seda iga kord, kui kirjutan Pivot -tabelitest, kuid see on tõsi).

Põhimõistmise ja väikese lohistamise korral saate mõne sekundiga ämbritäie töö tehtud.

Kuigi Pivot -tabelites saab mõne klõpsuga palju ära teha, on mõned asjad, mis vajaksid mõningaid lisatoiminguid või pisut tööd.

Ja üks selline asi on Pivot -tabelis erinevate väärtuste loendamine.

Selles õpetuses näitan teile, kuidas Exceli liigendtabelis loendada erinevaid väärtusi ja kordumatuid väärtusi.

Kuid enne, kui hakkan selgeks tegema, kuidas erinevaid väärtusi kokku lugeda, on oluline mõista erinevust „erineva arvu” ja „kordumatu arvu” vahel.

Distinct Count Vs Unikaalne krahv

Kuigi need võivad tunduda samad, see ei ole.

Allpool on näide, kus on olemas nimede andmekogum ja ma olen ainulaadsed ja eristuvad nimed eraldi välja toonud.

Unikaalsed väärtused/nimed on need, mis esinevad ainult üks kord. See tähendab, et kõik nimed, mis korduvad ja millel on duplikaadid, ei ole ainulaadsed. Unikaalsed nimed on loetletud ülaltoodud andmekogumi veerus C.

Erinevad väärtused/nimed on need, mis esinevad andmekogumis vähemalt üks kord. Nii et kui nimi ilmub kolm korda, loetakse see ikkagi üheks eraldiseisvaks nimeks. Seda on võimalik saavutada, eemaldades topeltväärtused/nimed ja säilitades kõik eraldiseisvad. Eraldi nimed on loetletud ülaltoodud andmekogumi veerus B.

Selle põhjal, mida olen näinud, on enamikel juhtudel, kui inimesed ütlevad, et soovivad saada unikaalse arvu Pivot -tabelis, siis tegelikult tähendavad nad erinevat arvu, mida ma selles õpetuses kajastan.

Loendage Exceli liigendtabelis erinevaid väärtusi

Oletame, et teil on müügiandmed, nagu allpool näidatud:

Näidisfaili allalaadimiseks ja selle järgimiseks klõpsake siin

Ülaltoodud andmekogumi abil ütleme, et soovite leida vastuse järgmistele küsimustele:

1. Mitu müügiesindajat on igas piirkonnas (mis pole midagi muud kui iga piirkonna müügiesindajate selge arv)?
2. Mitu müügiesindajat müüs printeri aastatel 2021–2022?

Pivot -tabelid võivad andmete mõne hetkega koheselt kokku võtta, kuid erinevate väärtuste loendamiseks peate tegema veel mõned toimingud.

Kui kasutate Excel 2013 või versioonid pärast seda, Pivot Tabelis on sisseehitatud funktsioon, mis annab kiiresti selge arvu. Ja kui kasutate Excel 2010 või versioonid enne seda, peate muutma lähteandmeid, lisades abistaja veeru.

Selles õpetuses käsitletakse järgmisi kahte meetodit:

• Abi veeru lisamine algsesse andmekogumisse unikaalsete väärtuste lugemiseks (töötab kõikides versioonides).
• Andmete lisamine andmemudelisse ja suvandi Distinct Count kasutamine (saadaval rakenduses Excel 2013 ja pärast seda).

Roger näitab selles artiklis ka kolmandat meetodit (mida ta nimetab Pivot Pivot Table meetodiks).

Alustame!

Abikolonni lisamine andmestikku

Märkus. Kui kasutate Excel 2013 ja uuemaid versioone, jätke see meetod vahele ja liikuge järgmise meetodi juurde (kuna see kasutab sisseehitatud liigendtabeli funktsiooni - Eristatav krahv).

See on lihtne viis liigendtabelis erinevate väärtuste loendamiseks, kuna lähteandmetesse on vaja lisada vaid abistajaveerg. Kui olete lisanud abistajaveeru, saate selle uue andmekogumi abil arvutada erinevat arvu.

Kuigi see on lihtne lahendus, on sellel meetodil mõningaid puudusi (mida käsitletakse selles juhendis hiljem).

Esmalt näitan teile, kuidas lisada abistajaveergu ja saada selge arv.

Oletame, et mul on allpool näidatud andmekogum:

Lisage veergu F järgmine valem ja rakendage see kõigi lahtrite puhul, mille andmed on külgnevates veergudes.

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

Ülaltoodud valem kasutab funktsiooni COUNTIFS, et loendada, mitu korda nimi antud piirkonnas ilmub. Pange tähele, et kriteeriumide vahemik on $ C $ 2: C2 ja $ B $ 2: B2. See tähendab, et see laieneb veerust allapoole laienedes.

Näiteks lahtris E2 on kriteeriumivahemikud $ C $ 2: C2 ja $ B $ 2: B2 ning lahtris E3 laienevad need vahemikud kuni $ C $ 2: C3 ja $ B $ 2: B3.

See tagab, et funktsioon COUNTIFS loeb nime esimese eksemplari 1 -ks, nime teise eksemplari 2 -ks jne.

Kuna me tahame saada ainult erinevaid nimesid, kasutatakse funktsiooni IF, mis tagastab 1, kui piirkonnale esmakordselt ilmub nimi, ja tagastab 0, kui see uuesti ilmub. See tagab, et loendatakse ainult erinevaid nimesid, mitte kordusi.

Allpool on näha, kuidas teie andmestik näeks välja, kui olete lisanud abistaja veeru.

Nüüd, kui oleme lähteandmeid muutnud, saame selle abil luua liigendtabeli ja kasutada abistaja veergu, et saada igas piirkonnas müügiesindajate selge arv.

Allpool on toodud sammud selle tegemiseks.

1. Valige andmekogus suvaline lahter.
2. Klõpsake vahekaarti Lisa.
3. Klõpsake Pivot Table (või kasutage kiirklahvi - ALT + N + V)
4. Veenduge dialoogiboksis Pivot Tabeli loomine, et tabel/vahemik on õige (ja sisaldab abistajaveergu) ja „Uus tööleht” valitud.
5. Klõpsake nuppu OK.

Ülaltoodud toimingud sisestaksid uue lehe, millel on pöördtabel.

Lohistage ridade piirkonnas välja „Piirkond” ja väärtuste alal välja „D loend”.

Saate pöördtabeli, nagu allpool näidatud:

Nüüd saate muuta veeru päise väärtusest „Summa D arv” asemel „Müügiesindaja”.

Abistajaveeru kasutamise puudused:

Kuigi see meetod on üsna sirgjooneline, pean ma esile tooma mõned puudused, mis tulenevad lähteandmete muutmisest pöördetabelis:

• Andmeallikas koos abistajaveeruga ei ole nii dünaamiline kui liigendtabel. Kuigi saate liigendtabeli abil andmeid lõigata ja tükeldada mis tahes viisil, kaotate abistajaveeru kasutamisel osa sellest võimalusest. Oletame, et lisate abistajaveeru, et saada igas piirkonnas eraldi müügiesindajate arv. Mis siis saab, kui soovite ka saada printerite müügiesindajate arvu. Peate naasma lähteandmete juurde ja muutma abistaja veeru valemit (või lisama uue abistajaveeru).
• Kuna lisate Pivot Tabeli allikale (mis lisatakse ka Pivot -vahemällu) rohkem andmeid, võib see kaasa tuua suurema Exceli faili suuruse.
• Kuna kasutame Exceli valemit, võib see muuta teie Exceli töövihiku aeglaseks, kui teil on tuhandeid ridu andmeid.

Lisage andmed andmemudelisse ja tehke kokkuvõte, kasutades erinevat arvu

Pivot -tabel lisas Excel 2013 -sse uue funktsiooni, mis võimaldab andmekogumi kokkuvõtte tegemisel saada selge arvu.

Kui kasutate eelmist versiooni, ei saa te seda meetodit kasutada (nagu peaksite proovima lisada abistaja veeru, nagu on näidatud ülaltoodud meetodil).

Oletame, et teil on allpool näidatud andmekogum ja soovite saada igas piirkonnas ainulaadse müügiesindaja arvu.

Allpool on toodud sammud tabeli selge loendusväärtuse saamiseks.

1. Valige andmekogus suvaline lahter.
2. Klõpsake vahekaarti Lisa.
3. Klõpsake Pivot Table (või kasutage kiirklahvi - ALT + N + V)
4. Veenduge dialoogiboksis Pivot Tabeli loomine, et tabel/vahemik on õige ja valikul Uus tööleht.
5. Märkige ruut „Lisa need andmed andmemudelisse”
6. Klõpsake nuppu OK.

Ülaltoodud sammud sisestaksid uue lehe, millel on uus pöördetabel.

Lohistage piirkond ridade piirkonnas ja müügiesindaja väärtuste piirkonnas. Saate pöördtabeli, nagu allpool näidatud:

Ülaltoodud liigendtabel annab müügiesindajate koguarvu igas piirkonnas (ja mitte erinevat arvu).

Pivot -tabeli selge arvu saamiseks järgige alltoodud samme.

1. Paremklõpsake veeru „Müügiesindajate arv” mis tahes lahtrit.
2. Klõpsake väärtusevälja sätteid
3. Valige dialoogiboksis Väärtusväljade seaded arvutuste tüübiks „Eristatav arv” (selle leidmiseks peate võib -olla loendi alla kerima).
4. Klõpsake nuppu OK.

Märkate, et veeru nimi muutub „müügiesindajate arvu” asemel „eriliseks müügiesindajate arvuks”. Saate selle muuta mis iganes soovite.

Mõned asjad, mida teate andmemudelile andmete lisamisel:

• Kui salvestate andmed andmemudelisse ja avate seejärel Exceli vanemas versioonis, kuvatakse teile hoiatus: „Mõnda pöördtabeli funktsiooni ei salvestata”. Te ei pruugi näha erinevat arvu (ja andmemudelit), kui see avatakse vanemas versioonis, mis seda ei toeta.
• Kui lisate andmed andmemudelisse ja koostate liigendtabeli, ei kuvata selles arvutatud väljade ja arvutatud veergude lisamise võimalusi.

Näidisfaili allalaadimiseks klõpsake siin

Mida teha, kui soovite loendada unikaalseid väärtusi (ja mitte erinevaid väärtusi)?

Kui soovite arvestada unikaalseid väärtusi, pole teil liigendtabelis sisseehitatud funktsioone ja peate tuginema ainult abistajate veergudele.

Pidage meeles - ainulaadsed väärtused ja erinevad väärtused ei ole samad. Erinevuse teadasaamiseks klõpsake siin.

Üks näide võib olla see, kui teil on allpool olev andmekogum ja soovite teada saada, kui palju müügiesindajaid on iga piirkonna jaoks ainulaadne. See tähendab, et nad tegutsevad ainult ühes konkreetses piirkonnas ja mitte teistes.

Sellistel juhtudel peate looma ühe mitmest abistajaveerust.

Sel juhul aitab järgmine valem:

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

Ülaltoodud valem kontrollib, kas müügiesindaja nimi esineb ainult ühes piirkonnas või rohkem kui ühes piirkonnas. Ta teeb seda, lugedes nime esinemiste arvu piirkonnas ja jagades selle nime esinemiste koguarvuga. Kui väärtus on väiksem kui 1, näitab see, et nimi esineb kahes või enam kui kahes piirkonnas.

Kui nimi esineb rohkem kui ühes piirkonnas, tagastab see väärtuse 0, tagastab ühe.

Valem kontrollib ka seda, kas nime korratakse samas piirkonnas või mitte. Kui nime korratakse, tagastab ainult esimene nime eksemplar väärtuse 1 ja kõik muud eksemplarid 0.

See võib tunduda natuke keeruline, kuid see sõltub jällegi sellest, mida proovite saavutada.

Seega, kui soovite Pivot -tabelis loendada unikaalseid väärtusi, kasutage abiveerge ja kui soovite loendada erinevaid väärtusi, võite kasutada sisseehitatud funktsioone (Excel 2013 ja uuemates versioonides) või kasutada abiveergu.

Näidisfaili allalaadimiseks klõpsake siin

Samuti võivad teile meeldida järgmised liigendtabeli õpetused:

• Kuidas Excelis liigendtabelis andmeid filtreerida
• Kuupäevade rühmitamine Exceli liigendtabelites
• Kuidas rühmitada numbreid Exceli liigendtabelis
• Tingimusvormingu rakendamine Exceli pöördtabelis
• Viilutajad Exceli liigendtabelis
• Pivot -tabeli värskendamine Excelis
• Pivot -tabeli kustutamine Excelis