Tutorial mempamerkan beberapa formula yang berbeza untuk melakukan dua dimensi carian dalam Excel. Lihat sahaja alternatif dan pilih kegemaran anda :)

Apabila mencari sesuatu dalam spreadsheet Excel anda, kebanyakan masa anda akan melihat secara menegak dalam lajur atau mendatar dalam baris. Tetapi kadang -kadang anda perlu melihat kedua -dua baris dan lajur. Dalam erti kata lain, anda berhasrat untuk mencari nilai di persimpangan baris dan lajur tertentu. Ini dipanggil Matrix Lookup (aka 2-dimensi atau 2-way Lookup ), dan tutorial ini menunjukkan bagaimana untuk melakukannya dalam 4 cara yang berbeza.

Formula Perlawanan Perlawanan Indeks Excel

Cara yang paling popular untuk melakukan carian dua hala dalam Excel adalah dengan menggunakan perlawanan perlawanan indeks. Ini adalah variasi formula perlawanan indeks klasik yang mana anda menambah satu fungsi perlawanan untuk mendapatkan kedua -dua baris dan nombor lajur:

Indeks ( data_array , padanan ( vlookup_value , lookup_column_range , 0), padanan ( nilai hlookup , lookup_row_range , 0))

Sebagai contoh, mari kita buat formula untuk menarik populasi haiwan tertentu dalam satu tahun tertentu dari jadual di bawah. Sebagai permulaan, kami menentukan semua hujah:

• Data_array - b2: e4 (sel data, tidak termasuk tajuk baris dan lajur)
• Vlookup_value - H1 (sasaran haiwan)
• Lookup_column_range - a2: a4 (tajuk baris: nama haiwan) - A3: A4
• Hlookup_Value - H2 (tahun sasaran)
• Lookup_row_range - b1: e1 (tajuk lajur: tahun)

Letakkan semua hujah bersama dan anda akan mendapat formula ini untuk carian dua hala:

=INDEX(B2:E4, MATCH(H1, A2:A4, 0), MATCH(H2, B1:E1, 0))

Jika anda perlu melakukan carian dua hala dengan lebih daripada dua kriteria, lihat artikel ini: Perlawanan Indeks dengan pelbagai kriteria dalam baris dan lajur.

Bagaimana formula ini berfungsi

Walaupun ia mungkin kelihatan agak kompleks pada pandangan pertama, logik formula benar -benar mudah dan mudah difahami. Fungsi indeks mengambil nilai dari array data berdasarkan nombor baris dan lajur, dan dua fungsi perlawanan membekalkan nombor tersebut:

INDEX(B2:E4, row_num, column_num)

Di sini, kami memanfaatkan keupayaan perlawanan (lookup_value, lookup_array, [match_type]) untuk mengembalikan kedudukan relatif lookup_value dalam lookup_array .

Oleh itu, untuk mendapatkan nombor baris, kami mencari haiwan yang menarik (H1) merentasi tajuk baris (A2: A4):

MATCH(H1, A2:A4, 0)

Untuk mendapatkan nombor lajur, kami mencari tahun sasaran (H2) merentasi tajuk lajur (B1: E1):

MATCH(H2, B1:E1, 0)

Dalam kedua -dua kes, kami mencari perlawanan tepat dengan menetapkan hujah ke -3 kepada 0.

Dalam contoh ini, perlawanan pertama mengembalikan 2 kerana nilai vlookup kami (beruang kutub) terdapat dalam A3, iaitu sel 2 ^ND dalam A2: A4. Perlawanan kedua mengembalikan 3 kerana nilai Hlookup (2000) didapati dalam D1, iaitu sel 3 ^RD dalam B1: E1.

Memandangkan perkara di atas, formula mengurangkan:

INDEX(B2:E4, 2, 3)

Dan mengembalikan nilai di persimpangan baris ² dan 3 ^RD dalam array data B2: E4, yang merupakan nilai dalam sel D3.

Formula VLOOKUP DAN PERTANYAAN UNTUK CARI 2-WAY

Satu lagi cara untuk melakukan carian dua dimensi dalam Excel adalah dengan menggunakan gabungan fungsi VLOOKUP dan perlawanan:

Vlookup ( vlookup_value , table_array , sepadan ( hlookup_value , lookup_row_range , 0), palsu)

Untuk jadual sampel kami, formula mengambil bentuk berikut:

=VLOOKUP(H1, A2:E4, MATCH(H2, A1:E1, 0), FALSE)

Di mana:

• Table_array - A2: E4 (sel data termasuk tajuk baris)
• Vlookup_value - H1 (sasaran haiwan)
• Hlookup_Value - H2 (tahun sasaran)
• Lookup_row_range - A1: E1 (Tajuk Lajur: Tahun)

Bagaimana formula ini berfungsi

Inti formula adalah fungsi VLOOKUP yang dikonfigurasikan untuk perlawanan tepat (argumen terakhir yang ditetapkan kepada FALSE), yang mencari nilai carian (H1) dalam lajur pertama array jadual (A2: E4) dan mengembalikan nilai dari lajur lain dalam baris yang sama. Untuk menentukan lajur mana yang akan mengembalikan nilai dari, anda menggunakan fungsi perlawanan yang juga dikonfigurasi untuk perlawanan tepat (argumen terakhir yang ditetapkan hingga 0):

MATCH(H2, A1:E1, 0)

Padanan carian untuk nilai dalam H2 merentasi tajuk lajur (A1: E1) dan mengembalikan kedudukan relatif sel yang dijumpai. Dalam kes kami, tahun sasaran (2010) terdapat dalam E1, iaitu ^ke -5 dalam pelbagai carian. Oleh itu, nombor 5 pergi ke argumen col_index_num vlookup:

VLOOKUP(H1, A2:E4, 5, FALSE)

VLOOKUP mengambilnya dari sana, mendapati perlawanan tepat untuk nilai cariannya dalam A2 dan mengembalikan nilai dari lajur ^ke -5 dalam baris yang sama, iaitu sel E2.

Nota penting! Untuk formula berfungsi dengan betul, table_array (a2: e4) vlookup dan lookup_array perlawanan (A1: E1) mesti mempunyai bilangan lajur yang sama, jika tidak, nombor yang diluluskan oleh col_index_num akan salah (tidak akan sesuai dengan kedudukan lajur di Table_Array ).

Fungsi Xlookup untuk melihat baris dan lajur

Baru -baru ini Microsoft telah memperkenalkan satu lagi fungsi dalam Excel yang dimaksudkan untuk menggantikan semua fungsi carian yang sedia ada seperti VLOOKUP, HLOOKUP dan perlawanan indeks. Antara lain, Xlookup dapat melihat persimpangan baris dan lajur tertentu:

Xlookup ( vlookup_value , vlookup_column_range , xlookup ( hlookup_value , hlookup_row_range , data_array ))

Untuk set data sampel kami, formula berjalan seperti berikut:

=XLOOKUP(H1, A2:A4, XLOOKUP(H2, B1:E1, B2:E4))

Nota. Fungsi XlookUp hanya tersedia dalam Excel untuk Microsoft 365, Excel 2021, dan Excel untuk Web.

Bagaimana formula ini berfungsi

Formula menggunakan keupayaan XLOOKUP untuk mengembalikan keseluruhan baris atau lajur. Fungsi dalaman mencari tahun sasaran dalam baris header dan mengembalikan semua nilai untuk tahun itu (dalam contoh ini, untuk tahun 1980). Nilai -nilai tersebut pergi ke argumen Return_array dari Xlookup luar:

XLOOKUP(H1, A2:A4, {22000;25000;700}))

Fungsi Xlookup luar mencari haiwan sasaran merentasi tajuk lajur dan mengembalikan nilai dalam kedudukan yang sama dari Return_array.

Formula Sumproduct untuk carian dua hala

Fungsi sumproduct adalah seperti pisau Switzerland di Excel - ia boleh melakukan banyak perkara di luar tujuan yang ditetapkan, terutama ketika datang untuk menilai pelbagai kriteria.

Untuk mencari dua kriteria, dalam baris dan lajur, gunakan formula generik ini:

Sumproduct ( vlookup_column_range = vlookup_value ) * ( hlookup_row_range = hlookup_value ), data_array )

Untuk melakukan pencarian 2-cara dalam dataset kami, formula berjalan seperti berikut:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2), B2:E4)

Sintaks di bawah akan berfungsi juga:

=SUMPRODUCT((A2:A4=H1) * (B1:E1=H2) * B2:E4)

Bagaimana formula ini berfungsi

Di tengah -tengah formula, kami membandingkan dua nilai carian terhadap tajuk baris dan lajur (haiwan sasaran dalam H1 terhadap semua nama haiwan dalam A2: A4 dan tahun sasaran dalam H2 terhadap semua tahun dalam B1: E1):

(A2:A4=H1) * (B1:E1=H2)

Ini menghasilkan 2 array nilai yang benar dan palsu, di mana True mewakili perlawanan:

{FALSE;FALSE;TRUE} * {FALSE,TRUE,FALSE,FALSE}

Operasi pendaraban memaksa nilai-nilai yang benar dan palsu ke dalam 1 dan 0 dan menghasilkan array dua dimensi 4 lajur dan 3 baris (baris dipisahkan oleh titik koma dan setiap lajur data dengan koma):

{0,0,0,0;0,0,0,0;0,1,0,0}

Fungsi sumproduct mengalikan unsur -unsur array di atas oleh item B2: E4 dalam kedudukan yang sama:

{0,0,0,0;0,0,0,0;0,1,0,0} * {22000,13800,8500,3500;25000,23000,22000,20000;700,2000,2300,2500}

Dan kerana mendarab dengan sifar memberikan sifar, hanya item yang sepadan dengan 1 dalam array pertama yang bertahan:

SUMPRODUCT({0,0,0,0;0,0,0,0;0,2000,0,0})

Akhirnya, sumproduct menambah unsur -unsur array yang dihasilkan dan mengembalikan nilai tahun 2000.

Nota. Jika jadual anda mempunyai lebih daripada satu baris atau/dan tajuk lajur dengan nama yang sama, array akhir akan mengandungi lebih daripada satu nombor selain sifar, dan semua nombor tersebut akan ditambah. Hasilnya, anda akan mendapat sejumlah nilai yang memenuhi kedua -dua kriteria. Inilah yang menjadikan formula sumproduct berbeza dari perlawanan perlawanan indeks dan vlookup, yang mengembalikan pertandingan pertama yang dijumpai.

Pencarian Matrix dengan Ranges Dinamakan (Persimpangan Eksplisit)

Satu cara yang lebih mudah untuk melakukan pencarian matriks dalam Excel adalah dengan menggunakan julat bernama. Inilah Caranya:

Bahagian 1: Nama Lajur dan Baris

Cara terpantas untuk menamakan setiap baris dan setiap lajur di dalam jadual anda ialah:

1. Pilih keseluruhan jadual (A1: E4 dalam kes kami).
2. Pada tab Formula , dalam kumpulan nama yang ditakrifkan , klik Buat dari Pemilihan atau tekan pintasan CTRL Shift F3 .
3. Dalam Buat Nama dari kotak dialog Pemilihan , pilih baris atas dan lajur kiri, dan klik OK.

   

Ini secara automatik mencipta nama berdasarkan tajuk baris dan lajur. Walau bagaimanapun, terdapat beberapa kaveat:

• Jika lajur dan/atau baris tajuk tajuk adalah nombor atau mengandungi aksara tertentu yang tidak dibenarkan dalam nama Excel, nama untuk lajur dan baris tersebut tidak akan dibuat. Untuk melihat senarai nama yang dibuat, buka Pengurus Nama ( Ctrl F3 ). Jika beberapa nama hilang, tentukan mereka secara manual seperti yang dijelaskan dalam cara menamakan julat dalam Excel.
• Jika beberapa tajuk baris atau lajur anda mengandungi ruang, ruang akan digantikan dengan garis bawah, contohnya, polar_bear .

Untuk jadual sampel kami, Excel secara automatik hanya mencipta nama baris. Nama lajur perlu dibuat secara manual kerana tajuk lajur adalah nombor. Untuk mengatasinya, anda hanya boleh mendahului nombor dengan garis bawah, seperti _1990 .

Hasilnya, kami mempunyai julat bernama berikut:

Bahagian 2: Buat formula carian matriks

Untuk menarik nilai di persimpangan baris dan lajur yang diberikan, taipkan satu formula generik berikut dalam sel kosong:

= row_name column_name

Atau sebaliknya:

= column_name row_name

Sebagai contoh, untuk mendapatkan populasi paus biru pada tahun 1990, formula itu semudah:

=Blue_whale _1990

Jika seseorang memerlukan arahan yang lebih terperinci, langkah -langkah berikut akan membimbing anda melalui proses:

1. Dalam sel di mana anda mahu hasilnya muncul, taipkan tanda kesamaan (=).
2. Mula menaip nama baris sasaran, katakan, Blue_whale . Selepas anda menaip beberapa aksara, Excel akan memaparkan semua nama sedia ada yang sepadan dengan input anda. Klik dua kali nama yang dikehendaki untuk memasukkannya dalam formula anda:

   

3. Selepas nama baris, taipkan ruang , yang berfungsi sebagai pengendali persimpangan dalam kes ini.
4. Masukkan nama lajur sasaran ( _1990 dalam kes kami).

   

5. Sebaik sahaja kedua -dua nama baris dan lajur dimasukkan, Excel akan menyerlahkan baris dan lajur yang sepadan dalam jadual anda, dan anda tekan Enter untuk melengkapkan formula:

   

Pencarian matriks anda dilakukan, dan tangkapan skrin di bawah menunjukkan hasilnya:

Itulah cara untuk mencari baris dan lajur di Excel. Saya mengucapkan terima kasih kerana membaca dan berharap dapat melihat anda di blog kami minggu depan!

Muat turun yang ada

Buku kerja contoh carian 2-dimensi

Atas ialah kandungan terperinci Perlawanan perlawanan indeks di Excel untuk carian dua dimensi. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!