Lagerdata til Excel: gratis øvelsesfil med opgaver og svar

På et lager er Excel ofte det værktøj der holder styr på det hele. Nogen skal vide hvad der står på hylderne, hvad det er værd, og hvad der skal bestilles før det slipper op. Tit er det en person med et regneark på et par hundrede rækker og en håndfuld formler. Her får du en lagerliste du kan øve de formler på, med opgaver der har et facit.

Filen er lagerlisten hos en lille grossist i kontorartikler: 80 varer fra kuglepenne til hæve-sænkeborde, fordelt på seks kategorier og fem leverandører. Hver vare har et varenummer, antal på lager, et genbestillingspunkt, en kostpris og en salgspris. Løs opgaverne i filen først, og tjek bagefter dine svar under hver opgave her på siden.

Lagerdata: 80 varer i seks kategorier

Arket Lager har én række for hver vare og ni kolonner:

Varenummeret er den eneste kolonne hvor hver værdi kun findes én gang, så det er den du slår op i. I den her fil er varenavnene også unikke, men på et rigtigt lager kan to varer sagtens hedde det samme, for eksempel to kontorstole fra hver sin leverandør. Vænn dig til at slå koden op.

Dataene er pæne med vilje: én overskriftsrække, ingen tomme rækker og tal der står som rigtige tal. Vil du hellere øve oprydning, så tag filen med rodet data.

De tre tal en lagerliste skal kunne give

Næsten alle spørgsmål om et lager handler om en af tre ting:

1. Hvad har vi? Et antal eller en sum af stk., tit med en betingelse: varer i én kategori, stk. fra én leverandør, varer der er udsolgt.
2. Hvad er det værd? Antal gange en pris, lagt sammen. Til kostpris er det værdien i regnskabet. Til salgspris er det hvad lageret kan indbringe.
3. Hvad skal vi bestille? En sammenligning mellem to kolonner i samme række: er lageret nede på eller under genbestillingspunktet?

Det første klarer TÆL.HVIS og SUM.HVIS. Det andet og det tredje kræver en formel der regner række for række, før den lægger sammen, og her kommer SUMPRODUKT ind i billedet. Opslag binder det hele sammen: når nogen spørger til én vare, finder du dens række med XOPSLAG.

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 Lager, i en tom kolonne til højre for dataene, og skriv resultatet ind ud for opgaven. Tjek det bagefter mod svaret her på siden.

Dataene går fra række 2 til række 81. Svarene her på siden bruger præcise områder som E2:E81, fordi de er nemme at læse. Gem en kopi af filen før du sorterer eller sletter noget. Svarene passer kun til dataene som de står i filen.

Du kan også bruge hele kolonner som E:E i stedet for E2:E81. Så virker formlen stadig når der kommer nye varer på listen, og det er tit sådan en lagerliste vokser: en ny række nederst hver gang indkøb tager en ny vare ind. SUM.HVIS og TÆL.HVIS springer overskriften over af sig selv, men i SUMPRODUKT skal alle områder være lige lange, så der er det nemmest at holde sig til de præcise områder.

Overskriftsrækken har filterknapper. De er en hurtig måde at tjekke et svar med øjnene: filtrér på Møbler, markér kolonnen På lager, og læs summen på statuslinjen. Er statuslinjen og din formel uenige, har en af dem en betingelse du ikke mente.

Opgaver med formler på lagerdata

Opgaverne starter med en enkelt funktion og bygger op til formler der sammenligner kolonner og ganger dem. De står i samme rækkefølge som i filen.

Tæl og læg sammen med én betingelse

De første opgaver er opvarmning. TÆL.HVIS (COUNTIF) tæller de rækker der passer på en betingelse, så antallet af varer i én kategori er =TÆL.HVIS(C2:C81;"Elektronik"). Betingelsen kan også være et tal: =TÆL.HVIS(E2:E81;0) tæller de varer der ikke er nogen af på hylden.

SUM.HVIS (SUMIF) lægger sammen i stedet for at tælle. De tre argumenter er hvor den skal lede, hvad den skal lede efter og hvad den skal lægge sammen: =SUM.HVIS(D2:D81;"TechLine";E2:E81) lægger antal stk. på lager sammen for én leverandør. De to områder skal starte i samme række og være lige store. Er de forskudt én række, brokker SUM.HVIS sig ikke. Den lægger bare de forkerte rækker sammen. Øv den i SUM.HVIS med en tekst som betingelse.

To betingelser med TÆL.HVISER

"Hvor mange kontorartikler er ved at slippe op?" er to betingelser: kategorien og lagerbeholdningen. TÆL.HVISER (COUNTIFS) tager dem i par af område og betingelse:

=TÆL.HVISER(C2:C81;"Kontorartikler";E2:E81;"<100")

En række tæller kun med når begge betingelser passer. Sammenligningen står i anførselstegn uden mellemrum og uden tusindtalsseparator. Prøv samme opbygning i TÆL.HVISER med to betingelser.

Slå en vare op på varenummeret

Når en kollega spørger til prisen på SKU-1042, ruller du ikke ned. Du slår den op:

=XOPSLAG("SKU-1042";A2:A81;H2:H81)

XOPSLAG (XLOOKUP) skal vide hvad den skal finde, hvor den skal finde det, og hvad den skal give tilbage. Den leder efter et præcist match, medmindre du beder om noget andet, så du skal ikke bruge det FALSK som LOPSLAG kræver. Skift H2:H81 ud med G2:G81, og du får kostprisen i stedet. Øv den i Slå en pris op med XOPSLAG.

Et varenummer der ikke står på listen, giver #I/T. Det er korrekt, men for den der læser arket, ligner det en fejl. XOPSLAG har et fjerde argument til netop det tilfælde:

=XOPSLAG("SKU-1099";A2:A81;B2:B81;"Findes ikke")

Nu står der Findes ikke i cellen når varenummeret mangler, og varenavnet når det findes. Det er den version du skal bruge i et ark andre skriver i. Øv den i XOPSLAG med en værdi for ikke fundet.

Pas på med ét: et varenummer skrevet med et mellemrum til sidst, som "SKU-1042 ", passer ikke. Giver et opslag der burde virke, #I/T, så tjek cellen du slår op fra, før du tjekker formlen.

Lagerværdi med SUMPRODUKT

Lagerværdien er antal gange kostpris for hver vare, lagt sammen. Du kunne lave en kolonne med =E2*G2, kopiere den ned og summere den. Det virker, men SUMPRODUKT (SUMPRODUCT) klarer det i én celle:

=SUMPRODUKT(E2:E81;G2:G81)

SUMPRODUKT ganger den første celle i hvert område, så den anden og så videre, og lægger alle resultaterne sammen. Områderne skal være lige store, ellers giver formlen #VÆRDI!. Skift G2:G81 ud med H2:H81, og du får værdien til salgspris i stedet. Forskellen mellem de to er den bruttoavance der står på hylderne. Øv idéen i Ordretotal med SUMPRODUKT.

Lagerværdi for én kategori

Skal du kun have værdien af Møbler, skal der en betingelse ind i SUMPRODUKT. En sammenligning på et område giver en liste med SAND og FALSK, og når du ganger med den, tæller SAND som 1 og FALSK som 0:

=SUMPRODUKT((C2:C81="Møbler")*E2:E81*G2:G81)

Rækker uden for Møbler bliver ganget med 0 og forsvinder. Rækker i Møbler bliver ganget med 1 og bliver. Sæt endnu en betingelse i parentes, og du kan have så mange betingelser du vil, helt uden SUM.HVISER.

Sammenlign to kolonner: hvad skal bestilles

Spørgsmålet om genbestilling er anderledes end de andre. Betingelsen er ikke en fast værdi. Det er et nyt tal i hver række: er lageret for den her vare nede på eller under genbestillingspunktet for den her vare? Det kan TÆL.HVIS ikke klare, fordi dens betingelse er én værdi for hele området. Det kan SUMPRODUKT:

