03 01
Excel бағдарламасының VLOOKUP көмегімен деректерге шамамен сәйкес келуін табыңыз
VLOOKUP функциясы қалай жұмыс істейді
Excel-дің VLOOKUP функциясы , тік іздеуді білдіреді , деректер немесе дерекқор кестесінде орналасқан нақты ақпаратты іздеу үшін пайдаланылады.
VLOOKUP әдетте деректердің бір өрісін шығарылым ретінде қайтарады. Бұл қалай жасалады:
- Сіз VLOOKUP-ға қажетті деректерді іздейтін деректер кестесінің қай жолында немесе жазбасында екенін көрсететін атауды немесе іздеу_мәнін келтіресіз
- Сіз бағанның нөмірін - col_index_num ретінде белгілі - сіз іздеген деректер
- Функция деректер кестесінің бірінші бағанында lookup_value іздейді
- VLOOKUP кейін берілген баған нөмірін пайдаланып сол жазбаның басқа өрісінен іздейтін ақпаратты табады және қайтарады
Алдымен деректерді сұрыптау
Әрдайым қажет болмаса да, алдымен сұрыптау кілті үшін ауқымның бірінші бағанын пайдаланып, VLOOKUP артуы бойынша іздеуде болатын деректер ауқымын сұрыптау әдетте жақсы.
Егер деректер сұрыпталмаса, VLOOKUP дұрыс емес нәтижені қайтаруы мүмкін.
VLOOKUP функциясының синтаксисі және дәлелдері
Функцияның синтаксисі функцияның орналасуына қатысты және функцияның атауын, жақшалар мен дәлелдерді қамтиды .
VLOOKUP функциясының синтаксисі:
= VLOOKUP (іздеу_мәні, table_array, col_index_num, range_lookup)
іздеу параметрі - (жоғарыда көрсетілген суретте сатылатын мөлшер) сияқты іздеуге қажетті мәнді талап етеді
table_array - (міндетті) бұл VLOOKUP сіз кейінгі ақпаратты табу үшін іздейтін деректердің кестесі.
- Table_array дегенде кемінде екі баған болуы керек
- Бірінші бағанда әдеттегі іздеу_мәні бар
col_index_num - (қажет) табылған мәннің баған нөмірі.
- Нөмірлеу іздеу_күні бағанымен 1-бағаннан басталады
- Егер col_index_num table_array аргументінде таңдалған бағандар санынан үлкен сан болса, #REF! қате функциямен қайтарылады
range_lookup - (міндетті емес) ауқым өсу тәртібінде сұрыпталатын-жасалмайтынын көрсетеді.
- Бірінші бағандағы деректер сұрыптау пернесі ретінде пайдаланылады
- Логикалық мән - TRUE немесе FALSE тек қолайлы мәндер
- Егер есептен шығарылса, мән TRUE мәніне әдепкі бойынша орнатылады
- Егер TRUE мәніне қойылса немесе шығарылса және ауқымның бірінші бағаны өсу тәртібінде сұрыпталмаса, қате нәтиже пайда болуы мүмкін
- Егер TRUE мәніне орнатылса немесе түсірілмесе және іздеу нышанына сәйкес болмаса, өлшемі немесе мәні кішірек болатын ең жақын сәйкестік search_key
- Егер FALSE мәніне орнатылса, VLOOKUP іздеу нышаны үшін дәл сәйкес келеді. Бірнеше сәйкес мән болса, бірінші сәйкес мән қайтарылады
- Егер FALSE мәніне орнатылса және search_key үшін сәйкес мән табылмаса, # N / A қатесі функциямен қайтарылады
Мысал: Сатып алынған сандағы дисконттау мөлшерлемесін табыңыз
Жоғарыда көрсетілген суретте VLOOKUP функциясы сатып алынған заттардың санына байланысты өзгеретін дисконт мөлшерлемесін табу үшін қолданылады.
Мысалда 19 данадан сатып алуға жеңілдік 2% құрайды. Себебі Сандық саны бағанның мәндерін қамтиды. Нәтижесінде VLOOKUP дәл сәйкестік таба алмайды. Оның орнына, дұрыс дисконт ставкасын қайтару үшін шамаланған сәйкестікті табу керек.
Шамамен сәйкестіктерді табу үшін:
- table_array ішіндегі деректерді өсу тәртібімен сұрыптау;
- range_lookup дәлелін TRUE мәніне орнатыңыз
Мысалда VLOOKUP функциясын қамтитын келесі формула сатып алынатын тауарлардың мөлшеріне жеңілдік табу үшін пайдаланылады.
= VLOOKUP (C2, $ C $ 5: $ D 8,2, TRUE)
Бұл формула тек жұмыс парағы ұяшығына жазылуы мүмкін болса да, төменде көрсетілген қадамдармен бірге пайдаланылатын басқа параметр - оның дәлелдерін енгізу үшін функцияның тілқатысу терезесін пайдалану.
- Диалогтық терезені пайдалану функцияның дәлелдерін дұрыс енгізуді жиі жеңілдетеді.
VLOOKUP тілқатысу терезесін ашу
В2 ұяшығындағы суретте көрсетілген VLOOKUP функциясына кіру үшін қолданылатын қадамдар:
- Белсенді ұяшықты жасау үшін B2 ұяшығына басыңыз - VLOOKUP функциясының нәтижелері көрсетілетін орын
- Формулалар қойындысын нұқыңыз.
- Функция ашылмалы тізімін ашу үшін таспадан іздеу және анықтаманы таңдаңыз
- Функция диалогтық терезесін келтіру үшін тізімде VLOOKUP түймесін басыңыз
03 02
Excel бағдарламасының VLOOKUP функциясының дәлелдерін енгізу
Ұяшық сілтемелеріне нұсқау
VLOOKUP функциясының дәлелдері жоғарыдағы суретте көрсетілгендей диалогтық терезенің жеке жолдарына енгізіледі.
Аргументтер ретінде қолданылатын ұяшық сілтемелері дұрыс жолға терілуі мүмкін немесе тінтуір көрсеткісі бар ұяшықтардың қалаған ауқымын бөлектеуді көрсететін төмендегі қадамдарда көрсетілгендей, оларды диалогтық терезеге енгізу үшін пайдалануға болады .
Нұсқауды қолданудың артықшылықтары:
- Бұл теруден жылдамырақ;
- Дұрыс ұялы сілтемелерді енгізу қателіктері аз.
Аргументтермен салыстырмалы және абсолютті ұялы сілтемелерді қолдану
Бірдей деректер кестесінен әртүрлі ақпаратты қайтару үшін VLOOKUP-тің бірнеше көшірмесін пайдалану әдеттен тыс емес. Мұны оңай ету үшін, VLOOKUP жиі бір ұяшықтан екіншісіне көшіруге болады. Функцияларды басқа ұяшықтарға көшіргенде, функцияның жаңа орнын ескере отырып, алынған ұяшық сілтемелерінің дұрыс болуын қамтамасыз ету үшін қамқорлық қажет.
Жоғарыдағы суретте доллар белгісі ( $ ) ұяшық сілтемелерін table_array аргументі үшін абсолютті ұяшық сілтемелері екенін көрсететін қоршауды орындайды , яғни функция басқа ұяшыққа көшірілсе, олар өзгермейді. Бұл VLOOKUP-тің бірнеше көшірмесі ақпараттың дерек көзі ретінде сол деректер кестесіне сілтеме жасайтыны жөн.
Өз кезегінде , lookup_value үшін қолданылатын ұяшық сілтемесі доллар белгілерімен қоршалған емес, бұл салыстырмалы ұяшық сілтемесін жасайды. Салыстырмалы ұяшық сілтемелері сілтеме жасайтын деректерге қатысты жаңа орынды көрсету үшін көшіру кезінде өзгереді.
Функция дәлелдерін енгізу
- VLOOKUP тілқатысу терезесіндегі Lookup _value жолын нұқыңыз
- Бұл ұяшық сілтемесін search_key аргументі ретінде енгізу үшін жұмыс парағындағы C2 ұяшығын басыңыз
- Диалогтық терезенің Table_array жолын нұқыңыз
- Осы ауқымды Table_array аргументі ретінде енгізу үшін жұмыс парағындағы C5-ден D8 ұяшықтарын бөлектеңіз - кестенің тақырыптары қосылмаған
- Аралықты абсолюттік ұяшық сілтемелеріне өзгерту үшін пернетақтадағы F4 пернесін басыңыз
- Диалогтық терезенің Col_index_num жолына нұқыңыз
- Осы жолда 2- ні Col_index_num аргументі ретінде енгізіңіз , себебі дисконттау ставкалары Table_array аргументінің 2-бағанында орналасқан
- Диалогтық терезенің Range_lookup жолын нұқыңыз
- True сөзін Range_lookup аргументі ретінде теріңіз
- Диалогтық терезені жабу және жұмыс парағына қайту үшін пернетақтадағы Enter пернесін басыңыз
- Жауап 2% (сатып алынған мөлшерге арналған дисконт мөлшерлемесі) жұмыс парағының D2 ұяшығында пайда болуы керек
- D2 ұяшығын басқанда, жұмыс парағының үстіндегі формула жолында толық функция = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) пайда болады
Неліктен VLOOKUP нәтиже бойынша 2% қайтарылды
- Мысалда, Саны бағанында search_key мәні 19-ға сәйкес болмайды.
- Is_sorted аргументі TRUE мәніне орнатылғандықтан, VLOOKUP іздеу_кішінің мәніне шамамен сәйкес келеді.
- 19-дан 19-ға дейінгі іздеу кілтінің мәнінен азырақ өлшемдегі ең жақын мән 11 болып табылады.
- Осылайша, VLOOKUP 11-ден асатын қатардағы жеңілдік пайызын іздейді, нәтижесінде 2% дисконттау мөлшерлемесін қайтарады.
03 03
Excel VLOOKUP жұмыс істемейді: # N / A және #REF қателер
VLOOKUP қате туралы хабарлар
Төмендегі қате туралы хабарлар VLOOKUP-мен байланысты.
A # N / A («value not available») Қате көрсетіледі Егер:
- Аралық дәлелдің бірінші бағанында іздеу параметрі табылмады
- Table_array аргументі дұрыс емес. Мысалы, аргумент ауқымның сол жағындағы бос бағандарды қамтуы мүмкін
- Range_lookup аргументі FALSE мәніне орнатылады және search_key дәлелінің дәл сәйкестігі ауқымның бірінші бағанында табылмайды
- Range_lookup аргументі TRUE мәніне орнатылған және ауқымның бірінші бағанындағы барлық мәндер search_key
#REF! («диапазоннан анықтама») Қате көрсетіледі Егер:
- Col_index_num дәлелі Table_array бағандарының санынан үлкен