Njuike sisdollui
Reaiddut ja málle

Sparing: kopiering av regnearkceller. Faste celleadresser

Regneark er gode å bruke når vi skal regne på sparing. Her får vi nytte av måten regnearkceller blir kopiert på.
Sparegris med mynter rundt seg. Foto.
Govva: Bård Løken / CC BY-NC-SA 4.0

Eksempel: Årlig sparing

Det er andre nyttårsdag, og Jonas fikk av slekten til sammen 2 500 kroner i julegave som han skal sette inn på en ny sparekonto. Jonas pleier å få penger av flere av slektningene sine til jul. Han regner med å kunne sette inn penger fra julegaver ennå i 5 år til, for da er han ferdig med videregående skole.

For å finne ut hvor mye han vil ha på konto om 5 år, går han ut ifra at han vil få omtrent 300 kroner mer i julegave for hvert år. Han har en sparekonto der han får 2,5 prosent årlig rente.

Oppgave 1

Hva kommer Jonas til å sette inn om to år?

Løsningsforslag

Etter to år vil julegavesummen ha økt med 300 kroner to ganger. Han vil derfor om to år kunne sette inn

2 500 kr+2·300 kr=3 100 kr

Oppgave 2

Lag en formel/funksjon S(x) som regner ut hva Jonas kommer til å sette inn om x år.

Løsningsforslag

Vi bruker den samme framgangsmåten som i oppgaven ovenfor og får

S(x)=2 500+x·300=2 500+300x

Enkel formelkopiering

Vi skal nå lage et regneark som viser innskuddene (beløpene han skal sette inn) nå og 5 år framover.

Regneark der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A6 er overskriften år nummer og i celle B6 er overskriften innskudd. Det første årsnummeret er 0 i celle A7. Skjermutklipp.
Govva: Bjarne Skurdal / CC BY-SA 4.0

Start med å lage et inndataområde med opplysningene i problemet. Legg inn det første innskuddet på 2 500 kroner i celle B2 og den årlige økningen på 300 kroner i celle B3. Så trenger vi renten i celle B4. Regnearket skal se ut som på bildet. Bruk regnskapsnummerformat på celler som skal ha pengeverdier (se siden Lønnsutregninger – bruk av celleadresser).

Vi skal bruke formler for å lage årsnumrene i kolonne A i stedet for å skrive dem inn manuelt. Vi trenger tallene fra og med 0 til og med 5. Vi starter årsnumrene på 0 siden det første innskuddet skal skje nå. Start med å skrive 0 i rute A7.

Vi vet at årsnummeret øker med 1 for hvert år. I celle A8 skriver vi derfor =A7+1 for at innholdet skal bli én større enn i cellen ovenfor. Kontroller at det blir riktig årsnummer, altså 1, i celle A8.

Vi kan bruke tilsvarende formel for celle A9 til og med A12 som den i celle A8. Betyr det at vi kan kopiere formelen i A8 til cellene A9 til A12 med vanlig kopiering? Problemet er at vi i formelen for celle A9 må bytte ut "A7" med "A8", men regneark er smarte og går automatisk ut ifra at vi vil oppdatere formelen for hver celle den skal limes inn i. Gjør derfor følgende:

  • Kopier celle A8.
  • Marker de cellene formelen skal inn i (A9 til A12).
  • Velg "Lim inn", for eksempel ved å trykke Ctrl-V.
Regneark der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A6 er overskriften år nummer og i celle B6 er overskriften innskudd. Det første årsnummeret er 0 i celle A7. Det siste årsnummeret er 5 i celle A12. Skjermutklipp.
Govva: Bjarne Skurdal / CC BY-SA 4.0

Kontroller at årsnumrene blir riktige.

En annen måte å kopiere formelen i A8 på er følgende:

  • Stå med markøren i celle A8 (den cellen vi ønsker å kopiere formelen fra).
  • Ta tak i det nederste høyre hjørnet av cella (som har en ekstra markering) med musepekeren, og dra hjørnet nedover til celle A12 er markert. Slipp musepekeren.

Oppgave 3

Trykk på "Vis formler" i regnearket for å se på formlene i kolonne A. Skriv en liten tekst som beskriver hvordan formelkopiering foregår i et regneark. Bruk som eksempel hva som skjer med formelen når den blir kopiert til celle A11.

Løsningsforslag

Når regnearket limer inn en formel i en celle, vil alle celleadresser i formelen endres etter hvor langt unna cellen er i forhold til cellen med den opprinnelige formelen. I celle A11 prøver vi å lime inn formelen =A7+1. Formelen står opprinnelig i celle A8, og A11 ligger 3 rader rett under A8. Det betyr at "A7" i formelen må skiftes 3 rader nedover og blir til "A10". Formelen i celle A11 blir derfor =A10+1.

Regneark og formelvisning av regnearket der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A6 er overskriften år nummer og i celle B6 er overskriften innskudd. Det første årsnummeret er 0 i celle A7. Det siste årsnummeret er 5 i celle A12. Tallet i celle A8 er laget med formelen =A7 pluss 1, og resten av årsnummerene nedenfor er laget ved å kopiere denne formelen. Skjermutklipp.
Govva: Bjarne Skurdal / CC BY-SA 4.0

Merk at dersom vi hadde prøvd å lime inn formelen i celle B11, ville i tillegg kolonnebokstaven "A" i formelen ha blitt skiftet til "B". Formelen i celle B11 ville ha blitt "=B10+1". Prøv!

Oppgave 4

Så skal vi regne ut innskuddene for alle disse årene. I år nummer null vet vi at Jonas skal sette inn 2 500 kroner. Hva slags regnearkformel bruker vi i celle B7?

Fasit

Her skal vi rett og slett overføre tallet i celle B2. Da skriver vi "=B2" i celle B7 for å kopiere innholdet fra celle B2.

Oppgave 5

Bruk formelen/funksjonen S(x) til å lage en regnearkformel i celle B8 som regner ut hva Jonas skal sette inn neste år.

Løsningsforslag

Formelen/funksjonen S(x) sier at vi skal ta det første innskuddet og legge til 300 multiplisert med årsnummeret. Det første innskuddet står i celle B2, det faste tillegget i celle B3, og årsnummeret står i celle A8. Da skriver vi =B2+B3*A8 i celle B8. Kontroller at du får riktig svar i cellen.

Oppgave 6

Nå hadde det vært fint om det gikk an å kopiere formelen i B8 nedover til cellene B9 til B12. Prøv dette. Ble det riktig svar i disse cellene? Trykk på "Vis formler", og forklar eventuelt hvorfor det ikke ble riktige svar.

Løsning
Eksempel på feil ved formelkopiering. Formelvisning av regnearket der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A6 er overskriften år nummer og i celle B6 er overskriften innskudd. Det første årsnummeret er 0 i celle A7. Det siste årsnummeret er 5 i celle A12. Tallet i celle A8 er laget med formelen =A7 pluss 1, og resten av årsnummerene nedenfor er laget ved å kopiere denne formelen. Formlene fra og med celle B9 til og med celle B12 er feil på grunn av feil bruk av formelkopiering av formelen i celle B8. Skjermutklipp.
Feil formel i cellene B9 - B12 pga feil bruk av formelkopiering. Govva: Bjarne Skurdal / CC BY-SA 4.0

Det blir ikke riktige svar. I noen av cellene kommer det bare en feilmelding. Årsaken til det er at regnearket tror at vi skal endre alle celleadressene i formelen i takt med plasseringen av cellene som formelen skal limes inn i. Men vi ønsker å bruke tallene i cellene B2 og B3 i alle cellene! Det er bare den siste celleadressen, A8, vi ønsker skal endre seg.

Faste celleadresser

Heldigvis er det en enkel måte å fikse problemet på i den forrige oppgaven. Når vi skal kopiere formler som inneholder celleadresser som vi ikke ønsker skal endres under kopieringa, gjør vi det ved å sette et dollartegn "quot; foran kolonnebokstaven og foran radnummeret i celleadressen. Dersom vi for eksempel ikke ønsker at celleadressen B2 skal endres når vi kopierer en formel, skriver vi $B$2 i formelen i stedet for B2 før vi kopierer den. Da blir dette en fast celleadresse.

Oppgave 7

Hva blir formelen i celle B8 slik at den kan kopieres rett til cellene B9 til B12? Kopier formelen, og kontroller at svarene blir riktige.

Løsningsforslag

Formelen i celle B8 blir =$B$2+$B$3*A8. Når denne blir kopiert, er det bare celleadressen A8 som vil endre seg i formelen når vi limer den inn i cellene B9 til B12.

Regneark og formelvisning av regnearket der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A6 er overskriften år nummer og i celle B6 er overskriften innskudd. Det første årsnummeret er 0 i celle A7. Det siste årsnummeret er 5 i celle A12. Tallet i celle A8 er laget med formelen =A7 pluss 1, og resten av årsnummerene nedenfor er laget ved å kopiere denne formelen. I formlene for innskudd er det brukt faste celleadresser med dollartegn for at formelkopieringen skal fungere. Skjermutklipp.
Govva: Bjarne Skurdal / CC BY-SA 4.0

Vi ser at innskuddene øker med 300 for hvert år, som rett er.

Så må vi regne ut hvor mye Jonas har i banken 5 år fram i tid. Her har vi lagt opp til at vi i kolonne C skal regne ut hva hvert innskudd er verdt om 5 år. Hvert år et av innskuddene står i banken, vil verdien av det øke med 2,5 prosent. Det tilsvarer at vi multipliserer med vekstfaktoren ved 2,5 prosent økning.

Oppgave 8

Hva blir vekstfaktoren ved en økning på 2,5 prosent?

Løsningsforslag

Vekstfaktoren ved 2,5 prosent økning finner vi ved å regne ut

1+2,5100=1+0,025=1,025

Oppgave 9

Det passer å legge inn formelen for vekstfaktoren i celle B5 som en del av inndataområdet selv om vi må gjøre en utregning. Hva skriver vi i denne cellen?

Løsningsforslag

Vi skriver =1+B4/100. Kontroller at resultatet blir 1,025.

Nå er det ikke noen tom rad mellom inndataområdet og utregningene lenger. Dersom vi vil, kan vi legge til en ny rad i regnearket ved å høyreklikke på 6-tallet i radoverskriften til rad 6 og legge inn en ny rad over for å få litt luft. Så skriver vi inn overskriften "Verdi om 5 år" i celle C7 (ved siden av overskriften "Innskudd"). Resultatet blir som på bildet nedenfor.

Regneark og formelvisning av regnearket der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Vekstfaktoren på 1,025 er regnet ut nedenfor. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A7 er overskriften år nummer og i celle B7 er overskriften innskudd. I celle C7 er overskriften verdi om 5 år. Det første årsnummeret er 0 i celle A8. Det siste årsnummeret er 5 i celle A13. Tallet i celle A9 er laget med formelen =A8 pluss 1, og resten av årsnummerene nedenfor er laget ved å kopiere denne formelen. Tallet for innskudd i celle B9 er regnet ut med formelen =$B$2+$B$3*A9. Denne formelen er kopiert ned til og med rad 13. Skjermutklipp.
Govva: Bjarne Skurdal / CC BY-SA 4.0

Spørsmål

Hva skjer med formlene våre i cellene nedenfor rad 6 nå når vi har satt inn en ekstra rad? Skaper dette problemer?

Svar

Dette skaper heldigvis ikke noen problemer for oss. Når vi setter inn en ny rad 6, vil alle celleadressene som ligger nedenfor rad 6 få økt radnummeret med 1. Regnearkprogrammet tar hensyn til at vi legger inn nye rader og oppdaterer formlene i alle celler nedenfor rad 6 automatisk. Det betyr at der det for eksempel sto "A9" i en formel tidligere, står det nå "A10".

Spørsmål

Hvor lenge skal det første innskuddet på 2 500 kroner stå? Hvor lenge skal det andre og det siste stå?

Svar

Det første innskuddet skal stå i 5 år. Det andre blir stående ett år mindre, altså 4 år. Det siste innskuddet (det sjette) er akkurat gjort når 5 år har gått, så det har stått 0 år.

Oppgave 10

Det nest siste innskuddet (på 3 700 kroner) skal stå i ett år. Bruk vekstfaktoren, og sett opp regnestykket som gir hvor mye dette beløpet vokser til etter ett år i banken.

Løsningsforslag

Vi må multiplisere innskuddet med vekstfaktoren opphøyd i antallet år innskuddet skal stå. Her er det bare ett år, og vi får

3 700 kr·1,0251  (=3 700 kr·1,025)

Oppgave 11

Det andre innskuddet (på 2 800 kroner) skal stå i fire år. Bruk vekstfaktoren, og sett opp regnestykket som gir hvor mye dette beløpet vokser til etter fire år i banken.

Løsningsforslag

2 800 kr·1,0254

Vi ønsker nå å lage en formel i celle C8 som vi kan kopiere nedover. Legg merke til at tallet vekstfaktoren skal opphøyes i, starter på 5 for det første innskuddet og går ned til 0 for det sjette og siste innskuddet. Dersom det hadde vært motsatt, kunne vi i celle C8 ha skrevet =B8*$B$8^(A8), altså opphøyd i årsnummeret, som vi har i kolonne A, men det blir ikke riktig.

Oppgave 12

Lag enn formel i celle C8 som kan kopieres til cellene C9 til C13.

Tips

Problemet er å finne en formel der det vi opphøyer vekstfaktoren i, starter med 5 i celle C8 og slutter på 0 i celle C13, det vil si går nedover fra 5 til 0 mens årsnummeret i kolonne A går fra 0 til 5, altså motsatt.

Løsningsforslag

Er du enig i at dersom vi legger sammen årsnummeret med det vi skal opphøye vekstfaktoren i, får vi alltid 5 uansett hvilken rad vi ser på? Da må det vi skal opphøye i, være lik 5 minus årsnummeret. Formelen i celle C8 blir derfor

=B8*$B$5^(5-A8)

Oppgave 13

Lag ferdig regnearket, og finn ut hvor mye Jonas har i banken om 5 år, altså rett etter det sjette innskuddet.

Løsningsforslag
Regneark og formelvisning av regnearket der inndataområdet er første innskudd på 2500 kroner, årlig økning på 300 kroner og rente på 2,5 prosent. Vekstfaktoren på 1,025 er regnet ut nedenfor. Overskriftene står i kolonne A mens tallene står i kolonne B. I celle A7 er overskriften år nummer og i celle B7 er overskriften innskudd. I celle C7 er overskriften verdi om 5 år. Det første årsnummeret er 0 i celle A8. Det siste årsnummeret er 5 i celle A13. Tallet i celle A9 er laget med formelen =A8 pluss 1, og resten av årsnummerene nedenfor er laget ved å kopiere denne formelen. Tallet for innskudd i celle B9 er regnet ut med formelen =$B$2+$B$3*A9. Denne formelen er kopiert ned til og med rad 13. Denne formelen er kopiert ned til og med rad 13. Tallet for verdi av innskuddet om 5 år i celle C8 er regnet ut med formelen = B8*$B$5^(5-A8), og denne formelen er kopiert ned til og med rad 13. Skjermutklipp.
Govva: Bjarne Skurdal / CC BY-SA 4.0

Jonas vi ha 20 622 kroner stående på kontoen etter det siste innskuddet.

Fiillat

Laptop og eple. Foto.
Govva: NTB / CC BY-NC-SA 4.0

Ting å lære seg om regneark

Vis innhold

Formelkopiering

Når vi kopierer og limer inn en regnearkformel, vil celleadressene i formelen endre seg i takt med avstanden fra den cellen formelen er kopiert fra.

Fast celleadresse ved formelkopiering

Dersom vi ønsker at en celleadresse i enn formel skal være fast når formelen skal kopieres, skriver vi et dollartegn $ foran både kolonnebokstaven og radnummeret i celleadressen før vi kopierer formelen.

Eksempel:

Dersom formelen inneholder celleadressen B2 og B2 skal være fast i formelkopieringa, skriver vi $B$2 der formelen alltid skal ha tallet i celle B2.

Sette inn ny rad

Vi kan sette inn en ny rad i regnearket ved å høyreklikke på radnummeret på den raden vi vil sette inn den nye raden over (eller under). Tilsvarende kan vi høyreklikke på en kolonnebokstav dersom vi vil sette inn en ny kolonne rett før eller rett etter den kolonnen vi klikket på.
Formler i cellene vil oppdatere seg automatisk ved disse operasjonene slik at utregningene ikke blir endret.

Spørsmål

Må vi bruke dollartegn foran kolonnebokstaven B i formelen i celle B9 før vi kopierer den til cellene B10 til B13?

Svar

Nei, vi må egentlig ikke det. Siden formelen bare skal kopieres til celler i den samme kolonnen (B), trenger vi ikke sette dollartegn før kolonnebokstaven B på de to plassene der "B" finnes i formelen, men vi må ha dollartegn før radnumrene i disse celleadressene (siden formlene skal kopieres til nye rader).

Prøv å utelate dollartegnet foran de to B-ene i formelen i celle B9. Slett formlene i cellene B10–B13, og lim inn den nye formelen fra celle B9. Er regnearket fremdeles riktig?

Spørsmål

Vil formelen i celle B9 fungere i celle B8? Hvorfor/hvorfor ikke vil den (ikke) fungere?

Svar

Formelen vil fungere fordi årsnummeret, som står i rute A8, er lik null. Vi kan trygt kopiere formelen i B9 opp til celle B8. Prøv!