Excel -funktioner kan være ekstremt kraftfulde, hvis du får styr på at kombinere forskellige formler. Ting, der kunne have virket umulige, ville pludselig begynde at ligne et barns leg.
Et sådant eksempel er at finde det nærmeste match til en opslagsværdi i et datasæt i Excel.
Der er et par nyttige opslagsfunktioner i Excel (f.eks. VLOOKUP & INDEX MATCH), som kan finde det nærmeste match i et par enkle tilfælde (som jeg vil vise med eksempler herunder).
Men det bedste er, at du kan kombinere disse opslagsfunktioner med andre Excel -funktioner for at gøre meget mere (herunder at finde det nærmeste match for en opslagsværdi i en usorteret liste).
I denne vejledning viser jeg dig, hvordan du finder det nærmeste match til en opslagsværdi i Excel med opslagsformler.
Find det tætteste match i Excel
Der kan være mange forskellige scenarier, hvor du skal lede efter det nærmeste match (eller den nærmeste matchende værdi).
Nedenfor er de eksempler, jeg vil dække i denne artikel:
- Find provisionen baseret på salg
- Find den bedste kandidat (baseret på den nærmeste erfaring)
- Find den næste begivenhedsdato
Lad os komme igang!
Klik her for at downloade eksempelfilen
Find provision (søger den nærmeste salgsværdi)
Antag, at du har et datasæt som vist herunder, hvor du vil finde provisionerne for alt salgspersonale.
Provisionen tildeles baseret på salgsværdien. Og dette beregnes ved hjælp af tabellen til højre.
For eksempel, hvis en sælger foretager det samlede salg på 5000, er provisionen 0%, og hvis han/hun foretager det samlede salg på 15000, er provisionen 5%.
For at få provisionen skal du finde det nærmeste salgsinterval, der er lige lavere end salgsværdien. For eksempel, for 15000 salgsværdi, ville provisionen være på 10.000 (hvilket er 5%) og for salgsværdi på 25000 ville provisionen være på 20.000 (hvilket er 7%).
For at finde den nærmeste salgsværdi og få provision, kan du bruge den omtrentlige match i VLOOKUP.
Nedenstående formel ville gøre dette:
= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)
Bemærk, at i denne formel er det sidste argument 1, som fortæller formlen at bruge et omtrentligt opslag. Dette betyder, at formlen ville gå igennem salgsværdierne i kolonne E og finde den værdi, der bare er lavere end opslagsværdien.
Derefter giver VLOOKUP -formlen provisionen for denne værdi.
Bemærk: For at dette skal fungere, skal du have dataene sorteret i stigende rækkefølge.Klik her for at downloade eksempelfilen
Find den bedste kandidat (baseret på den nærmeste erfaring)
I eksemplet ovenfor skulle dataene sorteres i stigende rækkefølge. Men der kan være tilfælde, hvor dataene ikke er sorteret.
Så lad os dække et eksempel og se, hvordan vi kan finde det nærmeste match i Excel ved hjælp af en kombination af formler.
Nedenfor er et eksempel på datasæt, hvor jeg skal finde det medarbejdernavn, der har erhvervserfaringen tættest på den ønskede værdi. Den ønskede værdi i dette tilfælde om 2,5 år.
Bemærk, at dataene ikke er sorteret. Den nærmeste oplevelse kan også være enten mindre eller mere end give -oplevelsen. For eksempel er 2 år og 3 år begge lige tæt (forskel på 0,5 år).
Nedenfor er formlen, der vil give os resultatet:
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Tricket i denne formel er at ændre opslagsmatrixen og opslagsværdien for at finde den mindste oplevelsesforskel i krævede og faktiske værdier.
Lad os først forstå, hvordan du ville gøre det manuelt (og derefter vil jeg forklare, hvordan denne formel fungerer).
Når du gør dette manuelt, vil du gå gennem hver celle i kolonne B og finde forskellen i oplevelsen mellem, hvad der kræves, og det, som en person har. Når du har alle forskellene, finder du den, der er mindst, og henter navnet på den person.
Det er præcis, hvad vi gør med denne formel.
Lad mig forklare.
Opslagningsværdien i MATCH-formlen er MIN (ABS (D2-B2: B15)).
Denne del giver dig den mindste forskel mellem den givne erfaring (som er 2,5 år) og alle de andre oplevelser. I dette eksempel returnerer det 0,3
Bemærk, at jeg har brugt ABS til at sikre, at jeg leder efter det nærmeste (hvilket kan være mere eller mindre end den givne oplevelse).
Nu bliver denne mindste værdi vores opslagsværdi.
Lookup-arrayet i MATCH-funktionen er ABS (D2- $ B $ 2: $ B $ 15).
Dette giver os en række tal, hvorfra 2,5 (den krævede erfaring) er trukket fra.
Så nu har vi en opslagsværdi (0,3) og et opslagsarray ({6.8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})
MATCH -funktion finder positionen 0,3 i dette array, som også er positionen for den persons navn, der har den nærmeste oplevelse.
Dette positionsnummer bruges derefter af INDEX -funktionen til at returnere personens navn.
Bemærk: Hvis der er flere kandidater, der har samme minimumserfaring, vil ovenstående formel give navnet på den første matchende medarbejder.
Find den næste begivenhedsdato
Dette er et andet eksempel, hvor du kan bruge opslagsformler til at finde den næste dato for en begivenhed baseret på den aktuelle dato.
Nedenfor er datasættet, hvor jeg har hændelsesnavne og hændelsesdatoer.
Det, jeg ønsker, er navnet på den næste begivenhed og datoen for begivenheden for denne kommende begivenhed.
Nedenfor er formlen, der vil give navnet på den kommende begivenhed:
= INDEX ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)
Og nedenstående formel giver den kommende begivenhedsdato:
= INDEX ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)
Lad mig forklare, hvordan denne formel fungerer.
For at få hændelsesdatoen søger MATCH -funktionen efter den aktuelle dato i kolonne B. I dette tilfælde leder vi ikke efter en nøjagtig match, men en omtrentlig. Og derfor er det sidste argument for MATCH -funktionen 1 (som finder den største værdi, der er mindre end eller lig med opslagsværdien).
Så MATCH -funktionen returnerer placeringen af cellen, der har datoen, der er lige mindre end eller lig med den aktuelle dato. Så den næste begivenhed, i dette tilfælde, ville være i den næste celle (da listen er sorteret i stigende rækkefølge).
Så for at få den kommende begivenhedsdato, skal du blot tilføje en til cellepositionen, der returneres af MATCH -funktionen, og det vil give dig cellepositionen for den næste begivenhedsdato.
Denne værdi er derefter givet af INDEX -funktionen.
For at få hændelsesnavnet bruges den samme formel, og området i INDEX -funktion ændres fra kolonne B til kolonne A.
Klik her for at downloade eksempelfilen
Ideen med dette eksempel kom til mig, da en ven henvendte sig med en anmodning. Han havde en liste over alle sine venner/slægtninges fødselsdage i en kolonne og ville vide, at den næste fødselsdag kommer (og navnet på personen).Dette er tre eksempler, der viser, hvordan du finder den nærmeste matchende værdi i Excel ved hjælp af opslagsformler.
Du kan også lide følgende Excel -tips/vejledninger
- Få det sidste nummer fra en liste ved hjælp af VLOOKUP -funktion.
- Få flere opslagsværdier uden gentagelse i en enkelt celle.
- VLOOKUP Vs. INDEX/MATCH
- Excel INDEX MATCH
- Find den sidste forekomst af en opslagsværdi en liste i Excel
- Find og fjern dubletter i Excel
- Betinget formatering.
