Formula generik
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Ringkasan
Pencarian sensitif huruf di Excel
Secara lalai, carian standard di Excel tidak peka huruf besar kecil. Kedua-dua VLOOKUP dan INDEX / MATCH hanya akan mengembalikan perlawanan pertama, tanpa mengira kes.
Cara langsung untuk mengatasi had ini, adalah dengan menggunakan formula larik berdasarkan INDEX / MATCH dengan EXACT. Walau bagaimanapun, jika anda hanya mencari nilai angka, SUMPRODUCT + EXACT juga memberikan cara yang menarik dan fleksibel untuk melakukan carian yang sensitif terhadap huruf besar.
Dalam contohnya, kami menggunakan formula berikut
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Walaupun formula ini adalah formula larik, ia tidak perlu dimasukkan dengan Control + Shift + Enter, kerana SUMPRODUCT menangani array secara semula jadi.
Penjelasan
SUMPRODUCT direka untuk berfungsi dengan tatasusunan, yang digandakan, kemudian jumlahnya.
Dalam kes ini, kita adalah dua tatasusunan dengan SUMPRODUCT: B3: B8 dan C3: C8. Caranya adalah dengan menjalankan ujian pada nilai di lajur B, kemudian menukar nilai BENAR / SALAH yang dihasilkan menjadi 1 dan 0. Kami menjalankan ujian dengan TEPAT seperti ini:
EXACT(E3,B3:B8)
Yang menghasilkan tatasusunan ini:
(SALAH; SALAH; BENAR; SALAH; SALAH; SALAH)
Perhatikan bahawa nilai sebenarnya di kedudukan 3 adalah padanan kita. Kemudian kami menggunakan negatif ganda (iaitu -, yang secara teknikal adalah "double unary") untuk memaksa nilai-nilai BENAR / SALAH ini menjadi 1 dan 0. Hasilnya ialah susunan ini:
(0; 0; 1; 0; 0; 0)
Pada titik pengiraan ini, formula SUMPRODUCT kelihatan seperti ini:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT kemudian hanya menggandakan item dalam setiap array bersama-sama untuk menghasilkan susunan akhir:
(0; 0; 775; 0; 0; 0)
SUMPRODUCT mana yang kemudian dijumlahkan, dan mengembalikan 775.
Jadi, inti dari formula ini adalah bahawa nilai PALSU digunakan untuk membatalkan semua nilai lain. Satu-satunya nilai yang bertahan adalah nilai yang BENAR.
Perhatikan bahawa kerana kita menggunakan SUMPRODUCT, formula ini dilengkapi dengan kelainan yang unik: jika terdapat banyak perlawanan, SUMPRODUCT akan mengembalikan jumlah pertandingan tersebut. Ini mungkin atau mungkin bukan yang anda mahukan, jadi berhati-hatilah sekiranya anda mengharapkan banyak perlawanan!
Ingat, formula ini hanya berfungsi untuk nilai angka, kerana SUMPRODUCT tidak mengendalikan teks. Sekiranya anda ingin mendapatkan semula teks, gunakan INDEX / MATCH + EXACT.