=SUMPRODUKT(--(E2:E81<=F2:F81))

Sammenligningen tjekker alle 80 rækker på én gang og giver SAND eller FALSK for hver. De to minustegn laver SAND om til 1 og FALSK om til 0, og SUMPRODUKT lægger dem sammen. Det dobbelte minus ser du tit i formler fra folk der bruger Excel hver dag.

Vil du hellere se det række for række, så lav en kolonne med =HVIS(E2<=F2;"Bestil";"OK"), kopiér den ned, og tæl rækkerne med Bestil. Det er en fin måde at tjekke svaret fra SUMPRODUKT på, og det er præcis sådan en kolonne en indkøber bruger. Sæt et filter på den, og du har ugens bestillingsliste.

Find den dyreste vare

MAKS (MAX) giver dig den højeste salgspris, men spørgsmålet er hvilken vare den hører til. Det svarer INDEKS (INDEX) og SAMMENLIGN (MATCH) på sammen:

=INDEKS(B2:B81;SAMMENLIGN(MAKS(H2:H81);H2:H81;0))

SAMMENLIGN finder placeringen af den højeste pris i kolonne H, og INDEKS giver varenavnet på samme placering i kolonne B. Nullet betyder præcist match. Mønsteret virker til alle spørgsmål af typen "hvilken række har den største": skift MAKS ud med MIN, og du finder den billigste. Øv SAMMENLIGN alene i Find en placering med SAMMENLIGN.

Dækningsgrad på én vare

Dækningsgraden er den del af salgsprisen du beholder når varen er betalt: én minus kostpris divideret med salgspris. For ét varenummer er det to opslag i samme formel:

=AFRUND(1-XOPSLAG("SKU-1010";A2:A81;G2:G81)/XOPSLAG("SKU-1010";A2:A81;H2:H81);3)

Formatér cellen som procent, så kan du læse den i procent. Bland ikke dækningsgrad og avanceprocent sammen. Avanceprocenten er fortjenesten divideret med kostprisen, og den er altid den største af de to. Spørg hvilken der menes, når chefen beder om "avancen".

Samme regnestykke virker på hele lageret. Lagerværdien til salgspris minus lagerværdien til kostpris er den bruttoavance der står på hylderne:

=SUMPRODUKT(E2:E81;H2:H81)-SUMPRODUKT(E2:E81;G2:G81)

Divider resultatet med lagerværdien til salgspris, og du har dækningsgraden for hele lageret. Den ligger tæt på gennemsnittet af varernes dækningsgrad, men ikke helt, fordi de dyre varer med mange stk. på lager vejer mere. Prøv at regne begge tal ud, og se hvor stor forskellen er.

Datoer som betingelse

Kolonnen Senest modtaget har rigtige datoer, så du kan sammenligne dem. Varer der sidst blev leveret før 1. juli, er dem der har stået længst uden en ny levering:

=TÆL.HVIS(I2:I81;"<"&DATO(2025;7;1))

Sammenligningen står i anførselstegn, og datoen bliver sat på med &. DATO (DATE) laver datoen ud fra år, måned og dag, så formlen virker uanset hvordan datoer bliver vist på din computer. Læs mere i guiden om datoer i Excel.

Vil du se hvor længe hver vare har ventet siden sidste levering, så træk datoen fra årets sidste dag: =DATO(2025;12;31)-I2 giver antal dage for varen i række 2. Kopiér formlen ned, og sortér efter den nye kolonne, så ligger de langsomme varer øverst. I en fil du bruger hver dag, skriver du IDAG() i stedet for en fast dato, så tallet vokser af sig selv hver morgen.

Tjek dine svar som en lagerchef

En formel der giver et tal, er ikke det samme som en formel der giver det rigtige tal. Folk der passer et lager til daglig, har et par vaner der fanger de fleste fejl før andre ser dem:

