Excel-draaitabellen zijn geweldig (ik weet dat ik dit elke keer vermeld als ik over draaitabellen schrijf, maar het is waar).
Met een basiskennis en een beetje slepen en neerzetten, kunt u in een paar seconden een hoop werk gedaan krijgen.
Hoewel er veel kan worden gedaan met een paar klikken in draaitabellen, zijn er enkele dingen die een paar extra stappen of een beetje werk vergen.
En zo'n ding is om verschillende waarden in een draaitabel te tellen.
In deze zelfstudie laat ik u zien hoe u verschillende waarden en unieke waarden in een Excel-draaitabel kunt tellen.
Maar voordat ik inga op het tellen van verschillende waarden, is het belangrijk om het verschil te begrijpen tussen 'verschillende telling' en 'unieke telling'
Verschillende telling versus unieke telling
Hoewel deze misschien hetzelfde lijken, het is niet.
Hieronder ziet u een voorbeeld van een dataset met namen en ik heb unieke en verschillende namen afzonderlijk vermeld.
Unieke waarden/namen zijn die maar één keer voorkomen. Dit betekent dat alle namen die worden herhaald en dubbele namen hebben, niet uniek zijn. Unieke namen worden vermeld in kolom C in de bovenstaande dataset
Verschillende waarden/namen zijn die ten minste één keer in de dataset voorkomen. Dus als een naam drie keer voorkomt, wordt deze nog steeds geteld als één afzonderlijke naam. Dit kan worden bereikt door de dubbele waarden/namen te verwijderen en alle afzonderlijke te behouden. Verschillende namen worden vermeld in kolom B in de bovenstaande dataset.
Op basis van wat ik heb gezien, bedoelen mensen meestal wanneer ze zeggen dat ze de unieke telling in een draaitabel willen krijgen, eigenlijk een verschillende telling, wat ik in deze tutorial behandel.Tel verschillende waarden in Excel-draaitabel
Stel u heeft de verkoopgegevens zoals hieronder weergegeven:
Klik hier om het voorbeeldbestand te downloaden en volg mee
Stel dat u met de bovenstaande dataset het antwoord op de volgende vragen wilt vinden:
- Hoeveel verkopers zijn er in elke regio (wat niets anders is dan het aantal verkopers in elke regio)?
- Hoeveel verkopers hebben de printer verkocht in 2021-2022?
Hoewel draaitabellen de gegevens onmiddellijk met een paar klikken kunnen samenvatten, moet u nog een paar stappen nemen om het aantal verschillende waarden te krijgen.
Als je gebruikt Excel 2013 of versies daarna, er is een ingebouwde functionaliteit in draaitabel die u snel het duidelijke aantal geeft. En als je gebruikt Excel 2010 of eerdere versies, moet u de brongegevens wijzigen door een hulpkolom toe te voegen.De volgende twee methoden worden in deze zelfstudie behandeld:
- Een hulpkolom toevoegen aan de originele dataset om unieke waarden te tellen (werkt in alle versies).
- De gegevens toevoegen aan een gegevensmodel en de optie Distinct Count gebruiken (beschikbaar in Excel 2013 en latere versies).
Er is een derde methode die Roger in dit artikel laat zien (die hij de Pivot the Pivot Table-methode noemt).
Laten we beginnen!
Een helperkolom toevoegen aan de gegevensset
Opmerking: als u Excel 2013 en hogere versies gebruikt, slaat u deze methode over en gaat u naar de volgende (omdat deze een ingebouwde draaitabelfunctie gebruikt - Verschillende telling).Dit is een gemakkelijke manier om verschillende waarden in de draaitabel te tellen, aangezien u alleen een hulpkolom aan de brongegevens hoeft toe te voegen. Nadat u een hulpkolom hebt toegevoegd, kunt u deze nieuwe gegevensset gebruiken om de afzonderlijke telling te berekenen.
Hoewel dit een gemakkelijke oplossing is, zijn er enkele nadelen aan deze methode (die later in deze zelfstudie worden behandeld).
Laat me je eerst laten zien hoe je een helperkolom toevoegt en een duidelijke telling krijgt.
Stel dat ik de dataset heb zoals hieronder weergegeven:
Voeg de volgende formule toe in kolom F en pas deze toe op alle cellen met gegevens in de aangrenzende kolommen.
=ALS(AANTALLEN.ALS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)
De bovenstaande formule gebruikt de AANTAL.ALS-functie om het aantal keren te tellen dat een naam in de gegeven regio voorkomt. Merk ook op dat het criteriumbereik $C$2:C2 en $B$2:B2 is. Dit betekent dat het blijft uitzetten terwijl je door de kolom gaat.
In cel E2 zijn de criteriabereiken bijvoorbeeld $C$2:C2 en $B$2:B2 en in cel E3 worden deze bereiken uitgebreid tot $C$2:C3 en $B$2:B3.
Dit zorgt ervoor dat de functie AANTAL.ALS het eerste exemplaar van een naam telt als 1, het tweede exemplaar van de naam als 2, enzovoort.
Omdat we alleen de verschillende namen willen krijgen, wordt de ALS-functie gebruikt die 1 retourneert wanneer een naam voor de eerste keer voor een regio verschijnt en 0 retourneert wanneer deze opnieuw verschijnt. Dit zorgt ervoor dat alleen afzonderlijke namen worden geteld en niet de herhalingen.
Hieronder ziet u hoe uw dataset eruit zou zien als u de helperkolom hebt toegevoegd.
Nu we de brongegevens hebben gewijzigd, kunnen we deze gebruiken om een draaitabel te maken en de hulpkolom te gebruiken om het afzonderlijke aantal verkopers in elke regio te krijgen.
Hieronder staan de stappen om dit te doen:
- Selecteer een cel in de gegevensset.
- Klik op het tabblad Invoegen.
- Klik op draaitabel (of gebruik de sneltoets - ALT + N + V)
- Controleer in het dialoogvenster Draaitabel maken of de tabel/het bereik correct is (en de hulpkolom bevat) en 'Nieuw werkblad' is geselecteerd.
- Klik OK.
De bovenstaande stappen zouden een nieuw blad invoegen met de draaitabel.
Sleep het veld 'Regio' in het gebied Rijen en het veld 'D-telling' in het gebied Waarden.
U krijgt een draaitabel zoals hieronder weergegeven:
Nu kunt u de kolomkop wijzigen van 'Sum of D count' in 'Sales Rep'.
Nadelen van het gebruik van een helperkolom:
Hoewel deze methode vrij eenvoudig is, moet ik een paar nadelen benadrukken die gepaard gaan met het wijzigen van de brongegevens in een draaitabel:
- De gegevensbron met de hulpkolom is niet zo dynamisch als een draaitabel. Met een draaitabel kun je de gegevens op elke gewenste manier in stukjes snijden en in blokjes snijden, maar als je een hulpkolom gebruikt, verlies je een deel van die vaardigheid. Laten we zeggen dat u een hulpkolom toevoegt om het aantal verschillende verkopers in elke regio te krijgen. Nu, wat als u ook het duidelijke aantal verkopers wilt zien die printers verkopen. U moet teruggaan naar de brongegevens en de formule van de helperkolom wijzigen (of een nieuwe helperkolom toevoegen).
- Aangezien u meer gegevens toevoegt aan de draaitabelbron (die ook wordt toegevoegd aan de draaitabel), kan dit leiden tot een groter Excel-bestand.
- Omdat we een Excel-formule gebruiken, kan het uw Excel-werkmap traag maken als u duizenden rijen gegevens hebt.
Gegevens toevoegen aan gegevensmodel en samenvatten met behulp van verschillende tellingen
Draaitabel heeft nieuwe functionaliteit toegevoegd in Excel 2013 waarmee u de afzonderlijke telling kunt krijgen terwijl u de gegevensset samenvat.
Als u een eerdere versie gebruikt, kunt u deze methode niet gebruiken (zoals u zou moeten proberen de helperkolom toe te voegen zoals weergegeven in de methode hierboven).
Stel dat u een dataset heeft zoals hieronder weergegeven en dat u het aantal unieke verkopers in elke regio wilt zien.
Hieronder vindt u de stappen om een duidelijke telwaarde in de draaitabel te krijgen:
- Selecteer een cel in de gegevensset.
- Klik op het tabblad Invoegen.
- Klik op draaitabel (of gebruik de sneltoets - ALT + N + V)
- Controleer in het dialoogvenster Draaitabel maken of de tabel/het bereik correct is en het nieuwe werkblad is geselecteerd.
- Vink het vakje aan met de tekst - "Voeg deze gegevens toe aan het gegevensmodel"
- Klik OK.
De bovenstaande stappen zouden een nieuw blad invoegen met de nieuwe draaitabel.
Sleep de regio in het gebied Rijen en Verkoopvertegenwoordiger in het gebied Waarden. U krijgt een draaitabel zoals hieronder weergegeven:
De bovenstaande draaitabel geeft het totale aantal verkopers in elke regio (en niet het afzonderlijke aantal).
Volg de onderstaande stappen om de afzonderlijke telling in de draaitabel te krijgen:
- Klik met de rechtermuisknop op een cel in de kolom 'Aantal verkoopvertegenwoordigers'.
- Klik op Instellingen waardeveld
- Selecteer in het dialoogvenster Waardeveldinstellingen 'Afzonderlijke telling' als het type berekening (mogelijk moet u door de lijst bladeren om deze te vinden).
- Klik OK.
U zult merken dat de naam van de kolom verandert van 'Aantal verkoopvertegenwoordiger' in 'Verschillend aantal verkoopvertegenwoordigers'. Je kunt het veranderen in wat je maar wilt.
Enkele dingen die u weet wanneer u uw gegevens toevoegt aan het gegevensmodel:
- Als u uw gegevens opslaat in het gegevensmodel en vervolgens opent in een oudere versie van Excel, ziet u een waarschuwing - 'Sommige draaitabelfuncties worden niet opgeslagen'. Mogelijk ziet u de afzonderlijke telling (en het gegevensmodel) niet wanneer deze wordt geopend in een oudere versie die dit niet ondersteunt.
- Wanneer u uw gegevens toevoegt aan een gegevensmodel en een draaitabel maakt, worden niet de opties weergegeven om berekende velden en berekende kolommen toe te voegen.
Klik hier om het voorbeeldbestand te downloaden
Wat als u unieke waarden wilt tellen (en geen afzonderlijke waarden)?
Als u unieke waarden wilt tellen, heeft u geen ingebouwde functionaliteit in de draaitabel en hoeft u alleen op hulpkolommen te vertrouwen.
Onthoud - Unieke waarden en verschillende waarden zijn niet hetzelfde. Klik hier om het verschil te weten.Een voorbeeld kan zijn wanneer u de onderstaande gegevensset heeft en u wilt weten hoeveel verkopers uniek zijn voor elke regio. Dit betekent dat ze slechts in één specifieke regio actief zijn en niet in de andere.
In dergelijke gevallen moet u een of meer hulpkolommen maken.
Voor dit geval doet de onderstaande formule het:
=ALS(ALS(AANTALLEN.ALS($C$2:$C$1001,C2,$B$2:$B$1001,B2)/AANTAL.ALS($C$2:$C$1001,C2)1,0,1),0)
De bovenstaande formule controleert of de naam van een vertegenwoordiger slechts in één regio of in meer dan één regio voorkomt. Het doet dat door het aantal keren dat een naam in een regio voorkomt te tellen en dit te delen door het totale aantal keren dat de naam voorkomt. Als de waarde kleiner is dan 1, geeft dit aan dat de naam in twee of meer dan twee regio's voorkomt.
Als de naam in meer dan één regio voorkomt, wordt een 0 geretourneerd, anders wordt een één geretourneerd.
De formule controleert ook of de naam in dezelfde regio wordt herhaald of niet. Als de naam wordt herhaald, retourneert alleen het eerste exemplaar van de naam de waarde 1, en alle andere exemplaren retourneren 0.
Dit lijkt misschien een beetje ingewikkeld, maar het hangt weer af van wat je probeert te bereiken.
Dus als u unieke waarden in een draaitabel wilt tellen, gebruik dan hulpkolommen en als u afzonderlijke waarden wilt tellen, kunt u de ingebouwde functionaliteit gebruiken (in Excel 2013 en hoger) of een hulpkolom gebruiken.
Klik hier om het voorbeeldbestand te downloaden
Misschien vind je de volgende zelfstudies voor draaitabels misschien ook leuk:
- Gegevens filteren in een draaitabel in Excel
- Datums groeperen in draaitabellen in Excel
- Nummers groeperen in draaitabel in Excel
- Voorwaardelijke opmaak toepassen in een draaitabel in Excel
- Slicers in Excel-draaitabel
- Hoe de draaitabel in Excel te vernieuwen
- Een draaitabel in Excel verwijderen
