Formula Lookup Excel karo Kriteria Lipat

Kanthi nggunakake rumus rangkean ing Excel, kita bisa nggawe formula panelusur sing nggunakake macem-macem kritéria kanggo nemokake informasi ing basis data utawa tabel data.

Rumus rangkap nlusur nesting fungsi MATCH ing fungsi INDEX .

Tutorial iki kalebu langkah demi langkah conto nggawe rumus goleki sing nggunakake macem-macem kritéria kanggo nggolek panyedhiya titanium Widget ing basis data sampel.

Sawise langkah-langkah ing topik tutorial ing ngisor iki lumaku sampeyan nggawe lan nggunakake rumus katon ing gambar ndhuwur.

01 saka 09

Ngetik Data Tutorial

Langkah pisanan ing tutorial iki yaiku kanggo nglebokake data menyang lembar kerja Excel.

Kanggo ngetutake langkah-langkah ing tutorial, ketik data sing dituduhake ing gambar ndhuwur menyang sel kasebut .

• Ketik dhuwure ndhuwur data menyang sel D1 kanggo F2
• Ketik nomer kapindho menyang sel D5 kanggo F11

Baris 3 lan 4 diklumpukake kanggo ngisi formula rumus nalika tutorial iki.

Tutorial ora kalebu format sing katon ing gambar, nanging iki ora bakal mengaruhi cara formula panelusur bisa dianggo.

Informasi pilihan format kaya sing katon ing ndhuwur kasedhiya ing Basic Excel Formatting Tutorial.

02 saka 09

Miwiti fungsi INDEX

Fungsi INDEX minangka salah siji saka sawetara ing Excel sing nduweni pirang-pirang formulir. Fungsi duwe Formulir Array lan Formulir Referensi .

Formulir Array mengembalikan data aktual dari database atau tabel data, sedangkan Formulir Referensi memberi Anda referensi sel atau lokasi data dalam tabel.

Ing tutorial iki kita bakal nggunakake Form Array wiwit kita pengin ngerti jeneng supplier kanggo widget titanium tinimbang referensi sel menyang supplier ing database kita.

Saben wangun nduweni daftar argumen sing beda sing kudu dipilih sadurunge diwiwiti fungsi kasebut.

Tutorial Langkah

1. Klik ing sel F3 kanggo nggawe sel aktif . Iki ngendi kita bakal ngetik fungsi sing dijupuk.
2. Klik tab Rumus saka menu pita .
3. Pilih Lookup lan Referensi saka pita kanggo mbukak dhaptar gulung fungsi.
4. Klik ing INDEX ing daftar kanggo ngundhuh kotak dialog Pilih Argumen .
5. Pilih array, row_num, col_num ing kotak dialog.
6. Klik OK kanggo mbukak kothak dialog INDEX fungsi.

03 saka 09

Mlebu menyang Argumen Arus Fungsi INDEX

Argumentasi pertama sing dibutuhake yaiku argumen Array. Argumentasi iki nemtokake macem-macem sel sing bakal digoleki kanggo data sing dikarepake.

Kanggo tutorial iki argumen bakal dadi basis data sampel kita.

Tutorial Langkah

1. Ing kothak dialog INDEX, klik ing baris Array .
2. Serat sel D6 kanggo F11 ing lembar kerja kanggo nglebokake jarak menyang kothak dialog.

04 saka 09

Miwiti Fungsi Nested MATCH

Nalika nesting siji fungsi ing sajrone liyane, ora bisa mbukak kothak dialog fungsi sing kapindho utawa dicithak kanggo nglebokake argumentasi sing perlu.

Fungsi nested kudu diketik minangka salah sawijining argumen fungsi pisanan.

Ing tutorial iki, fungsi MATCH nested lan argumen-argumen kasebut bakal dilebokake ing baris kedua fungsi dialog INDEX - baris Row_num .

Iku penting kanggo dicathet yen, nalika ngetik fungsi kanthi manual, argumen fungsi dipisah saka siji liyane kanthi koma "," .

Ketik Argumentasi Lookup_value MATCH Function

Langkah pisanan ngetik fungsi MATCH nested yaiku kanggo nglebokake argumen Lookup_value .

Lookup_value bakal dadi referensi lokasi utawa sel kanggo istilah telusuran sing arep kita cocog ing basis data.

Biasane Lookup_value mung nampa siji kritéria utawa istilah panelusuran. Kanggo nggolek kriteria akeh, kita kudu ngluwihi Lookup_value .

Iki dilakoni kanthi nggabung utawa gabung karo rong utawa luwih referensi sel kanthi nggunakake simbol ampersand " & ".

Tutorial Langkah

1. Ing kothak dialog fungsi INDEX, klik ing baris Row_num .
2. Ketik katandhingake jeneng pandhuan sing diikuti karo krenjang babak sing mbukak " ( "
3. Klik ing sel D3 kanggo ngetik referensi sèl menyang kothak dialog.
4. Ketik ampersand " & " sawise referensi sèl D3 kanggo nambah referensi sel kapindho.
5. Klik ing sel E3 kanggo nglebokake referensi sel liya menyang kothak dialog.
6. Ketik koma "," sawisé referensi sel E3 kanggo ngrampungake entri argumentasi Lookup_value MATCH.
7. Ninggalake kotak dialog fungsi INDEX mbukak kanggo langkah sabanjure ing tutorial.

Ing langkah pungkasan tutorial, Lookup_values ​​bakal dipasrahake menyang sel D3 lan E3 lembar kerja.

05 saka 09

Nambahake Lookup_array kanggo Fungsi MATCH

Langkah iki kalebu nambah argumen Lookup_array kanggo fungsi MATCH nested.

Lookup_array iku jangkoan sel sing fungsi MATCH bakal nggoleki kanggo nemokake argumen Lookup_value sing ditambahake ing tutorial sadurunge.

Awit kita wis nemtokake rong kolom panelusuran ing argumen Lookup_array kita kudu nglakoni sing padha kanggo Lookup_array . Fungsi MATCH mung nggoleki siji array kanggo saben istilah kasebut.

Kanggo ngetik macem-macem array, kita gunakake ampersand " & " kanggo nggabungake array bebarengan.

Tutorial Langkah

Langkah-langkah kasebut bakal ditindakake sawisé koma kasebut mlebu ing langkah sadurunge ing baris Row_num ing kotak dialog fungsi INDEX.

1. Klik ing baris Row_num sawise koma kanggo nyeleh titik insertion ing pungkasan entri saiki.
2. Sorot sel D6 kanggo D11 ing lembar kerja kanggo ngetik jangkoan. Iki minangka urutan pertama fungsi kanggo nggoleki.
3. Ketik ampersand " & " sawise referensi sel D6: D11 amarga kita pengin fungsi kanggo nelusuri rong array.
4. Serat sel E6 nganti E11 ing lembar kerja kanggo nglebokake jarak. Iki minangka urutan kapindho fungsi kanggo nelusuri.
5. Ketik koma "," sawise referensi sel E3 kanggo ngrampungake entri argum Lookup_array fungsi MATCH.
6. Ninggalake kotak dialog fungsi INDEX mbukak kanggo langkah sabanjure ing tutorial.

06 saka 09

Nambahake jinis Pencocokan lan Ngrampungke MATCH Function

Argumentasi katelu lan final saka fungsi MATCH yaiku argumentasi Match_type.

Argumentasi iki ngandhani Excel cara cocog karo Lookup_value kanthi angka ing Lookup_array. Pilihanipun yaiku: 1, 0, utawa -1.

Argumentasi iki opsional. Yen dilirwakake fungsi iki nggunakake nilai default 1.

• yen Match_type = 1 utawa dilirwakake: MATCH nemokake nilai paling gedhe sing kurang saka utawa padha karo Lookup_value. Data Lookup_array kudu diurutake miturut urutan munggah.
• yen match_type = 0: MATCH nemokake nilai pisanan sing persis padha karo Lookup_value. Data Lookup_array bisa diurutake miturut urutan apa wae.
• yen Match_type = -1: MATCH nemokake nilai sing paling cilik sing luwih dhuwur tinimbang utawa padha karo Lookup_value. Data Lookup_array kudu disusun miturut urutan sing mudhun.

Tutorial Langkah

Langkah-langkah kasebut bakal ditindakake sawisé koma kasebut mlebu ing langkah sadurunge ing baris Row_num ing kotak dialog fungsi INDEX.

1. Sasampuning koma ing baris Row_num , ketik nol " 0 " awit kita pengin fungsi nested kanggo ngasilake pas sing cocog karo syarat sing kita lebokake ing sel D3 lan E3.
2. Ketik kren kurung tutup " ) " kanggo ngrampungake fungsi MATCH.
3. Ninggalake kotak dialog fungsi INDEX mbukak kanggo langkah sabanjure ing tutorial.

07 saka 09

Balik menyang Fungsi INDEX

Saiki yen fungsi MATCH rampung, kita bakal pindhah menyang baris katelu saka kotak dialog sing mbukak lan ketik argumen pungkasan kanggo fungsi INDEX.

Argumentasi katelu lan pungkasan iki yaiku argumen Column_num sing ngandhani Excel nomer kolom ing sawetara D6 nganti F11 lan bakal nemokake informasi sing kita pengin bali kanthi fungsi kasebut. Ing kasus iki, Supplier kanggo titanium widgets .

Tutorial Langkah

1. Klik ing baris Column_num ing kothak dialog.
2. Ketik nomer telung " 3 " (ora ana tanda kutip) ing baris iki amarga kita nggoleki data ing kolom katelu saka range D6 kanggo F11.
3. Aja Klik OK utawa nutup kotak dialog fungsi INDEX. Sampeyan kudu tetep mbukak kanggo langkah sabanjure ing tutorial - nggawe rumus rangkap .

08 saka 09

Nggawe Formula Array

Sadurunge nutup kothak dialog, kita kudu ngganti fungsi nested kita dadi formula rangkean .

Formula rangkap yaiku ngidini kanggo nelusuri macem-macem istilah ing tabel data. Ing tutorial iki kita looking kanggo cocog rong istilah: Widget saka kolom 1 lan titanium saka kolom 2.

Nggawe rumus rangkean ing Excel rampung kanthi mencet CTRL , SHIFT , lan ENTER tombol ing keyboard bebarengan.

Efek saka mencet tombol iki bebarengan yaiku kanggo ngubengi fungsi karo penyepuh kriting: {} nuduhake saiki formula rumus.

Tutorial Langkah

1. Kanthi kothak dialog rampung isih mbukak saka langkah sadurunge tutorial iki, pencet terus tombol CTRL lan SHIFT ing keyboard banjur pencet lan ngeculake tombol ENTER .
2. Yen wis rampung kanthi bener, kothak dialog bakal ditutup lan kesalahan # N / A bakal katon ing sel F3 - sèl ing ngendi kita ngetik fungsi kasebut.
3. Kesalahan # N / A katon ing sel F3 amarga sel D3 lan E3 kosong. D3 lan E3 minangka sel ngendi kita marang fungsi kanggo nggoleki Lookup_values ​​ing langkah 5 tutorial. Sawise data ditambahake menyang rong sel kasebut, kesalahan bakal diganti karo informasi saka database .

09 saka 09

Nambah Kriteria Panelusuran

Langkah pungkasan ing tutorial iki kanggo nambahake istilah-istilah panelusuran ing lembar kerja kita.

Kaya kasebut ing langkah sadurunge, kita looking kanggo cocog istilah Widget saka kolom 1 lan Titanium saka kolom 2.

Yen, lan mung yen, rumus kita nemokake match kanggo rong istilah ing kolom sing cocok ing basis data, bakal ngasilake nilai saka kolom katelu.

Tutorial Langkah

1. Klik ing sel D3.
2. Ketik Widget banjur pencet tombol Ketik ing keyboard.
3. Klik ing sel E3.
4. Ketik Titanium banjur pencet tombol Ketik ing keyboard.
5. Jeneng supplier Supaya Widgets Inc. kudu katon ing sel F3 - lokasi fungsi kasebut awit iku mung supplier sing kadaptar sing ngedol Titanium Widget.
6. Nalika sampeyan ngeklik fungsi F3 sel
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   katon ing garis rumus ndhuwur lembaran .

Cathetan: Ing conto kita mung ana siji supplier kanggo widget titanium. Yen ana luwih saka siji supplier, pemasok kadhaptar ing database kasebut bali kanthi fungsi kasebut.