Vērtību uzmeklēšana, izmantojot funkciju VLOOKUP, INDEX vai MATCH

Padoms.: Mēģiniet izmantot jaunās XLOOKUP un XMATCH funkcijas: šajā rakstā aprakstīto funkciju uzlabotas versijas. Šīs jaunās funkcijas darbojas jebkurā virzienā un tiek atgrieztas precīzas atbilstības pēc noklusējuma, atvieglojot to lietošanu nekā to priekšgājēji.

Pieņemsim, ka jums ir Office atrašanās vietas numuru saraksts, un ir jāzina, kuri darbinieki ir katrā birojā. Izklājlapa ir milzīga, tāpēc, iespējams, uzskatāt, ka tas ir grūts uzdevums. To ir diezgan viegli paveikt, izmantojot funkciju LOOKUP.

Funkcijas VLOOKUP un HLOOKUP apvienojumā ar index un Matchir dažas no noderīgākajām Excel funkcijām.

Piezīme.: Uzmeklēšanas vedņa līdzeklis programmā Excel vairs nav pieejams.

Tālāk ir parādīts piemērs, kā izmantot funkciju VLOOKUP.

=VLOOKUP(B2,C2:E7,3,TRUE)

Šajā piemērā B2 ir pirmais arguments— datu elements, kas funkcijai ir jādarbojas. Attiecībā uz funkciju VLOOKUP šis pirmais arguments ir vērtība, ko vēlaties atrast. Šis arguments var būt šūnas atsauce vai fiksēta vērtība, piemēram, "Bērziņš" vai 21 000. Otrais arguments ir šūnu diapazons, C2-: E7, kurā meklēt meklējamo vērtību. Trešais arguments ir kolonna šajā šūnu diapazonā, kurā ir ietverta meklētā vērtība.

Ceturtais arguments nav obligāts. Ievadiet patiess vai APLAMs. Ja ievadāt TRUE vai atstājat šo argumentu tukšu, funkcija atgriež pirmajā argumentā norādītās vērtības aptuveno atbilstību. Ja ievadāt APLAMs, funkcija atbilstoši pirmajam argumentam nodrošina vērtību. Citiem vārdiem sakot, ceturtais arguments tiek atstāts tukšs — vai arī tiek ievadīts patiess — tas ļauj elastīgāk.

Šis piemērs parāda, kā darbojas šī funkcija. Ievadot vērtību šūnā B2 (pirmais arguments), funkcija VLOOKUP meklē šūnās diapazonā C2: E7 (otrais arguments) un atgriež tuvāko aptuveno atbilstību no diapazona trešās kolonnas, kolonnā E (trešais arguments).

Tipisks funkcijas VLOOKUP lietojums

Ceturtais arguments ir tukšs, tāpēc funkcija atgriež aptuvenu atbilstību. Ja neatgriež, kolonnās C un D ir jāievada kāda vērtība, lai vispār iegūtu kaut kādu rezultātu.

Kad esat apmierināts ar funkciju VLOOKUP, funkcija HLOOKUP ir vienādi viegli lietojama. Jūs ievadāt tos pašus argumentus, bet tas meklē rindās, nevis kolonnās.

INDEX un MATCH izmantošana VLOOKUP vietā

Pastāv daži ierobežojumi, izmantojot funkciju VLOOKUP, funkcija VLOOKUP var tikai uzmeklēt vērtību no kreisās puses uz labo. Tas nozīmē, ka kolonna, kurā ir uzmeklētā vērtība, vienmēr ir jānovieto pa kreisi no kolonnas, kas satur atgriežamo vērtību. Tagad, ja jūsu izklājlapa nav veidota šādā veidā, pēc tam Nelietojiet funkciju VLOOKUP. Izmantojiet funkciju INDEX un MATCH kombināciju.

Šajā piemērā redzams mazs saraksts, kurā meklējamā vērtība ir Čikāga, kas nav vistālāk pa kreisi. Tāpēc mēs nevaram izmantot funkciju VLOOKUP. Tā vietā izmantosim funkciju MATCH, lai atrastu Čikāgu diapazonā B1: B11. Tas ir atrasts 4. rindā. Pēc tam INDEX izmanto šo vērtību kā uzmeklēšanas argumentu un atrod Čikāgas populāciju 4. kolonnā (D kolonna). Izmantotā formula tiek rādīta šūnā A14.

INDEX un MATCH izmantošana, lai uzmeklētu vērtību

Papildu piemērus par INDEX un MATCH izmantošanu, nevis funkciju VLOOKUP, skatiet rakstā https://www.MrExcel.com/Excel-tips/Excel-VLOOKUP-index-Match/ ar Bill jelen, Microsoft MVP.

Izmēģiniet!

Ja vēlaties eksperimentēt ar uzmeklēšanas funkcijām pirms to izmēģināšanas ar saviem datiem, Lūk, daži datu paraugi.

VLOOKUP piemērs darbā

Kopējiet tālāk norādītos datus tukšā izklājlapā.

Padoms.: Pirms ielīmējat datus programmā Excel, iestatiet kolonnu platumu kolonnās no A līdz 250 pikseļi un noklikšķiniet uz Aplauzt tekstu (cilneSākums , grupa Līdzinājums ).

Blīvums

Viskozitāte

Temperatūra

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

Formula

Apraksts

Rezultāts

=VLOOKUP(1,A2:C10,2)

Izmantojot aptuvenu atbilstību, kolonnā A meklē vērtību 1, atrod tajā lielāko vērtību, kas ir mazāka vai vienāda ar 1, kas ir 0,946, un tad tai pašā rindā atgriež vērtību no kolonnas B.

2,17

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

Izmantojot aptuvenu atbilstību, kolonnā A meklē vērtību 1, atrod tajā lielāko vērtību, kas ir mazāka vai vienāda ar 1, kas ir 0,946, un tad tai pašā rindā atgriež vērtību no kolonnas C.

100

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

Izmantojot precīzu atbilstību, kolonnā A meklē vērtību 0,7. Tā kā kolonnā A precīzas atbilstības nav, tiek atgriezta kļūda.

#N/A

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

Izmantojot aptuvenu atbilstību, kolonnā A meklē vērtību 0,1. Tā kā 0,1 ir mazāka vērtība par kolonnas A mazāko vērtību, tiek atgriezta kļūda.

#N/A

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

Izmantojot aptuvenu atbilstību, kolonnā A meklē vērtību 2, atrod tajā lielāko vērtību, kas ir mazāka vai vienāda ar 2, kas ir 1,29, un tad tai pašā rindā atgriež vērtību no kolonnas B.

1,71

HLOOKUP piemērs

Kopējiet visas šūnas šajā tabulā un ielīmējiet tās tukšas Excel darblapas šūnā A1.

Padoms.: Pirms ielīmējat datus programmā Excel, iestatiet kolonnu platumu kolonnās no A līdz 250 pikseļi un noklikšķiniet uz Aplauzt tekstu (cilneSākums , grupa Līdzinājums ).

Asis

Bloki

Briketes

4

4

9

5

7

10

6

8

11

Formula

Apraksts

Rezultāts

=HLOOKUP("Asis"; A1:C4; 2; TRUE)

1. rindā uzmeklē “Asis” un atgriež vērtību no 2. rindas, kas atrodas tajā pašā kolonnā (kolonnā A).

4

=HLOOKUP("Bloki"; A1:C4; 3; FALSE)

1. rindā uzmeklē “Bloki” un atgriež vērtību no 3. rindas, kas atrodas tajā pašā kolonnā (kolonnā B).

7

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

1. rindā uzmeklē “B” un atgriež vērtību no 3. rindas, kas atrodas tajā pašā kolonnā. Tā kā vērtībai “B” nav tieši atbilstošas vērtības, tiek izmantota lielākā rindas 1 vērtība, kas ir mazāka par “B”: “Asis” kolonnā A.

5

=HLOOKUP("Briketes"; A1:C4; 4)

1. rindā uzmeklē “Briketes” un atgriež vērtību no 4. rindas, kas atrodas tajā pašā kolonnā (kolonnā C).

11

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

Trīs rindu masīva konstantē uzmeklē skaitli 3 un atgriež vērtību no tās pašas kolonnas (šajā gadījumā trešās) 2. rindas. Masīva konstantē ir trīs vērtību rindas, katra rinda atdalīta ar semikolu (;). Tā kā vērtība “c” ir atrodama rindā 2 un atrodas tajā pašā kolonnā, kur 3, tiek atgriezts “c”.

c

Piemēri INDEKSā un ATBILSTĪBĀ

Šajā pēdējā piemērā kopā tiek lietota funkcija INDEX un MATCH, lai atgrieztu agrāko rēķina numuru un atbilstošo datumu katrai no piecām pilsētām. Tā kā datums tiek atgriezts kā skaitlis, tiek izmantota funkcija TEXT, lai to formatētu kā datumu. Funkcija INDEX faktiski izmanto funkcijas MATCH rezultātu kā argumentu. Funkciju INDEX un MATCH kombinācija katrā formulā tiek izmantota divas reizes — vispirms, lai atgrieztu rēķina numuru, un pēc tam, lai atgrieztu datumu.

Kopējiet visas šūnas šajā tabulā un ielīmējiet tās tukšas Excel darblapas šūnā A1.

Padoms.: Pirms ielīmējat datus programmā Excel, iestatiet kolonnu platumu no A līdz D 250 pikseļiem un noklikšķiniet uz Aplauzt tekstu (cilneSākums , grupa Līdzinājums ).

Rēķins

Pilsēta

Rēķina datums

Agrākais rēķins, kārtots pēc pilsētas, ar datumu

3115

Atlanta

07.04.12.

="Jelgava = "&INDEX($A$2:$C$33,MATCH("Jelgava",$B$2:$B$33,0),1)& ", Rēķina datums: " & TEXT(INDEX($A$2:$C$33,MATCH("Jelgava",$B$2:$B$33,0),3),"dd.mm.yy")

3137

Atlanta

09.04.12.

="Cēsis = "&INDEX($A$2:$C$33,MATCH("Cēsis",$B$2:$B$33,0),1)& ", Rēķina datums: " & TEXT(INDEX($A$2:$C$33,MATCH("Cēsis",$B$2:$B$33,0),3),"dd.mm.yy")

3154

Atlanta

11.04.12

="Liepāja = "&INDEX($A$2:$C$33,MATCH("Liepāja",$B$2:$B$33,0),1)& ", Rēķina datums: " & TEXT(INDEX($A$2:$C$33,MATCH("Liepāja",$B$2:$B$33,0),3),"dd.mm.yy")

3191

Atlanta

21.04.12.

="Valmiera = "&INDEX($A$2:$C$33,MATCH("Valmiera",$B$2:$B$33,0),1)& ", Rēķina datums: " & TEXT(INDEX($A$2:$C$33,MATCH("Valmiera",$B$2:$B$33,0),3),"dd.mm.yy")

3293

Atlanta

25.04.12.

="Valka = "&INDEX($A$2:$C$33,MATCH("Valka",$B$2:$B$33,0),1)& ", Rēķina datums: " & TEXT(INDEX($A$2:$C$33,MATCH("Valka",$B$2:$B$33,0),3),"dd.mm.yy")

3331

Atlanta

27.04.12.

3350

Atlanta

28.04.12.

3390

Atlanta

01.05.12.

3441

Atlanta

02.05.12.

3517

Jelgava

08.05.12.

3124

Ostina

09.04.12.

3155

Ostina

11.04.12.

3177

Ostina

19.04.12.

3357

Ostina

28.04.12.

3492

Cēsis

06.05.12.

3316

Dallasa

25.04.12.

3346

Dallasa

28.04.12.

3372

Liepāja

01.05.12.

3414

Liepāja

01.05.12.

3451

Dallasa

02.05.12.

3467

Dallasa

02.05.12.

3474

Dallasa

04.05.12.

3490

Dallasa

05.05.12.

3503

Liepāja

08.05.12.

3151

Ņūorleāna

09.04.12.

3438

Ņūorleāna

02.05.12.

3471

Valmiera

04.05.12.

3160

Tampa

18.04.12.

3328

Tampa

26.04.12.

3368

Tampa

29.04.12.

3420

Tampa

01.05.12.

3501

Valka

06.05.12.

Skatiet arī

Ātrās uzziņas karte: VLOOKUP atsvaidzinātāja

uzmeklēšanas un atsauču funkcijas (atsauce)

Izmantojiet argumentu table_array funkcijā VLOOKUP

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka jums varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta speciālistiem.

×