Inhoud
Draaitabellen is een van de krachtigste tools in Excel. Hiermee kunt u met slechts enkele muisklikken verschillende samenvattingen van grote hoeveelheden gegevens analyseren en samenvatten. In dit artikel maken we kennis met draaitabellen, begrijpen we wat ze zijn, leren we ze maken en aanpassen.
Dit artikel is geschreven met Excel 2010. Het concept van draaitabellen is in de loop der jaren niet veel veranderd, maar de manier waarop u ze maakt, verschilt enigszins in elke nieuwe versie van Excel. Als u een versie van Excel heeft die niet 2010 is, moet u er rekening mee houden dat de schermafbeeldingen in dit artikel afwijken van wat u op uw scherm ziet.
Een beetje geschiedenis
In de begindagen van spreadsheetsoftware, de Lotus 1-2-3-regelbal. Zijn dominantie was zo volledig dat de inspanningen van Microsoft om zijn eigen software (Excel) te ontwikkelen als alternatief voor Lotus tijdverspilling leken. Nu snel vooruit naar 2010! Excel domineert spreadsheets meer dan Lotus-code ooit in zijn geschiedenis heeft gedaan, en het aantal mensen dat Lotus nog steeds gebruikt, is bijna nul. Hoe kon dit gebeuren? Wat was de reden voor zo'n dramatische wending?
Analisten identificeren twee hoofdfactoren:
- Ten eerste besloot Lotus dat dit nieuwerwetse GUI-platform genaamd Windows slechts een voorbijgaande rage was die niet lang zou duren. Ze weigerden een Windows-versie van Lotus 1-2-3 te bouwen (maar slechts voor een paar jaar), en voorspelden dat de DOS-versie van hun software alles zou zijn wat consumenten ooit nodig zouden hebben. Microsoft heeft Excel natuurlijk speciaal voor Windows ontwikkeld.
- Ten tweede introduceerde Microsoft een tool in Excel genaamd PivotTables die niet beschikbaar was in Lotus 1-2-3. Draaitabellen, exclusief voor Excel, bleken zo overweldigend nuttig te zijn dat mensen de neiging hadden om bij de nieuwe Excel-softwaresuite te blijven in plaats van door te gaan met Lotus 1-2-3, die ze niet had.
Draaitabellen, samen met het onderschatten van het succes van Windows in het algemeen, speelden de dodenmars voor Lotus 1-2-3 en luidden het succes van Microsoft Excel in.
Wat zijn draaitabellen?
Dus, wat is de beste manier om te karakteriseren wat draaitabellen zijn?
In eenvoudige bewoordingen zijn draaitabellen samenvattingen van sommige gegevens, gemaakt om de analyse van deze gegevens te vergemakkelijken. In tegenstelling tot handmatig gemaakte totalen, zijn Excel-draaitabellen interactief. Eenmaal gemaakt, kunt u ze gemakkelijk wijzigen als ze niet de afbeelding geven waarop u hoopte. Met slechts een paar muisklikken kunnen totalen worden omgedraaid, zodat kolomkoppen rijkoppen worden en vice versa. Je kunt veel doen met draaitabellen. In plaats van te proberen alle functies van draaitabellen in woorden te beschrijven, is het gemakkelijker om het in de praktijk te demonstreren …
De gegevens die u analyseert met draaitabellen kunnen niet willekeurig zijn. Het zouden onbewerkte onbewerkte gegevens moeten zijn, zoals een soort lijst. Het kan bijvoorbeeld een lijst zijn met verkopen die het bedrijf in de afgelopen zes maanden heeft gedaan.
Kijk naar de gegevens in de onderstaande afbeelding:
Merk op dat dit geen ruwe onbewerkte gegevens zijn, zoals het al is samengevat. In cel B3 zien we $ 30000, wat waarschijnlijk het totale resultaat is dat James Cook in januari deed. Waar zijn de originele gegevens dan? Waar komt het bedrag van $ 30000 vandaan? Waar is de originele lijst met verkopen waarvan dit maandtotaal is afgeleid? Het is duidelijk dat iemand geweldig werk heeft verricht door alle verkoopgegevens van de afgelopen zes maanden te ordenen en te sorteren en deze om te zetten in de tabel met totalen die we zien. Hoe lang denk je dat het heeft geduurd? Uur? Tien uur?
Feit is dat bovenstaande tabel geen draaitabel is. Het is met de hand gemaakt van onbewerkte gegevens die elders zijn opgeslagen en het duurde minstens een paar uur om te verwerken. Zo'n overzichtstabel kan in slechts enkele seconden worden gemaakt met behulp van draaitabellen. Laten we uitzoeken hoe…
Als we teruggaan naar de oorspronkelijke verkooplijst, ziet deze er ongeveer zo uit:
Het zal je misschien verbazen dat we uit deze lijst met transacties met behulp van draaitabellen en in slechts een paar seconden een maandelijks verkooprapport in Excel kunnen maken, dat we hierboven hebben geanalyseerd. Ja, dat en meer kunnen wij!
Hoe maak je een draaitabel?
Zorg er eerst voor dat u enkele brongegevens in een Excel-blad hebt staan. De lijst met financiële transacties is de meest typische die voorkomt. In feite kan het een lijst van alles zijn: contactgegevens van werknemers, een cd-verzameling of de gegevens over het brandstofverbruik van uw bedrijf.
Dus we starten Excel ... en laden zo'n lijst ...
Nadat we deze lijst in Excel hebben geopend, kunnen we beginnen met het maken van een draaitabel.
Selecteer een cel in deze lijst:
Dan op het tabblad Invoeging (Invoegen) selecteer commando Draaitabel (draaitabel):
Er verschijnt een dialoogvenster Maak een draaitabel (Een draaitabel maken) met twee vragen voor u:
- Welke gegevens te gebruiken om een nieuwe draaitabel te maken?
- Waar plaats je de draaitabel?
Aangezien we in de vorige stap al een van de lijstcellen hebben geselecteerd, wordt de hele lijst automatisch geselecteerd om een draaitabel te maken. Merk op dat we een ander bereik, een andere tabel en zelfs een externe gegevensbron kunnen selecteren, zoals een Access- of MS-SQL-databasetabel. Bovendien moeten we kiezen waar we de nieuwe draaitabel willen plaatsen: op een nieuw blad of op een van de bestaande. In dit voorbeeld zullen we de optie kiezen - Nieuw werkblad (naar een nieuw blad):
Excel maakt een nieuw blad en plaatst er een lege draaitabel op:
Zodra we op een cel in de draaitabel klikken, verschijnt er een ander dialoogvenster: Lijst met draaitabelvelden (Pivottabelvelden).
De lijst met velden boven aan het dialoogvenster is een lijst met alle titels uit de oorspronkelijke lijst. In de vier lege gebieden onder aan het scherm kunt u de draaitabel vertellen hoe u de gegevens wilt samenvatten. Zolang deze velden leeg zijn, staat er ook niets in de tabel. Het enige wat we hoeven te doen is de koppen van het bovenste gedeelte naar de lege gedeelten eronder te slepen. Tegelijkertijd wordt er automatisch een draaitabel gegenereerd, in overeenstemming met onze instructies. Als we een fout maken, kunnen we de koppen uit het onderste gedeelte verwijderen of andere slepen om ze te vervangen.
De Omgeving Values (Betekenissen) is waarschijnlijk de belangrijkste van de vier. Welke kop in dit gebied wordt geplaatst, bepaalt welke gegevens worden samengevat (som, gemiddelde, maximum, minimum, enz.). Dit zijn bijna altijd numerieke waarden. Een uitstekende kandidaat voor een plek in dit gebied zijn de gegevens onder het kopje Hoeveelheid (Kosten) van onze originele tafel. Sleep deze titel naar het gebied Values (Waarden):
Houd er rekening mee dat de titel Hoeveelheid is nu gemarkeerd met een vinkje, en in het gebied Values (Waarden) er is een item verschenen Som van bedrag (Bedrag veld Bedrag), wat aangeeft dat de kolom Hoeveelheid opgesomd.
Als we naar de draaitabel zelf kijken, zien we de som van alle waarden uit de kolom Hoeveelheid originele tafel.
Dus onze eerste draaitabel is gemaakt! Handig, maar niet bijzonder indrukwekkend. We willen waarschijnlijk meer informatie over onze gegevens dan we momenteel hebben.
Laten we kijken naar de oorspronkelijke gegevens en proberen een of meer kolommen te identificeren die kunnen worden gebruikt om deze som te splitsen. We kunnen onze draaitabel bijvoorbeeld zo vormen dat het totale aantal verkopen voor elke verkoper afzonderlijk wordt berekend. Die. rijen worden toegevoegd aan onze draaitabel met de naam van elke verkoper in het bedrijf en hun totale verkoopbedrag. Om dit resultaat te bereiken, sleept u gewoon de titel verkoper (vertegenwoordiger) naar de regio Rijlabels (Snaren):
Het wordt interessanter! Onze draaitabel begint vorm te krijgen...
Zie je de voordelen? Met een paar klikken hebben we een tabel gemaakt die erg lang zou duren om handmatig te maken.
Wat anders kunnen we doen? In zekere zin is onze draaitabel klaar. We hebben een handige samenvatting gemaakt van de originele gegevens. Belangrijke informatie reeds ontvangen! In de rest van dit artikel bekijken we enkele manieren om complexere draaitabellen te maken en leren we hoe u deze kunt aanpassen.
Draaitabel instellen
Ten eerste kunnen we een tweedimensionale draaitabel maken. Laten we dit doen met behulp van de kolomkop Betaalmethode (Betalingswijze). Sleep gewoon de titel Betaalmethode naar het gebied Kolomlabels (kolommen):
We krijgen het resultaat:
Ziet er erg gaaf uit!
Laten we nu een driedimensionale tabel maken. Hoe zou zo'n tafel eruit zien? Laten we zien…
Koptekst slepen Pakket (Complex) naar het gebied rapportfilters (Filteren):
Let op waar hij is...
Dit geeft ons de mogelijkheid om de rapportage te filteren op basis van “Welk vakantiecomplex is betaald”. We kunnen bijvoorbeeld een uitsplitsing per verkopers en betalingsmethoden voor alle complexen zien, of in een paar muisklikken, de weergave van de draaitabel wijzigen en dezelfde uitsplitsing alleen weergeven voor degenen die het complex hebben besteld Zonzoekers.
Dus als u dit goed begrijpt, kan onze draaitabel driedimensionaal worden genoemd. Laten we doorgaan met het instellen...
Als plotseling blijkt dat alleen betaling per cheque en creditcard (dat wil zeggen contante betaling) in de draaitabel moet worden weergegeven, kunnen we de weergave van de titel uitschakelen Contant geld (Contant geld). Hiervoor, naast Kolomlabels klik op de pijl-omlaag en schakel het selectievakje in het vervolgkeuzemenu uit Contant geld:
Laten we eens kijken hoe onze draaitabel er nu uitziet. Zoals je kunt zien, is de kolom Contant geld van haar verdwenen.
Draaitabellen opmaken in Excel
Draaitabellen zijn natuurlijk een zeer krachtig hulpmiddel, maar tot nu toe zien de resultaten er een beetje saai en saai uit. De getallen die we bij elkaar optellen, zien er bijvoorbeeld niet uit als bedragen in dollars - het zijn slechts getallen. Laten we dit oplossen.
Het is verleidelijk om in zo'n situatie te doen wat je gewend bent en gewoon de hele tabel (of het hele blad) te selecteren en de standaard cijferopmaakknoppen op de werkbalk te gebruiken om het gewenste formaat in te stellen. Het probleem met deze aanpak is dat als je ooit de structuur van de draaitabel in de toekomst verandert (wat gebeurt met een kans van 99%), de opmaak verloren gaat. Wat we nodig hebben is een manier om het (bijna) permanent te maken.
Laten we eerst het item vinden Som van bedrag in Values (Waarden) en klik erop. Selecteer in het menu dat verschijnt het item Waarde Veldinstellingen (Waardeveld opties):
Er verschijnt een dialoogvenster Waarde Veldinstellingen (Waardeveldopties).
Klik op de knop nummer Formaat (Getalnotatie), wordt een dialoogvenster geopend. Cellen opmaken (celformaat):
Van de lijst Categorie (Nummernotaties) selecteer Accounting (Financieel) en zet het aantal decimalen op nul. Druk nu een paar keer op OKom terug te gaan naar onze draaitabel.
Zoals u kunt zien, zijn de getallen opgemaakt als dollarbedragen.
Nu we toch bezig zijn met opmaak, laten we de opmaak voor de hele draaitabel instellen. Er zijn verschillende manieren om dit te doen. We gebruiken degene die eenvoudiger is ...
Klik op de Draaitabelhulpmiddelen: Ontwerp (Werken met draaitabellen: Constructor):
Vouw vervolgens het menu uit door op de pijl in de rechterbenedenhoek van de sectie te klikken Draaitabelstijlen (PivotTable Styles) om de uitgebreide collectie inline-stijlen te zien:
Kies een geschikte stijl en bekijk het resultaat in je draaitabel:
Andere draaitabelinstellingen in Excel
Soms moet u gegevens op datum filteren. In onze lijst met transacties staan bijvoorbeeld heel veel datums. Excel biedt een hulpmiddel om gegevens te groeperen op dag, maand, jaar, enzovoort. Laten we eens kijken hoe het gedaan is.
Verwijder eerst de invoer. Betaalmethode uit de regio Kolomlabels (kolommen). Om dit te doen, sleept u het terug naar de lijst met titels en verplaatst u in plaats daarvan de titel Datum geboekt (datum boeking):
Zoals u kunt zien, heeft dit onze draaitabel tijdelijk onbruikbaar gemaakt. Excel heeft een aparte kolom gemaakt voor elke datum waarop een transactie is gedaan. Daardoor kregen we een hele brede tafel!
Om dit op te lossen, klikt u met de rechtermuisknop op een datum en selecteert u in het contextmenu GROEP (Groep):
Het groeperingsdialoogvenster verschijnt. We kiezen Maanden (Maanden) en klik op OK:
Voila! Deze tabel is veel handiger:
Trouwens, deze tabel is bijna identiek aan die aan het begin van het artikel, waar de verkooptotalen handmatig werden samengesteld.
Er is nog een heel belangrijk punt dat u moet weten! U kunt niet één, maar meerdere niveaus van rij- (of kolomkoppen) maken:
… en het zal er zo uitzien …
Hetzelfde kan worden gedaan met kolomkoppen (of zelfs filters).
Laten we terugkeren naar de oorspronkelijke vorm van de tabel en kijken hoe we gemiddelden kunnen weergeven in plaats van sommen.
Om te beginnen, klik op Som van bedrag en selecteer in het menu dat verschijnt Waarde Veldinstellingen (Waardeveld opties):
De lijst Vat het waardeveld samen met (Bewerking) in het dialoogvenster Waarde Veldinstellingen (Opties voor waardeveld) selecteer Gemiddelde (Gemiddeld):
Laten we tegelijkertijd, terwijl we hier zijn, veranderen Aangepaste naam (Aangepaste naam) met Gemiddeld bedrag (Bedrag veld Bedrag) naar iets korter. Voer in dit veld iets in als Gem:
Media OK en kijk wat er gebeurt. Merk op dat alle waarden zijn gewijzigd van totalen in gemiddelden en dat de tabelkop (in de cel linksboven) is gewijzigd in Gem:
Als je wilt, kun je direct het bedrag, het gemiddelde en het aantal (verkopen) in één draaitabel krijgen.
Hier is een stapsgewijze handleiding over hoe u dit kunt doen, te beginnen met een lege draaitabel:
- Koptekst slepen verkoper (vertegenwoordiger) naar de regio Kolomlabels (kolommen).
- Sleep de titel drie keer Hoeveelheid (Kosten) naar gebied Values (Waarden).
- Voor het eerste veld Hoeveelheid verander de titel in Totaal (Bedrag), en de getalnotatie in dit veld is Accounting (financieel). Het aantal decimalen is nul.
- Tweede veld Hoeveelheid naam gemiddelde, stel de bewerking ervoor in Gemiddelde (Gemiddelde) en de getalnotatie in dit veld veranderen ook in Accounting (Financieel) met nul decimalen.
- Voor het derde veld Hoeveelheid stel een titel in Tellen en een operatie voor hem - Tellen (Aantal stuks)
- In het Kolomlabels (Kolommen) veld automatisch aangemaakt Σ Waarden (Σ Waarden) – sleep het naar het gebied Rijlabels (Lijnen)
Dit is wat we zullen eindigen:
Totaalbedrag, gemiddelde waarde en aantal verkopen - alles in één draaitabel!
Conclusie
Draaitabellen in Microsoft Excel bevatten veel functies en instellingen. In zo'n klein artikel komen ze niet eens in de buurt om ze allemaal te dekken. Er is een klein boek of een grote website voor nodig om alle mogelijkheden van draaitabellen volledig te beschrijven. Gedurfde en nieuwsgierige lezers kunnen hun verkenning van draaitabellen voortzetten. Om dit te doen, klikt u met de rechtermuisknop op bijna elk element van de draaitabel en ziet u welke functies en instellingen worden geopend. Op het lint vind je twee tabbladen: Hulpmiddelen voor draaitabellen: Opties (analyse) en Design (Constructeur). Wees niet bang om een fout te maken, u kunt de draaitabel altijd verwijderen en opnieuw beginnen. Je hebt een kans die lange tijd gebruikers van DOS en Lotus 1-2-3 nooit hebben gehad.