Note: The other languages of the website are Google-translated. Back to English

Bagaimana cara mengembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma?

Di Excel, kita dapat menerapkan fungsi VLOOKUP untuk mengembalikan nilai pertama yang cocok dari sel tabel, tetapi, terkadang, kita perlu mengekstrak semua nilai yang cocok dan kemudian dipisahkan oleh pemisah tertentu, seperti koma, tanda hubung, dll… menjadi satu sel seperti gambar berikut yang ditampilkan. Bagaimana kita bisa mendapatkan dan mengembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma di Excel?

doc mengembalikan beberapa nilai yang dipisahkan koma 1

Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Fungsi Buatan Pengguna

Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Kutools for Excel


Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Fungsi Buatan Pengguna

Biasanya, tidak ada cara langsung bagi kami untuk mengekstrak dan mengembalikan beberapa nilai yang cocok dan dipisahkan dengan koma ke dalam satu sel, di sini, Anda dapat membuat Fungsi Buatan Pengguna untuk menyelesaikan pekerjaan ini, lakukan hal berikut:

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Klik Menyisipkan > Modul, dan tempel kode berikut di Modul Jendela.

Kode VBA: Kembalikan beberapa nilai pencarian ke dalam satu sel yang dipisahkan koma

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby Extendoffice
    Dim I As Long
    Dim xRet As String
    For I = 1 To LookupRange.Columns(1).Cells.Count
        If LookupRange.Cells(I, 1) = LookupValue Then
            If xRet = "" Then
                xRet = LookupRange.Cells(I, ColumnNumber) & Char
            Else
                xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
            End If
        End If
    Next
    SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

3. Kemudian simpan kode ini dan tutup jendela Modul, kembali ke lembar kerja Anda, dan masukkan rumus ini: = SingleCellExtract (D2, A2: B15,2, ",") ke dalam sel kosong yang ingin Anda kembalikan hasilnya. Lalu tekan Enter kunci untuk mendapatkan hasilnya, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 2

Note: Dalam rumus di atas:

D2: menunjukkan nilai sel yang ingin Anda cari;

A2: B15: adalah rentang data yang Anda inginkan untuk mengambil data;

2: angka 2 adalah nomor kolom yang nilai pencocokannya akan dikembalikan;

,: koma adalah pemisah yang ingin Anda pisahkan beberapa nilai.

Anda dapat mengubahnya sesuai kebutuhan Anda.


Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Kutools for Excel

Jika Anda memiliki Kutools untuk Excel, tugas ini tidak lagi menjadi masalah. Itu Lanjutan Gabungkan Baris utilitas dapat membantu Anda menggabungkan semua nilai relatif berdasarkan kolom.

Kutools untuk Excel : dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari

Setelah menginstal Kutools untuk Excel, lakukan hal berikut:

1. Pilih rentang data yang ingin Anda gabungkan semua nilai yang cocok berdasarkan kolom.

2. Klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris, lihat tangkapan layar:

3. di Gabungkan Baris Berdasarkan Kolom kotak dialog, klik nama kolom yang ingin Anda gabungkan berdasarkan, lalu klik Kunci utama tombol, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 4

4. Lalu klik nama kolom lain yang ingin Anda gabungkan nilainya yang cocok, dan klik Menggabungkan untuk memilih satu pemisah untuk memisahkan nilai gabungan, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 5

5. Lalu klik OK tombol, semua sel yang sesuai dengan nilai yang sama telah digabungkan menjadi satu sel yang dipisahkan dengan koma, lihat tangkapan layar:

doc mengembalikan beberapa nilai yang dipisahkan koma 6 2 doc mengembalikan beberapa nilai yang dipisahkan koma 7

Klik untuk mengetahui detail selengkapnya tentang utilitas Advanced Combine Rows ini…

Unduh dan uji coba gratis Kutools untuk Excel Sekarang!


Demo: Kembalikan beberapa nilai pencarian dalam satu sel yang dipisahkan koma dengan Kutools for Excel

Kutools untuk Excel: dengan lebih dari 300 add-in Excel yang praktis, gratis untuk dicoba tanpa batasan dalam 30 hari. Unduh dan uji coba gratis Sekarang!

Alat Produktivitas Kantor Terbaik

Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%

  • Reuse: Masukkan dengan cepat rumus, bagan yang kompleks dan apa pun yang pernah Anda gunakan sebelumnya; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
  • Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
  • Gabungkan Sel / Baris / Kolom tanpa kehilangan Data; Pisahkan Konten Sel; Gabungkan Baris / Kolom Duplikat... Mencegah Sel Duplikat; Bandingkan Rentang...
  • Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
  • Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
  • Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
  • Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
  • Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
  • Lebih dari 300 fitur canggih. Mendukung Office / Excel 2007-2019 dan 365. Mendukung semua bahasa. Penerapan yang mudah di perusahaan atau organisasi Anda. Fitur lengkap uji coba gratis 30 hari. Jaminan uang kembali 60 hari.
tab kte 201905

Tab Office Membawa antarmuka Tab ke Office, dan Membuat Pekerjaan Anda Jauh Lebih Mudah

  • Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
  • Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
  • Tingkatkan produktivitas Anda hingga 50%, dan kurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (14)
Rated 5 dari 5 · peringkat 2
Komentar ini diminimalkan oleh moderator di situs
Ketika saya akan menempelkan dan menyimpan modul, ada pesan pop-up yang muncul bahwa pemeriksa kompatibilitas fungsionalitas kehilangan fungsi yang signifikan
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk posting ini. Tahukah Anda bagaimana saya akan memanipulasi dua bilangan bulat terpisah yang dibuat ini. Sebagai contoh, katakanlah bahwa fungsi '=SingleCellExtract' sekarang menghasilkan (1 , 2). Apakah ada cara untuk memiliki sel di sebelahnya yang berfungsi (1+.5 , 2+.5)?
Komentar ini diminimalkan oleh moderator di situs
Ini berfungsi tetapi memperlambat excel saya secara besar-besaran! Adakah tips untuk membantu mempercepat?
Komentar ini diminimalkan oleh moderator di situs
Ini tidak bekerja. Saya tidak dapat membuatnya berfungsi di aplikasi saya sendiri, jadi saya menyalin/menempelkan vba dan rumusnya dan selalu mengembalikan kesalahan
Komentar ini diminimalkan oleh moderator di situs
terima kasih, pertama saya berhasil membuat ini berfungsi tanpa memperlambat kinerja. Saya menggunakan nilai daripada teks itu jadi pertanyaan saya adalah saya ingin mengembalikan semua yang kurang dari 19 poin dalam daftar. Bisakah Ekstrak sel tunggal berfungsi untuk itu atau apakah itu harus menjadi nilai tertentu?
Komentar ini diminimalkan oleh moderator di situs
Perintah VB terputus ketika rentang lebih panjang dari 154 baris (yaitu: B154)....
Komentar ini diminimalkan oleh moderator di situs
Kesalahan muncul jika menambah ukuran array
Komentar ini diminimalkan oleh moderator di situs
Ketika 2 Kriteria Cocok maka Kembalikan Beberapa Nilai Pencarian Dalam Satu Sel yang Dipisahkan Koma
A2=B2 Kemudian Hasil Dari Range dengan "SingleCellExtract" - Tolong.......
Komentar ini diminimalkan oleh moderator di situs
Selamat pagi,

kode VBA bekerja dengan sempurna dengan lembar kerja saya, cukup jelas dan sederhana, namun, saya mencoba menemukan cara untuk memberi tahu excel agar hanya mengembalikan nilai unik. Apakah itu mungkin menggunakan kode yang sama ini?
Komentar ini diminimalkan oleh moderator di situs
Hai,

Saya ingin kode validasi yang tepat untuk beberapa nilai yang dipisahkan oleh koma dan spasi untuk setiap nilai.

Contoh:
Lucy, Tom, Nicol, Akash, Apple

Silakan putar ulang jika Anda memiliki saran.
Rated 4.5 dari 5
Komentar ini diminimalkan oleh moderator di situs
Halo, Manikanta
Untuk memisahkan beberapa nilai dengan koma dan spasi, Anda hanya perlu menambahkan spasi di belakang koma, ubah rumusnya seperti ini: =SingleCellExtract(D2,A2:B15,2,", ").
Silakan dicoba, semoga bisa membantu Anda!
Komentar ini diminimalkan oleh moderator di situs
Hai Skyyang,

Terima kasih atas tayangan ulang Anda!

Saya sudah mencoba dengan cara yang sama tetapi dalam nilai sel terakhir satu koma tambahan (,) di bawah ini adalah contohnya.

Lucy, Tom, Nicol, Akash, Apple,

Ini tidak akan berfungsi untuk file Json, jadi saya ingin nilai dipisahkan dengan koma dan spasi seperti di bawah ini.

Lucy, Tom, Nicol, Akash, Apple

Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Halo, Manikanta
Dalam hal ini, Anda dapat menerapkan Fungsi Buatan Pengguna di bawah ini:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function


Setelah menempelkan kode, silakan gunakan rumus ini: =MultipleValues($A$2:$A$15,D2,$B$2:$B$15,", ")

Silakan coba, semoga ini bisa membantu Anda!
Jika Anda masih memiliki masalah lain, silakan komentar di sini.
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-return-multiple-matching-1.png
Komentar ini diminimalkan oleh moderator di situs
Hai Skyyang,

Ini berfungsi sekarang, Terima kasih atas tanggapan cepat Anda.

Ini sangat berguna bagi saya sekali lagi Terima kasih atas bantuan Anda.

Salam,
Manikanta.
Rated 5 dari 5
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan