Artimiausio numerio radimas

Turinys

Praktikoje labai dažnai pasitaiko atvejų, kai jums ir man reikia rasti artimiausią reikšmę aibėje (lentelėje) tam tikro skaičiaus atžvilgiu. Tai gali būti, pavyzdžiui:

Nuolaidos apskaičiavimas priklausomai nuo apimties.
Premijų dydžio apskaičiavimas priklausomai nuo plano įgyvendinimo.
Pristatymo įkainių apskaičiavimas priklausomai nuo atstumo.
Tinkamų konteinerių prekėms parinkimas ir kt.

Be to, priklausomai nuo situacijos, gali reikėti apvalinti ir aukštyn, ir žemyn.

Yra keli būdai – akivaizdūs ir ne tokie akivaizdūs – išspręsti tokią problemą. Pažvelkime į juos paeiliui.

Pirmiausia įsivaizduokime tiekėją, kuris suteikia nuolaidas didmeninei prekybai, o nuolaidos procentas priklauso nuo perkamų prekių kiekio. Pavyzdžiui, perkant daugiau nei 5 vnt., taikoma 2% nuolaida, o perkant nuo 20 vnt – jau 6% ir t.t.

Kaip greitai ir gražiai suskaičiuoti nuolaidos procentą įvedant perkamų prekių kiekį?

1 būdas: įdėtieji IF

Metodas iš serijos „ką čia galvoti – reikia pašokti!“. Įdėtų funkcijų naudojimas IF (JEI) nuosekliai patikrinti, ar langelio reikšmė patenka į kiekvieną intervalą, ir parodyti atitinkamo diapazono nuolaidą. Tačiau formulė šiuo atveju gali pasirodyti labai sudėtinga:

Artimiausio numerio radimas

Manau, akivaizdu, kad derinti tokią „lėlę pabaisą“ ar po kurio laiko pabandyti pridėti porą naujų sąlygų yra smagu.

Be to, „Microsoft Excel“ turi IF funkcijos įdėjimo limitą – 7 kartus senesnėse versijose ir 64 kartus naujesnėse versijose. O jei reikia daugiau?

2 metodas. VLOOKUP su intervalų rodiniu

Šis metodas yra daug kompaktiškesnis. Norėdami apskaičiuoti nuolaidos procentą, naudokite legendinę funkciją VPR (ŽIŪRĖTI) apytikslės paieškos režimu:

Artimiausio numerio radimas

kur

