Innføring I Excel

Grunnleggende Excel 2007 Tor Espen Kristensen [email protected]

Innhold

iii

Innhold 1 Litt om Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1

2 Formler i regneark . . . . . . . . . . . . . . . . . . . . . . . . . . . .

1

3 Grafisk framstilling i Excel . . . . . . . . . . . . . . . . . . . . . . . Sektordiagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Søyle/stolpediagram . . . . . . . . . . . . . . . . . . . . . . . . . . .

6 7 8

4 Innebygde funksjoner . . . . . . . . . . . . . . . Middelverdi . . . . . . . . . . . . . . . . . . . . . Median . . . . . . . . . . . . . . . . . . . . . . . . tilfeldig, tilfeldigmellom og antall.hvis Trendlinje . . . . . . . . . . . . . . . . . . . . . . Andre funksjoner . . . . . . . . . . . . . . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

. . . . . .

12 12 14 14 15 16

5 Verktøy i Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Sortering og lister . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Målsøking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

17 17 19

6 Oppgaver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

21

Litt om Excel

1

1 Litt om Excel Det er vanlig å bruke Excel og regneark som to synonyme ord. Excel er et av flere programmer i Microsofts Office. De senere årene har derimot OpenOffice blitt stadig mer aktuelt. Dette heftet er basert på Excel 2007. Vår strategi er å gå gjennom en del eksempler som illustrerer hvordan oppgaver kan løses ved hjelp av regneark.

Figur 1 Excel

Et regneark kan vi tenke på som et stort rutenett. I rutene (cellene) kan vi skrive inn tekst ➍, tall ➎ eller formler ➏. Vi henviser til en bestemt celle på samme måte som i et sjakkbrett; ved å bruke bokstaven for kolonnen og tallet for raden. På figur 1 står teksten «Spenning» i celle B3. Vi kan be Excel om å utføre regnestykker ved å skive inn formler. I celle C7 har vi beregnet forholdet Spenning/strøm ved å skrive formelen =B7/A7. Når vi så trykker : (enter) vil Excel utføre operasjonen og gi svaret (0,41) i cellen. Vi kan se hvilken formel som er utført i formellinjen ➊. Vi kan velge hvilket format vi ønsker tallene våre i ved å klikke på knappene ved ➌. Dersom vi vil endre bakgrunnsfarge i en eller flere celler kan vi gjøre dette ved å bruke knappene ved ➐. (se også figure 8)

2 Formler i regneark Mange praktiske problemstillinger kan være nokså krevende å løse med papir og blyant. Det kan være mange tall som skal legges sammen eller

2

Regneark med Excel

Figur 2 En enkel valutakalkulator

mange størrelser som varierer. I slike tilfeller kan regneark være til god hjelp. Eksempel 1 Vi ønsker å lage en valutakalkulator som regner ut hva du får når du veksler fra norske kroner til amerikanske dollar. Siden kursen stadig forandres, må denne lett kunne justeres. På figur 2 ser du et eksempel på hvordan en slik kalkulator kan lages. I kolonne A er det skrevet inn tekst som forklarer hva som skal stå i kolonne B. I B4 har vi ført inn den gjeldende valutakurs, mens i B6 føres inn hvor mange norske kroner som skal veksles inn. I B8 skriver vi inn hva svaret skal bli uttrykt ved rutenavna. Vi vet at svaret skal være antall norske kroner delt på kursen. Det vil si B5/B4. Når vi skriver inn en slik formel, må vi alltid starte med likhetstegnet =. Det vil si at vi i B8 skriver «=B6/B4». I eksempelet over brukte vi en formel for å gjøre om til USD (=B5/B4). Excel har også innebygd en del «ferdige» formler eller funksjoner som vi kan bruke. Eksempel 2 Lisa har sommerjobb på butikken. Hun tjener 62 kroner i timen. Her er timelisten hennes: Uke

Mandag

Tirsdag

Onsdag

Torsdag

Fredag

Lørdag

32

7

4

4

7

4

6

33

7

0

6

6

8

8

34

8

6

6

8

6

0

a) Hvor mye tjener hun i hver uke? b) Hvor mye tjener hun til sammen?

Formler i regneark

3

Løsning: Vi skriver tallene inn i et regneark. Pass på å skrive nok forklarende tekst (figur 3). Før vi går løs på selve oppgaven, merker vi oss hvordan cellene er formatert i regnearket (figur 3).

Figur 3 Timelisten i Excel. Pass på å skrive forklarende tekst i regneark.

En vanlig feil er å skrive «kr 62,00» i celle B2. Excel vil da tolke dette som tekst og ikke som et tall (på grunn av de to bokstavene «kr»). Vi skal heller formatere cellen riktig for å få fram kr og riktig antall desimaltall. (ved ➌ på figur 1 side 1). For å gjøre det klikker vi på ikonet For å regne ut hvor mye Lisa tjener hver uke, er det lurt a summere timene. Vi kan skrive formelen =B5+C5+D5+E5+F5+G5 i for eksempel H5, men det blir litt tungvint i lengden, spesielt når antall ledd som skal summeres blir mange. I stedet kan vi bruke en innbygget funksjon i Excel som summerer for oss. Denne har ikonet P. Gå til den cellen du vil ha summen i (i vårt tilfelle H5). Klikk på P og marker det området du vil summere ved å klikke ned venstre musetast i B5 og dra markøren bort til G5 mens du hele tiden holder venstre museknapp nede. Da vil du få noe slik som i figur 4. Trykk så Enter og du får svaret i celle H5, nemlig 32. Gjenta dette for alle de tre ukene. For å gjøre dette behøver du egentlig ikke å gå gjennom hele prosessen beskrevet ovenfor. Det er nok å klikke på celle H5, klikke i nedre høyre hjørne og dra nedover de to cellene mens du hele tiden holder museknappen nede. (Se figur 5.) Nå har vi regnet ut antall timer Lisa har jobbet hver uke. Neste steg er da å regne ut hvor mye hun tjente hver uke ved å multiplisere timetallet med timelønnen. Dette gjør vi i kolonne I. Vi bruker da formelen =H5*B2 (timelønnen har vi skrevet inn i B2). Der er lurt å skrive inn timelønnen i en egen celle. Da vil det være lett å gjøre utregningene om igjen om vi oppdager at lønnen var for eksempel 72 kr/time. Vi vil få et regneark som på figur 6 når vi har løst hele oppgaven. Merk at du ikke sånn uten videre kan bruke trikset med å dra cellene nedover når det gjelder lønnen. Dette fordi du da vil få formelen =B3*H6 i

Figur 5 Autokopiering: Klikk i cellen du vil kopiere nedover (eller bortover), flytt markøren til den lille firkanten i nedre høyre hjørne. Ta tak i denne ved å holde venstre musetast nede og dra.

4

Regneark med Excel

Figur 4 Autosummer i Excel.

Figur 6 løst!

Oppgaven

I6 og altså ikke =B2*H6. Vi sier at cellehenvisningen er relativ. For å løse dette problemet kan vi bruke absolutt henvisning. I I5 skriver vi formelen =B$2*H5. Dollartegnet forteller programmet at det ikke skal endre tallet bak dollartegnet når du autokopierer formler. Du får tak i dollartegnet ved å trykke på AltGr og 4 $ (men du holder inne AltGr ). En annen måte å få fram dollartegnet i Excel er å trykke på F4 like etter at du har skrevet inn B2. Oppgave 1 Hansen har 9000 kr i banken 1.1.2005. Han er interessert i å finne ut hvor mye innskuddet vil vokse i løpet av en tiårsperiode. Hansen regner med å få 4 % p.a. rente i denne perioden. Sett regnestykket opp i et regneark. Begynn gjerne slik som i figur 7. a) Hvor mye har Hansen stående i banken etter 5 år? b) Hvor mange år vil det gå før han har 12 000 kr i banken? c) Hvor mange år går det før startkapitalen er fordoblet?

Formler i regneark

5

B

A

C

D

Oversikt over sparepengene til Hansen

1 2 3

Startkapital

kr 9 000, 00

4

Rentefot

4%

6

År

Kapital 01.01

7

2005

8

2006

9

2007

10

2008

11



5 Rente

Kapital 31.12

=B3

Figur 7 Hansens lønn

d) Hvor mye har Hansen stående i banken etter 10 år? Oppgave 2 I følge en persisk legende, ble sjakkbrettet oppfunnet av en tjener til den persiske kongen. Kongen ble så begeistret for spillet at han ville belønne tjeneren, og ba ham ønske seg noe. Tjeneren tenkte seg om og ba om at han måtte få ett hvetekorn for den første ruten på sjakkbrettet, to for den andre, fire for den tredje osv. For hver ny rute på brettet fordoblet han antall korn. Kongen ble forundret over det han trodde var et beskjedent ønske. Var det beskjedent? Hvor mye korn fikk tjeneren? I margen ser du starten på en tabell som førere regnskap med antall riskorn. Gjør tabellen ferdig. Oppgave 3 (Tatt fra Breiteig og Fuglestad 2000) Tallene i hver kolonne i tabell 1 følger visse tallmønstre. Skriv flere tall videre i hver kolonne. Lag den på regneark ved hjelp av formler. Kan du lage ulike formler til samme tallrekke? A 1

B

C

D

E

F −0,14

0,12 7,6

1

3

2 0,135 6,3

1,5

0,9

2,8 −0,11

2,25 0,27

1,12 −0,08

3

0,15 5,0

7

G

4 Tabell 1

Hvordan fortsetter tallmønstrene?

Oppgave 4 Lise setter opp et budsjett over utgifter til bilhold i løpet av ett år. Hun regner med en kjørelengde på 12 000 km, og Lise opererer med følgende utgiftsposter:

Rute nr.

Antall riskorn

1

1

2

2

3

4

4

8

5

16

6

32





6

Regneark med Excel

– Bensinutgiftene regnes ut fra 9,00 kr per liter og et forbruk på 0,85 liter per mil – Årsavgift til staten er på 2 265 kr – Forsikringspremien er på 6 200 kr, men hun får 70 % rabatt på dette beløpet – Verksteds- og vedlikeholdsutgifter settes til 6 500 kr – Garasjeleien er 250 kr per måned a) Hva antar Lise at utgiftene vil bli i løpet av ett år? Ved årets slutt viste deg seg at Lise hadde kjørt 9 500 km. b) Hva ble Lises utgifter til bilhold dette året? (De andre utgiftspostene er slik som oppgitt ovenfor) Oppgave 5 Lag et regneark som gjør om fra grader Celsius til grader Fahrenheit. Sammenhengen mellom disse er gitt ved: Grader Fahrenheit = 1, 8  grader Celsius + 32

Figur 8 Vi kan også formatere celler etter behov ved å markere de aktuelle cellene, trykke ned høyre musetast og velge «Formater celler». Du vil da få opp dette vinduet.

3 Grafisk framstilling i Excel Å framstille data grafisk ved hjelp av Excel kan være motiverende! Med noen enkle klikk, og vips så er diagrammet tegnet! Dette har selvsagt sine ulemper. Vet du egentlig hva det er du framstiller? Kan du tolke diagrammene du så pent får framstilt? Dette er noe vi må være bevisst på når vi jobber med grafisk framstilling i Excel.

