Erkende items tellen in draaitabel

Hoe unieke items te tellen (count distinct) in een Excel draaitabel. Drie methoden, voor verschillende versies van Excel. Download de gratis werkmap om mee te volgen

Met dank aan Roger Govier, die deze tutorial en werkmap heeft gemaakt.

Unieke telling

Als u een draaitabel gebruikt om uw gegevens samen te vatten, hebt u wellicht vragen als:

• Hoeveel unieke (verschillende) klanten hebben in elke regio aankopen gedaan?
• Hoeveel unieke producten zijn er in elke winkel verkocht?
• Hoeveel unieke verkopers hebben elk product per regio verkocht?

Er is geen ingebouwde functie “Unieke telling” in een normale draaitabel, maar in deze handleiding ziet u hoe u een unieke telling van items in een draaitabel kunt krijgen.

• In Excel 2013 en later, maak een Data Model
• In Excel 2010, en latere versies, gebruik een techniek die “Pivot the Pivot table”.
• Of, in oudere versies, voeg een nieuwe kolom toe aan de brongegevens, en gebruik CountIf.
• Als de Power Pivot add-in is geïnstalleerd, gebruik deze dan om een afzonderlijke telling weer te geven

Deze video toont de stappen om een afzonderlijke telling weer te geven met het Data Model.

Inleiding

Vaak hebben we grote gegevensverzamelingen die we met behulp van een draaitabel willen analyseren, maar we willen alleen de unieke occurrences van enkele gegevensitems tellen. Helaas hebben draaitabellen nooit de ingebouwde mogelijkheid gehad om unieke waarden te tellen.

In dit voorbeeld heeft het voorbeeldbestand 4999 records met productverkopen, met de regio en de naam van de verkoper. De eerste paar records zijn te zien in de schermafbeelding hieronder. U kunt het voorbeeldbestand downloaden via de onderstaande link.

Een draaitabel maken van de gegevens zou ons iets als het volgende opleveren, als we Persoon toevoegen aan het gebied Waarden van de draaitabel en het samenvatten met Telling.

Hiermee krijgen we echter een telling van alle transacties, niet de telling van het unieke aantal personen dat deze transacties binnen elke regio heeft verricht.

Een unieke telling verkrijgen

Er zijn workarounds die u kunt gebruiken om een unieke telling te verkrijgen:

• Methode 1: Voeg in Excel 2013 en later de brongegevens van de draaitabel toe aan het gegevensmodel, en een unieke telling kan eenvoudig worden uitgevoerd.
• Methode 2: Gebruik in Excel 2010 en later de techniek “pivot a pivot”.
• Methode 3: Voeg in oudere versies van Excel een kolom toe aan de brongegevens om een 1 te plaatsen in cellen op een rij waar de waarde uniek is, en een 0 in alle andere cellen. Vervolgens levert het optellen van deze extra kolom de unieke waarde op.
• Methode 4: Gebruik PowerPivot om de draaitabel te maken, en gebruik de functies ervan om een unieke telling te maken. Zie de details hieronder.

Alle vier methoden worden hieronder beschreven, samen met een prestatievergelijking tussen methode 2 en 3.

1. Toevoegen aan gegevensmodel – Excel 2013 en later

In Excel 2013, als u de brongegevens van een draaitabel toevoegt aan het gegevensmodel van de werkmap, kunt u eenvoudig een unieke telling maken.

NOOT: deze techniek maakt een op OLAP gebaseerde draaitabel, die enkele beperkingen heeft, zoals geen groepering en geen berekende velden of berekende items. Als u deze beperkte functies nodig hebt, kunt u in plaats daarvan de methode “pivot a pivot” proberen.

Een unieke graaf (Distinct Count)

Om de draaitabel te maken, volgt u deze stappen:

• Selecteer een cel in de tabel met brongegevens.
• Onder aan het dialoogvenster Maak draaitabel zet u een vinkje bij “Voeg deze gegevens toe aan het gegevensmodel”
• Klik op OK

Om de opmaak van de draaitabel in te stellen, volgt u deze stappen:

• In de draaitabel voegt u Regio toe aan het Rijen-gebied.
• Voeg deze 3 velden toe aan het veld Waarden — Persoon, Eenheden, Waarde
• Het veld Persoon bevat tekst, dus het is standaard ingesteld op Telling van Persoon. De telling toont het totale aantal transacties in elke regio, niet een unieke telling van personen

Om een unieke telling van personen in elke regio te krijgen, volgt u deze stappen:

• Klik met de rechtermuisknop op een van de waarden in het veld Persoon
• Klik op Waardeveldinstellingen

• In de lijst Waardeveld samenvatten door scrollt u naar beneden, en klik op Distinct Count (Onderscheiden aantal), en klik vervolgens op OK

Het veld Persoon verandert, en in plaats van het totale aantal transacties wordt nu een afzonderlijke telling van de namen van verkopers weergegeven.

2. Pivot the Pivot – Excel 2010

Om een uniek aantal verkopers per regio te krijgen in Excel 2010, kunnen we een Pivot Rapport maken, en dan “Pivot the Pivot” om ons eindresultaat te krijgen.

Maak de eerste Pivot Tabel

• Maak een Pivot Tabel van deze gegevens, met Regio en Persoon in het gebied Rijen
• Voeg Eenheden en Waarde toe in het gebied Waarden. Omdat Persoon een tekstveld is, wordt het in de draaitabel automatisch weergegeven als “Telling van”.
• Opmaak de draaitabel met de opmaak Tabellarisch rapport
• Stel alle itemlabels zo in dat ze in elke rij worden herhaald.
• Verander de Bijschriften, zodat de “Som van”
  TIP: Typ de oorspronkelijke veldnaam, met een spatie aan het eind, bijv. “eenheden”

Dit ziet er nu uit als een normale tabel met gegevens, maar het is een samenvatting van de oorspronkelijke volledige gegevenstabel met 4.999 rijen.

Create a Named Range for the Start Cell

Next, you’ll name the first cell in the pivot table.

• Selecteer cel B2, die de kop is in de kolom Regio van de draaitabel
• Klik in de Formulebalk en typ een naam voor de cel — pvtStart
• Druk op Enter om de naam af te ronden

Maak een naambereik voor de laatste rij

De volgende stap is het berekenen van de locatie van de laatste rij in de draaitabel. We gebruiken de MATCH functie, met een zeer groot getal, om het laatste getal in kolom D te vinden, en het rijnummer te krijgen. Deze formule werkt alleen in een kolom met getallen.

• Op het Gegevensblad, in cel M2, voert u deze formule in, om de laatste rij in de Eenheden-kolom van de draaitabel te berekenen:

  =MATCH(9,9E+307,Method_1!D:D,1)

• Om die cel een naam te geven, selecteert u cel M2, en in het vak Naam typt u PvtEnd, en drukt u op Enter.

Een dynamische reeks met naam maken

Volgende stap is het maken van een dynamische reeks met naam, om naar deze nieuwe tabel te verwijzen. Deze reeks begint in de cel met de naam pvtstart (de cel met de koptekst van de regio), en eindigt in de laatste rij gegevens in kolom E (de genoemde reeks, PvtEnd).

• Op het tabblad Formules in het lint klik je op Naam definiëren
• In het dialoogvenster Nieuwe naam voer je de naam mijnGegevens in
• In de vervolgkeuzelijst Bereik selecteer je Werkmap
• In het vak Verwijst naar typ je deze formule:
  =pvtStart:INDEX(Methode_1!$E:$E, PvtEnd)
• Klik op OK, om het genoemde bereik te maken

Maak de tweede draaitabel

Maak tenslotte een tweede draaitabel, gebaseerd op het dynamische bereik, myData.

Plaats Regio in het Rij-gebied, en Persoon, Eenheden en Waarde in het Waarden-gebied

Dit toont het aantal unieke verkopers per regio, zoals hieronder getoond. Bijvoorbeeld, in de eerste draaitabel toonde de regio Oost 8 unieke namen in de kolom Persoon, en dat is de telling in deze draaitabel.

Rewresh the Pivot Tables

Natuurlijk, omdat er 2 Pivot Tables bij deze oplossing betrokken zijn, moeten beide worden ververst nadat gegevens zijn toegevoegd of gewijzigd in de brontabel. Het is van essentieel belang dat eerst de eerste Pivot Table wordt ververst, gevolgd door een Refresh van de Final Pivot Table.

U kunt de draaitabellen handmatig verversen, of een macro gebruiken. Als u een macro wilt gebruiken, voegt u een van de volgende procedures toe aan het blad met uw uiteindelijke draaitabel.

A) Als beide draaitabellen op hetzelfde blad staan, gebruikt u deze code:

Private Sub Worksheet_Activate() Me.PivotTables(1).PivotCache.Refresh Me.PivotTables(2).PivotCache.Refresh End Sub

B) Als de draaitabellen op verschillende bladen staan, gebruikt u deze code (vervang de naam van uw eigenlijke blad door “yoursheetname”):

Private Sub Worksheet_Activate() Sheets("yoursheetname").PivotTables(1).PivotCache.Refresh Me.PivotTables(1).PivotCache.Refresh End Sub

(Optioneel) Geef het gegevensbereik van de draaitabel een naam

Als u macro’s gebruikt om de draaitabellen te verversen, kunt u ook een macro gebruiken om het bereik met de naam myData na elke verversing opnieuw in te stellen. Voeg de volgende procedure toe aan uw werkmap en roep deze SetmyData procedure aan in de Worksheet_Activate procedures.

Sub SetmyData()'Change "PT_A" to whatever name'you have given to your first Pivot Table'that is to be used as the source'for for the final Pivot Table'Also change the sheet name'from "Method_1" to'your sheet name if it is differentDim PTName As StringDim ShName As StringPTName = "PT_A"ShName = "Method_1"ThisWorkbook.Names.Add _ Name:="myData", _ RefersTo:=Sheets(ShName) _ .PivotTables(PTName) _ .TableRange1End Sub

3. Gebruik COUNTIF — Excel 2007 en eerder

In eerdere versies van Excel hebben draaitabellen niet de mogelijkheid om rijlabels te herhalen. Om een unieke telling van verkopers per regio te krijgen, kunt u een nieuwe kolom in de brongegevens toevoegen, en een COUNTIF-formule invoeren

U kunt het voorbeeldbestand downloaden via de onderstaande koppeling.

De COUNTIF-formule

Volg de volgende stappen om de formule toe te voegen:

In de brondata voegt u een nieuwe kolomkop — “Uniek” — toe in cel J1

In cel J2 voert u deze formule in, en kopieert u deze naar beneden naar de laatste rij gegevens:

=IF(COUNTIF($E$5:E5,E5)>1,0,1)

De eerste verwijzing in het bereik ($E$5) is Absoluut, en de tweede is relatief (E5), dus als we de formule naar beneden kopiëren, zal het bereik uitbreiden van $E$5:E5 naar $E$5:E6, dan $E$5:E7 enzovoort.

In elke rij controleren we hoe vaak de waarde in kolom E (Persoon) is voorgekomen, tot aan de huidige rij. Als het meer dan één keer is, is het resultaat nul, dus die persoon wordt niet meer geteld. Zo kunnen we zien hoe vaak de naam van een persoon uniek in de dataset is voorgekomen.

Met de extra kolom erbij zouden de gegevens er als volgt uitzien. De tweede keer dat “Harry” voorkomt, levert een nul op in de kolom Uniek.

NOOT: Als er personen met dezelfde naam zijn die in verschillende regio’s worden verkocht:

• In Excel 2007 of later: Gebruik COUNTIFS, om meerdere criteria te controleren. Bijvoorbeeld: =IF(COUNTIFS($D$5:$D5,D5,$E$5:E5,E5)=1,1,0)
• In Excel 2003 of eerder: voeg de namen van de regio’s en personen samen in een nieuwe kolom, en gebruik vervolgens de COUNTIF-formule om de unieke waarden in die samengevoegde kolom te vinden.

