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

Bagaimana cara mengekstrak daftar nilai unik secara dinamis dari rentang kolom di Excel?

Untuk rentang kolom yang nilainya berubah secara teratur, dan Anda selalu perlu mendapatkan semua nilai unik dari rentang tersebut tidak peduli bagaimana itu berubah. Bagaimana cara membuat daftar dinamis dari nilai-nilai unik? Artikel ini akan menunjukkan cara menghadapinya.

Ekstrak daftar nilai unik secara dinamis dari rentang kolom dengan rumus
Ekstrak daftar nilai unik secara dinamis dari rentang kolom dengan kode VBA


Ekstrak daftar nilai unik secara dinamis dari rentang kolom dengan rumus

Seperti gambar di bawah yang ditunjukkan, Anda perlu mengekstrak daftar nilai unik secara dinamis dari rentang B2: B9. Silakan coba rumus array berikut.

1. Pilih sel kosong seperti D2, masukkan rumus di bawah ini ke dalamnya dan tekan Ctrl + perubahan + Enter kunci secara bersamaan. (B2: B9 adalah data kolom yang ingin Anda ekstrak nilai uniknya, D1 adalah sel di atas tempat rumus Anda berada)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. Tetap memilih sel D2, lalu seret Fill Handle ke bawah untuk mendapatkan semua nilai unik dari kisaran yang ditentukan.

Sekarang semua nilai unik dalam rentang kolom B2: B9 diekstraksi. Ketika nilai dalam rentang ini berubah, daftar nilai unik akan segera berubah secara dinamis.

Pilih dan sorot semua nilai unik dengan mudah dalam rentang di excel:

The Pilih Sel Duplikat & Unik kegunaan Kutools untuk Excel dapat membantu Anda dengan mudah memilih dan menyorot semua nilai unik (termasuk duplikat pertama) atau nilai unik yang muncul hanya sekali, serta nilai duplikat yang Anda butuhkan seperti gambar di bawah ini.
Unduh Kutools untuk Excel sekarang! (30- jalur bebas hari)


Ekstrak daftar nilai unik secara dinamis dari rentang kolom dengan kode VBA

Anda juga dapat mengekstrak daftar nilai unik secara dinamis dari rentang kolom dengan kode VBA berikut.

1. tekan lain + F11 tombol secara bersamaan untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.

2. Dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul. Kemudian salin dan tempel kode VBA di bawah ini ke file Modul jendela.

Kode VBA: Ekstrak daftar nilai unik dari suatu rentang

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

Note: Dalam kode, D2 adalah sel Anda akan menemukan daftar nilai unik. Anda dapat mengubahnya sesuai kebutuhan.

3. Kembali ke lembar kerja, klik Menyisipkan > bentuk > empat persegi panjang. Lihat tangkapan layar:

4. Gambar persegi panjang di lembar kerja Anda, lalu masukkan beberapa kata yang perlu Anda tampilkan di atasnya. Kemudian klik kanan dan pilih Tetapkan Makro dari menu klik kanan. Dalam Tetapkan Makro kotak dialog, pilih BuatDaftar Unik dalam Nama makro kotak, dan kemudian klik OK tombol. Lihat tangkapan layar:

5. Sekarang klik pada tombol persegi panjang, a Kutools untuk Excel kotak dialog muncul, pilih rentang berisi nilai unik yang perlu Anda ekstrak, lalu klik OK .

Mulai sekarang, Anda dapat mengulangi langkah 5 di atas untuk memperbarui daftar nilai unik secara otomatis.


Terkait artikel:


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 (35)
Belum ada peringkat. Jadilah yang pertama memberi peringkat!
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk tutorialnya. Dengan menggunakan metode rumus, bagaimana Anda akan mengubah rumus jika Anda ingin menambahkan kualifikasi kategori? Katakanlah di kolom C Anda membedakan apakah item itu buah atau sayuran. Bagaimana Anda mengubah kode untuk hanya menyortir buah-buahan unik dan mengecualikan sayuran? Saya mencoba mengganti COUNTIF dengan COUNTIFS, menggunakan kriteria countifs kedua (LIST RANGE,"CATEGORY") tetapi hasilnya kosong. Apakah saya perlu memperluas array saya dan memasukkan VLOOKUP?
Komentar ini diminimalkan oleh moderator di situs
Saya baik di excel tetapi saya benar-benar mencoba untuk membungkus kepala saya tentang bagaimana dan mengapa rumus di atas bekerja (berfungsi untuk apa yang saya gunakan tetapi saya harus mengerti mengapa). Saya terkadang bingung menggunakan array sehingga penjelasan apa pun dalam istilah idiot akan sangat membantu Salam
Komentar ini diminimalkan oleh moderator di situs
Formula ini sudah usang dan tidak berfungsi. Saya benar-benar baru saja mengatur lembar excel yang tepat ini untuk melihat apakah saya bisa membuat formula ini berfungsi dan ternyata tidak.
Komentar ini diminimalkan oleh moderator di situs
Hei pria,
Versi Office mana yang Anda gunakan?
Komentar ini diminimalkan oleh moderator di situs
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - menemukan ini berfungsi dari situs lain...

Gunakan Ctrl+Shift+Enter untuk mendapatkan fungsi array (kurung kurawal). Seret salin-tempel rumus hingga #NA ditampilkan. Kumpulan data saya ada di Kolom-Q, dibandingkan untuk melihat apakah ada dalam daftar unik di Kolom-V, yang terus membentang di sepanjang kolom yang sama ini.
Komentar ini diminimalkan oleh moderator di situs
Selamat siang.
Harap cantumkan semua nilai unik kolom Q dengan rumus abobv, lalu gunakan rumusnya =IF(D2=V1,"Cocok","Tidak cocok") untuk membandingkan apakah keunikan di kolom Q dibandingkan dengan kolom V di baris yang sama .
Komentar ini diminimalkan oleh moderator di situs
Halo, dan terima kasih atas bantuan Anda.

Saya benar-benar membutuhkan fungsi ini, tetapi daftar "nilai unik" saya perlu diperluas melintasi kolom, bukan baris, sehingga daftar yang diperluas ke bawah baris tidak akan berfungsi untuk saya.

Bagaimana saya bisa mengubah rumus ini untuk membuat daftar "nilai unik" meluas saat saya menyeretnya melintasi kolom?

Mengimbangi()?
Mengubah urutan()?
Indirect() dengan string referensi absolut yang digabungkan dengan referensi ke kolom, bukan baris?


Terima kasih lagi!
Komentar ini diminimalkan oleh moderator di situs
Ryan yang terhormat,
Rumus ini =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter can membantu Anda untuk memecahkan masalah.
Lihat tangkapan layar di bawah ini:
Komentar ini diminimalkan oleh moderator di situs
Juga, untuk alasan apa pun, rumus asli memberikan:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

mengembalikan peringatan "referensi melingkar" dan tidak akan menghitung..
Komentar ini diminimalkan oleh moderator di situs
Ryan yang terhormat,
Versi Office mana yang Anda gunakan? Rumusnya berfungsi dengan baik di Office 2016 dan 2013.
Komentar ini diminimalkan oleh moderator di situs
Saya pernah mengalami ini sebelumnya - perbaikan saya adalah saya memasukkan rumus ke dalam sel D1 (setara dengan lembar kerja yang saya gunakan). Sel mana pun yang sesuai dengan $D:$1 Anda harus memasukkannya ke dalam sel di bawah ini - D2. Maaf jika itu bukan alasan Anda mendapatkan kesalahan
Komentar ini diminimalkan oleh moderator di situs
Adakah kiat agar opsi VBA berfungsi dengan Excel 2016 untuk macOS? Saya telah mengikuti langkah-langkahnya; Namun, ketika saya menjalankan makro, tidak ada yang terjadi sama sekali. Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Daer Jones,
Silakan coba kode VBA di bawah ini dan beri tahu saya apakah itu berfungsi untuk Anda. Terima kasih!

Sub BuatDaftar Unik()
Redupkan xRng Sebagai Rentang
Redupkan xLastRow Selamanya
Redupkan xLastRow2 Selamanya
Redupkan Aku Sebagai Bilangan Bulat
' Pada Kesalahan Lanjutkan Selanjutnya
Set xRng = Application.InputBox("Silakan pilih rentang:", "Kutools for Excel", Selection.Address, , , , , 8)
Jika xRng Bukan Apa-apa Kemudian Keluar Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).Hapus Kolom Duplikat:=1, Header:=xlNo
xLastRow2 = Sel(Rows.Count, "B").End(xlUp).Baris
Untuk I = 1 Ke xLastRow2
Jika ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Nilai = "" Kemudian
ActiveSheet.Range("D2:D" & xLastRow2).Sel(I).Hapus
End If
Next
End Sub
Komentar ini diminimalkan oleh moderator di situs
Hai Kristal,
Saya mencoba menggunakan versi VB dari daftar nilai unik dan mengalami masalah.
Rentang yang saya inginkan untuk membuat kolom nilai unik adalah semua rumus yang merujuk ke tab yang berbeda.
Bagaimana cara mendapatkan nilai untuk ditransfer alih-alih formula?
Komentar ini diminimalkan oleh moderator di situs
Mike yang terhormat,
Harap ubah referensi rumus Anda menjadi absolut, lalu terapkan skrip VB.
Komentar ini diminimalkan oleh moderator di situs
Saya memiliki masalah yang sama, kecuali bahwa rumus saya merujuk ke nama kolom dan tidak dapat dikonversi menjadi absolut.
Bagaimana cara mengubah vba untuk menempelkan nilai dan bukan rumus?
Komentar ini diminimalkan oleh moderator di situs
Bagaimana Anda menambahkan beberapa kriteria, seperti jika Anda hanya ingin menambahkan ke daftar dinamis jika tanggalnya hanya 9/12?

Saya mencoba "&" dalam rumus MATCH, tetapi tidak berhasil.

Misalnya, berdasarkan contoh Anda:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Ini melempar kesalahan atau membuat duplikat.

Atau, saya telah membaca bahwa "+" mungkin berfungsi, meskipun saya tidak dapat membuatnya berfungsi. Atau menggunakan KECIL.

Ide ide?
Komentar ini diminimalkan oleh moderator di situs
Zac yang terhormat,
Maaf saya tidak dapat membantu dengan ini, Anda dapat memposting pertanyaan Anda di forum kami: https://www.extendoffice.com/forum.html untuk mendapatkan lebih banyak dukungan Excel dari profesional kami.
Komentar ini diminimalkan oleh moderator di situs
Bagaimana Anda menambahkan variabel kedua? Misalnya, saya ingin semua elemen unik dalam satu kolom yang juga berbagi nilai yang sama di kolom lain. Dalam contoh Anda, bayangkan kolom ke-3 berjudul "Departemen" yang akan memiliki nilai seperti produk, daging, dll. Saya menyadari bahwa itu semua adalah Produce, tetapi semoga Anda mengerti maksud saya. Apakah Anda akan mengubah rumus CountIF menjadi COUNTIFS atau Anda memodifikasinya dengan cara lain?
Komentar ini diminimalkan oleh moderator di situs
Hai Matt
Silakan coba rumus ini =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Ya","").
Misalkan dua daftar yang dibandingkan adalah Kolom A dan Kolom C, Jika nilai unik hanya tinggal di Kolom A tetapi tidak di Kolom C, maka akan ditampilkan Ya di kolom B; sedangkan jika tidak mengembalikan apa pun di Kolom B, itu berarti nilai yang sesuai tetap berada di Kolom A dan Kolom C.
Komentar ini diminimalkan oleh moderator di situs
Terima kasih atas jawabannya .. tapi panas untuk mengeluarkan nilai unik itu jika ditampilkan YA .. bisakah Anda memberi tahu saya rumus untuk menarik nilai unik di kolom yang berbeda.
Komentar ini diminimalkan oleh moderator di situs
Jika saya melakukan ini untuk lembar excel seribu baris pada versi terbaru Excel di Mac, itu tidak akan pernah kembali. Baris pertama berfungsi, tetapi ketika saya menduplikasi, excel masuk ke mode komputasi yang belum mengembalikan nilai selama lebih dari dua jam sekarang.

Adakah pemikiran tentang cara melakukan ini untuk daftar besar (hingga 2k baris) yang akan mengembalikan 50 atau 60 nilai unik?

Saya mengolok-olok ini di aplikasi "Nomor", dan itu berfungsi dengan baik di sana, hanya membutuhkan beberapa menit untuk menghitung. Hanya butuh waktu lama di Excel sehingga saya bertanya-tanya apakah itu akan pernah selesai. Saya berencana untuk membiarkannya "berjalan" semalaman untuk melihat apa yang akan terjadi.
Komentar ini diminimalkan oleh moderator di situs
Periksa opsi Menghitung Anda. Itu perlu diatur ke otomatis. File > Opsi > Rumus > Opsi penghitungan > Penghitungan Buku Kerja (Pemilihan otomatis)
Komentar ini diminimalkan oleh moderator di situs
Saya mencoba menyeret rumus ke bawah melewati data aktual saya sehingga saya dapat memasukkan kumpulan data berukuran berbeda dan tidak perlu menyesuaikan apa pun. Namun, baris terakhir setelah data aktual saya berakhir selalu mengembalikan "0". Saya menggunakan nilai unik untuk sesuatu yang lain di kolom yang berdekatan, dan 0 menyebabkan nilai terakhir diulang (Ketika saya menghapus 0 nilainya tidak lagi diulang). Adakah ide bagaimana cara memperbaikinya? Saya juga menggunakan Office 365 Bisnis
Komentar ini diminimalkan oleh moderator di situs
Hai, terima kasih atas bantuan Anda.
Sekarang, bagaimana saya bisa membuat nilai saya juga diurutkan menurut abjad? (Saya tidak ingin menggunakan filter di tabel master saya)
Haruskah saya menggunakan COUNTIFS daripada COUNTIF?
Tolong bantu
Komentar ini diminimalkan oleh moderator di situs
Hai Alexis,
Maaf tidak dapat mengurutkan nilai yang diekstraksi secara alfabet pada saat yang sama dengan rumus. Terima kasih atas komentarmu.
Komentar ini diminimalkan oleh moderator di situs
Saya menggunakan rumus =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") yang bagus untuk satu kolom tetapi data saya tersebar di berbagai kolom dan baris. Bisakah saya mengedit rumus untuk memasukkan seluruh area? Data saya hidup dari AC4 hingga AR60...
Komentar ini diminimalkan oleh moderator di situs
Saya mencoba Kode VBA dan rumusnya. Kode VBA berfungsi dengan sangat baik tetapi saya tidak dapat menyimpan file dengan makro. Tapi masalahnya adalah saya tidak bisa membuat formulanya bekerja. Apakah ada yang punya ide? Terima kasih
Komentar ini diminimalkan oleh moderator di situs
Hai Charlotte,
Terima kasih atas komentarmu. Anda bisa menyimpan file dengan makro untuk digunakan di masa mendatang dengan menyimpan buku kerja sebagai Buku Kerja Excel Macro-Enabled.
Untuk soal rumus, bisakah Anda memberikan tangkapan layar data Anda? Terima kasih atas komentarmu.
Komentar ini diminimalkan oleh moderator di situs
terima kasih banyak
Komentar ini diminimalkan oleh moderator di situs
bagaimana cara membuat kode vba berfungsi untuk rentang di mana rumus lain digunakan?pada kolom BI ada rumus, mengacu pada kolom D dan E.
Jika saya menggunakan menerapkan kode ke kolom L (katakanlah), (jelas, memodifikasi sel dalam kode dengan benar) makro mengembalikan rumus yang diterapkan ke kolom M dan N... Ini berfungsi, lalu, tetapi tidak seperti yang saya inginkan! Bagaimana cara menyimpan nilai di kolom B? terima kasih
Komentar ini diminimalkan oleh moderator di situs
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote kumpulan data. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje bertemu unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 kriteria filteren. Ook daar zijn wel weer oplossingen voor, maar dat lebih kompleks.
Komentar ini diminimalkan oleh moderator di situs
Saya ingin dapat melakukan hal yang sama persis, kecuali menggunakan dua rentang kolom terpisah (B2:B9) serta (D2:D9) apakah ini mungkin?
Komentar ini diminimalkan oleh moderator di situs
Hai Antonius,
Anda dapat menempatkan hasil di kolom yang sama dengan data asli. Seperti kolom B dalam hal ini.
Tetapi Anda perlu mereferensikan sel teratas dari sel hasil dalam rumus sebagai berikut.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
Komentar ini diminimalkan oleh moderator di situs
dengan prosedur penyaring ini sangat cepat

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se define un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplikat y la d57 la dejo en blanco
3. satu kali dikeluarkan semuestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239"). Tindakan Filter Lanjutan:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unik:=Benar
Belum ada komentar yang diposting di sini

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