Contents [show]
Tutorial kecil ini menerangkan cara membuat fungsi VPR (VLOOKUP), menunjukkan beberapa formula lain yang Excel boleh cari dalam cara sensitif huruf besar dan menunjukkan kekuatan dan kelemahan setiap fungsi.
Saya rasa setiap pengguna Excel tahu fungsi apa yang melakukan carian menegak. Betul, ia adalah fungsi VPR. Walau bagaimanapun, hanya sedikit orang yang mengetahuinya VPR tidak sensitif huruf, iaitu huruf kecil dan huruf besar adalah sama untuknya.
Berikut ialah contoh cepat yang menunjukkan ketidakupayaan VPR mengenali daftar. Katakan dalam sel A1 mengandungi nilai "bil" dan sel A2 – Formula “Bil”:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… akan menghentikan cariannya pada "bil" kerana nilai itu didahulukan dalam senarai dan mengekstrak nilai daripada sel B1.
Kemudian dalam artikel ini, saya akan menunjukkan kepada anda cara melakukannya VPR kes sensitif. Selain itu, kami akan mempelajari beberapa lagi fungsi yang boleh melakukan carian sensitif huruf besar-besaran dalam Excel.
Kita akan mulakan dengan yang paling mudah – VIEW (LIHAT) dan SUMPRODUCT (SUMPRODUCT), yang, malangnya, mempunyai beberapa batasan yang ketara. Seterusnya, kita akan melihat dengan lebih dekat formula yang sedikit lebih kompleks INDEKS+PADANAN (INDEX+MATCH), yang berfungsi dengan sempurna dalam sebarang situasi dan dengan mana-mana set data.
Fungsi VLOOKUP adalah peka huruf besar-besaran
Seperti yang anda sedia maklum, fungsi biasa VPR tidak peka huruf besar-besaran. Walau bagaimanapun, terdapat cara untuk menjadikannya sensitif huruf besar. Untuk melakukan ini, anda perlu menambah lajur tambahan pada jadual, seperti yang ditunjukkan dalam contoh berikut.
Katakan dalam lajur B terdapat pengecam produk (Item) dan anda ingin mengekstrak harga produk dan ulasan yang sepadan daripada lajur C и D. Masalahnya ialah pengecam mengandungi kedua-dua aksara kecil dan huruf besar. Contohnya, nilai sel B4 (001Tvci3u) dan B5 (001Tvci3U) berbeza hanya dalam kes aksara terakhir, u и U masing-masing.
Seperti yang anda boleh bayangkan, formula carian biasa
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
akan kembali $ 90, sejak nilai 001Tvci3u berada dalam julat carian lebih awal daripada 001Tvci3U. Tetapi bukan itu yang kita perlukan, bukan?
Untuk mencari dengan fungsi VPR dalam Excel case sensitive, anda perlu menambah lajur pembantu dan mengisi selnya dengan formula berikut (di mana B ialah lajur carian):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
Formula ini memecahkan nilai yang dikehendaki kepada aksara yang berasingan, menggantikan setiap aksara dengan kodnya (contohnya, bukannya A pada 65, sebaliknya a kod 97) dan kemudian menggabungkan kod ini menjadi rentetan nombor yang unik.
Selepas itu, kami menggunakan fungsi mudah VPR untuk carian sensitif huruf besar-besaran:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
Operasi fungsi yang betul VPR sensitif huruf besar bergantung kepada dua faktor:
- Lajur pembantu mestilah lajur paling kiri dalam julat yang boleh dilihat.
- Nilai yang anda cari mesti mengandungi kod aksara dan bukannya nilai sebenar.
Cara menggunakan fungsi CODE dengan betul
Formula yang dimasukkan ke dalam sel lajur tambahan menganggap bahawa semua nilai carian anda mempunyai bilangan aksara yang sama. Jika tidak, maka anda perlu mengetahui nombor terkecil dan terbesar dan menambah seberapa banyak ciri IFERROR (IFERROR) berapa banyak aksara ialah perbezaan antara nilai carian terpendek dan paling lama.
Sebagai contoh, jika nilai carian terpendek ialah 3 aksara dan terpanjang ialah 5 aksara, gunakan formula ini:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
Untuk fungsi PSTR (MID) Anda menyediakan hujah berikut:
- hujah pertama - teks (teks) ialah teks atau rujukan sel yang mengandungi aksara yang akan diekstrak (dalam kes kami ialah B2)
- hujah pertama - bilangan_mula (start_position) ialah kedudukan pertama daripada aksara tersebut yang akan diekstrak. awak masuk 1 dalam fungsi pertama PSTR, 2 – dalam fungsi kedua PSTR dan lain-lain.
- hujah pertama - num_chars (bilangan_aksara) – Menentukan bilangan aksara untuk diekstrak daripada teks. Oleh kerana kita hanya memerlukan 1 aksara sepanjang masa, dalam semua fungsi kita menulis 1.
HAD: fungsi VPR bukanlah penyelesaian terbaik untuk carian sensitif huruf besar-besaran dalam Excel. Pertama, penambahan lajur tambahan diperlukan. Kedua, formula berfungsi dengan baik hanya jika data adalah homogen, atau bilangan sebenar aksara dalam nilai yang dicari diketahui. Jika ini bukan kes anda, lebih baik menggunakan salah satu penyelesaian yang kami tunjukkan di bawah.
Fungsi LOOKUP untuk carian sensitif huruf besar-besaran
fungsi VIEW (LOOKUP) berkaitan VPR, walau bagaimanapun sintaksnya membenarkan carian sensitif huruf besar-kecil tanpa menambah lajur tambahan. Untuk melakukan ini, gunakan VIEW digabungkan dengan fungsi TEPAT (TEPAT).
Jika kita mengambil data dari contoh sebelumnya (tanpa lajur tambahan), maka formula berikut akan mengatasi tugas itu:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
Carian formula dalam julat A2: A7 padanan tepat dengan nilai sel F2 peka huruf besar dan kecil dan mengembalikan nilai dari lajur B baris yang sama.
suka VPRfungsi VIEW berfungsi sama dengan teks dan nilai angka, seperti yang anda boleh lihat dalam tangkapan skrin di bawah:
Penting! Untuk fungsi VIEW berfungsi dengan betul, nilai dalam lajur carian hendaklah diisih dalam tertib menaik, iaitu daripada terkecil kepada terbesar.
Biar saya terangkan secara ringkas bagaimana fungsi itu berfungsi TEPAT dalam formula yang ditunjukkan di atas, kerana ini adalah perkara utama.
fungsi TEPAT membandingkan dua nilai teks dalam argumen pertama dan kedua dan mengembalikan TRUE jika ia betul-betul sama, atau SALAH jika tidak. Adalah penting bagi kita bahawa fungsi TEPAT kes sensitif.
Mari lihat bagaimana formula kami berfungsi LIHAT+TEPAT:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- fungsi TEPAT membandingkan nilai sel F2 dengan semua elemen dalam lajur A (A2:A7). Mengembalikan TRUE jika padanan tepat ditemui, sebaliknya FALSE.
- Oleh kerana anda memberikan hujah fungsi pertama VIEW nilai TRUE, ia mengekstrak nilai yang sepadan daripada lajur yang ditentukan (dalam kes kami, lajur B) hanya jika padanan tepat ditemui, sensitif huruf besar-besaran.
Saya harap penjelasan ini jelas dan kini anda memahami idea utama. Jika ya, maka anda tidak akan mengalami sebarang kesulitan dengan fungsi lain yang akan kami analisis lebih lanjut, kerana. mereka semua bekerja pada prinsip yang sama.
HAD: Data dalam lajur carian mesti diisih dalam tertib menaik.
SUMPRODUCT – mencari nilai teks, sensitif huruf besar dan kecil, tetapi hanya mengembalikan nombor
Seperti yang telah anda fahami dari tajuknya, SUMPRODUCT (SUMPRODUCT) ialah satu lagi fungsi Excel yang akan membantu anda melakukan carian sensitif huruf besar-besaran, tetapi hanya akan mengembalikan nilai angka. Jika pilihan ini tidak sesuai dengan anda, maka anda boleh terus ke himpunan INDEKS+PADANAN, yang memberikan penyelesaian untuk sebarang kes dan untuk sebarang jenis data.
Mula-mula, izinkan saya menerangkan secara ringkas sintaks fungsi ini, ini akan membantu anda lebih memahami formula sensitif huruf besar-kecil yang berikut.
fungsi SUMPRODUCT mendarabkan elemen tatasusunan yang diberikan dan mengembalikan hasil tambah. Sintaks kelihatan seperti ini:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
Oleh kerana kami memerlukan carian sensitif huruf besar-besaran, kami menggunakan fungsi tersebut TEPAT (TEPAT) daripada contoh sebelumnya sebagai salah satu pengganda:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
Seperti yang anda ingat, TEPAT membandingkan nilai sel F2 dengan semua elemen dalam lajur A. Mengembalikan TRUE jika padanan tepat ditemui, sebaliknya FALSE. Dalam operasi matematik, Excel mengambil TRUE sebagai 1, dan SALAH untuk 0Lanjut SUMPRODUCT mendarab nombor ini dan menjumlahkan hasilnya.
Sifar tidak dikira kerana apabila didarab ia sentiasa memberi 0. Mari kita lihat dengan lebih dekat apa yang berlaku apabila padanan tepat dalam lajur A ditemui dan dikembalikan 1… Fungsi SUMPRODUCT mendarab nombor dalam lajur B on 1 dan mengembalikan hasilnya - nombor yang sama! Ini kerana keputusan produk lain adalah sifar, dan ia tidak menjejaskan jumlah yang terhasil.
Malangnya fungsi SUMPRODUCT tidak boleh berfungsi dengan nilai teks dan tarikh kerana ia tidak boleh didarab. Dalam kes ini, anda akan menerima mesej ralat #NILAI! (#VALUE!) seperti dalam sel F4 dalam gambar di bawah:
HAD: Mengembalikan nilai angka sahaja.
INDEX + MATCH – carian sensitif huruf besar-besaran untuk sebarang jenis data
Akhir sekali, kami hampir dengan formula carian tanpa had dan sensitif huruf besar yang berfungsi dengan mana-mana set data.
Contoh ini datang terakhir, bukan kerana yang terbaik ditinggalkan untuk pencuci mulut, tetapi kerana pengetahuan yang diperoleh daripada contoh sebelumnya akan membantu anda memahami formula sensitif huruf besar-besaran dengan lebih baik dan lebih cepat. INDEKS+PADANAN (INDEX+PERLAWANAN).
Seperti yang anda mungkin meneka, gabungan fungsi LEBIH TERDEDAH и INDEKS digunakan dalam Excel sebagai alternatif yang lebih fleksibel dan berkuasa untuk VPR. Artikel Menggunakan INDEX dan MATCH dan bukannya VLOOKUP akan menerangkan dengan sempurna cara fungsi ini berfungsi bersama.
Saya hanya akan meringkaskan perkara utama:
- fungsi LEBIH TERDEDAH (MATCH) mencari nilai dalam julat tertentu dan mengembalikan kedudukan relatifnya, iaitu nombor baris dan/atau lajur;
- Seterusnya, fungsi INDEKS (INDEX) mengembalikan nilai daripada lajur dan/atau baris tertentu.
Kepada formula INDEKS+PADANAN boleh mencari secara sensitif huruf besar-besaran, anda hanya perlu menambah satu fungsi padanya. Tidak sukar untuk meneka apa itu lagi TEPAT (TEPAT):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
Dalam formula ini TEPAT berfungsi dengan cara yang sama seperti bersama-sama dengan fungsi VIEW, dan memberikan hasil yang sama:
Perhatikan bahawa formula INDEKS+PADANAN disertakan dalam pendakap kerinting ialah formula tatasusunan dan anda mesti melengkapkannya dengan menekan Ctrl + Shift + Enter.
Mengapakah INDEX+MATCH penyelesaian terbaik untuk carian sensitif huruf besar-besaran?
Kelebihan utama bundle INDEKS и LEBIH TERDEDAH:
- Tidak memerlukan penambahan lajur tambahan, tidak seperti VPR.
- Tidak memerlukan lajur carian untuk diisih, tidak seperti VIEW.
- Berfungsi dengan semua jenis data – nombor, teks dan tarikh.
Formula ini kelihatan sempurna, bukan? Sebenarnya, tidak. Dan itulah sebabnya.
Andaikan bahawa sel dalam lajur nilai pulangan yang dikaitkan dengan nilai carian adalah kosong. Apakah keputusan yang akan dikembalikan oleh formula? Tidak? Mari lihat apakah formula yang sebenarnya dikembalikan:
Op, formula mengembalikan sifar! Ini mungkin bukan masalah besar jika anda bekerja dengan nilai teks tulen. Walau bagaimanapun, jika jadual mengandungi nombor, termasuk sifar "sebenar", ini menjadi masalah.
Malah, semua formula carian lain (VLOOKUP, LOOKUP dan SUMPRODUCT) yang kami bincangkan sebelum ini berkelakuan dengan cara yang sama. Tetapi anda mahu formula yang sempurna, bukan?
Untuk menjadikan formula sensitif huruf besar INDEKS+PADANAN sempurna, letakkannya dalam fungsi IF (JIKA) yang akan menguji sel dengan nilai pulangan dan mengembalikan hasil kosong jika ia kosong:
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
Dalam formula ini:
- B ialah lajur dengan nilai pulangan
- 1+ ialah nombor yang mengubah kedudukan relatif sel yang dikembalikan oleh fungsi LEBIH TERDEDAH, ke alamat sebenar sel. Sebagai contoh, dalam fungsi kita LEBIH TERDEDAH tatasusunan carian diberikan A2: A7, iaitu kedudukan relatif sel A2 akan 1, kerana ia adalah yang pertama dalam tatasusunan. Tetapi kedudukan sebenar sel A2 dalam lajur ialah 2, jadi kami tambah 1untuk membuat perbezaan dan mempunyai fungsi TIDAK LANGSUNG (TIDAK LANGSUNG) mendapatkan semula nilai daripada sel yang dikehendaki.
Gambar di bawah menunjukkan formula sensitif huruf besar yang diperbetulkan INDEKS+PADANAN Dalam tindakan. Ia mengembalikan hasil kosong jika sel yang dikembalikan kosong.
Saya menulis semula formula ke dalam lajur B:Duntuk memuatkan bar formula pada tangkapan skrin.
Formula kembali 0jika sel yang dikembalikan mengandungi sifar.
Jika anda mahu pautan INDEKS и LEBIH TERDEDAH memaparkan beberapa mesej apabila nilai pulangan kosong, anda boleh menulisnya dalam petikan terakhir (“”) formula, contohnya, seperti ini:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
