Мазмұны
Біз жақында мақаланы ең пайдалы Excel функцияларының біріне арнадық VPR және оны дерекқордан жұмыс парағы ұяшығына қажетті ақпаратты шығару үшін қалай пайдалануға болатынын көрсетті. Функцияның екі пайдалану жағдайы бар екенін де айттық VPR және олардың біреуі ғана деректер қорының сұрауларымен айналысады. Бұл мақалада сіз функцияны пайдаланудың басқа аз белгілі әдісін білесіз VPR Excel бағдарламасында.
Егер сіз мұны әлі жасамаған болсаңыз, функция туралы соңғы мақаланы оқып шығыңыз VPR, өйткені төмендегі барлық ақпарат бірінші мақалада сипатталған принциптермен бұрыннан таныс екеніңізді болжайды.
Мәліметтер қорымен, функциялармен жұмыс істегенде VPR бірегей идентификатор беріледі, ол біз тапқымыз келетін ақпаратты анықтау үшін пайдаланылады (мысалы, өнім коды немесе тұтынушының сәйкестендіру нөмірі). Бұл бірегей код дерекқорда болуы керек, әйтпесе VPR қате туралы хабарлайды. Бұл мақалада біз функцияны пайдаланудың осы әдісін қарастырамыз VPRидентификатор дерекқорда мүлде болмаған кезде. Функция сияқты VPR шамамен режимге ауысады және бірдеңені тапқымыз келгенде бізге қандай деректерді беру керектігін таңдайды. Белгілі бір жағдайларда бұл дәл қажет.
Өмірден мысал. Тапсырманы қойдық
Осы мақаланы нақты өмірлік мысалмен суреттейік – сату көрсеткіштерінің кең ауқымына негізделген комиссияларды есептеу. Біз өте қарапайым нұсқадан бастаймыз, содан кейін мәселені шешудің жалғыз ұтымды шешімі функцияны пайдалану болғанша оны біртіндеп күрделендіреміз. VPR. Біздің ойдан шығарылған тапсырмамыздың бастапқы сценарийі келесідей: егер сатушы бір жылда 30000 30 доллардан астам сату жасаса, оның комиссиясы 20% құрайды. Әйтпесе, комиссия тек XNUMX% құрайды. Оны кесте түрінде көрсетейік:
Сатушы өзінің сату деректерін B1 ұяшығына енгізеді, ал B2 ұяшығындағы формула сатушы күте алатын комиссияның дұрыс мөлшерлемесін анықтайды. Өз кезегінде, алынған мөлшерлеме B3 ұяшығында сатушы алуы тиіс жалпы комиссияны есептеу үшін қолданылады (тек B1 және B2 ұяшықтарын көбейту).
Кестенің ең қызықты бөлігі B2 ұяшығында орналасқан – бұл комиссия ставкасын анықтау формуласы. Бұл формулада Excel функциясы бар IF (Егер). Бұл функциямен таныс емес оқырмандар үшін мен оның қалай жұмыс істейтінін түсіндіремін:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
жағдай кез келгенінің мәнін қабылдайтын функция аргументі болып табылады НАҒЫЗ КОД (ШЫН), немесе FALSE (ЖАЛҒАН). Жоғарыдағы мысалда B1 өрнегі
В1 В5-тен кем екені рас па?
Немесе басқаша айтуға болады:
Жылдағы сатылымның жалпы сомасы шекті мәннен аз екені рас па?
Бұл сұраққа жауап берсек ИӘ (TRUE), содан кейін функция қайтарылады мәні шын болса (ШЫН болса, мән). Біздің жағдайда бұл В6 ұяшығының мәні болады, яғни жалпы сатылымдар шекті мәннен төмен болған кездегі комиссиялық мөлшерлеме. Сұраққа жауап берсек ЖОҚ (FALSE) содан кейін қайтарады мәні жалған болса (мәні FALSE болса). Біздің жағдайда бұл В7 ұяшығының мәні, яғни жалпы сатылымдар шекті мәннен жоғары болған кездегі комиссиялық мөлшерлеме.
Көріп отырғаныңыздай, егер біз $20000 жалпы сатылымдарды алсақ, біз B2 ұяшығында 20% комиссиялық мөлшерлемені аламыз. Егер біз $40000 мәнін енгізсек, онда комиссия ставкасы 30% өзгереді:
Біздің үстел осылай жұмыс істейді.
Тапсырманы күрделендіреміз
Істі сәл қиындатып көрейік. Тағы бір шекті белгілейік: егер сатушы 40000 40 доллардан астам табыс тапса, онда комиссия ставкасы XNUMX% дейін артады:
Барлығы қарапайым және түсінікті болып көрінеді, бірақ B2 ұяшығындағы формуламыз айтарлықтай күрделене түседі. Егер сіз формулаға мұқият қарасаңыз, функцияның үшінші аргументі екенін көресіз IF (IF) басқа толыққанды функцияға айналды IF (Егер). Бұл конструкция функциялардың бір-біріне ұясы деп аталады. Excel бұл конструкцияларға қуана мүмкіндік береді және олар тіпті жұмыс істейді, бірақ оларды оқу және түсіну әлдеқайда қиын.
Біз техникалық мәліметтерді зерттемейміз – ол не үшін және қалай жұмыс істейді және кірістірілген функцияларды жазудың нюанстарына кірмейміз. Өйткені, бұл функцияға арналған мақала VPR, Excel бағдарламасына толық нұсқаулық емес.
Қандай жағдай болмасын, формула күрделене түседі! Сатудан 50 50000 доллардан асатын сатушылар үшін 60000% комиссиялық ставканың басқа нұсқасын енгізсек ше? Егер біреу 60 XNUMX доллардан астам сатқан болса, олар XNUMX% комиссия төлей ме?
Енді В2 ұяшығындағы формула қатесіз жазылса да, мүлдем оқылмайтын болып қалды. Менің ойымша, өз жобаларында ұя салудың 4 деңгейі бар формулаларды қолданғысы келетіндер аз. Оңай жолы бар шығар?!
Және мұндай жол бар! Функция бізге көмектеседі VPR.
Мәселені шешу үшін VLOOKUP функциясын қолданамыз
Үстеліміздің дизайнын сәл өзгертейік. Біз бірдей өрістер мен деректерді сақтаймыз, бірақ оларды жаңа, ықшам түрде реттейміз:
Біраз уақыт бөліп, жаңа үстелді тексеріңіз Бағалар кестесі алдыңғы шекті кестемен бірдей деректерді қамтиды.
Негізгі идея - функцияны пайдалану VPR кесте бойынша қалаған тарифтік мөлшерлемені анықтау Бағалар кестесі сату көлеміне байланысты. Сатушы кестедегі бес шектің біріне тең емес сомаға тауарды сата алатынын ескеріңіз. Мысалы, ол 34988 долларға сата алады, бірақ ондай сома жоқ. Функция қалай жұмыс істейтінін көрейік VPR мұндай жағдаймен күресуге болады.
VLOOKUP функциясын енгізу
B2 ұяшығын (біз формуланы енгізгіміз келетін жер) таңдап, табыңыз КӨРУ (VLOOKUP) Excel функциялары кітапханасында: формулалар (формулалар) > Функционалды кітапхана (Функциялар кітапханасы) > Іздеу және сілтеме (Сілтемелер мен массивтер).
Диалогтық терезе пайда болады Функция аргументтері (Функция аргументтері). Аргументтердің мәндерін бір-бірден бастап толтырамыз Іздеу_мәні (Іздеу_мәні). Бұл мысалда бұл B1 ұяшығынан сатылымның жалпы сомасы. Курсорды өріске қойыңыз Іздеу_мәні (Іздеу_мәні) және B1 ұяшығын таңдаңыз.
Әрі қарай, функцияларды көрсету керек VPRдеректерді қайдан іздеу керек. Біздің мысалда бұл кесте Бағалар кестесі. Курсорды өріске қойыңыз Кесте_массиві (Кесте) және бүкіл кестені таңдаңыз Бағалар кестесітақырыптарды қоспағанда.
Әрі қарай, формуланы пайдалану арқылы деректерді алу үшін қай бағанды көрсету керек. Бізді комиссияның мөлшерлемесі қызықтырады, ол кестенің екінші бағанында. Сондықтан, дәлел үшін Col_index_num (Баған_саны) 2 мәнін енгізіңіз.
Соңында біз соңғы дәлелді енгіземіз - Ауқымды_іздеу (Аралық_іздеу).
Маңызды: бұл функцияны қолданудың екі жолы арасындағы айырмашылықты жасайтын осы аргументтің қолданылуы VPR. Мәліметтер қорымен жұмыс істегенде аргумент Ауқымды_іздеу (аралықты_іздеу) әрқашан мәнге ие болуы керек FALSE (FALSE) дәл сәйкестікті іздеу үшін. Функцияны пайдалануымызда VPR, біз бұл өрісті бос қалдыруымыз керек немесе мән енгізуіміз керек НАҒЫЗ КОД (ШЫН). Бұл опцияны дұрыс таңдау өте маңызды.
Түсінікті болу үшін біз таныстырамыз НАҒЫЗ КОД (ШЫН) өрісте Ауқымды_іздеу (Аралық_іздеу). Дегенмен, өрісті бос қалдырсаңыз, бұл қате болмайды, өйткені НАҒЫЗ КОД оның әдепкі мәні:
Біз барлық параметрлерді толтырдық. Енді басамыз OK, ал Excel функциясы біз үшін формула жасайды VPR.
Егер біз сатылымның жалпы сомасына бірнеше түрлі мәндермен тәжірибе жасасақ, формуланың дұрыс жұмыс істейтініне көз жеткіземіз.
қорытынды
Функция болған кезде VPR мәліметтер қорымен, аргументпен жұмыс істейді Ауқымды_іздеу (аралықты_іздеу) қабылдау керек FALSE (ЖАЛҒАН). Және мән ретінде енгізілді Іздеу_мәні (Іздеу_мәні) дерекқорда болуы керек. Басқаша айтқанда, ол дәл сәйкестікті іздейді.
Осы мақалада қарастырған мысалда дәл сәйкестікті алудың қажеті жоқ. Бұл функция болған кезде VPR қажетті нәтижені қайтару үшін шамамен режимге ауысу керек.
Мысалға: Сату көлемі 34988 АҚШ доллары болатын сатушы үшін комиссиялық есепте қандай мөлшерлемені қолдану керектігін анықтағымыз келеді. Функция VPR бізге 30% мәнін қайтарады, бұл мүлдем дұрыс. Бірақ неге формула 30% немесе 20% емес, дәл 40% қамтитын жолды таңдады? Шамамен іздеу деген нені білдіреді? Түсінікті болайық.
Аргумент болған кезде Ауқымды_іздеу (аралық_іздеу) мәні бар НАҒЫЗ КОД (ШЫН) немесе түсірілген, функция VPR бірінші баған арқылы қайталанады және іздеу мәнінен аспайтын ең үлкен мәнді таңдайды.
Маңызды нүкте: Бұл схема жұмыс істеуі үшін кестенің бірінші бағанасы өсу ретімен сұрыпталуы керек.