Excel-funksies kan baie kragtig wees as jy die kans kry om verskillende formules te kombineer.Wat eens onmoontlik gelyk het, kan skielik soos kinderspeletjies word.
Een so 'n voorbeeld is om die naaste waarde in 'n Excel-datastel te vind.
Daar is verskeie nuttige opsoekfunksies in Excel (soos VLOOKUP en INDEX MATCH) wat die naaste kan vindwaarde(soos getoon in die voorbeeld hieronder).
Maar die beste deel is dat jy hierdie opsoekfunksies met ander Excel-funksies kan kombineer om soveel meer te doen (insluitend om die naaste passing aan 'n opsoekwaarde in 'n ongesorteerde lys te vind).
In hierdie tutoriaal sal ek jou wys hoe om 'n opsoekformule te gebruik om die naaste passing aan 'n opsoekwaarde in Excel te vind.
Daar is baie verskillende scenario's waar jy die naaste passing (of naaste passing waarde) moet vind.
Hier is 'n voorbeeld wat ek in hierdie artikel sal dek:
- Vind kommissie tariewe gebaseer op verkope
- Vind die beste kandidaat (gebaseer op naaste ervaring)
- Vind die volgende gebeurtenis datum
kom ons begin!
Contents [show]
Vind kommissie tariewe (kyk vir die naaste verkoopswaarde)
Gestel jy het 'n datastel soos die een hieronder waar jy die kommissiekoerse vir alle verkoopspersone wil vind.
Kommissies word verdeel op grond van die waarde van die verkoop.Dit word bereken deur die tabel aan die regterkant te gebruik.
Byvoorbeeld, as die verkoopspersoon se totale verkope 5000 0 is, is die kommissie 15000%, en as sy/haar totale verkope 5 XNUMX is, is die kommissie XNUMX%.
Om 'n kommissiekoers te kry, moet jy die naaste verkoopsreeks vind wat net onder die verkoopswaarde is.Byvoorbeeld, vir 15000 10,000 verkope is die kommissie 5 25000 (of 20,000%), en vir 7 XNUMX verkope is die kommissiekoers XNUMX XNUMX (of XNUMX%).
Om die naaste verkoopswaarde te vind en 'n kommissiekoers te kry, kan jy benaderde passing in VLOOKUP gebruik.
Die volgende formule kan dit doen:
=VLOOKUP(B2,$E$2:$F$6,2,1)
Let daarop dat in hierdie formule, die laaste parameter 1 is, wat die formule sê om benaderde opsoek te gebruik.Dit beteken dat die formule deur die verkoopswaarde in kolom E sal gaan en die waarde net onder die opsoekwaarde sal vind.
Die VLOOKUP-formule sal dan die kommissiekoers vir daardie waarde gee.
注意: Om dit te doen, moet jy die data in stygende volgorde sorteer.
Vind die beste kandidaat (gebaseer op naaste ervaring)
In die voorbeeld hierbo moet die data in stygende volgorde gesorteer word.Maar daar kan gevalle wees waar die data nie gesorteer is nie.
Kom ons neem dus 'n voorbeeld en kyk hoe ons formulekombinasies kan gebruik om die naaste passing in Excel te vind.
Hieronder is 'n voorbeelddatastel waar ek die name van werknemers moet vind wie se werkservaring die naaste aan die verlangde waarde is.Die verwagte waarde in hierdie geval is 2.5 jaar.
Let daarop dat die data nie gesorteer is nie.Daarbenewens kan die naaste ervarings minder of meer as 'n gegewe ervaring wees.Byvoorbeeld, 2 jaar en 3 jaar is ewe naby (0.5 jaar verskil).
Hier is die formule wat ons die resultaat gee:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Die truuk in hierdie formule is om die opsoekskikking en opsoekwaardes te verander om die kleinste empiriese verskil tussen die verlangde en werklike waardes te vind.
Kom ons verstaan eers hoe jy dit met die hand sal doen (en dan sal ek verduidelik hoe hierdie formule werk).
Wanneer jy dit met die hand doen, gaan jy deur elke sel in kolom B en vind die verskil tussen die ervaring wat jy wil hê en die ervaring wat jy het.Sodra jy al die verskille het, vind jy die kleinste een en kry daardie persoon se naam.
Dit is presies wat ons met hierdie formule doen.
Laat ek verduidelik.
Die opsoekwaarde in die MATCH-formule is MIN(ABS(D2-B2:B15)).
Hierdie afdeling gee jou die minimum verskil tussen die gegewe ervaring (dws 2.5 jaar) en alle ander ervarings.In hierdie geval gee dit 0.3 terug
Let daarop dat ek ABS gebruik om seker te maak ek soek die naaste (waarskynlik meer of minder as die gegewe ervaring).
Nou word hierdie minimum waarde ons opsoekwaarde.
Die opsoekskikking in die MATCH-funksie is ABS(D2-$B$2:$B$15).
Dit gee ons 'n reeks getalle waarvan 2.5 (ervaring vereis) afgetrek word.
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({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})
Die MATCH-funksie vind die posisie van 0.3 in hierdie skikking, wat die posisie is van die naam van die persoon met die naaste ervaring.
Die INDEX-funksie gebruik dan die posnommer om die persoon se naam terug te gee.
Let wel: Indien daar verskeie kandidate met dieselfde minimum ervaring is, sal die bogenoemde formule die naam van die eerste bypassende werknemer gee.
Vind die volgende gebeurtenis datum
Hier is nog 'n voorbeeld waar jy 'n opsoekformule kan gebruik om die volgende datum van 'n gebeurtenis op grond van die huidige datum te vind.
Hieronder is die datastel waar ek gebeurtenisname en gebeurtenisdatums het.
Wat ek wil hê, is die naam van die volgende geleentheid en die geleentheidsdatum van hierdie komende geleentheid.
Hier is die formule wat die naam van die komende geleentheid gee:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
Die volgende formule sal die komende gebeurtenisdatums gee:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Kom ek verduidelik hoe hierdie formule werk.
Om die gebeurtenisdatum te kry, soek die MATCH-funksie in kolom B vir die huidige datum.In hierdie geval soek ons nie 'n presiese passing nie, maar 'n benaderde passing.Daarom is die laaste parameter van die MATCH-funksie 1 (dit vind die grootste waarde minder as of gelyk aan die opsoekwaarde).
Die MATCH-funksie sal dus die posisie van die sel terugstuur waarvan die datum toevallig kleiner as of gelyk is aan die huidige datum.So in hierdie geval sal die volgende gebeurtenis in die volgende sel wees (aangesien die lys in stygende volgorde gesorteer is).
Dus, om die komende gebeurtenisdatum te kry, voeg net een by die selposisie wat deur die MATCH-funksie teruggestuur word en dit sal jou die selposisie van die volgende gebeurtenisdatum gee.
Hierdie waarde word dan deur die INDEX-funksie gegee.
Om die gebeurtenisnaam te kry, gebruik dieselfde formule en verander die reeks in die INDEX-funksie van kolom B na kolom A.
Hierdie voorbeeld het by my opgekom toe 'n vriend 'n versoek gerig het.Hy lys al sy vriende/familie se verjaarsdae in 'n kolom en wil weet die volgende verjaarsdag kom (en die persoon se naam).
Die bogenoemde drie voorbeelde wys hoe om die opsoekformule te gebruik om die naaste waarde in Excel te vind.