Maak een draaitabel

Om de unieke tellingen in een draaitabel te zien:

• Maak een draaitabel van deze gegevens, met Regio in het gebied Rijen
• Voeg Uniek, Eenheden en Waarde toe in het gebied Waarden.
• Verander de rubriek “Unique” in “Count of Person” of “Person”

We kunnen zien dat er 30 personen zijn die de verkoop hebben verricht, en het aantal van elk van hen dat zich in elke regio bevindt, en als we ervoor kiezen een regio uit te breiden, zouden we de individuele totalen voor elke persoon te zien krijgen.

Vergelijking van prestaties

Gebruik, indien mogelijk, de “pivot a pivot” methode, die veel sneller is.

COUNTIF Methode

Het gebruik van een COUNTIF formule in de brondata werkt, en geeft ons het gewenste resultaat, en op een relatief kleine dataset is de methode acceptabel. Maar het is zeer kostbaar in termen van verwerkingstijd, en op zeer grote datasets kan het extreem traag zijn.

Gebruik makend van Charles Williams’ “FastExcel” om de rekentijd van de werkmap te berekenen, levert het onderstaande resultaat op, waarbij de tijd die nodig is om het Gegevensblad opnieuw te berekenen 224 milliseconden bedraagt.

Pivot a Pivot Methode

De methode “Pivot a Pivot” levert ook het juiste resultaat op, met een unieke telling van Persoon, zoals vereist. Bovendien zijn er geen extra kolommen in de brongegevens nodig en hoeven er geen nieuwe formules te worden ingevoerd.

De resulterende werkmap is kleiner, en opnieuw met behulp van Charles Williams’ “FastExcel” om de tijd voor de berekening van het Gegevensblad is een ongelooflijk snelle 1.2 milliseconden

Bij kleine datasets is de timing van herberekening misschien niet kritisch, maar bij grotere datasets van 300.000 tot 400.000 rijen worden deze verschillen wel erg belangrijk.

Power Pivot

Als u de PowerPivot add-in hebt geïnstalleerd, kunt u die gebruiken om een onderscheidende telling voor een veld weer te geven. Deze video toont de stappen voor het maken van een Power Pivot draaitabel, en het toevoegen van een veld met de unieke telling.

Voor geschreven instructies, zie de Excel 2013 stappen op mijn draaitabel blog. Er zijn ook instructies voor Excel 2010.

Steekproefbestand: Om de video te kunnen volgen, downloadt u het voorbeeldbestand dat voor deze video is gebruikt

Haal de voorbeeldbestanden

• Gegevensmodel: Download de voorbeeldwerkmap Data Model, die het voorbeeld voor methode 1 (Data Model) bevat. Het bestand is gezipt, en in xlsx formaat. Het bestand bevat geen macro’s.
• Geen gegevensmodel: Download de niet-Data Model voorbeeld werkmap, die voorbeelden bevat voor Methode 2 (pivot een pivot) en Methode 3 (kolom in brongegevens). Het bestand is gezipt, en in xlsb-indeling. Het bestand bevat geen macro’s.
• Power Pivot-video: Om mee te volgen met de Power Pivot video in Methode 4, download dan het voorbeeldbestand dat voor de video is gebruikt

Gerelateerde tutorials

Bereiken benoemen

Uniek tellen in PowerPivot

Voorbeeldbestanden – Roger Govier

Over de ontwikkelaar

Roger Govier is een Excel MVP uit het Verenigd Koninkrijk die opdrachten in Excel en VBA uitvoert voor klanten wereldwijd. Hoewel hij geniet van de intellectuele uitdaging van het oplossen van problemen met werkbladfuncties, beweert Roger intrinsiek lui te zijn, dus hij zoekt altijd naar een snelle en eenvoudige manier om solide werkbare oplossingen te bieden.

Vind meer van Roger’s tutorials en voorbeeldbestanden hier: Sample Files – Roger Govier

U kunt Roger bereiken op: [email protected]

Roger Govier