VLOOKUP untuk Teks

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

Di bawah ini adalah contoh excel VLOOKUP untuk teks.

Anda boleh memuat turun Templat VLOOKUP untuk Teks Excel ini di sini - Templat VLOOKUP untuk Teks Excel

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 nombor 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 pencarian, sebab utama mestilah format nombor di kedua-dua 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 dapat kita lihat, ISNUMBER mengenal pasti nilai bukan angka 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 untuk melakukan ini, berikut 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 tetingkap khas tampal pilih "darab" sebagai pilihan.

Tekan ok, ia akan menukar semua teks yang diformat 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 carian.

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-tama ia akan menukar nilai bukan numerik menjadi nilai numerik, 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 itu nombor 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 sedemikian, kita perlu memasukkan fungsi TEXT untuk nilai pencarian dalam fungsi VLOOKUP.

Masalahnya di sini adalah fungsi TEX menukar bahkan nilai numerik 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, jika berangka kita akan menggunakan LOOKUP biasa atau jika tidak, kita akan menerapkan fungsi TEKS 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.