En “må ha” funksjon i Excel er finn.rad. Til å begynne med kan finn.rad-funksjonen virke litt merkelig og nesten litt ulogisk. Men som med alle ting, er det kun snakk om trening og forståelse.
I hovedsak brukes finn.rad til å finne en gitt verdi i en liste. Jo større en liste er, jo mer arbeid blir det å prøve finne verdiene en er på jakt etter, dersom en skal lete på den manuelle måten. Med finn.rad kan bestemte oppslag (som ofte blir brukt som beskrivelse) gjøres raskt og presist for mange rader.
Om du synes artikkelen er litt lang, kan du trøste deg med at det er korte videoer til hvert eksempel!
Du kan også laste ned Excel-filen eksemplene og videoene er basert.
Beskrivelse av funksjonen
Fra hjelpeteksten i Excel:
Søker etter en verdi i kolonnen lengst til venstre i en tabell, og returnerer en verdi i samme rad fra en kolonne du angir. Standardinnstillingen er at tabellen må være sortert i stigende rekkefølge.
Finn.rad har 4 argumenter. Det siste argumentet er strengt tatt valgfritt, men ikke i praksis. I mindre og enkle eksempler, kan det virke som det siste argumentet ikke betyr noe, men det er bare tilsynelatende. Tenk alltid at finn.rad har 4 argumenter.
Søkeverdi
Søkeverdi er som regel en bestemt celle på samme rad som beregningen ligger. Dette er verdien som finn.rad skal prøve å finne tilsvarende i et bestemt område (gitt i matrise argumentet). I bildet over blir dette cellen som inneholder Lønnstrinnet for en gitt ansatt (B4).Matrise er området der en skal finne Søkeverdien. Det viktige her er at det er den første kolonnen i dette området som finn.rad vil lete. Derfor kan ikke lønnen stå først og lønnstrinnet etterpå, som viste i bildet. I tillegg må området også inneholde alle interessante verdier i de neste kolonnene. Her er det viktig å huske på bruken av dollartegn, fordi matrisen er det samme for alle beregninger.
Kolonneindeks
Kolonneindeks er hvilket kolonnenummer i den oppgitte matrisen som resultatet skal hentes fra. I bildet over var det to kolonner i matrise området, og bruttolønnen er i den andre kolonne, derfor blir dette argumentet bare tallet 2. Er det flere kolonner i matrisen må en i første omgang bare telle antallet kolonner frem til ønsket kolonne.
Søkeområdet
Søkeområdet skal enten være SANN eller USANN. Ved å bruke SANN er det et søk på tilnærmet lik verdi mellom Søkeverdi og den første kolonnen i matrise. Dette forutsetter også at den matrisen er sortert stigende. Denne metoden er brukes oppslag mot intervaller. I denne sammenhengen betyr tilnærmet i praksis større eller lik med.
Ved å bruke USANN er det søk på 100% nøyaktig likhet verdi mellom Søkeverdi og den første kolonnen i matrise. Da trenger ikke matrise være sortert.
Se video av eksempelet
Praktisk eksempel
Eksemplet med Lønnstrinn er en grei måte å få frem flere momenter med finn.rad.
Et enkelt utgangspunkt:
Ansatte:
Basert på lønnstrinnet er skal det hentes tilhørende verdier fra selve lønnstrinntabellen. Listen i bildet under kan lastes ned som et Excel arbeidsbok, fra de offisielle nettsidene.
Lønnstrinn:
Hva som ønskes er egentlig uvesentlig, så lenge en teller riktig kolonne i matrisen. Som eksempel kan det være bruttolønn per måned i D-kolonnen, etterpå pensjonistinnskudd fra G-kolonnen.
Stå i ønsket celle og skriv =finn og listen nedenfor skal vises, og velg finn.rad.
Argumentet søkeverdi er cellen ved siden av, der lønnstrinnet står.
For neste argument, er det nødvendig å klikke over i Lønnstrinn arket og merke hele det aktuelle området. Og huske bruken av dollartegn i dette tilfellet.
Kolonneindeks ble i dette tilfellet 4 (skulle hente fra D-kolonne og matrisen startet i A-kolonnen).
For søkeområdet skal det være USANN, fordi det skal hentes et bestemt lønnstrinn, nøyaktig det som er skrevet for denne personen
Resultatet etter kopiering blir:
For neste beregning som skulle hente Pensjonsinnskudd som ligger i kolonne 7 (G-kolonnen), blir oppsettet i praksis nesten helt likt.
Kun argumentet for kolonneindeks er forskjellig.
Se video av eksempelet
Enkelt oppslag og feilhåndtering
Fra ansattlisten i eksempelet over, kan det tenkes et behov for å skrive inn en gitt verdi, f. eks. Ansattnr og vise tilhørende informasjon.
I Ansatt listen er Etternavn den tredje kolonnen.
Og ettersom det er et unikt Ansattnr og målet er å finne nøyaktig det Ansattnr, må det være USANN i siste argument.
For å hente brutto blir det nesten samme funksjon. Kun kolonneindeks som blir forskjellig.
Forskjell på SANN og USANN
I eksemplet over kan det raskt vises hva som vil være konsekvensen av å ikke velge USANN i det siste arugmentet.
Med USANN:
Med SANN:
Det kommer et resultat, ettersom Ansattnr er formelt sett et tall. Med SANN i siste argument, benytter finn.rad en teknikk som kalles for Binærsøk, som kan gjøre at resultatet kan virke veldig vilkårlig.
Håndtere enkel feil
Når det siste argumentet er USANN, altså at det søkes etter absolutt likhet, er det 2 varianter som er verdt et lite tillegg. Hva som skjer dersom cellen er tom og hva som skjer dersom det skrives inn feil Ansattnr.
Begge vil varianter vil gi #I/T feilen, som står for ikke tilgjengelig. Finn.rad prøver å finne full likhet, men det som står i Søkeverdi har ikke et motstykke.
Den enkleste varianten er bruke funksjonen HVISFEIL.
Ved feil Ansattnr blir resultatet:
Som alternativ:
Der det første argumentet er finn.rad funksjonen i seg selv. Det andre argumentet hva som skal vises i stedet for feilmeldingen. Den teksten trenger bare og vises en gang i den første celle og ikke nødvendigvis ikke det samme feilmeldingen i alle cellene bortover.
For brutto kan det derfor benyttes:
Her vises ingen ting i stedet for feilmeldingen:
Dersom cellen som skal ha Ansattnr er tomt, gir det også en feilmelding, som igjen viser det som blir bestemt av HVISFEIL. For å unngå dette er en vanlig HVIS funksjon praktisk (se forrige tips).
Hvis cellen er tom, ikke vis noe, ellers gjør hele beregningen.
Se video av eksempelet
Søke i et intervall
Selv om det å søke på en absolutt likhet er muligens den vanligste måten å bruke finn.rad, er muligheten til å søke i ett intervall svært nyttig. Dette gjøres ved å bruke SANN (tilnærmet) i det siste argumentet.
Et viktig moment er at finn.rad brukes i stedet for mange nøstede hvis funksjoner. I stedet for mange funksjoner, som kan gjøre endringer og vedlikehold veldig tungvidt, holder det ofte med én finn.rad funksjon.
Det neste momentet er nødvendigheten av at område for matrise argumentet og at dette området er sortert stigende etter første kolonne.
Som en enkel måte å lage en gruppering etter lønnstrinn, kan være et eksempel om vist over. Trinn er sortert stigende, med en ønsket tekst ved siden av. Ved å benytte SANN i siste argument, søket i praksis være, større eller lik.
Basert på et gitt Lønnstrinn går finn.rad igjennom området og sjekker om Lønnstrinnet er større eller lik verdien i Trinn rad for rad, inntil Lønnstrinnet ikke lenger er større eller lik Trinn.
En annen måte å si dette på er:
• Fra og med 0 til og med 39 -> Gruppe A
• Fra og med 40 til og med 69 -> Gruppe B
• Fra og med 70 til og med 89 -> Gruppe C
• Osv.
Ikke helt presist med tanke på desimaler, men hovedpoenget bør bære være greit.
Selv om det er litt andre forutsetninger, er tankesettet mer eller mindre det samme som tidligere.
• Søkeverdien er Lønnstrinn på det samme raden.
• Matrise er området med Grupperingen.
• Kolonneindeks blir kolonne 2 (etter matrisen)
• Søkeområde er SANN fordi det skal være tilnærmet (større eller lik).
Se video av eksempelet
To kjente problemstillinger
Tekst til tall og tall til tekst
Det er nok tilfeller hvor finn.rad ikke oppfører seg som ønsket. Et eksempel tidligere var det at verdien ikke fantes og det er strengt tatt ikke feil som så dann, men en informasjon.
Men finn.rad er også sensitiv når det gjelder tekst og tall. Dvs. at verdien er fra vårt ståsted lik, men for Excel er det stor forskjell på et tall, og en tekst som ser ut som et tall.
Tallet 19 er ikke det samme som teksten 19.
I det første området er det Lønnstrinn tall, mens det er tekst i det andre. En ‘vanlig’ finn.rad gir en feilmelding fordi Excel ikke finner en likhet (rad 5).
Dersom et tall er definert som tekst, er et triks å gange (multiplisere) med 1 for å gjøre teksten til tall (kun mulig når teksten kan faktisk bli et tall som i eksemplet over). Fordelen med denne varianten er at funksjonen i seg selv håndterer situasjonen, uten at en trenger å gjøre til selve grunnlaget.
Den motsatte veien blir at søkeverdien er et tall, mens den skal slå opp mot en tekst. Det simple trikset for å gjøre et tall om til en tekst, er ‘å kjede på en tom streng’. For å kjede på en tom streng kan det benyttes &”” (to anførselstegn).
Ekstra mellomrom
En “dødare” av mange ellers gode regneark kan være mellomrom bak en verdi i en celle. Det finnes teknikker for å finne ut om det er mellomrom bak, samt teknikker for å gjøre noe med det, men det er ikke tema akkurat nå.
I dette eksemplet er det søkeverdien som har ekstra mellomrom, mens matrise har gode verdier. For å fjerne ekstra mellom fra en verdi finnes trimme funksjonen.
I mange tilfeller vil kan “fiksen” være relativt enkel.
MEN…
Som alltid i Excel er kvaliteten på dataene, både med tanke på datatypen i den enkelte cellen (tall/tekst) og organiseringen av dataene på et overordnet plan (den gode listen).
Dess større avvik og variasjoner gjør alt arbeid med dataene vanskeligere og mer komplisert.
Se video av eksempelet
Kommentar
Finn.rad er en “må ha” funksjon. Den er fleksible og kan tilpasses for forskjellige måter, og som med så mange andre metoder i Excel, er det snakk om mengdetrening og litt kreativitet.
Å lære seg denne funksjoner er for de aller fleste, vel verdt tiden.
Hev din Excel-kompetanse
Delta på et av våre Excel kurs og lær hvordan du jobber smart og effektivt med Excel!
Sjekk også vår YouTube-kanal for korte videoer om Excel.