Når en værdi skal placeres i et trin, fx et ordrebeløb i et rabattrin, skal opslaget ikke finde et præcist match, men det nærmeste trin. Skriv trinenes nedre grænser i en tabel, og brug:
Med LOPSLAG er det tilsvarende =LOPSLAG(D2;$A$2:$B$5;2;SAND). Den kræver, at grænserne står sorteret stigende. Det gør XOPSLAG ikke, som vi viser længere nede.
Rabattrin: grænserne er “fra og med”
| Række | A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|---|
| 1 | Ordrebeløb fra | Rabat | Ordrebeløb | LOPSLAG SAND | XOPSLAG -1 | Pris efter rabat | |
| 2 | 0 | 0% | 0,00 | 0% | 0% | 0,00 | |
| 3 | 1.000 | 5% | 999,99 | 0% | 0% | 999,99 | |
| 4 | 5.000 | 10% | 1.000,00 | 5% | 5% | 950,00 | |
| 5 | 10.000 | 15% | 4.999,50 | 5% | 5% | 4.749,53 | |
| 6 | 5.000,00 | 10% | 10% | 4.500,00 | |||
| 7 | 12.500,00 | 15% | 15% | 10.625,00 |
Præcis på grænsen giver begge funktioner det nye trin: 1.000,00 giver 5 %, og 5.000,00 giver 10 %. Et beløb lige under grænsen bliver i trinnet nedenunder. Beløb over det sidste trin får det sidste trins rabat. Prisen efter rabat er =D2*(1-F2).
Husk rækken med 0
Den nederste grænse skal være den mindste værdi, du kan få. Vi lavede den samme tabel uden rækken med 0 og slog 500 op:
| Formel | 500,00 | 1.000,00 |
|---|---|---|
=LOPSLAG(L2;$I$2:$J$4;2;SAND) |
#I/T |
5% |
=XOPSLAG(L2;$I$2:$I$4;$J$2:$J$4;0;-1) |
0% | 5% |
Et beløb under første trin har ikke noget “nærmeste mindre” trin og giver #I/T. Enten tilføjer du rækken med 0, eller du bruger XOPSLAG’s fjerde argument til at angive, hvad der skal ske. Her giver 0 ingen rabat.
Fragtpriser: grænserne er “til og med”
En prisliste kan også være skrevet omvendt: op til og med 1 kg koster 49 kr., op til og med 5 kg koster 79 kr. osv. Her er grænsen den øvre grænse, og du skal bruge matchtilstand 1, der tager den nærmeste større værdi:
LOPSLAG har ikke en tilsvarende mulighed. Et nærliggende forsøg er at skrive tabellen om til “over”-grænser (over 0 kg, over 1 kg, over 5 kg …) og bruge SAND. Det går galt præcis på grænserne:
| Række | D | E | F |
|---|---|---|---|
| 1 | Vægt (kg) | XOPSLAG 1 | LOPSLAG SAND |
| 2 | 0,5 | 49 | 49 |
| 3 | 1 | 49 | 79 |
| 4 | 1,01 | 79 | 79 |
| 5 | 5 | 79 | 99 |
| 6 | 5,2 | 99 | 99 |
| 7 | 20 | 149 | 149 |
| 8 | 20,5 | Over 20 kg | 149 |
En pakke på præcis 1 kg fik 79 kr. i stedet for 49 kr., fordi LOPSLAG tager grænsen 1 som “fra og med”. Og en pakke på 20,5 kg fik den højeste pris i stedet for en besked om, at den er for tung. Afprøv altid dine formler med værdier præcis på grænserne, før du stoler på dem.
Karakterer på 7-trinsskalaen
Karakterskalaen består af de syv karakterer -3, 00, 02, 4, 7, 10 og 12 ifølge Undervisningsministeriet. Skalaen beskriver karaktererne med ord, ikke med point, så om og hvordan point omregnes til karakterer, afhænger af den enkelte prøve.
| Række | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Point fra | Karakter | Point | XOPSLAG -1 | LOPSLAG SAND | |
| 2 | 0 | -3 | 0 | -3 | -3 | |
| 3 | 20 | 00 | 19,5 | -3 | -3 | |
| 4 | 40 | 02 | 20 | 00 | 00 | |
| 5 | 50 | 4 | 39 | 00 | 00 | |
| 6 | 65 | 7 | 40 | 02 | 02 | |
| 7 | 80 | 10 | 50 | 4 | 4 | |
| 8 | 92 | 12 | 64,9 | 4 | 4 | |
| 9 | 65 | 7 | 7 | |||
| 10 | 91,99 | 10 | 10 | |||
| 11 | 92 | 12 | 12 | |||
| 12 | 100 | 12 | 12 |
Pas også på med point, der vises afrundet. En celle med 64,9 point og talformatet 0 viste 65, men opslaget gav 4, fordi Excel bruger den rigtige værdi og ikke den viste. Skal point afrundes efter en bestemt regel, før de omregnes, så gør det med en funktion i formlen. Se afrunding i Excel.
Når grænserne ikke er sorteret
Vi byttede om på to rækker i rabattabellen, så grænserne stod i rækkefølgen 0, 5.000, 1.000, 10.000:
| Ordrebeløb | LOPSLAG SAND | XOPSLAG -1 | XOPSLAG -1 med søgetilstand 2 |
|---|---|---|---|
| 999,99 | 0% | 0% | 0% |
| 1.000,00 | 0% | 5% | 0% |
| 4.999,50 | 0% | 5% | 0% |
| 5.000,00 | 10% | 10% | 10% |
| 12.500,00 | 15% | 15% | 15% |
LOPSLAG gav 0 % for 1.000 og 4.999,50 uden nogen fejlmeddelelse. XOPSLAG med matchtilstand -1 gav de rigtige rabatter, selvom tabellen ikke var sorteret. Det holder dog kun med standardsøgningen. Beder du om binær søgning med søgetilstand 2 (=XOPSLAG(D2;$A$2:$A$5;$B$2:$B$5;;-1;2)), forudsætter Excel ifølge Microsoft sorterede data, og resultaterne blev lige så forkerte som med LOPSLAG.
Bruger du LOPSLAG, så sortér grænserne stigende, og lad være med at udelade SAND/FALSK ved en fejl. Hvad der sker, når et almindeligt opslag ved en fejl bruger omtrentligt match, viser vi i sammenligningen af opslagsfunktionerne og i fejlfindingen af #I/T.