Note: The other languages of the website are Google-translated. Back to English
Vpiši se  \/ 
x
or
x
Registracija  \/ 
x

or

Kako uporabljati INDEX in MATCH skupaj v Excelu

Pri delu z Excelovimi tabelami lahko nenehno najdete situacije, v katerih morate poiskati vrednost. V tej vadnici vam bomo pokazali, kako uporabiti kombinacijo funkcij INDEX in MATCH za vodoravna in navpična iskanja, dvosmerna iskanja, iskanja, občutljiva na velike in male črke, in iskanja, ki izpolnjujejo več kriterijev.

Kaj počneta funkciji INDEX in MATCH v Excelu

Kako uporabljati funkcije INDEX in MATCH skupaj


Kaj počneta funkciji INDEX in MATCH v Excelu

Preden uporabimo funkciji INDEX in MATCH, se prepričajmo, da vemo, kako nam lahko INDEX in MATCH najprej pomagata pri iskanju vrednosti.

Uporaba funkcije INDEX v Excelu

O INDEX funkcija v Excelu vrne vrednost na danem mestu v določenem obsegu. Sintaksa funkcije INDEX je naslednja:

=INDEX(array, row_num, [column_num])
  • matrika (obvezno) se nanaša na obseg, iz katerega želite vrniti vrednost.
  • vrstica_num (obvezno, razen če je prisotna številka_stolpca) se nanaša na številko vrstice matrike.
  • stolpec_številka (neobvezno, vendar obvezno, če je številka_vrstice izpuščena) se nanaša na številko stolpca matrike.

Na primer vedeti končni rezultat izpita Jeffa, 6. učenec na seznamu, lahko uporabite funkcijo INDEX takole:

= INDEKS (E2: E11, 6) >>> vrne 60

ujemanje indeksa Excel 01

√ Opomba: obseg E2: E11 je tam, kjer je naveden zaključni izpit, medtem ko je številka 6 najde izpitno oceno 6th študent.

Tukaj naredimo majhen test. Za formulo =INDEX(B2:E2,3), kakšno vrednost bo vrnil? ---Da, vrnilo se bo Kitajskaje 3. vrednost v danem območju.

Zdaj bi morali vedeti, da lahko funkcija INDEX odlično deluje z vodoravnimi ali navpičnimi razponi. Kaj pa, če ga potrebujemo, da vrne vrednost v večjem obsegu z več vrsticami in stolpci? No, v tem primeru bi morali uporabiti tako številko vrstice kot številko stolpca. Na primer, da bi izvedeli dežela, iz katere prihaja Emily z INDEX lahko poiščemo vrednost s številko vrstice 8 in številko stolpca 3 v celicah od B2 do E11 takole:

=INDEX(B2:E11,8,3) >>> vrne Kitajska

ujemanje indeksa Excel 02

Glede na zgornje primere, o funkciji INDEX v Excelu morate vedeti, da:

  • Funkcija INDEX lahko deluje z navpičnimi in vodoravnimi razponi.
  • Funkcija INDEX ni občutljiva na velike in male črke.
  • Številka vrstice je pred številko stolpca (če potrebujete obe številki) v formuli INDEX.

Vendar pa za res veliko bazo podatkov z več vrsticami in stolpci zagotovo ni priročno, da uporabimo formulo z natančno številko vrstice in številko stolpca. In to je to, ko bi morali kombinirati uporabo funkcije MATCH.

Zdaj pa se najprej seznanimo z osnovami funkcije MATCH.


Uporaba funkcije MATCH v Excelu

Funkcija MATCH v Excelu vrne številsko vrednost, lokacijo določenega elementa v danem obsegu. Sintaksa funkcije MATCH je naslednja:

=MATCH(lookup_value, lookup_array, [match_type])
  • iskalni_array (obvezno) se nanaša na obseg celic, kjer želite, da MATCH išče.
  • match_type (neobvezno), 1, 0 or -1:
  • 1(privzeto) bo MATCH našel največjo vrednost, ki je manjša ali enaka lookup_value. Vrednosti v iskalni_array mora biti postavljen v naraščajočem vrstnem redu.
  • 0, MATCH bo našel prvo vrednost, ki je natančno enaka lookup_value. Vrednosti v iskalni_array lahko v poljubnem vrstnem redu. (V primerih, ko je vrsta ujemanja nastavljena na 0, lahko uporabite nadomestne znake.)
  • -1, MATCH bo našel najmanjšo vrednost, ki je večja ali enaka lookup_value. Vrednosti v iskalni_array morajo biti postavljene v padajočem vrstnem redu.

Na primer vedeti mesto Vere na seznamu imen, lahko uporabite formulo MATCH takole:

= MATCH ("vera", C2: C11,0) >>> vrne 4

ujemanje indeksa Excel 03

√ Opomba: funkcija MATCH ni občutljiva na velike in male črke. Rezultat »4« pomeni, da je ime »Vera« na četrtem mestu seznama. »4« v formuli je vrsta ujemanja, ki bo našla prvo vrednost v iskalnem nizu, ki je natančno enaka iskalni vrednosti »Vera«.

Vedeti položaj točke "96" v vrsti od B2 do E2, lahko uporabite MATCH takole:

=MACH(96,B2:E2,0) >>> vrne 4

ujemanje indeksa Excel 04

☞ Stvari, ki jih moramo vedeti o funkciji MATCH v Excelu:

  • Funkcija MATCH vrne položaj iskane vrednosti v iskalnem nizu, ne same vrednosti.
  • Funkcija MATCH vrne prvo ujemanje v primeru dvojnikov.
  • Tako kot funkcija INDEX lahko tudi funkcija MATCH deluje z navpičnimi in vodoravnimi razponi.
  • MATCH tudi ni občutljiv na velike in male črke.
  • Če je iskalna vrednost formule MATCH v obliki besedila, jo postavite v narekovaje.

Zdaj, ko vemo o osnovnih rabah funkcij INDEX in MATCH v Excelu, zavihajmo rokave in se pripravimo združiti obe funkciji.


Kako uporabljati funkcije INDEX in MATCH skupaj

V tem delu bomo govorili o različnih okoliščinah za uporabo funkcij INDEX in MATCH za izpolnjevanje različnih potreb.

Primer za kombiniranje INDEX in MATCH

Oglejte si spodnji primer, da ugotovite, kako lahko združimo funkcije INDEX in MATCH:

Na primer vedeti Evelyn končni rezultat izpita, moramo uporabiti formulo:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> vrne 90

ujemanje indeksa Excel 05

No, ker je formula morda videti zapletena, pojdimo skozi vsak njen del.

ujemanje indeksa Excel 06

Kot lahko vidite zgoraj, velika INDEX formula vsebuje tri argumente:

  • matrika: A2: D11 pove INDEX, naj vrne ujemajočo se vrednost iz celic do konca A2 do D11.
  • vrstica_num: MATCH("evelyn",B2:B11,0) pove INDEX točno vrstico vrednosti.
  • Glede formule MATCH jo lahko razložimo tako: vrniti položaj prve vrednosti, ki je natančno enak “evelyn” v celicah od B2 do B11 v številski vrednosti, ki je 5.
  • stolpec_številka: MATCH("zaključni izpit",A1:D1,0) pove INDEX točen stolpec vrednosti.
  • Glede formule MATCH jo lahko razložimo tako: vrniti položaj prve vrednosti, ki je natančno enak »končnemu izpitu« v celicah od A1 do D1 v številski vrednosti, ki je 4.

Torej lahko vidite veliko formulo tako preprosto, kot smo jo prikazali spodaj:

= INDEX (A2: D11,5,4)

V primeru smo uporabili trdo kodirane vrednosti, "evelyn" in "zaključni izpit". Vendar v tako veliki formuli ne želimo trdo kodiranih vrednosti, saj jih bomo morali spremeniti vsakič, ko bomo iskali nekaj novega. V takih okoliščinah lahko z uporabo sklicev na celice naredimo formulo tako dinamično:

= INDEX (A2: D11,UJEMA (G2,B2:B11,0),UJEMA (F3,A1:D1,0))

ujemanje indeksa Excel 07


INDEX in MATCH, da uporabite levo iskanje

Recimo, da morate poznati Evelyn razred, kako lahko uporabimo INDEX in MATCH, da poznamo odgovor? Če ste bili pozorni, bi morali opaziti, da je stolpec razreda na levi strani stolpca z imenom in je zunaj zmožnosti druge Excelove zmogljive funkcije iskanja, VLOOKUP.

Pravzaprav je leva zmožnost iskanja eden od vidikov, kjer je kombinacija INDEX in MATCH boljša od VLOOKUP.

Vedeti Evelyn razred, vse kar morate storiti je, da spremenite vrednost v celici F3 v "Razred" in uporabite isto formulo, kot je prikazano zgoraj, funkciji INDEX in MATCH vam bosta nato takoj povedali odgovor:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> vrne A

ujemanje indeksa Excel 08

Če imate inštalvodila Kutools za Excel, profesionalni dodatek za Excel, ki ga je razvila naša ekipa, lahko uporabite tudi levo iskanje za določene vrednosti z njegovim POGLED od desne proti levi funkcija z nekaj kliki. Za implementacijo funkcije pojdite na Kutools zavihek v Excelu, poiščite Formula in kliknite POGLED od desne proti levi na spustnem seznamu Super POGLED. Videli boste pojavno pogovorno okno, kot je to:

ujemanje indeksa Excel 09

Kliknite tukaj za konkretne korake za uporabo leve funkcije iskanja s Kutools za Excel.


INDEX in MATCH, da uporabite dvosmerno iskanje

Ali lahko zdaj ustvarite kombinacijo INDEX in MATCH z dinamičnimi vrednostmi iskanja za uporabo dvosmernih iskanj? Vadimo izdelavo formul v celicah G3, G4 in G5, kot je prikazano spodaj:

ujemanje indeksa Excel 10

Tukaj so odgovori:

Celica G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Celica G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Celica G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Opomba: Po uporabi formul lahko zlahka dobite informacije o vseh študentih tako, da spremenite ime v celici G2.


INDEX in MATCH, da uporabite iskanje, ki razlikuje velike in male črke

Iz zgornjih primerov vemo, da funkciji INDEX in MATCH nista občutljivi na velike in male črke. V primerih, ko potrebujete svojo formulo za razlikovanje velikih in malih črk, lahko dodate Točno deluje na vaše formule takole:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Opomba: To je matrična formula, ki zahteva, da vnesete Ctrl + Shift + Enter. V vrstici s formulo se bo nato prikazal par zavitih oklepajev.

Na primer vedeti JIMMY-jev rezultat na izpitu, uporabite takšne funkcije:

ujemanje indeksa Excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> vrne 86

Lahko pa uporabite tudi sklice na celice:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> vrne 86
Opomba: Ne pozabite vnesti z Ctrl + Shift + Enter.


INDEX in MATCH za uporabo iskanja z več merili

Ko se ukvarjate z veliko zbirko podatkov z več stolpci in podnapisi, je vedno težko najti nekaj, kar ustreza več pogojem. V tem primeru si oglejte spodnjo formulo za iskanje po več merilih:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Opomba: to je formula matrike, ki zahteva, da vnesete z Ctrl + Shift + Enter. V vrstici s formulo se bo nato prikazal par zavitih oklepajev.

Na primer, da bi našli končni izpit Coco razreda A, ki je iz Indije, formula je naslednja:

ujemanje indeksa Excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> vrne 88
√ Opomba: ne pozabite vnesti s Ctrl + Shift + Enter.

No, kaj če nenehno pozabljate uporabljati Ctrl + Shift + Enter dokončati formulo, tako da formula vrne napačne rezultate? Tukaj imamo bolj zapleteno formulo, s katero lahko dopolnite z eno preprosto Vnesite ključ:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Za isti primer zgoraj, da poiščete končni izpit Coco razreda A, ki je iz Indije, formula, ki potrebuje samo običajno Vnesite zadetek je naslednji:

ujemanje indeksa Excel 13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> vrne 88

Tukaj ne bomo uporabljali trdo kodiranih vrednosti, saj bomo želeli univerzalno formulo v primeru več kriterijev. Le tako lahko enostavno dosežemo želeni rezultat s spreminjanjem vrednosti v celicah G2, G3, G4 v zgornjem primeru.

z Kutools za Excel"s Funkcija iskanja v več pogojih, lahko z nekaj kliki poiščete določene vrednosti z več merili. Za implementacijo funkcije pojdite na Kutools zavihek v Excelu, poiščite Formula in kliknite Iskanje v več pogojih na spustnem seznamu Super POGLED. Nato boste videli pojavno pogovorno okno, kot je prikazano spodaj:

ujemanje indeksa Excel 14

Kliknite tukaj za konkretne korake za uporabo funkcije iskanja v več pogojih s Kutools za Excel.


INDEX in MATCH, da uporabite iskanje v več stolpcih

Če imamo Excelovo preglednico z različnimi stolpci, ki delijo en napis, kot je prikazano spodaj, kako lahko povežemo ime vsakega učenca z njegovim/njenim razredom z INDEX in MATCH?

ujemanje indeksa Excel 15

Naj vam pokažem, kako dokončati nalogo z našim profesionalnim orodjem Kutools za Excel. S svojim pomočnik formule, študente lahko hitro povežete z njihovimi razredi, kot je prikazano spodaj:

1. Izberite ciljno celico, v kateri želite uporabiti funkcijo.

2. Pod Kutools jeziček, pojdi na Pomočnik za formulo, Kliknite Pomočnik za formulo na spustnem seznamu.

ujemanje indeksa Excel 16

3. Izberite Iskanje iz vrste formule, nato kliknite na Kazalo in ujemanje v več stolpcih.

ujemanje indeksa Excel 17

4. a. Kliknite 1. ikona indeksa ujemanja Excelgumb na desni strani Iskanje_col da izberete celice, iz katerih želite vrniti vrednost, tj. imena razredov. (Tu lahko izberete samo en stolpec ali vrstico.)
    b. Kliknite 2 ikona indeksa ujemanja Excelgumb na desni strani Tabela_rng da izberete celice, ki se ujemajo z vrednostmi v izbranem Iskanje_col, tj. imena učencev.
    c. Kliknite 3 ikona indeksa ujemanja Excelgumb na desni strani Iskalna_vrednost da izberete celico, ki jo želite poiskati, tj. ime študenta, ki ga želite povezati z njegovim/njenim razredom.

ujemanje indeksa Excel 18

5. Kliknite V redu, v ciljni celici boste videli ime razreda Jimmyja.

ujemanje indeksa Excel 19

6. Zdaj lahko povlečete ročico za polnjenje navzdol, da zapolnite razrede drugih učencev.

ujemanje indeksa Excel 20

Kliknite, če želite prenesti Kutools za Excel za 30-dnevno brezplačno preskusno različico.



  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.