Екі немесе бірнеше кестелерді қалай біріктіре аламын?

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

Жолдарды біріктіру айтарлықтай алға қарай бағытталған, бірақ бағандарды біріктіру қиынырақ болуы мүмкін, себебі кесте жолдары басқа кестедегі жолдармен әрқашан да сәйкес келмейді. VLOOKUP арқылы сәйкестендіру барысында туындайтын ақауларды алдын ала аласыз.

VLOOKUP функциясын пайдаланып екі кестені біріктіріңіз

Төменде көрсетілген мысалда екі кестенің атауларын "Көк" және "Қызғылт сары" деп өзгерттік. Көк кестеде әрбір жол тапсырысқа арналған сызық элементі бар. Сондықтан 20050 тапсырыс идентификатор нөмірінде екі элемент бар, 20051 тапсырыс идентификатор нөмірінде бір элемент бар, 20052 тапсырыс идентификатор нөмірінде үш элемент бар және т.с.с. Сатылымдар идентификатор нөмірі мен Аймақ бағандарын Көк кестеде біріктіргіміз келеді, оларды Қызғылт сары кестенің Тапсырыс идентификатор нөмірінде сәйкес элементтерге негіздеу керек.

Екі бағанды басқа кестемен біріктіру

Алайда, тапсырыс идентификатор нөмірі мәндері Көк кестеде қайталанады, ал Қызғылт сары кестедегі тапсырыс идентификатор нөмірі мәндері бірегей болып келеді. Егер Қызғылт сары кестедегі деректерді жай көшіріп алып, қоятын болсақ, 20050 тапсырысының екінші сызық элементіне арналған Сатылымдар идентификатор нөмірі мен Аймақ мәндері бір жолмен өшіріледі, осылайша сол жаңа бағандардағы мәндердің қалғандары бірден Көк кестеге ауысады.

Егер әрі қарай орындағыңыз келсе, мұнда Көк кестеге арналған деректер бар, оларды бос жұмыс парағына көшіре аласыз. Оны жұмыс парағына қойған соң, кестеге айналдыру үшін Ctrl+T пернелер тіркесімін басыңыз да, кестенің атауын Көк етіп ауыстырыңыз.

Тапсырыс идентификаторы

Сату күні

Өнім идентификаторы

20050

2/2/14

C6077B

20050

2/2/14

C9250LB

20051

2/2/14

M115A

20052

3/2/14

A760G

20052

3/2/14

E3331

20052

3/2/14

SP1447

20053

3/2/14

L88M

20054

4/2/14

S1018MM

20055

5/2/14

C6077B

20056

6/2/14

E3331

20056

6/2/14

D534X

Мұнда Қызғылт сары кестеге арналған деректер берілген; оны сол жұмыс парағына көшіріңіз. Оны жұмыс парағына қойған соң, кестеге айналдыру үшін Ctrl+T пернелер тіркесімін басыңыз да, кестенің атауын Қызғылт сары етіп ауыстырыңыз.

Тапсырыс идентификаторы

Сатылымдар идентификаторы

Аймақ

20050

447

Батыс

20051

398

Оңтүстік

20052

1006

Солтүстік

20053

447

Батыс

20054

885

Шығыс

20055

398

Оңтүстік

20056

644

Шығыс

20057

1270

Шығыс

20058

885

Шығыс

Әрбір тапсырысқа арналған Сатылымдар идентификаторы мен Аймақ мәндері әрбір тапсырыстың бірегей сызық элементімен сәйкес екендігіне көз жеткізуіміз керек. Солай жасау үшін Сатылымдар идентификаторы мен Аймақ кесте тақырыптарын Көк кестенің оң жағындағы ұяшықтарға қоямыз және Қызғылт сары кестенің Сатылымдар идентификаторы мен Аймақ бағандарынан дұрыс мәндерді алу үшін VLOOKUP формулаларын пайдаланамыз. Қадамдар реті:

  1. Сатылымдар идентификаторы мен Аймақ тақырыптарын Қызғылт сары кестеге (тек сол екі ұяшықтарды) көшіріп алыңыз.

  2. Тақырыптарды Көк кестенің Өнім идентификаторы тақырыбының оң жағындағы ұяшыққа қойыңыз.

Енді, Көк кесте жаңа Сатылымдар идентификаторы мен Аймақ бағандарын қосқанда, бес бағанға дейін ұлғайды.

  1. Көк кестеде Сатылымдар идентификаторы астындағы бірінші ұяшықта келесі формуланы жазыңыз:

    =VLOOKUP(

  2. Көк кестедегі 20050 тапсырыс идентификаторы бағанындағы бірінші ұяшықты таңдаңыз.

Жартылай толтырылған формула келесідей көрінеді:

Жартылай VLOOKUP формуласы

[@[Тапсырыс идентификаторы]]бөлігі "Тапсырыс идентификаторы бағанынан сол жолдағы мәнді алу" дегенді білдіреді.

  1. Үтір қойыңыз, "Қызғылт сары[#Барлығы]" формулаға қосылатындай етіп, тінтуірмен бүкіл Қызғылт сары кестесін таңдаңыз.

  2. Тағы бір үтір қойыңыз, 2, тағы да және 0–осылай: ,2,0

  3. Enter пернесін басыңыз, толтырылған формула келесідей көрінеді:

Толтырылған VLOOKUP формуласы

Қызғылт сары[#Барлығы] бөлігі "Қызғылт сары кестедегі барлық ұяшықтарда көрінеді" дегенді білдіреді. 2 дегеніміз «екінші бағаннан мәнді алу» және 0 дегеніміз «нақты сәйкестік болғанда ғана мәнді қайтару».

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

  1. 3 қадамға оралыңыз, бірақ осы жолы Аймақ астында бірінші ұяшықта сол формуланы жазып бастаңыз.

  2. 6 қадамда 2-ні 3-пен ауыстырып, толтырылған формула келесідей көрінеді:

Толтырылған VLOOKUP формуласы

Осы формула мен бірінші формула арасында бір ғана айырмашылық бар, біріншісі Қызғылт сары кестенің 2 бағанынан мәндерді алса, екіншісі оларды 3 бағаннан алады.

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

  1. Сатылымдар идентификаторы бағанындағы барлық мәндер ұяшықтарын таңдап, оларды көшіру үшін Ctrl+C пернелер тіркесімін басыңыз.

  2. Қою > астынан Басты көрсеткісін басыңыз.

Қою жиынтығын көрсететін Қою параметрі астындағы көрсеткі

  1. Қою жиынтығындағы Мәндерді қою пәрменін басыңыз.

Қою жиынтығындағы Мәндерді қою түймешігі

  1. Аймақ бағанындағы барлық мәндер ұяшықтарын таңдап, оларды көшіріңіз де, 10 және 11 қадамдарын қайталаңыз.

Енді екі бағандағы VLOOKUP формулалары мәндермен ауыстырылды.

Кестелер мен VLOOKUP туралы қосымша ақпарат

Кестенің өлшемін бағандар мен жолдар қосу арқылы өзгерту

Excel кестесі формулаларындағы құрылымдық сілтемелерді пайдалану

VLOOKUP: Оны қашан және қалай пайдалану керек (оқыту курстары)

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

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

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

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

×