Sektordiagram

7

Sektordiagram For å illustrere hvordan vi kan lage et sektordiagram i Excel vil vi ta for oss eksempel 2.4 i (Lysø, 1999).

Eksempel 3 En gruppe studenter undersøkte hvilke treningsformer som ble foretrukket i klassen de gikk i. Resultatene er ført opp i tabellen under. Aearobic: 5 Svømming: 4 Sykling: 10 squash: 2 ski: 9 Tabell 2 Studentenes treningsvalg

Dette fører vi inn i et regneark:

Figur 9 Du setter inn diagrammer, grafer og liknende ved å velge fanen «Sett inn»

Marker så A2–B5 og klikk så på fanen «Sett inn» og velg så «Sektor». Du får da opp et sektordiagram som du kan jobbe videre med. Hvor vil du ha forklaringene? Hvilke farger vil du at diagrammet skal ha? Se figur 10 Figur 10 Du får flere alternativer etter at du har valgt diagramtype. Merk at disse går vekk dersom du klikker i regnearket.

8

Regneark med Excel

Figur 11 Sektordiagram tegnet med Excel

Søyle/stolpediagram Vi kaster en terning 100 ganger og får følgende frekvenstabell:

Antall øyne

Hyppighet

1 2 3 4 5 6

16 15 17 18 21 13

Sum:

100

Vi ønsker å vise resultatet i et stolpediagram. Vi skriver da dataene inn i et regneark og markerer kolonnen med verdiene i (se figur 12 i margen). Vi klikker på fanen «Sett inn» og velger stolpediagram. Klikk til slutt på «Fullfør». Du vil da få et diagram som på figur 13.

Figur 12 Vi markere («svarter») de cellene som vi vil illustrere med søylediagram

Oppgave 6 Lag et søylediagram som illustrerer studentenes treningsvalg på side 7. Du skal få noe slikt som diagrammet i figur 14. Hvilke type diagram synes du best illustrerer studentenes valg?

Søyle/stolpediagram

9

Figur 13 Søylediagram laget i Excel

Figur 14 Studentenes treningsvalg

Vi kan også plotte grafer til funksjoner i Excel. Eksempel 4 Vi måler temperaturen et døgn og får følgende resultat: Tid (kl)

2

4

6

8

10

12

14

16

18

20

22

24

Temp (°C)

12

11

10

14

16

18

19

24

22

19

16

14

Vi skriver dette inn i et regneark, markerer det vi har skrevet inn og klikker på fanen «Sett inn» og velger deretter punktdiagram som vist på figur 15.

Figur 15 Punktdiagram i Excel 2007

Du kan velge om du vil plotte bare punktene, om det skal være linjediagram eller utglattet; om punktene skal være med eller ikke. Når du har klikket på ett av disse alternativene, vil du få tegnet inn diagrammet i regnearket. Dette diagrammet kan være greit nok slik det står, men det hender ofte at vi ønsker å endre verdien på aksene (for eksempel om de er noe annet enn 1, 3, 4, osv). Da klikker du på «Merk data» og velger «rediger» i vinduet du da får opp. Klikk så i feltet med «Serie X-verdier» og marker deretter det som er x-verdiene i regnearket ditt. Se figur 17. Etter at du har gjort dine valg kan du få et diagram som vist på figur 16

10

Regneark med Excel

Figur 16 Temperaturen som funksjon av tiden.

Figur 17 Du kan endre verdiene på xaksen ved å klikke på Merk data, så rediger og til slutt Serie x-akse.

Eksempel 5 Plott grafen til funksjonen y = x 2 + 2x − 3. Løsning: Vi er nødt til å lage en tabell over en del verdier for funksjonen. Vi lager derfor et regneark der vi skriver inn en del verdier for x og regner ut de tilhørende verdiene for y:

Søyle/stolpediagram

11

Legg merke til at x 2 + 2x − 3 skrives inn som =A2ˆ2+2*A2-3 i celle B2. Denne formelen autokopierer vi nedover og får på den måten en verditabell for funksjonen. Så markerer vi tallene og klikker på Sett inn Diagram. Vi velger punktdiagram og alternativet «Punktdiagram med utjevnede linjer». Etter litt pynting og fikling får vi følgende diagram:

Oppgave 7 Vi har målt høyden til et tre i årene 1972 til 2004 og fått følgende målinger: År Høyde (m)

1972

1976

1980

1984

1988

1992

1996

2000

2004

1,0

1,6

2,6

3,8

5,9

7,5

8,6

9,1

9,5

Plott høyden som en funksjon av årstallet (årstallene på x-aksen og høyden på y-aksen).

12

Regneark med Excel

Figur 18 Du kan formatere aksene slik du måtte ønske.

4 Innebygde funksjoner Vi har sett at vi kan lage enkle formler i Excel. Det kan være å multiplisere tallene i to celler. Formelen vi da skriver kan for eksempel være =A2*B5. Vi har også summert tall i rekker/rader ved å bruke autosummer (P). I Excel finnes det mange slike innebygde funksjoner, og vi skal her bli kjent med noen av disse. I Excel er det innebygget en egen funksjonsveiviser. Denne finner du ved å klikke på ikonet (➊ på figur 1 side 1. Se også figur 19)

Middelverdi Vi vil nå bruke funksjonsveiviseren til å regne ut middelverdien (også kalt gjennomsnittet) for et datasett. Middelverdien regner vi ut ved å summere sammen alle tallene vi skal regne ut middelverdien til og dele på antall tall i datasettet. Dette er noe vi lett kan la programmet gjøre for oss. Eksempel 6 Vi skal ved hjelp av Excel regne ut middelverdien til tallene 1, 3, 4, 3, 4, 2, 4, 3, 2, 3, 4, 3. Vi førere da tallene inn i et regneark og går til den cellen vi vil at svaret skal stå i: Vi klikker så på for å sette i gang funksjonsveiviseren (figur 19 og 20). Velg gjennomsnitt i listen til venstre. For å redusere antall funksjoner, kan det være lurt å velge kategorien statistikk. Klikk så ok. Da er det på tide å spesifisere hvilke tall som det skal regnes ut gjennomsnitt av. I stedet for å

Middelverdi

13

Figur 19 Når du skal få Excel til å regne ut noe ved hjelp av de innebygde funksjonene klikker du i den ruten du vil ha svaret (i dette tilfellet B15), klikker på fx knappen (1), velger kategori (2) og finner funksjonen du vil bruke (3).

Figur 20 Vi markerer de cellene hvis tall vi skal regne ut gjennomsnittet til (A2-A13).

skrive dette manuelt inn i ruten ved siden av tall 1, kan vi gå i regnearket vårt direkte og markere de aktuelle tallene (se figur 20). Så er det bare å klikke på OK og gjennomsnittet er regnet ut! (Det ble 3). Oppgave 8 Regn ut medianen til tallene i eksempelet over. (Gjør det samme som vi gjorde for middelverdi, men velg median i stedet for gjennomsnitt. Svaret er 3. )

14

Regneark med Excel

Median Eksempel 7 Nedenfor er alderen til studentene i klasse 1f på lærerskolen ført inn: 18

18 19 19

19

22

23 27

31

Vi ønsker å finne medianene, som er definert som den observasjonen som ligger i midten etter at observasjonene er ordnet i stigende rekkefølge. Her er det lett å se at svaret er 19, men når antall observasjoner øker blir det en slitsom og kanskje litt kjedelig jobb å ordne dem etter rekkefølge. Da kan det være greit å bruke en egen funksjon som heter median. Denne finner du under statistikk på funksjonslisten. Oppgave 9 Regn ut medianen til observasjonene ovenfor ved å bruke Excel. Hva om en av studentene på 18 år sluttet. Hva blir medianen da?

tilfeldig, tilfeldigmellom og antall.hvis To andre nyttige innebygd funksjoner i Excel er tilfeldig og tilfeldigmellom(;). La oss si at du ønsker å simulere et terningkastforsøk. Du vil med andre ord at Excel skal generere et tilfeldig helt tall mellom 1 og 6. Du skriver da i en celle formelen =tilfeldigmellom(1;6).

Figur 21 Vi kan simulere mange terningkastforsøk i Excel ved å autokopiere formelen i celle A1.

Når vi har skrevet inn denne formelen, kan vi utvide forsøket ved å autokopiere cellen til andre celler (se figur 5 side 3). På den måten får vi gjort forsøket så mange ganger vi måtte ønske (Figur 21). Trykk F9 for å oppdatere regnearket ditt, slik at du kan få nye tall.

Trendlinje

15

Neste utfordring blir så å plassere resultatene i en frekvenstabell. Det er selvsagt en håpløs jobb å gjøre dette manuelt. Hva om vi ønsker å gjøre forsøket på nytt? Vi bruker derfor funksjonen antall.hvis. Med denne kan du spesifisere området du vil telle fra og hva som skal telles. I figur 22 har vi laget en frekvenstabell ved å bruke antall.hvis-funksjonen.

Figur 22 Funksjonen antall.hvis. Legg merke til at vi har valgt området til å være A:A. Det vil si at vi skal telle i hele kolonne A. Vilkåret er C3. Det vil si at vi teller antall forekomster i kolonne A som er lik det som står i celle C3 (det vil si lik 1)

I celle D3 har vi skrevet inn formelen =antall.hvis(A:A;C3). Det betyr at vi skal telle antall celler i kolonne A som har samme verdi som C3. Vi bruker så autokopier og lager hele frekvenstabellen.

Oppgave 10 Gjør forsøket over. Presenter resultatet ved hjelp av søylediagram. Oppdater regnearket og se hva som skjer. Hvor mange terningkast bør vi ha før det resultatet begynner å bli relativt nok så jevnt (det vil si til det er omtrent like mange av hvert slag)?

Trendlinje Vi har sett at det går an å plotte en kurve ved bruke diagramveiviseren. En annen mulighet som finnes i Excel er å finne linjen som best passer til et sett med punkter. Eksempel 8 I et forsøk måler elevene spenning og strøm for en motstand. De fikk følgende måleserie: I (A)

0,4

0,8

1,2

1,6

2,0

U (V)

0,3

0,4

0,5

0,7

0,9

Når vi plotter disse punktene i et diagram får vi:

16

Regneark med Excel

U 1,0 b b

0,5

b b b

0,5

1,0

1,5

2,0 I

Vi ser at punktene ligger så godt som på en linje. Vi ønsker å finne linjen som passer best de gitte punktene (i en eller annen forstand – som vi ikke kommer inn på her). Vi fører dataene inn i et regneark, markerer dataene, klikker på fanen «Sett inn» og velger punktdiagram. Når vi har plottet punktene i et diagram, høyreklikker vi på ett av punktene. Da vil vi få opp et vindu hvor vi kan velge «Legg til trendlinje. . . » (se figur 23) Du vil da få opp et nytt

Figur 23 Vi kan legge til trendlinjen til et sett med punkter.

vindu hvor du kan velge hvilke type kurve du vil tilpasse datasettet med. Du kan også velge om kurven skal gå gjennom ett gitt punkt (for eksempel origo) og om du vil at funksjonsuttrykket skal være med i diagrammet. Se figur 23. Dersom vi velger «
Andre funksjoner Det er fint om du tar deg litt tid til å lete og leke med Excel. Da vil du snart finne ut at det er en rekke ting den kan regne ut, som for eksempel typetall

Verktøy i Excel

17

Figur 24 Valg for trendlinjen. Du kan velge hvilke type trendlinje (1) og andre alternativer (2)

(modus), skjevhet, varians og standardavvik. Prinsippet for bruken av disse er den samme. Marker ruten du vil ha svaret i, klikk på den funksjonen du vil bruke og marker datamaterialet ditt.

5 Verktøy i Excel Sortering og lister Dersom du har laget en liste med en del data, så kan Excel sortere listen for deg. Det er mulig å sortere på tre nivå. Eksempel 9 I tabell 3 er resultatene etter en prøve i matematikk ført opp.

18

Tabell 3 Resultater fra en prøve

Regneark med Excel

A

B

1

Navn

Poeng

2

Lise

23

3

Anne M

21

4

Geir

12

5

Trine

14

6

Truls

11

7

Tor

19

8





C

D

E

F

Vi ønsker nå å kunne sortere denne listen alfabetisk og etter poeng. Marker alle cellene og klikk på fanen «Data». Du vil da kunne velge «Sorter» i menyene som vist på figur 25. Klikk på OK og listen er sortert!

Figur 25 Sorteringskriterier i Excel. Her sorterer vi først etter poeng og deretter navn. Begge i stigende rekkefølge.

Eksempel 10 Det finnes en annen måte å få orden på lister på. Vi kan legge et filter på listen. Marker alle kolonnene du vil legge et filter på. Velg Data-fanen og klikk på «Filtrer» (se figur 26). Du kan nå enkelt filtrere ut enkelte data ved å klikke på kanppen for å velge for eksempel Jens.

Målsøking

19

Figur 26 Autofilter i Excel.

Målsøking Hva skal du gjøre om du vet hva resultatet av en formel skal være, men vet ikke hva som må stå i en annen celle for å få dette resultatet? La oss vise ved hjelp av noen eksempler hvordan slike oppgaver kan løses med Excel. Eksempel 11 Hvor mye penger må du sette inn i banken for at det skal stå 2000 kr på konto etter ti år når rentefoten er 3,3 %? Vi setter opp et regneark som på figur 27. I celle A3 har skrevet 1000 kr (som ikke gir det ønskede resultat). Vi går så til celle E12 (hvor vi ønsker det skal stå 2000 kr etter at vi har endret beløpet i calle A3.) Velger vi «Målsøking» under «Hvaskjer-hvis-analyse» vil vi få opp et dialogvindu som spør hvilke verdi du ønsker å sette cellen til og hvilke celle som skal endres. Skriv inn 2000 og A3 i feltene (som vist på figur 27) og klikk så på Ok. Da vil du få beskjed om hva resultatet av målsøkingen ble. Eksempel 12 Vi kan bruke «Målsøking» til å løse likninger. La oss se på likningen x 2 − 5x + 1 = 0 (1) Vi lager da et regneark der vi velger en eller annen verdi i celle A1, for eksempel 1, og skriver inn formelen =A1ˆ2-5*A1+1 i celle A2. (Merk at vi bruker tegnet ˆ for å lage potenser i Excel.) Du vil da få −3 som svar i A2. For å løse likning (1) vil vi at det skal stå 0 i celle A2. Vi går derfor til denne cellen, velger «Målsøking. . . » og skriver inn 0 som «Til verdi» og A1 som cellen som skal endres. Klikk ok og få 0,21 som en løsning.

20

Regneark med Excel

Figur 27 Tilpass verdien til en celle ved å bruke «Målsøking».

Merknad: Slike kvadratiske likninger kan ha to løsninger. Excel vil da velge den løsningen som er nærmest den verdien vi startet med i celle A1. Gjenta prosedyren ovenfor, men velg nå A1 lik 10. Da skal du få den andre løsningen av (1), nemlig 4,79. Oppgave 11 Løs likningene ved å bruke metoden over: a) 2x + 4 = 10 b) x 2 − 4x − 5 = 0 c) x 2 − x + 4 = 10 d) 10 000 ċ 1,02x = 20 000 e) 2x − x 3 = x 2 + 1

Oppgaver

21

6 Oppgaver Oppgave 12 Ole har fått seg ekstrajobb. I oppsettet nedenfor finner du opplysninger om Oles skatteprosent og inntekt i januar 2003. Lønn Skatteprosent ’

20 Januar 2003

Bruttoinntekt (kr)

5000

Skatt (kr)

1000

Nettoinntekt (kr)

4000

Bruk regneark når du løser disse oppgavene: a) Hvilken formel må du bruke for å regne ut skatten? b) Hvilken formel må du bruke for å regne ut nettoinntekt? c) I februar har Ole en bruttoinntekt på 7800 kr. Hva blir nettoinntekten hans denne måneden?

Oppgave 13 Liv jobber på i et grossistlager og har mottatt følgende bestilling: 1 offroad-sykkel til 1990 kr 3 sykler til 2550 kr per stykk 2 sykler til 3990 kr per stykk

Kunden må betale 25% i merverdiavgift (mva). Hva må kunden betale? Bruk regneark når du løser oppgaven. Begynn gjerne slik som figuren ved siden av.

Salg av sykler MVA (%) Tekst

25 Antall

Enhetspris i kr

1

1990

Offroadsykkel

