Werken met draaitabellen in Microsoft Excel

PivotTables zijn een van de krachtigste functies van Microsoft Excel. Hiermee kunnen grote hoeveelheden gegevens worden geanalyseerd en samengevat in slechts enkele muisklikken. In dit artikel verkennen we draaitabellen, begrijpen wat ze zijn, en leren ze maken en aanpassen.

Opmerking: dit artikel is geschreven met Excel 2010 (bèta). Het concept van een draaitabel is in de loop der jaren weinig veranderd, maar de methode om er een te maken is in bijna elke iteratie van Excel veranderd. Als u een versie van Excel gebruikt die niet in 2010 is, kunt u verschillende schermen verwachten die u in dit artikel ziet.

Een beetje geschiedenis

In de begintijd van spreadsheetprogramma's heeft Lotus 1-2-3 de roost bepaald. De dominantie was zo compleet dat mensen dachten dat het tijdverspilling was voor Microsoft om moeite te doen om hun eigen spreadsheet-software (Excel) te ontwikkelen om te concurreren met Lotus. Flash-forward naar 2010, en Excel's dominantie van de spreadsheet-markt is groter dan die van Lotus ooit was, terwijl het aantal gebruikers dat Lotus 1-2-3 draait nul benadert. Hoe is dit gebeurd? Wat veroorzaakte zo'n dramatische ommekeer van fortuinen?

Volgens analisten van de industrie zijn dit twee factoren: ten eerste besloot Lotus dat dit mooie nieuwe GUI-platform genaamd "Windows" een voorbijgaande bevlieging was die nooit van de grond zou komen. Ze weigerden om een Windows-versie van Lotus 1-2-3 te maken (althans voor een paar jaar), en voorspelden dat hun DOS-versie van de software alles was wat iemand ooit nodig zou hebben. Microsoft heeft natuurlijk Excel exclusief voor Windows ontwikkeld. Ten tweede heeft Microsoft een functie voor Excel ontwikkeld die Lotus niet in 1-2-3 heeft verstrekt, namelijk Draaitabellen. De draaitabelfunctie, exclusief voor Excel, werd zo overweldigend nuttig geacht dat mensen bereid waren om een heel nieuw softwarepakket (Excel) te leren in plaats van vast te houden aan een programma (1-2-3) dat het niet had. Deze functie, samen met de verkeerde inschatting van het succes van Windows, was de doodsteek voor Lotus 1-2-3 en het begin van het succes van Microsoft Excel.

In staat zijn draaitabellen te begrijpen

Dus wat is een draaitabel precies??

Simpel gezegd, een draaitabel is een samenvatting van sommige gegevens, gemaakt om eenvoudige analyse van de gegevens mogelijk te maken. Maar in tegenstelling tot een handmatig gemaakte samenvatting zijn Excel PivotTables interactief. Als u er eenmaal een hebt gemaakt, kunt u deze eenvoudig wijzigen als deze niet exact inzicht biedt in uw gegevens waarnaar u op zoek was. Met een paar klikken kan de samenvatting worden "gedraaid" - zodanig geroteerd dat de kolomkoppen rijrubrieken worden en omgekeerd. Er kan nog veel meer worden gedaan. In plaats van te proberen alle functies van draaitabellen te beschrijven, zullen we ze eenvoudig demonstreren ...

De gegevens die u analyseert met behulp van een draaitabel, kunnen niet zomaar zijn ieder data - het moet zijn rauw gegevens, voorheen onbewerkt (niet-gepolariseerd) - meestal een lijst van een soort. Een voorbeeld hiervan is de lijst met verkooptransacties in een bedrijf in de afgelopen zes maanden.

Bestudeer de gegevens hieronder:

Merk op dat dit zo is niet ruwe data. In feite is het al een soort samenvatting. In cel B3 zien we $ 30.000, wat blijkbaar het totaal is van de verkoop van James Cook voor de maand januari. Dus waar zijn de onbewerkte gegevens? Hoe kwamen we aan het cijfer van $ 30.000? Waar is de oorspronkelijke lijst met verkooptransacties waarvoor dit cijfer is gegenereerd? Het is duidelijk dat ergens iemand de moeite heeft genomen om alle verkooptransacties van de afgelopen zes maanden samen te vatten in de samenvatting die we hierboven zien. Hoe lang denk je dat dit duurde? Een uur? Tien?

