Cara melakukan penghitungan (seperti VLOOKUP) untuk setiap item yang telah Alt + Dimasukkan dalam sel.
Menonton video
- Penampil mengunduh data dari sistem di mana setiap item dipisahkan oleh Alt + Enter
- Bill: Mengapa Anda melakukan ini? Penampil: Ini adalah cara saya mewarisi data. Saya ingin tetap seperti itu.
- Tagihan: Apa yang ingin Anda lakukan dengan 40% nilai yang tidak ada dalam tabel? Penonton: Tidak ada jawaban
- Tagihan: Ada cara yang rumit untuk menyelesaikan ini jika Anda memiliki alat Power Query terbaru.
- Sebaliknya, Makro VBA untuk mengatasinya - makro harus berfungsi sepenuhnya kembali ke Excel 2007
- Alih-alih melakukan VLOOKUP, lakukan serangkaian Temukan & Ganti dengan VBA
Transkrip Video
Pelajari Excel Dari, Podcast Episode 2150: VLOOKUP Setiap Alt + Nilai yang Dimasukkan Di Setiap Sel.
Hei. Selamat datang kembali di netcast. Saya Bill Jelen. Hari ini, salah satu pertanyaan yang lebih aneh. Seseorang berkata, hei, saya ingin melakukan VLOOKUP untuk setiap nilai di dalam sel, dan ketika saya membuka file Excel, datanya telah ALT + Dimasukkan. Jadi, ada 4 item dalam urutan ini dan semuanya dipisahkan oleh ALT + ENTER, lalu hanya 2 di sini dan 6 di sini dan seterusnya.
Saya kembali ke orang yang mengirim ini. Saya seperti, yah, ini cara yang sangat buruk untuk menyimpan data ini. Mengapa kau melakukan ini? Dan dia seperti, saya seperti saya tidak melakukannya. Ini adalah cara mengunduh data. Saya berkata, apakah boleh jika saya membaginya menjadi baris terpisah? Tidak, Anda harus tetap seperti ini.
Baik. Jadi, tidak ada cara yang baik untuk melakukan VLOOKUP untuk setiap item, dan besok, pada episode besok, 2151, saya akan menunjukkan kepada Anda bagaimana kami dapat menggunakan fitur baru di Power Query untuk melakukan ini, tetapi Anda harus melakukannya agar Office 365 memilikinya.
Jadi, hari ini, saya ingin menggunakan metode yang akan kembali ke masa lalu, dan yang telah saya lakukan di sini adalah saya telah membuat lembar kerja baru dengan LOOKUPTABLE, jadi ini adalah itemnya. Saya juga memperhatikan bahwa ada banyak hal, sekitar 40% dari hal-hal di sini, tidak ada di LOOKUPTABLE. Saya berkata, apa yang ingin Anda lakukan di sana, dan tidak ada jawaban untuk pertanyaan itu, jadi saya akan membiarkan mereka apa adanya jika saya tidak menemukan yang cocok.
Baiklah, jadi, yang saya miliki di sini adalah saya memiliki lembar bernama LOOKUPTABLE dan Anda akan melihat bahwa file saya sekarang disimpan sebagai xlsx dan saya akan menggunakan makro VBA. Untuk menggunakan makro VBA, Anda tidak bisa menjadikannya sebagai xlsx. Itu melanggar aturan. Jadi, Anda harus HEMAT AS dan simpan ini xlsm. FILE, SAVE AS, dan ubah dari BUKU KERJA menjadi XLSM BUKU KERJA MAKRO YANG DIAKTIFKAN, atau BUKU KERJA BINER - salah satunya akan berfungsi - baiklah, lalu klik SIMPAN.
Baiklah, jadi, sekarang kita diizinkan untuk menjalankan makro. ALT + F11 untuk membuka perekam makro. Anda mulai dengan layar abu-abu besar ini. SISIPKAN, MODUL, dan ada modul kami, dan ini kodenya. Jadi, saya menyebutnya ReplaceInPlace dan saya mendefinisikan satu lembar kerja. Itulah LookupTable. Anda akan meletakkan nama lembar kerja tabel pencarian asli Anda di sana, dan kemudian tabel pencarian saya dimulai di kolom A, yaitu kolom 1. Jadi, saya pergi ke baris terakhir di kolom 1, tekan tombol AKHIR dan panah ATAS, atau , tentu saja, CONTROL + panah atas akan melakukan hal yang sama, mencari tahu barisnya, dan kemudian kita akan beralih dari setiap baris dari 2 ke FinalRow. Mengapa 2? Karena judulnya ada di baris 1. Jadi saya ingin mengganti, mulai dari baris 2 sampai ke baris terakhir, dan begitu, untuk setiap baris dari 2 ke FinalRow, FromValue adalah 's di kolom A dan ToValue adalah yang ada di kolom B.
Sekarang jika, karena alasan tertentu, data Anda berada di J dan K, maka J ini akan menjadi kolom ke-10 sehingga Anda meletakkan 10 di sana, dan K akan menjadi kolom ke-11, dan kemudian, dalam Seleksi, kita akan mengganti dari FromValue ke ToValue. Ini sangat penting di sini. xlPart, xlPart - dan itu adalah L, bukan angka 1 - xlPart yang mengatakan itu akan memungkinkan kita untuk mengganti bagian sel karena nomor bagian tersebut semuanya dipisahkan oleh karakter linefeed. Meskipun Anda tidak bisa melihatnya, itu ada di sana. Jadi, itu seharusnya memungkinkan kita untuk tidak secara tidak sengaja memperbarui hal yang salah, dan kemudian xlByRows, MatchCase, False, SearchFormat, False, ReplaceFormat, False, Next i.
Baik. Jadi, ini makro kecil kami di sini. Ayo coba. Kami akan mengambil data ini, dan saya tidak ingin menghancurkan apa pun jadi saya hanya akan mengambil data asli dan menyalinnya ke kanan. Baik. Jadi, kami memiliki pilihan kami di sana. Sebenarnya, saya akan mulai dari sini. CONTROL + BACKSPACE, lalu ALT + F8 untuk mendapatkan daftar semua makro. Ada REPLACEINPLACE kami. Saya akan mengklik JALANKAN, dan di mana pun ia menemukan item di LOOKUPTABLE, nomor item itu diganti dengan item tersebut, yang tampaknya melakukan VLOOKUP, meskipun kami tidak menyelesaikannya dengan VLOOKUP sama sekali.
Baik. Jadi, hei, buku baru yang keluar - Power Excel With, Edisi 2017, 617 Excel Mysteries Solved - semua jenis tip baru yang hebat di sana.
Penutup hari ini: penampil mengunduh data dari sistem di mana setiap item dipisahkan oleh ALT + ENTER, dan kemudian perlu melakukan VLOOKUP pada setiap item, dan, Anda tahu, mengapa saya melakukan ini; jadi, orang itu berkata, saya tidak melakukannya tetapi saya harus tetap seperti ini; dan kemudian 40% dari nilai tidak ada di tabel, ya, tidak ada jawaban; jadi saya kira mereka akan menambahkan item tersebut ke tabel; sekarang, besok, kita akan berbicara tentang cara menyelesaikan ini dengan Power Query, tetapi, hari ini, makro ini akan berfungsi sepenuhnya di semua versi Windows Excel, setidaknya kembali ke Excel 2007; jadi, alih-alih VLOOKUP, cukup temukan dan ganti dengan VBA.
Baiklah. Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2150.xlsm
