GROUPBY og PIVOTBY: oversigter uden pivottabel

Du har en pivottabel med omsætning pr. sælger. Den ser fin ud, indtil der kommer nye ordrer i listen, og tallene står stille, fordi ingen har trykket Opdater.

Eller også har du slet ikke lavet en pivottabel, fordi den skulle stå et bestemt sted i en rapport, og så har du i stedet skrevet en SUM.HVISER for hver sælger.

GROUPBY og PIVOTBY løser begge dele. De laver den samme slags oversigt som en pivottabel, men som en formel i én celle, der opdaterer sig i det øjeblik data ændrer sig. Her får du de seks greb, du bruger oftest. Regn med en halv time, hvis du prøver dem undervejs.

Først: navne og version

Navnene: Microsoft bruger selv de engelske navne GROUPBY og PIVOTBY i den danske hjælp, og det gør jeg også her. I dansk Excel kan navnet være oversat, PIVOTBY hedder for eksempel PIVOT.EFTER. Skriv =GRU eller =PIVOT i en celle, og vælg funktionen i listen, der dukker op. Så får du det navn, din Excel bruger.

Versionen: Funktionerne findes i Excel til Microsoft 365. Sidder du i en ældre, købt version som Excel 2019, får du #NAVN? (engelsk Excel: #NAME?). Det er ikke formlen, der er noget galt med, det er programmet, der ikke kender den.

Data i en tabel: Alle eksempler bruger en tabel ved navn Ordrer med kolonnerne Dato, Region, Sælger, Produkt, Status og Beløb. Markér dine data og tryk Ctrl+T, så kommer nye rækker automatisk med i formlerne.

1. Omsætning pr. sælger

=GROUPBY(Ordrer[Sælger];Ordrer[Beløb];SUM)

Hvad den gør: Det første argument er det, du vil gruppere efter. Det andet er de tal, der skal regnes på. Det tredje er, hvad der skal ske med tallene, her SUM.

Resultatet løber ned i cellerne under formlen: én række pr. sælger, sorteret alfabetisk, og en total nederst. Bemærk at SUM står uden parenteser. Du fortæller Excel, hvilken beregning den skal bruge, du kalder den ikke selv.

2. Største først og uden total

=GROUPBY(Ordrer[Sælger];Ordrer[Beløb];SUM;;0;-2)

Hvad de nye argumenter gør: Det femte argument styrer totaler. 0 betyder ingen total, 1 betyder en total nederst. Det sjette styrer sorteringen: 2 betyder sortér efter kolonne 2 i resultatet, altså beløbet, og minus betyder faldende.

De tomme pladser: De to semikoloner efter SUM er ikke en tastefejl. Det fjerde argument, om der skal være overskrifter, springer du over ved at lade pladsen stå tom.

Det er din top-liste over sælgere, og den bytter selv rundt på rækkefølgen, når nogen overhaler hinanden.

3. Kun de åbne ordrer

=GROUPBY(Ordrer[Sælger];Ordrer[Beløb];SUM;;;;Ordrer[Status]="Åben")

Hvad den gør: Det syvende argument er et filter. Kun de rækker, hvor betingelsen er sand, kommer med i oversigten.

Vil du have to betingelser, bruger du samme logik som i FILTRER: gangetegn betyder OG.

=GROUPBY(Ordrer[Sælger];Ordrer[Beløb];SUM;;;;(Ordrer[Status]="Åben")*(Ordrer[Region]="Nord"))

4. Gruppér på to niveauer

=GROUPBY(Ordrer[[Region]:[Sælger]];Ordrer[Beløb];SUM;;1)

Hvad den gør: Giver du to kolonner som første argument, grupperer Excel først efter region og derefter efter sælger inden for hver region.

Typisk faldgrube: Ordrer[[Region]:[Sælger]] betyder alle kolonner fra Region til Sælger. Det virker kun, fordi de to kolonner står ved siden af hinanden i tabellen. Står der en kolonne imellem, kommer den med i grupperingen.

Sætter du totaldelen til 2 i stedet for 1, får du også en subtotal for hver region.

5. En rigtig krydstabel med PIVOTBY

=PIVOTBY(Ordrer[Region];Ordrer[Produkt];Ordrer[Beløb];SUM)

Hvad den gør: PIVOTBY har ét argument mere end GROUPBY. Det første bliver til rækker, det andet til kolonner, og så følger tallene og beregningen. Her får du regionerne ned ad siden, produkterne hen over toppen og en total i begge retninger.

Det er præcis den oversigt, de fleste bygger en pivottabel for at få. Forskellen er, at den her står i en celle, du selv har valgt, og aldrig skal opdateres.

6. Andet end summer

=GROUPBY(Ordrer[Produkt];Ordrer[Beløb];GENNEMSNIT)

Byt SUM ud med GENNEMSNIT (engelsk Excel: AVERAGE), og du får gennemsnitligt ordrebeløb pr. produkt. ANTAL tæller ordrerne, MAKS og MIN finder den største og mindste. Det er de samme funktioner, du kender i forvejen, bare skrevet uden parenteser.

Den fejl du vil møde: #OVERLØB!

Hvad den betyder: Oversigten vil gerne løbe ud i cellerne omkring formlen, men der står noget i vejen. Du kan også møde den som #SPILL!.

Typisk årsag: En gammel værdi, en tom formel eller flettede celler i området. Eller at formlen står inde i en Excel-tabel, hvor overløb ikke er tilladt.

Sådan retter du: Klik på fejlen, og vælg Markér blokerende celler. Slet det, der står i vejen, eller flyt formlen ud ved siden af tabellen. Husk at en oversigt med totaler fylder en række mere, end du tror.

Pivottabel eller formel?

Pivottabellen er ikke død. Den er stadig det hurtigste værktøj, når du vil trække felter rundt og kigge på data fra forskellige vinkler, og den har udsnitsværktøjer, som dine kollegaer kan klikke på.

Formlen er bedst, når oversigten skal stå fast i en rapport, som andre læser:

Den opdaterer sig selv. Ingen Opdater-knap, ingen gamle tal i månedsrapporten.
Du kan regne videre på den. Står oversigten i E2, betyder E2# hele resultatet, uanset hvor mange rækker der kommer.
Den kan kombineres. Filteret kan pege på en celle, hvor læseren skriver en region, så oversigten skifter med.

Prøv det selv

Jeg har lavet en Excel-fil med 40 ordrer i tabellen Ordrer og seks opgaver, der følger afsnittene ovenfor. Du skriver formlerne i de gule celler, og løsningsarket viser formlerne og det resultat, du skal ende med. Et kontroltal på opgavearket fortæller dig, om din samlede omsætning passer.

Hent øvelsesfilen (Excel, xlsx)

Til sidst

Den største gevinst ved GROUPBY og PIVOTBY er, at oversigten holder op med at være et øjebliksbillede. Den viser det, der står i dine data lige nu, hver gang nogen åbner filen.

Hos KontorAkademiet tager jeg en time om måneden med den slags praktiske greb. Ikke i et bestemt program, men i det du sidder med til daglig, så du og dine kollegaer får timerne tilbage lidt efter lidt.

Table of contents