Saskaitiet atšķirīgās vērtības Excel rakurstabulā (vienkārša soli pa solim)

Excel rakurstabulas ir pārsteidzošas (es zinu, ka to pieminu katru reizi, kad rakstu par rakurstabulām, bet tā ir taisnība).

Ar pamata izpratni un nelielu vilkšanu un nomešanu jūs varat paveikt visu darbu dažu sekunžu laikā.

Lai gan rakurstabulās var paveikt daudz ar dažiem klikšķiem, ir dažas lietas, kurām būtu jāveic dažas papildu darbības vai nedaudz jāstrādā.

Un viena šāda lieta ir saskaitīt atšķirīgas vērtības rakurstabulā.

Šajā apmācībā es parādīšu, kā Excel rakurstabulā saskaitīt atšķirīgas vērtības, kā arī unikālas vērtības.

Bet pirms ķeras pie dažādu vērtību skaitīšanas, ir svarīgi saprast atšķirību starp “atšķirīgo skaitu” un “unikālo skaitu”.

Distinct Count Vs Unikālais skaits

Lai gan tie var šķist viens un tas pats, tas nav.

Tālāk ir sniegts piemērs, kurā ir nosaukumu datu kopa, un es esmu uzskaitījis unikālus un atšķirīgus nosaukumus atsevišķi.

Unikālas vērtības/nosaukumi ir tie, kas notiek tikai vienu reizi. Tas nozīmē, ka visi vārdi, kas atkārtojas un kuriem ir dublikāti, nav unikāli. Unikālie nosaukumi ir uzskaitīti iepriekšējās datu kopas C slejā

Atšķirīgas vērtības/nosaukumi ir tie, kas vismaz vienu reizi parādās datu kopā. Tātad, ja vārds parādās trīs reizes, tas joprojām tiek uzskatīts par vienu atšķirīgu nosaukumu. To var panākt, noņemot vērtību/nosaukumu dublikātus un saglabājot visas atšķirīgās. Iepriekš minēto datu kopas B slejā ir norādīti atšķirīgi nosaukumi.

Pamatojoties uz to, ko esmu redzējis, vairumā gadījumu, kad cilvēki saka, ka vēlas iegūt unikālo skaitu rakurstabulā, tie patiesībā nozīmē atšķirīgu skaitu, par ko es runāju šajā apmācībā.

Saskaitiet atšķirīgās vērtības Excel rakurstabulā

Pieņemsim, ka jums ir pārdošanas dati, kā parādīts zemāk:

Noklikšķiniet šeit, lai lejupielādētu parauga failu un sekotu tam

Izmantojot iepriekš minēto datu kopu, pieņemsim, ka vēlaties atrast atbildi uz šādiem jautājumiem:

1. Cik tirdzniecības pārstāvju ir katrā reģionā (kas nav nekas cits kā atšķirīgais pārdošanas pārstāvju skaits katrā reģionā)?
2. Cik pārdošanas pārstāvis pārdeva printeri 2021.-2022.

Lai gan rakurstabulas var uzreiz apkopot datus ar dažiem klikšķiem, lai iegūtu atšķirīgu vērtību skaitu, jums būs jāveic vēl dažas darbības.

Ja jūs izmantojat Excel 2013 vai versijas pēc tam, rakurstabulā ir iebūvēta funkcionalitāte, kas ātri sniedz atšķirīgu skaitu. Un, ja jūs izmantojat Excel 2010 vai versijas pirms tam, jums būs jāmaina avota dati, pievienojot palīgu kolonnu.

Šajā apmācībā ir apskatītas šādas divas metodes:

• Palīgkolonnas pievienošana sākotnējā datu kopā, lai uzskaitītu unikālas vērtības (darbojas visās versijās).
• Datu pievienošana datu modelim un opcijas Distinct Count izmantošana (pieejams programmā Excel 2013 un versijās pēc tam).

Ir trešā metode, kuru Rodžers parāda šajā rakstā (ko viņš sauc par Pivot Pivot tabulas metodi).

Sāksim!

Palīgkolonnas pievienošana datu kopai

Piezīme. Ja izmantojat Excel 2013 un jaunākas versijas, izlaidiet šo metodi un pārejiet pie nākamās (jo tajā tiek izmantota iebūvēta rakurstabulas funkcija - Izcils grāfs).

Tas ir vienkāršs veids, kā saskaitīt atšķirīgas vērtības rakurstabulā, jo avota datiem ir jāpievieno tikai palīgkolonna. Kad esat pievienojis palīga kolonnu, varat izmantot šo jauno datu kopu, lai aprēķinātu atšķirīgo skaitu.

Lai gan tas ir vienkāršs risinājums, šai metodei ir daži trūkumi (apskatīti vēlāk šajā apmācībā).

Vispirms ļaujiet man parādīt, kā pievienot palīgu kolonnu un iegūt atšķirīgu skaitu.

Pieņemsim, ka man ir datu kopa, kā parādīts zemāk:

F slejā pievienojiet šādu formulu un izmantojiet to visām šūnām, kurām blakus esošajās kolonnās ir dati.

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

Iepriekšminētā formula izmanto funkciju COUNTIFS, lai saskaitītu, cik reižu vārds parādās attiecīgajā reģionā. Ņemiet vērā arī to, ka kritēriju diapazons ir $ C $ 2: C2 un $ B $ 2: B2. Tas nozīmē, ka, ejot lejup pa kolonnu, tas turpina paplašināties.

Piemēram, šūnā E2 kritēriju diapazoni ir $ C $ 2: C2 un $ B $ 2: B2, un šūnā E3 šie diapazoni paplašinās līdz $ C $ 2: C3 un $ B $ 2: B3.

Tas nodrošina, ka funkcija COUNTIFS vārda pirmo gadījumu skaita kā 1, vārda otro gadījumu kā 2 utt.

Tā kā mēs vēlamies iegūt tikai atšķirīgos nosaukumus, tiek izmantota funkcija IF, kas atgriež 1, kad reģiona nosaukums parādās pirmo reizi, un atgriež 0, kad tas parādās vēlreiz. Tas nodrošina, ka tiek uzskaitīti tikai atšķirīgi vārdi, nevis atkārtojumi.

Tālāk ir norādīts, kā izskatītos jūsu datu kopa, kad esat pievienojis palīga kolonnu.

Tagad, kad esam pārveidojuši avota datus, mēs varam to izmantot, lai izveidotu rakurstabulu un izmantotu palīgu kolonnu, lai iegūtu atšķirīgu pārdošanas pārstāvju skaitu katrā reģionā.

Tālāk ir norādītas darbības, kā to izdarīt:

1. Datu kopā atlasiet jebkuru šūnu.
2. Noklikšķiniet uz cilnes Ievietot.
3. Noklikšķiniet uz rakurstabulas (vai izmantojiet īsinājumtaustiņu - ALT + N + V)
4. Dialoglodziņā Izveidot rakurstabulu pārliecinieties, vai ir atlasīta pareiza tabula/diapazons (un tajā ir iekļauta palīga kolonna) un “Jauna darblapa”.
5. Noklikšķiniet uz Labi.

Iepriekš minētās darbības ievietos jaunu lapu, kurā ir rakurstabula.

Velciet lauku “Reģions” apgabalā Rindas un lauku “D skaits” apgabalā Vērtības.

Jūs saņemsiet rakurstabulu, kā parādīts zemāk:

Tagad jūs varat mainīt slejas galveni no “Sum of D count” uz “Sales Rep”.

Palīgkolonnas izmantošanas trūkumi:

Lai gan šī metode ir diezgan vienkārša, man ir jāuzsver daži trūkumi, kas rodas, mainot avota datus rakurstabulā:

• Datu avots ar palīga kolonnu nav tik dinamisks kā rakurstabula. Lai gan, izmantojot rakurstabulu, varat griezt un sagriezt datus jebkādā veidā, izmantojot palīgu kolonnu, jūs zaudējat daļu no šīs iespējas. Pieņemsim, ka pievienojat palīgu kolonnu, lai katrā reģionā iegūtu atsevišķu tirdzniecības pārstāvju skaitu. Ko darīt, ja vēlaties arī iegūt atšķirīgu pārdošanas printeru skaitu. Jums būs jāatgriežas pie avota datiem un jāmaina palīga kolonnas formula (vai jāpievieno jauna palīga kolonna).
• Tā kā jūs pievienojat vairāk datu rakurstabulas avotam (kas tiek pievienots arī rakurstabulai), tas var izraisīt lielāku Excel faila izmēru.
• Tā kā mēs izmantojam Excel formulu, tas var palēnināt jūsu Excel darbgrāmatas darbību, ja jums ir tūkstošiem datu rindu.

Pievienojiet datus datu modelim un apkopojiet, izmantojot atšķirīgu skaitu

Pivot tabula programmā Excel 2013 pievienoja jaunu funkcionalitāti, kas ļauj iegūt atšķirīgu skaitu, apkopojot datu kopu.

Ja izmantojat iepriekšējo versiju, šo metodi nevarēsit izmantot (tāpat kā mēģiniet pievienot palīga kolonnu, kā parādīts iepriekš minētajā metodē).

Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties iegūt unikālo pārdošanas pārstāvju skaitu katrā reģionā.

Tālāk ir norādītas darbības, lai rakurstabulā iegūtu atšķirīgu skaitīšanas vērtību.

1. Datu kopā atlasiet jebkuru šūnu.
2. Noklikšķiniet uz cilnes Ievietot.
3. Noklikšķiniet uz rakurstabulas (vai izmantojiet īsinājumtaustiņu - ALT + N + V)
4. Dialoglodziņā Izveidot rakurstabulu pārliecinieties, vai tabula/diapazons ir pareizs un atlasīta jauna darblapa.
5. Atzīmējiet izvēles rūtiņu “Pievienot šos datus datu modelim”
6. Noklikšķiniet uz Labi.

Iepriekš minētās darbības ievietos jaunu lapu, kurā ir jauna rakurstabula.

Velciet apgabalu Rindas apgabalā un apgabalā Vērtības tirdzniecības pārstāvis. Jūs saņemsiet rakurstabulu, kā parādīts zemāk:

Iepriekšējā rakurstabulā ir norādīts pārdošanas pārstāvju kopējais skaits katrā reģionā (nevis atsevišķais skaits).

Lai iegūtu atšķirīgo skaitu rakurstabulā, veiciet tālāk norādītās darbības.

1. Ar peles labo pogu noklikšķiniet uz jebkuras šūnas slejā “Pārdevēju skaits”.
2. Noklikšķiniet uz Vērtību lauka iestatījumi
3. Dialoglodziņā Vērtības lauka iestatījumi kā aprēķina veidu atlasiet “Atšķirīgs skaits” (iespējams, lai to atrastu, jums ir jāritina saraksts uz leju).
4. Noklikšķiniet uz Labi.

Jūs pamanīsit, ka kolonnas nosaukums tiek mainīts no “Pārdošanas pārstāvju skaits” uz “Atšķirīgs pārdošanas pārstāvju skaits”. Jūs varat to mainīt uz visu, ko vēlaties.

Dažas lietas, ko zināt, pievienojot datus datu modelim:

• Ja saglabājat datus datu modelī un pēc tam atverat vecākā Excel versijā, tiks parādīts brīdinājums - “Dažas rakurstabulas funkcijas netiks saglabātas”. Iespējams, neredzēsit atšķirīgo skaitu (un datu modeli), ja tas tiks atvērts vecākā versijā, kas to neatbalsta.
• Pievienojot datus datu modelim un izveidojot rakurstabulu, tajā netiks rādītas iespējas pievienot aprēķinātos laukus un aprēķinātās kolonnas.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Ko darīt, ja vēlaties saskaitīt unikālas vērtības (nevis atšķirīgas vērtības)?

Ja vēlaties saskaitīt unikālas vērtības, rakurstabulā nav iebūvētu funkciju, un jums būs jāpaļaujas tikai uz palīgu kolonnām.

Atcerieties - unikālas vērtības un atšķirīgas vērtības nav vienādas. Noklikšķiniet šeit, lai uzzinātu atšķirību.

Viens piemērs varētu būt, ja jums ir zemāk esošā datu kopa un vēlaties uzzināt, cik pārdošanas pārstāvju ir unikāli katram reģionam. Tas nozīmē, ka tie darbojas tikai vienā noteiktā reģionā, bet ne citos.

Šādos gadījumos jums ir jāizveido viena no vairākām palīgu kolonnām.

Šajā gadījumā zemāk esošā formula palīdz:

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

Iepriekš minētā formula pārbauda, ​​vai tirdzniecības pārstāvja vārds ir atrodams tikai vienā reģionā vai vairākos reģionos. Tas tiek darīts, saskaitot vārda sastopamības skaitu reģionā un dalot to ar kopējo vārda sastopamības gadījumu skaitu. Ja vērtība ir mazāka par 1, tas norāda, ka nosaukums sastopams divos vai vairākos reģionos.

Ja nosaukums parādās vairāk nekā vienā reģionā, tas atgriež 0, bet atgriež vienu.

Formula arī pārbauda, ​​vai vārds tiek atkārtots tajā pašā reģionā vai nē. Ja nosaukums tiek atkārtots, tikai pirmā vārda gadījums atgriež vērtību 1, bet visi citi gadījumi atgriež 0.

Tas var šķist nedaudz sarežģīti, taču tas atkal ir atkarīgs no tā, ko jūs mēģināt sasniegt.

Tātad, ja rakurstabulā vēlaties saskaitīt unikālas vērtības, izmantojiet palīgu kolonnas un, ja vēlaties saskaitīt atšķirīgas vērtības, varat izmantot iebūvēto funkcionalitāti (programmā Excel 2013 un jaunākās versijās) vai palīgu kolonnu.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Jums varētu patikt arī šādas rakurstabulu apmācības:

• Kā filtrēt datus rakurstabulā programmā Excel
• Kā grupēt datumus Excel rakurstabulās
• Kā grupēt numurus Excel rakurstabulā
• Kā lietot nosacītu formatējumu Excel rakurstabulā
• Slicers Excel rakurstabulā
• Kā atsvaidzināt rakurstabulu programmā Excel
• Pivot tabulas dzēšana programmā Excel