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	Bø
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

RENTE

RENTE()

Giver renten på et lån ud fra løbetid.

Funktion i Libreoffice Calc: RENTE()

Funktion i Sheets:

Anvendelse: RENTE(nper;ydelse;NV)

Et lån på 15.000,- over 24 mdr, med en ydelse 650,-

EKsempel: RENTE(24;-650;15000) = 0,3162%

Dvs. den årlige rente er på 12 · 0,3162 = 3,794% pa

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

NV()

Giver nutidsværdien på et lån baseret på fast ydelse og fast rente

Funktion i Sheets: NV().

Anvendelse: NV(rente;Nper;Ydelse)

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

En fast ydelse på kr. 650, over 24 mdr med en rente på 0,31%

Lån: NV(0,0031;24;-650) = kr. 15.011,41

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