✅ Raskite artimiausią atitikmenį „Excel“ naudodami formules

Turinys

Raskite „Excel“ artimiausią atitikmenį

„Excel“ funkcijos gali būti labai efektyvios, jei pradedate derinti įvairias formules. Daiktai, kurie galėjo atrodyti neįmanomi, staiga pradėtų atrodyti kaip vaikų žaidimas.

Vienas iš tokių pavyzdžių yra rasti artimiausią paieškos vertės atitiktį „Excel“ duomenų rinkinyje.

„Excel“ yra keletas naudingų paieškos funkcijų (pvz., VLOOKUP & INDEX MATCH), kuri gali rasti artimiausią atitikimą keliais paprastais atvejais (kaip parodysiu toliau pateiktus pavyzdžius).

Tačiau geriausia yra tai, kad galite sujungti šias paieškos funkcijas su kitomis „Excel“ funkcijomis, kad atliktumėte daug daugiau (įskaitant artimiausios paieškos vertės atitikties paiešką nerūšiuotame sąraše).

Šioje pamokoje aš jums parodysiu, kaip rasti artimiausią „Excel“ paieškos vertės atitikimą naudojant peržiūros formules.

Raskite „Excel“ artimiausią atitikmenį

Gali būti daug skirtingų scenarijų, kai reikia ieškoti artimiausios atitikties (arba artimiausios atitikties vertės).

Žemiau pateikiami pavyzdžiai, kuriuos aptarsiu šiame straipsnyje:

Raskite komisinį tarifą, pagrįstą pardavimais
Raskite geriausią kandidatą (remiantis artimiausia patirtimi)
Kito įvykio datos paieška

Pradėkime!

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Raskite komisinį tarifą (ieškote artimiausios pardavimo vertės)

Tarkime, kad turite duomenų rinkinį, kaip parodyta žemiau, kur norite rasti visų pardavimo personalo komisinių normas.

Komisinis mokestis priskiriamas pagal pardavimo vertę. Ir tai apskaičiuojama naudojant dešinėje esančią lentelę.

Pavyzdžiui, jei pardavėjas parduoda 5000, komisinis mokestis yra 0%, o jei jis/ji parduoda 15 000, komisinis mokestis yra 5%.

Norėdami gauti komisinį tarifą, turite rasti artimiausią pardavimo diapazoną, kuris yra tik mažesnis nei pardavimo vertė. Pavyzdžiui, už 15 000 pardavimo vertę komisinis mokestis būtų 10 000 (tai yra 5%), o pardavimo vertė - 25 000, komisinis mokestis būtų 20 000 (tai yra 7%).

Norėdami rasti artimiausią pardavimo vertę ir gauti komisinį tarifą, galite naudoti apytikslę atitiktį VLOOKUP.

Žemiau pateikta formulė tai padarys:

= VLOOKUP (B2, $ 2 $: $ F $ 6,2,1)

Atminkite, kad šioje formulėje paskutinis argumentas yra 1, kuris nurodo formulei naudoti apytikslę peržiūrą. Tai reiškia, kad formulė peržiūrėtų pardavimo stulpelio E vertes ir surastų vertę, kuri yra tik mažesnė už paieškos vertę.

Tada VLOOKUP formulė pateiks šios vertės komisinį mokestį.

Pastaba: Kad tai veiktų, turite surūšiuoti duomenis didėjančia tvarka.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Raskite geriausią kandidatą (remiantis artimiausia patirtimi)

Pirmiau pateiktame pavyzdyje duomenis reikėjo surūšiuoti didėjančia tvarka. Tačiau gali būti atvejų, kai duomenys nerūšiuojami.

Taigi, pateiksime pavyzdį ir pažiūrėkime, kaip naudojant formulių derinį galime rasti artimiausią atitikmenį „Excel“.

Žemiau yra duomenų rinkinio pavyzdys, kuriame turiu rasti darbuotojo vardą, kurio darbo patirtis yra arčiausiai norimos vertės. Norima vertė šiuo atveju per 2,5 metų.

Atminkite, kad duomenys nerūšiuoti. Be to, artimiausia patirtis gali būti mažesnė arba didesnė nei duoti. Pavyzdžiui, 2 metai ir 3 metai yra vienodai artimi (skirtumas - 0,5 metų).

Žemiau yra formulė, kuri duos mums rezultatą:

= INDEKSAS ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))

Šios formulės gudrybė yra pakeisti paieškos masyvą ir paieškos vertę, kad būtų nustatytas minimalus reikalaujamų ir faktinių verčių skirtumas.

Pirmiausia suprasime, kaip tai darytumėte rankiniu būdu (o tada paaiškinsiu, kaip ši formulė veikia).

Atlikdami tai rankiniu būdu, peržiūrėsite kiekvieną B stulpelio langelį ir sužinosite skirtumą tarp to, ko reikia, ir to, ką turi žmogus. Kai turėsite visus skirtumus, rasite tą, kuris yra minimalus, ir atsiųskite to asmens vardą.

Būtent tai mes darome pagal šią formulę.

Leisk man paaiškinti.

Paieškos vertė MATCH formulėje yra MIN (ABS (D2-B2: B15)).

Šioje dalyje pateikiamas minimalus skirtumas tarp nurodytos patirties (kuri yra 2,5 metų) ir visos kitos patirties. Šiame pavyzdyje jis grąžina 0,3

Atminkite, kad naudojau ABS, kad įsitikinčiau, jog ieškau artimiausio (tai gali būti daugiau ar mažiau nei nurodyta patirtis).

Dabar ši minimali vertė tampa mūsų paieškos verte.

Funkcijos MATCH paieškos masyvas yra ABS (D2- $ B $ 2: $ B $ 15).

Tai suteikia mums skaičių masyvą, iš kurio buvo atimta 2,5 (reikalinga patirtis).

Taigi dabar turime paieškos vertę (0,3) ir paieškos masyvą ({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})

Funkcija MATCH šiame masyve nustato 0,3 poziciją, kuri taip pat yra asmens, turinčio artimiausią patirtį, vardo pozicija.

Tada šį pozicijos numerį funkcija INDEX naudoja norėdama grąžinti asmens vardą.

Pastaba: Jei yra keli kandidatai, turintys tą pačią minimalią patirtį, aukščiau pateiktoje formulėje bus nurodytas pirmojo atitinkančio darbuotojo vardas.

Raskite kito įvykio datą

Tai dar vienas pavyzdys, kai galite naudoti paieškos formules, kad surastumėte kitą įvykio datą pagal dabartinę datą.

Žemiau yra duomenų rinkinys, kuriame turiu įvykių pavadinimus ir įvykių datas.

Noriu kito įvykio pavadinimo ir šio būsimo įvykio datos.

Žemiau yra formulė, kuri suteiks būsimo įvykio pavadinimą:

= INDEKSAS ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Žemiau pateikta formulė parodys būsimo įvykio datą:

= INDEKSAS ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Leiskite man paaiškinti, kaip ši formulė veikia.

Norėdami gauti įvykio datą, funkcija MATCH B skiltyje ieško dabartinės datos. Tokiu atveju mes ieškome ne tikslios atitikties, o apytikslės. Taigi paskutinis funkcijos MATCH argumentas yra 1 (kuri nustato didžiausią vertę, mažesnę arba lygią paieškos vertei).

Taigi funkcija MATCH grąžintų langelio, kurio data yra tik mažesnė arba lygi dabartinei datai, padėtį. Taigi kitas įvykis, šiuo atveju, būtų kitame langelyje (nes sąrašas surūšiuotas didėjančia tvarka).

Taigi, norėdami sužinoti artėjančio įvykio datą, tiesiog pridėkite vieną prie langelio padėties, kurią grąžino funkcija MATCH, ir ji suteiks jums kito įvykio datos langelio padėtį.

Šią vertę nurodo funkcija INDEX.

Įvykio pavadinimui gauti naudojama ta pati formulė, o INDEX funkcijos diapazonas iš B stulpelio pakeičiamas į A stulpelį.

Spustelėkite čia, jei norite atsisiųsti pavyzdinį failą

Šio pavyzdžio idėja man kilo, kai draugė kreipėsi su prašymu. Jis stulpelyje turėjo visų savo draugų/giminių gimtadienių sąrašą ir norėjo sužinoti artėjančio gimtadienio datą (ir asmens vardą).

Tai yra trys pavyzdžiai, rodantys, kaip rasti artimiausią atitinkančią „Excel“ vertę naudojant peržiūros formules.

Jums taip pat gali patikti šie „Excel“ patarimai/vadovėliai