Пошук бліжэйшага нумара

змест

На практыцы вельмі часта сустракаюцца выпадкі, калі нам з вамі неабходна знайсці ў мностве (табліцы) найбольш блізкае значэнне ў адносінах да зададзенага ліку. Гэта можа быць, напрыклад:

Разлік зніжкі ў залежнасці ад аб'ёму.
Разлік сумы прэмій у залежнасці ад выканання плана.
Разлік кошту дастаўкі ў залежнасці ад адлегласці.
Выбар падыходнай тары для тавараў і інш.

Прычым акругленне можа спатрэбіцца як у большы, так і ў меншы бок, у залежнасці ад сітуацыі.

Ёсць некалькі спосабаў - відавочных і не вельмі - вырашыць такую праблему. Давайце разгледзім іх паслядоўна.

Для пачатку ўявім пастаўшчыка, які дае скідкі на опт, і працэнт скідкі залежыць ад колькасці набытага тавару. Напрыклад, пры куплі больш за 5 штук даецца зніжка 2%, а пры куплі ад 20 штук – ужо 6% і г.д.

Як хутка і прыгожа разлічыць працэнт зніжкі пры ўводзе колькасці набытага тавару?

Спосаб 1: Укладзеныя IF

Метад з серыі «што тут думаць - трэба скакаць!». Выкарыстанне ўкладзеных функцый IF (КАЛІ) каб паслядоўна праверыць, ці трапляе значэнне ячэйкі ў кожны з інтэрвалаў, і адлюстраваць зніжку для адпаведнага дыяпазону. Але формула ў гэтым выпадку можа апынуцца вельмі грувасткай:

Пошук бліжэйшага нумара

Я думаю, відавочна, што адладжваць такую «ляльку-монстра» або спрабаваць праз некаторы час дадаць да яе пару новых умоў - гэта весела.

Акрамя таго, Microsoft Excel мае абмежаванне ўкладзенасці для функцыі IF – 7 разоў у старых версіях і 64 разы ў новых версіях. Што рабіць, калі вам трэба больш?

Спосаб 2. VLOOKUP з праглядам інтэрвалаў

Гэты спосаб значна больш кампактны. Каб разлічыць працэнт зніжкі, скарыстайцеся легендарнай функцыяй ВПР (ВПР) у рэжыме прыблізнага пошуку:

Пошук бліжэйшага нумара

дзе

B4 – значэнне колькасці тавару ў першай транзакцыі, на якую мы шукаем зніжку
$G$4:$H$8 – спасылка на табліцу скідак – без «шапкі» і з адрасамі, замацаванымі знакам $.
2 — парадкавы нумар слупка ў табліцы скідак, з якога мы хочам атрымаць значэнне скідкі
TRUE, – тут “сабака” закапаны. Калі як апошні аргумент функцыі ВПР спецыфікаваных ХЛУСНІЦЬ (ХЛУСНЯ) або 0, тады функцыя будзе шукаць строгі матч у слупку колькасці (і ў нашым выпадку гэта дасць памылку #N/A, бо ў табліцы скідак няма значэння 49). Але калі замест ХЛУСНІЦЬ запіс TRUE, (Праўда) або 1, тады функцыя будзе шукаць не дакладны, а бліжэйшы найменшы кошт і дасць нам патрэбны працэнт зніжкі.

Мінусам гэтага метаду з'яўляецца неабходнасць сартаваць табліцу скідак па ўзрастанні па першым слупку. Калі такой сартавання няма (або яна робіцца ў зваротным парадку), то наша формула не будзе працаваць:

Пошук бліжэйшага нумара

Адпаведна, гэты падыход можна выкарыстоўваць толькі для пошуку бліжэйшага найменшага значэння. Калі вам трэба знайсці бліжэйшы самы вялікі, то вы павінны выкарыстоўваць іншы падыход.

Спосаб 3. Знаходжанне бліжэйшага па велічыні з дапамогай функцый INDEX і MATCH

А цяпер паглядзім на нашу праблему з іншага боку. Дапусцім, мы прадаем некалькі мадэляў прамысловых помпаў рознай магутнасці. Табліца продажаў злева паказвае магутнасць, неабходную кліенту. Нам трэба падабраць помпа бліжэйшай максімальнай або роўнай магутнасці, але не менш, чым патрабуецца па праекце.

Функцыя VLOOKUP тут не дапаможа, таму прыйдзецца выкарыстоўваць яе аналаг - кучу функцый INDEX. (ІНДЭКС) і БОЛЬШ ВЫКЛЮЧАНЫЯ (МАТЧ):

Пошук бліжэйшага нумара

Тут функцыя MATCH з апошнім аргументам -1 працуе ў рэжыме пошуку бліжэйшага па велічыні значэння, а функцыя INDEX затым здабывае патрэбнае нам імя мадэлі з суседняга слупка.

Спосаб 4. Новая функцыя VIEW (XLOOKUP)

Калі ў вас версія Office 365 з усімі ўсталяванымі абнаўленнямі, то замест VLOOKUP (ВПР) можна скарыстацца яе аналагам – функцыяй ПРАГЛЯД (XLOOKUP), які я ўжо падрабязна прааналізаваў:

Пошук бліжэйшага нумара

Тут:

B4 – пачатковае значэнне колькасці тавару, на які мы шукаем зніжку
$G$4:$G$8 – дыяпазон, дзе мы шукаем супадзенні
$H$4:$H$8 – дыяпазон вынікаў, з якіх вы хочаце вярнуць зніжку
чацвёрты аргумент (-1) уключае пошук бліжэйшага найменшага ліку, які мы жадаем, замест дакладнага супадзення.

Перавагі гэтага метаду ў адсутнасці неабходнасці сартаваць табліцу скідак і магчымасці пошуку пры неабходнасці не толькі бліжэйшага найменшага, але і бліжэйшага найбольшага значэння. Апошні аргумент у гэтым выпадку будзе 1.

Але, на жаль, гэтая функцыя ёсць пакуль не ва ўсіх - толькі ў шчаслівых уладальнікаў Office 365.

Спосаб 5. Power Query

Калі вы яшчэ не знаёмыя з магутнай і цалкам бясплатнай надбудовай Power Query для Excel, тады вам сюды. Калі вы ўжо знаёмыя, то давайце паспрабуем выкарыстаць яго для вырашэння нашай праблемы.

Давайце спачатку зробім некаторыя падрыхтоўчыя працы:

Давайце пераўтворым нашы зыходныя табліцы ў дынамічныя (разумныя) з дапамогай спалучэння клавіш Ctrl+T або каманда Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу).
Для нагляднасці дамо ім імёны. продажаў и Купоны таб Канструктар (Дызайн).
Загрузіце кожную з табліц па чарзе ў Power Query з дапамогай кнопкі З табліцы/дыяпазону таб Дата (Дадзеныя — з табліцы/дыяпазону). У апошніх версіях Excel гэтая кнопка была перайменавана ў З лісточкамі (З аркуша).
Калі табліцы маюць розныя назвы слупкоў з колькасцю, як у нашым прыкладзе («Колькасць тавараў» і «Колькасць ад…»), то іх трэба перайменаваць у Power Query і назваць аднолькава.
Пасля гэтага вы можаце вярнуцца ў Excel, выбраўшы каманду ў акне рэдактара Power Query Галоўная — Зачыніць і загрузіць — Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у…) а затым варыянт Проста стварыце злучэнне (Толькі стварыць злучэнне).
Далей пачынаецца самае цікавае. Калі ў вас ёсць досвед працы з Power Query, то я мяркую, што далейшы ход думак павінен быць у накірунку аб'яднання гэтых дзвюх табліц з дапамогай запыту аб'яднання (зліцця) а-ля VLOOKUP, як гэта было ў папярэднім метадзе. Фактычна, нам трэба будзе аб'яднацца ў рэжыме дадання, што зусім не відавочна на першы погляд. Выберыце ва ўкладцы Excel Дадзеныя – Атрымаць даныя – Аб’яднаць запыты – Дадаць (Дадзеныя — Атрымаць даныя — Аб'яднаць запыты — Дадаць) а потым нашы сталы продажаў и Купоны у якое з'явілася акне:
Пасля націску на кнопку OK нашы сталы будуць склеены ў адзінае цэлае – адзін пад адным. Звярніце ўвагу, што слупкі з колькасцю тавараў у гэтых табліцах ляжалі адзін пад адным, т.к. яны маюць аднолькавую назву:
Калі для вас важная зыходная паслядоўнасць радкоў у табліцы продажаў, то, каб пасля ўсіх наступных пераўтварэнняў вы маглі яе потым аднавіць, дадайце ў нашу табліцу пранумараваны слупок з дапамогай каманды Даданне слупка - слупок індэкса (Дадаць слупок — слупок індэкс). Калі паслядоўнасць радкоў для вас не мае значэння, то гэты крок можна прапусціць.
Цяпер з дапамогай выпадальнага спісу ў шапцы табліцы адсартуйце яе па слупках колькасць па ўзрастанні:
І галоўны прыём: пстрыкніце правай кнопкай мышы на загалоўку слупка Зніжка выбраць каманду Запаўненне - Уніз (Запоўніць — Уніз). Пустыя вочкі с нуля аўтаматычна запаўняецца папярэднімі значэннямі скідкі:
Засталося аднавіць зыходную паслядоўнасць радкоў шляхам сартавання па слупках індэкс (вы можаце смела выдаліць яго пазней) і пазбавіцца ад непатрэбных радкоў з дапамогай фільтра нуля па слупку Код здзелкі: