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).
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.
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 |
Jelgava |
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 |
Jelgava |
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 |
Jelgava |
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 |
Jelgava |
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 |
Jelgava |
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 |
Jelgava |
27.04.12. |
|
|
3350 |
Jelgava |
28.04.12. |
|
|
3390 |
Jelgava |
01.05.12. |
|
|
3441 |
Jelgava |
02.05.12. |
|
|
3517 |
Jelgava |
08.05.12. |
|
|
3124 |
Cēsis |
09.04.12. |
|
|
3155 |
Cēsis |
11.04.12. |
|
|
3177 |
Cēsis |
19.04.12. |
|
|
3357 |
Cēsis |
28.04.12. |
|
|
3492 |
Cēsis |
06.05.12. |
|
|
3316 |
Liepāja |
25.04.12. |
|
|
3346 |
Liepāja |
28.04.12. |
|
|
3372 |
Liepāja |
01.05.12. |
|
|
3414 |
Liepāja |
01.05.12. |
|
|
3451 |
Liepāja |
02.05.12. |
|
|
3467 |
Liepāja |
02.05.12. |
|
|
3474 |
Liepāja |
04.05.12. |
|
|
3490 |
Liepāja |
05.05.12. |
|
|
3503 |
Liepāja |
08.05.12. |
|
|
3151 |
Valmiera |
09.04.12. |
|
|
3438 |
Valmiera |
02.05.12. |
|
|
3471 |
Valmiera |
04.05.12. |
|
|
3160 |
Valka |
18.04.12. |
|
|
3328 |
Valka |
26.04.12. |
|
|
3368 |
Valka |
29.04.12. |
|
|
3420 |
Valka |
01.05.12. |
|
|
3501 |
Valka |
06.05.12. |
