1. Formlen “hvis” samt “hvis-i-hvis”
Excel har 1.000-vis af foruddefinerede formler. En af de mest brugte er “Hvis” eller “If” på engelsk.
- Jeg har indtastet en lille tabel. I kolonne C vil jeg lave en vurdering af sælgernes indsats. Hvis de har solgt for 100 eller over, så skal der I cellen stå “Supersælger”. Ellers skal der stå “Dovendyr”
- Stå I cellen (1)
- Gå op I formellinen oven over, og klik på knappen fx (2)
- Find formlen “Hvis” (“If” på engelsk) (3)
- Klik til sidst på OK (4)
- Du skal ikke selv taste formlen, du kan udfylde det nye pop-op vindue. Gør det således:
- Logisk test B2 >= 100. Dvs. Celle B2 skal være større end eller lig med 100 (1)
- Hvis det er sandt, så skriv værdien “Supersælger” (2)
- Hvis det er falsk, så skriv værdien “Dovendyr” (3)
- Klik til sidst på knappen OK (4)
- Resultatet ser således ud. Der står det rigtige I cellen (1)
- Bemærk at du I formellinjen kan se formlen (2). Så du kan altså også taste formlen ind. I starten vil jeg dog anbefale, at du bruger formelvinduerne
- Resultatet bliver
Link til toppen af siden.
Bonusberegning med Hvis-formlen
- Vi er ikke helt færdige. Hvis de sælger for 100 eller mere end det, så skal de have en bonus. Dvs. De får 5 procent. Det kan vi også bruge formlen Hvis til.
- Start helt lige som før:
- Stå I cellen (1)
- Gå op I formellinen oven over, og klik på knappen fx (2)
- Find formlen “Hvis” (“If” på engelsk) (3)
- Klik til sidst på OK
- Udfyld formelvinduet således:
- Den logiske test har jeg leget lidt med, I stedet for B2 >= 100 kan man selvfølgelig også skrive B2 < 100. Så det gør jeg
- Hvis udsagnet er sandt, så er der ingen bonus. Derfor værdien 0
- Hvis udsagnet er falsk, så skal sælgeren have 5% bonus. Det udregnes som B2 * 5%
- Resultatet bliver
Link til toppen af siden.
Hvis-i-hvis
- Indtil nu har vi kun haft 2 muligheder. Hvad nu hvis der er flere end 2 muligheder?
- Vi siger nu, at hvis salget er på eller over 110, så skal sælgeren have 8% I bonus
- Ovenfor så formlen således ud:
=IF(B2 < 100;0;B2 * 5%)
- IF er selvfølgelig formlen
- B2 < 100 er spørgsmålet, om værdien I celle B2 er mindre end 100
- 0 er svaret, hvis udsagnet er sandt
- B2 * 5% er det, den skal gøre, hvis udsagnet er falsk
- Det, vi skal gøre, er at sætte en ny Hvis-formel ind I stedet for B2 * 5%
- Formlen bliver
IF (B2 < 110; B2 * 5%; B2*10%)
- Hvis udsagnet er sandt, så udbetales 5%. Hvis falsk, så 10%
- Så kan du måske spørge: “Jamen, hvad så med dem, der slet ikke skal have bonus?”. Svaret er, at de er allerede sorteret fra.
- På denne måde kan vi blive ved med at lave hvis-i-hvis sætninger
- Formlen kommer til at se sådan ud:
- De 2 første linjer ser ud som før (1)
- Værdien hvis falsk bliver nu til en hvis sætning: IF (B2 < 110; B2 * 5%; B2*10%)
- Du kan se hele formlen i formel-vinduet øverst (3)
- Det ses, at Rikke Rabats bonus nu er dobbelt så stor som før.
Link til toppen af siden.
Opgaver
1.1 Hvis-sætning
- Indtast nedenstående
- Følg vejledningen fra det første afsnit:
- Beregn den første søjle, hvor du skal afgøre, om sælgeren er en supersælger eller et dovendyr (vist med orange)
- Beregn den anden række med 5% bonus (vist med grøn)
- Ekstra: Det er blevet jul. Så selvom nogle af sælgerne er dovne, så får de 2% I bonus alligevel. Ellers skal de have 8%. Lav den nye beregning
Ekstra: Hvis-I-hvis
- Gennemfør beregningen, hvor sælgerne med på eller over 110 I omsætning får ikke 5% men 10%.
- Lav en ny række med Freja Fantastisk. Hun har 154 I omsætning. Lav en formel I en ny søjle, der kan give sælgere med over 150 hele 15% I omsætning
Ekstra: Postnumre
- Du husker sikkert kundelisten fra før. Den er vedhæftet herunder.
- Du skal lave en formel, som kan udføre følgende.
- Hvis postnummeret er under 5000 så skriv: “Ligger øst for Storebælt”
- Hvis postnummeret er på eller over 5000 men under 6000 skriv “Ligger på Fyn”
- Hvis postnumemret er på eller over 6000 skriv “Ligger I Jylland”
- Og så den svære: Hvis postnummeret er på eller over 3700 men mindre end 3800 skriv “Ligger på Bornholm”
- Forklar, hvordan du vil få det til at fungere
- Prøv det i praksis
Link til toppen af siden.
2. Opslagsformler (vopslag, lopslag mv.)
- Du kan bruge Excel til at lave opslag I tabeller med.
- Det lyder måske ikke så nyttigt, men forestil dig, at du har et excel-ark med måse 3.000 kunder eller 5.000 varer. Så sparer du pludselig meget tid, hvis du kan indtaste f.eks. et varenummer og se oplysningerne om varen
- Vigtigt: Den værdi, du slår op på, skal være unik. F.eks. kundenummer, varenummer, postnummer, cpr-nummer osv. Ellers virker det ikke.
————-
- Der er flere opslagsformler, her vil jeg bruge formlen til et lodret opslag. “lopslag” på dansk. “vlookup” på engelsk
- Det fungere således:
- Til venstre har du tabellen (1)
- Jeg har et felt, hvor jeg indtaster kundenummeret I (2)
- Til højre slår min formel så op tabellen og finder de oplysninger, jeg ønsker (3)
- Lad os kikke ind I formlen: Formlen I celle J3, der finder “Benny”, ser sådan ud:
- Lookup_value = I4. Dvs den celle, hvor jeg indtaster den værdi, jeg vil slå op med. Her kundenummeret (1)
- Table_array = A3:G15. Det er det område, som tabellen befinder sig I (2). Vigtigt: Opslagsværdien skal være den første kolonne I tabellen
- Col_index_num = Jeg vil slå fornavnet op, og det er kolonne 2 fra venstre. Derfor tallet “2” (3)
- Til sidst står der en værdi “False” (4). Det betyder, at jeg kun accepterer nøjagtige macth på det, jeg slår op med. Hvis jeg satte den til true, ville den måske godt acceptere “22” eller “12”. Men jeg vil have et præcist match
Link til toppen af siden.
Opgaver
2.1 Prøv lopslaget af
- Du er nu en erfaren Excel bruger. Så jeg vil overlade forklaringen til nedenstående video
Link til toppen af siden.
3. Betinget formatering med farver ud fra formel
- Marker det område, du ønsker at arbejde med. F.eks. en række eller hele tabellen (1)
- I fanen Hjem vælg Betinget formatering (Conditional Formatting) (2) > Ny regel (New Rule) (3)
- I pop-op vinduet vælg, at du selv vil skrive formlen: Brug en formel til at afgøre, hvilke celler der skal formateres (1)
- Klik på OK for at lukke vinduet
- I det nye pop-op vindue skriver du en formel, f.eks.
- =$G4 > 200
- Det vil sige, at alle celler i tilhørende række til G4 i det tidligere markerede område (= hele tabellen undtagen rækken med overskrifter) , svarende til cellerne A4, B4, C4 osv til og med G4 får den ønskede formatering, hvis deres værdi er over 200
- Klik på knappen Format (2) og vælg den ønskede formatering. Jeg har valgt farven grøn (3)
- Klik til sidst på knappen OK (4)
- =$G4 > 200
- Vi er nu i et nyt vindue med en oversigt over reglerne
- Den regel, der lige er oprettet, er selvfølgelig den eneste regel (1)
- Forrest formlen
- Næste trin viser resultatet, hvis formlen er sand
- Og så vises det område, som den gælder i (= hele tabellen undtagen overskrifterne)
- For at se resultatet, klik på knappen Udfør (Apply) (2). Så ser du, hvad der sker (3), men du lukker ikke vinduet
- Vi mangler stadigvæk et par regler til at farve celler mellem 100 og 200 gule, og dem på og under 100 røde
- Klik på knappen Ny regel (New rule) (1)
- En-efter-en indtast reglerne, så vi ender med at have regler for alle 3 farver
- Vi har i forvejen = $G4 > 200, gul farve
- = $G4 > 100, gul farve
- = $G4 <= 100, rød farve
- Der er knapper til at redigere regler, slette regler samt flytte reglerne op og ned i rækkefølgen.
BEMÆRK: Den læser nedefra. Hvis 2 regler dækker det samme område, så bestemmer den øverste over den nederste. Den grønne regel som dækker > 200 vil bestemme over den gule, der dækker > 100. Heldigvis, for det er det lige det, jeg ønsker - Resultatet ser du tv. (4)
- Undervejs klik på Apply (5) for at se resultatet uden at lukke vinduet samt OK (6) for til sidst at lukke vinduet, når du er tilfreds med resultatet.
Link til toppen af siden.
Opgaver
3.1 Betinget formatering
- Prøv vejledningen af
- Ekstra: Leg med mulighederne uden formler under Betinget formatering
- Ekstra: Prøv i sorteringen at sortere tabellen efter farve, det kan den godt
Link til toppen af siden.