logo

TechCodeview

Funkciji INDEX in MATCH v Excelu

INDEX-MATCH je postal bolj priljubljeno orodje za Excel, saj rešuje omejitev funkcije VLOOKUP in je lažji za uporabo. Funkcija INDEX-MATCH v Excelu ima številne prednosti pred funkcijo VLOOKUP:

  1. INDEX in MATCH sta bolj prilagodljiva in hitrejša od Vlookupa
  2. Možno je izvesti vodoravno iskanje, navpično iskanje, dvosmerno iskanje, levo iskanje, iskanje z upoštevanjem velikih in malih črk in celo iskanje na podlagi več meril.
  3. V razvrščenih podatkih je INDEX-MATCH 30 % hitrejši od VLOOKUP. To pomeni, da je v večjem naboru podatkov 30 % hitreje bolj smiselno.

Začnimo s podrobnimi koncepti vsakega INDEXA in MATCH.



Funkcija INDEX

Funkcija INDEX v Excelu je zelo zmogljiva in hkrati prilagodljivo orodje, ki pridobi vrednost na dani lokaciji v obsegu. Z drugimi besedami, vrne vsebino celice, določeno z odmikom vrstice in stolpca.

Sintaksa: 

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

Parametri:



    referenca: Niz celic, v katere želite zamakniti. Lahko je en obseg ali celoten nabor podatkov v podatkovni tabeli. vrstica [neobvezno]: Število zamaknjenih vrstic. To pomeni, da če izberemo referenčni obseg tabele kot A1:A5, potem je celica/vsebina, ki jo želimo ekstrahirati, na kolikšni navpični razdalji. Tukaj bo za vrstico A1 1, za vrstico A2 = 2 in tako naprej. Če podamo vrstico = 4, bo izvlekel A4. Ker je vrstica neobvezna, tako da, če ne podamo nobene številke vrstice, ekstrahira celotne vrstice v referenčnem obsegu. To je v tem primeru A1 do A5. stolpec [neobvezno]: število zamaknjenih stolpcev. To pomeni, da če izberemo referenčni obseg tabele kot A1:B5, je celica/vsebina, ki jo želimo ekstrahirati, na kolikšni vodoravni razdalji. Tukaj bo za A1 vrstica 1 in stolpec 1, za B1 vrstica bo 1, vendar bo stolpec 2 podobno za A2 vrstica = 2 stolpca = 1, za B2 vrstica = 2 stolpca = 2 in tako naprej. Če podamo vrstico = 5 in stolpec 2, bo izvlekel B5. Ker stolpec ni obvezen, tako da če ne podamo nobene vrstice št. potem bo izvlekel celoten stolpec v referenčnem območju. Na primer, če podamo vrstico = 2 in stolpec kot prazen, bo izvlekel (A2:B2). Če ne podamo vrstice in stolpca, bo izvlekel celotno referenčno tabelo, ki je (A1:B5).

Referenčna tabela: Naslednja tabela bo uporabljena kot referenčna tabela za vse primere funkcije INDEX. Prva celica je na B3 (HRANA), zadnja diagonalna celica pa na F10 (180).

Referenčna tabela

Primeri: Spodaj je nekaj primerov indeksnih funkcij.



1. primer: Vrstice in stolpci niso omenjeni.

Vhodni ukaz: =KAZALO(B3:C10)

Primer 1

2. primer: Omenjene so samo vrstice.

Vhodni ukaz: =KAZALO(B3:C10;2)

Primer 2

Primer 3: Omenjene so tako vrstice kot stolpci.

Vhodni ukaz: =INDEX(B3:D10;4;2)

Primer 3

4. primer: Omenjeni so le stolpci.

Vhodni ukaz: =INDEX(B3 : D10 , , 2)

Primer 4

Težava s funkcijo INDEX: Težava s funkcijo INDEX je, da je treba določiti vrstice in stolpce za podatke, ki jih iščemo. Predpostavimo, da imamo opravka z naborom podatkov strojnega učenja 10.000 vrstic in stolpcev, potem bo zelo težko iskati in ekstrahirati podatke, ki jih iščemo. Prihaja koncept funkcije ujemanja, ki bo identificirala vrstice in stolpce na podlagi nekega pogoja.

Funkcija MATCH

Pridobi položaj postavke/vrednosti v obsegu. To je manj izpopolnjena različica VLOOKUP-a ali HLOOKUP-a, ki vrne samo informacije o lokaciji in ne dejanskih podatkov. MATCH ne razlikuje med velikimi in malimi črkami in ga ne zanima, ali je obseg vodoravni ali navpični.

Sintaksa: 

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

Parametri:

    search_key: vrednost za iskanje. Na primer 42, Mačke ali I24. obseg: enodimenzionalna matrika za iskanje. Lahko je ena vrstica ali en stolpec. npr.->A1:A10, A2:D2 itd. search_type [izbirno]: metoda iskanja. = 1 (privzeto) najde največjo vrednost, ki je manjša ali enaka search_key, ko je obseg razvrščen v naraščajočem vrstnem redu.
    • = 0 najde točno vrednost, ko obseg ni razvrščen.
    • = -1 najde najmanjšo vrednost, ki je večja ali enaka search_key, ko je obseg razvrščen v padajočem vrstnem redu.

Številko vrstice ali številko stolpca je mogoče najti s funkcijo ujemanja in jo lahko uporabite znotraj funkcije indeksa, tako da, če obstaja kakšna podrobnost o predmetu, lahko izvlečete vse informacije o predmetu tako, da poiščete vrstico/stolpec elementa z uporabo ujemanja nato ga ugnezdi v funkcijo indeksa.

Referenčna tabela: Naslednja tabela bo uporabljena kot referenčna tabela za vse primere funkcije MATCH. Prva celica je na B3 (HRANA), zadnja diagonalna celica pa na F10 (180).

Referenčna tabela Funkcija MATCH

Primeri: Spodaj je nekaj primerov funkcije MATCH-

1. primer: Vrsta iskanja 0, pomeni natančno ujemanje.

Vhodni ukaz: =MATCH(Južna Indija,C3:C10,0)

Primer 1 MATCH

2. primer: Vrsta iskanja 1 (privzeto).

Vhodni ukaz: =MATCH(Južna Indija,C3:C10)

Primer 2 TEKMA

c oblikovani niz


Primer 3: Vrsta iskanja -1.

Vhodni ukaz: =MATCH(Južna Indija,C3:C10,-1)

Primer 3 MATCH

INDEX-MATCH Skupaj

V prejšnjih primerih so bile statične vrednosti vrstic in stolpcev podane v funkciji INDEX. Predpostavimo, da ni predznanja o položaju vrstic in stolpcev, nato pa je položaj vrstic in stolpcev mogoče zagotoviti s funkcijo MATCH. To je dinamičen način za iskanje in pridobivanje vrednosti.

Sintaksa: 

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

Referenčna tabela: Uporabljena bo naslednja referenčna tabela. Prva celica je na B3 (HRANA), zadnja diagonalna celica pa na F10 (180).

Referenčna tabela INDEX-MATCH

primer: Recimo, da je naloga najti stroške Masala Dose. Znano je, da stolpec 3 predstavlja stroške artiklov, vendar položaj vrstice Masala Dosa ni znan. Težavo lahko razdelimo na dva koraka -

Korak 1: Poiščite položaj Masala Dosa z uporabo formule:

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

Tu B3:B10 predstavlja hrano v stolpcu, 0 pa pomeni natančno ujemanje. Vrnil bo številko vrstice Masala Dosa.

2. korak: Poiščite stroške Masala Dose. Uporabite funkcijo INDEX, da poiščete ceno Masala Dose. Z zamenjavo zgornje poizvedbe funkcije MATCH znotraj funkcije INDEX na mestu, kjer se zahteva natančen položaj Masala Dosa, in je številka stolpca stroškov 3, kar je že znano.

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

INDEX-MATCH Skupaj

Dvosmerno iskanje z INDEX-MATCH skupaj

V prejšnjem primeru je bil položaj stolpca atributa Cost trdo kodiran. Torej ni bilo popolnoma dinamično.

1. primer: Predpostavimo, da tudi številka stolpca Cost ni znana, potem jo lahko dobimo s formulo:

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

Tukaj B3:F3 predstavlja stolpec glave.

2. primer: Ko sta vrednost vrstice in stolpca podana prek funkcije MATCH (brez podajanja statične vrednosti), se to imenuje dvosmerno iskanje. To je mogoče doseči s formulo:

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

Dvosmerno iskanje

Levo iskanje

Ena od ključnih prednosti funkcij INDEX in MATCH pred funkcijo VLOOKUP je zmožnost izvajanja iskanja v levo. To pomeni, da je mogoče iz katerega koli atributa na desni izluščiti položaj vrstice elementa, vrednost drugega atributa na levi pa je mogoče izluščiti.

Recimo, da kupite hrano, katere cena bi morala biti 140 Rs. Posredno pravimo, da kupite Biryani. V tem primeru je znan strošek Rs 140/-, hrano je treba izvleči. Ker je stolpec Stroški postavljen desno od stolpca Hrana. Če je uporabljen VLOOKUP, ne bo mogel iskati po levi strani stolpca Cost. Zato s funkcijo VLOOKUP ni mogoče pridobiti imena hrane.

Za premagovanje te pomanjkljivosti je mogoče uporabiti funkcijo INDEX-MATCH, levo iskanje.
Korak 1: Prvo izvleči položaj vrstice Cost 140 Rs z uporabo formule:

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

Tukaj D3: D10 predstavlja stolpec Stroški, kjer se izvaja iskanje številke vrstice Stroški 140 Rs.

2. korak: Ko pridobite številko vrstice, je naslednji korak uporaba funkcije INDEX za ekstrahiranje imena hrane s formulo:

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

Tukaj B3:B10 predstavlja stolpec s hrano, 140 pa je strošek živila.

Levo iskanje

Iskanje z upoštevanjem velikih in malih črk

Funkcija MATCH sama po sebi ne razlikuje med velikimi in malimi črkami. To pomeni, da obstaja ime hrane DHOKLA in je funkcija MATCH uporabljena z naslednjo iskalno besedo:

  1. Dhokla
  2. dhokla
  3. DhOkLA

Vsi bodo vrnili položaj vrstice DHOKLA. Vendar pa lahko funkcijo EXACT uporabite z INDEX in MATCH, da izvedete iskanje, ki upošteva velike in male črke.

Točna funkcija: Excelova funkcija EXACT primerja dva besedilna niza, pri čemer upošteva velike in male črke, ter vrne TRUE, če sta enaka, in FALSE, če nista. EXACT razlikuje med velikimi in malimi črkami.

Primeri:

    EXACT(DHOKLA,DHOKLA): To bo vrnilo True. EXACT(DHOKLA,Dhokla): To bo vrnilo False. EXACT(DHOKLA,dhokla): To bo vrnilo False. EXACT(DHOKLA,DhOkLA): To bo vrnilo False.

primer: Recimo, da je naloga iskanje vrste hrane Dhokla, vendar z upoštevanjem velikih in malih črk. To je mogoče storiti z uporabo formule-

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

Tukaj bo funkcija EXACT vrnila True, če se vrednost v stolpcu B3:B10 ujema z Dhokla z enakimi velikimi in malimi črkami, sicer bo vrnila False. Zdaj bo funkcija MATCH uporabljena v stolpcu B3:B10 in iskala vrstico z natančno vrednostjo TRUE. Po tem bo funkcija INDEX pridobila vrednost stolpca C3:C10 (stolpec vrste hrane) v vrstici, ki jo vrne funkcija MATCH.

Iskanje z upoštevanjem velikih in malih črk

Iskanje več kriterijev

Ena najtežjih težav v Excelu je iskanje na podlagi več meril. Z drugimi besedami, iskanje, ki se ujema v več kot enem stolpcu hkrati. V spodnjem primeru sta funkciji INDEX in MATCH ter logična logika uporabljeni za ujemanje v 3 stolpcih-

  1. hrana.
  2. Stroški.
  3. Količina.

Za izločitev skupnih stroškov.

primer: Recimo, da je naloga izračunati skupne stroške testenin

    Hrana: testenine. Cena: 60. Količina: 1.

V tem primeru torej obstajajo trije kriteriji za izvedbo ujemanja. Spodaj so navedeni koraki za iskanje na podlagi več meril –

Korak 1: Najprej povežite stolpec s hrano (B3:B10) s testeninami po formuli:

 'PASTA' = B3:B10>

To bo pretvorilo vrednosti B3:B10 (stolpec s hrano) v logične vrednosti. To je res, kjer je hrana testenine, drugod je napačno.

2. korak: Nato uskladite merila stroškov na naslednji način:

 60 = D3:D10>

To bo zamenjalo vrednosti D3:D10 (stolpec s stroški) kot logične. To je True, kjer je Cost=60 sicer False.

3. korak: Naslednji korak je ujemanje tretjih kriterijev, ki so Količina = 1, na naslednji način:

 1 = E3:E10>

To bo zamenjalo stolpec E3:E10 (stolpec količine) kot True, kjer je količina = 1, sicer bo nastavljeno na False.

4. korak: Pomnožite rezultat prvega, drugega in tretjega kriterija. To bo presečišče vseh pogojev in pretvorbo logične vrednosti True/False kot 1/0.

5. korak: Zdaj bo rezultat stolpec z 0 in 1. Tu uporabite funkcijo MATCH, da poiščete število vrstic stolpcev, ki vsebujejo 1. Kajti če ima stolpec vrednost 1, potem to pomeni, da izpolnjuje vse tri kriterije.

6. korak: Ko dobite številko vrstice, uporabite funkcijo INDEX, da dobite skupne stroške te vrstice.

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

Tu F3:F10 predstavlja stolpec skupnih stroškov.