Padoms.: Mēģiniet izmantot jaunās funkcijas XLOOKUP un XMATCH , šajā rakstā aprakstīto funkciju uzlabotās versijas. Šīs jaunās funkcijas pēc noklusējuma darbojas jebkurā virzienā un atgriež precīzas atbilstības, atvieglojot un ērtāku lietošanu nekā priekšteči.
Pieņemsim, ka jums ir biroja atrašanās vietu numuru saraksts un ir jāzina, kuri darbinieki ir katrā no šiem birojiem. Izklājlapa ir milzīga, tāpēc, iespējams, ka tas ir sarežģīts uzdevums. Ar uzmeklēšanas funkciju to ir diezgan viegli izdarīt.
Funkcijas VLOOKUP un HLOOKUP kopā ar INDEX un MATCH ir dažas no noderīgākajām funkcijām programmā Excel.
Piezīme.: Uzmeklēšanas vedņa līdzeklis vairs nav pieejams programmā Excel.
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. VLOOKUP gadījumā 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ā jāmeklē meklējamā vērtība. Trešais arguments ir šī šūnu diapazona kolonna, kurā ir meklētā vērtība.
Ceturtais arguments nav obligāts. Ievadiet TRUE vai FALSE. 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 FALSE, funkcija atbildīs vērtībai, ko nodrošina pirmais arguments. Citiem vārdiem sakot, atstājot ceturto argumentu tukšu ( vai ievadot TRUE), tas nodrošina lielāku elastību.
Šis piemērs parāda, kā darbojas šī funkcija. Ievadot vērtību šūnā B2 (pirmais arguments), VLOOKUP meklē šūnās diapazonā C2:E7 (2. arguments) un atgriež tuvāko aptuveno atbilstību no diapazona trešās kolonnas (kolonnas E (3. 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.
Ja apiemācīsit funkciju VLOOKUP, funkcija HLOOKUP būs tikpat vienkārši lietojama. Ievadiet tos pašus argumentus, bet meklē rindās, nevis kolonnās.
FUNKCIJAS INDEX un MATCH izmantošana funkcijas VLOOKUP vietā
Izmantojot funkciju VLOOKUP, ir noteikti ierobežojumi — funkcija VLOOKUP var uzmeklēt tikai vērtību no kreisās puses uz labo pusi. Tas nozīmē, ka kolonnai, kurā ir uzmeklamā vērtība, vienmēr jāatrodas pa kreisi no kolonnas, kurā atrodas atgriežamā vērtība. Ja izklājlapa nav veidota šādi, neizmantojiet VLOOKUP. Tā vietā izmantojiet funkciju INDEX un MATCH kombināciju.
Šajā piemērā redzams mazs saraksts, kurā meklējamā vērtība Čikāgā nav kreisajā kolonnā. Tāpēc nevaram izmantot VLOOKUP. Tā vietā mēs izmantosim funkciju MATCH, lai diapazonā B1:B11 atrastu Čikāgu. Tas atrodas 4. rindā. Pēc tam INDEX izmanto šo vērtību kā uzmeklēšanas argumentu un 4. kolonnā (kolonna D) atrod čikāgas populāciju. Izmantotā formula ir redzama šūnā A14.
Papildu piemērus par INDEX un MATCH izmantošanu VLOOKUP vietā skatiet rakstā no https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Bill Jelen, Microsoft MVP.
Izmēģiniet!
Ja vēlaties eksperimentēt ar uzmeklēšanas funkcijām, pirms tās izmēģināt ar saviem datiem, šeit ir parauga dati.
VLOOKUP piemērs darbā
Kopējiet tālāk norādītos datus tukšā izklājlapā.
Padoms.: Pirms datu ielīmēšanas programmā Excel iestatiet kolonnu no A līdz C platumu uz 250 pikseļiem un noklikšķiniet uz Aplauzt tekstu (cilnes Sā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 datu ielīmēšanas programmā Excel iestatiet kolonnu no A līdz C platumu uz 250 pikseļiem un noklikšķiniet uz Aplauzt tekstu (cilnes Sā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 |
INDEX un MATCH piemēri
Pēdējā piemērā izmantotas funkcijas INDEX un MATCH, lai atgrieztu agrāko rēķina numuru un to atbilstošo datumu katrā no piecām pilsētām. Tā kā datums tiek atgriezts kā skaitlis, mēs izmantojam funkciju TEXT, lai formatētu tos kā datumus. 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 datu ielīmēšanas programmā Excel iestatiet kolonnu no A līdz D platumu uz 250 pikseļiem un noklikšķiniet uz Aplauzt tekstu (cilnes Sā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. |
