VLOOKUP, INDEX, немесе MATCH функциялары бар мәндерді іздеу

Сізде кеңсе бөлмелері нөмірлерінің тізімі бар, ал енді әр кеңсе бөлмесінде қайсы қызметкерлер отыратынын білу қажет. Бірақ электрондық кесте тым үлкен, сондықтан не істеуге болады? Іздеу функциясын пайдаланыңыз. ТАУЫСТЫРУ және ҚАУЫСТЫРУ функциялары өте қолайлы, сонымен қатар ИНДЕКС және СӘЙКЕСТІК.

Ескерту :  Егер Іздеу шеберін табуға әрекет жасасаңыз, бұл мүмкіндік Excel бағдарламасында енді жоқ.

Мынау ТАУЫСТЫРУ функциясын қалай пайдалану туралы еске салу.

=ТАУЫСТЫРУ(B2,C2:E7,3,ДҰРЫС)

Бірініші дәлел—функция жасауы қажет деректер бөлігі —табу қажет мән. Бұл ұяшық сілтемесі немесе, "алма" немесе 21000 сияқты, қиын мән болуы мүмкін. Екінші дәлел бұл табу қажет мән бар деп ойлаған ұяшықтар ауқымы. Мына мысалда, ол C2-C7 ұяшықтары. Үшінші дәлел бұл көргіңіз келген мән бар ұяшықтар ауқымындағы баған.

Төртінші дәлел бұл қосымша. Дұрыс немесе Өтірік деген сөздерді енгізуге болады. Егер ДҰРЫС енгізсеңіз, немесе дәлелді бос қалдырсаңыз, функция бірінші дәлелде анықтаған мәннің жақын сәйкестіктігін қайтарады. Егер ӨТІРІК енгізсеңіз, функция бірінші дәлел бойынша берілетін мәнге сәйкес береді. Яғни, төртініші дәлелді бос қалдыру немесе ДҰРЫС енгізу қосымша икемділікті береді.

Бұл мысал функция қалай істейтіні туралы көрсетеді. B2 ұяшыққа (бірінші дәлел) мәнді енгізгенде, ТАУЫСТЫРУ C2-E7 ұяшықтарды іздейді (екінші дәлел) және ауқымдағы үшінші бағаннан ең жақын сәйкестікті береді, E бағаны (үшінші дәлел).

VLOOKUP функциясының әдеттегі қолдануы

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

VLOOKUP функциясы қолайлы болса, HLOOKUP функциясын пайдалану қиын болмайды. Бір дәлелді енгізесіз, бірақ ол мәндерді бағандар орнына жолдарда табады.

Тексеріп көріңіз

Жеке дерегіңізден шығарудан бұрын іздеу функцияларын тәртіпке келтіргіңіз келсе, кейбір адамдарға VLOOKUP және HLOOKUP функцияларын пайдалану ұнайды, басқалары INDEX және MATCH ұнатады. Әрбір әдісті тексеріп көріп, қайсысы сізге қолайлы екенін көріңіз.

ТАУЫСТЫРУ жұмыста

Кестедегі барлық ұяшықтарды көшіріп, Excel бағдарламасындағы бос кестенің A1 ұяшығына қойыңыз.

Тұспал   Деректерді Excel бағдарламасына қоймас бұрын A бағанынан C бағанына 250 пиксел бойынша бағанның енін белгілеңіз, содан кейін Мәтінді тасымалдау түймешігін басыңыз (Басты қойындысы, Туралау тобы).

Тығыздық

Жабысқақтық

Температура

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Формула

Сипаттама

Нәтиже

'=VLOOKUP(1,A2:C10,2)

Жақын сәйкестікті пайдалану A бағанындағы 1 мәнін іздейді, 0,946 болған А бағанындағы ең үшкен 1 тең немесе кіші мәнді тауып, бірдей жолдағы B бағанындағы мәнді қайтарады.

=VLOOKUP(1,A2:C10,2)

'=VLOOKUP(1,A2:C10,3,TRUE)

Жақын сәйкестікті пайдалану A бағанындағы 1 мәнін іздейді, 0,946 болған А бағанындағы ең үшкен 1 тең немесе кіші мәнді тауып, бірдей жолдағы C бағанындағы мәнді қайтарады.

=VLOOKUP(1,A2:C10,3,TRUE)

'=VLOOKUP(0.7,A2:C10,3,FALSE)

Нақты сәйкестікті пайдалану А бағанындағы 0,7 мәнін іздейді. Себебі қате қайтарылған А бағанында нақты сәйкестік жоқ.

=VLOOKUP(0.7,A2:C10,3,FALSE)

'=VLOOKUP(0.1,A2:C10,2,TRUE)

Жақын сәйкестікті пайдалану А бағанындағы 0,1 мәнін іздейді. Себебі 0,1 мәні қате қайтарылған А бағанындағы ең кіші мәннен азырақ.

=VLOOKUP(0.1,A2:C10,2,TRUE)

'=VLOOKUP(2,A2:C10,2,TRUE)

Жақын сәйкестікті пайдалану А бағанындағы 2 мәнін іздейді, 1,29 болған А бағанындағы ең үшкен 2 тең немесе кіші мәнді тауып, бірдей жолдағы B бағанының мәнін қайтарады.

=VLOOKUP(2,A2:C10,2,TRUE)

ҚАУЫСТЫРУ жұмыста

Кестедегі барлық ұяшықтарды көшіріп, Excel бағдарламасындағы бос кестенің A1 ұяшығына қойыңыз.

Тұспал   Деректерді Excel бағдарламасына қоймас бұрын A бағанынан C бағанына 250 пиксел бойынша бағанның енін белгілеңіз, содан кейін Мәтінді тасымалдау түймешігін басыңыз (Басты қойындысы, Туралау тобы).

Ілгіштер

Тіреулер

Бұрандалар

4

4

9

5

7

10

6

8

11

Формула

Сипаттама

Нәтиже

'=HLOOKUP("Axles", A1:C4, 2, TRUE)

1-жолдағы "Ілгіштерді" іздейді және бірдей бағандағы (А бағаны) 2-қатардағы мәнді қайтарады.

=HLOOKUP("Axles",A1:C4,2,TRUE)

'=HLOOKUP("Bearings", A1:C4, 3, FALSE)

1-жолдағы "Тіреулерді" іздейді және бірдей бағандағы (В бағаны) 3-қатардағы мәнді қайтарады.

=HLOOKUP("Bearings",A1:C4,3,FALSE)

'=HLOOKUP("B", A1:C4, 3, TRUE)

1-жолдағы "B" тауып, дәл сол бағанның 3-жолындағы мәнді қайтарады. "B" үшін нақты сәйкестік табылмағандықтан, "B" мәнінен кіші 1-жолдағы ең үлкен мән пайдаланылады: A бағанындағы "Ілгіштерді".

=HLOOKUP("B",A1:C4,3,TRUE)

'=HLOOKUP("Bolts", A1:C4, 4)

1-жолдағы "бұрандаларды" іздейді және бірдей бағандағы (С бағаны) 4-жолдан мәнді қайтарады.

=HLOOKUP("Bolts",A1:C4,4)

'=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE)

Үшінші жол көрсеткішінің тұрақтысындағы 3-нөмірді іздейді және бірдей бағаннан (бұл жағдайда үшінші баған) мәнді қайтарады. Көрсеткі тұрақтысында мәндердің үш жолы бар, әр жол үтірлі нүктемен (;) бөлінген. Себебі "c" 3 сияқты бірдей бағанда және 2-жолда табылды, "c" қайтарылды.

=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)

Әрекеттегі INDEX және MATCH

Ең алғашқы шот нөмірін және оның әр бес қаланың бірі үшін сәйкес келетін күнін қайтару үшін бұл мысал INDEX және MATCH функцияларын бірге пайдаланады. Себебі мерзім нөмір ретінде қайтарылады, TEXT функциясын оны мерзім ретінде пішімдеу үшін пайдаланамыз. INDEX функциясы MATCH функциясының нәтижесін өзінің аргументі ретінде пайдаланады. INDEX және MATCH функцияларының тіркесімі әр формулада екі рет пайдаланылады — алдымен, есеп-шот нөмірін, кейін мерзімді қайтаруда.

Кестедегі барлық ұяшықтарды көшіріп, Excel бағдарламасындағы бос кестенің A1 ұяшығына қойыңыз.

Тұспал   Деректі Excel бағдарламасына қоюдан бұрын баған ендерін A — D бағандары арқылы 250 пиксельге дейін орнатып, Мәтінді тасымалдау түймешігін басыңыз (Басты қойынды, Туралау тобы).

Есеп-шот

Қала

Есеп-шот мерзімі

Қала, мерзімі бойынша ең алғашқы есеп-шот

3115

Алматы

4/7/12

="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Есеп-шот мерзімі: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy")

3137

Алматы

4/9/12

="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Есеп-шот мерзімі: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy")

3154

Алматы

4/11/12

="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Есеп-шот мерзімі: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy")

3191

Алматы

4/21/12

="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Есеп-шот мерзімі: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy")

3293

Алматы

4/25/12

="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Есеп-шот мерзімі: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy")

3331

Алматы

4/27/12

3350

Алматы

4/28/12

3390

Алматы

5/1/12

3441

Алматы

5/2/12

3517

Алматы

5/8/12

3124

Austin

4/9/12

3155

Austin

4/11/12

3177

Austin

4/19/12

3357

Austin

4/28/12

3492

Austin

5/6/12

3316

Астана

4/25/12

3346

Астана

4/28/12

3372

Астана

5/1/12

3414

Астана

5/1/12

3451

Астана

5/2/12

3467

Астана

5/2/12

3474

Астана

5/4/12

3490

Астана

5/5/12

3503

Астана

5/8/12

3151

Шымкент

4/9/12

3438

Шымкент

5/2/12

3471

Шымкент

5/4/12

3160

Tampa

4/18/12

3328

Tampa

4/26/12

3368

Tampa

4/29/12

3420

Tampa

5/1/12

3501

Tampa

5/6/12

Іздеу функциялары туралы қосымша

Беттің жоғарғы жағы

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

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

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

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

×