B4 – prekių kiekio vertė pirmame sandoryje, kuriam ieškome nuolaidos
$4 G$: $8 H$ – nuoroda į nuolaidų lentelę – be „antraštės“ ir su $ ženklu fiksuotais adresais.
2 — eilės numeris nuolaidų lentelės stulpelio, iš kurio norime gauti nuolaidos vertę
TIKROJI – štai kur palaidotas „šuo“. Jei kaip paskutinis funkcijos argumentas VPR nurodyti MELAVIMAS (FALSE) arba 0, tada funkcija ieškos griežtos rungtynės kiekio stulpelyje (o mūsų atveju tai duos #N/A klaidą, nes nuolaidų lentelėje nėra 49 reikšmės). Bet jei vietoj MELAVIMAS rašyti TIKROJI (TIESA) arba 1, tada funkcija ieškos ne tikslios, o artimiausias mažiausias vertės ir suteiks mums reikalingą nuolaidos procentą.

Šio metodo trūkumas yra tai, kad reikia rūšiuoti nuolaidų lentelę didėjančia tvarka pagal pirmąjį stulpelį. Jei tokio rūšiavimo nėra (arba tai daroma atvirkštine tvarka), tada mūsų formulė neveiks:

Artimiausio numerio radimas

Atitinkamai, šis metodas gali būti naudojamas tik artimiausiai mažiausiai vertei rasti. Jei jums reikia rasti artimiausią didžiausią, turite naudoti kitą metodą.

3 būdas. Artimiausio didžiausio suradimas naudojant INDEX ir MATCH funkcijas

Dabar pažvelkime į mūsų problemą iš kitos pusės. Tarkime, mes parduodame kelis įvairaus galingumo pramoninių siurblių modelius. Pardavimų lentelėje kairėje parodyta klientui reikalinga galia. Turime pasirinkti artimiausios didžiausios arba vienodos galios siurblį, bet ne mažesnį nei reikalaujama pagal projektą.

Funkcija VLOOKUP čia nepadės, tad teks naudoti jos analogą – krūvą INDEX funkcijų (INDEKSAS) ir DAUGIAU ATSKIRTA (MATCH):

Artimiausio numerio radimas

Čia funkcija MATCH su paskutiniu argumentu -1 veikia artimiausios didžiausios reikšmės radimo režimu, o funkcija INDEX iš gretimo stulpelio ištraukia mums reikalingą modelio pavadinimą.

4 būdas. Nauja funkcija VIEW (XLOOKUP)

Jei turite „Office 365“ versiją su įdiegtais visais naujinimais, tada vietoj VLOOKUP (ŽIŪRĖTI) galite naudoti jo analogą – funkciją VIEW (XLOOKUP), kurį jau išsamiai išanalizavau:

Artimiausio numerio radimas

Čia:

B4 – pradinė prekės kiekio, kuriam ieškome nuolaidos, vertė
$4 G$: $8 G$ – asortimentas, kuriame ieškome degtukų
4 USD: 8 USD – rezultatų diapazonas, iš kurio norite grąžinti nuolaidą
ketvirtas argumentas (-1) apima artimiausio mažiausio skaičiaus paiešką, o ne tikslios atitikties.

Šio metodo privalumai yra tai, kad nereikia rūšiuoti nuolaidų lentelės ir galimybė prireikus ieškoti ne tik artimiausios mažiausios, bet ir artimiausios didžiausios vertės. Paskutinis argumentas šiuo atveju bus 1.

Deja, šią funkciją turi dar ne visi – tik laimingi „Office 365“ savininkai.

5 metodas. Power Query

Jei dar nesate susipažinę su galingu ir visiškai nemokamu Power Query priedu, skirtu Excel, tuomet esate čia. Jei jau esate susipažinę, pabandykime jį panaudoti savo problemai išspręsti.

Pirmiausia atlikime parengiamuosius darbus:

Konvertuokime šaltinio lenteles į dinamines (išmaniąsias) naudodami sparčiuosius klavišus "Ctrl"+T arba komanda Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę).
Kad būtų aiškumo, suteikime jiems pavadinimus. Pardavimas и Nuolaidos kortelė Konstruktorius (Dizainas).
Įkelkite kiekvieną lentelę paeiliui į Power Query naudodami mygtuką Iš lentelės/diapazono kortelė Duomenys (Duomenys – iš lentelės/diapazono). Naujausiose „Excel“ versijose šis mygtukas buvo pervadintas į Su lapais (Iš lapo).
Jei lentelėse yra skirtingi stulpelių pavadinimai su kiekiais, kaip mūsų pavyzdyje („Prekių kiekis“ ir „Kiekis nuo…“), tada jos turi būti pervadintos „Power Query“ ir pavadintos taip pat.
Po to galite grįžti į „Excel“, pasirinkę komandą „Power Query“ redaktoriaus lange Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis - Uždaryti ir įkelti - Uždaryti ir įkelti į...) ir tada variantas Tiesiog sukurkite ryšį (Tik sukurti ryšį).
Tada prasideda įdomiausia. Jei turite Power Query patirties, manau, kad tolimesnė mintis turėtų būti nukreipta į šių dviejų lentelių sujungimą su sujungimo užklausa (sujungimas) a la VLOOKUP, kaip buvo ankstesniame metode. Tiesą sakant, turėsime susijungti pridėjimo režimu, o tai iš pirmo žvilgsnio nėra akivaizdu. Pasirinkite „Excel“ skirtuke Duomenys – Gauti duomenis – Sujungti užklausas – Pridėti (Duomenys – Gauti duomenis – Sujungti užklausas – Pridėti) ir tada mūsų stalai Pardavimas и Nuolaidos pasirodžiusiame lange:
Spustelėję OK mūsų lentelės bus sulipusios į vientisą visumą – viena po kita. Atkreipkite dėmesį, kad šiose lentelėse stulpeliai su prekių kiekiu pateko viena po kitos, nes. jie turi tą patį pavadinimą:
Jei jums svarbi pradinė pardavimo lentelės eilučių seka, tada, kad po visų vėlesnių transformacijų galėtumėte ją atkurti, pridėkite numeruotą stulpelį į mūsų lentelę naudodami komandą Stulpelio pridėjimas – rodyklės stulpelis (Pridėti stulpelį – rodyklės stulpelį). Jei eilučių seka jums nesvarbu, galite praleisti šį veiksmą.
Dabar, naudodami lentelės antraštėje esantį išskleidžiamąjį sąrašą, surūšiuokite ją pagal stulpelius Kiekis Didėjimo tvarka:
Ir pagrindinis triukas: dešiniuoju pelės mygtuku spustelėkite stulpelio antraštę Nuolaida pasirinkti komandą Užpildymas – žemyn (Užpildymas – žemyn). Tuščios ląstelės su null automatiškai užpildomos ankstesnėmis nuolaidų reikšmėmis:
Belieka atkurti pradinę eilučių seką rūšiuojant pagal stulpelius rodyklė (vėliau galėsite drąsiai ištrinti) ir atsikratyti nereikalingų linijų su filtru null pagal stulpelį Sandorio kodas: