Preskoči na glavno vsebino

INDEX in MATCH v več stolpcih

Če želite poiskati vrednost z ujemanjem v več stolpcih, uporabite formulo matrike, ki temelji na INDEX in MATCH funkcije, ki vključuje MMULT, TRANSPOSE in STOLPEC vam bo naredil uslugo.

indeks se ujema z več stolpci 1

Kako poiskati vrednost z ujemanjem v več stolpcih?

Če želite izpolniti ustrezen razred vsakega učenca kot je prikazano v zgornji tabeli, kjer je informacije so navedene v več stolpcih, lahko najprej uporabite trik funkcij MMULT, TRANSPOSE in COLUMN, da ustvarite matrično matriko. Potem vam bo funkcija MATCH dala položaj vaše iskalne vrednosti, ki bo napajana v INDEX, da pridobi vrednost, ki jo iščete v matriki.

Splošna skladnja

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

√ Opomba: To je matrična formula, ki zahteva, da vnesete Ctrl + Shift + Vnesite.

  • obseg_vrnitve: Obseg, iz katerega želite, da formula vrne informacije o razredu. Tukaj se nanaša na obseg razreda.
  • iskalna_vrednost: Vrednost, ki jo je formula uporabila za iskanje ustreznih informacij o razredu. Tukaj se nanaša na dano ime.
  • iskalni_array: Obseg celic, kjer je lookup_value je na seznamu; Obseg z vrednostmi za primerjavo z lookup_value. Tu se nanaša na obseg imen.
  • match_type 0: Prisili MATCH, da poišče prvo vrednost, ki je popolnoma enaka lookup_value.

Da bi našli tisto razred Jimmyja, kopirajte ali vnesite spodnjo formulo v celico H5 in pritisnite Ctrl + Shift + Vnesite da dobite rezultat:

= INDEX ($ 5 $: $ 7 $,(MACH(1,MMULT(--($C$5:$E$7=G5),PRENOSI (STOPEC($C$5:$E$7)^0)),0)))

√ Opomba: Zgornji znaki za dolar ($) označujejo absolutne reference, kar pomeni, da se ime in obsegi razredov v formuli ne bodo spremenili, ko formulo premaknete ali kopirate v druge celice. Upoštevajte, da referenci celice, ki predstavlja iskalno vrednost, ne smete dodajati znakov za dolar, saj želite, da je relativna, ko jo kopirate v druge celice. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah.

indeks se ujema z več stolpci 2

Pojasnilo formule

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

  • ---($C$5:$E$7=G5): Ta segment preveri vsako vrednost v obsegu $C$5:$E$7 če sta enaka vrednosti v celici G5, in ustvari matriko TRUE in FALSE, kot je ta:
    {TRUE, FALSE, FALSE; FALSE, FALSE, FALSE; FALSE, FALSE, FALSE}.
    Dvojni negativni bo nato pretvoril vrednosti TRUE in FALSE v 1s in 0s, da bo dal niz, kot je ta:
    {1,0,0; 0,0,0; 0,0,0}.
  • STOLPEC($C$5:$E$7): Funkcija COLUMN vrne številke stolpcev za obseg $C$5:$E$7 v nizu, kot je ta: 3,4,5 {}.
  • PRENOS (STOLPEC($C$5:$E$7)^0) = PRENOS (3,4,5 {}^0): Po dvigu moči na 0 se bodo vsa števila v matriki {3,4,5} pretvorila v 1: {1,1,1}. Funkcija TRANSPOSE nato pretvori matriko stolpcev v niz vrstic, kot je ta: {1; 1; 1}.
  • MMULT(---($C$5:$E$7=G5),PRENOS (STOLPEC($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Funkcija MMULT vrne matrični produkt dveh nizov takole: {1; 0; 0}.
  • UJEMA (1,MMULT(---($C$5:$E$7=G5),PRENOS (STOLPEC($C$5:$E$7)^0)), 0) = UJEMA (1,{1; 0; 0}, 0): Vrsta ujemanja 0 prisili funkcijo MATCH, da vrne položaj prvega ujemanja 1 v matriki {1; 0; 0}, Ki je 1.
  • KAZALO ($ 5 $: $ 7 $,(UJEMA(1,MMULT(---($C$5:$E$7=G5),PRENOS (STOLPEC($C$5:$E$7)^0)), 0))) = INDEX ($ 5 $: $ 7 $,1): Funkcija INDEX vrne 1st vrednost v razrednem območju $ 5 $: $ 7 $, Ki je A.

Če želite preprosto poiskati vrednost z ujemanjem v več stolpcih, lahko uporabite tudi naš profesionalni dodatek za Excel Kutools za Excel. Oglejte si navodila tukaj za izpolnitev misije.


Sorodne funkcije

Excel INDEX funkcija

Funkcija Excel INDEX vrne prikazano vrednost na podlagi danega položaja iz obsega ali matrike.

Excel MATCH funkcija

Funkcija Excel MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj vrednosti.

Excel MMULT funkcija

Excelova funkcija MMULT vrne matrični produkt dveh nizov. Rezultat matrike ima enako število vrstic kot matrika1 in enako število stolpcev kot matrika2.

Excel TRANSPOSE funkcija

Excelova funkcija TRANSPOSE zasuka orientacijo obsega ali matrike. Na primer, lahko zasuka tabelo, ki je vodoravno razporejena v vrsticah, navpično v stolpcih ali obratno.

Excel stolpec funkcija

Funkcija COLUMN vrne število stolpcev, v katerih se pojavi formula, ali vrne številko stolpca dane reference. Na primer, formula =COLUMN(BD) vrne 56.


Sorodne formule

Iskanje po več merilih z INDEX in MATCH

Pri obravnavi velike zbirke podatkov v Excelovi preglednici z več stolpci in napisi vrstic je vedno težko najti nekaj, kar ustreza več kriterijem. V tem primeru lahko uporabite matrično formulo s funkcijami INDEX in MATCH.

Dvosmerno iskanje z INDEX in MATCH

Za iskanje nečesa v vrsticah in stolpcih v Excelu ali pa rečemo, da poiščemo vrednost na presečišču določene vrstice in stolpca, lahko uporabimo pomoč funkcij INDEX in MATCH.

Poiščite vrednost najbližjega ujemanja z več merili

V nekaterih primerih boste morda morali poiskati najbližjo ali približno vrednost ujemanja na podlagi več meril. S kombinacijo funkcij INDEX, MATCH in IF lahko to hitro opravite v Excelu.


Najboljša orodja za pisarniško produktivnost

Kutools za Excel - vam pomaga izstopati iz množice

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike  |  Izbriši prazne vrstice  |  Združite stolpce ali celice brez izgube podatkov  |  Krog brez formule ...
Super VLookup: Več meril  |  Več vrednosti  |  Na več listih  |  Nejasno iskanje...
Adv. Spustni seznam: Preprost spustni seznam  |  Odvisni spustni seznam  |  Večkrat izberite spustni seznam...
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  Primerjaj stolpce z Izberite Enake in različne celice ...
Predstavljene funkcije: Mrežni fokus  |  Pogled oblikovanja  |  Velika vrstica formule  |  Upravitelj delovnih zvezkov in listov | Knjižnica virov (Samodejno besedilo)  |  Izbirnik datuma  |  Združite delovne liste  |  Šifriranje/dešifriranje celic  |  Pošljite e-pošto po seznamu  |  Super filter  |  Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake ...)  |  50 + Graf Vrste (Gantt Chart ...)  |  40+ Praktično Formule (Izračunajte starost glede na rojstni dan ...)  |  19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti ...)  |  12 Pretvorba Orodja (Številke v besede, Pretvorba valut ...)  |  7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeli Excelove celice ...)  |  ... in več

Kutools za Excel se ponaša z več kot 300 funkcijami, Zagotavljanje, da je vse, kar potrebujete, le en klik stran ...


Kartica Office - omogočite branje in urejanje z zavihki v programu Microsoft Office (vključite Excel)

  • Eno sekundo za preklop med desetinami odprtih dokumentov!
  • Vsak dan zmanjšajte na stotine klikov z miško, poslovite se od roke miške.
  • Poveča vašo produktivnost za 50% pri ogledu in urejanju več dokumentov.
  • Prinaša učinkovite zavihke v Office (vključno z Excelom), tako kot Chrome, Edge in Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations