Regnearkmodeller i Excel - tips og oppgaver

Lag regnearkmodeller for hver av situasjonene nedenfor. Bruk formler og ledetekster slik at regnearket er brukervennlig og lett forståelig. For en del av oppgavene er det delvis ferdige ark tilgjengelig som du kan arbeide videre med. I disse er det også flere opplysninger til oppgaven. For noen oppgaver er det også en fasit. Vil du lagre et av disse regnearkene på din egen maskin/område, kan du høyreklikke med musa og velge mellom å åpne regnearket eller lagre det.

Først er det noen oppgaver hvor regnearkene kan brukes til gjentatte beregninger med ulike datasett. Deretter er det noen oppgaver hvor regnearket benyttes til presentasjon av data.

A) Regneark for gjentatte beregninger med ulike data.

Kikk gjerne først på Praktiske eksempler på bruk av regneark i www.brukersystemer.net. Oppgavene nedenfor bygger i stor grad på bruk av HVIS-funksjonen og FINN.RAD-funksjonen. Trenger du trening i å bruke HVIS-funksjonen, kan du finne tips og oppgaver i ulik vanskelighetsgrad her: Excel tips: HVIS-funksjonen.

1) Enkel rabattberegning

fig 1

Lag et regneark som skal se omtrent ut som figur 1. Sum skal beregnes ut fra Enhetspris og Antall. Så skal brukeren få beregnet rabatt ved salg over en viss grense. Bruk grenseverdien 1000 kr, slik at hvis det selges for over 1000 kr skal det gis en rabatt på 7,5%. Brukeren skal taste sine inndata i de gule cellene. I de blå cellene skal det stå beregningsuttrykk som gir riktige utdata. Tips: Bruk HVIS-funksjonen for å få til dette! Delvis ferdig regneark, uten formler: enkel_rabattberegning.xls.

fig 2

Så skal du gjøre et par endringer i dette regnearket: Det er alltid en fordel at tall som brukes i formler er synlige i selve regnearket. På denne måten blir bruken sikrere og det blir letter å vedlikeholde regnearket. I begynnelse av denne oppgaven la du inn en test i HVIS-funksjonen på hvorvidt en celleverdi var større enn 1000, og da skulle det gis en viss rabattsats, 7,5%. Nå skal du ordne det slik at disse grenseverdiene blir synlig i egne celler i regnearket, f.eks. slik som i figur 2. (Husk å formatere feltet med rabattprosenten som prosent!) Fasit

2) Timeplan

Lag et regneark som skal se ut omtrent som figur 3. Her skal du lage en (forenklet) timeplan for en skoledag på bare 3 timer.

fig 3

Anta at du skal kunne skrive inn ukedagens nummer (1=mandag osv) i celle C3. Så skal dagens navn og timeplanen komme opp i kolonne F. Bruk FINN.RAD-funksjonen med oppslag i en tabell. Tabellen skal ligge i et eget regneark, Tabeller. Legg også inn et uttrykk i celle F1 som viser en gledelig tekst hvis ukedagen er 7, dvs søndag! Delvis ferdig regneark, uten formler, og med tilleggsopplysninger: timeplan.xls. Fasit

3) Utvidet rabattberegning

Lag et regneark som skal se omtrent ut som figur 4. Her skal en bruker kunne beregne rabatt ved salg til kunder. Brukeren taster sine inndata i de gule cellene. I de blå cellene skal du plassere beregningsuttrykk som gir riktige utdata.

fig 4

Det skal først beregnes hva summen blir utfra enhetspris og antall. Dette skal stå i celle C7. Så skal det beregnes en kvantumsrabatt utfra en tabell. Bruk der FINN.RAD-funksjonen for å slå opp rabatten utfra hvilket beløp som blir resultatet i celle C7. Husk at rabatten her tilsvarer salgssummen ganget med rabattprosenten. Det skal også beregnes en fastkunde-rabatt. Her bruker du en HVIS-funksjon som tester om det står J eller N i celle C3. Delvis ferdig regneark (uten formler) og med tilleggsopplysninger: rabattberegning.xls. For første del av oppgaven er det en Fasit.

4) Videoutleie og -salg

Lag et regneark som skal se ut omtrent som figur 5. Her driver du en butikk for utleie og salg av video/DVD. Du selger også CD-plater. Selgeren skal bare behøve å fylle ut de gule feltene: Han skal oppgi varenummeret (som står på varen) og antall. Ved hjelp av oppslag i tabeller på et eget ark skal feltene i kolonne F komme automatisk med varens navn, pris, eventuell rabatt og tilbakeleveringsfrisk for video/DVD (eldre filmer på video kan beholdes i 3 døgn, ellers er det 1 døgns leietid). Bruk igjen FINN.RAD-funksjonen.

fig 5

Her skal det gis rabatt på 8% hvis salget er 200kr eller mer, 15% for 500kr eller mer og 20% for salg på 1000kr eller mer.

Bruk 2 ulike tabeller i arket Tabeller, én for å slå opp varenavn og varepris og én for å slå opp rabatten.

Delvis ferdig regneark, uten formler, men med noen tilleggsopplysninger finner du her: videoutleie.xls. Ferdig regneark, med formler: Fasit.

5) Skiutleie

Lag et regneark som skal se ut omtrent som figur 6. Her skal du drive skiutleie ved et alpinanlegg.

fig 6

Som vanlig er inndatafeltene markert med gult, det er her brukeren skal taste inn sine opplysninger: Utstyrets nummer, hvor mange dager utleien skal gjelde og om kunden er en fast kunde.

I de blå feltene skal det igjen stå formler.

fig 7

I et nytt ark, Tabeller, kan du legge inn følgende opplysninger for oppslag (se figur 7).

Bruk FINN.RAD- og HVIS-funksjonene for å bygge opp beregningsuttrykk i kolonne F i arket Prisberegning.

Delvis ferdig regneark, uten formler, men med noen tilleggsopplysninger finner du her: skiutleie.xls.

6) Grunnstoffene

I Excel-arbeidsboken grunnstoff.xls finner du en liste over grunnstoffene med atomnummer, symbol, norsk og engelsk navn, atommasse, tetthet i gram pr kubikkcentimeter, smeltepunkt og kokepunkt. Listen starter slik som du ser i figur 8.

fig 8

Nå vet du kanskje at vann har en tetthet på 1 g/cc. Nå skal du lage et nytt regneark i Ark 1 i denne arbeidsboken. Der skal du kunne skrive inn atomnummeret til et grunnstoff og automatisk få opp noen opplysninger om dette grunnstoffet i flere godt synlige celler. De opplysningene som skal komme frem er: Symbol, norsk navn, engelsk navn, atommasse, tetthet i gram pr kubikkcentimeter, smeltepunkt og kokepunkt. I tillegg skal det være en celle som viser om stoffet flyter (dvs om det er lettere eller tyngre enn vann). Det skal også være celler som viser i hvilken tilstand stoffet er ved vanlig romtemperatur, dvs ved 20°C. Tips. Ikke se på fasiten før du har gjort et hederlig forsøk!

B) Regneark for presentasjon av data

Data som ligger som rader med informasjon i et regneark kan presenteres på et utall ulike måter. Verktøyet for å gjøre dette er det som kalles pivottabeller. I oppgavene nedenfor anbefales det også å forsøke å lage andre oppsette etter egen fantasi. Du bør også for hver pivottabell bruke flere forskjellige av de mulighetene som ligger i Autoformat (under Formatmenyen) for å gi tabellene ulikt utseende og oppsett.

1) Fotball

Lag en liste over fotballspillere i et regneark med følgende fem overskrifter: Klubb, Spiller, År, Antall mål, Lønn. Den kan se ut omtrent som figur 9.

fig 9

Registrer to-tre spillere på hvert av to-tre ulike lag. Registrer data for 3 forskjellige år (ingen spillere skifter klubb i perioden). Antall mål skal være det antallet de scoret det året. Det blir altså én linje pr spiller pr år. Til sammen skulle dette bli 10-15 linjer. Et delvis ferdig regneark kan du finne her: fotball.xls

Navngi området slik at du kan bruke det i en pivottabell. Husk at områder som skal brukes i pivottabeller også må omfatte overskriftsraden!

Lag først en enkel pivottabell hvor du kan velge klubb. Informasjon om spillernes totale målscore og samlete inntekt skal vises i linjer nedover. Tips

Lag deretter en tabell hvor spillernes målscore og inntekt også er delt opp på hvert enkelt år. Tips

2 ) Hobbyfiskere

Lag en liste over fire fiskere på fisketur, to menn og to kvinner. Listen skal inneholde navnet på fiskeren, hva slags fisk som er fisket, hva fisken veide, dato og om fiskeren er mann eller kvinne. Hver fisk som fiskes skal registreres. Bruk fiskesortene: Berggylt, Mort, Uer, Makrell, Sei. Fisketuren varte i 3 dager. Lag listen i et eget ark i Excel-arbeidsboken. Et delvis ferdig regneark kan du finne her: fiskere.xls

fig 10

Lag en pivottabell som viser hvor mange som er fisket av de ulike fiskeslagene. Tips

Lag en annen tabell som viser hvor mange fisk av hvert slag hver enkelt fisker har fisket og hvor mye disse veide til sammen. Tips

Lag en tabell hvor du kan velge navnet på en fisker og få opp data om hva han har fisket på de forskjellige dagene.

Lag en tabell hvor du finner den prosentvise fordelingen av ulike fiskeslag, fordelt etter kjønnet på fiskeren. Tips

Lag en tabell hvor du kan velge en dato og så få opp den prosentvise fordelingen av ulike fiskeslag, fordelt etter kjønnet på fiskeren.

Finn selv på flere tabeller du kan lage med disse dataene.

3 ) Spørreundersøkelse

Pivottabeller kan egne seg godt for å presentere resultatene fra spørreundersøkelser og andre store lister. Det er nokså enkelt å sette opp en krysstabell, dvs en tabell som kobler sammen to eller flere av spørsmålene i undersøkelsen. Et regneark med resultatene fra en spørreundersøkelse om røyking kan du finne her: rking.xls. Dette kan være resultater fra en spørreundersøkelse på en eller flere videregående skoler. Spørsmålene elevene måtte svare på er følgende: Er du jente eller gutt? Røyker du? Røyker foreldrene dine? Røyker søsknene dine? Er du over 18 år? Drikker du alkohol?

fig 11

I figur 11 ser du litt av resultatene fra en slik spørreundersøkelse.

Lag først en krysstabell for å se på sammenhengen mellom elevenes og foreldrenes røykevaner. Tips

Lag også krysstabeller som viser følgende sammenhenger:
Mellom egne og søskens røykevaner. Mellom foreldres og søskens røykevaner. Mellom egne røykevaner og egne drikkevaner. Mellom alder (over eller under 18 år) og røykevaner. Mellom kjønn og røykevaner.

La alle tabellene vise resultatene både i antall og i prosent. Formater prosentene slik at de viser 1 desimal. Tips

Meny for IKT-fag | Toppen av siden