Bolehkah Excel VLOOKUP mengembalikan semua hasil dan menggabungkannya dengan koma di antara?
Tonton Video
- Tujuannya adalah untuk menggabungkan semua jawapan teks dari VLOOKUP
- Kaedah Bill: Gunakan Fungsi VBA yang disebut GetAll
- Senarai unik menggunakan Remove Duplicates
- Kaedah Mike:
- Senarai unik menggunakan Penapis Lanjutan
- Fungsi TEXTJOIN ditambahkan di Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Kerana fungsi IF, formula memerlukan Ctrl + Shift + Enter setiap kali anda mengedit formula
- Alt AQOR Enter akan menjalankan semula Penapis Lanjutan!
Transkrip Video
Episod 183: Sertailah semua Pertandingan VLOOKUP
Bill Jelen: Hei, selamat datang kembali. Sudah tiba masanya untuk Dueling Excel Podcast yang lain. Saya Bill Jelen dari, saya akan disertai oleh Mike Girvin untuk Excel Is Fun. Ini Episod 183 kami: Sertailah Semua Pertandingan VLOOKUP.
(Muzik)
Baiklah, soalan hari ini dari Matt. Bolehkah VLOOKUP mengembalikan semua hasil dan menggabungkannya dengan ruang koma di antara masing-masing. Sebagai contoh, 109876 yang mana dua ini di sini, bolehkah ia mengembalikan ruang koma Minyak Rendah Diperiksa pada 12/12. Dan tentu saja jika ada lebih banyak, ia akan kembali lebih banyak. Baiklah, jadi penyelesaian saya di sini adalah menggunakan beberapa VBA. Baiklah, jadi pastikan ia disimpan sebagai xlsm atau anda tidak dapat menjalankan VBA atau xlsb, tetapi tidak xlsx - xlsx adalah satu-satunya fail yang tidak dapat menjalankan VBA. Kami akan menekan Alt + F11, pastikan anda menggunakan Dual183 atau apa sahaja nama buku kerja anda. Masukkan Modul ke dalam modul kosong dan kita akan menampal kod ini, baiklah.
Mari lihat fungsi GetAll ini, dan inilah nombor ID yang kami cari dan kemudian julat yang ingin kami cari. Dan kita bermula, kita akan mengembalikan pemboleh ubah yang dipanggil GetAll, jadi kita mulakan dengan yang sama dengan kosong. Untuk setiap sel dalam julat saya, jika Nilai Sel adalah yang kita cari maka kita akan mengambil GetAll = GetAll & "" dan kemudian Cell.Offset (0 baris, 1 lajur), dengan kata lain nilai itu betul-betul di sebelah nombor ID itu kerana kembali ke VBA, inilah nombor ID. Sekiranya kita menjumpai nombor ID yang sepadan, kita mahu menambah 1 lajur. Sekarang, bagaimana jika anda ingin menambah 2 lajur atau 3 lajur, maka anda menukar 0 baris dan 1 lajur ini menjadi 2. Baiklah, periksa juga untuk melihat apakah - kami tidak meletakkan ruang koma jika ini yang pertama.Oleh itu, jika pembolehubah GetAll pada masa ini "" maka kita tidak akan meletakkan ruang koma, okey?
Oleh itu, sekarang kita mempunyai fungsi ini, perhatikan betapa mudahnya menyelesaikan masalah Matt. Kita akan datang ke sini dan mari kita ambil IDnya, Ctrl + C dan tampal Ctrl + V seperti itu. Data, Buang Pendua, klik OK. Oleh itu, terdapat senarai ID yang unik dan kemudian kami ingin mengatakan = getall dan kami mencari nilai itu dalam koma E2. Melihat julat ini di sini, saya akan menekan F4. F4 berfungsi seperti fungsi biasa. Dan sekali lagi mengalihkan soalan Matt, klik dua kali untuk menembaknya. Ia akan berjaya.
Dan mari kita cuba, mari mencuba sesuatu yang gila di sini. Mari buat Frasa 1 dan masukkan sebilangannya seperti Frasa 1 hingga 10. Kami akan menandatangani semua ini ke 109999. Tampal, dan kemudian tampal di sini. Salin formula itu ke bawah, edit formula sehingga tentu saja sampai ke bawah, tentu saja. Yup. Dan ia akan mengembalikan semua Frasa tersebut. Baiklah, jadi itu adalah penyelesaian saya, VBA, sedikit fungsi di sana. Mike, mari lihat apa yang anda ada.
Mike Girvin: Terima kasih,. GetAll, itu adalah fungsi VBA yang hebat. Baiklah, saya akan pergi ke helaian di sini. Saya sudah menukarnya ke jadual Excel sehingga apabila kita menambah rekod di bawah, semoga semuanya akan dikemas kini.
Sekarang perkara pertama yang akan saya lakukan ini dalam dua bahagian. Saya boleh membuat formula di sini untuk mengekstrak senarai unik tetapi saya ingin melihat pilihan lain: Penapis lanjutan mempunyai pilihan senarai unik ekstrak dan ia boleh dikemas kini. Saya akan menyerlahkan hanya data lajur ID, ke Penapis Lanjutan atau saya akan menggunakan papan kekunci Alt, A, Q. Sekarang, Senarai senarai di tempat, tidak mungkin. Saya mahu Salin ke lokasi lain. Ia hanya mendapat lajur A. dan kerana jadual Excel yang akan berkembang kemudian. Saya tidak mempunyai kriteria, saya mahu menyalinnya ke D1 dan menyemak rekod Unik sahaja. Klik OK.
Sekarang, saya akan datang ke sini, Semua Komen masuk dan saya akan menggunakan fungsi yang hanya berfungsi di Excel 2016 Office 365: = fungsi TEXTJOIN. Fungsi ini sahaja bernilai mendapatkan versi terbaru Excel. Ini adalah tugas biasa yang ingin dilakukan oleh orang lain, bergabung bersama banyak perkara. Sekarang pembatas kita dalam ",", dan yang paling menarik mengenai fungsi ini ialah kita boleh memberitahu untuk Mengabaikan sel kosong. Sekarang, saya boleh meletakkan BENAR, 1 atau Tinggalkan, Hapus. Jadi, saya akan Tinggalkan, Hilangkan. Dan di sinilah kita memerlukan teks kita. Kami akan menggunakan fungsi IF untuk menyaring dan mendapatkan item yang kami mahukan. Saya akan mengatakan melihat seluruh lajur ini di sini: Nama Jadual dan kemudian di () nama Medan, ada antara anda = dengan rujukan sel relatif ini, itulah ujian logiknya. Sekiranya saya mengklik ini dan tekan kekunci F9 untuk menilai,anda dapat melihat sekarang kita hanya mempunyai 2 TRUES, Ctrl + Z sekarang saya menaip koma dan dengan susunan Trues and Falses, sekarang saya dapat memberikannya item untuk dipilih. Jadi sekarang, kami akan memilih hanya item yang BENAR di sini dari julat ini. Koma dan saya ingin memastikan meletakkan "" - yang akan muncul sebagai sel kosong berkenaan dengan argumen kedua dalam TEXTJOIN.
Sekarang, saya akan menutup kurungan dan sekarang fungsi IF akan membuat rentetan Trues and Falses, item sebenar dari julat ini akan diambil jika melihatnya Benar dan semua item lain akan mempunyai sel kosong itu. Dan teka apa? TEXTJOIN akan mengabaikan semua sel kosong tersebut dan mengembalikan item yang sepadan dengan ID ini, dan kemudian menggabungkannya dengan pembatas tersebut. Sekarang ini sudah pasti formula Array yang memerlukan ketukan kekunci khas Ctrol + Shift + Enter. Argumen ujian logik menahan operasi Array kami dan argumen itu tidak dapat mengira operasi Array ini dengan betul kecuali kami menggunakan papan kekunci Ctrl + Shift + Enter. Sekarang saya akan menutup kurungan. Sebenarnya kita dapat membuktikan 1 di sini dalam Teks 1 jika saya F9 semua ini, kita dapat melihat kita mendapat 2 item tersebut, sel selebihnya akan diabaikan. Ctrl + Z. Sekarang, mari 's masukkan ini ke dalam sel dengan Ctrl + Shift + Enter. Segera cari ke Formula Bar. Kurungan keriting itu adalah Excel yang memberitahu anda bahawa ia memahami dan menghitungnya sebagai formula Array. Sekarang saya boleh mengklik dua kali dan menghantarnya. Itu kelihatan baik.
Saya akan pergi ke sel terakhir dan tekan F2 untuk mengesahkan bahawa semua julat kelihatan dengan betul. Sekarang yang saya tidak mahu lakukan ialah saya tidak mahu menekan Enter kerana formula itu setelah kita memasukkannya ke dalam Mod Edit hanya akan mengira dengan betul jika kita menggunakan Ctrl + Shift + Enter; atau, kerana kita sudah memasukkan formula, kita hanya dapat menggunakan kunci Esc untuk kembali ke apa pun yang ada di dalam sel sebelum kita memasukkannya ke dalam Mod Edit.
Sekarang, mari kita uji ini. Saya akan klik di sel terakhir di sini dan tekan Tab dan kemudian taip ID baru, Tab, Tab. Satu lagi rekod baru, Tab, dan saya sudah dapat melihat bahawa saya tidak mempunyai cukup kerja di sini. Saya, kita akan meletakkan - Sempurna dan kemudian Masukkan. Sekarang, ini tidak akan dikemas kini secara automatik seperti jika kita mempunyai banyak formula bahawa kita mengira item unik dan kemudian mengekstrak item unik, tetapi tidak ada masalah. Tengok ini. Kami boleh mengemas kini senarai rekod unik ini kerana kami menggunakan Penapis Lanjutan dan tidak kira sel mana anda memulakannya kerana apabila Penapis Lanjutan dipanggil, ia mengingati julat ekstrak dan rentang yang awalnya dilihat. Anda boleh mengklik Penapis Lanjutan atau menggunakan papan kekunci Alt + A + Q. Kita mesti memilih Salin ke lokasi lain, tetapi lihatlah.Ini benar-benar diingati dan berkembang menjadi A13 kerana ciri Jadual Excel. Ia mengingati julat ekstrak. Saya mesti menyemak rekod Unik sahaja tetapi klik OK.
Now, I have to come over and copy this formula down. And there you go, using Advanced Filter and the amazing TEXTJOIN function with, in Array operation to get just the items that match. Alright, throw back to.
Bill Jelen: Hey, Mike, that is awesome. Alright, wrapping up this episode. I used the VBA function called GetAll, and my unique list was created by Remove Duplicates which is far easier than Advanced Filter but the problem is it’s a one-time thing. It doesn't remember the previous settings. Mike created his unique list using Advanced Filter which means that he could later redo that Advanced Filter without re-specifying the input range and the extract range. And then TEXTJOIN, a beautiful new function, added an Office 365. Mike says that alone is a reason to get the latest Office. I said the TEXTJOIN would be life-changing. TEXTJOIN is awesome because it can handle Arrays.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Oh hei, saya ingin mengucapkan terima kasih kepada semua orang kerana berhenti. Kami akan berjumpa anda di lain masa untuk podcast Dueling Excel yang lain dan Excel Is Fun.
Muat turun fail
Muat turun fail contoh di sini: Duel183.xlsm