Waarschijnlijk wel, ja. Zie je, de bovenstaande spreadsheet is eigenlijk niet een draaitabel. Het is handmatig gemaakt van onbewerkte gegevens die elders zijn opgeslagen en het heeft inderdaad een paar uur geduurd om te compileren. Het is echter precies het soort samenvatting dat kon worden gemaakt met behulp van draaitabellen, in welk geval het slechts een paar seconden zou hebben geduurd. Laten we kijken hoe ...

Als we de oorspronkelijke lijst met verkooptransacties zouden opsporen, zou het er ongeveer zo uit kunnen zien:

U zult er misschien versteld van staan dat we, met behulp van de draaitabelfunctie van Excel, een maandelijkse verkoopsamenvatting kunnen maken die lijkt op de bovenstaande in enkele seconden, met slechts een paar muisklikken. We kunnen dit - en nog veel meer ook!

Hoe maak je een draaitabel aan?

Zorg eerst dat u enkele onbewerkte gegevens in een werkblad in Excel hebt staan. Een lijst met financiële transacties is typisch, maar het kan een lijst zijn met zo ongeveer alles: contactgegevens voor werknemers, uw verzameling CD's of cijfers voor het brandstofverbruik van het wagenpark van uw bedrijf.

Dus we starten Excel ... en we laden zo'n lijst ...

Zodra de lijst in Excel is geopend, zijn we klaar om de draaitabel te maken.

Klik op een enkele cel binnen de lijst:

Dan, van de invoegen tab, klik op de draaitabel icoon:

De Maak draaitabel Er verschijnt een venster met twee vragen: op welke gegevens moet uw nieuwe draaitabel zijn gebaseerd en waar moet deze worden gemaakt? Omdat we al op een cel in de lijst hebben geklikt (in de stap hierboven), is de volledige lijst rond die cel al voor ons geselecteerd ($ A $ 1: $ G $ 88 op de betalingen blad, in dit voorbeeld). Houd er rekening mee dat we een lijst kunnen selecteren in een andere regio van een ander werkblad, of zelfs een externe gegevensbron, zoals een Access-databasetabel of zelfs een MS-SQL Server-databasetabel. We moeten ook selecteren of we willen dat onze nieuwe draaitabel wordt gemaakt op a nieuwe werkblad, of op een bestaand een. In dit voorbeeld selecteren we een nieuwe een:

Het nieuwe werkblad is voor ons gemaakt en er is een lege draaitabel gemaakt op dat werkblad:

Er verschijnt ook een ander vak: het PivotTable Field List. Deze veldlijst wordt getoond wanneer we op een cel in de draaitabel klikken (hierboven):

De lijst met velden in het bovenste deel van het vak is eigenlijk de verzameling kolomkoppen uit het oorspronkelijke onbewerkte gegevenswerkblad. De vier lege vakken in het onderste deel van het scherm stellen ons in staat om de manier te kiezen waarop we willen dat onze draaitabel de onbewerkte gegevens samenvat. Tot dusver is er niets in die vakken, dus de draaitabel is leeg. Het enige dat we moeten doen is de velden naar beneden slepen uit de bovenstaande lijst en ze in de onderste vakken neerzetten. Vervolgens wordt automatisch een draaitabel gemaakt die overeenkomt met onze instructies. Als we het verkeerd hebben, hoeven we de velden alleen maar terug te slepen naar waar ze vandaan kwamen en / of te slepen nieuwe naar beneden om ze te vervangen.

De waarden Box is misschien wel de belangrijkste van de vier. Het veld dat naar dit vak wordt gesleept, vertegenwoordigt de gegevens die op de een of andere manier moeten worden samengevat (door optellen, middelen, het vinden van het maximum, minimum, enz.). Het is bijna altijd numeriek gegevens. Een perfecte kandidaat voor deze box in onze voorbeeldgegevens is het veld "Bedrag" / kolom. Laten we dat veld naar de slepen waarden doos:

Merk op dat (a) het veld "Bedrag" in de lijst met velden nu is aangevinkt, en "Som van Bedrag" is toegevoegd aan de waarden kader, wat aangeeft dat de kolom van het bedrag is gesommeerd.

Als we de draaitabel zelf bekijken, vinden we inderdaad de som van alle "Bedrag" -waarden van het onbewerkte gegevensblad:

We hebben onze eerste draaitabel gemaakt! Handig, maar niet bijzonder indrukwekkend. Het is waarschijnlijk dat we wat meer inzicht in onze gegevens nodig hebben dan dat.

Verwijzend naar onze voorbeeldgegevens, moeten we een of meer kolomkoppen identificeren die we zouden kunnen gebruiken om dit totaal te splitsen. We kunnen bijvoorbeeld besluiten dat we graag een samenvatting van onze gegevens willen zien waar we een hebben rij kop voor elk van de verschillende verkopers in ons bedrijf, en een totaal voor elk. Om dit te bereiken, hoeven we alleen maar het veld 'Verkoper' naar de map te slepen Rijlabels doos:

Nu, eindelijk beginnen dingen interessant te worden! Onze draaitabel begint vorm te krijgen ... .

Met een paar klikken hebben we een tabel gemaakt die veel tijd kost om handmatig te doen.

Dus wat kunnen we nog meer doen? Welnu, in zekere zin is onze draaitabel compleet. We hebben een nuttige samenvatting van onze brongegevens gemaakt. De belangrijke dingen zijn al geleerd! Voor de rest van het artikel zullen we enkele manieren onderzoeken waarop complexere draaitabellen kunnen worden gemaakt en manieren waarop die draaitabellen kunnen worden aangepast.

Ten eerste kunnen we een maken twee-dimensionale tafel. Laten we dat doen door "Betalingsmethode" als kolomkop te gebruiken. Sleep de kop 'Betalingsmethode' naar de Kolometiketten doos:

Die ziet er zo uit:

Beginnen te krijgen heel stoer!

Laten we er een maken drie-dimensionale tafel. Hoe zou zo'n tafel eruit kunnen zien? Goed, laten we even kijken…

Sleep de kolom / kop "Pakket" naar de Rapportfilter doos:

Merk op waar het eindigt ... .

Hiermee kunnen we ons rapport filteren op basis van welk "vakantiepakket" werd gekocht. We kunnen bijvoorbeeld de uitsplitsing van verkoper vs. betalingsmethode voor zien allemaal pakketten, of, met een paar klikken, verander het om dezelfde uitsplitsing voor het pakket "Sunseekers" te tonen:

En dus, als u er op de juiste manier over nadenkt, is onze draaitabel nu driedimensionaal. Laten we blijven aanpassen ...

Als het bijvoorbeeld blijkt dat we alleen willen zien controleren en creditcard transacties (d.w.z. geen contante transacties), dan kunnen we het "Cash" -item uit de kolomkoppen deselecteren. Klik op de vervolgkeuzepijl naast Kolometiketten, en vink "Cash" af:

Laten we eens kijken hoe dat eruit ziet ... Zoals u kunt zien, is "Cash" verdwenen.

opmaak

Dit is duidelijk een zeer krachtig systeem, maar tot nu toe zien de resultaten er heel duidelijk en saai uit. Om te beginnen lijken de cijfers die we optellen niet op dollarbedragen - gewoon oude cijfers. Laten we dat rechtzetten.

Een verleiding kan zijn om te doen wat we gewend zijn in dergelijke omstandigheden en gewoon de hele tabel (of het hele werkblad) selecteren en de standaardknoppen voor nummeropmaak op de werkbalk gebruiken om het formatteren te voltooien. Het probleem met die benadering is dat als je ooit de structuur van de draaitabel in de toekomst verandert (wat voor 99% waarschijnlijk is), die getalnotaties dan verloren zullen gaan. We hebben een manier nodig die ze (semi-) permanent maakt.

Eerst zoeken we het item "Som van Bedrag" in de waarden kader en klik erop. Er verschijnt een menu. We selecteren Waarde veld instellingen ... uit het menu:

De Waarde veldinstellingen box verschijnt.

Klik op de Nummer formaat knop en de standaard Formaat cellenvak komt naar voren:

Van de Categorie lijst, selecteer (zeg) Accounting, en zet het aantal decimalen op 0. Klik OK een paar keer om terug te keren naar de draaitabel ...

Zoals u kunt zien, zijn de cijfers correct geformuleerd als dollarbedragen.

Hoewel we het over opmaak hebben, laten we de volledige draaitabel opmaken. Er zijn een paar manieren om dit te doen. Laten we een eenvoudige gebruiken ...

Klik op de Draaitabel gereedschappen / ontwerp tab:

Laat vervolgens de pijl rechtsonder in de pijl naar beneden vallen Draaitabelstijlen lijst om een uitgebreide verzameling ingebouwde stijlen te zien:

Kies degene die u aanspreekt en bekijk het resultaat in uw draaitabel:

Andere opties

We kunnen ook werken met datums. Meestal zijn er veel, vele datums in een transactielijst zoals die waarmee we zijn begonnen. Maar Excel biedt de mogelijkheid om gegevensitems samen te groeperen op dag, week, maand, jaar, enz. Laten we eens kijken hoe dit wordt gedaan.

Laten we eerst de kolom 'Betalingsmethode' verwijderen uit de Kolometiketten in het vak (sleep het eenvoudigweg terug naar de lijst met velden) en vervang het door de kolom "Date Booked":

Zoals u kunt zien, maakt dit onze draaitabel onmiddellijk nutteloos, waardoor we één kolom krijgen voor elke datum waarop een transactie plaatsvond - een zeer brede tafel!

Om dit op te lossen, klikt u met de rechtermuisknop op een datum en selecteert u Groep… uit het context-menu:

Het groeperingsvak wordt weergegeven. We selecteren Maanden en klik op OK:

Voila! EEN veel nuttiger tabel:

(Overigens is deze tabel vrijwel identiek aan de tabel aan het begin van dit artikel - de originele verkoopsamenvatting die handmatig is gemaakt.)

Een ander cool ding om op te letten, is dat je meer dan één rij koppen (of kolomkoppen) kunt hebben:

... die er zo uitziet ... .

U kunt iets soortgelijks doen met kolomkoppen (of zelfs rapportfilters).

Om de dingen weer eenvoudig te houden, laten we zien hoe we het kunnen plotten gemiddeld waarden, in plaats van gesommeerde waarden.

Klik eerst op "Som van bedrag" en selecteer Waarde veld instellingen ... uit het context-menu dat verschijnt:

In de Vat het waardeveld samen met lijst in de Waarde veldinstellingen vak, selecteer Gemiddelde:

Terwijl we hier zijn, laten we de Aangepaste naam, van "Average of Amount" naar iets beknopter. Typ zoiets als "Gem.":

Klik OK, en zie hoe het eruit ziet. Merk op dat alle waarden veranderen van gesommeerde totalen in gemiddelden en dat de tabeltitel (cel linksboven) is gewijzigd in "Gem.":

Als we willen, kunnen we zelfs sommen, gemiddelden en tellingen hebben (telt mee = hoeveel verkopen zijn er) allemaal op dezelfde draaitabel!

Hier zijn de stappen om zoiets op zijn plaats te krijgen (uitgaande van een lege PivotTable):

Sleep 'Verkoper' naar de Kolometiketten
Sleep het veld "Bedrag" naar beneden in de waarden vak drie keer
Voor het eerste veld "Bedrag" wijzigt u de aangepaste naam in "Totaal" en de getalnotatie in Accounting (0 decimalen)
Voor het tweede veld "Bedrag", wijzigt u de aangepaste naam in "Gemiddeld", de functie ervan Gemiddelde en het is het nummerformaat om Accounting (0 decimalen)
Verander voor het derde veld "Bedrag" de naam in "Tellen" en zijn functie in tellen
Sleep de automatisch aangemaakt veld uit Kolometiketten naar Rijlabels

Dit is wat we eindigen met:

Totaal, gemiddelde en telling op dezelfde draaitabel!

Conclusie

Er zijn veel, veel meer functies en opties voor draaitabellen gemaakt door Microsoft Excel - veel te veel om op te nemen in een artikel als dit. Om het potentieel van PivotTables volledig te dekken, zou een klein boek (of een grote website) nodig zijn. Dappere en / of geekige lezers kunnen PivotTables vrij eenvoudig verder verkennen: klik gewoon met de rechtermuisknop op zowat alles en zie welke opties voor u beschikbaar worden. Er zijn ook de twee linttabs: Draaitabel gereedschappen / opties en Ontwerp. Het maakt niet uit of je een fout maakt - het is gemakkelijk om de draaitabel te verwijderen en opnieuw te beginnen - een mogelijkheid die oude DOS-gebruikers van Lotus 1-2-3 nooit hadden.

Als u in Office 2007 werkt, kunt u ons artikel over het maken van een draaitabel in Excel 2007 bekijken.

We hebben een Excel-werkmap toegevoegd die u kunt downloaden om uw draaitabelvaardigheden te oefenen. Het zou met alle versies van Excel vanaf 97 moeten werken.

Download onze praktische Excel-werkmap