Oppgave 14 (Tatt fra skolenettet.no) I juni 1970 tok Svein ut 100 kr fra Postsparebanken. Det stod da igjen 1 kr på kontoen. Svein glemte det hele inntil han i 1992 fikk beskjed om at han hadde 33 kr i banken. I løpet av de 22 årene hadde kontoen vokst med 32 kr! Hvor mange prosent rente hadde Svein fått hvert år? Her er plan for et regneark som kan brukes for å finne rentefoten:

Pris i kr

22

Regneark med Excel

A

B

Rentefot

p

3

År:

Beløp

Rente:

4

0

1

=B4*$B$1

5

1

=B4+C4

1

C

2

Tabell 4 Kopier formlene nedover ved å bruke autokopiering

Oppgave 15 Gunnar har fått sommerjobb som jordbærplukker hos Pettersen. Han får 8 kr for hver liter han plukker. I tillegg får han 70 kr per dag. Tabellen viser hvor mye han plukker den første uka. Mandag Tirsdag

Onsdag

Torsdag Fredag

90 liter

70 liter

80 liter

85 liter

95 liter

a) Hvor mye tjener han de forskjellige dagene, og hvor mye får han i ukelønn? Bruk regneark når du løser oppgaven. Begynn gjerne slik: Lønn for jordbærplukking Fast lønn per dag

70

Lønn per liter i kr

8

Dag Antall liter

Mandag

Tirsdag

90

85

Onsdag

Torsdag

Fredag

Ukelønn

Lønn i kr per dag

Trond har sommerjobb som jordbærplukker hos Andersen. Han får 10 kr for hver liter han plukker, men ikke noe fast beløp per dag. Hver dag den første uka plukker Trond akkurat like mye som Gunnar. b) Bruk regneark og finn ut hvor mye Trond tjener hver av dagene, og hvor mye han får i ukelønn. c) Lag et diagram som viser både hva Gunnar og hva Trond tjener hver av dagene. Hvem ville du helst ha jobbet for, Pettersen eller Andersen?

Oppgaver

23

Oppgave 16 Per, Anders og Line eier en hytte sammen. De skal betale utgiftene til strøm i forhold til hvor mange kilowattimer (kWh) hver av dem bruker. De leser av målerstanden hver gang de kommer til hytta og hver gang de reiser fra hytta. På den måten finner de ut hvor mange kWh hver av dem har brukt. Første halvår 2003 ble forbruket slik: Navn

Målerstand ankomst (kWh)

Målerstand avreise (kWh)

Per

127592

127788

Line

127788

128129

Anders

128129

128612

Line

128612

129578

Strømforbruk (kWh)

Prisen på en kilowattime (kWh) er 78 øre. Bruk regneark til å finne ut hvor mye hver av de tre skal betale.

Oppgave 17 Kari satte for 10 år siden 9000 kr i banken. I dag har dette forrentet seg til 13451 kroner. a) Hva er rentefoten? (vi antar at det er fast rente på denne kontoen) b) Hvor mange år går det før startkapitalen er fordoblet? c) Turid satte 15 000 kroner i banken samtidig med Kari. Hvor mye har hun i dag? d) Hvor lang tid tar det før Turid har 30 000 kroner på konto? Oppgave 18 (Lønn til avisbud (fra skolenettet.no)) Arild går med avisen «Gladnytt». Han betaler ikke skatt. Derfor får han utbetalt hele lønna. Slik beregner «Gladnytt» Arilds lønn for en måned: Sykkelpenger:

115,00 kr

Grunnlønn:

148,00 kr

Aviser:

4,30 kr per avis (gjennomsnitt per dag)

Slett rute:

45,50 kr per km

Bakkete rute:

55,50 kr per km

Minstelønn:

650,00 kr per måned

Arilds budrute er 4 km på slett vei og 1,3 km i bakke.

24

Regneark med Excel

a) En måned hadde Arild 97 aviser i gjennomsnitt per dag. Hvor mye fikk han utbetalt denne måneden? b) En måned kom avisen ut bare 22 dager. Hver dag noterte Arild hvor mange aviser han hadde: 97–97–97–97–96–95–95–95–98–98–98–98 98–98–98–99–99–99–99–99–99–99–97–97 Hvor mange aviser hadde Arild i gjennomsnitt per dag? c) Hvor stor var lønna denne måneden? d) Lag plan for et regneark som beregner lønn for avisbud. Bruk regnearket og prøv deg fram: Et avisbud fikk 1020 kr lønn. Lag et par forslag til antall aviser og lengde på denne budruta. Vurdér Hva synes du om denne måten å beregne lønna på? Er alle tallene enkle å bestemme? Kan du lage en bedre modell for beregning av lønn? Oppgave 19 (Tatt fra skolenettet.no) Elevene ved Lie skole skal på klassetur sammen med tre lærere. De ber om pristilbud fra tre selskap. Ryenruta: 1100 kr i fast avgift 5,50 kr per kilometer

Hagenruta: 4000 kr for turer opp til 800 km

Lie turservice: 9 kr per kilometer

Elevene vurderer tre mulige turer: Sted km én vei Kristiansand Dyrepark ca 200 Tusenfryd ca 140 Hunderfossen ca 320 Gjør overslag Hvilket av tilbudene er best til hvert av de tre reisemålene? Hvor langt kan elevene komme for 3500 kr? Tabell i regneark Bruk et regneark og lag en tabell som viser pris for ulike avstander. Regnearket kan for eksempel se slik ut:

Oppgaver

25

A

B

C

D

1

Antall km

Ryenruta

Lie turservice

Hagenruta

2

200

??

=A2*9

4000

3

210

4

220

=D2

Tabell 5 Kopier formlene nedover ved å bruke autokopiering

Hvilken formel kan du bruke rute B2? Viser tabellen nøyaktig hvor mange km en må kjøre for at det skal bli lik pris med Ryenruta og Lie turservice? Hva om du sammenlikner Lie turservice og Hagenruta? Sammenlikn også Hagenruta og Ryenruta. Bruk kurvetegningsprogram Lag et funksjonsuttrykk som viser prisen (y kr) for ulike avstander (x km) med hvert av de tre selskapene? Tegn alle tre kurvene i samme koordinatsystem. Bruk programmet til å finne koordinatene for skjæringspunktene mellom kurvene. Hvilken informasjon gir koordinatene for skjæringspunktene? Vurdér Kunne du funnet svar på spørsmålene over uten å bruke dataverktøy? Hvilke fordeler og ulemper ser du med å bruke dataverktøy på slike problem? En ekstra utfordring Hvor mye tar hver av selskapene per km?

Oppgave 20 Lag et regnearkoppsett til følgende oppgave: Plasser tallene fra 1 til 9 i rektanglene under slik at summene av de fire tallene langs hver av sidene i trekanten blir like.

Du bør med andre ord lage et regneark hvor du kan skrive inn tallene i rektanglene og hvor regnearket regner ut summen langs de tre sidene i talltrekanten. Regnearket kan se slik ut:

26

Regneark med Excel

Figur 28 Løsing av oppgave 20 på regneark

Ekstra utfordring: Bruk hvis (eller dersom i Calc på nynorsk) til å sette opp regnearket slik at det står «Bingo!» i en celle dersom alle tre summene er like.

Oppgave 21 Studer oppstillingen:

A

B

C

D

E

1

1

2

3

2

4

5

6

3

7

8

9

63

27

100

3

4 5 6

7

F

Tallene 1,. . . , 9 er satt inn i hver sin rute i området A1:C3. I noen ruter i kolonne E er det skrevet formler. Det står for eksempel =A1*C1 i E1 og =E1+E3+A5+C5 i E5. Finn ut hvordan oppstillingen er og lag det på regnearket. Finn ut hvordan tallene i området A1:C3 må stå for å få summen i rute E5 størst mulig og minst mulig. Prøv å lage tilsvarende oppgaver av denne typen.

Oppgave 22 (Spillet Blink, Tatt fra Breiteig and Fuglestad (2000)) Arbeid to og to sammen. Velg et tall som skal være mål, for eksempel 100. Skriv det inn i rute B3. Velg et tall til å starte med, for eksempel 17. Sett det i rute B5. Oppgaven videre er å sette inn tall i kolonne C, slik at det multiplisert med nabotallet i B-kolonna blir så nære 100 som mulig. Den som først treffer (eller kommer nær nok) målet, vinner.

Oppgaver

27

B

A 1

C

D

Blink – treff tallet!

2 3

100

Målet er:

4

Forsøk

5

Start med tallet:

17

5,5

1. forsøk

6

Neste tall:

93,5

1,1

2. forsøk

7

Neste tall:

102,85

8

Neste tall:

3. forsøk 4. forsøk

Oppgave 23 Nedenfor er det tegnet opp et kvadrat med 9 ruter. Du skal sette inn tallene 0, 1, 2, 3, 4, 5, 6, 7 og 8 slik at «summen» blir størst mulig. «Summen» finner du ved å legge sammen: (1) alle produktene du får ved å multiplisere to tall ved siden av hverandre. I eksempelet under: 0 ċ 1 + 1 ċ 2 + 3 ċ 4 + 4 ċ 5 + 6 ċ 7 + 7 ċ 8 = 132 (2) alle produktene du får ved å multiplisere to tall under hverandre. I eksempelet under: 0 ċ 3 + 3 ċ 6 + 1 ċ 4 + 4 ċ 7 + 2 ċ 5 + 5 ċ 8 = 100 Du får den søkte «Summen» ved å addere de to tallene i (1) og (2): 132 + 100 = 232. 0

1

2

3

4

5

6

7

8

Løs denne oppgaven ved å bruke et regneark som hjelpemiddel.

Oppgave 24 Tom og Susan er på besøk hos besteforeldrene som har gård. De går bort til noen sauer og kyllinger. Tom sier: «Jeg ser dyr». Susan sier: «Jeg ser 52 bein til sammen» Hvor mange dyr er det av hvert slag?

28

Regneark med Excel

Oppgave 25 Noen personer går på kafé. Der kjøper de kaffe til 5 kr pr kopp og kake til 9 kr pr stykke. Alle bestiller det samme og de betaler 133 kroner til sammen. Hvor mange kopper kaffe drakk hver person? Bruk et regneark til å løse oppgaven.

Oppgave 26 Noen elever ble spurt om hvor mange spill de hadde hjemme. Resultatet ble: 5

3

7

5

8

7

6

7

a) Framstill resultatet i en frekvenstabell og i et passende diagram. b) Finn middelverdien, medianen og typetallet. Hvordan tolker du resultatet? c) Finn standardavviket for datamaterialet. Hvor stor del av elevene er innenfor ett standardavvik? Tolk resultatet

Oppgave 27 Klasse 10g har hatt prøve i matematikk. Resultatet fordelte seg slik: Karakter

Antall elever

6

2

5

5

4

6

b) Fremstill resultatene i et sektordiagram.

3

11

c) Hva er gjennomsnittskarakteren?

2

5

1

1

a) Fremstill resultatene i et søylediagram.

Oppgave 28 Lag et regneark som viser oversikten over karakterene på en prøve i en klasse. Dikt opp 12 navn og dikt opp 12 karakterer til disse elevene. a) Sorter listen etter karakterer og navn. b) Sett opp regnearket til å telle antall enere, toere, treere, osv. ved å bruke antall.hvis (tal.dersom dersom du bruker nynorsk-versjonen av Calc). Du skal med andre ord lage en frekvenstabell. c) Be regnearket regne ut gjennomsnittet, medianen og typetallet (modus) på prøven. Husk å skriv tekst som forklarer hva det er du regner ut!

Oppgaver

d) Be regnearket om å formatere alle karakterene under 3 med rød skrift. Dette gjør du ved å velge «Betinget formatering» under «Format» på menyen i Excel. I Calc finner du det som «Vilkårsformatering» under «Format» i menyen. Merk at du i Calc først må lage en egen stil med rød tekst i «Stilhandsamar».

Oppgave 29 Vi kaster en terning. Hva er da sannsynligheten for å få en sekser? Simuler forsøket ved å bruke TILFELDIG() (i Excel) eller TILFELDIGMELLOM (i Calc). Lag en frekvenstabell og framstill forsøket grafisk ved hjelp av et stolpediagram. Oppgave 30 Det er blitt gjort en spørreundersøkelse i klassen 6a og klassen 6b. Resultatet fra undersøkelsen ble: Høyde i 6a: 140, 150, 142, 155, 174, 161, 142, 155, 169, 155, 173, 161, 163 Skonummer i 6a: 34,35,41,36,37,42,43,37,38,37,39, 37, 36 Høyde i 6b: 147, 156, 156, 159, 176, 147, 161, 170, 156, 155, 163, 141, 169,169, 149 Skonummer i 6b: 39, 37, 42, 34, 36, 38, 38, 42, 40, 39, 37, 36, 35, 34, 40 Legg inn dataene fra undersøkelsen. Eksperimenter med fremstillingsformer. Finn beliggenhetsmål og spredningsmål for de to klassene og for den samlede gruppa med 6. klassinger. Hvordan vil du tolke resultatene på undersøkelsen?

Oppgave 31 Vi kaster tre mynter. Hva er da sannsynligheten for å få to kron og én mynt? a) Lag et regneark som simulerer forsøket. La for eksempel K=0 og M=1 og bruk funksjonen =AVRUND(TILFELDIG();0) til å velge ut 1 eller 0 tilfeldig. b) Gjør forsøket 50 ganger i regnearket og regn ut antall utfall der det ble (i) tre kron (ii) to kron og én mynt (iii) én mynt og to kron (iv) tre kron c) Hva er utfallsrommet her dersom vi skal bruke en uniform sannsynlighetsmodell?

29

30

Regneark med Excel

Oppgave 32 (Eskeproblemet)

21 cm

29,7 cm Vi kan lage ei eske uten lokk av et A4-ark. Der det er heltrukket linje må vi klippe. Ei stiplet linje viser hvor vi skal brette. a) Hvilke geometriske figurer ser du på tegningen over? b) Hvor langt inn må du klippe hvis du klipper 4 cm fra et hjørne? Lag ei slik eske. Beregn volumet. c) Hvor høy er den eska dere laget? Lag esker med andre høyder. Beregn volumene. d) Kan dere lage ei eske som rommer nøyaktig én liter? Hva er det største volumet dere kan få? e) Det fins en sammenheng mellom høyden på eska og volumet av den. Lag en grafisk framstilling av sammenhengen. Oppgave 33 (Smørpakken) En fabrikk skal lage en smørpakke som veier 500 g. Volumet av smørpakka blir da 500 cm3 . De vil at lengden på pakka skal være dobbelt så stor som bredden. Eska må naturligvis ha lokk. Av en tom melkekartong (1 liter) kan du lage modell av ei smørpakke:

h 7 cm

14 cm

Hvilken høyde må denne smørpakka ha? Hvor stor blir overflaten? Hvilken høyde måtte smørpakka ha hvis bredden var 6 cm og lengden 12 cm? Hvor stor ville overflaten da være? Hvilke mål må vi bruke for å få overflaten minst mulig?

Oppgaver

31

Oppgave 34 (Avgangseksamen i grunnskolen 1991) (Bruk regneark til å løse oppgaven.) Lise har 100 meter netting som hun skal bruke til å gjerde inn et rektangelformet jordstykke. Området ligger ved en elv. Lise vil at arealet av jordstykket skal bli så stort som mulig. Hun velger derfor å la elva utgjøre den ene siden av området fordi det ikke trengs noe gjerde mot elva. Lengden av siden som er parallell med elva, kaller hun b, og lengden av hver av de to andre sidene kaller hun a.

a) Hvor lang er b hvis a er 10 m, og hvor stort er arealet av området da? b) Sett a lik: 20, 30, 40, 50. Regn ut b og arealet (A) av området for hver av de oppgitte verdiene for a. Sett de resultatene du fikk i oppgave a) og oppgave b) opp i en oversiktelig tabell. c) Utrykk lengden av gjerdet (100 m) ved hjelp av a og b. d) Vis at arealet av området kan skrives slik: A = 100a − 2a 2 . e) Finn ut hvor lang a må være for at arealet skal bli størst mulig. Forklar hvordan du fant svaret. Oppgave 35 I tabellen under er det noen målinger av strøm og spenning for en motstand. I

0,3

0,5

0,7

0,9

1,1

U

0,1

0,15

0,2

0,28

0,34

a) Plott spenningen U som funksjon av strømmen I i et koordinatsystem. b) Finn en funksjonssammenheng (en formel) mellom U og I.

Referanser Breiteg, T. and A. B. Fuglestad (2000). Data i matematikken (2 ed.). Aschehaug. Breiteig, T. and A. B. Fuglestad (2000). Data i matematikken (2 ed.). Aschehaug. Lysø, K. O. (1999). Statistikk og sannsynlighetsregning. Caspar forlag.