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

Bagaimana cara mengekstrak nilai unik dari beberapa kolom di Excel?

Misalkan Anda memiliki beberapa kolom dengan beberapa nilai, beberapa nilai diulangi dalam kolom yang sama atau kolom berbeda. Dan sekarang Anda ingin menemukan nilai yang ada di salah satu kolom hanya sekali. Apakah ada trik cepat bagi Anda untuk mengekstrak nilai unik dari beberapa kolom di Excel?


Ekstrak nilai unik dari beberapa kolom dengan rumus array

Berikut adalah rumus array yang juga dapat membantu Anda mengekstrak nilai-nilai unik dari beberapa kolom.

1. Dengan asumsi nilai-nilai Anda dalam jangkauan A2: C9, masukkan rumus berikut ke sel E2:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

Note: Dalam rumus di atas, A2: C9 menunjukkan rentang sel yang ingin Anda ekstrak nilai uniknya, E1: E1 adalah sel pertama dari kolom yang ingin Anda tempatkan hasilnya, $ 2: $ 9 berdiri dari baris berisi sel yang ingin Anda gunakan, dan $ A: $ C menunjukkan kolom berisi sel yang ingin Anda gunakan. Harap ubah ke milik Anda.

2. Lalu tekan Shift + Ctrl + Enter bersama-sama, lalu seret gagang isian untuk mengekstrak nilai unik hingga sel kosong muncul. Lihat tangkapan layar:


Ekstrak nilai unik dari beberapa kolom dengan Tabel Pivot

Jika Anda terbiasa dengan tabel pivot, Anda dapat dengan mudah mengekstrak nilai unik dari beberapa kolom dengan langkah-langkah berikut:

1. Pertama-tama silahkan masukkan satu kolom kosong baru di sebelah kiri data anda, pada contoh ini saya akan memasukkan kolom A disamping data asli.

2. Klik satu sel di data Anda, dan tekan Alt + D tombol, lalu tekan P kunci segera untuk membuka Panduan PivotTable dan PivotChart, pilih Beberapa rentang konsolidasi di wizard langkah1, lihat tangkapan layar:

3. Lalu klik Next tombol, periksa Buat satu bidang halaman untuk saya opsi di wizard step2, lihat tangkapan layar:

4. Lanjutkan mengklik Next tombol, klik untuk memilih rentang data yang termasuk kolom kiri baru sel, lalu klik Add tombol untuk menambahkan rentang data ke Semua rentang kotak daftar, lihat tangkapan layar:

5. Setelah memilih rentang data, lanjutkan klik Next, di panduan langkah 3, pilih tempat Anda ingin meletakkan laporan PivotTable sesuka Anda.

6. Terakhir, klik Finish untuk menyelesaikan wizard, dan tabel pivot telah dibuat di lembar kerja saat ini, kemudian hapus centang semua bidang dari Pilih bidang untuk ditambahkan ke laporan bagian, lihat tangkapan layar:

7. Kemudian periksa bidangnya Nilai atau seret Nilai ke Baris label, sekarang Anda akan mendapatkan nilai unik dari beberapa kolom sebagai berikut:


Ekstrak nilai unik dari beberapa kolom dengan kode VBA

Dengan kode VBA berikut, Anda juga dapat mengekstrak nilai unik dari beberapa kolom.

1. Tahan ALT + F11 kunci, dan itu membuka Jendela Microsoft Visual Basic for Applications.

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

VBA: Ekstrak nilai unik dari beberapa kolom

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3. Lalu tekan F5 untuk menjalankan kode ini, dan kotak prompt akan muncul untuk mengingatkan Anda memilih rentang data yang ingin Anda gunakan. Lihat tangkapan layar:

4. Dan kemudian klik OK, kotak prompt lain akan muncul untuk membiarkan Anda memilih tempat untuk meletakkan hasilnya, lihat tangkapan layar:

5. Klik OK untuk menutup dialog ini, dan semua nilai unik telah diekstraksi sekaligus.


Ekstrak nilai unik dari satu kolom dengan fitur luar biasa

Terkadang, Anda perlu mengekstrak nilai unik dari satu kolom, metode di atas tidak akan membantu Anda, di sini, saya dapat merekomendasikan alat yang berguna-Kutools untuk Excel, Dengan yang Ekstrak sel dengan nilai unik (termasuk duplikat pertama) utilitas, Anda dapat dengan cepat mengekstrak nilai-nilai unik.

Catatan:Untuk menerapkan ini Ekstrak sel dengan nilai unik (termasuk duplikat pertama), pertama, Anda harus mengunduh Kutools untuk Excel, lalu terapkan fitur dengan cepat dan mudah.

Setelah menginstal Kutools untuk Excel, lakukan seperti ini:

1. Klik sel tempat Anda ingin mengeluarkan hasilnya. (Note: Jangan mengklik sel di baris pertama.)

2. Lalu klik Kutools > Pembantu Formula > Pembantu Formula, lihat tangkapan layar:

3. di Rumus Pembantu kotak dialog, lakukan operasi berikut:

  • Pilih Teks pilihan dari Rumus Tipe daftar drop-down;
  • Lalu pilih Ekstrak sel dengan nilai unik (termasuk duplikat pertama) dari Pilih fromula kotak daftar;
  • Di kanan Masukan argumen bagian, pilih daftar sel yang ingin Anda ekstrak nilai uniknya.

4. Lalu klik Ok , dan seret gagang isian ke sel yang ingin Anda daftarkan semua nilai uniknya hingga sel kosong ditampilkan, lihat tangkapan layar:

Unduh Gratis Kutools untuk Excel Sekarang!


Artikel yang lebih relatif:

  • Hitung Jumlah Nilai Unik Dan Berbeda Dari Sebuah Daftar
  • Misalkan, Anda memiliki daftar panjang nilai dengan beberapa item duplikat, sekarang, Anda ingin menghitung jumlah nilai unik (nilai yang muncul dalam daftar hanya sekali) atau nilai yang berbeda (semua nilai berbeda dalam daftar, artinya unik nilai + nilai duplikat pertama) di kolom seperti gambar kiri yang ditampilkan. Artikel ini, saya akan berbicara tentang cara menangani pekerjaan ini di Excel.
  • Ekstrak Nilai Unik Berdasarkan Kriteria Di Excel
  • Misalkan, Anda memiliki rentang data berikut yang ingin Anda daftar hanya nama unik kolom B berdasarkan kriteria tertentu dari kolom A untuk mendapatkan hasil seperti gambar di bawah ini. Bagaimana Anda bisa menangani tugas ini di Excel dengan cepat dan mudah?
  • Hanya Izinkan Nilai Unik Di Excel
  • Jika Anda hanya ingin menyimpan nilai unik yang dimasukkan ke dalam kolom lembar kerja dan mencegah duplikat, artikel ini akan memperkenalkan beberapa trik cepat bagi Anda untuk menangani tugas ini.
  • Jumlahkan Nilai Unik Berdasarkan Kriteria Di Excel
  • Misalnya, saya memiliki berbagai data yang berisi kolom Nama dan Urutan, sekarang, untuk menjumlahkan hanya nilai unik di kolom Urutan berdasarkan kolom Nama seperti gambar berikut yang ditampilkan. Bagaimana mengatasi tugas ini dengan cepat dan mudah di Excel?

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 (31)
Rated 5 dari 5 · peringkat 1
Komentar ini diminimalkan oleh moderator di situs
Is this formula complete? =INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"",
Komentar ini diminimalkan oleh moderator di situs
Ini belum diperbaiki :sedih:
Komentar ini diminimalkan oleh moderator di situs
buang-buang waktu..... rumus TIDAK bekerja
Komentar ini diminimalkan oleh moderator di situs
Terima kasih!!! Saya telah menghabiskan berjam-jam mencoba melakukan ini dan mencari tahu apa yang terjadi pada Pivot Wizard (artikel lain).
Komentar ini diminimalkan oleh moderator di situs
Saya menggunakan kode VBA Anda, tetapi tidak ingin kotak itu muncul. Sebagai gantinya saya ingin mendefinisikan dengan tepat rentang sel apa yang digunakan setiap waktu dan kotak apa yang tepat untuk menempatkan output. Rentang input dan output akan berada di dua lembar yang berbeda. bagaimana cara memperbarui VBA untuk melakukannya? Terima kasih!!
Komentar ini diminimalkan oleh moderator di situs
Hai! Adakah yang tahu mengapa rumus ini tampaknya menyebabkan kesalahan setelah baris 87? Seperti, itu bekerja dengan sempurna dan kemudian pada titik tertentu itu hanya mengembalikan saya kesalahan untuk setiap baris .. yang merupakan yang terburuk! Karena saya sangat dekat dengan apa yang saya butuhkan di sini...
Komentar ini diminimalkan oleh moderator di situs
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"" It doesn't work
Komentar ini diminimalkan oleh moderator di situs
hai saya ingin mengekstrak sel unik dari kolom pertama ketika saya membandingkannya dengan kolom lain (saya memiliki tiga kolom yang tidak sama), bagaimana saya bisa melakukannya?
Komentar ini diminimalkan oleh moderator di situs
hai saya memiliki tiga kolom yang tidak sama dan ingin mengekstrak sel unik dari kolom pertama. bagaimana saya bisa melakukannya?? Terima kasih sebelumnya
Komentar ini diminimalkan oleh moderator di situs
saya cinta

Ekstrak Nilai Unik Dari Beberapa Kolom Dengan Tabel Pivot
Komentar ini diminimalkan oleh moderator di situs
Bisakah Anda mengirimkan rumus yang benar ... fungsi VBA berfungsi dengan baik.
Hanya untuk proyek saya, saya lebih suka menggunakan rumus yang benar.


Terima kasih
Komentar ini diminimalkan oleh moderator di situs
adakah yang tahu, untuk outputnya, bagaimana membuatnya menjadi beberapa baris tetapi tidak menjadi satu baris? (saat ini satu hasil baris dicapai oleh worksheetfunction.transpose, tetapi yang ingin saya capai (sebagai hasilnya) adalah ketika memilih untuk 3 kolom, hasil yang dikembalikan juga 3 kolom, bukan satu
Komentar ini diminimalkan oleh moderator di situs
Rumus array ini BENAR. Data di kolom A sampai C, rumus hasil pertama di sel D2... Yang ini beda dengan rumus array lain yang nanti di copy rumus ke bawah dan Ctrl+Shift+Enter all formula. Namun, rumus array ini harus dilakukan Ctrl+Shift+Enter di sel pertama dan salin ke bawah.
Komentar ini diminimalkan oleh moderator di situs
Muchas gracias por la makro!!! saya fue muy util
Komentar ini diminimalkan oleh moderator di situs
saya telah menyesuaikan ke lembar saya tetapi saya hanya mengembalikan nilai pertama dalam array yang ditentukan ... apa yang saya lewatkan?
Komentar ini diminimalkan oleh moderator di situs
Halo, Cody,
Rumus di atas berfungsi dengan baik di lembar kerja saya, dapatkah Anda memberikan tangkapan layar masalah data Anda di sini?
Terima kasih!
Komentar ini diminimalkan oleh moderator di situs
Mengenai versi formula, bisakah Anda menjelaskan lebih detail apa yang dilakukan bagian ini? *100+COLUMN($A:$C),7^8)),"R0C00") Secara khusus, apa itu? * 100, 7 ^ 8, dan "R0C000" sedang mengerjakan? Saya memahami segala sesuatu yang lain, tetapi saya tidak tahu untuk apa ini.
Komentar ini diminimalkan oleh moderator di situs
Sedikit terlambat untuk tanggapan saya di sini tapi ...
ROW($2:$9)*100 - ini mengalikan angka baris *100, jadi jika di baris 5, sekarang jumlahnya 500
COLUMN($A:$C) - ini akan ditambahkan ke nomor baris*100, jadi jika baris 5 col 2, maka jumlahnya adalah 502.
7^8)), - ini (saya pikir) memiliki nilai maksimal untuk pernyataan min dari sebelumnya.
"R0C00") - ini memformat teks berdasarkan nomor. Dalam contoh, kami memiliki 502 sehingga menghasilkan R5C02 (baris 5, kolom 02).

Jika Anda memiliki banyak kolom tetapi tidak banyak baris, maka Anda dapat mengubahnya menjadi ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk kodenya. Saya menggunakan kode VBA halaman ini. Apakah ada cara untuk menambahkan kode pengurutan setelah nilai unik diekstraksi sehingga mengurutkannya secara otomatis?
Komentar ini diminimalkan oleh moderator di situs
bisakah kita membuat fungsi uniqdata alih-alih makro?
Komentar ini diminimalkan oleh moderator di situs
Hai, lhan,Jika Anda menyukai Fungsi Buatan Pengguna untuk membuat rumus untuk memecahkan masalah ini, kode di bawah ini dapat membantu Anda:Setelah memasukkan kode, pilih daftar sel tempat Anda ingin meletakkan hasilnya. Kemudian ketikkan rumus ini:=Unik(A1:C4)  di bilah rumus.Tekan Ctrl + Shift + Enter kunci bersama 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
Komentar ini diminimalkan oleh moderator di situs
Czy untuk seni?
Komentar ini diminimalkan oleh moderator di situs
Rumus array di atas berfungsi dengan baik saat digunakan dengan data di lembar yang sama, namun ketika saya mencoba menggunakannya untuk mereferensikan data yang sama persis dari lembar lain, rumus tidak menghasilkan apa-apa. Saya tidak tahu mengapa. Apakah ada batasan dengan fungsi array yang mencegah Anda mereferensikan rentang di lembar yang berbeda?

Terima kasih atas setiap wawasan yang dapat Anda berikan.
Komentar ini diminimalkan oleh moderator di situs
Halo Erin,

Senang untuk membantu. Fungsi INDIRECT dalam rumus ini lebih rumit untuk digunakan saat mereferensikan data di lembar kerja lain. Tidak disarankan untuk menggunakan fitur ini saat mereferensikan rentang di lembar kerja yang berbeda.

Misalnya: Sekarang data ada di Sheet1, saya ingin mereferensikan konten sel C2 dari Sheet1 di Sheet2. Pertama, di dua sel mana pun di Sheet2, seperti D1 dan D2, masukkan masing-masing Sheet1 dan C2. Pada titik ini, masukkan rumus di sel kosong Sheet2:
=INDIRECT("'"&D1&"'!"&D2), maka konten sel C2 di Sheet1 dapat dikembalikan.

Seperti yang Anda lihat, itu membuat segalanya menjadi lebih kompleks. Semoga penjelasan saya bisa membantu. Semoga harimu menyenangkan.

Hormat kami,
Mandy
Komentar ini diminimalkan oleh moderator di situs
Halo Pak! VBA bekerja dengan sangat baik, terima kasih banyak untuk itu! Saya bertanya-tanya, Jika saya mengubah data asli, apakah mungkin untuk menyegarkan kolom dengan nilai unik secara otomatis?
Rated 5 dari 5
Komentar ini diminimalkan oleh moderator di situs
Halo Ioannis,

Senang untuk membantu. Setelah Anda mengubah data asli, VBA tidak dapat me-refresh hasil secara otomatis. Dan cara termudah yang dapat saya pikirkan adalah dengan menekan Ctrl + Alt + F9 untuk menyegarkan semua hasil di lembar kerja di semua buku kerja yang terbuka. Semoga harimu menyenangkan.

Hormat kami,
Mandy
Komentar ini diminimalkan oleh moderator di situs
Terima kasih untuk artikel yang bagus ini.

Untuk orang yang menggunakan array formular di Excel non-Inggris harus ada perhatian khusus dari string format teks: dalam contoh Anda: "R0C00".
Untuk bahasa Jerman ini akan diterjemahkan menjadi "Z0S00". Namun, "S" adalah karakter khusus yang mengacu pada detik untuk pemformatan waktu. Karakter ini perlu diloloskan dan oleh karena itu format string yang benar untuk Excel Jerman adalah "Z0\S00".

Saya harap ini membantu seseorang di masa depan :-)
Belum ada komentar yang diposting di sini
Muat Lebih
Tinggalkan komentar anda
Posting sebagai Tamu
×
Beri peringkat pos ini:
0   Karakter
Lokasi yang Disarankan