Деректерді іздеу және салыстыру үшін ВПР «VLOOKUP» функциясын қолдану

№4 тәжірибелік жұмыс

Тақырыбы: Деректерді іздеу және салыстыру үшін ВПР «VLOOKUP» функциясын қолдану

Жұмыс мақсаты: MS Excel-де ВПР (VLOOKUP) функциясымен жұмыс істеу (Функция шебері – «Сілтемелер мен массивтер» санаты) бойынша тәжірибелік дағдыларды қалыптастыру 

Әдістемелік нұсқаулар:

1. ВПР немесе Vlookup функциясы.
2. ВПР (VLOOKUP) функциясының қызметі.
3. ВПР (VLOOKUP) функциясының жалпы формуласы.
4. ВПР (VLOOKUP) функциясының көшірмелері.
5. ВПР (VLOOKUP) функциясын қолдану кезіндегі қателіктер.

Қысқаша теориялық мәліметтер

ВПР немесе Vlookup функциясы 

ВПР немесе VLOOKUP функциясы – Excel бағдарламасында іздеу функциясы, ол сұраныс бойынша бір кестеден кілт сөздерді іздейді және қажетті ақпаратты екіншісіне тасымалдайды; үлкен деректер массивімен жұмыс істеу үшін қолайлы.

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

ВПР (VLOOKUP) функциясының қызметі

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

ВПР (VLOOKUP) функциясының жалпы формуласы

=VLOOKUP (қажетті мән; іздеуге арналған аймақ; қажетті мән бар баған нөмірі; 0 (егер жалған болса) немесе 1 (егер ақиқат болса).

Нақты дәл мән рұқсат етілген кезде «Жалған» мәні, ал деректер шамамен қажет болса, «Ақиқат» мәні қойылады. Бұл тек VLOOKUP функциясына қатысты. Басқа жағдайларда «Ақиқат» мәні шарттың орындалуын, ал «Жалған» мәні орындалмауын білдіреді.

ВПР (VLOOKUP) функциясының көшірмелері

ВПР (VLOOKUP) функциясы өте күрделі, бірақ оны Excel бағдарламасындағы басқа формулалармен алмастыруға болады:

• ПРОСМОТР (LOOKUP) жаңа Google-кестелерде қол жетімді және бағанды немесе жолды қажетті мәнмен табуға көмектеседі;
• ИНДЕКС (INDEX) және ПОИСКПОЗ (MATCH) – бір уақытта пайдалану кезінде олар сұраныс орнын графтардан іздейді және басқа ұяшыққа орын ауыстырады;
• СУММЕСЛИ (SUMIF) – таңдалған диапазонды қорытындылайды. Мысалы, егер тек 360000 теңгеден жоғары жалақыны қосу керек болса.

ВПР (VLOOKUP) функциясын қолдану кезіндегі қателіктер

ВПР (VLOOKUP) функциясы – Excel бағдарламасындағы ең күрделі формуланың бірі, сондықтан қателіктер кетуі мүмкін. Кейбір дәлсіздіктерді бірден байқауға болады – бағдарлама есептеу жүргізбейді; қате көрінбейді – нәтиже бар, бірақ дұрыс емес.

Қолданушының типтік қателіктерін талдайық:

• #Н/Д – сілтеме дұрыс жасалмаған кездегі жалпы мән. Дұрыс толтырылмаған мәні бар терезеге сілтеме құрдыңыз немесе ұяшық бос және сілтемеде деректер болуы керек.
• Формула жұмыс істеді, бірақ оны қолданған кезде қате мәндер пайда болады. Бұл ең қауіпті қате, өйткені үлкен аналитикалық Excel кестелерінде оны байқау қиын. Бұл сілтемені не кестені бекітпеген кезде болады. Сілтемелер F4 пернесі арқылы «$» белгісімен бекітіледі.
• ВПР функциясы нәтиже береді, бірақ тексеру кезінде олар дәл емес. Содан кейін соңғы мәнде не тұрғанын тексеріңіз: 0 немесе 1 (жалған не ақиқат).
• #ЗНАЧ – жолдағы мән тым ұзын болған кезде ештеңе шықпайды.

Тәжірибелік жұмысты орындау тәртібі мен әдістемесі:

• Тәжірибелік жұмыстың барлық тапсырмаларын Microsoft Excel кестелік процессорын қолдана отырып шығару қажет. Әр тапсырма Excel жұмыс кітабының жекелеген парағында ұсынылуы керек.

4.1-мысал. Прайс парақтан тауарлар бағаларын Тапсырыс кестесіне автоматты түрде ауыстырып, содан кейін тауардың атауына назар аудара отырып, құнын есептеуге болатындай етіп ВПР функциясын қолданыңыз (4.1-сурет).

[pic 1]

4.1-сурет. Тапсырыс кестесі мен Прайс парағы

Шешімі: Excel функциялар жиынтығында, Сілтемелер мен массивтер (Ссылки и массивы) санатында (Lookup and reference) ВПР (VLOOKUP) функциясы берілген мәнді іздейді (мысалда бұл «алма» сөзі), көрсетілген кестенің сол жақ бағанында (прайс-парақ) жоғарыдан төменге қарай жылжып, оны тауып, көрші ұяшықтың мазмұнын береді (23).

Схемалық түрде бұл функцияның жұмысын келесідей елестетуге болады (4.2-сурет):

[pic 2]

4.2-сурет. Тапсырыс кестесі мен Прайс парағы кестелеріндегі байланыс