LOPSLAG() mm.

Jeg har i tidligere artikler beskæftige mig med forskellige måder at lave opslag i et regneark på. Fx i artiklerne Opslag til højre og venstre, Mere om opslag, Opslag i intervaller, Opslag via koordinater og Specielt opslag i Matrix foruden i nogle af mine artikler om makroer. Disse artikler handler alle om nogle specielle opslagsvariationer, som man kun har brug for ved specielle lejligheder. Denne artikel, som er skrevet på opfordring, handler om den almindelige brug af den mest almindeligt anvendt opslagsfunktion, nemlig funktionen LOPSLAG() - og om dens pendant VOPSLAG().

LOPSLAG bruges til at slå op i en kolonne og returnere indholdet fra en kolonne til højre for opslagskolonnen. Funktionen har denne syntaks:

LOPSLAG(opslagsværdi;tabelmatirx;kolonneindeks_nr;[lig_med]).

De firkantede parenteser angiver at det sidste argument er valgfrit og det er det da også, men jeg anbefaler at man altid udfylder alligevel for forståelsens skyld. Microsofts forklaring på funktionen lyder: "Søger efter en værdi i første kolonne i en tabel og returner en værdi i den samme række fra en anden kolonne, du har angivet. Tabellen skal som standard sorteres i stigende rækkefølge."   Det lyder nemt for alle der kender funktionen, men har man ikke prøvet den før, er det ikke sikkert, at det er lige så nemt at forstå. Hvad betyder fx "opslagsværdi" og hvad er "kolonneindeks-nr"? Her er det nok nemmest at illustrere det med et eksempel:

I et ark har jeg følgende tabel

Dette er en kundeliste, hvor der løbende tilkommer nye kunder. Det betyder umiddelbart at jeg for hver eneste kunde skal taste både postnummer og by. Det er selvfølgelig til at overleve, hvis der kun er få kunder, men med mange bliver det umiddelbart noget mere besværligt. I en database ville dette slet ikke være "tilladt" da det strider mod god databaseskik. Dette skyldes den såkaldte tilføjelsesanomali, som går ud på, at vi skal taste overflødig information ved oprettelse af nye linjer. Også en problemstilling med såkaldt opdateringsanomali kan optræde. Det kan det også i regnearket. På et tidspunkt skiftede postnummer 2800 navn fra Lyngby til Kongens Lyngby. Det betyder at alle de rækker i regnearket, hvor det står Lyngby skal rettes til Kongens Lyngby. Da cellerne ikke står samlet vil det være en langsom operation at gøre det manuelt. Her kunne en LOPSLAG() have løst begge problemstillinger.

Et andet sted i regnearket (eller i et andet ark i mappen) laver jeg en tabel over samtlige postnumre (eller i hvert fald fald samtlige relevante postnumre - her vist i uddrag).
I eksemplet her står disse data i kolonne J og K i samme ark som mine kundedata, men de kunne altså have stået hvor som helst. Tabellen starter i række 1 og slutter i række 30. Dette område (J1:K30) skal om lidt bruges i min formel som tabelmatrix. Dette udtryk dækker altså over det område i regnearket, som indeholder de data, jeg skal have min formel til at slå op i. Som beskrevet ovenfor slår funktionen op i den første kolonne og returnerer det, som står i samme række men i en anden kolonne. Her er der kun to kolonner at vælge mellem, så returværdien skal altså findes i kolonne 2. Læg mærke til, at det ikke er kolonne "K", men netop "2", som derfor er kolonneindeks_nr. Dette begreb dækker præcis over, hvilket nummer kolonne i området, vores returværdi står i. Det betyder at såfremt jeg beder min LOPSLAG() om at lede efter fx "2800", vil funktionen finde den række dette postnummer står i og så returnere det bynavn, som står i anden kolonne, her "Lyngby".

Hvis funktionen skal kopieres lodret eller vandret, fordi vi har brug for at slå op efter flere postnumre, skal man huske at i så fald må referencen til tabelmatrixen i selve formlen ikke være relativ. Kopierer vi fx J1:K30 en række ned, vil der komme til at stå J2:K31 i cellen nedenunder og derefter J3:K32 og så fremdeles og så passer væres område i formlerne ikke overens med området, vi faktisk har data i. Vi må derfor ændre referencerne til at være absolutte, $J$1:$K$31. Skriv formlen almindeligt, altså

=LOPSLAG(opslagsværdi;J1:K30;2;[lig_med])

Marker så J1:K30 og tryk på F4, så sættes de fire dollartegn. Nu kan formlen kopieres uden at opslagsområdets adresser ændres. NB! Der skal naturligvis ikke stå "opslagsværdi" og "lig_med", men de to argumenter har jeg ikke gennemgået endnu :-).

En anden måde er at navngive sit opslagsområde. Marker hele opslagsområde, altså J1:K30. Klik derefter i ruden til venstre i formellinjen (hvor der står J1 (eller adressen på den første celle i din markering, hvis dit område er et andet sted). Slet J1 og skriv i stedet et sigende navn på dit område, fx "postnumre". Afslut med Enter. Nu kan du bruge dit navn i din formel i stedet for referencerne, og dette ændres ikke ved kopiering.

Nu er dataområdet færdigt, og du er klar til at gå i gang med din opslagsfunktion. Den kan du med fordel bruge der, hvor vi ellers tastede bynavnene i kundelisten. Du sletter altså bynavnene og erstatter dem med en formel.

Når der trykkes Enter vil denne formel se på den værdi, der står i D2 (2800), gå over i opslagsområdet, finde værdien her og returnere den værdi, som står i 2. kolonne, her Lyngby. Formlen kunne også have heddet =LOPSLAG(D2;$J$1:$K$20;2;FALSK) hvis der var brugt absolutte referencer i stedet for et navn.

Du kan nu kopiere formlen nedad hvorefter alle cellerne med byer udfyldes med det bynavn, der hører til det pågældende postnummer. Opslagsværdi er altså den celle, hvor det, der skal ledes efter i opslagsområdets første kolonne står. [lig_med] kan kun have to værdier, SAND eller FALSK. FALSK, som er anvendt i denne formel betyder at der kun skal returneres en værdi, hvis det jeg søger efter findes eksakt i kolonne 1 i tabelmatrixen. Havde jeg fx haft postnummer 4000 stående, ville jeg have fået fejlen #I/T!, der fortæller mig at 4000 ikke findes i min postnummertabel. Hvis jeg i stedet skriver SAND returneres værdien fore den nærmeste opslagsværdi over det sted, hvor det søgte burde stå. Dette lyder måske mærkeligt, men det ville i det konkrete tilfælde betyde at hvis jeg skrev 4000 ville jeg få returneret "Holte" fordi 2840 er den værdi, der står lige over 4000's plads. Dette bruger man til opslag i intervaller, som jeg skal vise nedenfor.

Når jeg nu opretter en ny kunde, skal jeg kun taste navn, adresse og postnummer, så klarer en kopiering af min formel resten. Og når "Lyngby" skifter navn til "Kongens Lyngby" skal jeg ikke rette det for hver kunde, men kan nøjes med at rette det i min tabelmatrix. Én rettelse af Lyngby til Kongens Lyngby i matrixedn og så ser mit regneark ud som vist til venstre.

Nu er det ikke altid at der kun er to kolonner i tabelmatrixen, men det gør ikke noget. Den kan sagtens være større, og det man skal returnere behøver ikke at stå i kolonne 2, bare opslagsværdien findes i kolonne 1.

Har jeg fx denne tabelmatrix kan jeg på grundlag af cpr-nummeret hente Adresse ud ved at henvise til 3. kolonne og Stilling ved at henvise til 4. kolonne. Med fx 101010-1010 i E1 vil =LOPSLAG(E1;A1:D3;3;FALSK) hente "Ågade" og 111111-1111 vil med =LOPSLAG(E1;A1:D3;FALSK) vil hente "Underløber".

Opslag i Interval

Ovenstående henter altid eksakte værdier (afsluttet med FALSK) og findes værdierne ikke, returneres #I/T! (der står for IKKE TILGÆNGELIG). Men af og til har man brug for at få returneret en værdi, selv om der ikke er et eksakt match i første kolonne. Det gælder hvis samme værdi skal returneres for et helt interval. Lad os antage, at vi har nogle sælgere, der skal have bonus alt efter hvor meget de sælger for på en måned. Sælger de under 10 skal de ikke have bonus. Sælger de mellem 10 og 19 skal de have 10.000. Sælge de mellem 20 og 29 skal de have 20.000 , sælger de mellem 30 og 49 skal de have 50.000 og sælger de 50 eller derover skal de have 100.000.

Det kunne man selvfølgelig godt "pinde ud" ved at skrive fx 1, 2, 3 osv. i hver sin celle og så ud for hver af disse skrive 0, og så fremdeles, men man kan også bruge en anden metode.

Opret en tabelmatrix som den til venstre. I kolonne 1 skrives de underste grænser for vore intervaller i stigende orden med den laveste grænse øverst og den højeste nederst. I kolonnen til højre skrives den værdi, som opnås ved netop det pågældende salg. Da 0 styk udløser 0 kr. skal der stå 0 ud for 0. 10 styk er det mindste, der udløser 10.000 kr., 20 styk er det mindste der udløser 20.000 kr. og så videre.  Hvis vi så har sælgerne et sted i arket med deres solgte styk kan vi bruge LOPSLAG() til at beregne deres bonus. Hvis vi antager at vi har 6 sælgere stående i kolonne E og deres solgte antal i kolonne F, kan vi i F-kolonnen indsætte denne formel (ovenstående område har jeg navngivet Bonus): =LOPSLAG(F2;bonus;2;SAND). Når formlen kopieres nedad opnår jeg dette resultat:

Ud fra det konkrete salg slås op i tabelmatrixen og da fx 22 ikke findes, tager den den opslagsværdi, der står lige over, altså 20 og returnerer det tal der står ud for dette, altså 20,.000, hvilket netop var den bonus, sælgerne skulle have når de solgte mellem 20 og 29 styk. Hannelore Mikkelsens salg, 75, ligger i det "øverste område", der hvor der udbetales samme bonus, så funktionen ser på det ovenstående tal, 50, og giver den bonus, der står ud for dette, hvilket passer med vores regel.

Også denne type funktion kan arbejde med flere kolonner.  Hvis jeg vil knytte en kommentar til de forskellige salgsmængder, kan jeg i c-kolonnen skrive fx "Håbløst salg" ud for 0, "Dårligt salg" ud for 10 og så fremdeles, I så fald kunne =LOPSLAG(F2;bonus;3;SAND) returnere "Godt salg" ud for Rita Grøn og "Fantastisk salg" ud for Hannelore. NB! Husk at formlen skal rettes så c-kolonnen er med i tabelmatrixen.

VOPSLAG()

Funktionen VOPSLAG virker på nøjagtigt samme måde som LOPSLAG() men forventer at tabelmatrixen står i rækker i stedet for kolonner, fx

og vi så indtaster patientens vægt (fx 46) i A4, så vil =VOPSLAG(A4;A1:F2;2;SAND) returnere 1,3 hvilket netop den dosering man skal have, hvis man vejer mellem 40 og 59 kg.

- Tilbage til Excel -