Tutorial Excel: Pencarian dua hala dengan anggaran INDEX dan MATCH

Isi kandungan

Jalan pintas yang berkaitan

Dalam video ini, kita akan melihat bagaimana membina pencarian dua arah dengan INDEX dan MATCH, menggunakan padanan anggaran.

Di sini kita mempunyai kalkulator kos sederhana, yang meninjau kos berdasarkan lebar dan tinggi bahan. Perlawanan perlu dilakukan secara hampir. Contohnya, jika lebarnya 250, dan tinggi 325, hasil yang betul ialah $ 1,800.

Sekiranya lebarnya 450, dan ketinggiannya tetap pada 325, hasil yang betul adalah $ 3,600.

Kita boleh membina formula yang melakukan carian ini menggunakan INDEX dan MATCH.

Pertama, mari kita buat INDEX berfungsi sebagai bukti konsep, dengan menyusun bahagian rumus. Ini adalah kaedah terbaik untuk memastikan anda mempunyai idea yang tepat, sebelum anda memulakannya.

Oleh itu, dengan data dalam jadual kami sebagai susunan, dan dengan lebar 450 dan tinggi 325, INDEX akan memerlukan nombor baris 3, dan nombor lajur 4, untuk mendapatkan nilai yang betul dalam jadual . Ini berfungsi dengan baik tetapi tentu saja ia tidak akan berubah kerana nilainya keras.

Oleh itu, mari kita tetapkan fungsi MATCH yang kita perlukan untuk mengira nilai-nilai ini.

Untuk mendapatkan nilai lebar, yang merupakan angka baris di INDEX, kami akan menggunakan nilai pencarian dari M7, dan nilai di lajur B, sebagai tatasusunan. Untuk jenis padanan, kami ingin menggunakan 1 untuk pencocokan anggaran kerana nilainya disusun dalam urutan menaik. Hasilnya adalah 4.

Untuk mendapatkan ketinggian, yang merupakan lajur di dalam INDEX, kita sekali lagi akan menggunakan PERTANDINGAN dengan nilai dari M8, nilai ketinggian dari baris 6. Sekali lagi, jenis padanan ditetapkan lagi ke 1 untuk perkiraan hampir. Hasilnya adalah 3.

Sekarang jika saya menukar lebar menjadi 350 dan tinggi menjadi 550, kita akan mendapat hasil baru.

Nilai-nilai ini betul-betul yang kita perlukan untuk INDEX. Jadi sekarang saya hanya akan menyalin dan menampal fungsi MATCH ke dalam formula INDEX yang asal.

Lebar masuk untuk nombor baris.

Dan ketinggian masuk untuk nombor lajur.

Kami kini mempunyai pencarian dinamik yang menghitung kos berdasarkan lebar dan tinggi dengan tepat, hampir sama dengan pencocokan.