Мундариҷа
Мо ба наздикӣ мақолаеро ба яке аз функсияҳои муфидтарини Excel бахшида кардем VPR ва нишон дод, ки чӣ гуна онро барои аз пойгоҳи додаҳо ба ячейкаи варақи корӣ истихроҷ кардан мумкин аст. Мо инчунин қайд кардем, ки ду ҳолати истифода барои функсия вуҷуд дорад VPR ва танҳо яке аз онҳо бо дархостҳои пойгоҳи додаҳо сарукор дорад. Дар ин мақола, шумо роҳи дигари камтар маълуми истифодаи функсияро меомӯзед VPR дар Excel.
Агар шумо ин корро накарда бошед, пас боварӣ ҳосил кунед, ки мақолаи охиринро дар бораи функсия хонед VPR, зеро ҳамаи маълумоти дар поён овардашуда тахмин мекунанд, ки шумо аллакай бо принсипҳои дар мақолаи аввал тавсифшуда шинос ҳастед.
Ҳангоми кор бо базаҳои маълумот, функсияҳо VPR идентификатори нодир мегузарад, ки он барои муайян кардани маълумоте, ки мо мехоҳем пайдо кунем (масалан, рамзи маҳсулот ё рақами мушаххаси муштарӣ) истифода мешавад. Ин рамзи беназир бояд дар базаи маълумот мавҷуд бошад, дар акси ҳол VPR дар бораи хато хабар медиҳад. Дар ин мақола мо ин тарзи истифодаи функсияро дида мебароем VPRвақте ки id дар базаи маълумот умуман вуҷуд надорад. Гӯё функсия VPR ба реҷаи тахминӣ гузаред ва интихоб мекунад, ки ҳангоми пайдо кардани чизе ба мо кадом маълумотро пешниҳод кунад. Дар баъзе ҳолатҳо, ин маҳз ҳамон чизест, ки лозим аст.
Як мисол аз хаёт. Мо вазифа гузоштем
Биёед ин мақоларо бо мисоли воқеӣ нишон диҳем - ҳисоб кардани комиссияҳо дар асоси доираи васеи ченакҳои фурӯш. Мо аз як варианти хеле содда оғоз мекунем ва сипас тадриҷан онро мураккаб мекунем, то он даме ки роҳи ягонаи ҳалли оқилонаи масъала истифодаи функсия аст. VPR. Сенарияи ибтидоии вазифаи тахайюлии мо чунин аст: агар фурӯшанда дар як сол зиёда аз 30000 доллари амрикоиро фурӯшад, пас комиссияи ӯ 30% аст. Дар акси ҳол, комиссия танҳо 20% аст. Биёед онро дар шакли ҷадвал гузорем:
Фурӯшанда маълумоти фурӯши худро ба ячейкаи B1 ворид мекунад ва формула дар ячейкаи B2 меъёри дурусти комиссияро, ки фурӯшанда интизор аст, муайян мекунад. Дар навбати худ, меъёри натиҷавӣ дар ячейкаи B3 барои ҳисоб кардани комиссияи умумии фурӯшанда бояд истифода шавад (танҳо бо зарб задани ҳуҷайраҳои B1 ва B2).
Қисмати ҷолибтарини ҷадвал дар ҳуҷайраи B2 ҷойгир аст - ин формулаи муайян кардани меъёри комиссия мебошад. Ин формула дорои функсияи Excel мебошад IF (АГАР). Барои он хонандагоне, ки бо ин функсия шинос нестанд, ман шарҳ медиҳам, ки он чӣ гуна кор мекунад:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
шароит далели функсияест, ки арзиши якеро мегирад РАМЗИ ҳақиқӣ (РОҲ), ё козиб (ДУРУГ). Дар мисоли боло ифодаи B1
Оё дуруст аст, ки B1 аз B5 камтар аст?
Ё шумо метавонед онро ба таври дигар бигӯед:
Оё дуруст аст, ки ҳаҷми умумии фурӯш дар як сол аз арзиши ҳадди аксар камтар аст?
Агар ба ин савол чавоб дихем ҲА (TRUE), пас функсия бармегардад арзиши агар рост бошад (арзиш агар TRUE бошад). Дар ҳолати мо, ин арзиши ячейкаи B6 хоҳад буд, яъне меъёри комиссия, вақте ки фурӯшҳои умумӣ аз ҳадди поёнтар аст. Агар ба савол чавоб дихем Не (FALSE) пас бармегардад арзиш агар дурӯғ бошад (арзиш агар FALSE). Дар ҳолати мо, ин арзиши ячейкаи B7 аст, яъне меъёри комиссионӣ, вақте ки фурӯши умумӣ аз ҳадди боло аст.
Тавре ки шумо мебинед, агар мо фурӯши умумии $20000-ро гирем, мо дар ячейкаи B2 20% комиссия мегирем. Агар мо арзиши $40000 ворид кунем, пас меъёри комиссия 30% тағир меёбад:
Ин аст, ки мизи мо кор мекунад.
Мо вазифаро душвор мегардонем
Биёед корро каме душвортар кунем. Биёед як ҳадди дигарро муқаррар кунем: агар фурӯшанда зиёда аз 40000 доллар кор кунад, пас меъёри комиссия то 40% меафзояд:
Ҳама чиз оддӣ ва равшан ба назар мерасад, аммо формулаи мо дар ҳуҷайраи B2 ба таври назаррас мураккабтар мешавад. Агар шумо бодиққат ба формула назар кунед, шумо мебинед, ки аргументи сеюми функсия IF (АГ) ба вазифаи дигари мукаммал табдил ёфт IF (АГАР). Ин сохтмон лона гузоштани функсияҳо ба ҳамдигар номида мешавад. Excel бо хушнудӣ ба ин сохтмонҳо иҷозат медиҳад ва онҳо ҳатто кор мекунанд, аммо хондан ва фаҳмидани онҳо хеле душвортар аст.
Мо ба тафсилоти техникӣ - чаро ва чӣ гуна он кор карданро омӯхта наметавонем ва мо ба нозукиҳои навиштани функсияҳои лона дохил намешавем. Баъд аз ҳама, ин мақолаест, ки ба функсия бахшида шудааст VPR, дастури пурраи Excel нест.
Дар ҳар сурат, формула мураккабтар мешавад! Чӣ мешавад, агар мо як варианти дигари меъёри комиссияи 50% барои фурӯшандагоне, ки дар фурӯш зиёда аз 50000 доллар кор мекунанд, ҷорӣ кунем. Ва агар касе зиёда аз 60000 доллар фурӯхта бошад, оё онҳо 60% комиссияро пардохт мекунанд?
Акнун формулаи ячейкаи В2, ҳатто агар он бе хато навишта шуда бошад ҳам, комилан хонданӣ шудааст. Ман фикр мекунам, ки шумораи ками онҳое ҳастанд, ки мехоҳанд формулаҳои дорои 4 сатҳи лонаро дар лоиҳаҳои худ истифода баранд. Бояд роҳи осонтар бошад?!
Ва чунин роҳ вуҷуд дорад! Функсия ба мо кӯмак мекунад VPR.
Мо функсияи VLOOKUP-ро барои ҳалли мушкилот истифода мебарем
Биёед тарҳи мизи худро каме дигар кунем. Мо ҳама майдонҳо ва маълумотҳои якхеларо нигоҳ медорем, аммо онҳоро ба таври нав ва паймонтар ҷойгир мекунем:
Як лаҳза ва боварӣ ҳосил кунед, ки мизи нав Ҷадвали нархгузорӣ дар бар мегирад, ҳамон маълумоте, ки ҷадвали ҳадди гузашта.
Идеяи асосӣ истифодаи функсия аст VPR мувофики чадвал ставкаи тарифии дилхохро муайян кунад Ҷадвали нархгузорӣ вобаста ба хачми фуруш. Лутфан таваҷҷӯҳ намоед, ки фурӯшанда метавонад молро ба маблағи ба яке аз панҷ ҳадди ниҳоии ҷадвал баробар набошад, фурӯшад. Масалан, ӯ метавонист ба 34988 доллар фурӯшад, аммо чунин маблағ вуҷуд надорад. Биёед бубинем, ки функсия чӣ гуна аст VPR бо чунин вазъият мубориза бурда метавонад.
Ворид кардани функсияи VLOOKUP
Чашмаки B2-ро интихоб кунед (дар он ҷое ки мо формулаи худро гузорем) ва пайдо кунед ИНТИЗОР (VLOOKUP) дар Китобхонаи Функсияҳои Excel: Формула (формулаҳо) > Китобхонаи функсионалӣ (Китобхонаи функсия) > Ҷустуҷӯ ва маълумотнома (Иқтибосҳо ва массивҳо).
Қуттии муколама пайдо мешавад Далелҳои функсия (Аргументҳои функсия). Мо арзишҳои далелҳоро як ба як пур мекунем, аз он сар карда Ҷустуҷӯи_арзиш (Ҷустуҷӯи_арзиш). Дар ин мисол, ин маблағи умумии фурӯш аз ячейкаи B1 аст. Курсорро дар майдон гузоред Ҷустуҷӯи_арзиш (Lookup_value) ва чашмаки B1-ро интихоб кунед.
Баъдан, шумо бояд функсияҳоро муайян кунед VPRдар куҷо маълумотро ҷустуҷӯ кардан мумкин аст. Дар мисоли мо ин ҷадвал аст Ҷадвали нархгузорӣ. Курсорро дар майдон гузоред Ҷадвали_массив (Ҷадвал) ва тамоми ҷадвалро интихоб кунед Ҷадвали нархгузорӣба истиснои сарлавҳаҳо.
Баъдан, мо бояд муайян кунем, ки кадом сутунро аз формулаи мо маълумот гирифтан лозим аст. Мо ба меъёри комиссия, ки дар сутуни дуюми ҷадвал аст, таваҷҷӯҳ дорем. Аз ин рӯ, барои баҳс Рақами_индекс (Сутуни_рақам) арзиши 2-ро ворид кунед.
Ва ниҳоят, мо далели охиринро пешниҳод мекунем - Ҷустуҷӯи_диапазон (Ҷустуҷӯи фосилавӣ).
Муҳим: ин истифодаи ин далел аст, ки фарқияти байни ду роҳи татбиқи функсияро ба вуҷуд меорад VPR. Ҳангоми кор бо пойгоҳи додаҳо, аргумент Ҷустуҷӯи_диапазон (range_lookup) бояд ҳамеша арзиш дошта бошад козиб (FALSE) барои ҷустуҷӯи мувофиқати дақиқ. Дар истифодаи мо аз функсия VPR, мо бояд ин майдонро холӣ гузорем ё арзиш ворид кунем РАМЗИ ҳақиқӣ (РОСТ). Интихоби дурусти ин вариант хеле муҳим аст.
Барои равшантар кардани он, мо муаррифӣ мекунем РАМЗИ ҳақиқӣ (РОСТ) дар майдон Ҷустуҷӯи_диапазон (Ҷустуҷӯи фосилавӣ). Гарчанде, агар шумо майдонро холӣ гузоред, ин хатогӣ нахоҳад буд, зеро РАМЗИ ҳақиқӣ арзиши пешфарзи он аст:
Мо ҳама параметрҳоро пур кардем. Акнун мо пахш мекунем OK, ва Excel барои мо формулаеро бо функсия эҷод мекунад VPR.
Агар мо бо якчанд арзишҳои гуногун барои маблағи умумии фурӯш озмоиш кунем, мо боварӣ ҳосил мекунем, ки формула дуруст кор мекунад.
хулоса
Вақте ки функсия VPR бо базахои маълумотхо кор мекунад, аргумент Ҷустуҷӯи_диапазон (диапазони_ҷустуҷӯ) бояд қабул кунад козиб (ДУРУГ). Ва арзиши воридшуда ҳамчун Ҷустуҷӯи_арзиш (Lookup_value) бояд дар базаи маълумот мавҷуд бошад. Ба ибораи дигар, он мувофиқати дақиқро меҷӯяд.
Дар мисоле, ки мо дар ин мақола дидаем, зарурати ба даст овардани мувофиқати дақиқ нест. Ин ҳолат аст, вақте ки функсия VPR барои баргардонидани натиҷаи дилхоҳ бояд ба ҳолати тахминӣ гузарад.
Барои намуна: Мо мехоҳем муайян кунем, ки кадом меъёрро дар ҳисобкунии комиссия барои фурӯшанда бо ҳаҷми фурӯш $34988 истифода барем. Функсия VPR ба мо арзиши 30% -ро бармегардонад, ки комилан дуруст аст. Аммо чаро формула сатри дорои 30%-ро интихоб кардааст, на 20% ё 40%? Ҷустуҷӯи тахминӣ чӣ маъно дорад? Биёед равшан бошем.
Вакте ки бахс Ҷустуҷӯи_диапазон (interval_lookup) арзиш дорад РАМЗИ ҳақиқӣ (TRUE) ё партофташуда, функсия VPR сутуни якумро такрор мекунад ва бузургтарин арзишеро интихоб мекунад, ки аз арзиши ҷустуҷӯ зиёд нест.
Нуқтаи муҳим: Барои он ки ин схема кор кунад, сутуни якуми ҷадвал бояд бо тартиби афзоиш мураттаб карда шавад.