INDEX in MATCH z več nizi
Recimo, da imate več tabel z enakimi napisi, kot je prikazano spodaj, za iskanje vrednosti, ki se ujemajo z merili podajanja iz teh tabel, bo morda težko delo. V tej vadnici se bomo pogovarjali o tem, kako poiskati vrednost v več matrikah, obsegih ali skupinah z ujemanjem določenih meril z INDEX, MATCH in IZBERITE funkcije.
Kako poiskati vrednost v več nizih?
Če želite poznati vodje različnih skupin, ki pripadajo različnim oddelkom, lahko najprej uporabite funkcijo IZBIRA, da ciljate na tabelo, iz katere želite vrniti ime voditelja. Funkcija MATCH bo nato ugotovila položaj vodilnega v tabeli, kamor spada. Nazadnje bo funkcija INDEX pridobila vodilnega na podlagi informacij o položaju in posebnega stolpca, kjer so navedena imena vodilnih.
Splošna skladnja
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Številka CHOOSE, ki se uporablja za označevanje matrike s seznama niz1, niz2,… vrniti rezultat iz.
- niz1, niz2,…: Nizi, iz katerih se vrne rezultat. Tukaj se nanaša na tri tabele.
- iskalna_vrednost: Vrednost, ki jo je kombinirana formula uporabila za iskanje položaja ustreznega vodje. Tukaj se nanaša na dano skupino.
- iskalni_array: Obseg celic, kjer je lookup_value je na seznamu. Tukaj se nanaša na obseg skupine. Opomba: Obseg skupine lahko uporabite iz katerega koli oddelka, saj so vsi enaki in moramo dobiti samo številko položaja.
- stolpec_številka: Stolpec, ki ga navedete, iz katerega želite pridobiti podatke.
Če želite poznati vodja skupine D, ki pripada oddelku A, kopirajte ali vnesite spodnjo formulo v celico G5 in pritisnite Vnesite da dobite rezultat:
=INDEX(IZBERI(1,5 B$: 8 C$,11 B$: 14 C$,17 B$: 20 C$),MACH(F5,$ 5 $: $ 8 $,0),2)
√ 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. Ko vnesete formulo, povlecite ročico za polnjenje navzdol, da uporabite formulo v spodnjih celicah, in nato spremenite matrika št ustrezno.
Pojasnilo formule
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Funkcija CHOOSE vrne 1st matrike iz treh nizov, navedenih v formuli. Torej se bo vrnilo 5 B$: 8 C$, tj obseg podatkov oddelka A.
- MACH(F5,$B$5:$B$8,0): Vrsta ujemanja 0 prisili funkcijo MATCH, da vrne položaj prvega ujemanja Skupina D, vrednost v celici F5, v nizu $ 5 $: $ 8 $, Ki je 4.
- KAZALO (CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MACH(F5,$B$5:$B$8,0),2) = INDEX (5 B$: 8 C$,4,2): Funkcija INDEX pridobi vrednost na presečišču 4th vrstici in 2nd stolpec obsega 5 B$: 8 C$, Ki je Emily.
Da bi se izognili spremembam matrika št v formuli vsakič, ko jo kopirate, lahko uporabite stolpec za pomočnike, stolpec D. Formula bi bila taka:
=INDEX(IZBERI(D5,5 B$: 8 C$,11 B$: 14 C$,17 B$: 20 C$),MACH(F5,$ 5 $: $ 8 $,0),2)
√ Opomba: številke 1, 2, 3 v stolpcu za pomočnike navedite matrika 1, matrika 2, matrika 3 znotraj funkcije IZBIRA.
Sorodne funkcije
Funkcija Excel INDEX vrne prikazano vrednost na podlagi danega položaja iz obsega ali matrike.
Funkcija Excel MATCH išče določeno vrednost v obsegu celic in vrne relativni položaj vrednosti.
Funkcija CHOOSE vrne vrednost iz argumenta seznama vrednosti na podlagi podane številke indeksa. Na primer, CHOOSE (3, »Apple«, »Peach«, »Orange«) vrne oranžno, številka indeksa je 3 in oranžna je tretja vrednost za številko indeksa v funkciji.
Sorodne formule
Iskanje vrednosti iz drugega delovnega lista ali delovnega zvezka
Če veste, kako uporabljati funkcijo VLOOKUP za iskanje vrednosti na delovnem listu, vam vrednosti vlookup iz drugega delovnega lista ali delovnega zvezka ne bodo predstavljale težav.
Vlookup z dinamičnim imenom lista
V mnogih primerih boste morda morali podatke povzeti na več delovnih listih. S kombinacijo funkcije VLOOKUP in funkcije POSREDNO lahko ustvarite formulo za iskanje določenih vrednosti na delovnih listih z dinamičnim imenom lista.
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.
Najboljša orodja za pisarniško produktivnost
Kutools za Excel - vam pomaga izstopati iz množice
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.