Ең жақын нөмірді табу – Маған жақын жерде пайдалы тағам

Мазмұны

Тәжірибеде сіз бен маған берілген санға қатысты жиынтықтағы (кестедегі) ең жақын мәнді табу қажет болатын жағдайлар өте жиі кездеседі. Бұл, мысалы, болуы мүмкін:

Көлеміне байланысты жеңілдікті есептеу.
Жоспардың орындалуына байланысты сыйақы мөлшерін есептеу.
Қашықтыққа байланысты жөнелту тарифтерін есептеу.
Тауарлар үшін қолайлы ыдыстарды таңдау және т.б.

Сонымен қатар, жағдайға байланысты дөңгелектеу жоғары және төмен қажет болуы мүмкін.

Мұндай мәселені шешудің бірнеше жолы бар - айқын және соншалықты айқын емес. Оларды ретімен қарастырайық.

Алдымен көтерме саудада жеңілдіктер беретін жеткізушіні елестетіп көрейік, ал жеңілдіктің пайызы сатып алынған тауарлардың санына байланысты. Мысалы, 5 данадан артық сатып алғанда, 2% жеңілдік беріледі, ал 20 данадан сатып алғанда - қазірдің өзінде 6% және т.б.

Сатып алынған тауардың санын енгізу кезінде жеңілдік пайызын қалай тез және әдемі есептеуге болады?

1-әдіс: кірістірілген IFs

«Ойлауға не бар – секіру керек!» сериясынан әдіс. Кірістірілген функцияларды пайдалану IF (Егер) ұяшық мәнінің әрбір интервалға түсетінін дәйекті түрде тексеру және сәйкес ауқым үшін жеңілдікті көрсету. Бірақ бұл жағдайда формула өте қиын болуы мүмкін:

Ең жақын санды табу

Менің ойымша, мұндай «құбыжық қуыршағын» жөндеу немесе біраз уақыттан кейін оған бірнеше жаңа шарттар қосуға тырысу қызық екені анық.

Сонымен қатар, Microsoft Excel бағдарламасында IF функциясы үшін кірістіру шегі бар – ескі нұсқаларда 7 рет және жаңа нұсқаларда 64 рет. Егер сізге көбірек қажет болса ше?

2-әдіс. Аралық көрінісі бар VLOOKUP

Бұл әдіс әлдеқайда ықшам. Жеңілдік пайызын есептеу үшін аңызға айналған функцияны пайдаланыңыз VPR (КӨРУ) шамамен іздеу режимінде:

Ең жақын санды табу

қайда

