Pivottabeller forklaret, og formlerne der gør det samme

Pivottabeller har ry for at være avancerede. Det er de ikke. En pivottabel besvarer én type spørgsmål, "hvor meget af det her pr. det der?", og den gør det med nogle få klik i stedet for en formel. Samlet salg pr. region. Antal ordrer pr. kunde. Gennemsnitlig leveringstid pr. fragtfirma og måned.

Denne guide forklarer, hvad en pivottabel faktisk beregner, hvordan du laver en trin for trin, og den håndfuld problemer, der driller folk. Den viser også de formler, der giver de samme tal, for når du kender dem, bliver du meget bedre til at læse pivottabeller, og fordi nogle job, test og regneark kræver formeludgaven.

Hvad en pivottabel gør

Start med en tabel, hvor hver række er én post: én ordre, én transaktion, én registrering af timer. En pivottabel grupperer rækkerne efter værdierne i én kolonne og opsummerer så en anden kolonne for hver gruppe.

Lad os sige, at du har otte ordrer med en region, en vare og et beløb. En pivottabel med Region i Rækker og Salg i Værdier giver dig én række pr. region og summen af dens salg. Sæt Vare i Kolonner, så får du et gitter: regionerne ned langs siden, varerne hen over toppen og en total i hver celle. Det er alt, hvad en pivottabel er. Resten er tilvalg ovenpå, og dem kan du lære, efterhånden som du får brug for dem.

Under motorhjelmen er hvert tal i gitteret en betinget sum, et betinget antal eller et betinget gennemsnit: "summen af Salg, hvor Region er Nord, og Vare er Sko". Det er præcis det, SUM.HVISER beregner, og derfor giver formlerne senere i guiden de samme resultater.

Gør data klar

De fleste problemer med pivottabeller er i virkeligheden problemer med data. Før du indsætter en, så tjek, at dine data følger disse regler:

• Én overskriftsrække med et entydigt navn i hver kolonne og ingen tomme overskrifter. Pivottabellen bruger overskrifterne som feltnavne.
• Én post pr. række. Ingen subtotalrækker, ingen tomme rækker midt i og ingen noter under data.
• Én slags værdi pr. kolonne. Kolonnen Salg skal kun indeholde tal. Et enkelt "i/t" gør hele kolonnen til tekst set fra pivottabellens side.
• Ingen flettede celler. Ophæv fletningen, og udfyld værdierne.

Den hurtigste måde at gøre data klar til en pivottabel på er at klikke inde i dem og trykke Ctrl+T for at oprette en Excel-tabel. En tabel vokser automatisk, når du tilføjer rækker, så en pivottabel, der bygger på den, får de nye data med, når du opdaterer.

Lav en pivottabel trin for trin

Her er hele processen i Excel til Windows og Mac. Menuerne ser lidt forskellige ud fra version til version, men trinene er de samme.

1. Klik i en vilkårlig celle i dine data. Du behøver ikke markere hele området; Excel finder selv kanterne.
2. Vælg Indsæt og derefter Pivottabel. Excel foreslår området eller tabellen og tilbyder at placere pivottabellen i et nyt regneark. Sig ja til begge dele.
3. Kig på ruden Pivottabelfelter. Øverst står en liste med dine kolonneoverskrifter. Nederst er der fire felter: Filtre, Kolonner, Rækker og Værdier.
4. Træk et felt ned i Rækker. Træk Region ned i Rækker, så får du én række pr. region.
5. Træk et talfelt ned i Værdier. Træk Salg ned i Værdier, så tilføjer Excel "Sum af Salg", altså totalen pr. region, plus en hovedtotal.
6. Træk eventuelt et felt ned i Kolonner. Træk Vare ned i Kolonner, så bliver den ene kolonne med totaler til et gitter med regioner og varer.
7. Træk eventuelt et felt ned i Filtre. Et felt i Filtre giver dig en rulleliste over pivottabellen, så du for eksempel kun kan vise ét år.

Det er det hele. Vil du ændre layoutet, trækker du felterne mellem de fire felter, og vil du fjerne et, trækker du det ud af ruden.

Sum, antal, gennemsnit og mere

Som standard lægger Excel talfelter sammen og tæller tekstfelter. Vil du ændre det, så klik på feltet i Værdier, vælg Værdifeltindstillinger, og vælg Sum, Antal, Gennemsnit, Maks, Min eller en af de andre.

Her er, hvad de mest brugte indstillinger beregner, skrevet som den formel, du ville bruge i stedet. Først summen pr. region:

Antal fortæller dig, hvor mange rækker hver gruppe har, uanset beløbene:

Og Gennemsnit dividerer summen med antallet:

To andre indstillinger er værd at kende. Vis værdier som (i samme dialogboks) laver summer om til procenter: % af hovedtotal, % af kolonnetotal, % af rækketotal eller forskellen fra forrige måned. Og du kan sætte det samme felt ind i Værdier to gange, én gang som Sum og én gang som Antal, så du ser begge dele side om side.

Procent af total

"% af hovedtotal" dividerer hver gruppes sum med totalen for det hele. Som formel er det en SUM.HVIS divideret med en SUM:

Pivottabeludgaven opdaterer, når du opdaterer pivottabellen. Formeludgaven opdaterer, så snart data ændrer sig. Vores opgave om andel af total beder om præcis den formel.

Rækker og kolonner på én gang

Når du sætter ét felt i Rækker og et andet i Kolonner, har hver celle i gitteret to betingelser. Som formel er det SUM.HVISER med én betingelse fra rækkens etiket og én fra kolonnens overskrift:

Det er dollartegnene, der får det til at virke. $E2 låser kolonnen, men lader rækken ændre sig, når du kopierer nedad, så hver række læser sin egen region. F$1 låser rækken, men lader kolonnen ændre sig, når du kopierer til højre, så hver kolonne læser sin egen vare. Skriv formlen én gang i F2, kopiér den til højre og nedad, så har du bygget pivottabellen op med formler. Opgaven med krydstabellen træner præcis det, og den er en af de bedste opgaver, der findes, til at forstå blandede referencer.

Gruppér datoer

Sætter du et datofelt i Rækker, grupperer nyere versioner af Excel det automatisk i år, kvartaler og måneder. Gør din ikke det, eller vil du have en anden gruppering, så højreklik på en dato i pivottabellen, og vælg Gruppér. Du kan gruppere efter dage, måneder, kvartaler og år eller en kombination.

Er Gruppér gråt og kan ikke vælges, indeholder datokolonnen mindst én værdi, der ikke er en rigtig dato, som regel tekst, der ligner en dato. Find den, ret den, og opdatér.

Tal kan også grupperes. Højreklik på et tal i Rækker, vælg Gruppér, og angiv start, slut og interval, så får du intervaller som 0-99, 100-199 og så videre. Det er praktisk til ordrestørrelser eller aldre.

Filtre og udsnitsværktøjer

Feltet Filtre giver dig en rulleliste over pivottabellen. Udsnitsværktøjer gør det samme med knapper: Markér pivottabellen, vælg fanen Pivottabelanalyse, derefter Indsæt udsnitsværktøj, og sæt flueben ved felterne. Udsnitsværktøjer er lettere for andre at bruge, og ét udsnitsværktøj kan styre flere pivottabeller, der bygger på de samme data, og så er du godt på vej mod et enkelt dashboard.

Du kan også filtrere inde i Rækker eller Kolonner med den lille pil ved etiketten, for eksempel for kun at vise de ti største kunder målt på salg.

Gør pivottabellen læsbar

En ny pivottabel er korrekt, men sjældent pæn. Nogle få indstillinger gør den til noget, du kan sætte ind i en rapport.

Talformat. Formaterer du cellerne direkte, forsvinder formatet, når pivottabellen opdateres. Åbn i stedet Værdifeltindstillinger, klik på Talformat, og vælg formatet dér: tusindtalsseparator, ingen decimaler eller procent. Så bliver formatet siddende.

Rapportlayout. På fanen Design har Rapportlayout tre muligheder. Kompakt form, som er standard, sætter alle rækkefelter i én kolonne med indrykning. Dispositionsform og tabelform giver hvert rækkefelt sin egen kolonne, og det er lettere at læse og lettere at kopiere over i et andet ark. Vælg også Gentag alle elementnavne, hvis du vil kopiere resultatet et andet sted hen, så hver række har alle sine etiketter.

Subtotaler og hovedtotaler. På samme fane kan du slå subtotaler og hovedtotaler fra eller flytte dem. Med to felter i Rækker kan subtotaler pr. gruppe være nyttige; med ét felt gentager de bare hovedtotalen.

Sortering. Klik på pilen ved en rækkeetiket, og vælg Flere sorteringsindstillinger for at sortere efter værdien, for eksempel regionerne efter samlet salg med den største først. Sortering efter etiketten er standard, og det er sjældent det, læseren vil have.

Feltnavne. "Sum af Salg" er præcist, men klodset. Klik på overskriften i pivottabellen, og skriv et nyt navn, for eksempel "Salg". Det må ikke være helt det samme som kildekolonnens navn, så "Salg " med et mellemrum til sidst er et kendt trick.

Beregnede felter

Nogle gange står det tal, du skal bruge, ikke i data, men kan beregnes ud fra dem: avance ud fra salg og kostpris eller pris pr. stk. ud fra omsætning og antal. Et beregnet felt tilføjer et nyt felt til pivottabellen med en formel som =Salg-Kostpris. Du finder det under Pivottabelanalyse, Felter, elementer og sæt, Beregnet felt.

Beregnede felter regner på summerne, ikke på de enkelte rækker. En avanceprocent defineret som =Avance/Salg giver det rigtige svar for hver gruppe, fordi den dividerer gruppens samlede avance med dens samlede salg. Et felt, der ganger to kolonner række for række, for eksempel pris gange antal, giver det forkerte svar, fordi det ganger summerne. Beregn det i stedet i en ny kolonne i kildedata, og opdatér.

Et eksempel fra start til slut

Sådan ser en typisk opgave ud. Din chef sender et ark med sidste kvartals 600 ordrer og spørger: "Hvilken region solgte mest, og hvordan klarede hver vare sig i hver region?"

1. Klik i data, og tryk Ctrl+T, så det bliver en tabel. Tjek, at kolonnen Salg er højrestillet hele vejen ned, for så er det tal.
2. Indsæt en pivottabel i et nyt ark. Træk Region ned i Rækker og Salg ned i Værdier. Sortér regionerne efter Sum af Salg med den største først. Så er det første spørgsmål besvaret.
3. Træk Vare ned i Kolonner. Nu er hver celle én regions salg af én vare. Sæt talformatet til hele tal med tusindtalsseparator.
4. Sæt Salg ind i Værdier en gang til, og sæt Vis værdier som til % af rækketotal. Nu kan du se hver vares andel inden for hver region, og det er ofte det mest interessante svar.
5. Tjek ét tal i hånden, før du sender. En SUM.HVISER for én region og én vare skal give den samme total som pivottabellen. Gør den det, så send.

Det hele tager fem minutter, og trin 5 er det, der redder dig fra en pinlig rettelse bagefter. Gør det til en fast vane, også når du har travlt.

Opdatering og andre ting, der går galt

Her er de problemer, folk oftest løber ind i, nogenlunde i rækkefølge efter hvor tit de sker.

Tallene ændrede sig ikke, da data gjorde. Pivottabeller er øjebliksbilleder. Når du har ændret kildedata, så højreklik på pivottabellen, og vælg Opdater, eller tryk Alt+F5. Opdater alle på fanen Data opdaterer alle pivottabeller i projektmappen.

Nye rækker mangler. Har du tilføjet rækker under det oprindelige kildeområde, er det ikke nok at opdatere. Vælg Skift datakilde, og udvid området, eller endnu bedre: Lav kilden om til en tabel med Ctrl+T, så den vokser af sig selv.

"Antal af Salg" i stedet for "Sum af Salg". Kolonnen indeholder tekst eller tomme celler et sted. Ret data, og skift så feltindstillingen til Sum. Den sædvanlige synder er et tal, der er gemt som tekst; det står til venstre i cellen.

Den samme gruppe står der to gange. "Nord" og "Nord " (med et mellemrum) er to forskellige værdier for en pivottabel. Rens kildekolonnen med FJERN.OVERFLØDIGE.BLANKE eller Søg og erstat, og opdatér.

"(tom)" står som en gruppe. Nogle rækker har en tom celle i det felt. Udfyld dem, eller filtrér (tom) fra, hvis de virkelig mangler.

En formel, der peger ind i pivottabellen, giver mærkelige resultater. Klikker du på en celle i en pivottabel, mens du skriver en formel, indsætter Excel funktionen HENTPIVOTDATA i stedet for en almindelig reference. Den er lavet til at blive ved med at virke, når pivottabellen skifter form. Vil du have en almindelig reference, så skriv celleadressen i stedet for at klikke, eller slå Generér HentPivotData fra i pivottabellens indstillinger.

Pivottabel eller formler?

Begge giver de samme tal. De er gode til hver sine ting.

Brug en pivottabel, når:

• Du udforsker data og endnu ikke ved, hvilken opsummering du har brug for. Det er meget hurtigere at trække felter rundt end at skrive formler om.
• Opsummeringen kun skal bruges én gang, til et møde eller en mail.
• Du har brug for gruppering efter måned eller kvartal eller en hurtig top ti.

Brug formler, når:

• Opsummeringen skal have et fast layout, for eksempel en månedsrapport med de samme rækker hver gang.
• Resultaterne skal opdatere, uden at nogen trykker Opdater.
• Andre beregninger bygger på resultaterne, for eksempel en budgetopfølgning eller en provisionsberegning.
• Opsummeringen skal kombinere data på måder, en pivottabel ikke kan, for eksempel et forhold mellem to forskellige kilder.

Mange gode regneark bruger begge dele: en pivottabel til at udforske og kontrollere og en tabel med SUM.HVISER til den udgave, der kommer med i rapporten. Når de to stemmer overens, kan du stole på tallene. Den krydskontrol er også en god vane i økonomiarbejde, som vores guide til Excel til økonomi handler om.

I Excel 365 giver funktionen ENTYDIGE dig listen over rækkeetiketter automatisk: =ENTYDIGE(A2:A9) spreder de forskellige regioner ned ad en kolonne, og en SUM.HVIS ved siden af giver en opsummering med formler, der vokser med data.

Pivottabeller i Google Sheets

Google Sheets har også pivottabeller. Markér data, og vælg Indsæt og derefter Pivottabel. Editoren til højre har de samme fire områder, Rækker, Kolonner, Værdier og Filtre, og det samme udvalg af Sum, Antal, Gennemsnit og andre under opsummeringen. To forskelle: Sheets opdaterer sine pivottabeller automatisk, når data ændrer sig, uden at du skal trykke Opdater, og den kan foreslå pivottabeller for dig. Alle formlerne i denne guide virker også i Sheets.

Pivottabeller i Excel-test

Arbejdsgiveres test spørger til pivottabeller på to måder. Paratvidensspørgsmål spørger, hvilket område et felt skal i, eller hvordan du skifter Sum til Antal. Praktiske opgaver giver dig data og beder om en opsummering, "samlet salg pr. region og vare", som du kan lave med en pivottabel, hvis testmiljøet har en, eller med SUM.HVIS og SUM.HVISER, hvis det er en simulator i browseren uden pivottabeller. Kan du begge veje, er du dækket ind uanset hvad.

Forbereder du dig til en test, så øv formelvejen, til den går hurtigt, for den viser bedømmeren, at du forstår, hvad opsummeringen beregner. Sørg derefter for, at du kan indsætte en pivottabel, skifte opsummeringsfunktion og opdatere den i din egen Excel.

Kort fortalt

En pivottabel grupperer rækkerne i en tabel efter et eller to felter og lægger sammen, tæller eller finder gennemsnittet af et andet felt for hver gruppe. Lav en med Indsæt, Pivottabel, og træk så felter ned i Rækker, Kolonner, Værdier og Filtre. Hold kildedata rene og i en tabel, opdatér efter hver ændring, og tjek værdifeltindstillingen, når en sum pludselig bliver til et antal.

Hvert tal i en pivottabel er en betinget sum, et betinget antal eller et betinget gennemsnit, som du også kan skrive som SUM.HVIS, TÆL.HVIS, MIDDEL.HVIS eller SUM.HVISER. Opgaverne i opsummeringstabeller nedenfor bygger hver af dem op, fra en enkelt total pr. region til en hel krydstabel.