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

Bagaimana cara vlookup dan mengembalikan banyak nilai tanpa duplikat di Excel? 

Terkadang, Anda mungkin ingin vlookup dan mengembalikan beberapa nilai yang cocok ke dalam satu sel sekaligus. Tetapi, jika ada beberapa nilai berulang yang diisi ke dalam sel yang dikembalikan, bagaimana Anda bisa mengabaikan duplikat dan hanya menyimpan nilai unik saat mengembalikan semua nilai yang cocok seperti gambar layar berikut yang ditampilkan di Excel?

doc mengembalikan beberapa nilai unik 1

Vlookup dan mengembalikan beberapa nilai yang cocok tanpa duplikat dengan menggunakan Fungsi Buatan Pengguna


Vlookup dan mengembalikan beberapa nilai yang cocok tanpa duplikat dengan menggunakan Fungsi Buatan Pengguna

Kode VBA berikut dapat membantu Anda mengembalikan beberapa nilai yang cocok tanpa duplikat, lakukan seperti ini:

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 mengembalikan beberapa nilai unik yang cocok:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. Setelah memasukkan kode, selanjutnya klik Tools > Referensi di tempat terbuka Microsoft Visual Basic untuk Aplikasi jendela, dan kemudian, di muncul keluar Referensi - VBAProject kotak dialog, periksa Runtime Microsoft Scripting pilihan dalam Referensi yang Tersedia kotak daftar, lihat tangkapan layar:

doc mengembalikan beberapa nilai unik 2

4. Lalu klik OK untuk menutup kotak dialog, simpan dan tutup jendela kode, kembali ke lembar kerja, dan masukkan rumus ini: =MultipleLookupNoRept(E2,A2:C17,3) ke dalam sel kosong tempat Anda ingin menampilkan hasilnya, tekan Enter kunci untuk mendapatkan hasil yang benar sesuai kebutuhan Anda. Lihat tangkapan layar:

doc mengembalikan beberapa nilai unik 3

Note: Dalam rumus di atas, E2 adalah kriteria yang ingin Anda vlookup, A2: C17 adalah rentang data yang ingin Anda gunakan, angkanya 3 adalah nomor kolom yang berisi nilai yang dikembalikan.


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 (13)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
bagaimana jika saya ingin membuat daftar dalam tabel dari ini alih-alih semua hasil dalam satu sel?
Komentar ini diminimalkan oleh moderator di situs
Halo, Tom,
Jika Anda ingin mengekstrak nilai unik dalam daftar sel alih-alih satu sel, rumus berikut dapat membantu Anda:

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Silakan coba.
Komentar ini diminimalkan oleh moderator di situs
Hai Skyyang,

Terima kasih banyak untuk formula ini.
Ini bekerja untuk saya. Namun, butuh waktu lama untuk memproses dari kumpulan data yang besar.
Bisakah kita memodifikasi formula ini untuk bekerja sedikit lebih cepat?
Terima kasih lagi
rasike
Komentar ini diminimalkan oleh moderator di situs
Hi skyyang bagaimana jika Anda ingin hasilnya sebagai kolom?
Komentar ini diminimalkan oleh moderator di situs
Apakah ada cara untuk menambahkan spasi di antara beberapa nilai yang diambil dalam hasil tanpa memasukkan koma di akhir daftar? Misalnya hasil Anda di atas akan ditampilkan sebagai: "Emily, James, Daisy, Gary" alih-alih seperti ini: "Emily,James,Daisy,Gary"

Saya mencoba mengedit bagian kode VBA ini: xStr = xStr & xDic.Keys(I) & "," menjadi ini: xStr = xStr & xDic.Keys(I) & ", "

Itu memang menambahkan spasi di antara nilai-nilai, tetapi juga menambahkan koma setelah nilai terakhir. "Emily, James, Daisy, Gary,"

Apakah ada cara untuk membuatnya berfungsi dengan spasi tetapi tanpa koma tambahan setelah nilai terakhir?
Komentar ini diminimalkan oleh moderator di situs
Halo, Demeter,
Gunakan spasi untuk memisahkan nilai, Anda hanya perlu mengubah kode vba:
dari xStr = xStr & xDic.Keys(i) & "," menjadi ini: xStr = xStr & xDic.Keys(i) & " "

Silakan coba.
Komentar ini diminimalkan oleh moderator di situs
xStr = xStr & xDic.Keys(I) & "," menjadi ini: xStr = xStr & xDic.Keys(I) & ", "

Apakah ada cara untuk mengganti "," dengan ALT+ENTER di dalam sel, sehingga hasilnya akan berada di sel yang sama tetapi pada baris yang berbeda? Apakah saya perlu memperkenalkan modul VBA tambahan untuk itu dan menggabungkannya?

Juga, kode ini cukup lambat saat mengulang tabel besar. Adakah yang tahu ada solusi yang lebih cepat?
Komentar ini diminimalkan oleh moderator di situs
Hai, Imre,
Untuk memisahkan nilai hasil dengan tombol Alt + Enter, harap terapkan Fungsi Buatan Pengguna berikut:

Fungsi MultipleLookupNoRept(Lookupvalue Sebagai String, LookupRange Sebagai Range, ColumnNumber Sebagai Integer)
Dim xDic Sebagai Kamus Baru
Redupkan xRows Selamanya
Redupkan xStr Sebagai String
Redupkan Aku Selamanya
On Error Resume Next
xRows = LookupRange.Rows.Count
Untuk i = 1 Ke xRows
Jika LookupRange.Columns(1).Cells(i).Value = Lookupvalue Maka
xDic.Tambahkan LookupRange.Columns(ColumnNumber).Sel(i).Nilai, ""
End If
Next
xStr = ""
MultipleLookupNoRept = xStr
Jika xDic.Count > 0 Maka
Untuk i = 0 Ke xDic.Count - 1
xStr = xStr & xDic.Keys(i) & Chr(10) + Chr(13)
Next
MultipleLookupNoRept = Kiri(xStr, Len(xStr) - 1)
End If
Debug.Print xStr
End Function

Dan kemudian lakukan dengan langkah-langkah di atas dalam artikel ini, akhirnya, setelah memasukkan rumus, Anda harus mengklik Bungkus Teks di bawah tab Beranda.
Komentar ini diminimalkan oleh moderator di situs
Hi

Saya ingin membuat daftar dalam tabel dari ini alih-alih semua hasil dalam satu sel. Jadi saya telah menggunakan rumus serupa di bawah ini (apa yang Anda sarankan)

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Namun, ini membutuhkan waktu lama untuk diproses dari kumpulan data yang besar.
Apakah ada metode alternatif untuk memproses ini lebih cepat?
Terima kasih lagi
rasike
Komentar ini diminimalkan oleh moderator di situs
Hai,
sementara waktu multivlooks nilai lot lembar kerja saya hang. apakah ada cara lain untuk multivlookup tanpa pengulangan????

dan juga saya gunakan di desktop baru juga hanya hang ...

nilai data saya sekitar 10,000 baris
Komentar ini diminimalkan oleh moderator di situs
Halo , saya melakukan seperti yang Anda katakan dan itu bagus tetapi masih belum menyelesaikan salah satu masalah saya , apa yang terjadi ketika Anda memiliki nilai unik di setiap bulan ? =MultipleLookupNoRept(E2,A2:C17,3) , saya mencoba E2&1 untuk Januari tetapi tidak berhasil
Komentar ini diminimalkan oleh moderator di situs
Hai, Jam,
Bisakah Anda memberikan masalah Anda sebagai tangkapan layar di sini, sehingga saya dapat memahami kebutuhan Anda?
Komentar ini diminimalkan oleh moderator di situs
Ini bagus! Bagaimana saya mengadaptasi ini untuk tidak menambahkan nilai nol ke kamus? Saya sudah mencoba menambahkan huruf tebal di bawah, tetapi string terakhir masih kembali dengan , "", instance.


xRows = LookupRange.Rows.Count
Untuk i = 1 Ke xRows
Jika LookupRange.Columns(1).Cells(i).Value = Lookupvalue Dan Bukan IsEmpty(LookupRange.Columns(1).Cells(i).Value) Kemudian
xDic.Tambahkan LookupRange.Columns(ColumnNumber).Sel(i).Nilai, ""
End If
Next

Terima kasih,
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