Meny for IKT-fag

Excel tips: HVIS-funksjonen

HVIS-funksjonens oppbygning

Ved avansert bruk av regneark, men også i arbeid med databaser og i en mengde andre sammenhenger, er det nødvendig å beherske bruken av HVIS-funksjonen. HVIS-funksjonen brukes til å foreta valg. Et typisk eksempel er for utregning av rabatt. Anta at en som har kjøpt for over 1000kr skal ha 5% rabatt, ellers ikke.

HVIS-funksjonen har følgende syntaks (skrivemåte):

=HVIS( ... ; ... ; ... )

Som alle regnearkfunksjoner, består denne av ulike deler:

• først kommer likhetstegnet, =
• deretter kommer navnet på funksjon, =HVIS
• deretter kommer to parenteser, =HVIS( )
• mellom de to parentesene har vi de tre parametrene som funksjonen bruker, =HVIS( ... ; ... ; ... ). Parametere er tall, tekst, celleadresser og annet som funksjonen tar som inndata.

Resultatet av funksjonen kommer til syne i den cellen funksjonen står i.

HVIS-funksjonen har 3 parametere som skilles med semikolon ( ; ).

[1] Først en betingelse. Dette er det som skal testes, undersøkes.
[2] Så kommer det som skjer hvis betingelsen er SANN.
[3] Til slutt kommer det som skjer hvis betingelsen er USANN.

=HVIS(  ...  ;  ...  ;  ...  )
        [1]      ^       ^
         |       |       |
        sann -->[2]      |
         |               |
       usann ---------->[3]

Eksempler

Alle eksemplene som følger bør du prøve selv i regnearket.

=HVIS( 3=3 ; 1 ; 0 )

Siden 3 jo er lik 3 så vil resultatet her bli at tallet 1 kommer til syne i cellen hvor funksjonen står. Eller sagt med andre ord: Første parameter er likheten 3=3. Dette vet vi alltid er sant. Da blir andre parameter utført i cellen. Andre parameter er tallet 1, altså kommer tallet 1 frem i cellen.

=HVIS( 3<2 ; "Stemmer" ; "Stemmer ikke" )

Siden 3 ikke er mindre enn 2 så vil her den tredje parameteren utføres og teksten "Stemmer ikke" vil komme til syne i cellen. Legg merke til at når det er tekster tilstede i funksjoner så må teksten omsluttes av anførselstegn (hermetegn). Sagt med andre ord: Her er første parameter ulikheten 3<2. Dette vet vi alltid er usant. Da blir tredje parameter utført i cellen. Tredje parameter er teksten "Stemmer ikke" som dermed kommer frem i cellen.

=HVIS( "A"="B" ; "Tull" ; "Tøys" )

Hva blir resultatet i dette tilfellet? Hvis du er et minste i tvil, sjekk dette i regnearket og gå igjennom alt til nå en gang til.

Oppgaver

Noen av oppgavene har en fasit her. Det er lurest å forsøke selv i et regneark før du kikker på fasiten!

1) Lag på liknende måte som i eksemplene ovenfor en HVIS-funksjon som tester om tallet 4 er mindre enn tallet 10. Hvis så er tilfelle skal funksjonen vise teksten "OK!". Ellers skal funksjonen vise teksten "Rent vrøvl!". Forsikre deg om at funksjonen virker som den skal.

2) Bytt ut tallet 4 i forrige oppgave med tallet 14. Se etter at resultatet blir som du forventer.

3) Lag et uttrykk som tester om teksten "EPLE" er ulik teksten "PÆRE". Hvis så er tilfelle skal uttrykket vise tallet 7, ellers tallet 8. (Tegnet for "er ulik" er slik: <>.) Fasit

4) Bytt ut teksten "EPLE" i forrige oppgave med teksten "pære" (obs: små bokstaver!) og sjekk resultatet. Skiller regnearket mellom store og små bokstaver ved ulikheter (sammenlikninger)?

 

Teste verdi i en celle

Vanligvis brukes HVIS-funksjonen til å teste verdien i en annen celle i regnearket, for så å utføre det ene eller det andre avhengig av hva verdien i den andre cellen var.

La oss tenke oss et lite oppsett for å teste om et tall er større enn 10. Tallet som skal testes skrives inn i celle A1. Resultatet vises som en tekstmelding.

=HVIS( A1>10 ; "Større enn ti" ; "Ti eller mindre" )

Denne funksjonen tester om det som står i celle A1 er større enn 10. Hvis så er tilfelle, kommer teksten "Større enn ti" til syne i cellen. Hvis dette ikke er tilfelle, kommer teksten "Ti eller mindre" til syne i cellen. Skriv denne funksjonen inn i en regnearkcelle, men IKKE i celle A1, siden adressen A1 brukes i funksjonen. Dette er en vanlig feil som kalles sirkelreferanse. Husk at tekstmeldinger må omsluttes av anførselstegn.

Prinsippet i foregående eksempel kan brukes til å beregne en rabattsats:

=HVIS( A1>1000 ; 0,05 ; 0 )

Denne funksjonen tester om det som står i celle A1 er større enn 1000. Hvis dette er tilfelle, kommer tallet 0,05 til syne i cellen, ellers blir det stående 0. Du kan om ønskelig formatere cellen som inneholder dette uttrykket med prosentformat.

Oppgaver

5) Lag et uttrykk som tester om tallet i celle D5 er større enn 19. La uttrykket resultere i teksten "Større" hhv. "19 eller mindre". Kontroller at uttrykket virker etter hensikten.

6) Lag et uttrykk som tester om tallet i celle B2 er lik 4. La uttrykket resultere i tallet 0,15 hvis det er sant, 0,45 hvis det er usant. Formater cellen som inneholder HVIS-funksjonen med prosentformat. Fasit

Flere eksempler

Lag et lite regneark med innholdet nedenfor. Celleadresser for tekst og beregningsuttrykk står først på linjen, deretter celleinnholdet.

C1: Rabattsats:
C2: Rabatt:
C3: Å betale:

D1: =HVIS( A1>1000 ; 0,05 ; 0 )
D2: =A1*D1
D3: =A$1-D2

Skriv ulike verdier i celle A1, både større, lik og mindre enn 1000 og studer resultatet av uttrykket i D1.

$-tegnet i adressen A$1 brukes for senere lett å kunne kopiere uttrykket i cellene C3 og D3 nedover til andre linjer.

Dette regnearket kan forenkles litt ved å slå sammen uttrykkene i cellene D2 og D3 slik som vist nedenfor. Utvid nå regnearket slik:

C5: Rabatt:
C6: Å betale:

D5: =HVIS( A1>1000 ; A1*0,05 ; 0)
D6: =A$1-D5

Resultatet skal bli det samme som i forrige eksempel. Her legger vi et regneuttrykk (A1*0,05) inn i HVIS-funksjonen i stedet for å bruke en egen celle til dette.

Men det kan jo tenkes at salgssituasjonen her skal være litt forskjellig fra dette. Kanskje selgeren skal avgjøre om det skal gis rabatt eller ikke. Da kan vi f.eks. teste på denne måten:

C8: Rabatt (R)?
C9: Rabatt:
C10: Å betale

D9: =HVIS( D8="R" ; A1*0,05 ; 0)
D10: =A$1-D9

Sørg for at det står ett tall i celle A1. Forsøk nå å skrive "R" i celle D8 og sjekk resultatet. (Ikke skriv anførselstegnene!) Forsøk på samme måte å skrive "S" i D8. Forsøk også med "r" (liten, ikke stor bokstav). Slett til slutt innholdet i D8.

Det som skjedde her var at funksjonen i D9 undersøker om det i celle D8 står bokstaven "R". Hvis så er tilfelle, regnes rabatten ut som 5% av A1. Hvis ikke, er rabatten 0.

OBS: Vanlig feil: Celleadressen til funksjonen brukes i den samme funksjonen. Dette fører til sirkelreferanse - uttrykket blir meningsløst.

Oppgaver

Lag uttrykkene i kolonne G fra G1 og nedover. Sjekk alltid at uttrykket virkelig gjør det som det skal!

7) Hvis A3 er mindre enn 500 skal G1 vise verdien av A3, ellers skal det regnes ut A3*0,95 dvs. 5% rabatt.

8) Hvis A3 er mindre enn eller lik 1200 skal G2 vise verdien av A3, ellers skal det regnes ut A3-A3*0,15 dvs. 15% reduksjon. Fasit

9) Hvis A4 er "MVA" skal G3 bli merverdiavgiften av det som står i A3. Merverdiavgift er 23%. Ellers skal G3 bli 0.

10) Hvis A4 er "S" skal G4 summere det som står i A2 og det som står i A3. Ellers skal G4 bli differensen mellom disse to celleverdiene.

11) Hvis A4 er "Sirkel" skal G5 beregne sirkelarealet for en sirkel med radius den verdien som står i A3. Ellers skal G5 vise verdien i A3. Arealet av en sirkel finnes ved uttrykket pi*r². Tallet pi er 3,14159 og skal stå i celle A7. Fasit

12) I A2 og A3 skal det stå to tall. Hvis A2 er større enn A3 skal G6 vise differensen mellom de to tallene, ellers skal G6 vise summen av de to tallene. Samtidig skal H6 i det første tilfellet vise teksten "Differanse" og i det andre tilfellet "Sum".

 

Nøstede HVIS-funksjoner

Det er vanlig at det er behov for å teste på mer enn én enkelt betingelse. Hvis vi fortsetter med rabatteksempelet ovenfor, så kan vi tenke oss at alle får 5% rabatt hvis salget er for 1000kr eller mer, men spesielle "superkunder" skal ha 10% rabatt!

Da må vi først ha en funksjon som tester på salgets størrelse, deretter en funksjon inne i denne som tester om det er en "superkunde".

C12: Superkunde (S)?
C13: Rabatt:
C14: Å betale:

D13: =HVIS(A1>=1000 ; HVIS(D12="S" ; A1*0,10 ; A1*0,05) ; 0)
D14: =A$1-D13

Her kommer det altså en ny HVIS-funksjon på plassen til parameter nr 2, den parameteren som blir utført hvis betingelsen i parameter nr 1 er SANN.

Legg også merke til ulikheten i betingelsen. >= betyr større enn eller lik.

Når brukeren skal gi inndata i form av koder, som her koden "S" for å angi en "superkunde", så kan det lett gå galt. Hva om brukeren taster "X" i stedet for "S"? Vi kan legge inn en test som viser en feilmelding ved gal kode:

E12: =HVIS( OG(D12 <> "S" ; D12 <>"") ; "Feil kode" ; "")

På plassen til parameter nr 1 ser vi her funksjonen OG(). Her er det to ulike betingelser samtidig: K2 skal være forskjellig fra "S" og den skal samtidig være forskjellig fra "", dvs den skal ikke være tom. Hvis begge disse betingelsen er oppfylt samtidig (OG-funksjonen er SANN), så vises teksten "Feil kode" i cellen, ellers vises ingen ting. Tredje parameter er en tom tegnstreng som gir en blank celle. OG-funksjonen gir verdien SANN bare hvis begge parametrene er sanne, ellers gir den verdien USANN.

La oss gå videre: Det kan jo hende at vi vil ha to ulike satser, ikke et "superkunde"-system. De som handler for 1000kr eller mer skal ha 5% rabatt, men de som handler for 5000kr eller mer skal ha 7,5% rabatt. Dette kan løses slik:

C16: Rabatt:
C17: Å betale:

D16: =HVIS(A1>=1000 ; HVIS(A1>=5000 ; A1*0,075 ; A1*0,05) ; 0)
D17: =A$1-D16

Rabattsatsene kan med fordel stå i egne regnearkceller. Det er jo ikke fordelaktig å skjule tall inne i beregningsuttrykk slik som her.

La oss gjøre endringer som synliggjør tallene for rabattsatser:

A17: Lav rabattsats:
A18: Høy rabattsats:

B17: 0,05
B18: 0,075

C19: Rabatt:
C20: Å betale:

D19: =HVIS(A1>=1000; HVIS(A1>=5000; A1*B18; A1*B17 ) ; 0)
D20: =A$1-D19

Formater gjerne cellene B17 og B18 med prosentformat.

Referansen til cellene med rabattsatser kan forbedres ved å navngi cellene hvor rabattsatsene står, B17 og B18. Da kan vi få f.eks.:

D19: =HVIS(A1>=1000; HVIS(A1>=5000; A1*HSats; A1*LSats ) ; 0)

Rabattgrensene (her 1000kr og 5000kr) bør også stå i egne celler som navngis. Dette overlates til deg som en øving.

Tall som brukes i regneuttrykk bør normalt være synlige i regnearket for lettere å kunne endres senere. I dette tilfellet er det jo tenkelig at rabattgrensene en gang skal endres, f.eks. fra 1000kr til 1200kr. Rabattsatsene skal kanskje også endres osv.

Oppgaver

Lag uttrykkene i kolonne J fra J1 og nedover. Sjekk alltid at uttrykket virkelig gjør det som det skal!

13) Celle A4 skal inneholde enten "K" eller "M". Hvis A4 inneholder "K" skal J1 vise "Kvinne", hvis A4 inneholder "M" skal J1 vise "Mann". Andre verdier enn "K" eller "M" skal gi en blank celle i J1.

14) Hvis A3 er mindre enn 500 skal I2 vise verdien av A3. Hvis verdien er lik eller større enn 500, men mindre enn 1200 skal det fra A3 trekkes 8%. Hvis A3 er større enn eller lik 1200 skal det trekkes fra 15%. Fasit

15) Utvid oppgave 13 ovenfor slik at J1 viser en feilmelding hvis det skrives inn andre verdier enn "K" eller "M" i A4. Hvis A4 er blank ("") skal det ikke vises feilmelding.

16)  OBS! Oppgavene 16 og 17 er vanskelige! A4 kan være en av disse tre: "R", "G" eller "B". Hvis A4 er "R" skal J4 være A5 pluss 250, hvis A4 er "G" skal J4 være A5 pluss 500, hvis A4 er "B" skal J4 være A5 pluss 1000. Hvis A4 er noe annet enn disse tre skal J4 vise feilmeldingen "Gal kode", men ikke hvis A4 er tom (blank). Obs! Pass på at det er like mange høyre- som venstreparenteser! Fasit

17) A4 kan fortsatt være en av disse tre: "R", "G" eller "B". Hvis A4 er "R" skal J5 være A5 multiplisert med det som står i A6, hvis A4 er "G" skal J5 være A5 multiplisert med det som står i A7, hvis A4 er "B" skal J5 være A5 multiplisert med det som står i A8, hvis A4 er noe annet enn disse tre skal J5 vise feilmeldingen "Gal kode", men ikke hvis A4 er blank.

18) I J6 skal det være et uttrykk som tester om det er samme tall eller tekst i A6 og A7. Hvis tekst/tall er likt, skal meldingen "Samme verdi i A6 og A7" komme frem, ellers skal meldingen "A6 og A7 er ulike" vises. Fasit

19) Utvid uttrykket i oppgave 18 slik at det kommer en melding som forteller hvilken av de to cellene som inneholder den høyeste verdien.

20) I J7 skal det være et uttrykk som tester om A6 er mindre enn A1 samtidig som A7 er større enn A1. Hvis så er tilfelle, skal det komme en melding som sier "A1 er mellom A6 og A7". Fasit

21) Endre uttrykket i J7 (fra oppgave 20) slik at uttrykket også viser riktig resultat hvis A6 er større enn A7. (I oppgave 20 var A6 mindre enn A7.) Fasit

22) OBS! Denne oppgaven er svært vanskelig! Lag et uttrykk i J8 som bygger videre på uttrykkene i J6 og J7. Her skal det vises en melding som forteller om A1 er mindre enn den minste av A6 og A7, om den er mellom A6 og A7, eller om den er større enn den største av A6 og A7. Det må selvsagt også komme melding hvis A1 er lik en av A6 eller A7.  Fasit

 

Meny for IKT-fag | Toppen av siden

Copyright © 2002-04 Kristian Evensen -