Når du kopierer en formel, flytter Excel cellereferencerne med. Det er smart for beløbet i samme række, men forkert for en celle, der skal bruges af alle formlerne, fx en momssats. Sæt $ foran kolonne og række, så bliver referencen stående:
En reference med $ foran både kolonne og række kaldes en absolut reference (engelsk: absolute reference), og tasten F4 sætter dollartegnene for dig. Men $ beskytter ikke cellen mod at blive rettet: det gør arkbeskyttelse under Gennemse › Beskyt ark, hvor cellerne er låst på forhånd, men hvor låsen ifølge Microsoft først virker, når arket er beskyttet.
Først er det værd at se, hvad der går galt uden $.
Tre forskellige fejl fra én formel
Arket her har momssatsen på 25 % i B1 og beløb uden moms i kolonne A. Formlen =A4*B1 er skrevet i B4 og kopieret ned. Ved siden af står den samme formel med $B$1:
| Række | A | B | C | D |
|---|---|---|---|---|
| 3 | Beløb ekskl. moms | Moms (uden $) | Moms (med $B$1) | I alt |
| 4 | 1.000,00 | 250,00 | 250,00 | 1.250,00 |
| 5 | 250,00 | 0,00 | 62,50 | 312,50 |
| 6 | 80,00 | #VÆRDI! | 20,00 | 100,00 |
| 7 | 1.999,95 | 499.987,50 | 499,99 | 2.499,94 |
Første række er rigtig. Resten går galt på hver sin måde, fordi referencen til momssatsen er rykket med ned:
| Celle | Formlen blev til | Den peger på | Resultat |
|---|---|---|---|
| B5 | =A5*B2 |
En tom celle | 0,00 |
| B6 | =A6*B3 |
Overskriften “Moms (uden $)” | #VÆRDI! |
| B7 | =A7*B4 |
Momsen i rækken ovenover | 499.987,50 |
Den farligste er B5. Der kommer ingen fejl, momsen er bare 0. I kolonne C står =A5*$B$1, =A6*$B$1 osv. hele vejen ned, og alle beløb er rigtige. (Satsen på 25 % er den danske standardsats ifølge Skattestyrelsen. Mere om selve momsberegningen i guiden om moms i Excel.)
Sådan tænker Excel om en reference
En almindelig reference som B1 i cellen B4 betyder for Excel “cellen tre rækker oppe i samme kolonne”. Kopierer du formlen én række ned, peger den stadig tre rækker op, og så bliver det B2. $ foran kolonnebogstavet eller rækkenummeret gør den del fast.
| Reference | Når du kopierer nedad | Når du kopierer til højre | Bruges typisk til |
|---|---|---|---|
B1 |
Rækken ændres | Kolonnen ændres | Værdier i samme række |
$B$1 |
Uændret | Uændret | Én fast celle: sats, pris, dato |
$B4 |
Rækken ændres | Uændret | En kolonne med værdier pr. række |
B$3 |
Uændret | Kolonnen ændres | En overskriftsrække |
De to sidste kaldes blandede referencer. De er dem, man oftest mangler, når man kopierer i begge retninger.
Lås kun kolonnen: timeløn gange timer
Her har hver person sin timeløn i kolonne B og timer for tre uger i C til E. Lønnen skal beregnes i G til I, og formlen skrives én gang i G4 og kopieres til hele området:
| Række | A | B | C | D | E | F | G | H | I | J | K | L | M |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 3 | Navn | Timeløn | Timer uge 1 | Timer uge 2 | Timer uge 3 | Løn uge 1 | Løn uge 2 | Løn uge 3 | Uden $: uge 1 | Uden $: uge 2 | Uden $: uge 3 | ||
| 4 | Anna | 185,00 | 37 | 30 | 12,5 | 6.845,00 | 5.550,00 | 2.312,50 | 6.845,00 | 1.110,00 | 375,00 | ||
| 5 | Bo | 172,50 | 20 | 37 | 37 | 3.450,00 | 6.382,50 | 6.382,50 | 3.450,00 | 740,00 | 1.369,00 | ||
| 6 | Cecilie | 210,00 | 8 | 16 | 24 | 1.680,00 | 3.360,00 | 5.040,00 | 1.680,00 | 128,00 | 384,00 |
Uden $ blev formlen i L4 til =C4*D4, altså timer i uge 1 gange timer i uge 2. Beløbet 1.110,00 ser ikke umiddelbart forkert ud, og netop derfor er fejlen let at overse. Med $B4 blev H4 til =$B4*D4, som den skulle.
Timerne står her som almindelige tal. Kommer de fra en timeseddel med klokkeslæt, skal de først regnes om til timer som decimaltal fra en timeseddel, før de kan ganges med timelønnen.
Lås rækken og kolonnen hver for sig: en rabattabel
Skal du bygge en tabel, hvor rækkerne er antal og kolonnerne rabatsatser, har formlen brug for alle tre slags referencer på én gang:
| Række | A | B | C | D | E |
|---|---|---|---|---|---|
| 3 | Antal / rabat | 0% | 5% | 10% | 15% |
| 4 | 10 | 499,50 | 474,53 | 449,55 | 424,58 |
| 5 | 25 | 1.248,75 | 1.186,31 | 1.123,88 | 1.061,44 |
| 6 | 50 | 2.497,50 | 2.372,63 | 2.247,75 | 2.122,88 |
| 7 | 100 | 4.995,00 | 4.745,25 | 4.495,50 | 4.245,75 |
I E7 var formlen blevet til =$A7*$B$1*(1-E$3). Antallet er hentet fra række 7, rabatten fra kolonne E, og stykprisen er den samme som i B4.
En god huskeregel: $ står foran det, der ikke må flytte sig. Antallene står i kolonne A, så kolonnen låses. Rabatterne står i række 3, så rækken låses.
F4 sætter dollartegnene
Ifølge Microsoft kan du markere en reference i formellinjen og trykke F4 for at skifte mellem referencetyperne. Hvert tryk giver den næste type: B1, $B$1, B$1, $B1 og tilbage til B1. Det er hurtigere og giver færre tastefejl end at skrive $ selv.
$ låser ikke cellen, kun kopieringen
Et dollartegn betyder “ændr ikke referencen, når formlen kopieres”. Det betyder ikke, at referencen altid peger på B1. Vi har afprøvet, hvad der sker med =A4*$B$1, når arket ændres:
| Det gjorde vi | Formlen blev til | Resultat |
|---|---|---|
| Indsatte en række øverst | =A5*$B$2 |
Stadig rigtig: 250 |
| Flyttede momssatsen fra B1 til H1 (klip og sæt ind) | =A4*$H$1 |
Stadig rigtig: 250 |
| Slettede rækken med momssatsen | =A3*#REFERENCE! |
#REFERENCE! |
| Kopierede formlen fra B4 til E8 | =D8*$B$1 |
Beløbsdelen flyttede med |
Excel følger altså cellen, hvis den flytter. Men slettes den, er referencen væk, og alle formlerne viser #REFERENCE!.
Der er også forskel på at kopiere og at flytte selve formlen. Formlen =A4+B4 kopieret til F10 blev til =D10+E10. Formlen =A5+B5 klippet og sat ind i F12 blev ved med at være =A5+B5. Når du flytter en formel, bliver referencerne stående, også uden $.
Når $ bliver besværligt
Har du mange formler, der peger på den samme kolonne i en liste, er en Excel-tabel ofte nemmere. I en tabel skriver du [@Timer]*[@Timeløn] i stedet for cellereferencer, og formlen fylder selv ned. Se Excel-tabeller og strukturerede referencer.
Det samme problem med relative referencer dukker op i betinget formatering, hvor en regel uden de rigtige $ farver de forkerte celler. Det gennemgår vi i guiden om betinget formatering med formler.