✅ Leidke Excelis valemite abil lähim vaste

Lang L: none (table-of-contents)

Leidke Excelis lähim vaste

Exceli funktsioonid võivad olla äärmiselt võimsad, kui hakkate erinevate valemite kombineerimisel aru saama. Asjad, mis võisid tunduda võimatuna, hakkasid äkki lapse mänguna tunduma.

Üks selline näide on Excelis andmekogumis otsinguväärtuse lähima vaste leidmine.

Excelis on paar kasulikku otsingufunktsiooni (näiteks VLOOKUP & INDEX MATCH), mis leiavad mõne lihtsa juhtumi korral lähima vaste (nagu näitan allpool).

Kuid parim osa on see, et saate kombineerida neid otsingufunktsioone teiste Exceli funktsioonidega, et teha palju rohkem (sh leida otsinguväärtusele lähim vaste sortimata loendist).

Selles õpetuses näitan teile, kuidas leiate Excelis otsinguvalemitega lähima vaste otsinguväärtusele.

Leidke Excelis lähim vaste

Võib olla palju erinevaid stsenaariume, kus peate otsima lähima vaste (või lähima sobiva väärtuse).

Allpool on näited, mida ma selles artiklis käsitlen:

Otsige vahendustasu müügi põhjal
Leidke parim kandidaat (lähima kogemuse põhjal)
Järgmise sündmuse kuupäeva leidmine

Alustame!

Näidisfaili allalaadimiseks klõpsake siin

Otsige komisjonitasu (otsite lähimat müügiväärtust)

Oletame, et teil on allpool näidatud andmekogum, kust soovite leida kõigi müügitöötajate vahendustasusid.

Komisjonitasu määratakse müügiväärtuse alusel. Ja see arvutatakse paremal oleva tabeli abil.

Näiteks kui müüja teeb 5000 müüki, on vahendustasu 0% ja kui ta teeb 15 000 müüki, on vahendustasu 5%.

Komisjonimäära saamiseks peate leidma lähima müügivahemiku, mis on veidi madalam kui müügiväärtus. Näiteks 15000 müügiväärtuse korral oleks vahendustasu 10 000 (mis on 5%) ja müügiväärtuse 25 000 korral oleks vahendustasu 20 000 (mis on 7%).

Lähima müügiväärtuse leidmiseks ja vahendustasu saamiseks võite kasutada VLOOKUPi ligikaudset vastet.

Allolev valem teeks seda:

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

Pange tähele, et selles valemis on viimane argument 1, mis käsib valemil kasutada ligikaudset otsingut. See tähendab, et valem läbiks veeru E müügiväärtused ja leiaks väärtuse, mis on otsinguväärtusest veidi madalam.

Seejärel annab VLOOKUP valem selle väärtuse vahendustasu.

Märge: Et see toimiks, peate andmed sorteerima kasvavas järjekorras.

Näidisfaili allalaadimiseks klõpsake siin

Leidke parim kandidaat (lähima kogemuse põhjal)

Ülaltoodud näites tuli andmed sortida kasvavas järjekorras. Kuid võib juhtuda, et andmeid ei sorteerita.

Nii et toome näite ja vaatame, kuidas valemite kombinatsiooni abil Excelist lähima vaste leida.

Allpool on näidisandmekogum, kust pean leidma töötaja nime, kellel on soovitud väärtusele kõige lähem töökogemus. Soovitud väärtus sel juhul 2,5 aasta jooksul.

Pange tähele, et andmeid ei sorteerita. Samuti võib lähim kogemus olla kas väiksem või rohkem kui antud kogemus. Näiteks 2 aastat ja 3 aastat on mõlemad võrdselt lähedal (vahe 0,5 aastat).

Allpool on valem, mis annab meile tulemuse:

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

Selle valemi nipp on muuta otsimismassiivi ja otsinguväärtust, et leida nõutavate ja tegelike väärtuste minimaalne kogemuserinevus.

Mõistame kõigepealt, kuidas seda käsitsi teha (ja siis selgitan, kuidas see valem töötab).

Kui teete seda käsitsi, vaatate läbi veeru B iga lahtri ja leiate erinevuse nõutava kogemuse ja selle vahel, mis inimesel on. Kui teil on kõik erinevused, leiate minimaalse ja leidke selle inimese nimi.

Selle valemiga teeme täpselt seda.

Las ma selgitan.

Otsinguväärtus MATCH valemis on MIN (ABS (D2-B2: B15)).

See osa annab teile minimaalse erinevuse antud kogemuse (mis on 2,5 aastat) ja kõigi teiste kogemuste vahel. Selles näites tagastab see väärtuse 0,3

Pange tähele, et olen kasutanud ABS -i veendumaks, et otsin lähimat (mis võib olla rohkem või vähem kui antud kogemus).

Nüüd saab sellest minimaalsest väärtusest meie otsinguväärtus.

Funktsiooni MATCH otsimismassiiv on ABS (D2- $ B $ 2: $ B $ 15).

See annab meile hulga numbreid, millest on lahutatud 2,5 (nõutav kogemus).

Nüüd on meil otsinguväärtus (0,3) ja otsingumassiiv ({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})

Funktsioon MATCH leiab selles massiivis positsiooni 0,3, mis on ka lähima kogemusega isiku nimi.

Seda positsiooni numbrit kasutab funktsioon INDEX isiku nime tagastamiseks.

Märkus. Kui on mitu kandidaati, kellel on sama minimaalne kogemus, annab ülaltoodud valem esimese sobiva töötaja nime.

Leidke järgmise sündmuse kuupäev

See on veel üks näide, kus saate otsinguvalemite abil leida sündmuse järgmise kuupäeva praeguse kuupäeva põhjal.

Allpool on andmestik, kus mul on sündmuste nimed ja sündmuste kuupäevad.

Soovin järgmise sündmuse nime ja selle eelseisva sündmuse kuupäeva.

Allpool on valem, mis annab eelseisva sündmuse nime:

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

Ja allpool olev valem annab eelseisva sündmuse kuupäeva:

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

Las ma selgitan, kuidas see valem töötab.

Sündmuse kuupäeva saamiseks otsib funktsioon MATCH veerust B praeguse kuupäeva. Sellisel juhul ei otsi me täpset vastet, vaid ligikaudset. Seega on funktsiooni MATCH viimane argument 1 (mis leiab suurima väärtuse, mis on väiksem või võrdne otsinguväärtusega).

Seega tagastab funktsioon MATCH selle lahtri asukoha, mille kuupäev on praegusest kuupäevast väiksem või sellega võrdne. Seega oleks järgmine sündmus sel juhul järgmises lahtris (kuna loend on sorteeritud kasvavas järjekorras).

Nii et eelseisva sündmuse kuupäeva saamiseks lisage lihtsalt üks funktsioon MATCH tagastatud lahtri positsioonile ja see annab teile järgmise sündmuse kuupäeva lahtri asukoha.

Selle väärtuse annab funktsioon INDEX.

Sündmuse nime saamiseks kasutatakse sama valemit ja funktsiooni INDEX vahemik muudetakse veerust B veergu A.

Näidisfaili allalaadimiseks klõpsake siin

Selle näite mõte tekkis mul siis, kui sõber pöördus palvega. Tal oli veerus kõigi oma sõprade/sugulaste sünnipäevade nimekiri ja ta tahtis teada järgmist sünnipäeva (ja inimese nime).

Need on kolm näidet, mis näitavad, kuidas otsimisvalemite abil Excelist lähimat sobivat väärtust leida.

Samuti võivad teile meeldida järgmised Exceli näpunäited/õpetused