Ringkasan
Untuk melakukan pencarian padanan dua arah dengan pelbagai kriteria, anda boleh menggunakan formula larik berdasarkan INDEX dan MATCH, dengan bantuan fungsi IF untuk menerapkan kriteria. Dalam contoh yang ditunjukkan, formula dalam K8 adalah:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
di mana data (D6: H16), diameter (D5: H5), bahan (B6: B16), dan kekerasan (C6: C16) dinamakan julat yang digunakan untuk kemudahan sahaja.
Catatan: ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter
Penjelasan
Tujuannya adalah untuk mencari kadar suapan berdasarkan bahan, kekerasan, dan diameter bit gerudi. Nilai kadar suapan terdapat dalam data julat bernama (D6: H16).
Ini boleh dilakukan dengan formula INDEX dan MATCH dua hala. Satu fungsi MATCH menyelesaikan nombor baris (bahan dan kekerasan), dan fungsi MATCH yang lain mencari nombor lajur (diameter). Fungsi INDEX mengembalikan hasil akhir.
Dalam contoh yang ditunjukkan, formula dalam K8 adalah:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Jeda baris ditambahkan untuk kebolehbacaan sahaja).
Yang sukar ialah bahan dan kekerasan perlu dikendalikan bersama. Kita perlu menghadkan MATCH pada nilai kekerasan bagi bahan tertentu (Baja Karbon Rendah dalam contoh yang ditunjukkan).
Kita boleh melakukan ini dengan fungsi IF. Pada dasarnya, kita menggunakan IF untuk "membuang" nilai yang tidak relevan sebelum kita mencari perlawanan.
Perincian
Fungsi INDEX diberi data julat bernama (D6: H16) seperti untuk array. Fungsi MATCH pertama berfungsi dengan nombor baris:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Untuk mencari baris yang betul, kita perlu melakukan padanan tepat pada bahan, dan perkiraan yang hampir sama dengan kekerasan. Kami melakukan ini dengan menggunakan fungsi IF untuk menyaring kekerasan yang tidak berkaitan:
IF(material=K5,hardness) // filter
Kami menguji semua nilai dalam bahan (B6: B16) untuk melihat apakah nilai tersebut sesuai dengan nilai dalam K5 ("Baja Karbon Rendah"). Sekiranya demikian, nilai kekerasan dilalui. Sekiranya tidak, JIKA mengembalikan SALAH. Hasilnya adalah susunan seperti ini:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Perhatikan satu-satunya nilai yang masih ada adalah nilai yang berkaitan dengan Baja Karbon Rendah. Nilai-nilai lain sekarang SALAH. Susunan ini dikembalikan terus ke fungsi MATCH sebagai lookup_array.
Nilai pencarian untuk pertandingan berasal dari K6, yang berisi kekerasan yang diberikan, 176. MATCH dikonfigurasikan untuk perkiraan padanan dengan menetapkan jenis match_type ke 1. Dengan tetapan ini, MATCH mengabaikan nilai FALSE dan mengembalikan kedudukan padanan tepat atau nilai terkecil berikutnya .
Catatan: nilai kekerasan mesti disusun mengikut urutan menaik untuk setiap bahan.
Dengan kekerasan yang diberikan sebagai 176, MATCH mengembalikan 6, dihantar terus ke INDEX sebagai nombor baris. Kita sekarang boleh menulis semula formula asal seperti ini:
=INDEX(data,6,MATCH(K7,diameter,1))
Rumus PERTANDINGAN kedua mencari nombor lajur yang betul dengan melakukan taksiran pada diameter:
MATCH(K7,diameter,1) // get column num
Catatan: nilai diameter D5: H5 mesti disusun mengikut tertib menaik.
Nilai pencarian berasal dari K7 (0.75), dan lookup_array adalah diameter julat yang dinamakan (D5: H5).
Seperti sebelumnya, PERTANDINGAN diatur menjadi perkiraan pencocokan dengan menetapkan tipe padanan menjadi 1.
Dengan diameter yang diberikan sebagai 0,75, MATCH mengembalikan 3, dihantar terus ke fungsi INDEX sebagai nombor lajur. Formula asalnya kini dapat:
=INDEX(data,6,3) // returns 0.015
INDEX mengembalikan hasil akhir 0.015, nilai dari F11.
