Draaitabellen Excel lessen

De draaitabel is misschien wel de meest bruikbaar gereedschap in Excel. Met zijn hulp, volop kansen voor het analyseren van grote hoeveelheden data en snelle berekeningen.

Wat zijn draaitabellen in Excel? Draaitabellen in Excel voor dummies

Draaitabellen zijn Excel-tool voor het samenvatten en analyseren van grote hoeveelheden gegevens.

Laten we zeggen dat we een tabel van 1000 regels hebben met verkopen per klant voor het jaar:

De tabel toont:

Datums van bestellingen;
De regio waar de opdrachtgever zich bevindt;
Klanttype;
Klant;
Aantal verkopen;
Omzet;
Winst.

Laten we ons nu voorstellen dat onze manager taken heeft ingesteld om te berekenen:

TOP vijf klanten naar omzet;

U kunt verschillende functies en formules gebruiken om het antwoord op deze vragen te vinden. Maar wat als er niet drie, maar dertig taken zijn op basis van deze gegevens? Elke keer moet je formules en functies wijzigen en aanpassen voor elk type berekening.

Dit is waar de draaitabel-tool voor u zal zijn. onvervangbare assistent... Met zijn hulp kunt u elke vraag binnen enkele seconden beantwoorden, volgens de gegevens uit de tabel.

Ik hoop dat je je in het bovenstaande voorbeeld realiseerde hoe nuttig en noodzakelijk een draaitabel is. Laten we eens kijken hoe we ze kunnen gebruiken.

Hoe maak je een draaitabel in Excel

Volg deze stappen om een draaitabel in Excel te maken:

Selecteer een willekeurige cel in de tabel met de gegevens op basis waarvan u een draaitabel wilt maken;
Klik op het tabblad “Invoegen” => “Pivot Table”:

In het pop-upvenster bepaalt het systeem automatisch de gegevensgrenzen op basis waarvan u een draaitabel kunt maken. Deze methode werkt in de meeste gevallen standaard. Ik raad aan om elke keer dat u een draaitabel maakt, ervoor te zorgen dat het systeem de parameters voor het maken ervan correct heeft gedefinieerd:
- Tabel of bereik: het systeem detecteert automatisch gegevensgrenzen om een draaitabel te maken. Ze zijn correct op voorwaarde dat er geen spaties in de koppen en regels in de tabel staan. U kunt het gegevensbereik naar wens aanpassen.
Het systeem maakt standaard een tabel aan in een nieuw tabblad van het Excel-bestand. Als u het op een specifieke plaats op een specifiek blad wilt maken, kunt u de te maken grenzen specificeren in de kolom "Op bestaand blad".

Klik OK".

Nadat u op de knop "OK" hebt geklikt, wordt de draaitabel gemaakt.

Nadat de draaitabel is gemaakt, ziet u geen gegevens op het blad. Het enige dat beschikbaar is, is de naam van de draaitabel en het menu voor het selecteren van gegevens om weer te geven.

Voordat we beginnen met het analyseren van de gegevens, stel ik voor om te begrijpen wat elk veld en gebied van de draaitabel betekent.

Draaitabelregio's in Excel

Om draaitabellen effectief te gebruiken, is het belangrijk om goed te weten hoe ze werken en waaruit deze bestaat.

Lees hieronder meer over de gebieden:

Cache draaitabel
Waarden gebied
Strings gebied
Kolommengebied
Filtergebied

Wat is draaitabelcache?

Wanneer u een draaitabel maakt, maakt Excel een cache (tussenliggende gegevensbuffer met snelle toegang met informatie die met de grootste waarschijnlijkheid kan worden opgevraagd) van de gegevens op basis waarvan de tabel zal worden opgebouwd.

Wanneer u berekeningen uitvoert op de gemaakte tabel, verwijst Excel niet elke keer naar de oorspronkelijke gegevens, maar gebruikt informatie uit de cache. Deze functie vermindert de hoeveelheid systeembronnen die worden besteed aan het verwerken en berekenen van gegevens aanzienlijk.

Het is belangrijk om te onthouden dat de gegevenscache de grootte van het Excel-bestand vergroot.

Waarden gebied

Het gebied "Waarden" bevat alle waarden van de tabel en kan worden weergegeven als het hoofdonderdeel van de draaitabel. Stel dat we de verkopen van regio's per maand willen weergeven (uit het voorbeeld aan het begin van het artikel) met behulp van een draaitabel. Gearceerde waarden geel in onderstaande afbeelding en zijn de waarden die we aangeven in draaitabel in het gebied Waarden.

In het bovenstaande voorbeeld hebben we een draaitabel gemaakt die verkoopgegevens per regio weergeeft, uitgesplitst per maand.

Strings gebied

Tabelkoppen links van de waarden zijn rijen. In ons voorbeeld zijn dit de namen van de regio's. In de onderstaande schermafbeelding zijn de lijnen rood gemarkeerd:

Kolommengebied

De koppen bovenaan de tabelwaarden worden "Kolommen" genoemd.

In onderstaand voorbeeld zijn de velden “Kolommen” rood gemarkeerd, in ons geval zijn dit de waarden van maanden.

Filtergebied

Het gebied "Filters" wordt optioneel gebruikt en stelt u in staat het detailniveau van de draaitabelgegevens in te stellen. We kunnen bijvoorbeeld de gegevens "Client type" specificeren als een filter - " Supermarkt”En Excel zal de gegevens in de tabel alleen voor supermarkten weergeven.

Draaitabellen in Excel. Voorbeelden van

In de onderstaande voorbeelden bekijken we hoe u draaitabellen gebruikt om de drie vragen aan het begin van dit artikel te beantwoorden:

Wat is het omzetvolume voor de regio Noord in 2017?;
TOP vijf klanten naar omzet;
Wat is de plaats van de Ludnikov IP-client in de Vostok-regio in termen van inkomsten?

Voordat u de gegevens analyseert, is het belangrijk om te beslissen hoe de tabelgegevens eruit moeten zien (welke gegevens moeten worden gemarkeerd in kolommen, rijen, waarden, filters). Als we bijvoorbeeld klantverkoopgegevens per regio moeten weergeven, moeten we de namen van regio's in rijen, maanden in kolommen, verkoopwaarden in het veld "Waarden" plaatsen. Zodra u een idee heeft van hoe u de uiteindelijke draaitabel ziet, begint u deze te maken.

Het venster "PivotTable Fields" bevat gebieden en velden met waarden die in de draaitabel moeten worden geplaatst:

De velden worden gemaakt op basis van de oorspronkelijke gegevens die voor de draaitabel zijn gebruikt. In het gedeelte Gebieden plaatst u de velden en afhankelijk van het gebied waarin het veld wordt geplaatst, worden uw gegevens bijgewerkt in de draaitabel.

Velden van regio naar regio verplaatsen is gebruiksvriendelijke interface waarin, terwijl u zich verplaatst, de gegevens in de draaitabel automatisch worden vernieuwd.

Laten we nu proberen de vragen van de manager vanaf het begin van dit artikel te beantwoorden met behulp van de onderstaande voorbeelden.

Voorbeeld 1. Hoeveel omzet heeft de regio Noord?

Om het verkoopvolume van de regio Noord te berekenen, stel ik voor om de verkoopgegevens van alle regio's in de draaitabel te plaatsen. Hiervoor hebben we nodig:

maak een draaitabel en verplaats het veld "Regio" naar het gebied "Rijen";
plaats het veld "Omzet" in het gebied "Waarden"

We zullen het antwoord krijgen: de verkoop van de regio Noord is 1 233 006 966 ₽:

Voorbeeld 2. TOP vijf klanten naar verkoop

verplaats in de draaitabel het veld "Klant" naar het gebied "Rijen";
plaats het veld "Omzet" in het gebied "Waarden";
stel een financiële getalnotatie in voor draaitabelcellen met waarden.

We hebben de volgende draaitabel:

Standaard sorteert het Excel-systeem de gegevens in de tabel in: alfabetische volgorde... Volg deze stappen om de gegevens op verkoopvolume te sorteren:

ga naar het menu "Sorteren" => "Sorteren aflopend":

Als gevolg hiervan krijgen we een gesorteerde lijst met klanten op omzet.

Voorbeeld 3. Welke plaats qua omzet neemt de individuele ondernemer-klant Ludnikov in de regio Vostok in?

Om de plaats te berekenen op basis van het omzetvolume van de klant Ludnikov IP in de regio Vostok, stel ik voor om een draaitabel te maken die omzetgegevens per regio en klant in deze regio weergeeft.

Voor deze:

plaats het draaitabelveld "Regio" in het gebied "Rijen";
zet het veld "Klant" in het gebied "Strings" onder het veld "Regio";
stel de financiële getalnotatie in op de cellen met waarden.

Zodra u de velden "Regio" en "Klant" onder elkaar in het gebied "Rijen" plaatst, begrijpt het systeem hoe u de gegevens wilt weergeven en stelt het de juiste optie voor.

het veld "Omzet" wordt in het gebied "Waarden" geplaatst.

Als gevolg hiervan hebben we een draaitabel gekregen, die de gegevens van klantinkomsten binnen elke regio weergeeft.

Volg deze stappen om de gegevens te sorteren:

Klik klik met de rechtermuisknop op een van de regels met omzetgegevens in de draaitabel;
ga naar het menu "Sorteren" => "Sorteren aflopend":

In de resulterende tabel kunnen we bepalen welke plaats de klant van Ludnikov IP inneemt onder alle klanten van de Vostok-regio.

Er zijn verschillende opties om dit probleem op te lossen. U kunt het veld "Regio" naar het gebied "Filters" verplaatsen en de verkoopgegevens van de klant in de regels plaatsen, zodat de gegevens over de omzet van alleen de klanten van de regio Oost worden weergegeven.

Als je nog nooit een spreadsheet hebt gebruikt om documenten te maken, raden we je aan onze Excel-gids voor dummies te lezen.

Daarna kunt u uw eerste spreadsheet maken met tabellen, grafieken, wiskundige formules en opmaak.

Details van basisfuncties en kansen tafelprocessor MS-Excel. Beschrijving van de belangrijkste elementen van het document en instructies om ermee te werken in ons materiaal.

Werken met cellen. Opvulling en opmaak

Voordat u met specifieke stappen begint, moet u het basiselement van elk document in Excel begrijpen. Een Excel-bestand bestaat uit een of meer bladen die zijn onderverdeeld in kleine cellen.

Een cel is het basisonderdeel van elk Excel-rapport, elke tabel of elke grafiek. Elke cel bevat één blok informatie. Dit kan een getal, datum, valuta, maateenheid of ander gegevensformaat zijn.

Om een cel te vullen, klikt u erop met de aanwijzer en voert u in Nodige informatie... Om een eerder gevulde cel te bewerken, klik erop Dubbelklik muizen.

Rijst. 1 - een voorbeeld van het vullen van cellen

Elke cel op het blad heeft zijn eigen unieke adres. U kunt er dus berekeningen of andere bewerkingen mee uitvoeren. Wanneer u op een cel klikt, verschijnt een veld met het adres, de naam en de formule bovenaan het venster (als de cel betrokken is bij berekeningen).

Laten we de cel "Aandeel van aandelen" selecteren. Het locatieadres is A3. Deze informatie wordt aangegeven in het eigenschappenvenster dat wordt geopend. We kunnen ook de inhoud zien. Deze cel heeft geen formules, dus ze worden niet weergegeven.

Meer celeigenschappen en functies die in verband daarmee kunnen worden gebruikt, zijn beschikbaar in het contextmenu. Klik op de cel met de rechtertoets van de manipulator. Er wordt een menu geopend waarmee u de cel kunt opmaken, de inhoud kunt analyseren, een andere waarde kunt toewijzen en andere acties.

Rijst. 2 - contextmenu cel en zijn belangrijkste eigenschappen

Gegevens sorteren

Vaak worden gebruikers geconfronteerd met de taak om gegevens op een blad in Excel te sorteren. Met deze functie kunt u snel alleen de gewenste gegevens uit de hele tabel selecteren en bekijken.

Voordat u een reeds voltooide tabel heeft (we zullen later in het artikel uitzoeken hoe u deze kunt maken). Stel je voor dat je de gegevens voor januari in oplopende volgorde wilt sorteren. Hoe zou je het doen? Het banale overtypen van de tabel is extra werk, bovendien, als het omvangrijk is, zal niemand het doen.

Excel heeft een speciale functie voor sorteren. De gebruiker hoeft alleen:

Selecteer een tabel of informatieblok;
Open het tabblad "Gegevens";
Klik op het pictogram "Sorteren";

Rijst. 3 - Tabblad "Gegevens"

Selecteer in het geopende venster de kolom van de tabel waarover we acties zullen uitvoeren (januari).
Vervolgens het sorteertype (we groeperen op waarde) en ten slotte is de volgorde oplopend.
Bevestig de actie door op "OK" te klikken.

Rijst. 4 - sorteerparameters instellen

Zal gebeuren automatisch sorteren gegevens:

Rijst. 5 - het resultaat van het sorteren van de getallen in de kolom "Januari"

Op dezelfde manier kunt u sorteren op kleur, lettertype en andere parameters.

Wiskundige berekeningen

Het belangrijkste Excel voordeel- de mogelijkheid om automatisch berekeningen uit te voeren tijdens het invullen van de tabel. We hebben bijvoorbeeld twee cellen met waarden 2 en 17. Hoe voer je hun resultaat in de derde cel in zonder zelf de berekeningen uit te voeren?

Om dit te doen, moet u op de derde cel klikken, waarin het eindresultaat van de berekeningen wordt ingevoerd. Klik vervolgens op het functiepictogram f (x) zoals weergegeven in de onderstaande afbeelding. Selecteer in het geopende venster de actie die u wilt toepassen. SOM is de som, GEMIDDELDE is het gemiddelde, enzovoort. Volle lijst functies en hun namen in de Excel-editor zijn te vinden op de officiële website Microsoft.

We moeten de som van twee cellen vinden, dus klik op "SOM".

Rijst. 6 - selectie van de functie "SOM"

Het functieargumentenvenster heeft twee velden: "Nummer 1" en "Nummer 2". Selecteer het eerste veld en klik op de cel met het nummer "2". Het adres wordt naar de argumentstring geschreven. Klik op "Nummer 2" en klik op de cel met het nummer "17". Bevestig vervolgens de actie en sluit het venster. Als het nodig is om uit te voeren wiskundige acties met drie of grote hoeveelheid cellen, ga gewoon door met het invoeren van de waarden van de argumenten in de velden "Nummer 3", "Nummer 4" enzovoort.

Als in de toekomst de waarde van de opgetelde cellen verandert, wordt hun som automatisch bijgewerkt.

Rijst. 7 - het resultaat van de berekeningen

Tabellen maken

Alle gegevens kunnen worden opgeslagen in Excel-tabellen. De functie gebruiken: snelle installatie en opmaak, het is heel eenvoudig om het besturingssysteem in de editor te organiseren persoonlijk budget, een lijst met uitgaven, digitale gegevens voor rapportage en meer.

Tabellen in Excel hebben voorrang op vergelijkbare opties in Word en andere kantoorprogramma's... Hier heeft u de mogelijkheid om een tafel van elke afmeting te maken. De gegevens zijn eenvoudig in te vullen. Er is een functiepaneel voor het bewerken van inhoud. Daarnaast, klaar tafel kan worden geïntegreerd in docx-bestand via normale functie kopiëren plakken.

Volg de instructies om een tabel te maken:

Klik op het tabblad Invoegen. Selecteer Tabel aan de linkerkant van het optiepaneel. Als u gegevens wilt samenvatten, selecteert u het item "Pivot Table";
Selecteer met de muis een plaats op het blad die voor de tafel zal worden toegewezen. En u kunt ook de locatie van de gegevens invoeren in het venster voor het maken van elementen;
Klik op OK om de actie te bevestigen.

Rijst. 8 - een standaardtabel maken

Om te formatteren verschijning van de resulterende plaat, open de inhoud van de constructor en klik in het veld "Stijl" op de gewenste sjabloon. Indien gewenst, kunt u uw eigen weergave maken met een andere kleuren en selectie van cellen.

Rijst. 9 - de tabel opmaken

Het resultaat van het vullen van de tabel met gegevens:

Rijst. 10 - gevulde tafel

Voor elke cel in de tabel kunt u ook het gegevenstype, de opmaak en de informatieweergavemodus aanpassen. Het constructorvenster bevat alle benodigde opties voor verdere configuratie van de plaat, op basis van uw wensen.

Grafieken / grafieken toevoegen

Om een kaart of grafiek te maken, is een kant-en-klaar plaatje nodig, omdat de grafische gegevens precies gebaseerd zullen zijn op informatie uit individuele lijnen of cellen.

Om een grafiek/grafiek te maken, heb je nodig:

Selecteer de tafel volledig. Als u alleen een grafisch element moet maken om de gegevens van bepaalde cellen weer te geven, selecteert u alleen deze;
Open het tabblad Invoegen;
Selecteer in het vak aanbevolen grafieken het pictogram waarvan u denkt dat het is de beste manier zal de tabelinformatie visueel beschrijven. In ons geval is dit een volumetrisch cirkeldiagram... Verplaats de aanwijzer naar het pictogram en selecteer het uiterlijk van het element;

Op dezelfde manier kunt u spreidingsgrafieken, lijndiagrammen en afhankelijkheden van tabelelementen maken. Alles ontvangen grafische elementen kan ook worden toegevoegd aan tekstdocumenten Woord.

Er zijn echter veel andere functies in de Excel-spreadsheeteditor, voor: eerste werk de technieken die in dit artikel worden beschreven, zijn voldoende. Tijdens het maken van een document beheersen veel gebruikers onafhankelijk de meer geavanceerde opties. Dit komt door de handige en duidelijke interface laatste versies programma's.

Thematische video's:

Draaitabel in Excel is een krachtige tool voor gegevensanalyse waarmee u snel:

Gegevens voorbereiden voor rapporten;
Bereken verschillende indicatoren;
Groepsgegevens;
Filter en analyseer de statistieken van belang.

En bespaar je ook nog eens tonnen tijd.

In dit artikel leer je:

Hoe maak je een draaitabel?;
Een draaitabel gebruiken? groepstijdreeksen en evalueer de gegevens in dynamiek per jaar, kwartalen, maanden, dagen ...
Hoe de prognose te berekenen met behulp van draaitabel en Forecast4AC PRO;

Laten we eerst leren hoe u draaitabellen maakt.

Om een draaitabel te maken, moeten we de gegevens in de vorm van een eenvoudige tabel bouwen. Elke kolom moet 1 geparseerde parameter bevatten. We hebben bijvoorbeeld 3 kolommen:

Product
Verkoop in roebels

En in elke regel 3e parameters zijn gerelateerd, d.w.z. bijvoorbeeld, 02/01/2010, Product 1 werd verkocht voor 422 656 roebel.

Nadat u de gegevens voor de draaitabel hebt voorbereid, plaatst u de cursor naar de eerste kolom naar de eerste cel van een eenvoudige tabel, ga dan naar het menu "Invoegen" en druk op de knop "Pivot table"

Er verschijnt een dialoogvenster waarin:

Jij kan druk onmiddellijk op de knop "OK", en de draaitabel wordt in een apart blad weergegeven.
of u kunt de parameters van de gegevensuitvoer van de draaitabel configureren:

Bereik met gegevens die in de draaitabel worden weergegeven;
Waar de samenvatting moet worden weergegeven (in nieuw blad of op een bestaande (als u op een bestaande selecteert, moet u de cel specificeren waarin u de draaitabel wilt plaatsen)).

Klik op "OK", de draaitabel is klaar en wordt weergegeven in een nieuw blad. Laten we het blad Pivot noemen.

Aan de rechterkant van het blad ziet u de velden en gebieden waarmee u kunt werken. U kunt de velden naar de gebieden slepen en ze worden weergegeven in de draaitabel op het werkblad.
Aan de linkerkant van het blad bevindt zich een draaitabel.

Houd nu het veld "Product" met de linkermuisknop ingedrukt - sleep het naar het veld "Lijnnamen", het veld "Verkoop in roebels". - in "Waarden" in de draaitabel. Zo ontvingen we de totale omzet per goederen voor de hele periode:

Tijdreeksen groeperen en filteren in een draaitabel

Als we de verkoop van goederen per jaar, kwartalen, maanden, dagen willen analyseren en vergelijken, dan hebben we nodig: voeg de juiste velden toe aan de draaitabel.

Ga hiervoor naar het blad "Gegevens" en voeg na de datum 3 lege kolommen in. Selecteer de kolom "Product" en klik op "Invoegen".

Belangrijk zodat de nieuw toegevoegde kolommen zich binnen het bereik van een bestaande tabel met gegevens bevinden, dan hoeven we het draaipunt niet opnieuw uit te voeren om nieuwe velden toe te voegen, het is voldoende om het bij te werken.

De ingevoegde kolommen worden "Jaar", "Maand", "Jaar-Maand" genoemd.

Voeg nu in elk van deze kolommen de juiste formule toe om de gewenste tijdparameter te krijgen:

Voeg de formule toe aan de kolom "Jaar" = JAAR (met verwijzing naar de datum);
Voeg de formule toe aan de kolom "Maand" = MAAND (met verwijzing naar de datum);
Voeg de formule toe aan de kolom "Jaar - Maand" = CONCATENATE (link naar jaar; ""; link naar maand).

We krijgen 3 kolommen met jaar, maand en jaar en maand:

Ga nu naar het blad "Samenvatting", plaats de cursor op de draaitabel, klik met de rechtermuisknop op het menu en druk op de "Update" knop... Na de update hebben we in de lijst met velden: nieuwe draaitabelvelden"Jaar", "Maand", "Jaar - maand", die we hebben toegevoegd aan een eenvoudige tabel met gegevens:

Laten we nu de verkopen per jaar analyseren.

Om dit te doen, slepen we het veld "Jaar" naar de "kolomnamen" van de draaitabel. We krijgen een tabel met de verkoop per product per jaar:

Nu willen we nog dieper "dalen" naar het niveau van maanden en de verkoop per jaar en per maand analyseren. Sleep hiervoor het veld "maand" onder het jaar naar de "kolomnamen":

Om de dynamiek van maanden per jaar te analyseren, kunnen we de maanden naar het draaigebied "Rijnamen" verplaatsen en de volgende weergave van de draaitabel krijgen:

V deze inzending draaitabel zien we:

verkoop voor elk product in het bedrag voor het hele jaar (lijn met de naam van het product);
in meer detail verkoop voor elk product in elke maand in dynamiek gedurende 4 jaar.

Volgende uitdaging, wij we willen verkopen voor een maand uit de analyse verwijderen(bijvoorbeeld oktober 2012), omdat we hebben nog geen verkoopgegevens voor een volledige maand.
Sleep hiervoor "Jaar - maand" naar het draaigebied "Rapportfilter"

Klik op het overzichtsfilter dat hierboven verschijnt en zet een vinkje bij "Selecteer meerdere elementen"... Schakel vervolgens in de lijst met jaren en aantallen maanden 2012 10 uit en klik op OK.

Zo kunt u nieuwe parameters toevoegen om de tijd te wijzigen en die tijdsintervallen analyseren die voor u interessant zijn en in de vorm waarin u deze nodig heeft. De draaitabel berekent indicatoren voor de velden en filters die u instelt en voegt eraan toe als een interessegebied.

Pronoz berekenen met behulp van draaitabel en Forecast4AC PRO

Laten we de verkoop opbouwen met behulp van een draaitabel per product, per jaar en per maand. Ook de eindtotalen zetten we uit, zodat ze niet in de berekening worden meegenomen.

Om totalen in de draaitabel uit te schakelen, plaatst u de cursor op de kolom "Totaaltotaal" en klikt u op de knop "Groottotaal verwijderen". Het totaal verdwijnt uit het overzicht.

en druk op de knop "Forecast Model Chart" in het Forecast4AC PRO-menu

We krijgen de berekening van de prognose voor 12 maanden en mooi schema met de analyse van het prognosemodel (trend, seizoensgebondenheid en model) ten opzichte van de werkelijke gegevens. Forecast4AC PRO kan voorspellingen,, trend en andere indicatoren voor u berekenen en grafieken maken op basis van de gegevens die in de draaitabel worden weergegeven.

Draaitabel in Excel is een krachtige tool voor gegevensanalyse waarmee u snel en eenvoudig statistieken kunt berekenen en gegevens kunt plotten in de vorm waarin u geïnteresseerd bent.