B4 – біз жеңілдік іздеп отырған бірінші транзакциядағы тауар санының құны
$G$4:$H$8 – жеңілдіктер кестесіне сілтеме – «тақырыпсыз» және $ белгісімен бекітілген мекенжайлары бар.
2 — дисконт мәнін алғымыз келетін дисконт кестесіндегі бағанның реттік нөмірі
TRUE – «иттің» жерленген жері осы. Егер соңғы функция аргументі ретінде VPR көрсетіңіз ӨТІРІК (ЖАЛҒАН) немесе 0, содан кейін функция іздейді қатаң сәйкестік сан бағанында (және біздің жағдайда ол #N/A қатесін береді, себебі жеңілдік кестесінде 49 мәні жоқ). Бірақ оның орнына ӨТІРІК жазу TRUE (ШЫН) немесе 1, онда функция дәл емес, бірақ іздейді ең жақын ең кіші мәнін береді және бізге қажетті жеңілдік пайызын береді.

Бұл әдістің кемшілігі – жеңілдіктер кестесін бірінші баған бойынша өсу ретімен сұрыптау қажеттілігі. Егер мұндай сұрыптау болмаса (немесе ол кері тәртіпте жасалса), онда біздің формула жұмыс істемейді:

Ең жақын санды табу

Тиісінше, бұл тәсіл ең жақын ең кіші мәнді табу үшін ғана қолданылады. Ең жақын ең үлкенін табу керек болса, басқа тәсілді қолдану керек.

3-әдіс. INDEX және MATCH функциялары арқылы ең жақын ең үлкенін табу

Енді мәселемізге екінші жағынан қарайық. Біз әртүрлі қуаттылықтағы өнеркәсіптік сорғылардың бірнеше үлгілерін сатамыз делік. Сол жақтағы сату кестесі тұтынушыға қажетті қуатты көрсетеді. Біз ең жақын максималды немесе тең қуатты сорғыны таңдауымыз керек, бірақ жоба талап ететіннен кем емес.

VLOOKUP функциясы бұл жерде көмектеспейді, сондықтан оның аналогын – INDEX функцияларының топтамасын пайдалануға тура келеді. (ИНДЕКС) және КӨБІРЕК АШЫҚ (МАТЧ):

Ең жақын санды табу

Мұнда соңғы -1 аргументі бар MATCH функциясы ең жақын ең үлкен мәнді табу режимінде жұмыс істейді, ал INDEX функциясы содан кейін көрші бағанадан бізге қажет үлгі атауын шығарады.

4-әдіс. Жаңа функция VIEW (XLOOKUP)

Егер сізде барлық жаңартулары орнатылған Office 365 нұсқасы болса, VLOOKUP орнына (КӨРУ) оның аналогын – VIEW функциясын пайдалануға болады (ТҰРАҚТАУ), мен оны егжей-тегжейлі талдадым:

Ең жақын санды табу

Мұнда:

B4 – біз жеңілдік іздеп отырған өнім санының бастапқы мәні
$G$4:$G$8 – біз сіріңкелерді іздейтін диапазон
$H$4:$H$8 – жеңілдікті қайтарғыңыз келетін нәтижелер ауқымы
төртінші аргумент (-1) дәл сәйкестіктің орнына біз қалаған ең жақын ең кіші санды іздеуді қамтиды.

Бұл әдістің артықшылықтары жеңілдіктер кестесін сұрыптаудың қажеті жоқ және қажет болған жағдайда ең жақын ең кішкентай ғана емес, сонымен қатар ең жақын ең үлкен мәнді іздеу мүмкіндігі. Бұл жағдайда соңғы аргумент 1 болады.

Бірақ, өкінішке орай, бұл мүмкіндік барлығында әлі жоқ – тек Office 365-тің бақытты иелері.

5-әдіс. Power Query

Excel бағдарламасына арналған қуатты және толығымен тегін Power Query қондырмасымен әлі таныс болмасаңыз, сіз осындасыз. Егер сіз бұрыннан таныс болсаңыз, оны мәселемізді шешу үшін қолдануға тырысайық.

Алдымен дайындық жұмыстарын жүргізейік:

Бастапқы кестелерді пернелер тіркесімін пайдаланып динамикалық (ақылды) түрлендірейік Ctrl+T немесе команда Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу).
Түсінікті болу үшін оларға атау берейік. Сату и Жеңілдіктер қойындысы Конструктор (Дизайн).
Түймешікті пайдаланып кестелердің әрқайсысын Power Query қызметіне кезекпен жүктеңіз Кестеден/Ауқымнан қойындысы мәліметтер (Деректер — кестеден/ауқымнан). Excel бағдарламасының соңғы нұсқаларында бұл түйменің аты өзгертілді Жапырақтарымен (парақтан).
Егер кестелерде біздің мысалдағыдай («Тауарлар саны» және «... саны») сандары бар әртүрлі баған атаулары болса, олардың Power Query ішінде қайта аталып, бірдей аталуы керек.
Осыдан кейін Power Query өңдегішінің терезесіндегі пәрменді таңдау арқылы Excel бағдарламасына оралуға болады Негізгі бет — Жабу және жүктеу — Жабу және жүктеу… (Басты бет — Жабу&Жүктеу — Жабу&Жүктеу...) содан кейін опция Тек қосылым жасаңыз (Тек қосылым жасау).
Содан кейін ең қызықтысы басталады. Егер сізде Power Query бойынша тәжірибеңіз болса, менің ойымша, алдағы әдіс алдыңғы әдістегідей VLOOKUP қосу сұрауымен (біріктіру) осы екі кестені біріктіру бағытында болуы керек. Шын мәнінде, бізге қосу режимінде біріктіру керек болады, бұл бірінші көзқараста мүлдем анық емес. Excel қойындысында таңдаңыз Деректер – Деректерді алу – Сұрауларды біріктіру – Қосу (Деректер — Деректерді алу — Сұрауларды біріктіру — Қосу) содан кейін біздің үстелдер Сату и Жеңілдіктер пайда болған терезеде:
Басқаннан кейін OK біздің үстелдер бір-бірінің астына жабыстырылады. Осы кестелердегі тауарлардың саны көрсетілген бағандар бір-бірінің астына түсетінін ескеріңіз, өйткені. олардың аты бірдей:
Егер сатылымдар кестесіндегі жолдардың бастапқы тізбегі сіз үшін маңызды болса, онда барлық кейінгі түрлендірулерден кейін оны қалпына келтіруге болатындай, пәрменді пайдаланып кестемізге нөмірленген баған қосыңыз. Бағанды қосу – Индекс бағанасы (Баған қосу — индекс бағаны). Егер жолдар тізбегі сізге маңызды болмаса, онда бұл қадамды өткізіп жіберуге болады.
Енді кестенің тақырыбындағы ашылмалы тізімді пайдаланып, оны баған бойынша сұрыптаңыз сан Өсуі:
Және негізгі трюк: баған тақырыбын тінтуірдің оң жақ түймешігімен басыңыз Жеңілдік команда таңдау Толтыру – төмен (толтыру — төмен). бар бос ұяшықтар NULL алдыңғы жеңілдік мәндерімен автоматты түрде толтырылады:
Баған бойынша сұрыптау арқылы жолдардың бастапқы ретін қалпына келтіру қалады көрсеткіш (оны кейінірек қауіпсіз жоюға болады) және сүзгі арқылы қажет емес жолдардан құтылыңыз NULL баған бойынша транзакция коды: