Regneark

I dette dokument er der en oversigt over anvendelige funktioner for matematik C EUX Merkantil og HHX. Regnearksfunktionerne vil blive gennemgået for Microsoft Excel, Libreoffice Calc og Google Sheets.

Almindelige funktioner

Excel/Calc Sheets* Beskrivelse
SUM(Område) SUM(Område) Lægger tallene i et område sammen
ABS(Tal) ABS(Tal) Returnerer den positive værdi af et tal
AFRUND(Tal;Decimaler) ROUND(tal;Decimaler) Afrunder et tal med det valgte antal decimaler
REST(tal;division) MOD(tal;division) Finder rest (modulus) efter division f.eks. modulus 5/4 = 1
AFKORT(Tal;decimaler) TRUNC(tal;decimaler) Afkort et med til det valgte antal decimaler
HELTAL(Tal) INT(tal) Ændrer decimaltal til heltal
LIGE(Tal) EVEN(Tal) Runder op til næste hele tal
ULIGE(Tal) ODD(Tal) Runder op til næste ulige tal

* Hvis man indtaster de danske funktioner i Google Sheets, så bliver de omdannet til de engelske funktioner.

Tællere

Excel/Calc Sheets* Beskrivelse
TÆL(Område) COUNT(Område) Tæller antallet af celler med talværdier i et område
TÆLV(Område) COUNTA(Område) Tæller antallet af celler med talværdier og strenge i et område
TÆL.HVIS(Område;Betingelse) COUNTIF(Område;Betingelse) Tæller antallet af celler med talværdier og strenge i et område

Fejlmeddelelser

Når man arbejder med regneark sker der at man laver fejl i sine indtastninger og beregninger. Excel og andre regneark anvender primært to fejlmeddelelser til at vise hvad type fejl det er.

  • #DIV/0!
  • Formlen forsøger at dividere med 0.

  • #I/T
  • Det som formlen skulle vise i det konkrete felt er Ikke Tilgængelig ( #I/T )

  • #NA!
  • Værdien er ikke tilgængelige

  • #NAME?
  • Der er fejl i funktionen du anvender.

    Eks:

    TÆLHVIS() = #NAME? (Skulle hedde TÆL.HVIS)

  • #NULL!
  • Formlen har ingen løsninger

  • #NUM!
  • Tallet i formlen er ugyldig

    Eks:

    =SQRT(-2) = #NUM!

  • #REF
  • En eller flere af referencerne i formlen kan ikke findes.

  • #VÆRDI!
  • Funktionen kan ikke udføres fordi felterne er inkompatible. Det kan f.eks. være hvis du forsøger at lægget et tal og en tekst sammen

    Eks:

    1 + F = #VÆRDI!

Excel er ikke god til at fortælle hvad og hvor en fejl optræder. Det eneste du kan gøre er at kontrollere om dine indtastninger og værdier er korrekte.

Logiske funktioner

Logiske funktioner anvendes til at undersøge felter ud fra nogle betingelser. Hertil anvender man relationer

Matematisk funktion Excel/Calc/Sheets Forklaring
= = Det samme som
<> Forskellig fra
< < Mindre end
<= Mindre end og lige med
> > Større end
>= Større end og lig med

Herunder bliver følgende funktioner gennemgået:

Excel/Calc Sheets Beskrivelse
HVIS() Undersøger om en betingelse er opfyldt, og sætter en værdi for disse
HVISER() N/A Undersøger mange forskellige ting, og sætter en værdi for hvert punkt der er opfyldt
ELLER() OR() Undersøger en række betingelser. Blot en betingelse skal være opfyldt for at den svarer med SAND.
OG() AND() Undersøger en række betingelser. Alle betingelser skal være opfyldt for at den svarer med SAND.
ER.TAL() ISNUMBER Undersøger om indholdet er et tal eller en tekststreng.

Alle funktionerner er vist i dette regneark: regneark_logiske funktioner.xls

HVIS()

Bruges til at undersøge om en betingelse er opfyldt. Man vælger selv om det skal skrives ved hjælp af et tal eller en streng

Anvendelse (excel/Calc):

HVIS( *betingelse* ; *Værdi_for_sand* ; *værdi_for_falsk* )

Anvendelse (Sheets):

IF( *betingelse* ; *Værdi_for_sand* ; *værdi_for_falsk* )

Eksempel (B-kolonnen):

HVIS(A1 < 25;”Under 25”;”Over 25”)

Eksempel (C-kolonnen):

HVIS(A1=20;SAND;FALSK)
A B C D
1 10 Under 25 FALSK
2 20 Under 25 SAND
3 30 Over 25 FALSK
4 40 Over 25 FALSK

Man kan også lave en "shorthand" til en HVIS(), ved blot at skrive et semikolon (;) efter betingelsen.

HVIS(A1="kvinde";)

Her vil funktionen svare med O hvis det er sandt og FALSK hvis det ikke rigtigt.

HVISER()

Bruges til at undersøge flere betingelser på en gang.

Anvendelse (excel/Calc):

HVISER( *betingelse1* ; *værdi for sand* ; *betingelse2* ; *værdi for sand* ;….)

Funktionen virker ikke på Mac og Sheets

Eksempel (B-kolonne):

=HVISER(A1="lille";0;A1="mellem";1;A1="stor";2)
A B C D
1 lille 0
2 mellem 1
3 mellem 1
4 stor 2

ELLER()

Undersøger om en række betingelser er sande. Hvis blot en af dem er sande vil svarer funktionen tilbage med SAND ellers FALSK.

Anvendelse:

ELLER( *betingelse1* ; *betingelse2* ;...)

Eksempel (B-kolonne):

ELLER(A1="lille";A1="stor")
A B C D
1 lille SAND
2 mellem FALSK
3 mellem FALSK
4 stor SAND

OG()

Undersøger om alle givne betingelser er sande. Hvis de alle er sande vil svarer funktionen tilbage med SAND ellers FALSK.

Anvendelse:

og( *betingelse1* ; *betingelse2* ;...)

Eksempel (C-kolonne)

OG(A1="lille";A2="blå")
A B C D
1 lille grøn FALSK
2 lille gul FALSK
3 lille blå SAND
4 lille orange FALSK

Find tekststreng

For at finde en tekststreng skal man bruge funktionerne ER.TAL() og FIND().

FIND()

Denne funktion finder ud af hvor en tekststreng står i et felt. Forestil dig at vi har navnet Allan Hansen i feltet A1:

Funktion Position
FIND("A";A1) Allan 1
FIND("a";A1) Allan 4
FIND("s";A1) Allan Hansen 10
FIND("ø";A1) Allan Hansen #VÆRDI!

ER.TAL()

Funktionen undersøger om feltet har en værdi.

Eksempel (B-kolonne):

ER.TAL(A1)
A B C D
1 Hans FALSK
2 35 SAND
3 Gurli FALSK
4 3,14159265359 SAND

Kombiner

Ved at anvende FIND() inde i ER.TAL() kan vi altså finde ud af om en tekststreng findes. Hvis FIND() returnerer et tal, så findes tekststrengen, og ER.TAL() returnerer SAND

Anvendelse (excel/Calc):

ER.TAL( FIND( *tekststreng/værdi* ; *felt* ) )

Eksempel (B-kolonne):

ER.TAL(find("e";A1))
A B C D
1 Hans FALSK
2 Grethe SAND
3 Børge SAND
4 Gurli FALSK

Funktionen kan indlejres i HVIS(), HVISER(), ELLER() og OG(). På den måde kan man både finde ud af om noget står i teksten, og få Excel til at returnere en værdi.

Flere funktioner

Her kan du finde Microsofts gennemgang af logiske funktioner.

Logiske funktioner i Excel

Betinget formatering

For at skabe bedre overblik over ens data, kan man lave formatering af felterne med udgangspunkt i nogle betingelser. Funktionen tilgås ved at gå til:

Hjem → Betinget formatering → Regler for fremhævning af celler

Her vælger man så kriterierne for betingelsen.

_
_

I det næste vindue sættes betingelsen og formateringen.

Excel 2016 - Windows
_
Excel 2016 - Mac
_

Strenge

Man kan skære i tekster ved hjælp af:

Excel/Calc Sheets Beskrivelse
VENSTRE() LEFT() Returnerer antal tegn fra henstre.
HØJRE() RIGHT() Returnerer antal tegn fra højre.
MIDT() MID() Returnerer antal tegn fra en position.

I eksemplerne herunder er alle funktionerne vist med A1 = fr 5/1 2. modul, hvor positionen kan ses i nedenstående tabel.

Retning f r 5 / 1 2 . m o d u l
Venstre til højre 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
Højre mod Venstre 15 14 13 12 11 10 9 8 7 6 5 4 3 2 1

VENSTRE()

Bruges til at vise et antal tegn fra venstre mod højre

Anvendelse (excel/Calc)

VENSTRE( *tekststreng* ; *antal_tegn*)

Anvendelse (Sheets)

LEFT( *tekststreng* ; *antal_tegn*)

Eksempel

VENSTRE(A1;2)
A B C D
1 Hans Ha
2 Grethe Gr
3 Børge
4 Gurli Gu

HØJRE()

Bruges til at vise et antal tegn fra højre mod venstre.

Anvendelse (excel/Calc)

HØJRE( *tekststreng* ; *antal_tegn*)

Anvendelse (Sheets)

RIGHT( *tekststreng* ; *antal_tegn*)

Eksempel

HØJRE(A1;2)
A B C D
1 Hans ns
2 Grethe he
3 Børge ge
4 Gurli li

MIDT()

Bruges til at vise et antal tegn fra venstre mod højre

Anvendelse (excel/Calc)

MIDT( *tekststreng* ; *start_position* ; *antal_tegn*)

Anvendelse (Sheets)

MID( *tekststreng* ; *start_position* ; *antal_tegn*)

Udelades *antal_tegn* udskriver funktionen resten af tegnene i strengen

Eksempel (B-kolonne) - dag

=MIDT(A1;5;2)

Eksempel (C-kolonne) - månede

=MIDT(A1;7;2)

Eksempel (D-kolonne) - år

=MIDT(A1;9;4)
A B C D
1 ons 03012018 03 01 2018
2 fre 05012018 05 01 2018
3 man 08012018 08 01 2018
4 tir 09012018 09 01 2018

Du kan læse mere om disse funktioner

https://support.office.com/da-dk/article/Opdel-tekst-i-forskellige-kolonner-med-guiden-Konverter-tekst-til-kolonner-30b14928-5550-41f5-97ca-7a3e9c363ed7?ui=da-DK&rs=da-DK&ad=DK

Finansielle funktioner

Herunder er en tabel med betegnelser der anvendes ved regnearksfunktioner.

Betegnelse Forklaring
Rente Rente pr periode
Periode Den valgte periode
Nper Samlet antal perioder
Nv Startværdien på lånet
Fv Værdien der skal opnås
Type Hvornår ydelsen skal falder. 0 er afslutning af perioden (default) og 1 er starten.

NB: Vær opmærksom på at regneark anvender konventionerne (vedtagne standarder) at penge man udbetaler (Debit) er negative og penge man modtager (CREDIT) er positive. Derfor vil penge man udbetaler til en opsparing være et negativt beløb, men de penge man modtager være positivt.

Herunder bliver beregningerne gennemgået med udgangspunkt i et lån på 10.000,-, der afbetales over 3 år med en rente på 5,6% pa., samt en opsparing på 20000 der løber over 3 år med en rente på 2%

Dvs:

Lån Opsparing
Rente = 0,056/12 ≈ 0,46667%
Nper = 3*12 = 36
Nv = 10000
Fv = 0
Type = 0 (udelades da det er default
Rente = 0,002/12 ≈ 0,00167
Nper = 3*12 = 36
Nv = 0
Fv = 20000
Type = 0 (udelades da det er default

YDELSE

Funktionen YDELSE bliver brugt til beregne lån og opsparinger. Herunder vil følgende blive gennemgået:

Excel/Calc Sheets* Beskrivelse
YDELSE() PMT() Den samlede ydelse på lån/opsparing
R.YDELSE() IPMT() Renten i den konkrete periode
H.YDELSE() PPMT() Ydelsen på lånet/opsparingen i perioden

YDELSE()

Giver det beløb man skal betale hver periode. Her skal man ikke angive perioden, da ydelsen er den samme i alle perioder.

Funktion i Sheets: PMT()

Anvendelse: ydelse(rente;Nper;Nv;Fv;Type)

Lån: YDELSE(0,056/12;3*12;10000;0) = -302,41

Opsparing: YDELSE(0,002/12;3*12;0;20000) = -553,94

Tallene bliver negative da det er noget du skal indbetale - altså noget du har mindre.

  • Afdrag på lån pr månede: 302,41
  • Indbetaling til opsparing: 553,94

R.YDELSE()

Anvendelse: R.YDELSE(Rente;Periode;Nper;Nv;Fv;Type)

Lån (13. periode): R.YDELSE(0,056/12;13;3*12;10000;0) = -31,97

Opsparing (13. periode): R.YDELSE(0,002/12;13;3*12;0;20000) = 1,11

  • Rente i 13 periode: -31,97
  • Rente i 13 periode: 1,11

H.YDELSE()

Giver Afdraget i den konkrete periode

Anvendelse: H.YDELSE(Rente;Periode;Nper;Nv;Fv;Type)

Lån (13. periode): H.YDELSE(0,056/12;13;3*12;10000;0) = -270,44

Opsparing (13. periode): H.YDELSE(0,002/12;13;3*12;0;20000) = -555,05

  • Afdrag i 13 periode: -270,44
  • Bidrag i 13 periode: -555,05 (553,94+1,11 = 555.05)

Her er et regneark med eksemplerne ovenfor regneark_eksempler_01.xls

EFFEKTIV.RENTE()

Giver den effektive rente pr. år

Funktion i Libreoffice Calc: EFFEKTIV.RENTE_ADD()

Funktion i Sheets: EFFECT()

Anvendelse: EFFEKTIV.RENTE(rente pa;terminer)

Den effektive rente pr. år: EFFEKTIV.RENTE(0,056;12) = 5,746%

Læg mærke til at jeg amortisationstabellen har indsat den effektive rente.

Gode henvisninger

https://youtu.be/am37BuhqQ54

FV()

Giver Fremtidsværdien af en investering baseret på faste ydelser og fast rente

Funktion i Sheets: FV().

Anvendelse: FV(rente;Nper;Ydelse;NV)

Husk ydelsen skal angives negativ, da det er noget man udbetaler til lånet.

Lån: FV(0,002/12;3*12;-1500;0) = kr. 54.157.80

NPER()

Giver antallet af ydelser for et lån eller en investering:

Funktion i Sheets: NPER().

Anvendelse: NPER(rente;Ydelse;NV;FV;Type)

Herunder er udregningen på et lån på kr. 20.000, 2% pa. og en ydelse på kr. 1.500,-. Husk ydelsen skal angives negativ, da det er noget man udbetaler til lånet/opsparingen.

Lån: NPER(0,002/12;-1500;20000;0) = 13,35 ydelser

Amortisationstabel

Amortisationstabeller beskriver hvordan et lån udvikler sig/afskrives. Selve ordet amortisation stammer fra det franske amortir, som betyder "dræbe" ("bring to death").

Normalt vil en amortisationstabel være opbygget efter følgende skabelon:

Termin Primo restgæld Ydelse Rente Afdrag Ultimo Restgæld
Den gældende periode Gælden i starten af perioden Ydelse i perioden Renten i perioden Afdraget (Ydelse-Rente) Gæld efter afdrag

Forklaring til på kolonnerne

Kolonne Beskrivelse Handling
Termin Den gældende periode Indsættes manuelt
Primo restgæld Gælden i starten af perioden Indsættes manuelt første gang, men overføres herefter fra ultimo restgæld
Ydelse Ydelsen i perioden Beregnes med YDELSE()
Rente Renten i perioden Beregnes med R.YDELSE()
Afdrag Periodens afdrag på gælden Beregnes med H.YDELSE() eller - Ydelse - Rente
Ultimo Restgæld Gælden efter afdrag Beregnes med Primo restgæld - Afdrag

Herunder er et eksempel på en amortisationstabel, og det kan downloades her: regneark_amortisationstabel.xls

Serielån

Et Serielån er et lån hvor man hver månede afbetaler samme beløb på lånet, og hvor renten falder over tid. Lad os tage eksemplet med billånet ovenfor.

Termin Primo restgæld Rente Afdrag Ydelse Ultimo Restgæld
Den gældende periode Gælden i starten af perioden Renten i perioden Afdraget (Ydelse-Rente) Ydelse i perioden Gæld efter afdrag

Forklaring af kolonner

Kolonne Beskrivelse Handling
Termin Den gældende periode Indsættes manuelt
Primo restgæld Gælden i starten af perioden Indsættes manuelt første gang, men overføres herefter fra ultimo restgæld
Rente Renten i perioden Beregnes med Restgæld*Renten
Afdrag Periodens afdrag på gælden Afdraget er Restgæld/Antal terminer
Ydelse Ydelsen i perioden Ydelsen er Rente + Afdrag
Ultimo Restgæld Gælden efter afdrag Beregnes med Primo restgæld - Afdrag

Herunder er et eksempel på en serielånsstabel, og det kan downloades her: regneark_serielånsstabel.xls

Matematiske funktioner

Almindelige funktioner

Matematisk funktion Regneark (excel/calc/Sheets)
\( a^b \) potens(a;b) eller a^b
\( \sqrt a \) kvrod(a)
\( \sqrt[b]a \) a^(1/b)
\( log_{10}(a) \) log10(a)
\( log_{b}(a) \) log(a;b)
\( ln(a) \) ln(x)
\( \pi \) pi()

Statistik

Statistisk funktion Beskrivelse
tæl(Område) Antal observationer (værdier)
tælv(Område) Antal observationer (ikke tomme felter)
tæl.hvis(Område;Betingelse) Antal observationer i området der opfylder betingelsen. Hvis betingelsen er et tegn skal det omgives med anførselstegn (f.eks: ") .
middel(Område) Gennemsnit
min(Område) Mindste værdi
maks(Område) Største værdi
hyppigst(Område) hyppigste værdi
median(Område) Medianen
kvartil(Område;1/2/3) Første, anden og tredje kvartil

Varians og standardafvigelse

Statistisk funktion Beskrivelse
varians.s(Område) variansen for en stikprøve
varians.p(Område) variansen for en population
stdafv.s(område) Standardafvigelsen/spredningen for en stikprøve
stdafv.p(område) Standardafvigelsen/spredningen for en population

Tilfædige tal

Excel/Calc Sheets* Beskrivelse
SLUMP() RAND() Laver et tilfældigt tal i intervallet [0;1]
SLUMPMELLEM(start;slut) RANDBETWEEN(start;slut) Returnerer den positive værdi af et tal

Talsystemer

Konvertering Beskrivelse
bin.til.dec()
bin.til.hex()
dec.til.bin()
dec.til.hex()
hex.til.bin
hex.til.dec