Excel VLOOKUP hebat, tetapi tidak akan berfungsi apabila anda mempunyai situasi tertentu. Hari ini, lihat bagaimana menyelesaikan masalah VLOOKUP.
VLOOKUP adalah fungsi kegemaran saya di Excel. Sekiranya anda dapat melakukan VLOOKUP, anda dapat menyelesaikan banyak masalah di Excel. Tetapi ada perkara yang boleh meningkatkan VLOOKUP. Topik ini membincangkan beberapa daripadanya.
Tetapi pertama, asas VLOOKUP dalam bahasa Inggeris biasa.
Data dalam A: C berasal dari jabatan IT. Anda meminta penjualan mengikut item dan tarikh. Mereka memberi anda nombor Item. Anda memerlukan Penerangan Item. Daripada menunggu jabatan IT menjalankan semula data, anda dapati jadual yang ditunjukkan di lajur F: G.
Anda mahu VLOOKUP mencari item di A2 semasa mencari melalui lajur pertama jadual dengan harga $ F $ 3: $ G $ 30. Apabila VLOOKUP menemui padanan di F7, anda mahu VLOOKUP mengembalikan keterangan yang terdapat di lajur kedua jadual. Setiap VLOOKUP yang mencari pertandingan yang tepat harus berakhir dengan Salah (atau sifar, yang setara dengan Salah). Formula di bawah disusun dengan betul.
Perhatikan bahawa anda menggunakan F4 untuk menambahkan tanda empat dolar ke alamat untuk jadual carian. Semasa anda menyalin formula di lajur D, anda memerlukan alamat untuk jadual carian agar tetap berterusan. Terdapat dua alternatif umum: Anda boleh menentukan keseluruhan lajur F: G sebagai jadual carian. Atau, anda boleh memberikan nama F3: G30 dengan nama seperti ItemTable. Sekiranya anda menggunakan =VLOOKUP(A2,ItemTable,2,False), julat yang dinamakan bertindak sebagai rujukan mutlak.
Bila-bila masa anda melakukan banyak VLOOKUP, anda perlu menyusun lajur VLOOKUP. Isih ZA, dan sebarang kesalahan # N / A akan sampai ke puncak. Dalam kes ini, ada satu. Item BG33-9 hilang dari jadual carian. Mungkin ia adalah typo. Mungkin ia adalah item baru. Sekiranya baru, masukkan baris baru di mana sahaja di tengah-tengah jadual carian anda dan tambahkan item baru.
Adalah perkara biasa jika terdapat beberapa kesalahan # N / A. Tetapi dalam rajah di bawah, formula yang sama persis tidak memberikan apa-apa kecuali # N / A. Apabila ini berlaku, saya dapat melihat apakah saya dapat menyelesaikan VLOOKUP pertama. Anda mencari BG33-8 yang terdapat di A2. Mulakan pelayaran melalui lajur pertama jadual carian. Seperti yang anda lihat, nilai pemadanan jelas terdapat pada F10. Mengapa anda dapat melihatnya, tetapi Excel tidak dapat melihatnya?
Pergi ke setiap sel dan tekan kekunci F2. Inilah F10. Perhatikan bahawa kursor penyisipan muncul tepat selepas 8.
Berikut adalah sel A2 dalam mod Edit. Kursor penyisipan berjarak beberapa jarak dari 8. Ini adalah petanda bahawa pada suatu ketika, data ini disimpan dalam set data COBOL lama. Kembali ke COBOL, jika bidang Item ditakrifkan sebagai 10 aksara dan anda hanya menaip 6 aksara, COBOL akan memasukkannya dengan 4 ruang tambahan.
Penyelesaian? Daripada mencari A2, cari TRIM(A2).
Fungsi TRIM () menghilangkan ruang depan dan belakang. Sekiranya anda mempunyai banyak ruang antara kata, TRIM akan menukarnya menjadi satu ruang. Pada gambar di bawah terdapat ruang sebelum dan selepas kedua-dua nama di A1. =TRIM(A1)membuang semua kecuali satu ruang di A3.
Ngomong-ngomong, bagaimana jika masalahnya berada di ruang lajur di F dan bukannya di lajur A? Tambahkan lajur fungsi TRIM () ke E, menunjuk ke lajur F. Salin dan tampal sebagai nilai di F untuk membuat carian mula berfungsi semula.
Sebab lain yang sangat umum bahawa VLOOKUP tidak berfungsi ditunjukkan di sini. Lajur F mempunyai nombor nyata. Lajur A mempunyai teks yang kelihatan seperti nombor.
Pilih semua lajur A. Tekan alt = "" + D, E, F. Ini menjadikan Teks lalai ke Lajur dan akan menukar semua nombor teks menjadi nombor nyata. Pencarian mula berfungsi semula.
Tonton Video
- VLOOKUP menyelesaikan banyak masalah
- Masalah VLOOKUP biasa:
- Sekiranya VLOOKUP mula berfungsi, tetapi # N / A menjadi lebih menonjol: terlupa $ dalam jadual carian
- Beberapa # N / A: item yang hilang dari jadual
- Tiada VLOOKUP berfungsi: periksa ruang ketinggalan
- Keluarkan ruang belakang dengan TRIM
- Nombor dan nombor disimpan sebagai teks
- Pilih kedua lajur dan gunakan alt = "" + DEF
- Episod merangkumi jenaka yang dianggap lucu oleh kedua-dua akauntan dan orang IT, tetapi dengan alasan yang berbeza
Transkrip Video
Belajar Excel dari podcast, episod 2027 - Penyelesaian Masalah VLOOKUP!
Baiklah, siarkan keseluruhan buku ini, klik “i” di sudut kanan atas untuk masuk ke senarai main!
VLOOKUP Adakah fungsi kegemaran saya di seluruh Excel, dan saya selalu menceritakan jenaka ini dalam satu seminar saya, dan ia menjadi ketawa sama ada anda orang IT atau bukan orang IT. Saya selalu berkata "Baiklah, kami mempunyai data ini di sini di sebelah kiri, dan saya dapat melihat data tersebut dan memberitahu bahawa data itu berasal dari jabatan IT kerana itulah yang saya minta, tetapi sebenarnya bukan yang saya perlukan. Saya meminta tarikh dan kuantiti item, dan mereka memberi saya tarikh dan kuantiti nombor item, tetapi mereka tidak bersusah payah memberi saya keterangan, bukan? " Dan akauntan selalu mentertawakan ini, kerana ini berlaku pada mereka sepanjang masa, dan orang IT seperti "Baiklah, saya memberikan apa yang anda minta, itu bukan salah saya!" Oleh itu, mereka berdua menganggap itu lucu kerana alasan yang berbeza, jadi anda tahu, dan lelaki IT sibuk, dia tidak dapat kembali menulis semula pertanyaan,jadi kita mesti melakukan sesuatu untuk menyelamatkan diri kita sendiri.
Oleh itu, meja kecil ini saya salin dari tempat lain di komputer saya, dalam bahasa Inggeris biasa, apa yang dilakukan oleh VLOOKUP adalah "Hai, kami mempunyai nombor item W25-6." Kami mempunyai jadual di sini, saya ingin melayari lajur pertama jadual sehingga saya menemui nombor item itu, dan kemudian mengembalikan sesuatu dari baris itu. Baiklah, dalam kes ini, yang saya mahukan ialah lajur ke-2 dari baris itu. Dan kemudian pada akhir setiap VLOOKUP kita harus meletakkan salah FALSE atau 0. Sekarang di sini, setelah saya memilih julat, saya akan menekan kekunci F4, dan kemudian saya mahukan lajur ke-2 dan kemudian SALAH untuk tepat padanan. Jangan sekali-kali memilih pertandingan yang hampir, tidak pernah, tidak pernah! Ini bukan pertandingan yang hampir, ia adalah perkara yang sangat istimewa, ia tidak berfungsi sepanjang masa. Sekiranya anda ingin menyatakan semula nombor anda kepada Suruhanjaya Sekuriti dan Bursa, dengan segala cara jangan gunakan,BENAR atau biarkan sahaja, SALAH. Tetapi setiap VLOOKUP yang pernah anda buat harus berakhir, FALSE atau, 0, 0 lebih pendek daripada FALSE, anda seharusnya meletakkan FALSE di sana, tetapi 0 adalah perkara yang sama dengan FALSE.
Baiklah sekarang, penyelesaian masalah, perkara pertama, apabila anda melakukan banyak VLOOKUP, sangat normal jika mempunyai beberapa # N / As, bukan? Dan Saya Sentiasa menjumpai # N / A dengan menggunakan Data, ZA, yang membawa # N / As ke puncak, di sana, BG33-9, sama ada itu adalah kesalahan ketik atau item baru, baiklah, dan kami mendapat untuk mengetahuinya. Jadi di sini di sebelah kanan saya mempunyai data baru, saya akan memotongnya, dan kemudian Alt + IED, masukkan sel-sel tersebut di tengah, tidak perlu mengikut abjad, jadual ini tidak perlu disusun. Apabila anda menggunakan versi, SALAH, jadualnya tidak - anda boleh meletakkannya di mana sahaja yang anda mahukan, saya tidak meletakkannya pada akhirnya kerana saya tidak perlu menulis semula formula, saya hanya mahu formula bekerja. Baiklah, jadi beberapa # N / A, sangat normal, tapi lihat ini.
Apabila anda memulakan dengan jawapan yang sesuai, tetapi kemudian # N / A mula muncul sedikit kerap, dan akhirnya ia muncul sepanjang jalan, itu adalah tanda pasti bahawa anda tidak menutup rujukan jadual. Baiklah, jadi di sini jadual bergerak ketika saya menyalin formula ke bawah, kanan, dan jadi saya bernasib baik kerana memukul beberapa yang berada di akhir senarai. Tetapi akhirnya saya sampai ke titik di mana ia mencari di sini dalam sel kosong sepenuhnya, dan tentu saja tidak ada yang dijumpai. Baiklah, jadi itulah perkara pertama, anda mendapat pasangan yang berfungsi, beberapa # N / A, beberapa lagi yang berjaya, dan kemudian semua # N / A masih ada - tanda pasti bahawa anda tidak meletakkan $ dalam.
Kembali sahaja, F2 untuk mod Edit, pilih titik dua, tekan F4, yang meletakkan semua $ in, klik dua kali untuk menembak ke bawah, dan semuanya mula berfungsi lagi, baiklah. Seterusnya, formula yang sama persis, formula sempurna, BG33-8 lihat, kita tidak mendapat hak itu. Baiklah, jadi saya lihat, BG33-8, hei, ini dia, ada di sana, berwarna merah, semestinya saya melihatnya! Oleh itu, saya sampai di sebelah kanan ini, saya menekan F2, dan saya melihat titik penyisipan yang berkelip, dan melihat, tepat selepas jam 8, seperti itu, dan kemudian saya datang ke sini dan saya menekan F2, ada beberapa ruang ketinggalan di sana. Ini sangat, sangat biasa pada zaman COBOL, mereka akan berkata "Anda tahu, lihat, kami akan memberi anda 10 ruang untuk nombor item, dan jika anda tidak menaip semua 10 ruang, mereka akan mengisi ruang . " Dan ini sangat biasa,dan penyelesaian terbaik di sini adalah untuk menyingkirkan ruang utama dan ketinggalan dengan fungsi TRIM. Daripada A2 gunakan TRIM (A2), klik dua kali untuk menembak ke bawah, baiklah, masih BG33-9 kembali di jadual lain.
Baiklah, agar anda memahami bagaimana TRIM berfungsi, jadi saya menaip sekumpulan ruang, John, sekumpulan ruang, Durran, dan sebilangan ruang, dan kemudian saya menggabungkan * sebelum dan selepasnya sehingga anda dapat melihat seperti apa . Apabila saya meminta TRIM (P4), kami menyingkirkan semua ruang utama, semua ruang belakang, dan kemudian beberapa ruang dalaman dikurangkan menjadi satu ruang. Baiklah, supaya anda dapat melihat, menggambarkan secara visual di sana, bahawa mereka menyingkirkan ruang utama dan belakang, mana-mana ruang berganda di tengah menjadi satu ruang seperti itu. Jadi TRIM, alat hebat dan hebat di gudang senjata anda, baiklah, inilah satu lagi alat yang sangat biasa.
Sekiranya bukan ruang belakang, maka perkara yang paling biasa yang saya lihat, adalah di mana di sini kita mempunyai nombor yang benar, dan di sini kita mempunyai nombor yang disimpan sebagai teks. Dan VLOOKUP tidak akan melihatnya sebagai padanan, walaupun 4399, ini adalah angka, ini teks, tidak berfungsi. Cara terpantas untuk menukar lajur teks menjadi angka, pilih lajur, 3 huruf mengikut urutan, alt = "" DEF, dan tiba-tiba, VLOOKUP kami mula berfungsi lagi, petua hebat dan hebat dari sekitar 1500 podcast yang lalu. Baiklah, jadi itu adalah masalah VLOOKUP yang paling biasa, sama ada anda lupa $, atau anda mempunyai spasi ketinggalan, atau anda mempunyai nombor dan nombor yang disimpan sebagai teks. Semua petua ini terdapat dalam buku ini "MrExcel XL", klik ihe "i" di sudut kanan atas, anda boleh membeli buku tersebut.
Baiklah, rangkuman cepat: VLOOKUP menyelesaikan banyak masalah, jika VLOOKUP mula berfungsi tetapi # N / A! menjadi lebih menyerlah, anda terlupa meletakkan $ dalam jadual carian. Sekiranya terdapat beberapa # N / A, hanya item yang hilang dari meja. Sekiranya tidak ada VLOOKUP yang berfungsi dan teksnya, periksa ruang belakang, anda boleh menggunakan fungsi TRIM. Sekiranya anda mempunyai nombor dan nombor yang disimpan sebagai teks, pilih salah satu lajur, yang mempunyai teks, kemudian lakukan alt = "" DEF harus semua kembali ke nombor.
Baiklah, hei, saya ingin mengucapkan terima kasih kerana mampir, kami akan berjumpa anda di lain kali untuk siaran net lain!
Muat turun fail
Muat turun fail contoh di sini: Podcast2027.xlsx
