Begin met gegevens van meerdere jaren.
Stel dat u over twee jaar aan detailrecords beschikt. Elk record heeft een dagelijkse datum. Wanneer u een draaitabel maakt op basis van dit rapport, heeft u honderden rijen dagelijkse datums in de draaitabel. Dit is niet echt een samenvatting.
Kies een van die datumcellen in de draaitabel. Kies Groepsveld op het tabblad Analyseren op het lint.
Omdat u zich in een datumveld bevindt, krijgt u deze versie van het dialoogvenster Groepering. Schakel daarin Maanden uit en selecteer Jaren.
De dagelijkse datums worden naar jaren gerold. Verplaats het veld Datum van RIJEN naar KOLOMMEN.
Het resultaat is bijna perfect. Maar in plaats van een eindtotaal in kolom D, wilt u waarschijnlijk een procentuele afwijking.
Om de kolom Eindtotaal te verwijderen, kiest u op het tabblad Ontwerpen Eindtotalen, Alleen Aan voor kolommen. (Ik ben het ermee eens, dit is een van de meer lastige bewoordingen in Excel.)
Om de variantie-kolom te bouwen, moet u een formule schrijven buiten de draaitabel die naar de draaitabel verwijst. Raak de muis of pijltjestoetsen niet aan tijdens het bouwen van de formule, anders zal de vervelende GETPIVOTDATA-functie verschijnen. Typ in plaats daarvan =C5/B5-1en druk op Enter.
Met dank aan Александр Воробьев voor het voorstellen van deze tip.
Bekijk video
- Begin met gegevens van meerdere jaren
- Inzet, draaitabel
- Sleep het datumveld naar het rijgebied
- Excel 2016: druk op Ctrl + Z om datums te degroeperen
- Sleep inkomsten naar waardengebied
- Selecteer een datum in het rijgebied
- Gebruik groepsveld
- Kies maanden en jaren
- Hoe u subtotalen aan het veld jaren toevoegt na het groeperen van een draaitabel
- Gebruik tabelvorm in een draaitabel om elk rijveld een eigen kolom te geven
- Herhaal alle rijlabels om de lege plekken in een draaitabel in te vullen
- Sleep jaren naar het kolomgebied
- Klik met de rechtermuisknop op de kolomkop Algemeen totaal en verwijder
- Hoe de GetPivotData-functie te vermijden wanneer de formule naar een draaitabel verwijst
- Typ de formule zonder muis of pijlen om een variantie op te bouwen
- Met dank aan Александр Воробьев voor het voorstellen van deze tip
Videotranscriptie
Leer Excel van MrExcel podcast, aflevering 1998. Draaitabel jaar na jaar.
Ik ga dit hele boek podcasten, klik op die "i" in de rechterbovenhoek voor de MrExcel Excel-afspeellijst.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. Vandaag het eerste onderwerp in het boek over draaitabellen en het maken van een jaar-op-jaarrapport. Dus we beginnen met de dataset, die twee jaar beslaat, zie je, we gaan van 2018 tot 2019. Ik ga een draaitabel maken, invoegen, draaitabel maken, oké, dat is oké, laten we naar een nieuw werkblad.
Op het nieuwe werkblad gaan we het veld Datum nemen en het naar het gebied Rijen slepen. En dan, zie je, in Excel 2016 worden dingen automatisch gegroepeerd. Nu weet ik dat de meeste mensen nog steeds in Excel 2013 zijn, misschien Excel 2010, dus ik ga op Ctrl + Z drukken om het ongedaan te maken. Dat is de ervaring die u zult opdoen in 2010 en 2013, we nemen het veld Opbrengst en slepen het naar het gebied Waarden. Dus we beginnen met een lelijk rapport, er zullen honderden en honderden rijen zijn, omdat we elke dag verkopen. Maar ik ga naar het allereerste datumveld, allereerste datumveld, ga naar de draaitabelhulpmiddelen, analyseren, in 2013 of 2016 en 2010 heette het Opties, en kies Groepsveld. Zorg ervoor dat u een datum kiest voordat u Groepsveld kiest en verdeel de zaken naar maanden en jaren. Of kwartalen en jaren,of maanden en kwartalen en jaren, klik op OK. En wat we krijgen, is een nieuw virtueel veld genaamd Jaren hier beneden in het gebied Rijen.
Nu wat ergernissen. Het is vervelend dat ze ons geen jaartotalen geven, het is eenvoudig genoeg om dat op te lossen, hoewel ik dat in mijn geval niet hoef te repareren. We klikken op Veldinstellingen en Automatisch, omdat ik niet van plan ben de Jaren hier te verlaten. Elke keer dat ik een draaitabel maak, ga ik natuurlijk naar Ontwerpen en zeg ik Toon in tabelvorm om alles in zijn eigen kolom te krijgen. En herhaal vervolgens alle itemlabels om die lege plekken aan de linkerkant in te vullen. Nogmaals, dat zou hier niet nodig zijn, want ik ben niet van plan om de Jaren te laten waar het is, ik ga de Jaren nemen en het naar de Columns slepen, en dat is het rapport waar ik voor fotografeerde.
Oké, dus we hebben maanden aan de linkerkant, 2018 tot 2019. In kolom D, wat wil ik echt, ik wil geen algemeen totaal, klik ik met de rechtermuisknop en zeg Totaal en nu zijn we buiten een draaitabel. Dus,% Change, erg verleidelijk om deze formule te bouwen met de muis of de pijltjestoetsen, maar dat lukt niet, omdat je de… get Pivot Data-functie krijgt, die niet gemakkelijk kan worden gekopieerd. Dus = C5 / B5-1, we formatteren dat als een percentage en dubbelklikken om het naar beneden te kopiëren, rechts. Prachtig rapport dat laat zien waar we vorig jaar waren, waar we dit jaar waren en dan de procentuele verandering van jaar tot jaar.
Nou, deze tip en 40 andere staan allemaal in dit boek. Ga je gang, klik op die "i" in de rechterbovenhoek, naast het boek. Oké, dus je begint met de dataset met meerdere jaren, doe Invoegen, Draaitabel, sleep het datumveld naar het rijgebied. En in Excel 2016 drukt u op Ctrl + Z om hun automatische groepsupdates ongedaan te maken. Waarom laten we dit hier niet gewoon achter? Nou, het hangt er eigenlijk van af. Als u gegevens zou hebben van, laten we zeggen, 2 januari tot en met 30 december, zullen ze die gegevens niet correct groeperen. Het moet een heel jaar duren voordat je het jaarveld krijgt. Het kan zijn dat het voor jou werkt, ik ben een beetje een controlefreak, ik heb liever gewoon controle, daarom maak ik het ongedaan. Er is: sleep de omzet naar het waardengebied, selecteer een cel, een willekeurige datum in het rijgebied en vervolgens Groepsveld, kies Maanden en jaren,sleep jaren over het kolomgebied, klik met de rechtermuisknop op het eindtotaal om te verwijderen en bouw vervolgens een variantie op, typ de formule in, gebruik de muis- of pijltoetsen niet.
Ik heb geen idee hoe ik het moet uitspreken, maar dankzij deze lezer (Александр Воробьев) voor het suggereren van deze tip en bedankt voor het langskomen. We zien je de volgende keer voor een nieuwe netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast1998.xlsx
