Lähima numbri leidmine

Sisu

Praktikas on väga sageli juhtumeid, kui teie ja mina peame leidma komplektist (tabelist) antud arvu suhtes lähima väärtuse. See võib olla näiteks:

Allahindluse arvutamine sõltuvalt mahust.
Boonuste suuruse arvutamine sõltuvalt plaani täitmisest.
Saatekulude arvutamine sõltuvalt vahemaast.
Kaubale sobivate konteinerite valik jne.

Lisaks võib olenevalt olukorrast olla vajalik ümardamine nii üles kui alla.

Sellise probleemi lahendamiseks on mitu võimalust – ilmselget ja mitte nii ilmselget. Vaatame neid järjestikku.

Alustuseks kujutame ette tarnijat, kes annab hulgimüügile allahindlusi ja allahindluse protsent sõltub ostetud kauba kogusest. Näiteks ostes üle 5 tk, antakse 2% allahindlust ja alates 20 tk ostes – juba 6% jne.

Kuidas kiiresti ja kaunilt arvutada allahindlusprotsent ostetud kauba koguse sisestamisel?

1. meetod: pesastatud IF-id

Meetod sarjast “mis seal mõelda – on vaja hüpata!”. Pesastatud funktsioonide kasutamine IF (KUI) et kontrollida järjestikku, kas lahtri väärtus langeb igasse intervalli, ja kuvada vastava vahemiku allahindlus. Kuid valem võib sel juhul osutuda väga tülikaks:

Lähima numbri leidmine

Arvan, et on ilmselge, et sellise “koletisnuku” silumine või mõne aja pärast sellele paari uue tingimuse lisamine on lõbus.

Lisaks on Microsoft Excelis IF-funktsiooni pesastumislimiit – vanemates versioonides 7 korda ja uuemates versioonides 64 korda. Mis siis, kui vajate rohkem?

2. meetod. VLOOKUP intervallivaatega

See meetod on palju kompaktsem. Allahindlusprotsendi arvutamiseks kasutage legendaarset funktsiooni VPR (OTSING) ligikaudses otsingurežiimis:

Lähima numbri leidmine

kus

B4 – kaubakoguse väärtus esimeses tehingus, millele soodustust otsime
$ G $ 4: $ H $ 8 – link allahindluste tabelile – ilma “päise”ta ja $-märgiga fikseeritud aadressidega.
2 — allahindluste tabeli veeru järjekorranumber, millest tahame allahindluse väärtust saada
TRUE – siia on maetud “koer”. Kui funktsiooni viimase argumendina VPR täpsustage VALETAMINE (VALE) või 0, siis funktsioon otsib range vaste koguse veerus (ja meie puhul annab see vea #N/A, kuna allahindlustabelis pole väärtust 49). Aga kui selle asemel VALETAMINE kirjutama TRUE (TÕSI) või 1, siis ei otsi funktsioon täpset, vaid lähim väikseim väärtus ja annab meile vajaliku allahindluse protsendi.

Selle meetodi negatiivne külg on vajadus sortida allahindluste tabel esimese veeru järgi kasvavas järjekorras. Kui sellist sorteerimist pole (või tehakse seda vastupidises järjekorras), siis meie valem ei tööta:

Lähima numbri leidmine

Sellest tulenevalt saab seda lähenemisviisi kasutada ainult lähima väikseima väärtuse leidmiseks. Kui teil on vaja leida lähim suurim, peate kasutama teistsugust lähenemist.

3. meetod. Lähima suurima leidmine funktsioonide INDEX ja MATCH abil

Vaatame nüüd oma probleemi teisest küljest. Oletame, et müüme mitut erineva võimsusega tööstuspumpade mudelit. Vasakpoolses müügitabelis on näidatud kliendile vajalik võimsus. Peame valima pumba, mille võimsus on lähim maksimaalne või võrdne, kuid mitte väiksem kui projektis nõutav.

Funktsioon VLOOKUP siin ei aita, seega peate kasutama selle analoogi - hunnik INDEX-funktsioone (INDEX) ja ROHKEM AVALDATUD (MATCH):

Lähima numbri leidmine

Siin töötab funktsioon MATCH viimase argumendiga -1 lähima suurima väärtuse leidmise režiimis ja funktsioon INDEX eraldab seejärel külgnevast veerust meile vajaliku mudeli nime.

4. meetod. Uus funktsioon VIEW (XLOOKUP)

Kui teil on Office 365 versioon, kuhu on installitud kõik värskendused, siis VLOOKUP asemel (OTSING) saate kasutada selle analoogi – VIEW funktsiooni (XLOOKUP), mida olen juba üksikasjalikult analüüsinud:

Lähima numbri leidmine

siin:

B4 – toote koguse algväärtus, millele soodustust otsime
4 $ G$: 8 $ G$ – vahemik, kust me vasteid otsime
$H $ 4: $ H $ 8 – tulemuste vahemik, millelt soovite allahindlust tagastada
neljas argument (-1) sisaldab täpse vaste asemel lähima väikseima numbri otsimist.

Selle meetodi eelisteks on see, et puudub vajadus allahindluste tabeli sorteerimiseks ja võimalus otsida vajadusel mitte ainult lähima väikseima, vaid ka lähima suurima väärtuse järgi. Viimane argument sel juhul on 1.

Kuid kahjuks pole seda funktsiooni veel kõigil – ainult Office 365 õnnelikel omanikel.

5. meetod. Power Query

Kui te pole veel tuttav võimsa ja täiesti tasuta Exceli Power Query lisandmooduliga, siis olete siin. Kui olete juba tuttav, proovime seda oma probleemi lahendamiseks kasutada.

Teeme kõigepealt mõned ettevalmistustööd:

Teisendame oma lähtetabelid dünaamilisteks (nutikateks), kasutades kiirklahvi Ctrl+T või meeskond Avaleht – vorminda tabelina (Avaleht — vorminda tabelina).
Selguse huvides anname neile nimed. Müük и Allahindlused tab Ehitaja (Kujundus).
Laadige kõik tabelid kordamööda Power Querysse, kasutades nuppu Tabelist/vahemikust tab kuupäev (Andmed – tabelist/vahemikust). Exceli viimastes versioonides on see nupp ümber nimetatud Lehtedega (Lehelt).
Kui tabelitel on erinevad veergude nimed koos kogustega, nagu meie näites (“Kaubakogus” ja “Kogus alates…”), siis tuleb need Power Querys ümber nimetada ja nimetada sama.
Pärast seda saate naasta Excelisse, valides Power Query redaktori aknas käsu Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht — Sulge&laadi — Sule&laadi...) ja siis valik Looge lihtsalt ühendus (Loo ainult ühendus).
Siis algab kõige huvitavam. Kui teil on Power Queryga kogemusi, siis eeldan, et edasine mõttekäik peaks olema nende kahe tabeli ühendamise suunas a la VLOOKUP-i ühendamise päringuga, nagu ka eelmise meetodi puhul. Tegelikult peame ühendama lisamisrežiimis, mis pole esmapilgul üldse ilmne. Valige Exceli vahekaardil Andmed – Hangi andmed – Ühenda taotlused – Lisa (Andmed – Hangi andmed – Ühenda päringud – Lisa) ja siis meie lauad Müük и Allahindlused ilmuvas aknas:
Pärast klõpsamist nupul OK meie lauad liimitakse ühtseks tervikuks – üksteise alla. Pange tähele, et nende tabelite kaubakoguste veerud langesid üksteise alla, kuna. neil on sama nimi:
Kui teile on oluline müügitabeli esialgne ridade jada, siis selleks, et pärast kõiki järgnevaid teisendusi saaksite selle taastada, lisage meie tabelisse nummerdatud veerg käsuga Veeru lisamine – indeksi veerg (Lisa veerg – indeksi veerg). Kui ridade järjestus ei ole teie jaoks oluline, võite selle sammu vahele jätta.
Nüüd, kasutades tabeli päises olevat ripploendit, sorteerige see veeru järgi Kogus Kasvav:
Ja peamine nipp: paremklõpsake veeru päisel Allahindlus vali meeskond Täida – alla (Täida – alla). Tühjendage lahtrid koos tühjaks automaatselt täidetakse eelmiste allahindluste väärtustega:
Jääb alles taastada algne ridade jada, sorteerides veeru järgi indeks (saate selle hiljem julgelt kustutada) ja vabaneda tarbetutest joontest filtriga tühjaks veeru järgi Tehingu kood: