VLOOKUP функциясы

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

VLOOKUP құпиясы - сіз іздеген мән (қызметкердің тегі) сіз тапқыңыз келген мәннің (қызметкерлің телефон нөмірі) сол жағында тұратындай етіп ұйымдастыруында.

Синтаксис

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Мысалы:

  • =VLOOKUP(105,A2:C7,2,TRUE)

  • =VLOOKUP("Абенов",B2:E7,2,FALSE)

Аргумент аты

Сипаттамасы

lookup_value    (міндетті)

Ізделуі қажет мән. Іздегіңіз келген мән кесте массивінде көрсетілетін ұяшықтар ауқымының бірінші бағанында болуы қажет.

Мысалы, егер кесте массиві B2:D7 ұяшықтарынан өтетін болса, онда сіздің lookup_value B бағанында болуы қажет. Төмендегі сызбаны қараңыз. Lookup_value - мән немесе ұяшық сілтемесі болуы мүмкін.

кесте_массиві    (міндетті)

VLOOKUP іздейтін_мән мәнін іздейтін және мәнді қайтаратын ұяшықтар ауқымы.

Ұяшық ауқымындағы бірінші бағанында іздейтін_мән болуы қажет (мысалы, төменде көрсетілген суреттегі қызметкердің тегі.) Ұяшық ауқымында сіз тапқыңыз келетін қайтару мәні де болуы қажет (мысалы, төменде көрсетілген сызбадағы қызметкердің аты).

Жұмыс парағындағы ауқымдарды қалай таңдау қажеттігі туралы ақпарат алыңыз.

col_index_num    (міндетті)

Қайтару мәнінен тұратын баған нөмірі (кесте-массиві параметрінің сол жақтағы 1-ден бастау).

аралық_бойынша_қарап_шығу   (міндетті емес)

VLOOKUP функциясы не дәл сәйкестікті, не жуық сәйкестікті табатынын анықтайтын логикалық мән:

  • ШЫН кестедегі бірінші бағанды сан бойынша немесе алфавит бойынша сұрыптайды және содан кейін ең жақын мәнді іздейді. Егер белгілі бір әдісті көрсетпеген болсаңыз, бұл әдепкі әдіс болады.

  • ЖАЛҒАН бірінші бағандағы нақты мәнді іздейді.

Төмендегі суретте =VLOOKUP("Akers",B2:D5,2,FALSE) қызметімен Азат мәнін қайтару үшін жұмыс парағыңызды қалай баптау қажеттігі көрсетілген.

Excel бағдарламасында VLOOKUP формуласын жасауға қажетті мән мен көрсеткі мысалы

Мысалдар

Бұл мысалдарды Excel бағдарламасында пайдалану үшін, төмендегі кестедегі деректерді көшіріп, жаңа жұмыс парағының А1 ұяшығына қойыңыз.

Идентификатор

Тегі

Аты

Лауазымы

Туған күні

101

Өмірбаева

Жанна

Сауда өкілі

12/8/1968

102

Абенов

Марат

Директордың сату жөніндегі орынбасары

2/19/1952

103

Танекеева

Айсұлу

Сауда өкілі

8/30/1963

104

Тұрсынов

Азат

Сауда өкілі

9/19/1958

105

Болатов

Олжас

Сату бөлімінің меңгерушісі

3/4/1955

106

Нұрбеков

Серік

Сауда өкілі

7/2/1963

Формула

Сипаттама

=VLOOKUP(«Абенов»,B2:E7,2,FALSE)

table_array B2:E7 ұяшығының бірінші бағанындағы (B бағаны) Абенов мәнін іздейді және table_array ұяшығының екінші бағанында (С бағаны) табылған Марат мәнін қайтарады. range_lookup FALSE дәл сәйкестікті береді.

=VLOOKUP(102,A2:C7,2,FALSE)

А бағанындағы іздейтін_мән102 үшін тегінің дәл сәйкестігін іздейді. Әбенұлы шығады. Егер іздейтін_мән - 105 болса, Болатұлы шығады.

=IF(VLOOKUP(103,A1:E7,2,FALSE)=«Нұрбеков»,«Орналастырылды»,«Табылмады»)

Идентификаторлық нөмірі 103 болатын қызметкердің тегі Нұрбеков екенін көру үшін тексереді. Себебі, 103 - негізінен Танекеева, нәтижесі - Табылмады. Егер формулада «Нұрбековты» «Танекееваға» өзгертсеңіз, нәтижесі - Орналастырылды болады.

=INT(YEARFRAC(DATE(2014,6,30), VLOOKUP(105,A2:E7,5, FALSE), 1))

2014 қаржы жылындағы идентификатор нөмірі 105 болатын қызметкердің жасын табады. YEARFRAC функциясы арқылы қаржы жылының аяқталу мерзімінен туған күн алынып, 59 нәтижесі INT функциясы арқылы бүтін сан түрінде көрсетіледі.

=IF(ISNA(VLOOKUP(105,A2:E7,2,FALSE)) = TRUE, «Қызметкер табылмады», VLOOKUP(105,A2:E7,2,FALSE))

Егер идентификаторлық нөмірі 105 болатын қызметкер бар болса, қызметкердің тегін Болатов деп көрсетеді. Әйтпесе, Қызметкер табылмады деген хабар көрсетеді. VLOOKUP функциясы #N/A қате мәнін көрсеткен кезде, ISNA функциясы (IS функцияларын қараңыз) TRUE мәнін шығарады.

=VLOOKUP(104,A2:E7,3,FALSE) & " " & VLOOKUP(104,A2:E7,2,FALSE) & " - " & VLOOKUP(104,A2:E7,4,FALSE)

Идентификаторлық нөмірі 104 болатын қызметкер үшін, үш ұяшықтағы мәндерді толық сөйлем етіп біріктіредіНұржан Пернебаев - сауда өкілі.

Жалпы мәселелер

Мәселе

Неден қате кетті

Қате мән қайтарылды

Егер range_lookup - TRUE болса немесе есепке алынбаса, бірінші бағанды сан бойынша немесе алфавит бойынша сұрыптау қажет. Егер бірінші баған сұрыпталмаса, қайтарылған мән сіз күтпеген бір мән болуы қажет. Бірінші бағанды сұрыптаңыз немесе нақты сәйкестік үшін, ЖАЛҒАН мәнін пайдаланыңыз.

Ұяшықтағы #N/A

  • Егер range_lookup TRUE болса, егер lookup_value ішіндегі мән table_array кестесінің бірінші бағанындағы ең кіші мәннен аз болса, #N/A қате мәнін аласыз.

  • Егер range_lookup FALSE болса, #N/A қате мәні нақты нөмірдің табылмағанын көрсетеді.

#N/A, #REF және т.б. сияқты жұмыс парақтарындағы қателер туралы қосымша ақпарат алыңыз.

Ұяшықтағы #REF!

Егер col_index_num мәні table-array ішіндегі баған санынан үлкен болса, #REF! қате мәнін аласыз.

Ұяшықтағы #VALUE!

Егер table_array мәні 1-ден аз болса, #VALUE! қате мәніне қол жеткізесіз.

Ұяшықтағы #NAME?

#NAME? қате мәні әдетте формулада дәйексөз жоқ екенін білдіреді. Адамның атын іздеу үшін формуладағы аттын тырнақшаға алынуына көз жеткізіңіз. Мысалы, "Абенов" сияқты аты-жөнін =VLOOKUP("Абенов",B2:E7,2,FALSE) формуласына енгізіңіз.

Үздік әдістер

Орындалатын әрекет

Мақсаты

range_lookup үшін нақты сілтемелерді пайдалану

Нақты сілтемелерді пайдалану формулаларды дәл сол іздеу ауқымында іздейтіндей толтыруға мүмкіндік береді.

Нақты ұяшық сілтемелерін қалай қолдану қажеттігі туралы ақпарат алыңыз.

Санды немесе күн мәндерін мәтін ретінде сақтамаңыз.

Сан немесе күн мәндерін іздеген кезде, table_array бірінші бағанындағы деректер мәтін түрінде сақталмағанына көз жеткізіңіз Әйтпесе, VLOOKUP функциясы қате немесе күтілмеген мәнді қайтаруы мүмкін.

Бірінші бағанды сұрыптау

аралық_бойынша_қарап_шығу мәні TRUE болғанда VLOOKUP функциясын пайдаланбас бұрын, кесте_массиві алғашқы бағанын сұрыптаңыз.

Қойылмалы таңбаларды пайдалану

Егер аралық_бойынша_қарап_шығу аргументінің мәні FALSE болса, іздейтін_мән аргументі мәтін болса, іздейтін_мән аргументінде қойылмалы таңбаларды, сұрақ белгісін (?) және жұлдызшаны (*) пайдалануға болады. Сұрақ белгісі кез келген дара таңбаға сәйкес келеді; жұлдызша таңбалардың кез келген бірізділігіне сәйкес келеді. Егер нақты сұрақ белгісін немесе жұлдызшаны тауып алу қажет болса, таңба алдына ирек таңбасын (~) теріңіз.

Мысалы, =VLOOKUP("Fontan?",B2:E7,2,FALSE) формуласы Абеновтың барлық инстанциясын іздейді, соңғы әрпі өзгеруі мүмкін.

Дерегіңізде қате таңба жоқ екеніне көз жеткізіңіз.

Бірінші бағанда мәтіндік мәндерді іздеген кезде, бірінші бағандағы дерек сөйлем басындағы және соңындағы бос орындардың, тік ( ' немесе " ) және көлбеу ( ‘ немесе “) тырнақшалардың немесе басып шығарылмайтын таңбалардың жоқ екеніне көз жеткізіңіз. Мұндай жағдайларда, VLOOKUP функциясы қате немесе күтілмеген мәнді беруі мүмкін.

Дәлме-дәл нәтижеге қол жеткізу үшін және ұяшықтағы кесте мәндерінен кейінгі орындарды жою үшін, CLEAN функциясын немесе TRIM функциясын пайдаланыңыз.

Қосымша

Дағдыларды жетілдіру
Оқыту курсымен танысыңыз
Жаңа мүмкіндіктерге бірінші болып қол жеткізу
Office Insider бағдарламасына қосылу

Осы ақпарат пайдалы болды ма?

Пікіріңіз үшін рақмет!

Пікіріңізге рақмет! Сізді Office қолдау көрсету қызметіндегі агенттердің бірімен байланыстырған жөн болуы мүмкін.

×