Loncat ke daftar isi utama

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

Bagian ini akan membahas dua rumus: satu menggunakan rumus array yang cocok untuk semua versi Excel, dan satu lagi menggunakan rumus array dinamis khusus untuk Excel 365.

Ekstrak nilai unik dari beberapa kolom dengan rumus Array untuk semua versi Excel

Untuk pengguna dengan versi Excel apa pun, rumus array bisa menjadi alat yang ampuh untuk mengekstraksi nilai unik di beberapa kolom. Inilah cara Anda melakukannya:

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 + Masuk bersama-sama, lalu seret gagang isian untuk mengekstrak nilai unik hingga sel kosong muncul. Lihat tangkapan layar:

Penjelasan dari rumus ini:
  1. $ A $ 2: $ C $ 9: Ini menentukan rentang data yang akan diperiksa, yaitu sel dari A2 hingga C9.
  2. IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
    • $A$2:$C$9<>"" memeriksa apakah sel dalam rentang tersebut tidak kosong.
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 menentukan apakah nilai sel ini belum tercantum dalam rentang sel dari E1 hingga E1.
    • Jika kedua kondisi terpenuhi (yaitu, nilainya tidak kosong dan belum tercantum di kolom E), fungsi IF menghitung angka unik berdasarkan baris dan kolomnya (ROW($2:$9)*100+COLUMN($A: $C)).
    • Jika kondisi tidak terpenuhi, fungsi akan mengembalikan sejumlah besar (7^8), yang berfungsi sebagai pengganti.
  3. menit(...): Menemukan angka terkecil yang dikembalikan oleh fungsi IF di atas, sesuai dengan lokasi nilai unik berikutnya.
  4. TEKS(...,"R0C00"): Mengonversi angka minimum ini menjadi alamat gaya R1C1. Kode format R0C00 menunjukkan konversi angka ke dalam format referensi sel Excel.
  5. TIDAK LANGSUNG(...): Menggunakan fungsi INDIRECT untuk mengonversi alamat gaya R1C1 yang dihasilkan pada langkah sebelumnya kembali ke referensi sel gaya A1 normal. Fungsi INDIRECT memungkinkan referensi sel berdasarkan konten string teks.
  6. &"": Menambahkan &"" di akhir rumus memastikan hasil akhir diperlakukan sebagai teks, sehingga angka genap akan ditampilkan sebagai teks.
 
Ekstrak nilai unik dari beberapa kolom dengan rumus untuk Excel 365

Excel 365 mendukung array dinamis, sehingga lebih mudah mengekstrak nilai unik dari beberapa kolom:

Silakan masukkan atau salin rumus berikut ke sel kosong tempat Anda ingin meletakkan hasilnya, lalu klik Enter kunci untuk mendapatkan semua nilai unik sekaligus. Lihat tangkapan layar:

=UNIQUE(TOCOL(A2:C9,1))

Ekstrak nilai unik dari beberapa kolom dengan Kutools AI Aide

Melepaskan kekuasaan Kutools AI Ajudan untuk mengekstrak nilai unik dengan lancar dari beberapa kolom di Excel. Hanya dengan beberapa klik, alat cerdas ini menyaring data Anda, mengidentifikasi dan membuat daftar entri unik di rentang mana pun yang dipilih. Lupakan kerumitan rumus rumit atau kode vba; Rangkullah efisiensi Kutools AI Ajudan dan ubah alur kerja Excel Anda menjadi pengalaman yang lebih produktif dan bebas kesalahan.

Note: Untuk menggunakan ini Kutools AI Ajudan of Kutools untuk Excel, Mohon unduh dan instal Kutools untuk Excel pertama.

Setelah menginstal Kutools for Excel, silakan klik Kutools AI > Ajudan AI untuk membuka Kutools AI Ajudan panel:

  1. Ketik kebutuhan Anda ke dalam kotak obrolan, dan klik Kirim tombol atau tekan Enter kunci untuk mengirimkan pertanyaan;
    "Ekstrak nilai unik dari rentang A2:C9, abaikan sel kosong, dan tempatkan hasilnya mulai dari E2:"
  2. Setelah menganalisis, klik Eksekusi tombol untuk menjalankan. Kutools AI Aide akan memproses permintaan Anda menggunakan AI dan mengembalikan hasilnya di sel yang ditentukan langsung di Excel.

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 Selanjutnya tombol, periksa Buat satu bidang halaman untuk saya opsi di wizard step2, lihat tangkapan layar:

4. Lanjutkan mengklik Selanjutnya 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 Selanjutnya, 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.

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 AI Ajudan: Merevolusi analisis data berdasarkan: Eksekusi Cerdas   |  Hasilkan Kode  |  Buat Rumus Khusus  |  Analisis Data dan Hasilkan Grafik  |  Aktifkan Fungsi Kutools...
Fitur Populer: Temukan, Sorot, atau Identifikasi Duplikat   |  Hapus Baris Kosong   |  Gabungkan Kolom atau Sel tanpa Kehilangan Data   |   Putaran tanpa Formula ...
Pencarian Super: VLookup Beberapa Kriteria  |   VLookup Nilai Berganda  |   VLookup di Beberapa Lembar   |   Pencarian Fuzzy ....
Daftar Drop-down Lanjutan: Buat Daftar Drop Down dengan Cepat   |  Daftar Drop Down yang Bergantung   |  Multi-pilih Drop Down List ....
Manajer Kolom: Tambahkan Jumlah Kolom Tertentu  |  Pindahkan Kolom  |  Alihkan Status Visibilitas Kolom Tersembunyi  |  Bandingkan Rentang & Kolom ...
Fitur Unggulan: Fokus Kisi   |  Tampilan Desain   |   Bar Formula Besar   |  Manajer Buku Kerja & Lembar   |  Perpustakaan Sumberdaya (Teks otomatis)   |  Pemetik tanggal   |  Gabungkan Lembar Kerja   |  Enkripsi/Dekripsi Sel   |  Kirim Email berdasarkan Daftar   |  Filter Super   |   Filter Khusus (filter tebal/miring/coret...) ...
15 Perangkat Teratas:  12 Teks Tools (Tambahkan Teks, Hapus Karakter, ...)   |   50 + Grafik jenis (Gantt Chart, ...)   |   40+ Praktis Rumus (Hitung usia berdasarkan ulang tahun, ...)   |   19 Insersi Tools (Masukkan Kode QR, Sisipkan Gambar dari Jalur, ...)   |   12 Konversi Tools (Angka ke Kata, Konversi Mata Uang, ...)   |   7 Gabungkan & Pisahkan Tools (Lanjutan Gabungkan Baris, Pisahkan Sel, ...)   |   ... dan banyak lagi

Tingkatkan Keterampilan Excel Anda dengan Kutools for Excel, dan Rasakan Efisiensi yang Belum Pernah Ada Sebelumnya. Kutools for Excel Menawarkan Lebih dari 300 Fitur Lanjutan untuk Meningkatkan Produktivitas dan Menghemat Waktu.  Klik Di Sini untuk Mendapatkan Fitur yang Paling Anda Butuhkan...

Deskripsi Produk

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!
Comments (31)
Rated 5 out of 5 · 1 ratings
John
John
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
Reply
0
0
skyyang
skyyang     John
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
Reply
Report
0
0
Michael H.
Michael H.
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
Reply
0
0
skyyang
skyyang     Michael H.
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
Reply
Report
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
Reply
0
0
Ioannis Psychogios
Ioannis Psychogios
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Reply
0
0
Rated 5 out of 5
MandyZhou
MandyZhou     Ioannis Psychogios
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
Reply
Report
0
0
Erin Thomas
Erin Thomas
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
Reply
0
0
MandyZhou
MandyZhou     Erin Thomas
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
Reply
Report
0
0
Excel to żart
Excel to żart
This comment was minimized by the moderator on the site
Czy to żart?
Reply
0
0
İlhan
İlhan
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
Reply
0
0
skyyang
skyyang     İlhan
This comment was minimized by the moderator on the site
Hi, İlhan，If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


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
View Attachments (1 / 5)
Reply
Report
0
0
xxmrxsnowmanxx
xxmrxsnowmanxx
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
Reply
Report
0
0
Michael
Michael
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
Reply
0
0
Stealth
Stealth     Michael
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
Reply
0
0
Cody
Cody
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
Reply
0
0
skyyang
skyyang     Cody
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations