2 Manieren om Distinct Count te berekenen met draaitabellen
Bottom Line: Leer twee manieren om de uitdaging van gegevensanalyse, het berekenen van onderscheidende aantallen, op te lossen met draaitabellen.
Skill Level: Intermediate
Video Tutorial
Download het Excel-bestand
Ik heb zowel het originele bestand als het bestand met de oplossing voor u bijgevoegd om hier te downloaden:
Gegevensanalyse-uitdaging.xlsx (16.1 KB)
Data Analysis Challenge – Pivot Table Solution FINAL.xlsx (118.4 KB)
Telling van unieke rijen
In dit bericht gaan we twee verschillende manieren bekijken om een afzonderlijke telling uit te voeren met behulp van draaitabellen. Deze twee methoden zijn ingediend als oplossingen voor de gegevensanalyse-uitdaging die u hier kunt vinden:
Excel Data Analysis Challenge
Om de uitdaging samen te vatten: we willen een samenvattend rapport maken van het aantal deals per fase, maar er zijn meerdere rijen per deal in de CRM-gegevens. We moeten dus een manier vinden om voor elke deal een aparte telling te maken (waarbij unieke rijen worden geteld), zodat we ze bij elkaar kunnen optellen.
Tussen haakjes, dank aan iedereen die een oplossing voor de data-uitdaging heeft ingediend! Er waren veel goede inzendingen.
Oplossing #1 – Het gebruik van een Helper Column
Het mooie van deze oplossing is dat deze in elke versie van Excel kan worden gebruikt.
Start met het omzetten van uw gegevens in een Excel Tabel. Selecteer daartoe een cel in de gegevensverzameling en klik op Opmaken als tabel op het tabblad Start. Klik met de rechtermuisknop op de gewenste tabelopmaak en selecteer Opmaak toepassen en wissen.
Klik op OK wanneer het venster Opmaken als tabel verschijnt.
Nu uw gegevens in Tabel-formaat zijn, voegt u een hulpkolom toe aan de rechterkant van de tabel en geeft u deze het label Deal Count. Gebruik de COUNTIF-functie, met als bereik de kolom Deal ID en als criterium de cel in de kolom Deal ID die overeenkomt met de rij waarin u zich bevindt.
De formule geeft het aantal rijen voor elk nummer van de Deal ID. Als we de formule delen door het getal 1, krijgen we fracties in elk van die cellen die, wanneer ze bij elkaar worden opgeteld, voor elke deal één vermelding tellen.
De wijziging van de formule is hier in groen te zien:
=1/COUNTIF(,])
Nu we deze breuken hebben die ons een afzonderlijke telling zullen geven wanneer we onze draaitabel maken, kunnen we verder gaan en de draaitabel maken door op het tabblad Invoegen te kiezen voor Draaitabel.
Om ons samenvattend rapport te maken met behulp van de nieuwe draaitabel, plaatsen we de Verkoopfase in het gebied Rijen en Deal Count in het gebied Som van de waarden.
Dit geeft ons het samenvattingsrapport waarnaar we op zoek zijn, met een telling van deals in elke verkoopfase.
Het mooie van het gebruik van een draaitabel is dat wanneer we brongegevens toevoegen of verwijderen, we de draaitabel kunnen vernieuwen (Alt + F5) om de wijzigingen op te nemen.
Oplossing # 2 – Power Pivot gebruiken
Deze oplossing is alleen beschikbaar voor versies van Excel die 2013 of later voor Windows zijn.
We willen nog steeds dat onze gegevens worden opgemaakt als een Excel-tabel, maar we hebben geen hulpkolom nodig voor deze oplossing.
Deze keer, wanneer we onze draaitabel maken, gaan we het vakje aanvinken dat zegt Voeg deze tabel toe aan het gegevensmodel. (Data Model is een andere term voor PowerPivot.)
Wanneer u deze keer uw draaitabel maakt, sleept u Deal ID naar het gebied Som van waarden.
Dat levert in eerste instantie getallen op die we niet in ons overzichtsrapport willen hebben. Om dit te verhelpen, klikken we met de rechtermuisknop op de kolomkop Som van Deal ID en selecteren we Waardeveldinstellingen. Dit opent een venster waarin we Distinct Count als berekeningstype kunnen kiezen.
De functie Distinct Count doorloopt de kolom Deal ID en geeft ons een telling van de unieke waarden, zodat ons samenvattingsrapport er net zo uitziet als dat voor oplossing 1.
Vergelijking van de twee oplossingen
Beide oplossingen zijn geweldig omdat ze kunnen worden ververst als er nieuwe gegevens aan de brontabel worden toegevoegd.
Het voordeel van oplossing 1 is dat deze in elke versie van Excel kan worden uitgevoerd. Dat gezegd hebbende, als u 2013 of later in Windows gebruikt, is oplossing #2 de superieure optie. Oplossing #1 wordt namelijk onhandig als u de gegevens probeert te filteren (bijvoorbeeld voor een bepaald product) of slicers gebruikt om de gegevens verder te ontleden.
Als je meer wilt weten over het gebruik van draaitabellen, heb ik een aparte blogpost die je hier kunt bekijken: Introduction to Pivot Tables and Dashboards.
Other Solutions
Er zijn nog veel meer goede oplossingen voor de uitdaging ingediend. Ze omvatten het gebruik van Power Query en nieuwe dynamische functies. We zullen die in toekomstige berichten bekijken, maar ik wilde met deze twee beginnen omdat ze universeler waren in termen van Excel-versie-toegang.