Se video - Slå den anden, den tredje eller den niende matchende værdi op
Når det kommer til at slå data op i Excel, er der to fantastiske funktioner, som jeg ofte bruger - VLOOKUP og INDEX (mest i forbindelse med MATCH -funktionen).
Disse formler er dog designet til kun at finde den første forekomst af opslagsværdien.
Men hvad nu hvis du vil slå den anden, tredje, fjerde eller Nth-værdi op.
Nå, det kan lade sig gøre med en lille smule ekstra arbejde.
I denne vejledning vil jeg vise dig forskellige måder (med eksempler) om, hvordan du finder den anden eller den niende værdi i Excel.
Slå den anden, tredje eller nte værdi op i Excel
I denne vejledning dækker jeg to måder at slå den anden eller den niende værdi op i Excel:
- Ved hjælp af en hjælperkolonne.
- Brug af matrixformler.
Lad os komme i gang og dykke lige ind.
Brug af hjælperkolonne
Antag, at du er en uddannelseskoordinator i en organisation og har et datasæt som vist nedenfor. Du vil angive al uddannelsen foran en medarbejders navn.
I ovenstående datasæt har medarbejderne fået undervisning i forskellige Microsoft Office -værktøjer (Excel, PowerPoint og Word).
Nu kan du bruge VLOOKUP -funktionen eller INDEX/MATCH -kombinationsboksen til at finde den uddannelse, en medarbejder har gennemført. Det returnerer dog kun den første matchende forekomst.
For eksempel for John har han taget alle de tre uddannelser, men når jeg slår hans navn op med VLOOKUP eller INDEX/MATCH, vil det altid returnere 'Excel', som er den første træning for hans navn på listen .
For at få dette gjort kan vi bruge en hjælperkolonne og oprette unikke opslagsværdier i den.
Her er trinene:
- Indsæt en kolonne før kolonnen, der viser træningen.
- Indtast følgende formel i celle B2:
= A2 & COUNTIF ($ A $ 2: $ A2, A2)
- I celle F2 skal du indtaste følgende formel og kopiere og indsætte for alle de andre celler:
= IFNA (VLOOKUP ($ E2 & COLUMNS ($ F $ 1: F1), $ B $ 2: $ C $ 14,2,0), "")
Ovenstående formel ville returnere uddannelsen for hver medarbejder i den rækkefølge, den vises på listen. Hvis der ikke er angivet en uddannelse for en medarbejder, returnerer den en blank.
Hvordan fungerer denne formel?
COUNTIF -formlen i hjælperkolonnen gør hver medarbejders navn unikt ved at tilføje et nummer til det. For eksempel bliver den første instans af John John1, den anden instans bliver John2 og så videre.
VLOOKUP -formlen bruger nu disse unikke medarbejdernavne til at finde den matchende uddannelse.
Bemærk, at $ E2 & COLUMNS ($ F $ 1: F1) er opslagsværdien i formlen. Dette ville tilføje et nummer til medarbejdernavnet baseret på kolonnetallet. Når denne formel f.eks. Bruges i celle F2, bliver opslagsværdien "John1". I celle G2 bliver det “John2” og så videre.
Brug af Array Formula
Hvis du ikke vil ændre det originale datasæt ved at tilføje hjælperkolonner, kan du også bruge en matrixformel til at slå den anden, tredje eller nte værdi op.
Antag, at du har det samme datasæt som vist nedenfor:
Her er formlen, der returnerer den korrekte opslagsværdi:
= IFERROR (INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ""), COLUMNS ($ E $ 1) : E1))), "")
Kopier denne formel og indsæt den i celle E2.
Bemærk, at dette er en matrixformel, og du skal bruge Ctrl + Shift + Enter (hold Ctrl- og Shift -tasterne nede, og tryk på Enter -tasten), i stedet for at trykke på Enter -tasten.
Klik her for at downloade eksempelfilen.
Hvordan fungerer denne formel?
Lad os bryde denne formel i dele og se, hvordan den fungerer.
$ A $ 2: $ A $ 14 = $ D2
Ovenstående del af formlen sammenligner hver celle i A2: A14 med værdien i D2. I dette datasæt kontrollerer det, om en celle indeholder navnet "John" eller ej.
Det returnerer en matrix af TRUE of FALSE. Hvis cellen har navnet 'John', ville det være sandt, ellers ville det være falsk.
Nedenfor er den matrix, du ville få i dette eksempel:
{SAND; FALSK; FALSK; FALSK; FALSK; FALSK; SAND; FALSK; FALSK; FALSK; SAND
Bemærk, at den har SAND i 1., 7. og 111. position, da det er her, navnet John vises i datasættet.
HVIS ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ””)
Ovenstående IF-formel bruger arrayet SANDT og FALSKT og erstatter SANDT med placeringen af dets forekomst på listen (angivet med RÆ ($ A $ 2: $ A $ 14) -1) og FALSK med "" (mellemrum). Følgende er det resulterende array, du får med denne IF -formel:
{1;””;””;””;””;””;7;””;””;””;11;””;””}
Bemærk end 1, 7 og 11 er placeringen af John's forekomst på listen.
LILLE (HVIS ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ”” ”), COLUMNS ($ E $ 1: E1))
SMALL -funktionen vælger nu det første mindste, næst mindste, tredje mindste tal fra denne matrix. Bemærk, at den bruger funktionen KOLONNER til at generere kolonnetallet. I celle E2 returnerer funktionen COLUMNS 1 og SMALL -funktionen 1. I celle F2 returnerer COLUMNS -funktionen 2, og SMALL -funktionen returnerer 7.
INDEX ($ B $ 2: $ B $ 14, SMALL (IF ($ A $ 2: $ A $ 14 = $ D2, ROW ($ A $ 2: $ A $ 14) -1, ”” ”), COLUMNS ($ E $ 1: E1) ))
INDEX -funktionen returnerer nu værdien fra listen i kolonne B baseret på den position, der returneres af SMALL -funktionen. Derfor returnerer den i celle E2 'Excel', som er det første element i B2: B14. I celle F2 returnerer den PowerPoint, som er det syvende element på listen.
Da der er tilfælde, hvor der kun er en eller to uddannelser for nogle medarbejdere, ville INDEX -funktionen returnere en fejl. IFERROR -funktionen bruges til at returnere en blank i stedet for fejlen.
Bemærk, at jeg i disse eksempler har brugt rækkehenvisninger. I praktiske eksempler er det imidlertid fordelagtigt at konvertere data til en Excel -tabel. Ved at konvertere til en Excel -tabel kan du bruge strukturerede referencer, hvilket gør det lettere at oprette formler. En Excel -tabel kan også automatisk redegøre for alle nye træningsposter, der tilføjes til listen (så du ikke behøver at justere formlerne hver gang).
Hvad gør du, når du skal slå den anden, tredje eller Nth-værdi op? Jeg er sikker på, at der er flere måder at gøre dette på. Hvis du bruger noget lettere end det, der er angivet her, skal du dele med os alle i kommentarfeltet.
Klik her for at downloade eksempelfilen.
