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

Bagaimana cara vlookup dan menggabungkan beberapa nilai yang sesuai di Excel?

Seperti yang kita semua ketahui, file Vlookup Fungsi di Excel dapat membantu kita mencari nilai dan mengembalikan data yang sesuai di kolom lain, tetapi secara umum, ini hanya bisa mendapatkan nilai relatif pertama jika ada beberapa data yang cocok. Pada artikel ini, saya akan berbicara tentang cara vlookup dan menggabungkan beberapa nilai yang sesuai hanya dalam satu sel atau daftar vertikal.

Vlookup dan mengembalikan beberapa nilai yang cocok secara vertikal dengan rumus

Vlookup dan menggabungkan beberapa nilai yang cocok dalam sel dengan User Defined Function

Vlookup dan gabungkan beberapa nilai yang cocok dalam sel dengan Kutools for Excel


Misalkan, saya memiliki rentang data berikut, untuk mendapatkan semua nilai yang sesuai berdasarkan nilai tertentu secara vertikal seperti gambar berikut yang ditampilkan, Anda dapat menerapkan rumus array.

doc vlookup menggabungkan 1

1. Masukkan rumus ini: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") ke dalam sel kosong tempat Anda ingin meletakkan hasilnya, misalnya, E2, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan basis nilai relatif pada kriteria tertentu, lihat tangkapan layar:

doc vlookup menggabungkan 2

Note: Dalam rumus di atas:

A1: A16 adalah rentang kolom yang berisi nilai spesifik yang ingin Anda cari;

D2 menunjukkan nilai spesifik yang ingin Anda vlookup;

B1: B16 adalah rentang kolom yang datanya ingin Anda kembalikan;

$ 1: $ 16 menunjukkan referensi baris dalam rentang tersebut.

2. Kemudian pilih sel E2, dan seret gagang isian ke bawah ke sel sampai Anda mendapatkan sel kosong, dan semua nilai yang cocok dicantumkan ke dalam kolom seperti gambar berikut yang ditampilkan:

doc vlookup menggabungkan 3


Alih-alih mendapatkan nilai relatif secara vertikal, terkadang, Anda ingin nilai yang cocok menjadi satu sel dan menggabungkannya dengan pemisah tertentu. Dalam hal ini, Fungsi Buatan Pengguna berikut dapat membantu Anda.

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: Vlookup dan menggabungkan beberapa nilai yang cocok dalam satu sel

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Kemudian simpan dan tutup kode ini, kembali ke lembar kerja, dan masukkan rumus ini: = cusvlookup (D2, A1: B16,2) ke dalam sel kosong tempat Anda ingin meletakkan hasilnya, dan tekan Enter kunci, semua nilai yang sesuai berdasarkan data tertentu telah dikembalikan ke dalam satu sel dengan pemisah spasi, lihat tangkapan layar:

doc vlookup menggabungkan 4

Note: Dalam rumus di atas: D2 menunjukkan nilai sel yang ingin Anda cari, A1: B16 adalah rentang data yang ingin Anda ambil datanya, jumlahnya 2 adalah nomor kolom dari mana nilai yang cocok akan dikembalikan, Anda dapat mengubah referensi ini sesuai kebutuhan Anda.


Jika Anda memiliki Kutools untuk Excel, Dengan yang Lanjutan Gabungkan Baris fitur, Anda dapat dengan cepat menyelesaikan pekerjaan ini dengan mudah. Fitur ini dapat membantu Anda menggabungkan semua nilai yang cocok dengan pembatas tertentu berdasarkan data yang sama di kolom lain.

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 Anda inginkan untuk mendapatkan nilai yang sesuai berdasarkan data tertentu.

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

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

doc vlookup menggabungkan 6

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

doc vlookup menggabungkan 7

5. Dan kemudian klik Ok tombol, semua nilai yang sesuai berdasarkan nilai yang sama telah digabungkan bersama dengan pemisah tertentu, lihat tangkapan layar:

doc vlookup menggabungkan 8 2 doc vlookup menggabungkan 9

 Unduh dan uji coba gratis Kutools untuk Excel Sekarang!


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-2021 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.
  • Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
officetab bawah
Urutkan komentar berdasarkan
komentar (16)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Bagaimana cara mendapatkan hasilnya. Tolong bantu. data data1 hasil a 1 a1 b 2 a2 c b1 b2 c1 c2
Komentar ini diminimalkan oleh moderator di situs
Saat menggunakan cusvlookup apakah ada cara untuk menambahkan nama belakang juga dengan koma di antaranya yang mungkin muncul di Kolom C
Komentar ini diminimalkan oleh moderator di situs
Menyukai fungsi untuk Excel 2013 tetapi sedikit mengubahnya untuk mengubah karakter pemisah menjadi ";" alih-alih " " lalu hapus awalan ";" dari nilai gabungan Hasil nilai yang cocok dalam contoh saya akan memiliki ;result01 atau ;result01;result02 . Menambahkan ekstra If Left(xResult, 1) = ";" untuk menghapus tambahan ";" di awal string jika itu adalah karakter pertama. Saya yakin ada cara yang lebih rapi untuk melakukannya tetapi itu berhasil untuk saya. :) Fungsi CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" Untuk Setiap rng Dalam pWorkRng If rng = pValue Kemudian xResult = xResult & ";" & rng.Offset(1, pIndex - 0) Jika Kiri(xResult, 1) = ";" Maka xResult = MID(xResult,1) End If End If Next CusVlookup = xResult End Function
Komentar ini diminimalkan oleh moderator di situs
Buat kondisi if untuk hasil jika kosong.

Fungsi CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Redup x Sebagai Rentang
Hasil redup Sebagai String
hasil = ""
Untuk Setiap x Dalam rentang pencarian
Jika x = lookupval Maka
Jika Tidak Hasil = "" Maka
hasil = hasil & " " & x.Offset(0, indexcol - 1)
Lain
hasil = x.Offset(0, indexcol - 1)
End If
X berikutnya
CusVlookup = hasil
End Function
Komentar ini diminimalkan oleh moderator di situs
Ini luar biasa tetapi saya mencari sesuatu yang lain, saya memiliki tabel dengan RollNo StudentName sub1, sub2, sub3 ... Hasil Total, Ketika saya memasukkan Rollnumber itu akan memberikan hasil seperti "SName Sub1 64, sub2 78,... Total 389, Hasil lulus", apakah mungkin
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk menghapus nilai duplikat dalam rangkaian?
Komentar ini diminimalkan oleh moderator di situs
Halo, Yakub,
Mungkin artikel berikut dapat membantu Anda untuk mengatasi masalah Anda.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Silakan dicoba, semoga bisa membantu Anda!
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk membuat daftar nilai duplikat hanya sekali, menggunakan kode dan rumus vba di atas? Saya tidak yakin di mana harus meletakkan pernyataan countif>1 di bilah rumus, atau di vba itu sendiri. Tolong bantu
Komentar ini diminimalkan oleh moderator di situs
anda dapat menambahkan dua kondisi tambahan untuk melewati sel kosong dan melewati duplikat: Untuk i = 1 Ke CriteriaRange.Count
Jika CriteriaRange.Cells(i).Nilai = Kondisi Maka
Jika ConcatenateRange.Cells(i).Nilai <> "" Kemudian 'LEWATI BANK
Jika InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Kemudian 'SKIP JIKA DITEMUKAN DUPLICATE
xResult = xResult & Pemisah & ConcatenateRange.Cells(i).Nilai
End If
End If
End If
Selanjutnya saya
Komentar ini diminimalkan oleh moderator di situs
Saya harus mengatakan, saya telah mencoba mendapatkan formula untuk menggabungkan beberapa nilai dan mengembalikannya ke satu sel selama 2 hari sekarang. "Cara" ini telah menyelamatkan saya!! Terima kasih banyak! Saya tidak akan pernah mendapatkannya tanpa Modul Anda!
Saya punya 2 pertanyaan meskipun. Saya memiliki pembatas sebagai koma alih-alih spasi dan karena itu dimulai dengan koma. Apakah ada cara untuk mencegah koma awal tetapi menyimpan sisanya?
Pertanyaan kedua saya adalah; Ketika saya menggunakan gagang isian, itu mengubah nilai rentang serta nilai sel yang ingin saya cari. Saya ingin itu terus mengubah nomor sel yang ingin saya cari tetapi tetap mempertahankan nilai rentang yang sama. Bagaimana saya bisa mewujudkannya?

Terima kasih banyak atas bantuan Anda!!
Komentar ini diminimalkan oleh moderator di situs
cusVlookup bekerja sangat baik untuk saya. Cara lain untuk memiliki pemisah yang berbeda adalah dengan membungkus dua fungsi pengganti. Yang pertama (dari dalam ke luar) menggantikan ruang pertama tanpa spasi, yang kedua menggantikan semua ruang lain dengan " / " di dalam saya. Bisa menggunakan "," jika Anda ingin koma.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Tabel1,2)," ","",1)," "," / ")

Juga, jika nilai pencarian Anda bukan kolom pertama, Anda dapat menggunakan 0 atau angka negatif untuk menuju ke kolom di sebelah kiri.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Tabel1,-1)," ","",1)," "," / ")
Komentar ini diminimalkan oleh moderator di situs
Hai, jeff,
Terima kasih untuk berbagi Anda, Anda harus menjadi orang yang berhati hangat.
Komentar ini diminimalkan oleh moderator di situs
Ini berfungsi dengan baik untuk saya - apakah ada cara untuk mengubahnya yang memeriksa apakah sel berisi daripada kecocokan yang lengkap? Pada dasarnya saya memiliki daftar tugas di mana:
Kolom A: Dependensi (mis. 10003 10004 10008)
Kolom B: Referensi Tugas (mis. 10001)
Kolom C: Tugas Dependen (kolom untuk hasil rumus) - di mana ia akan mencari referensi tugas untuk melihat baris mana yang memuatnya di Kolom A, lalu daftar Referensi Tugas tugas tersebut.

Misalnya:

Baris | Kolom A | Kolom B | kolom C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Komentar ini diminimalkan oleh moderator di situs
Anda ingin menggunakan fungsi Instr() yang akan memeriksa sesuatu dalam string teks dalam sel. Anda juga dapat menggunakan Left() dan Right() jika Anda mencari detail awal atau akhir.
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk mendapatkan "nama" unik untuk "class1"
Komentar ini diminimalkan oleh moderator di situs
Halo, sym-john,
Mungkin artikel di bawah ini dapat menyelesaikan masalah Anda, silakan lihat:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Belum ada komentar yang diposting di sini
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan

Ikuti kami

Hak Cipta © 2009 - www.extendoffice.com. | Seluruh hak cipta. Dipersembahkan oleh ExtendOffice. | Peta Situs
Microsoft dan logo Office adalah merek dagang atau merek dagang terdaftar dari Microsoft Corporation di Amerika Serikat dan / atau negara lain.
Dilindungi oleh Sectigo SSL