INDEX-MATCH ir kļuvis par populārāku Excel rīku, jo tas atrisina funkcijas VLOOKUP ierobežojumus un ir vieglāk lietojams. Funkcijai INDEX-MATCH programmā Excel ir vairākas priekšrocības salīdzinājumā ar funkciju VLOOKUP:

1. INDEX un MATCH ir elastīgāki un ātrāki nekā Vlookup
2. Ir iespējams veikt horizontālo uzmeklēšanu, vertikālo uzmeklēšanu, divvirzienu uzmeklēšanu, meklēšanu pa kreisi, reģistrjutīgu meklēšanu un pat meklēšanu, pamatojoties uz vairākiem kritērijiem.
3. Kārtotajos datos INDEX-MATCH ir par 30% ātrāks nekā VLOOKUP. Tas nozīmē, ka lielākā datu kopā par 30% ātrāk ir saprātīgāk.

Sāksim ar detalizētiem katra INDEX un MATCH jēdzieniem.

INDEX Funkcija

Funkcija INDEX programmā Excel ir ļoti jaudīga, tajā pašā laikā elastīgs rīks, kas izgūst vērtību noteiktā diapazona vietā. Citiem vārdiem sakot, tas atgriež šūnas saturu, kas norādīts ar rindu un kolonnu nobīdi.

Sintakse:

=INDEX(reference, [row], [column])>

Parametri:

atsauce: šūnu masīvs, kurā jānobīda. Tas var būt viens diapazons vai visa datu kopa datu tabulā. rinda [neobligāti]: nobīdes rindu skaits. Tas nozīmē, ka, ja mēs izvēlamies tabulas atsauces diapazonu kā A1:A5, tad šūna/saturs, kuru vēlamies iegūt, ir vertikālā attālumā. Šeit A1 rindai būs 1, A2 rindai = 2 un tā tālāk. Ja mēs piešķiram rindai = 4, tad tiks iegūts A4. Tā kā rinda nav obligāta, tādēļ, ja mēs nenorādām nevienu rindas numuru, tā izņem visas rindas atsauces diapazonā. Šajā gadījumā tas ir no A1 līdz A5. kolonna [neobligāti]: nobīdes kolonnu skaits. Tas nozīmē, ka, ja mēs izvēlamies tabulas atsauces diapazonu kā A1:B5, šūna/saturs, kuru vēlamies iegūt, ir horizontālā attālumā. Šeit A1 rindai būs 1 un kolonnai būs 1, B1 rindai būs 1, bet kolonna būs 2 līdzīgi, ja A2 rinda = 2 kolonna = 1, B2 rindai = 2 kolonna = 2 un tā tālāk. Ja mēs piešķiram rindu = 5 un 2. kolonnu, tas izvilks B5. Tā kā kolonna nav obligāta, tad, ja mēs nenorādīsim nevienu rindu Nr. tad tas izvilks visu kolonnu atsauces diapazonā. Piemēram, ja rinda = 2 un kolonna ir tukša, tā tiks iegūta (A2:B2). Ja mēs nenorādīsim gan rindu, gan kolonnu, tiks iegūta visa atsauces tabula, kas ir (A1:B5).

Atsauces tabula: Šī tabula tiks izmantota kā atsauces tabula visiem funkcijas INDEX piemēriem. Pirmā šūna atrodas pie B3 (PĀRTIKA), bet pēdējā diagonālā šūna atrodas pie F10 (180).

Piemēri: Tālāk ir sniegti daži indeksa funkciju piemēri.

1. gadījums: Rindas un kolonnas nav minētas.

Ievades komanda: =INDEKSS(B3:C10)

2. gadījums: Tiek pieminētas tikai rindas.

Ievades komanda: =INDEKSS(B3:C10,2)

3. gadījums: Ir minētas gan rindas, gan kolonnas.

Ievades komanda: =INDEKSS(B3:D10;4;2)

4. gadījums: Ir minētas tikai kolonnas.

Ievades komanda: =INDEKSS(B3 : D10 , , 2)

Problēma ar INDEX funkciju: Problēma ar funkciju INDEX ir tāda, ka ir jānorāda rindas un kolonnas datiem, ko mēs meklējam. Pieņemsim, ka mums ir darīšana ar mašīnmācīšanās datu kopu, kurā ir 10 000 rindu un kolonnu, tad būs ļoti grūti meklēt un iegūt datus, ko mēs meklējam. Šeit nāk atbilstības funkcijas koncepcija, kas identificēs rindas un kolonnas, pamatojoties uz dažiem nosacījumiem.

Funkcija MATCH

Tas izgūst vienuma/vērtības pozīciju diapazonā. Tā ir mazāk pilnveidota VLOOKUP vai HLOOKUP versija, kas atgriež tikai informāciju par atrašanās vietu, nevis faktiskos datus. MATCH nav reģistrjutīgs, un nav svarīgi, vai diapazons ir horizontāls vai vertikāls.

Sintakse:

=MATCH(search_key, range, [search_type])>

Parametri:

search_key: vērtība, kas jāmeklē. Piemēram, 42, Cats vai I24. diapazons: viendimensijas masīvs, kas jāmeklē. Tā var būt viena rinda vai viena kolonna.piem.->A1:A10 , A2:D2 utt. search_type [neobligāts]: meklēšanas metode. = 1 (noklusējums) atrod lielāko vērtību, kas ir mazāka vai vienāda ar search_key, ja diapazons ir kārtots augošā secībā.
• = 0 atrod precīzu vērtību, ja diapazons ir nešķirots.
• = -1 atrod mazāko vērtību, kas ir lielāka vai vienāda ar meklēšanas_atslēgu, ja diapazons ir sakārtots dilstošā secībā.

Rindas numuru vai kolonnas numuru var atrast, izmantojot atbilstības funkciju, un to var izmantot indeksa funkcijā, tāpēc, ja ir kāda informācija par vienumu, visu informāciju par vienumu var iegūt, atrodot vienuma rindu/kolonnu, izmantojot atbilstību. pēc tam ligzdojot to indeksa funkcijā.

Atsauces tabula: Šī tabula tiks izmantota kā atsauces tabula visiem funkcijas MATCH piemēriem. Pirmā šūna atrodas pie B3 (PĀRTIKA), bet pēdējā diagonālā šūna atrodas pie F10 (180)

Piemēri: Tālāk ir sniegti daži funkcijas MATCH piemēri-

1. gadījums: Meklēšanas veids 0, tas nozīmē precīzu atbilstību.

Ievades komanda: =MATCH(dienvidindijas,C3:C10,0)

2. gadījums: 1. meklēšanas veids (noklusējums).

Ievades komanda: =MATCH (Dienvidindijas, C3:C10)

log4j

3. gadījums: Meklēšanas veids -1.

Ievades komanda: =MATCH(dienvidindijas,C3:C10,-1)

INDEKSS-MATCH Kopā

Iepriekšējos piemēros rindu un kolonnu statiskās vērtības tika norādītas funkcijā INDEX. Pieņemsim, ka nav priekšzināšanu par rindu un kolonnu pozīciju, tad rindu un kolonnu pozīciju var nodrošināt, izmantojot funkciju MATCH. Tas ir dinamisks veids, kā meklēt un iegūt vērtību.

Sintakse:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Atsauces tabula: Tiks izmantota šāda atsauces tabula. Pirmā šūna atrodas pie B3 (PĀRTIKA), bet pēdējā diagonālā šūna atrodas pie F10 (180)

Piemērs: Pieņemsim, ka uzdevums ir atrast Masala Dosa izmaksas. Ir zināms, ka 3. kolonna atspoguļo preču izmaksas, bet Masala Dosa rindas pozīcija nav zināma. Problēmu var iedalīt divos posmos -

1. darbība: Atrodiet Masala Dosas pozīciju, izmantojot formulu:

 =MATCH('Masala Dosa',B3:B10,0)>

Šeit B3:B10 apzīmē kolonnu Pārtika un 0 nozīmē precīzu atbilstību. Tas atgriezīs Masala Dosa rindas numuru.

2. darbība: Atrodiet Masala Dosa izmaksas. Izmantojiet INDEX funkciju, lai uzzinātu Masala Dosa izmaksas. Aizstājot iepriekš minēto MATCH funkcijas vaicājumu funkcijas INDEX iekšpusē vietā, kur nepieciešama precīza Masala Dosa pozīcija, un izmaksu kolonnas numurs ir 3, kas jau ir zināms.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Divu veidu meklēšana kopā ar INDEX-MATCH

Iepriekšējā piemērā atribūta izmaksas kolonnas pozīcija tika kodēta. Tātad, tas nebija pilnībā dinamisks.

1. gadījums: Pieņemsim, ka nav zināšanu arī par izmaksu kolonnas numuru, tad to var iegūt, izmantojot formulu:

 =MATCH('Cost',B3:F3,0)>

Šeit B3:F3 apzīmē galvenes kolonnu.

2. gadījums: Ja rindas, kā arī kolonnas vērtība tiek nodrošināta, izmantojot funkciju MATCH (nesniedzot statisku vērtību), to sauc par divvirzienu uzmeklēšanu. To var panākt, izmantojot formulu:

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Meklēt pa kreisi

Viena no galvenajām INDEX un MATCH priekšrocībām salīdzinājumā ar funkciju VLOOKUP ir iespēja veikt meklēšanu pa kreisi. Tas nozīmē, ka ir iespējams iegūt vienuma rindas pozīciju, izmantojot jebkuru atribūtu labajā pusē, un var iegūt cita atribūta vērtību kreisajā pusē.

Piemēram, pieņemsim, ka iegādājieties pārtiku, kuras cena ir 140 Rs. Netieši mēs sakām, ka iegādājieties Biryani. Šajā piemērā izmaksas Rs 140/- ir zināmas, ir nepieciešams iegūt pārtiku. Tā kā sleja Izmaksas atrodas pa labi no slejas Pārtika. Ja tiek lietots VLOOKUP, tas nevarēs veikt meklēšanu kolonnas Izmaksas kreisajā pusē. Tāpēc, izmantojot VLOOKUP, nav iespējams iegūt pārtikas nosaukumu.

Lai novērstu šo trūkumu, var izmantot funkciju INDEX-MATCH.
1. darbība: Pirmās izvilkuma rindas pozīcija izmaksas 140 Rs, izmantojot formulu:

 =MATCH(140, D3:D10,0)>

Šeit D3: D10 apzīmē izmaksu kolonnu, kurā tiek meklēts rindas numurs Maksa 140 Rs.

2. darbība: Pēc rindas numura iegūšanas nākamais solis ir izmantot INDEX funkciju, lai izvilktu pārtikas nosaukumu, izmantojot formulu:

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Šeit B3:B10 apzīmē pārtikas sleju un 140 ir pārtikas preces izmaksas.

Reģistrjutīga meklēšana

Funkcija MATCH pati par sevi nav reģistrjutīga. Tas nozīmē, ja ir pārtikas nosaukums DHOKLA un funkcija MATCH tiek izmantota ar šādu meklēšanas vārdu:

1. Dokla
2. dhokla
3. DhOkLA

Visi atgriezīs DHOKLA rindas pozīciju. Tomēr funkciju EXACT var izmantot kopā ar INDEX un MATCH, lai veiktu uzmeklēšanu, kurā tiek ņemti vērā lielie un mazie burti.

Precīza funkcija: Funkcija Excel EXACT salīdzina divas teksta virknes, ņemot vērā lielos un mazos burtus, un atgriež TRUE, ja tie ir vienādi, un FALSE, ja nav. EXACT ir reģistrjutīgs.

Piemēri:

EXACT (DHOKLA, DHOKLA): tas atgriezīsies True. EXACT (DHOKLA, Dhokla): tas atgriezīs False. EXACT(DHOKLA,dhokla): Tas atgriezīs False. EXACT (DHOKLA, DhOkLA): tas atgriezīs False.

Piemērs: Teiksim, uzdevums ir meklēt pārtikas veidu Dhokla, bet reģistrjutīgā veidā. To var izdarīt, izmantojot formulu -

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Šeit funkcija EXACT atgriezīs True, ja vērtība kolonnā B3:B10 sakrīt ar Dhokla ar to pašu gadījumu, pretējā gadījumā tā atgriezīs False. Tagad funkcija MATCH tiks lietota kolonnā B3:B10 un meklēs rindu ar precīzu vērtību TRUE. Pēc tam funkcija INDEX izgūs kolonnas C3:C10 (Pārtikas veida kolonna) vērtību rindā, ko atgriezusi funkcija MATCH.

Vairāku kritēriju meklēšana

Viena no sarežģītākajām Excel problēmām ir uzmeklēšana, pamatojoties uz vairākiem kritērijiem. Citiem vārdiem sakot, uzmeklēšana, kas atbilst vairāk nekā vienai kolonnai vienlaikus. Tālāk esošajā piemērā funkcijas INDEX un MATCH un Būla loģika tiek izmantotas, lai saskaņotu 3 kolonnas -

1. Ēdiens.
2. Izmaksas.
3. Daudzums.

Lai iegūtu kopējās izmaksas.

Piemērs: Pieņemsim, ka uzdevums ir aprēķināt Makaronu kopējās izmaksas, kur

Ēdiens: makaroni. Izmaksas: 60. Daudzums: 1.

Tātad šajā piemērā atbilstības veikšanai ir trīs kritēriji. Tālāk ir norādītas meklēšanas darbības, pamatojoties uz vairākiem kritērijiem -

1. darbība: Vispirms saskaņojiet pārtikas kolonnu (B3:B10) ar makaroniem, izmantojot formulu:

 'PASTA' = B3:B10>

Tas pārveidos B3:B10 (pārtikas kolonnas) vērtības par Būla vērtību. Tā ir patiesība, ja pārtika ir makaroni, citādi nepatiesi.

2. darbība: Pēc tam saskaņojiet izmaksu kritērijus šādā veidā:

 60 = D3:D10>

Tas aizstās D3:D10 (izmaksu kolonnas) vērtības kā Būla vērtības. Tas ir patiess, kur izmaksas = 60, bet citādi nepatiesi.

3. darbība: Nākamais solis ir atbilst trešajam kritērijam, kas ir daudzums = 1, šādā veidā:

 1 = E3:E10>

Tas aizstās E3:E10 kolonnu (Daudzuma kolonna) kā Patiess, kur daudzums = 1, pretējā gadījumā tā būs nepatiesa.

4. darbība: Reiziniet pirmā, otrā un trešā kritērija rezultātu. Tas būs visu nosacījumu krustpunkts un Būla vērtība Patiess/Nepatiess kā 1/0.

5. darbība: Tagad rezultāts būs kolonna ar 0 un 1. Šeit izmantojiet MATCH funkciju, lai atrastu rindu skaitu kolonnām, kurās ir 1. Jo, ja kolonnai ir vērtība 1, tas nozīmē, ka tā atbilst visiem trim kritērijiem.

6. darbība: Pēc rindas numura iegūšanas izmantojiet funkciju INDEX, lai iegūtu šīs rindas kopējās izmaksas.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Šeit F3:F10 apzīmē kopējo izmaksu sleju.