• Går det op? Antal stk. pr. leverandør skal give det samme som det samlede antal på lager. Fem resultater fra SUM.HVIS der ikke passer med én SUM, betyder en stavefejl i et leverandørnavn.
• Er størrelsen rimelig? Lagerværdien i én kategori skal være mindre end totalen, og Møbler skal være meget mere værd pr. stk. end Papir. Et svar der er ti gange for stort, er som regel et område der går én kolonne for langt.
• Kan du finde ét eksempel? Siger formlen at en vare skal bestilles, så filtrér listen, og kig på en af dem. Siger den at intet skal bestilles, så vær mistænksom.
• Passer antallet med filteret? Filtrér på en kategori, og sammenlign antallet af rækker på statuslinjen med din TÆL.HVIS.

Lageropgaver er populære i Excel-tests til job i indkøb, logistik og detail, og spørgsmålene ligner dem her på siden: et opslag, en optælling med to betingelser, en lagerværdi og en liste over hvad der skal bestilles. Læs mere om hvad der bliver testet, i guiden om Excel til jobsamtalen.

Øvelser uden facit

Ikke alt på en lagerliste er ét tal. De her øvelser træner det du ville gøre med filen på et job:

• Lav en bestillingsliste. Tilføj en kolonne der skriver Bestil når lageret er nede på eller under genbestillingspunktet, og filtrér på den. Tilføj en kolonne mere med hvor mange der skal bestilles for at komme op på det dobbelte af genbestillingspunktet.
• Lav en oversigt pr. kategori med de seks kategorier i én kolonne og TÆL.HVIS til antal varer og SUMPRODUKT til lagerværdien ved siden af. Lås områderne med $, så du kan kopiere formlerne ned.
• Fremhæv lave lagre med betinget formatering: en regel med en formel som =$E2<=$F2 farver hele rækken. Se hvordan i guiden om betinget formatering.
• Sortér efter lagerværdi. Tilføj en kolonne med antal gange kostpris, og sortér listen med det mest værdifulde lager øverst. På de fleste lagre står nogle få varer for det meste af værdien.
• Lav en pivottabel med leverandører i rækkerne og summen af antal på lager som værdier. Tilføj bagefter kategorien som et ekstra rækkefelt.
• Lav dataene om til en tabel med Ctrl+T, og skriv formlen til genbestilling med kolonnenavne i stedet for områder.

Når et svar ikke passer

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

• Områder i forskellig størrelse. SUMPRODUKT giver #VÆRDI! når E2:E81 møder G2:G80. Tjek at alle områder slutter i række 81.
• Et fast tal i stedet for rækkens tal. "Under genbestillingspunktet" er et nyt tal for hver vare. TÆL.HVIS med "<20" svarer på et andet spørgsmål.
• Opslag på navnet i stedet for koden. To varer kan hedde det samme, og XOPSLAG giver den første den finder. Slå varenummeret op.
• Dækningsgrad og avanceprocent byttet om. Divider med salgsprisen for dækningsgraden og med kostprisen for avanceprocenten.
• En stavefejl i betingelsen. "Elektronic" giver 0 uden nogen fejlbesked. Kopiér teksten fra dataene, eller peg på en celle der indeholder den.

Lagerdata i Google Sheets

Filen virker i Google Sheets. Upload den til Google Drive, og åbn den med Google Sheets. XOPSLAG, SUMPRODUKT, TÆL.HVISER og INDEKS med SAMMENLIGN virker på samme måde, og filterknapperne følger med. Se de andre forskelle i Google Sheets eller Excel.

Flere øvelsesdata

Filen er en af flere gratis datasæt med opgaver og svar. Hvert datasæt træner en ny slags formler:

• 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.
• Kundedata: 150 kunder til opslag, tekstfunktioner og optælling med to betingelser.
• 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 lagerliste stiller tre spørgsmål: hvad har vi, hvad er det værd, og hvad skal vi bestille? TÆL.HVIS, TÆL.HVISER og SUM.HVIS svarer på det første. SUMPRODUKT svarer på det andet og det tredje, fordi den kan gange og sammenligne række for række. XOPSLAG og INDEKS med SAMMENLIGN finder den ene vare nogen spørger til. Løs opgaverne i filen, tjek dine svar her, og find fejlen 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.