Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Kita sadurunge wis nerangake kanggo pamula carane nggunakake fungsi dhasar saka VLOOKUP (Inggris VLOOKUP, singkatan kanggo "vertical lookup function"). Lan pangguna sing berpengalaman ditampilake sawetara rumus sing luwih rumit.

Lan ing artikel iki kita bakal nyoba menehi informasi babagan cara liya nggarap telusuran vertikal.

Sampeyan bisa uga mikir: "Yagene iki perlu?". Lan iki perlu kanggo nuduhake kabeh cara telusuran sing bisa ditindakake. Kajaba iku, akeh watesan VLOOKUP asring nyegah asil sing dikarepake. Ing babagan iki, INDEX () MATCH () luwih fungsional lan maneka warna, lan uga duwe watesan sing luwih sithik.

Dhasar INDEX MATCH

Wiwit tujuan pandhuan iki kanggo nuduhake carane apik fitur iki, kita Ayo goleki informasi dhasar babagan prinsip operasi. Lan kita bakal nuduhake conto, lan uga nimbang apa, iku luwih apik tinimbang VLOOKUP ().

Sintaks Fungsi INDEX lan Panggunaan

Fungsi iki mbantu nemokake nilai sing dikarepake ing antarane area telusuran sing ditemtokake adhedhasar nomer kolom utawa baris. Sintaks:

=INDEX(larik, nomer baris, nomer kolom):

  • larik - wilayah ing ngendi panelusuran bakal ditindakake;
  • nomer baris - nomer baris sing bakal digoleki ing array sing ditemtokake. Yen nomer baris ora dingerteni, nomer kolom kudu ditemtokake;
  • nomer kolom - nomer kolom sing bakal ditemokake ing array sing ditemtokake. Yen nilai ora dingerteni, nomer baris dibutuhake.

Conto rumus prasaja:

=INDEX(A1:S10,2,3)

Fungsi kasebut bakal nggoleki ing kisaran saka A1 nganti C10. Angka nuduhake kang baris (2) lan kolom (3) kanggo nuduhake nilai sing dipengini saka. Asil bakal dadi sel C2.

Cukup prasaja, bener? Nanging nalika sampeyan nggarap dokumen nyata, sampeyan ora duwe informasi babagan nomer kolom utawa sel. Sing apa MATCH () fungsi kanggo.

Sintaks lan Panggunaan Fungsi MATCH

Fungsi MATCH () nggoleki nilai sing dikarepake lan nuduhake nomer kira-kira ing area panelusuran sing ditemtokake.

Sintaks searchpos() katon kaya iki:

=MATCH(nilai kanggo nggoleki, array kanggo nggoleki, jinis pencocokan)

  • nilai telusuran - nomer utawa teks sing bakal ditemokake;
  • larik sing digoleki - wilayah sing bakal digoleki;
  • jinis pencocokan - nemtokake manawa arep nggoleki nilai sing tepat utawa nilai sing paling cedhak:
    • 1 (utawa ora ana nilai sing ditemtokake) - ngasilake nilai paling gedhe sing padha karo utawa kurang saka nilai sing ditemtokake;
    • 0 - nuduhake sing cocog karo nilai sing digoleki. Ing kombinasi INDEX () MATCH () sampeyan bakal meh tansah kudu cocog pas, supaya kita nulis 0;
    • -1 - Nuduhake nilai paling cilik sing luwih gedhe utawa padha karo nilai sing ditemtokake ing rumus. Urut ditindakake kanthi urutan mudhun.

Contone, ing kisaran B1:B3 New York, Paris, London didaftar. Rumus ing ngisor iki bakal nuduhake nomer 3 amarga London nomer telu ing dhaptar:

=EXPOSE(London,B1:B3,0)

Carane nggarap fungsi INDEX MATCH 

Sampeyan bisa uga wis wiwit ngerti prinsip sing nggawe karya gabungan saka fungsi kasebut. Ing cendhak, banjur INDEX () nggoleki nilai sing dikarepake ing antarane baris lan kolom sing ditemtokake. Lan MATCH () nuduhake nomer nilai kasebut:

=INDEX(kolom saka ngendi nilai bali, MATCH(nilai kanggo nggoleki, kolom kanggo nggoleki, 0))

Isih angel ngerti cara kerjane? Mungkin conto bakal nerangake luwih apik. Upaminipun sampeyan duwe dhaptar ibukutha donya lan populasi:

Kanggo ngerteni ukuran populasi ibukutha tartamtu, contone, ibukutha Jepang, kita nggunakake rumus ing ngisor iki:

=INDEX(C2:C10, MATCH(Jepang, A2:A10,0))

Panjelasan:

  • MATCH () fungsi katon kanggo Nilai - "Jepang" ing array A2: A10 lan ngasilake nomer 3, amarga Jepang minangka nilai katelu ing dhaftar. 
  • tokoh iki menyangnomer baris"ing rumus INDEX () lan ngandhani fungsi kanggo print Nilai saka baris iki.

Dadi rumus ing ndhuwur dadi rumus standar INDEKS(C2:C10,3). Rumus nggoleki saka sel C2 nganti C10 lan ngasilake data saka sel katelu ing kisaran iki, yaiku, C4, amarga countdown diwiwiti saka baris kapindho.

Ora pengin menehi resep jeneng kutha ing rumus? Banjur nulis ing sel sembarang, ngandika F1, lan digunakake minangka referensi ing MATCH () rumus. Lan sampeyan bakal duwe rumus telusuran dinamis:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Penting! Jumlah baris ing Uploaded INDEX () kudu padha karo nomer baris ing dianggep array ing MATCH (), yen ora, sampeyan bakal entuk asil salah.

Ngenteni menit, apa ora mung nggunakake rumus VLOOKUP ()?

=VLOOKUP(F1, A2:C10, 3, Palsu)

 Apa gunane mbuwang wektu nyoba ngerteni kabeh kerumitan INDEX MATCH iki?

Ing kasus iki, iku ora Matter kang fungsi kanggo nggunakake. Iki mung conto kanggo ngerti carane INDEX () lan MATCH () fungsi bisa bebarengan. Conto liyane bakal nuduhake apa fungsi kasebut bisa ditindakake ing kahanan nalika VLOOKUP ora duwe daya. 

INDEX MATCH utawa VLOOKUP

Nalika nemtokake rumus panelusuran sing bakal digunakake, akeh sing setuju yen INDEX () lan MATCH () luwih unggul tinimbang VLOOKUP. Nanging, akeh wong isih nggunakake VLOOKUP (). Kaping pisanan, VLOOKUP () luwih prasaja, lan kapindho, pangguna ora ngerti kabeh keuntungan saka nggarap INDEX () lan MATCH (). Tanpa kawruh iki, ora ana sing setuju kanggo nglampahi wektu sinau sistem Komplek.

Mangkene kaluwihan utama INDEX () lan MATCH () tinimbang VLOOKUP ():

 

  • Telusuri saka tengen ngiwa. VLOOKUP() ora bisa nelusur saka tengen ngiwa, mula nilai sing digoleki kudu ana ing kolom paling kiwa ing tabel. Nanging INDEX () lan MATCH () bisa nangani iki tanpa masalah. Artikel iki bakal ngandhani apa sing katon ing praktik: carane nemokake nilai sing dikarepake ing sisih kiwa.

 

  1. Tambahan aman utawa mbusak kolom. Rumus VLOOKUP () nuduhake asil sing salah nalika mbusak utawa nambah kolom amarga VLOOKUP () mbutuhake nomer kolom sing tepat supaya bisa sukses. Alami, nalika kolom ditambahake utawa dibusak, nomer kasebut uga ganti. 

Lan ing rumus INDEX () lan MATCH (), sawetara kolom ditemtokake, ora kolom individu. Akibaté, sampeyan bisa nambah lan mbusak kolom kanthi aman tanpa kudu nganyari rumus saben wektu.

  1. Ora ana watesan ing volume panelusuran. Nalika nggunakake VLOOKUP (), jumlah total kritéria panelusuran kudu ora ngluwihi 255 karakter utawa sampeyan bakal entuk #VALUE! Dadi yen data sampeyan ngemot akeh karakter, INDEX () lan MATCH () minangka pilihan sing paling apik.
  2. Kacepetan pangolahan dhuwur. Yen tabel sampeyan relatif cilik, mula sampeyan ora bakal weruh prabédan. Nanging, yen tabel ngemot atusan utawa ewu larik, lan, kanthi mangkono, ana atusan lan ewu rumus, INDEX () lan MATCH () bakal luwih cepet tinimbang VLOOKUP (). Kasunyatane yaiku Excel mung bakal ngolah kolom sing ditemtokake ing rumus, tinimbang ngolah kabeh tabel. 

Dampak kinerja VLOOKUP () bakal katon utamane yen lembar kerja sampeyan ngemot akeh rumus kaya VLOOKUP () lan SUM (). Pemriksaan kapisah saka fungsi VLOOKUP () dibutuhake kanggo ngurai saben nilai ing array. Dadi Excel kudu ngolah informasi sing akeh banget, lan iki bakal nyuda karya kanthi signifikan.

Tuladha Formula 

Kita wis ngerteni kegunaan fungsi kasebut, supaya kita bisa nerusake menyang bagean sing paling menarik: aplikasi kawruh ing praktik.

Formula kanggo nggoleki saka tengen ngiwa

Kaya sing wis kasebut, VLOOKUP ora bisa nindakake telusuran iki. Dadi, yen nilai sing dikarepake ora ana ing kolom paling kiwa, VLOOKUP() ora bakal ngasilake asil. INDEX () lan MATCH () fungsi luwih Versatile, lan lokasi nilai ora muter peran gedhe kanggo wong-wong mau bisa.

Contone, kita bakal nambah kolom pangkat ing sisih kiwa meja kita lan nyoba kanggo nemtokake pangkat apa ing babagan populasi ibukutha Negara Kita.

Ing sel G1, kita nulis nilai sing bakal ditemokake, banjur gunakake rumus ing ngisor iki kanggo nggoleki ing kisaran C1:C10 lan ngasilake nilai sing cocog saka A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Prompt. Yen sampeyan arep nggunakake rumus iki kanggo sawetara sel, priksa manawa sampeyan ndandani kisaran nggunakake alamat absolut (contone, $A$2: $A$10 lan $C$2: 4C$10).

INDEKS MORE EXPOSED MORE EXPOSED  kanggo nggoleki ing kolom lan larik

Ing conto ing ndhuwur, kita wis nggunakake fungsi kasebut minangka panggantos kanggo VLOOKUP () kanggo ngasilake nilai saka sawetara baris sing wis ditemtokake. Nanging apa yen sampeyan kudu nindakake matriks utawa telusuran loro-lorone?

Iku muni rumit, nanging rumus kanggo petungan kuwi padha karo standar INDEX () MATCH () rumus, karo mung siji prabédan: MATCH () rumus kudu digunakake kaping pindho. Pisanan entuk nomer baris, lan kaping pindho entuk nomer kolom:

=INDEX(larik, MATCH(nilai telusuran vertikal, kolom telusuran, 0), MATCH(nilai telusuran horisontal, baris telusuran, 0))

Coba deleng tabel ing ngisor iki lan coba gawe rumus INDEKS() EXPRESS() EXPRESS() kanggo nampilake demografi ing negara tartamtu kanggo taun sing dipilih.

Negara target ana ing sel G1 (lookup vertikal) lan taun target ana ing sel G2 (lookup horizontal). Rumus bakal katon kaya iki:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Carane rumus iki dianggo

Kaya rumus komplèks liyane, luwih gampang dimangerteni kanthi dipérang dadi persamaan individu. Banjur sampeyan bisa ngerti apa fungsi saben individu:

  • PERTANDINGAN(G1,A2:A11,0) - goleki nilai (G1) ing kisaran A2: A11 lan nuduhake nomer nilai kasebut, ing kasus kita yaiku 2;
  • GOLEKSI(G2,B1:D1,0) – nggoleki nilai (G2) ing kisaran B1:D1. Ing kasus iki, asil ana 3.

Nomer baris lan kolom sing ditemokake dikirim menyang nilai sing cocog ing rumus INDEX ():

=INDEX(B2:D11,2,3)

Akibaté, kita duwe nilai sing ana ing sel ing persimpangan 2 larik lan 3 kolom ing kisaran B2:D11. Lan rumus nuduhake nilai sing dikarepake, sing ana ing sel D3.

Telusuri kanthi macem-macem kahanan kanthi INDEX lan MATCH

Yen sampeyan wis maca pandhuan kanggo VLOOKUP (), sampeyan mbokmenawa wis nyoba sawetara rumus telusuran. Nanging cara telusuran iki nduweni watesan sing signifikan - kudu nambah kolom tambahan.

Nanging kabar apik iku Kanthi INDEX () lan MATCH () sampeyan bisa nelusuri sawetara kahanan tanpa kudu ngowahi utawa ngganti lembar kerja.

Punika rumus panelusuran multi-kahanan umum kanggo INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Cathetan: rumus iki kudu digunakake bebarengan karo trabasan keyboard CTRL+SHIFT+ENTER.

Upamane sampeyan kudu nemokake nilai sing digoleki adhedhasar 2 kahanan: Buyer и Produk.

Iki mbutuhake rumus ing ngisor iki:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Ing rumus iki, C2:C10 minangka kisaran sing bakal ditindakake telusuran, F1 - kahanan iki, A2:A10 — yaiku kisaran kanggo mbandhingake kahanan, F2 - syarat 2, v2:v10 - kisaran kanggo mbandhingake kondisi 2.

Aja lali pencet kombinasi ing mburi karya karo rumus CTRL+SHIFT+ENTER - Excel bakal nutup rumus kanthi otomatis nganggo kurung kriting, kaya sing ditampilake ing conto:

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Yen sampeyan ora pengin nggunakake rumus array kanggo karya, banjur nambah liyane INDEX () kanggo rumus lan penet ENTER, bakal katon kaya ing conto:

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Carane rumus iki bisa

Rumus iki dianggo kanthi cara sing padha karo rumus INDEX () MATCH () standar. Kanggo nggoleki macem-macem kahanan, sampeyan mung nggawe sawetara kahanan Palsu lan Bener sing makili kahanan individu sing bener lan salah. Banjur kahanan kasebut ditrapake kanggo kabeh unsur array sing cocog. Rumus kasebut ngowahi argumen False lan True dadi 0 lan 1, lan ngasilake array ing ngendi 1 minangka nilai sing cocog sing ditemokake ing string. MATCH () bakal nemokake nilai pisanan sing cocog karo 1 lan pass menyang rumus INDEX (). Lan, bakal ngasilake nilai sing wis dikarepake ing baris kasebut saka kolom sing dikarepake.

A rumus tanpa Uploaded gumantung ing kemampuan INDEX () kanggo nangani dhewe. Kapindho INDEX () ing rumus cocog palsu (0), supaya nglewati kabeh array karo nilai kasebut menyang rumus MATCH (). 

Iki minangka panjelasan sing rada dawa babagan logika ing rumus iki. Kanggo informasi luwih lengkap maca artikel "INDEX MATCH karo sawetara kahanan".

AVERAGE, MAX lan MIN ing INDEX lan MATCH

Excel nduweni fungsi khusus dhewe kanggo nemokake rata-rata, maksimal, lan minimal. Nanging kepiye yen sampeyan pengin entuk data saka sel sing ana gandhengane karo nilai kasebut? Ing kasus iki AVERAGE, MAX lan MIN kudu digunakake bebarengan karo INDEX lan MATCH.

INDEX MATCH lan MAX

Kanggo nemokake nilai paling gedhe ing kolom D lan nampilake ing kolom C, gunakake rumus: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH lan MIN

Kanggo nemokake nilai paling cilik ing kolom D lan nampilake ing kolom C, gunakake rumus ing ngisor iki:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SEARCH INDEX lan SERPENT

Kanggo nemokake nilai rata-rata ing kolom D lan nampilake nilai kasebut ing C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Gumantung carane data sampeyan ditulis, argumen katelu kanggo MATCH () yaiku 1, 0, utawa -1:

  • yen kolom diurutake kanthi urutan munggah, nyetel 1 (banjur rumus bakal ngetung nilai maksimum, sing kurang saka utawa padha karo nilai rata-rata);
  • yen urutan mudhun, banjur -1 (rumus bakal ngasilake nilai minimal sing luwih gedhe utawa padha karo rata-rata);
  • Yen larik goleki ngemot nilai sing padha karo rata-rata, banjur setel dadi 0. 

 Ing conto kita, populasi diurutake ing urutan mudhun, supaya kita sijine -1. Lan asile yaiku Tokyo, amarga nilai populasi (13,189) paling cedhak karo nilai rata-rata (000).

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

VLOOKUP () uga bisa nindakake petungan kasebut, nanging mung minangka rumus array: VLOOKUP kanthi AVERAGE, MIN lan MAX.

INDEX MATCH lan ESND / IFERROR

Sampeyan mbokmenawa wis ngeweruhi yen rumus ora bisa nemokake nilai sing dipengini, iku mbalang kesalahan # N / A. Sampeyan bisa ngganti pesen kesalahan standar karo sing luwih informatif. Contone, atur argumen ing rumus Ing XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Kanthi rumus iki, yen sampeyan ngetik data sing ora ana ing tabel, formulir bakal menehi pesen sing wis ditemtokake.

Napa INDEX lan MATCH luwih apik tinimbang VLOOKUP ing Excel

Yen sampeyan pengin nyekel kabeh kasalahan, banjur kajaba kanggo Ing XNUMX bisa digunakake IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "Ana sing salah!")

Nanging elinga yen kesalahan masking kanthi cara iki ora apik, amarga kesalahan standar nglaporake pelanggaran ing rumus kasebut.

Muga-muga sampeyan nemokake pandhuan kanggo nggunakake fungsi INDEX MATCH () mbiyantu.

Ninggalake a Reply