Ҷустуҷӯи рақами наздиктарин

Мундариҷа

Дар амал аксар вақт ҳолатҳое рӯй медиҳанд, ки ба ман ва шумо лозим меояд, ки арзиши наздиктаринро дар маҷмӯи (ҷадвал) нисбат ба рақами додашуда пайдо кунем. Он метавонад, масалан:

Ҳисоб кардани тахфиф вобаста ба ҳаҷм.
Хисоб кардани маблаги мукофот вобаста ба ичрои план.
Ҳисоб кардани тарифҳои интиқол вобаста ба масофа.
Интихоби зарфҳои мувофиқ барои мол ва ғайра.

Ғайр аз он, вобаста ба вазъият, яклухткунӣ метавонад ҳам боло ва ҳам поён талаб карда шавад.

Якчанд роҳҳо вуҷуд доранд - возеҳ ва на он қадар равшан - барои ҳалли чунин мушкилот. Биёед онҳоро пайдарпай дида бароем.

Барои оғоз, биёед як молрасонеро тасаввур кунем, ки дар яклухт тахфиф медиҳад ва фоизи тахфиф аз миқдори моли харидашуда вобаста аст. Масалан, ҳангоми харидани зиёда аз 5 дона, 2% тахфиф дода мешавад ва ҳангоми харидани аз 20 дона - аллакай 6% ва ғайра.

Ҳангоми ворид кардани миқдори моли харидашуда фоизи тахфифро чӣ гуна зуд ва зебо ҳисоб кардан мумкин аст?

Усули 1: IF-ҳои дохилшуда

Усул аз силсилаи «Чӣ фикр кардан лозим аст - ба шумо ҷаҳидан лозим аст!». Истифодаи функсияҳои лона IF (АГАР) пай дар пай тафтиш кунед, ки оё арзиши ячейка ба ҳар як фосила рост меояд ва барои диапазони мувофиқ тахфиф нишон диҳед. Аммо формула дар ин ҳолат метавонад хеле душвор бошад:

Ҷустуҷӯи рақами наздиктарин

Ман фикр мекунам, маълум аст, ки ислоҳи чунин "лухтаки ҳаюло" ё кӯшиши илова кардани якчанд шарти нав ба он пас аз чанд вақт шавқовар аст.

Илова бар ин, Microsoft Excel барои функсияи IF маҳдудияти лона дорад - 7 маротиба дар версияҳои кӯҳна ва 64 маротиба дар версияҳои нав. Чӣ мешавад, агар ба шумо бештар лозим бошад?

Усули 2. VLOOKUP бо намуди фосилавӣ

Ин усул хеле зичтар аст. Барои ҳисоб кардани фоизи тахфиф, функсияи афсонавиро истифода баред VPR (ИНТИЗОР) дар ҳолати тахминии ҷустуҷӯ:

Ҷустуҷӯи рақами наздиктарин

ки дар

B4 – арзиши миќдори молњо дар муомилоти аввал, ки барои он мо тахфиф мељўем
$ G $ 4: $ H $ 8 - пайванд ба ҷадвали тахфиф - бе "сарлавҳа" ва суроғаҳо бо аломати $.
2 — рақами тартибии сутуни ҷадвали тахфиф, ки мо мехоҳем арзиши тахфифро гирем
ҲАҚИҚӢ — ин чо «саг»-ро дафн мекунанд. Агар ҳамчун далели охирини функсия VPR муайян кунед ДУРУГ ГУФТАН (ДУРУГ) ё 0, пас функсия ҷустуҷӯ мекунад мувофиқати қатъӣ дар сутуни миқдор (ва дар ҳолати мо он хатои #N/A медиҳад, зеро дар ҷадвали тахфиф арзиши 49 вуҷуд надорад). Аммо агар ба ҷои ДУРУГ ГУФТАН навиштан ҲАҚИҚӢ (ДУруст) ё 1, он гоҳ функсия на барои дақиқ, балки ҷустуҷӯ хоҳад кард наздиктарин хурдтарин арзиш дорад ва ба мо фоизи тахфифи лозимиро медиҳад.

Камбудии ин усул зарурати мураттаб кардани ҷадвали тахфиф бо тартиби афзоиш аз рӯи сутуни якум мебошад. Агар чунин навъбандӣ набошад (ё он бо тартиби баръакс анҷом дода шавад), формулаи мо кор намекунад:

Ҷустуҷӯи рақами наздиктарин

Мутаносибан, ин равишро танҳо барои дарёфти арзиши наздиктарини хурдтарин истифода бурдан мумкин аст. Агар ба шумо лозим аст, ки бузургтарини наздиктаринро пайдо кунед, пас шумо бояд равиши дигарро истифода баред.

Усули 3. Ҷустуҷӯи калонтарин наздиктарин бо истифода аз функсияҳои INDEX ва MATCH

Акнун биёед ба мушкили худ аз дигар тараф назар андозем. Фарз мекунем, ки мо якчанд модели насосҳои саноатии иқтидорҳои гуногунро мефурӯшем. Ҷадвали фурӯш дар тарафи чап қувваи талабкардаи муштариро нишон медиҳад. Ба мо лозим аст, ки насосро бо ҳадди наздиктарин ё баробар қудрат интихоб кунем, аммо на камтар аз он чизе, ки лоиҳа талаб мекунад.

Функсияи VLOOKUP дар ин ҷо кӯмак намекунад, бинобар ин шумо бояд аналоги он - як қатор функсияҳои INDEX -ро истифода баред (ИНДЕКС) ва БЕШТАР ФОШ (МАТЧ):

Ҷустуҷӯи рақами наздиктарин

Дар ин ҷо, функсияи MATCH бо аргументи охирин -1 дар реҷаи ёфтани арзиши наздиктарин бузургтарин кор мекунад ва функсияи 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 шинос набошед, пас шумо дар ин ҷо ҳастед. Агар шумо аллакай шинос бошед, биёед кӯшиш кунем, ки онро барои ҳалли мушкилоти худ истифода барем.

Биёед аввал баъзе корҳои омодагиро анҷом диҳем:

Биёед бо истифода аз миёнабурҳои клавиатура ҷадвалҳои сарчашмаи худро ба динамикӣ (интеллектуалӣ) табдил диҳем Ctrl+T ё даста Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал).
Барои равшанӣ, биёед ба онҳо ном гузорем. Sales и тахфиф ҷадвали Конструктор (Тарроҳӣ).
Бо истифода аз тугма ҳар як ҷадвалро бо навбат ба Power Query бор кунед Аз ҷадвал/диапазон ҷадвали Маълумот (Маълумот - Аз ҷадвал/диапазон). Дар версияҳои охирини Excel, ин тугма ба ном иваз карда шудааст Бо баргҳо (Аз варақ).
Агар ҷадвалҳо номҳои сутунҳои гуногун бо миқдор дошта бошанд, чунон ки дар мисоли мо («Миқдори молҳо» ва «Миқдори аз ...»), пас онҳо бояд дар Power Query номгузорӣ карда шаванд ва ҳамон ном дода шаванд.
Пас аз ин, шумо метавонед бо интихоби фармон дар равзанаи муҳаррири Power Query ба Excel баргардед Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед… (Хона — Пӯшед ва бор кунед — Пӯшед ва бор кунед…) ва баъд вариант Танҳо пайвастшавӣ эҷод кунед (Танҳо пайваст эҷод кунед).
Пас аз ҳама ҷолибтарин оғоз меёбад. Агар шумо дар Power Query таҷриба дошта бошед, пас ман фикр мекунам, ки хати минбаъдаи фикр бояд дар самти муттаҳид кардани ин ду ҷадвал бо дархости ҳамроҳ (якҷоя) a la VLOOKUP бошад, чунон ки дар усули қаблӣ буд. Дар асл, ба мо лозим меояд, ки дар ҳолати илова якҷоя шавем, ки ин дар назари аввал аён нест. Дар ҷадвали Excel интихоб кунед Маълумот - Гирифтани маълумот - Якҷоя кардани дархостҳо - Илова (Маълумот - Гирифтани маълумот - Якҷоя кардани дархостҳо - Замима) ва баъд мизҳои мо Sales и тахфиф дар равзанаи пайдошуда:
Баъд аз пахш кардан OK мизхои мо ба як бутун — дар зери хамдигар часпида мешаванд. Лутфан қайд кунед, ки сутунҳои миқдори молҳои ин ҷадвалҳо зери ҳамдигар меафтанд, зеро. онҳо як ном доранд:
Агар пайдарпайии аслии сатрҳои ҷадвали фурӯш барои шумо муҳим бошад, пас шумо метавонед онро пас аз ҳама тағиротҳои минбаъда барқарор кунед, бо истифода аз фармон ба ҷадвали мо сутуни рақамдорро илова кунед. Илова кардани сутун - Сутуни индекс (Иловаи сутун — Сутуни индекс). Агар пайдарпаии сатрҳо барои шумо аҳамият надиҳад, шумо метавонед ин қадамро гузаред.
Акнун, бо истифода аз рӯйхати афтанда дар сарлавҳаи ҷадвал, онро аз рӯи сутун ҷудо кунед шумора Ба боло:
Ва ҳиллаи асосӣ: тугмаи рости мушро дар сарлавҳаи сутун пахш кунед тахфифи даста интихоб кунед Пур кардан - Поён (Пур — Поён). Ҳуҷайраҳои холӣ бо ночиз ба таври худкор бо арзишҳои пештараи тахфиф пур карда мешавад:
Барои барқарор кардани пайдарпаии аслии сатрҳо бо ҷудокунӣ аз рӯи сутун боқӣ мемонад Индекс (шумо метавонед онро баъдтар бехатар нест кунед) ва бо филтр аз хатҳои нолозим халос шавед ночиз аз рӯи сутун Рамзи транзаксия: