VLOOKUP dengan Teks
Salah satu kriteria asas untuk bekerja dengan VLOOKUP adalah nilai "lookup" harus sama di sel hasil dan juga di tabel data utama, tetapi kadang-kadang walaupun nilai pencarian kelihatan sama di salah satu sel tetap kita akhirnya mendapat nilai ralat sebagai # N / A !. Ini kerana format nilai pandangan mesti berbeza di kedua-dua sel. Jadi dalam artikel ini, kami akan menunjukkan kepada anda cara bekerja dengan format teks dari nilai Vlookup.
Contoh VLOOKUP untuk Teks
Berikut adalah contoh excel VLOOKUP untuk teks.
Kadang-kadang nombor disimpan sebagai nilai teks, dan dalam kes seperti itu, kita tidak dapat menganggapnya sebagai angka kerana fungsi excel. Contohnya, lihat data di bawah.
Dalam data di atas, semuanya kelihatan seperti angka, tetapi ketika kita menjumlahkannya, kita harus memperoleh nilai total 3712054, tetapi ketika kita menerapkan fungsi SUM excel, kita mendapat angka di bawah.
Sebab di sebalik ini adalah kerana sebilangan nombor disimpan sebagai nilai teks. Jadi, bagaimana kita mengenal pasti nilai teks?
Kita dapat mengenal pasti nilai teks dengan menggunakan fungsi ISNUMBER excel. Fungsi ISNUMBER akan mengembalikan BENAR jika nilai sel yang dipilih adalah nombor atau yang lain akan mengembalikan SALAH.
Jadi di sel B5 & B6, kami mendapat hasilnya sebagai PALSU yang bermaksud nombor sel A5 & A6 disimpan sebagai nilai teks.
VLOOKUP Memerlukan Format Nombor Tepat
Sebagai contoh, lihat data di bawah untuk menerapkan fungsi VLOOKUP.
Dari Jadual 1, kita perlu menerapkan fungsi VLOOKUP untuk mendapatkan lajur pendapatan sehingga menghasilkan Jadual 2.
Jadi, terapkan fungsi VLOOKUP dalam jadual 2.
Kami mendapat hasil berikut.
Hasil fungsi VLOOKUP dalam sel E6 & E7 menunjukkan # N / A !.
Mari lihat nilai carian.
Ini biasanya berlaku dalam jumlah nilai carian. Sebab utama mestilah format nombor di kedua jadual tidak sama. Oleh itu, dalam kes sedemikian, kita perlu mengenal pasti nombor jadual yang disimpan sebagai teks.
Terapkan fungsi ISNUMBER untuk mengenal pasti nilai bukan berangka.
Seperti yang kita lihat, ISNUMBER mengenal pasti nilai bukan berangka dalam Jadual 2.
Apabila data jadual utama betul dan nombor jadual hasil disimpan sebagai teks, maka kita perlu menukar nombor berformat teks menjadi nilai berangka terlebih dahulu dan kemudian menerapkan VLOOKUP. Terdapat beberapa cara yang boleh kita lakukan, di bawah adalah kaedah.
Kaedah 1: Tukar nombor berformat teks menjadi nilai berangka melalui Paste Special
Pertama, masukkan nombor 1 di mana-mana sel dalam lembaran kerja dan salin sel itu.
Sekarang pilih nilai ID outlet pada jadual 2 dan buka kotak dialog Paste Special.
Untuk membuka kotak dialog khas tampal tekan alt = "" + E + S.
Kami mendapat kotak dialog berikut.
Di tampal, tetingkap khas, pilih "darab" sebagai pilihan.
Tekan ok, ia akan menukar semua angka berformat teks menjadi nilai berangka, dan VLOOKUP kini secara automatik mengambil data dari Jadual 1.
Kaedah 2: Tukar dengan Menggunakan Fungsi NILAI
Fungsi VALUE digunakan untuk menukar angka berformat teks menjadi nilai berangka. Seperti yang kita lihat, VLOOKUP kami tidak mengambil data kerana format nilai pencarian.
Untuk mengatasi masalah ini, yang menerapkan fungsi pencarian, kita perlu memasukkan fungsi NILAI.
Lihat formula di atas. Saya telah memasukkan fungsi carian dengan fungsi NILAI. Oleh kerana kita telah menerapkan fungsi NILAI di dalam fungsi VLOOKUP, pertama akan mengubah nilai bukan angka menjadi nilai numerik terlebih dahulu, kemudian VLOOKUP memperlakukannya sebagai nilai numerik saja.
Kaedah 3: Bagaimana jika Nombor Disimpan sebagai Teks dalam Jadual Utama
Kami telah melihat cara menukar nilai teks menjadi nombor dalam jadual hasil, tetapi apa nombornya disimpan sebagai nilai teks dalam jadual utama itu sendiri.
Seperti yang kita dapat dalam gambar di atas di jadual utama (Jadual 1), nilai itu sendiri disimpan sebagai teks. Dalam kes seperti ini, kita perlu memasukkan fungsi TEXT untuk nilai pencarian dalam fungsi VLOOKUP.
Masalahnya di sini ialah fungsi TEX menukar nilai numerik bahkan menjadi nilai teks, dan beberapa nilai yang disimpan sebagai nilai berangka tidak akan berfungsi dalam fungsi ini. Untuk ini, kita perlu memasukkan syarat IFERROR di excel.
Keadaan IFERROR menguji sama ada nilai carian adalah berangka atau tidak. Sekiranya berangka, kami akan menggunakan LOOKUP biasa, atau jika tidak, kami akan menerapkan fungsi TEXT di excel.
Oleh itu, kita memerlukan pengetahuan yang luas mengenai formula untuk bekerjasama dengan VLOOKUP di peringkat lanjutan dan pada senario yang berbeza.
Perkara yang Perlu Diingat
- Fungsi TRIM juga menukar nilai teks menjadi nilai berangka.
- Pertama, kita perlu menguji nombor jadual yang disimpan sebagai teks.
- ISNUMBER mengembalikan BENAR jika nilai sel yang dipilih adalah nombor atau yang lain akan mengembalikan SALAH.
