Bisakah Excel VLOOKUP mengembalikan semua hasil dan menggabungkannya dengan koma di antaranya?
Menonton video
- Tujuannya adalah untuk menggabungkan semua jawaban teks dari VLOOKUP
- Metode Bill: Gunakan Fungsi VBA yang disebut GetAll
- Daftar unik menggunakan Hapus Duplikat
- Metode Mike:
- Daftar unik menggunakan Filter Lanjutan
- Fungsi TEXTJOIN ditambahkan di Office 365
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Karena fungsi IF, rumus memerlukan Ctrl + Shift + Enter setiap kali Anda mengedit rumus
- Alt AQOR Enter akan menjalankan kembali Filter Lanjutan!
Transkrip Video
Episode 183: Bergabung dengan semua Pertandingan VLOOKUP
Bill Jelen: Hei, selamat datang kembali. Saatnya untuk Podcast Excel Duel lainnya. Saya Bill Jelen dari, saya akan bergabung dengan Mike Girvin untuk Excel Is Fun. Ini adalah Episode 183 kami: Gabung Semua Pertandingan VLOOKUP.
(Musik)
Baiklah, pertanyaan hari ini dari Matt. Dapatkah VLOOKUP mengembalikan semua hasil dan menggabungkannya dengan spasi koma di antara masing-masing hasil. Misalnya, 109876 yang dua ini di sini, dapatkah ia mengembalikan ruang koma Minyak Rendah Dicentang pada 12/12. Dan tentu saja jika ada lebih banyak, itu akan menghasilkan lebih banyak. Baiklah, jadi solusi saya di sini adalah menggunakan beberapa VBA. Baiklah, jadi pastikan itu disimpan sebagai xlsm atau Anda tidak dapat menjalankan VBA atau xlsb, tetapi bukan xlsx - xlsx adalah satu-satunya file yang tidak dapat menjalankan VBA. Kami akan menekan Alt + F11, pastikan Anda menggunakan Dual183 atau apa pun nama buku kerja Anda. Masukkan Modul ke dalam modul kosong dan kami akan menempelkan kode ini, oke.
Mari kita lihat fungsi GetAll ini, dan inilah nomor ID yang kita cari dan kisaran yang ingin kita lihat. Dan kita mulai, kita akan mengembalikan variabel yang disebut GetAll, jadi kita mulai dengan yang sama dengan blank kosong. Untuk setiap sel dalam rentang saya, jika Nilai Sel adalah yang kita cari, maka kita akan mengambil GetAll = GetAll & "" dan kemudian Cell.Offset (0 baris, 1 kolom), dengan kata lain nilainya itu persis di sebelah nomor ID itu karena di VBA, ini nomor ID. Jika kita menemukan nomor ID yang cocok kita ingin pergi ke 1 kolom. Sekarang, bagaimana jika Anda ingin mengubah 2 kolom di atas atau 3 kolom di atas, maka Anda mengubah 0 baris dan 1 kolom ini menjadi 2. Baik, juga periksa untuk melihat apakah - kami tidak memberi spasi koma jika ini adalah yang pertama.Jadi jika variabel GetAll saat ini adalah “” maka kita tidak akan menempatkan spasi koma, oke?
Jadi sekarang kita memiliki fungsi ini di sini, perhatikan betapa mudahnya ini untuk menyelesaikan masalah Matt. Kami akan datang ke sini dan ambil ID-nya, Ctrl + C dan tempel Ctrl + V seperti itu. Data, Hapus Duplikat, klik OK. Jadi ada daftar ID unik dan kemudian kami ingin mengatakan = getall dan kami mencari nilai itu di koma E2. Melihat melalui kisaran ini di sini, saya akan menekan F4. F4 bekerja seperti fungsi biasa. Dan sekali lagi menyingkirkan pertanyaan Matt, klik dua kali untuk menjawabnya. Ini akan berhasil.
Dan mari kita coba, mari kita coba sesuatu yang gila di sini. Mari kita lakukan Frase 1 dan taruh beberapa di antaranya seperti Frase 1 hingga 10. Kami akan menandatangani semua ini ke 109999. Tempel, lalu tempel di sini. Salin rumus itu ke bawah, edit rumusnya sampai ke bawah, tentunya. Ya. Dan itu akan mengembalikan semua Frase tersebut. Baiklah, jadi itu solusi saya, VBA, ada fungsi kecil di sana. Mike, mari kita lihat apa yang kamu punya.
Mike Girvin: Terima kasih ,. GetAll, itu adalah fungsi VBA yang luar biasa. Baiklah, saya akan membahas lembarannya di sini. Saya sudah mengubahnya menjadi tabel Excel sehingga ketika kami menambahkan catatan di bawah ini, semoga semuanya akan diperbarui.
Sekarang, hal pertama yang akan saya lakukan dalam dua bagian. Saya dapat melakukan rumus di sini untuk mengekstrak daftar unik tetapi saya ingin melihat opsi lain: Filter tingkat lanjut memiliki opsi ekstrak daftar unik dan dapat diperbarui. Saya akan menyorot hanya data kolom ID, ke Filter Lanjutan atau saya akan menggunakan keyboard Alt, A, Q. Sekarang, daftar Filter di tempat, tidak mungkin. Saya ingin menyalinnya ke lokasi lain. Itu hanya mendapat kolom A dan karena itu adalah tabel Excel yang akan diperluas nanti. Saya tidak memiliki kriteria apa pun, saya ingin menyalinnya ke D1 dan memeriksa catatan unik saja. Klik OK.
Sekarang, saya akan datang ke sini, Semua Komentar masuk dan saya akan menggunakan fungsi yang hanya berfungsi di Excel 2016 Office 365: = fungsi TEXTJOIN. Fungsi ini saja layak mendapatkan versi terbaru dari Excel. Ini adalah tugas umum yang ingin dilakukan orang, menggabungkan banyak hal bersama. Sekarang pembatas kita di ",", dan hal hebat tentang fungsi ini adalah kita dapat memberitahukannya ke Abaikan sel kosong. Sekarang, saya bisa meletakkan TRUE, 1 atau Leave it, Abaikan. Jadi, saya akan Meninggalkannya, Abaikan. Dan di sinilah kita membutuhkan teks kita. Kita akan menggunakan fungsi IF untuk memfilter dan mendapatkan item yang kita inginkan saja. Saya akan mengatakan melihat seluruh kolom ini di sini: Nama Tabel dan kemudian di () nama Bidang, apakah salah satu dari Anda = untuk referensi sel relatif ini, itulah tes logisnya. Jika saya mengklik ini dan menekan tombol F9 untuk mengevaluasi,Anda bisa lihat sekarang kami hanya memiliki 2 TRUES, Ctrl + Z sekarang saya mengetik koma dan dengan array Trues and Falses, sekarang saya bisa memberikan item untuk dipilih. Jadi sekarang, kita hanya akan memilih item yang BENAR di sini dari kisaran ini. Koma dan saya ingin memastikan untuk meletakkan "" - yang akan muncul sebagai sel kosong sehubungan dengan argumen kedua di TEXTJOIN.
Sekarang, saya akan menutup tanda kurung dan sekarang fungsi IF akan membuat string Trues dan Falses itu, item sebenarnya dari kisaran ini akan diambil jika dilihat True dan semua item lainnya akan memiliki sel kosong itu. Dan coba tebak? TEXTJOIN akan sepenuhnya mengabaikan semua sel kosong itu dan mengembalikan hanya item yang cocok dengan ID ini, lalu menggabungkannya dengan pembatas itu. Sekarang ini jelas merupakan rumus Array yang membutuhkan penekanan tombol khusus Ctrol + Shift + Enter. Argumen uji logis menahan operasi Array kami dan argumen itu tidak dapat menghitung operasi Array ini dengan benar kecuali kami menggunakan keyboard Ctrl + Shift + Enter. Sekarang saya akan menutup tanda kurung. Sebenarnya kita bisa membuktikan 1 di sini di Teks 1 jika saya F9 semua ini, kita bisa melihat kita mendapatkan 2 item, sisa sel kosong itu akan diabaikan. Ctrl + Z. Sekarang, biarkan 's masukkan ini ke dalam sel dengan Ctrl + Shift + Enter. Segera cari Formula Bar. Tanda kurung kurawal itu Excel memberi tahu Anda bahwa ia memahami dan menghitung ini sebagai rumus Array. Sekarang saya dapat mengklik dua kali dan mengirimkannya ke bawah. Itu terlihat bagus.
Saya akan pergi ke sel terakhir dan menekan F2 untuk memverifikasi bahwa semua rentang terlihat dengan benar. Sekarang yang tidak ingin saya lakukan adalah saya tidak ingin menekan Enter karena rumus tersebut setelah kita taruh di Edit Mode hanya akan menghitung dengan benar jika kita menggunakan Ctrl + Shift + Enter; atau, karena kita sudah memasukkan rumus, kita bisa menggunakan tombol Esc untuk kembali ke apa pun yang ada di dalam sel sebelum kita memasukkannya ke dalam Mode Edit.
Sekarang, mari kita uji ini. Saya akan mengklik di sel terakhir di sini dan tekan Tab lalu ketik ID baru, Tab, Tab. Rekor baru lainnya, Tab, dan saya sudah dapat melihat bahwa saya tidak memiliki cukup pekerjaan di sini. Saya, kita akan menempatkan - Sempurna dan kemudian Masuk. Sekarang, ini tidak akan diperbarui secara otomatis seperti jika kita memiliki banyak rumus yang kita hitung item unik dan kemudian mengekstrak item unik, tetapi tidak masalah. Menonton ini. Kami dapat memperbarui daftar rekaman unik ini karena kami menggunakan Filter Tingkat Lanjut dan tidak masalah dari sel mana Anda memulai karena ketika Filter Tingkat Lanjut dipanggil, itu mengingat rentang ekstrak dan rentang yang semula dilihat. Anda dapat mengklik Filter Lanjutan atau menggunakan keyboard Alt + A + Q. Kami memang harus memilih Salin ke lokasi lain, tetapi lihat itu.Ini benar-benar diingat dan diperluas ke A13 karena fitur Tabel Excel. Itu mengingat kisaran ekstrak. Saya harus memeriksa catatan unik saja tetapi klik OK.
Sekarang, saya harus datang dan menyalin rumus ini. Dan begitulah, menggunakan Filter Lanjutan dan fungsi TEXTJOIN yang menakjubkan dengan, dalam operasi Array untuk mendapatkan item yang cocok saja. Baiklah, kembalikan ke.
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 berterima kasih kepada semua orang karena telah mampir. Sampai jumpa di lain waktu untuk podcast Dueling Excel lainnya dari dan Excel Is Fun.
Unduh berkas
Unduh file sampel di sini: Duel183.xlsm
