Kundedata til Excel: gratis øvelsesfil med opgaver og svar

En kundeliste er noget af det første man får i hånden i Excel når man arbejder med salg, marketing eller kundeservice. Spørgsmålene er altid af samme slags: hvor mange kunder har vi i Aarhus, hvad køber en erhvervskunde for, hvem er kunde 2077, og hvor mange får egentlig nyhedsbrevet? Her får du en kundeliste du kan øve de spørgsmål på, med facit.

Filen er et udtræk fra en lille webshop: 150 kunder med navn, e-mail, by, kundetype, oprettelsesdato, antal ordrer og køb i alt. Den blander tekst, tal og datoer, og det er netop det der gør kundedata til god træning. Du tæller med betingelser, slår én kunde op, skiller tekst ad og sætter den sammen igen. Løs opgaverne i filen først, og tjek bagefter dine svar her på siden.

Kundedata: 150 kunder fra en webshop

Arket Kunder har én række for hver kunde og ti kolonner:

Navnene er opdigtede, og flere kunder hedder det samme. Kundenummeret er det eneste der er unikt, så det er det du slår op på. Private kunder bruger mest Gmail, Outlook og lignende, mens erhvervskunder har en adresse på deres firmas eget domæne. Den forskel kommer du til at bruge i tekstopgaverne.

Nogle kunder er oprettet, men har aldrig købt noget. De har 0 i Ordrer og 0 i Køb i alt. Husk dem når du regner gennemsnit ud, for de trækker et gennemsnit ned.

Sådan går du til en opgave

Spørgsmål om kunder gemmer tit et filter. "Hvad køber en erhvervskunde for i gennemsnit?" er et gennemsnit med én betingelse. "Hvor mange erhvervskunder er der i København?" er en optælling med to. Før du skriver noget, så spørg dig selv:

1. Skal du tælle, lægge sammen eller finde et gennemsnit? Det afgør om du skal bruge TÆL.HVIS, SUM.HVIS eller MIDDEL.HVIS.
2. Hvilke kunder tæller med? Hver betingelse i spørgsmålet bliver til et par af et område og en betingelse.
3. Handler det om én kunde? Så er det et opslag, og du starter med kundenummeret.

Når du ved det, er formlen halvt skrevet. Tjek derefter svaret groft: København er den største by i filen, så der skal være flere kunder der end i Esbjerg.

Sådan bruger du filen

Åbn filen, og gå til arket Opgaver. Hver opgave har et nummer, et spørgsmål og en tom celle til dit svar. Skriv formlerne på arket Kunder, i en tom kolonne til højre for dataene, og skriv resultatet ind ud for opgaven. Sammenlign det bagefter med svaret her på siden.

Dataene går fra række 2 til række 151. Svarene bruger præcise områder som E2:E151. Gem en kopi af filen før du sorterer eller sletter noget, for svarene passer kun til dataene som de står i filen. Overskriftsrækken har filterknapper, så du kan tjekke en optælling ved at filtrere og læse antallet af rækker på statuslinjen.

Opgaver med formler på kundedata

Opgaverne går fra enkle optællinger til opslag, tekst og datoer. De står i samme rækkefølge som i filen.

Tæl kunder med én og to betingelser

Antallet af kunder i én by er en helt almindelig TÆL.HVIS (COUNTIF): =TÆL.HVIS(E2:E151;"Aarhus"). Store og små bogstaver er ligegyldige i betingelsen, så "aarhus" giver det samme svar.

Kommer der en betingelse mere til, skal du bruge TÆL.HVISER (COUNTIFS). Erhvervskunder i København er de rækker hvor kundetypen er Erhverv og byen er København:

=TÆL.HVISER(F2:F151;"Erhverv";E2:E151;"København")

Hver betingelse er et par af et område og en betingelse, og en række tæller kun med når alle par passer. Rækkefølgen af parrene er ligegyldig. Øv den i TÆL.HVISER med to betingelser.

Gennemsnitligt køb pr. kundetype

MIDDEL.HVIS (AVERAGEIF) virker som SUM.HVIS: hvor den skal lede, hvad den skal lede efter og hvad den skal finde gennemsnittet af. Det gennemsnitlige køb for erhvervskunder er:

=AFRUND(MIDDEL.HVIS(F2:F151;"Erhverv";I2:I151);0)

AFRUND udenom giver et helt tal. Sammenlign resultatet med samme formel for Privat, så ser du hvorfor de fleste webshops behandler de to kundetyper forskelligt. Øv den i Gennemsnit af én gruppe med MIDDEL.HVIS.

Der er en fælde i gennemsnit på kundedata. Kunderne der aldrig har købt noget, har 0 i Køb i alt, og de er med i gennemsnittet. Vil du kun have gennemsnittet for kunder der har købt, så tilføj en betingelse med MIDDEL.HVISER: =MIDDEL.HVISER(I2:I151;F2:F151;"Erhverv";H2:H151;">0"). Hvilken der er rigtig, afhænger af spørgsmålet. Skriv ned hvilken du brugte.

Slå en kunde op på kundenummeret

XOPSLAG (XLOOKUP) finder en kunde på nummeret og giver dig den kolonne du vil have. E-mailadressen på kunde 2077 er:

=XOPSLAG(2077;A2:A151;D2:D151)

Første argument er det du leder efter, andet er hvor du leder, og tredje er det du vil have tilbage. Skift D2:D151 ud med E2:E151, og du får byen. Kundenummeret står som et tal i filen, så du slår det op som et tal uden anførselstegn. Havde numrene stået som tekst, skulle du skrive "2077" i anførselstegn, og et tal ville ikke passe. Det er en af de mest almindelige grunde til #I/T i rigtige kundelister. Øv opslaget i Slå en pris op med XOPSLAG.

I et rigtigt ark skriver du sjældent nummeret ind i formlen. Sæt det i en celle for sig, for eksempel L1, og peg på den: =XOPSLAG(L1;A2:A151;D2:D151). Nu kan alle skrive et nyt nummer i L1 og se e-mailen skifte. Lav det samme opslag for navn, by og køb ved siden af, så har du et lille kundekort der slår alt op om én kunde fra én celle.

Skriver nogen et nummer der ikke findes, giver kortet #I/T i alle felter. Giv XOPSLAG et fjerde argument, så står der noget pænere: =XOPSLAG(L1;A2:A151;D2:D151;"Ukendt kunde"). Det fjerde argument bliver kun brugt når nummeret mangler, så alle rigtige opslag virker som før. Øv det i XOPSLAG med en værdi for ikke fundet, og prøv bagefter at skrive et nummer over 2150 i L1 for at se det virke.

Sæt for- og efternavn sammen

Et opslag giver én værdi. Skal du have det fulde navn, laver du to opslag og sætter dem sammen med & og et mellemrum i anførselstegn:

=XOPSLAG(2033;A2:A151;B2:B151)&" "&XOPSLAG(2033;A2:A151;C2:C151)

Skal du lave en kolonne med fulde navne til alle kunder, er det nemmere: =B2&" "&C2 i en ny kolonne, kopieret ned. Glemmer du mellemrummet, løber navnene sammen. Øv det i Sæt for- og efternavn sammen med &.

Skil en e-mailadresse ad

Alt efter @ i en e-mailadresse er domænet. FIND giver placeringen af @, og MIDT (MID) tager teksten fra tegnet efter:

=MIDT(D2;FIND("@";D2)+1;50)

Tallet 50 er bare et tal der er stort nok til at nå enden af enhver adresse. MIDT stopper ved tekstens slutning, så det gør ikke noget at bede om for mange tegn. FIND skelner mellem store og små bogstaver og giver #VÆRDI! når tegnet mangler, og det er en hurtig måde at finde en e-mailadresse uden @. Øv den i Find @ i en mailadresse med FIND.

Delen før @ klarer du på samme måde med VENSTRE (LEFT): =VENSTRE(D2;FIND("@";D2)-1) giver alt frem til @, men uden selve @. Prøv det i Hent præfikset med VENSTRE.

Tæl domæner med et jokertegn

Du behøver ikke skille alle adresser ad for at tælle Gmail-brugere. En stjerne i betingelsen til TÆL.HVIS står for en hvilken som helst tekst, så den her tæller alle adresser der ender på @gmail.com:

=TÆL.HVIS(D2:D151;"*@gmail.com")

Skift domænet ud for at tælle Outlook eller Hotmail. Sæt stjernen i begge ender, som i "gmail", så tæller du adresser hvor ordet står et hvilket som helst sted. Jokertegn virker i TÆL.HVIS, SUM.HVIS, MIDDEL.HVIS og deres HVISER-udgaver, og i XOPSLAG hvis du beder om det.

Datoer som betingelse

Kunderne der blev oprettet i 2024, er de rækker hvor datoen er fra 1. januar 2024 og frem til, men ikke med, 1. januar 2025. Det er to betingelser på samme kolonne:

=TÆL.HVISER(G2:G151;">="&DATO(2024;1;1);G2:G151;"<"&DATO(2025;1;1))

Sammenligningen står i anførselstegn, og datoen bliver sat på med &. "Mindre end den første i næste år" er sikrere end "mindre end eller lig med 31. december", fordi den også virker hvis en dato har et klokkeslæt med. Læs mere i guiden om datoer i Excel.

Den største kunde og den gennemsnitlige ordre

Vil du finde den kunde der har købt for mest, så find det højeste køb med MAKS og dets placering med SAMMENLIGN, og hent nummeret fra samme placering med INDEKS:

=INDEKS(A2:A151;SAMMENLIGN(MAKS(I2:I151);I2:I151;0))

Den gennemsnitlige ordrestørrelse er alle køb divideret med alle ordrer, ikke gennemsnittet af hver kundes gennemsnit. =AFRUND(SUM(I2:I151)/SUM(H2:H151);0) giver den i ét hug. De to tal er forskellige, fordi kunder med mange ordrer vejer mere i det første.

Andele og store kunder

Andelen af kunder der får nyhedsbrevet, er antallet af Ja divideret med antallet af kunder:

=AFRUND(TÆL.HVIS(J2:J151;"Ja")/TÆLV(A2:A151);3)

Formatér cellen som procent, så kan du læse den som en procent. Guiden om at regne med procent i Excel har mere om andele.

SUM.HVIS med en talbetingelse lægger købene sammen for de kunder der passer på den. Kunder med 10 ordrer eller flere er den trofaste kerne i de fleste webshops:

=SUM.HVIS(H2:H151;">=10";I2:I151)

Divider med alle køb, så har du den andel af omsætningen der kommer fra dem. Øv betingelsen i SUM.HVIS med en talbetingelse.

Del kunderne i grupper med HVISER

Mange webshops deler kunderne op efter hvor meget de køber, så de bedste kunder får et opkald og resten får en mail. HVISER (IFS) tjekker betingelserne i rækkefølge og giver værdien for den første der er sand:

=HVISER(I2>20000;"Guld";I2>5000;"Sølv";SAND;"Bronze")

Sæt formlen i en ny kolonne ud for række 2, og kopiér den ned. Rækkefølgen betyder noget: tjekker du for mere end 5.000 først, havner alle guldkunder i Sølv, fordi 25.000 også er mere end 5.000. Start med den strengeste betingelse. SAND til sidst fanger alle der er tilbage. Øv opbygningen i Karakterer med HVISER.

Når kolonnen er udfyldt, tæller du hver gruppe med TÆL.HVIS og lægger købene sammen pr. gruppe med SUM.HVIS. I de fleste kundelister står en lille guldgruppe for en stor del af omsætningen. Det er det tal en salgschef vil se først.

Byg en kundeoversigt

Når du har løst opgaverne, så saml det i en lille oversigt. Skriv de fem byer i kolonne L, fra L2 til L6. Ved siden af tæller du kunderne, lægger købene sammen og finder gennemsnittet:

=TÆL.HVIS($E$2:$E$151;L2)

=SUM.HVIS($E$2:$E$151;L2;$I$2:$I$151)

Områderne er låst med $, mens L2 skal flytte sig når du kopierer ned. Tilføj en kolonne der deler antallet op i private kunder og erhvervskunder med TÆL.HVISER, og en række nederst med totalerne. Totalerne skal give antallet af rækker i filen og summen af Køb i alt. Gør de ikke det, er en by stavet forskelligt et sted.

Sådan en oversigt får du med en pivottabel på få klik. Bygger du den én gang med formler, lærer du hvad pivottabellen gør bag kulisserne, og formlerne opdaterer sig selv når dataene ændrer sig.

Ryd op før du tæller

Kundedataene i filen er pæne. Det er rigtige udtræk sjældent. Den samme kunde står to gange med to stavemåder, en by har et mellemrum til sidst, og et kundenummer står som tekst i halvdelen af rækkerne. Hver eneste af dem giver en forkert optælling uden fejlbesked, fordi "Aarhus " og "Aarhus" er to forskellige byer for TÆL.HVIS.

Så før du stoler på en optælling på en rigtig liste, så tjek den: filtrér kolonnen med byer, og kig på listen af værdier. Fem byer skal give fem punkter, ikke syv. Vil du øve at rydde op i den slags, har filen med rodet data det hele, med opgaver og svar.

Kundelister dukker også op i Excel-tests til job i salg, marketing og kundeservice. Spørgsmålene ligner dem her: en optælling med to betingelser, et gennemsnit pr. gruppe, et opslag på et nummer og en andel. Læs mere i guiden om Excel til jobsamtalen.

Øvelser uden facit

De her øvelser har ikke ét tal som svar, men det er dem du laver med en kundeliste på et job:

• Lav en kolonne med fulde navne med =B2&" "&C2 og en kolonne med domæner med MIDT og FIND. Sortér derefter efter domæne.
• Lav en tabel pr. by med de fem byer i én kolonne og TÆL.HVISER for private kunder og erhvervskunder ved siden af. Lås områderne med $, så du kan kopiere i begge retninger.
• Find kunder der er gået i stå: oprettet før 2023 og med 0 ordrer. Filtrér på begge kolonner, eller tæl dem med TÆL.HVISER.
• Find nye kunder pr. år. Skriv årstallene 2021 til 2025 i en kolonne, og tæl kunderne for hvert år med TÆL.HVISER og DATO. Lav et søjlediagram af resultatet, så kan du se om webshoppen vokser.
• Find de kunder der ikke vil have nyhedsbrevet, men køber meget. Filtrér på Nej i Nyhedsbrev, og sortér efter Køb i alt med de største øverst. Det er dem marketing gerne vil have fat i.
• Fremhæv de ti bedste kunder med betinget formatering på kolonnen Køb i alt. Guiden om betinget formatering viser hvordan.
• Lav en pivottabel med byer i rækkerne, kundetyper i kolonnerne og summen af Køb i alt som værdier.

Når et svar ikke passer

Her er de fejl der oftest giver et forkert tal på kundedata:

• Et tal slået op som tekst. "2077" i anførselstegn passer ikke med tallet 2077 i kolonne A. Drop anførselstegnene.
• Kunder uden ordrer glemt. De er med i alle gennemsnit, medmindre du bevidst lader dem ude.
• En manglende stjerne. "@gmail.com" uden stjerne tæller kun celler hvor der står præcis @gmail.com, og det er ingen.
• Datoer skrevet som tekst. ">=01-01-2024" kan blive læst anderledes på en anden computer. Brug DATO.
• Det forkerte gennemsnit. Gennemsnitligt køb pr. kunde og gennemsnitlig ordrestørrelse er to forskellige tal. Læs spørgsmålet to gange.

Kundedata i Google Sheets

Filen virker i Google Sheets. Upload den til Google Drive, og åbn den med Google Sheets. Alle opgaverne kan løses med de samme formler, og Google Sheets har desuden SPLIT, som skiller en e-mailadresse ad ved @ i ét trin. Se forskellene i Google Sheets eller Excel.

Flere øvelsesdata

Filen er en af flere gratis datasæt med opgaver og svar:

• Salgs- og medarbejderdata: 240 ordrer og 60 medarbejdere til SUM.HVIS, TÆL.HVISER, gennemsnit og datoer. Salgsfilen bliver også brugt i øvelserne med pivottabeller.
• Lagerdata: 80 varer med lager og priser til XOPSLAG og SUMPRODUKT.
• Rodet data: 60 ordrer fulde af ekstra mellemrum, tal som tekst og dubletter, til at øve oprydning.
• Regnskabsdata: et års bankposteringer til indtægter, udgifter, resultat og procenter.

Kort sagt

En kundeliste blander tal, tekst og datoer, og det gør den til god træning. TÆL.HVIS og TÆL.HVISER tæller kunder, MIDDEL.HVIS og SUM.HVIS regner køb ud pr. gruppe, og XOPSLAG finder én kunde på nummeret. FIND, MIDT og VENSTRE skiller en e-mailadresse ad, og & sætter et navn sammen igen. Løs opgaverne i filen, tjek dine svar her, og find ud af hvor det gik galt når et tal ikke passer.

Vil du øve én formel ad gangen på et lille ark, så start med opgaverne herunder. De tjekker dit svar med det samme og giver et tip når formlen er forkert. Den første opgave i hvert emne er gratis.