Ringkasan
Untuk melakukan pencarian pencocokan perkiraan dua arah dengan beberapa kriteria, Anda bisa menggunakan rumus array berdasarkan indeks dan kecocokan, dengan bantuan dari fungsi IF untuk menerapkan kriteria. Dalam contoh yang ditunjukkan, rumus di K8 adalah:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
di mana data (D6: H16), diameter (D5: H5), material (B6: B16), dan kekerasan (C6: C16) diberi nama rentang yang digunakan hanya untuk kenyamanan.
Catatan: ini adalah rumus array dan harus dimasukkan dengan Control + Shift + Enter
Penjelasan
Tujuannya adalah untuk mencari laju umpan berdasarkan material, kekerasan, dan diameter mata bor. Nilai laju umpan ada dalam data rentang bernama (D6: H16).
Ini dapat dilakukan dengan rumus INDEX dan MATCH dua arah. Satu fungsi MATCH menghitung nomor baris (material dan kekerasan), dan fungsi MATCH lainnya menemukan nomor kolom (diameter). Fungsi INDEX mengembalikan hasil akhir.
Pada contoh yang ditunjukkan, rumus di K8 adalah:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Jeda baris ditambahkan untuk keterbacaan saja).
Bagian yang sulit adalah material dan kekerasan harus ditangani bersama. Kita perlu membatasi MATCH pada nilai kekerasan untuk material tertentu (Baja Karbon Rendah dalam contoh yang ditunjukkan).
Kita bisa melakukan ini dengan fungsi IF. Pada dasarnya, kami menggunakan IF untuk "membuang" nilai yang tidak relevan sebelum kami mencari kecocokan.
Detail
Fungsi INDEX diberi data rentang bernama (D6: H16) seperti untuk array. Fungsi MATCH pertama menghitung nomor baris:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Untuk menemukan baris yang benar, kita perlu melakukan pencocokan tepat pada material, dan perkiraan kecocokan pada kekerasan. Kami melakukan ini dengan menggunakan fungsi IF untuk terlebih dahulu menyaring kekerasan yang tidak relevan:
IF(material=K5,hardness) // filter
Kami menguji semua nilai dalam material (B6: B16) untuk melihat apakah mereka cocok dengan nilai di K5 ("Baja Karbon Rendah"). Jika demikian, nilai kekerasan dilewatkan. Jika tidak, IF mengembalikan FALSE. Hasilnya adalah array seperti ini:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Perhatikan satu-satunya nilai yang bertahan adalah yang terkait dengan Baja Karbon Rendah. Nilai-nilai lainnya sekarang SALAH. Array ini dikembalikan langsung ke fungsi MATCH sebagai lookup_array.
Nilai pencarian untuk kecocokan berasal dari K6, yang berisi kekerasan yang diberikan, 176. MATCH dikonfigurasi untuk perkiraan kecocokan dengan menyetel match_type ke 1. Dengan pengaturan ini, MATCH mengabaikan nilai FALSE dan mengembalikan posisi kecocokan tepat atau nilai terkecil berikutnya .
Catatan: nilai kekerasan harus diurutkan dalam urutan menaik untuk setiap material.
Dengan kekerasan yang diberikan sebagai 176, MATCH mengembalikan 6, dikirim langsung ke INDEX sebagai nomor baris. Sekarang kita dapat menulis ulang rumus aslinya seperti ini:
=INDEX(data,6,MATCH(K7,diameter,1))
Rumus MATCH kedua menemukan nomor kolom yang benar dengan melakukan perkiraan kecocokan pada diameter:
MATCH(K7,diameter,1) // get column num
Catatan: nilai diameter D5: H5 harus diurutkan dalam urutan menaik.
Nilai pencarian berasal dari K7 (0,75), dan lookup_array adalah diameter rentang bernama (D5: H5).
Seperti sebelumnya, MATCH disetel ke perkiraan kecocokan dengan menyetel match_type ke 1.
Dengan diameter yang diberikan sebagai 0,75, MATCH mengembalikan 3, dikirim langsung ke fungsi INDEX sebagai nomor kolom. Rumus aslinya sekarang ditetapkan menjadi:
=INDEX(data,6,3) // returns 0.015
INDEX mengembalikan hasil akhir 0,015, nilai dari F11.
