Saat menemukan sel duplikat hanya dalam satu kolom atau daftar, kita dapat menerapkan COUNTIF berfungsi untuk mengetahui dan menghitung data duplikat dengan mudah.
1. Pilih sel kosong di samping kolom yang akan Anda temukan duplikatnya.

2. Ketikkan rumusnya = COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, dan tekan tombol Enter kunci.

3. Seret gagang IsiOtomatis sel rumus untuk menyalin rumus ini ke sel lain.

Catatan: Dalam rumusnya = COUNTIF ($ C $ 3: $ C $ 12, C3)> 1,
(1) $ C $ 3: $ C $ 12 adalah kolom atau daftar tempat Anda akan menemukan nilai duplikat di dalamnya. Karena kolom bersifat statis saat kita menyalin rumus ke sel lain, umumnya adalah referensi absolut dengan "$".
(2) C3 adalah sel pertama dari kolom yang ditentukan. Ini adalah referensi relatif karena kita membutuhkannya berubah secara otomatis saat kita menyalin rumus ke sel lain.
(3) Rumus ini akan kembali BENAR or SALAH. BENAR berarti nilai yang sesuai adalah duplikat, sedangkan SALAH menunjukkan nilai yang sesuai unik di kolom.
(4) Terkadang, BENAR atau SALAH mungkin tidak dipahami secara intuitif. Kita bisa menggabungkan rumus asli dan fungsi IF untuk dikembalikan Duplikat langsung.
= IF (COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, "Duplikat", "")

Dalam beberapa kasus, Anda perlu membandingkan dua kolom dan mencari tahu nilai duplikatnya. Misalnya, Anda memiliki dua daftar nama, dan ingin mengetahui nama duplikat di daftar kedua dibandingkan dengan yang pertama. Anda dapat menerapkan fungsi VLOOKUP dan IFERROR untuk menyelesaikannya dengan mudah.

1. Pilih sel kosong di samping daftar nama kedua.

2. Masukkan rumusnya = IFERROR (VLOOKUP (D3, $ B $ 3: $ B $ 18,1,0), ""), dan tekan tombol Enter kunci.

3. Seret gagang IsiOtomatis sel rumus untuk menyalin rumus ke sel lain yang Anda butuhkan.

Catatan: Dalam rumus di atas,
(1) D3 adalah sel pertama dalam daftar nama kedua. Referensi perlu berubah secara otomatis saat kita menyalin rumus ke sel lain, akibatnya, itu relatif.
(2) $ B $ 3: $ B $ 18 adalah daftar nama depan. Ini adalah referensi absolut karena rentang harus tetap statis saat kita menyalin rumus ke sel lain.
(3) Ketika sebuah nama diduplikasi dengan nama di daftar pertama, rumus akan mengembalikan nama; jika tidak maka akan kembali kosong.
(4) Anda juga bisa menerapkan rumusnya = IF (COUNTIF ($ B $ 3: $ B $ 18, D3)> 0, "Duplikat", "") untuk menemukan nama duplikat dalam daftar kedua dibandingkan dengan daftar pertama. Rumus ini akan kembali "Duplikat" jika nama yang sesuai adalah duplikat.

(5) Jika Anda perlu menemukan duplikat dalam dua kolom di dua lembar, Anda hanya perlu menambahkan nama lembar sebelum referensi kolom yang dibandingkan. Dalam contoh kita, cukup ubah $ B $ 3: $ B $ 18 menjadi Sheet1! $ B $ 3: $ B $ 18 dalam rumus.

Rumus yang diperkenalkan di atas tidak cocok dengan huruf besar / kecil saat menemukan duplikat, dikatakan mereka menganggap "apel" adalah duplikat dengan "APPLE". Di sini, Anda dapat menerapkan rumus array untuk menemukan nilai duplikat dalam satu kolom dengan kasus yang cocok.

1. Pilih sel kosong di samping kolom yang akan Anda temukan duplikatnya.

2. Ketikkan rumus array = IF (SUM ((- EXACT ($ C $ 3: $ C $ 12, C3))) <= 1, "", "Duplicate"), dan tekan Ctrl + perubahan + Enter kunci.

3. Seret gagang IsiOtomatis sel rumus untuk menyalin rumus array ke sel lain.

Catatan: Dalam rumus array di atas,
(1) $ C $ 3: $ C $ 12 adalah kolom tempat Anda perlu menemukan nilai duplikat. Referensi bersifat absolut karena referensi bersifat statis saat menyalin rumus array ke sel lain.
(2) C4 adalah sel pertama di kolom tersebut. Referensi bersifat relatif, karena referensi perlu berubah secara otomatis saat menyalin rumus array ke sel lain.
(3) Jika sel yang bersangkutan adalah duplikat, rumus array akan mengembalikan "Duplikat", jika tidak maka akan kosong.

Anda dapat menerapkan Format Bersyarat fitur untuk menyorot sel duplikat dengan cepat dalam kolom atau rentang.

1. Pilih kolom tempat Anda akan menyorot sel duplikat.

2. Klik Beranda > Format Bersyarat > Sorot Aturan Sel > Nilai duplikat. Lihat tangkapan layar di bawah ini:

3. Dalam dialog Duplicate Values ​​yang muncul, pilih Duplikat dari daftar tarik-turun pertama, pilih skenario sorotan dari daftar turun bawah kedua, dan klik OK .

Catatan: Jika skenario sorotan prasetel tidak dapat memenuhi kebutuhan Anda, Anda dapat memilih Format Kustom dari daftar turun bawah kedua, lalu pilih warna sorotan, font, dan batas sel yang Anda perlukan dalam dialog Format Sel yang muncul.

Kemudian Anda akan melihat semua sel duplikat disorot di kolom yang dipilih seperti gambar di bawah ini.

Catatan:  
(1) Setelah menyorot sel duplikat, kita dapat dengan mudah memfilter duplikat ini. (Klik untuk melihat caranya)
(2) Setelah menyorot sel duplikat, kami juga dapat menghapus duplikat secara massal dengan mudah. (Klik untuk melihat caranya)

Beberapa pengguna lebih suka menyorot baris berdasarkan sel duplikat di kolom tertentu. Dalam situasi ini, kita dapat membuat aturan pemformatan bersyarat kustom untuk menyelesaikannya.

1. Pilih rentang (tidak termasuk baris header) yang akan Anda sorot baris berdasarkan sel duplikat.

2. Klik Beranda > Format Bersyarat > Aturan baru.

3. Dalam dialog Aturan Pemformatan Baru,
(1) Klik untuk memilih Gunakan rumus untuk menentukan sel mana yang akan diformat pilihan;
(2) Ketikkan rumusnya = COUNTIF ($ C $ 3: $ C $ 12, $ C3)> 1 dalam Memformat nilai yang rumus ini benar kotak;
Tips: Dalam rumusnya, $ C $ 3: $ C $ 12 adalah kolom yang berisi sel duplikat, dan $ C3 adalah sel pertama di kolom tersebut.
(3) Klik dibentuk .

4. Dalam dialog Format Sel, tentukan warna isian, font, batas sel yang Anda butuhkan, dan klik OK tombol berturut-turut untuk menyimpan pengaturan.

Sejauh ini, dalam rentang yang dipilih, baris disorot berdasarkan sel duplikat di kolom yang ditentukan. Lihat tangkapan layar:

Untuk menyorot baris duplikat dalam rentang tertentu, Anda dapat menerapkan Format Bersyarat fitur untuk mencapainya juga.

1. Pilih rentang kecuali baris tajuk.

2. Klik Beranda > Format Bersyarat > Aturan baru.

3. Dalam dialog Aturan Pemformatan Baru:
(1) Klik untuk memilih Gunakan rumus untuk menentukan sel mana yang akan diformat pilihan;
(2) Dalam Memformat nilai yang rumus ini benar kotak, ketikkan rumusnya =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1;
(3) Klik dibentuk .

Catatan: Dalam rumus =COUNTIFS($B$3:$B$12,$B3,$C$3:$C$12,$C3,$D$3:$D$12,$D3)>1:
(1) $ B $ 3: $ B $ 12 adalah kolom pertama dalam rentang, dan $ B3 adalah sel pertama di kolom ini;
(2) $ C $ 3: $ C $ 12 adalah kolom kedua dalam rentang, dan $ C3 adalah sel pertama di kolom;
(3) $ D $ 3: $ D $ 12 adalah kolom ketiga dalam rentang, dan $ D3 adalah sel pertama di kolom;
(4) Jika ada lebih banyak kolom dalam rentang Anda, Anda dapat menambahkan referensi kolom dan sel pertamanya secara berurutan dalam rumus.

4. Dalam dialog Format Sel, tentukan warna sorotan, font, batas sel, dll. Sesuai kebutuhan Anda, lalu klik OK tombol berturut-turut untuk menyimpan pengaturan.

Sejauh ini, baris duplikat diidentifikasi dan disorot dalam rentang yang dipilih. Lihat tangkapan layar:

Anda mungkin telah memperhatikan bahwa semua duplikat diidentifikasi atau disorot dengan metode di atas. Terkadang, Anda hanya ingin melihat item mana yang diduplikasi kecuali contoh pertama. Anda juga bisa menyelesaikannya dengan Format Bersyarat fitur dan formula yang berbeda.

1. Pilih kolom dengan tajuk.
Tips: Jika Anda perlu menyorot baris berdasarkan duplikat dalam satu kolom kecuali contoh pertama, pilih rentang tanpa baris tajuk.

2. Klik Beranda > Format Bersyarat > Aturan baru.

3. Dalam dialog Aturan Pemformatan Baru:
(1) Klik untuk menyorot Gunakan rumus untuk menentukan sel mana yang akan diformat pilihan;
(2) Dalam Memformat nilai yang rumus ini benar kotak, ketikkan rumusnya = COUNTIF ($ C $ 3: $ C3, C3)> 1;
Tips: Untuk menyorot baris berdasarkan duplikat dalam satu kolom, ketikkan rumus = COUNTIF ($ C $ 3: $ C3, $ C3)> 1.
(3) Klik dibentuk .

4. Dalam dialog Format Sel yang muncul, tentukan warna sorotan, font, batas sel, dll. Sesuai kebutuhan, lalu klik OK tombol untuk menyimpan pengaturan.

Kemudian Anda akan melihat sel duplikat kecuali contoh pertama di kolom yang dipilih (atau baris berdasarkan duplikat di kolom tertentu) disorot. Lihat tangkapan layar:

Saat kami menyorot duplikat sel atau baris dengan Format Bersyarat fitur, semua duplikat disorot dengan warna yang sama. Namun, jika rangkaian nilai duplikat yang berbeda disorot dengan warna berbeda, akan lebih mudah untuk membaca dan mendapatkan perbedaannya. Di sini, VBA dapat membantu Anda menyelesaikannya di Excel.

1. tekan lain + F11 bersama untuk membuka jendela Microsoft Visual Basic for Applications.

2. Di jendela, klik Menyisipkan > Modul, lalu tempel kode di bawah ini ke jendela modul baru.

Sub HighlightDuplicatesInDifferentColors()
'Update by Extendoffice 20201013
Dim xURg, xRg, xFRg, xRgPre As Range
Dim xAddress As String
Dim xDt As Object
Dim xFNum, xCInt As Long
Dim xBol As Boolean
Dim xWs As Worksheet
Dim xSArr
Set xRg = Application.ActiveWindow.RangeSelection
If xRg.Count > 1 Then
xAddress = xRg.AddressLocal
Else
xAddress = xRg.Worksheet.UsedRange.AddressLocal
End If
On Error Resume Next
Set xURg = Application.InputBox("Select range:", "Kutools for Excel", xAddress, , , , , 8)
If xURg Is Nothing Then Exit Sub
Set xURg = Intersect(xURg.Worksheet.UsedRange, xURg)
Set xDt = CreateObject("scripting.dictionary")
Set xWs = xURg.Worksheet
xCInt = 5
xBol = Application.ScreenUpdating
Application.ScreenUpdating = False
For xFNum = 1 To xURg.Count
Set xFRg = xURg.Item(xFNum)
If Not IsError(xFRg) Then
If xFRg.Value <> "" Then
If xDt.exists(xFRg.Text) Then
xSArr = Split(xDt(xFRg.Text), ";")
If xSArr(1) = "Only" Then
xCInt = xCInt + 1
xSArr(1) = xCInt
Set xRgPre = xWs.Range(xSArr(0))
xRgPre.Interior.ColorIndex = xCInt
xDt(xFRg.Text) = xSArr(0) & ";" & xSArr(1)
End If
xFRg.Interior.ColorIndex = xSArr(1)

Else
xDt(xFRg.Text) = xFRg.Address & ";Only"
End If

End If
End If
Next
xURg.Worksheet.Active
xURg.Select
Application.ScreenUpdating = xBol
End Sub

3. tekan F5 atau klik Run ikon untuk menjalankan VBA ini.

4. Dalam dialog Kutools for Excel yang muncul, pilih kolom tempat Anda akan menyorot sel duplikat dengan warna berbeda, dan klik OK .

Kemudian Anda akan melihat setiap rangkaian nilai duplikat disorot dengan warna.

Alat pertama yang akan saya perkenalkan adalah Pilih Sel Duplikat & Unik fitur, disediakan oleh Kutools untuk Excel. Fitur ini dapat dengan cepat mengetahui sel unik atau duplikat dengan mudah.

1. Pilih kolom atau rentang tempat Anda ingin menemukan dan menyorot sel duplikat.

2. Klik Kutools > Pilih > Pilih Sel Duplikat & Unik.

3. Dalam dialog Select Duplicate & Unique Cells, centang opsi yang Anda butuhkan, dan klik Ok tombol untuk menyelesaikan operasi.

Catatan: Dalam dialog Select Duplicate & Unique Cells,
(1) Jika Anda perlu memilih atau menyorot semua duplikat kecuali contoh pertama, periksa Duplikat (Kecuali yang pertama) pilihan. Jika tidak, periksa Semua duplikat (Termasuk yang pertama) .
(2) Jika Anda perlu menyorot duplikat, centang Isi warna latar opsi, dan tentukan warna sorotan yang Anda butuhkan.
(3) Jika Anda ingin memilih atau menyorot baris berdasarkan duplikat di kolom yang dipilih, centang Pilih seluruh baris .
(4) Jika Anda ingin memilih atau menyorot nilai duplikat dengan kasus yang cocok, centang Hal - hal sensitif .

Kutools untuk Excel juga menyediakan alat yang luar biasa - Bandingkan Sel, untuk membantu kami menemukan dan menyorot sel duplikat dengan mudah dalam dua kolom.

1. Klik Kutools > Bandingkan Sel untuk membuka dialog Select Same & Different Cells.

2. Dalam dialog Select Same & Different Cells, tentukan dua kolom di Temukan Nilai di dan Menurut kotak, centang Nilai yang sama opsi, dan centang opsi lain yang Anda butuhkan.

Catatan:
(1) Jika Anda perlu menemukan baris duplikat, periksa Setiap baris pilihan; dan untuk menemukan sel duplikat, centang Sel tunggal pilihan dalam Berdasarkan bagian;
(2) Centang Isi warna latar opsi dan tentukan warna sorotan jika Anda perlu menyorot baris atau sel duplikat;
(3) Centang Seluruh baris dipilih opsi jika Anda perlu memilih atau menyorot seluruh baris berdasarkan duplikat;
(4) Centang Hal - hal sensitif opsi jika Anda ingin menemukan atau menyorot duplikat peka huruf besar kecil.

3. Klik Ok tombol berturut-turut untuk menyelesaikan pengaturan.

Kemudian Anda akan melihat duplikat di kolom yang Anda tentukan di Temukan nilai dalam kotak diidentifikasi dan disorot.

Metode ini akan memandu Anda untuk mengidentifikasi dan menyorot sel duplikat dengan aturan pemformatan bersyarat, dan kemudian memfilter menurut warna sorotan dengan mudah di Excel.

1. Terapkan pemformatan bersyarat untuk menemukan dan menyorot duplikat di kolom yang ditentukan. (Klik untuk melihat caranya)

2. Klik untuk memilih tajuk kolom dari kolom yang ditentukan, dan klik Data > Filter.

3. Silakan klik ikon filter  di tajuk kolom, dan pilih Filter dengan Warna, lalu pilih warna pemformatan bersyarat yang ditentukan dalam daftar turun bawah. Lihat tangkapan layar:

Kemudian Anda hanya akan melihat baris dengan sel duplikat yang disaring. Lihat tangkapan layar:

Alternatifnya, kita juga bisa mengidentifikasi duplikat dengan rumus di kolom pembantu, lalu memfilter duplikat dengan kolom pembantu dengan mudah di Excel.

1. Selain data asli, tambahkan kolom pembantu, dan ketik Duplikat sebagai tajuk kolom.

2. Pilih sel kosong pertama di bawah header kolom, masukkan rumusnya = IF (COUNTIF ($ C $ 3: $ C $ 12, C3)> 1, "Duplikat", ""), dan seret gagang IsiOtomatis sel ini untuk menyalin rumus ini ke sel lain.

Catatan: Dalam rumus di atas, $ C $ 3: $ C $ 12 adalah kolom yang berisi data duplikat, dan C3 adalah sel pertama (kecuali sel header) di kolom.

3. Klik untuk memilih tajuk kolom - Duplikat, dan klik Data > Filter.

4. Kemudian klik ikon filter  di tajuk kolom, centang saja Duplikat, dan klik OK tombol. Lihat tangkapan layar:

Sejauh ini, baris dengan nilai duplikat hanya difilter. Lihat tangkapan layar:

Secara umum, kita bisa menerapkan = COUNTIF (rentang, kriteria) untuk menghitung jumlah total nilai tertentu yang muncul dalam kisaran yang ditentukan. Mengatakan menghitung berapa kali "Apple" muncul di daftar A2: A10, Kita dapat menerapkan rumusnya = COUNTIF (A2: A10, "Apple") untuk menghitung jumlah nilai duplikat ini.

Namun, rumus = COUNTIF (rentang, kriteria) hanya menghitung nilai duplikat yang ditentukan. Bagaimana menghitung nilai duplikat dengan dua atau beberapa kriteria? Dan bagaimana jika menghitung duplikat peka huruf besar kecil dengan kriteria? Metode di bawah ini dapat membantu Anda memecahkan masalah ini.

Kita dapat menerapkan rumus array untuk menghitung nilai duplikat peka huruf besar kecil dengan kriteria di Excel. Misalnya, untuk menghitung berapa kali nilai "Apple" muncul di daftar B2: B21 dengan kasus yang cocok, Anda dapat menyelesaikannya sebagai berikut:

1. Pilih sel kosong.

2. Masukkan rumusnya = SUM (- PERSIS (B2: B20, E2)).

3. tekan Ctrl + perubahan + Enter untuk mengembalikan hasil penghitungan.

Catatan: Dalam rumus array,
(1) B2: B20 adalah kolom yang akan Anda hitung duplikatnya.
(2) E2 adalah sel yang berisi nilai tertentu yang ingin Anda hitung jumlah kemunculannya. Anda bisa mengubah referensi sel ke nilai dengan tanda kutip, kata "Apel".

Terkadang, Anda mungkin ingin menghitung duplikat dengan dua kriteria atau lebih. Anda dapat menerapkan COUNTIF berfungsi untuk menyelesaikannya.
Misalnya, ada tabel penjualan buah seperti gambar di bawah ini. Di sini kita perlu menghitung waktu pengulangan apel yang terjual pada 7/5/2020 dan jumlah penjualannya lebih dari 300. Anda bisa menghitung duplikatnya dengan kriteria berikut:

1. Pilih sel kosong.

2. Masukkan rumusnya =COUNTIFS(B3:B20,G4,C3:C20,G3,D3:D20,">300").

3. Tap Enter kunci untuk mendapatkan hasil penghitungan.

Catatan: Dalam rumus di atas,
(1) B3: B20 adalah kolom tanggal (pertama), dan G4 adalah kriteria tanggal;
(2) C3: C20 adalah kolom buah (kedua), dan G3 adalah kriteria buah;
(3) D3: D20 adalah kolom jumlah (ketiga), dan "> 300" adalah kriteria jumlah.
(4) Jika ada lebih banyak kolom dan kriteria di tabel Anda, Anda dapat menambahkan referensi kolom dan kriteria.

Untuk menghitung semua duplikat dalam kolom kecuali kejadian pertama, lakukan hal berikut:

1. Pilih sel kosong di samping kolom.

2. Masukkan rumusnya = JIKA (COUNTIF ($ B $ 3: B3, B3)> 1, "YA", ""), lalu seret gagang IsiOtomatis ke bawah untuk menyalin rumus ini ke sel lain.

Catatan: Dalam rumus di atas,
(1) $ B $ 3: B3 adalah rentang tempat Anda menghitung duplikatnya. Di $ B $ 3: B3, B3 akan berubah secara otomatis saat Anda menyalin rumus ke sel lain.
(2) B3 adalah sel pertama di kolom yang ditentukan.
(3) Rumus ini akan mengembalikan YA atau kosong. YES menunjukkan nilai yang sesuai adalah duplikat, sedangkan kosong berarti unik.

Kemudian semua duplikat di kolom yang ditentukan diidentifikasi. Kami dapat menghitung hasil rumus untuk mendapatkan jumlah total duplikat.

3. Pilih sel kosong.

4. Masukkan rumusnya = COUNTIF (C3: C16, "YA"), dan tekan tombol Enter kunci.

Catatan: Dalam rumus di atas,
(1) C3: C16 adalah rentang yang kami terapkan rumus untuk mengidentifikasi duplikat di langkah terakhir.
(2) YES adalah nilai yang dikembalikan oleh rumus terakhir.

Kemudian kami mendapatkan jumlah total nilai duplikat dalam kolom yang ditentukan. Jumlah total duplikat tidak termasuk kejadian pertama.

Untuk menghitung jumlah semua duplikat termasuk kejadian pertama di Excel, Anda bisa menerapkan rumus array untuk mencapainya.

1. Pilih sel kosong.

2. Masukkan rumusnya =ROWS(B3:B16)-SUM(IF(COUNTIF(B3:B16,B3:B16) =1,1,0)).

3. tekan Ctrl + perubahan + Enter untuk mengembalikan hasil penghitungan.

Catatan: Dalam rumus di atas, B3: B16 adalah kolom tertentu yang ingin kita hitung duplikatnya termasuk kejadian pertama di dalamnya.

Untuk menyederhanakan pekerjaan Anda dan membebaskan diri dari menghafal rumus lama yang membosankan, Anda dapat mencoba Pilih Sel Duplikat & Unik fitur, disediakan oleh Kutools untuk Excel, untuk menghitung dengan cepat jumlah nilai duplikat dalam daftar atau kolom tertentu tidak termasuk atau termasuk kejadian pertama.

1. Pilih kolom di mana Anda akan menghitung jumlah nilai duplikat, dan klik Kutools > Pilih > Pilih Sel Duplikat & Unik.

2. Dalam dialog Select Duplicate & Unique Cells, centang Duplikat (Kecuali yang pertama) or Semua duplikat (Termasuk yang pertama) pilihan yang Anda butuhkan, dan klik Ok .

3. Kemudian semua nilai duplikat termasuk atau tidak termasuk kejadian pertama dipilih, dan secara bersamaan sebuah dialog keluar dan menunjukkan berapa banyak sel yang telah dipilih. Lihat tangkapan layar di atas.

Mengatakan Anda ingin membandingkan dua daftar nama dan menghitung jumlah duplikat di antaranya, bagaimana Anda bisa menyelesaikan masalah ini dengan cepat? Kami juga bisa menyelesaikannya dengan rumus di Excel.

1. Pilih sel kosong.

2. Masukkan rumusnya = SUMPRODUCT (- (ISNUMBER (MATCH (B3: B12, D3: D18,0)))).

3. Tap Enter kunci.

Catatan: Dalam rumus di atas,
(1) B3: B12 adalah kolom pertama nama yang akan Anda hitung duplikatnya.
(2) D3: D18 adalah kolom kedua dari nama yang akan Anda hitung duplikatnya.

Atau, kita dapat menggunakan add-in pihak ketiga, Kutools untuk Excel, untuk menghitung dengan cepat jumlah total sel duplikat di antara dua kolom dengan mudah.

1. Klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda.

2. Dalam dialog Select Same & Difference Cells,
(1) Tentukan dua kolom di Temukan nilai dalam dan Menurut kotak secara terpisah.
(2) Periksa Sel tunggal .
(3) Periksa Nilai yang sama .

4. klik Ok .

Kemudian semua sel duplikat di kolom pertama dipilih, dan secara bersamaan dialog meminta dan menunjukkan berapa banyak sel duplikat yang telah dipilih. Lihat tangkapan layar:

Catatan: Fitur ini akan menghitung jumlah total nilai duplikat di kolom yang Anda tentukan di Temukan nilai dalam kotak di dialog Select Same & Different Cells. Jika Anda perlu menghitung jumlah total nilai duplikat di kolom kedua, terapkan Pilih Sel yang Sama & Berbeda fitur lagi dengan menentukan kolom kedua di Temukan nilai dalam kotak.

Anda dapat dengan cepat menghitung setiap nilai duplikat satu kali dengan rumus sebagai berikut:

1. Pilih sel kosong.

2. Masukkan rumusnya =SUMPRODUCT((C3:C19<>"")/COUNTIF(C3:C19,C3:C19&"")).

3. Tap Enter kunci untuk mendapatkan hasil penghitungan.

Catatan: Dalam rumus di atas, C3: C19 adalah kolom tertentu yang ingin Anda hitung setiap nilai duplikat satu kali.

Saat menghitung daftar, setiap nilai duplikat dapat dihitung sekali dengan kasus yang cocok, Anda dapat menerapkan rumus array untuk menyelesaikannya dengan mudah di Excel.

1. Pilih sel kosong.

2. Masukkan rumus array = SUM (IFERROR (1 / IF (C3: C19 <> "", FREQUENCY) (IF (EXACT (C3: C19, TRANSPOSE (C3: C19)), MATCH (ROW (C3: C19), ROW (C3: C19) ), ""), MATCH (BARIS (C3: C19), BARIS (C3: C19))), 0), 0)).

3. tekan Ctrl + perubahan + Enter kunci bersama untuk mengembalikan hasil penghitungan.

Catatan: Dalam rumus array di atas, C3: C19 adalah kolom yang ditentukan di mana Anda akan menghitung setiap rangkaian duplikat satu kali dengan kasus yang cocok.

Jika Anda memiliki Kutools untuk Excel diinstal, Anda juga dapat menerapkannya Hitung sel dengan nilai unik fitur untuk menghitung dengan cepat setiap rangkaian nilai duplikat satu kali di Excel.

1. Pilih sel kosong.

2. Klik Kutools > Pembantu Formula > Statistik > Hitung sel dengan nilai unik (termasuk nilai duplikat pertama).

3. Dalam dialog Pembantu Formula, tentukan kolom yang akan Anda hitung duplikatnya sekali di Jarak kotak, dan klik Ok .

Kemudian hasil penghitungan segera diisi ke sel yang dipilih.

Kita bisa menerapkan Subtotal fitur untuk menghitung setiap rangkaian nilai duplikat dalam kolom di Excel.

1. Pilih kolom yang akan Anda hitung setiap rangkaian nilai duplikat di dalamnya, dan klik Data > Urutkan A sampai Z or Urutkan Z ke A.

2. Dalam dialog Sort Warning yang muncul, centang Perluas seleksi pilihan, dan klik Jenis .

Kemudian Anda akan melihat pilihan telah diurutkan berdasarkan nilai duplikat dari kolom yang ditentukan.

3. Tetap pilih seleksi, dan klik Data > Subtotal.

4. Dalam dialog Subtotal,
(1) Pilih kolom tertentu dari Di setiap perubahan daftar drop-down;
(2) Pilih Menghitung dari Gunakan fungsi daftar drop-down;
(3) Hanya centang kolom yang ditentukan di Tambahkan subtotal ke kotak daftar;
(4) Klik OK .

Kemudian Anda akan melihat setiap rangkaian nilai duplikat dihitung, dan hasil penghitungan ditambahkan di bawah setiap rangkaian nilai duplikat, lihat tangkapan layar di atas.

Kami juga dapat membuat tabel pivot untuk menghitung dengan cepat setiap rangkaian nilai duplikat dengan mudah di Excel.

1. Pilih rentang yang berisi kolom tertentu, dan klik Menyisipkan > Tabel pivot.

2. Dalam dialog Buat PivotTable, tentukan tempat Anda akan menemukan tabel pivot baru, dan klik OK .

3. Di panel Bidang PivotTable, seret kolom yang ditentukan ke keduanya Baris dan Nilai - Nilai bagian. Kemudian Anda akan melihat setiap rangkaian nilai duplikat di kolom tertentu dihitung secara massal. Lihat tangkapan layar:

Jika sudah terpasang Kutools untuk Excel sudah, Anda dapat menerapkannya yang mudah digunakan Lanjutan Gabungkan Baris fitur untuk menghitung dengan cepat setiap rangkaian nilai duplikat di kolom yang ditentukan.

Catatan: Para Baris Gabungan Lanjutan Fitur akan mengubah rentang yang dipilih dan menghapus baris berdasarkan nilai duplikat di kolom kunci utama yang ditentukan. Untuk menyimpan data Anda, disarankan untuk membuat cadangan atau menyalin data Anda ke tempat lain sebelum operasi di bawah ini.

1. Tambahkan kolom kosong tepat ke rentang data asli, dan beri nama kolom baru sebagai Menghitung.

2. Pilih rentang data asli dan kolom baru bersama-sama, dan klik Kutools > Gabungkan & Pisahkan > Lanjutan Gabungkan Baris.

3. Dalam dialog Gabungkan Baris Tingkat Lanjut,
(1) Klik untuk memilih kolom tertentu yang akan Anda hitung setiap rangkaian nilai duplikat, dan klik Kunci utama.
(2) Klik untuk memilih kolom baru (Hitung), lalu klik Dihitung > Menghitung.
(3) Tentukan kombinasi atau tipe kalkulasi untuk kolom lain jika perlu.
(4) Klik Ok .

Kemudian Anda akan melihat setiap nilai duplikat rangkaian di kolom tertentu dihitung secara massal. Lihat tangkapan layar:

Mengatakan ada daftar buah-buahan di kolom. Dalam daftar tersebut, beberapa buah muncul berkali-kali. Sekarang Anda perlu menandai setiap duplikat buah sesuai urutan kemunculannya, bagaimana Anda bisa menyelesaikannya? Di sini, bagian ini akan memperkenalkan rumus untuk menyelesaikannya dengan mudah di Excel.

1. Tambahkan kolom kosong tepat ke data asli.

2. Masukkan rumusnya =IF(COUNTIF($C$3:$C$14,C3)>1,COUNTIF(C$3:C3,C3),"") di sel pertama dari kolom kosong yang ditambahkan.

3. Seret gagang IsiOtomatis sel rumus ini untuk menyalin rumus ke sel lain.

Catatan: Dalam rumus di atas,
(1) $ C $ 3: $ C $ 14 adalah kolom tertentu yang ingin Anda hitung nilai duplikatnya secara berurutan.
(2) C3 adalah sel pertama di kolom yang ditentukan.
(3) Jika nilai yang sesuai adalah duplikat, rumus ini akan mengembalikan nomor urut 1, 2, 3… berdasarkan urutan tampilan; jika nilai terkait unik, rumus ini akan kembali kosong.

Anda dapat menerapkan Hapus Duplikat fitur untuk menghapus semua nilai duplikat kecuali kejadian pertama secara langsung.

1. Pilih kolom tempat Anda ingin menghapus semua nilai duplikat kecuali kejadian pertama.

2. Klik Data > Hapus duplikat.

3. Dalam dialog Remove Duplicates Warning, centang file Lanjutkan dengan pilihan saat ini pilihan, dan klik Hapus Duplikat .

Tips: Untuk menghapus baris berdasarkan pada nilai duplikat dalam pemilihan, centang Perluas seleksi .

4. Dalam dialog Hapus Duplikat, centang hanya kolom yang ditentukan, dan klik OK .

Tips: Jika Anda telah memeriksa Perluas seleksi opsi di langkah terakhir, semua kolom akan dicantumkan di sini. Meski begitu, Anda perlu memeriksa kolom yang ditentukan saja.

5. Lalu sebuah dialog meminta dan menunjukkan berapa banyak nilai duplikat yang telah dihapus. Klik OK tombol untuk menutupnya.

Anda juga bisa menerapkan Filter Lanjutan fitur untuk menghapus semua nilai duplikat dari kolom yang ditentukan dengan mudah.

1. Klik Data > Advanced.

2. Dalam dialog Filter Tingkat Lanjut,
(1) Periksa Salin ke lokasi lain pilihan;
(2) Dalam Rentang daftar kotak, pilih kolom tertentu yang akan Anda hapus nilai duplikatnya;
(3) Dalam Salin ke kotak, tentukan rentang yang akan Anda tempelkan kolom;
(4) Centang Rekaman unik saja .
(5) Klik OK .

Kemudian Anda akan melihat kolom yang ditentukan ditempelkan ke kisaran yang ditentukan dengan semua nilai duplikat dihapus kecuali kejadian pertama. Lihat tangkapan layar:

Anda juga dapat menerapkan VBA untuk menghapus nilai duplikat dengan cepat kecuali kejadian pertama dari kolom di Excel.

1. tekan lain + F11 kunci untuk membuka jendela Microsoft Visual Basic for Application.

2. Klik Menyisipkan > Modul, lalu tempel kode VBA di bawah ini ke jendela modul baru.

Sub ExtendOffice_RemoveAllDeplicate () Dim xRg As Range Dim xURg, xFRg, xFFRg As Range Dim xI, xFNum, xFFNum As Integer Dim xDc As Object Dim xDc_keys Dim xBol As Boolean Dim xStr As String Dim xWs As Worksheet Red Dim xURgAddress As String On Error Resume Set Berikutnya xRg = Application.InputBox ("Select range:", "Kutools for Excel", "",,,,, 8) Jika xRg Tidak Ada Lalu Keluar Sub Set xURg = Intersect (xRg.Worksheet.UsedRange, xRg) Atur xWs = xURg.Worksheet Set xDc = CreateObject ("scripting.dictionary") xURgAddress = xURg.Address xBol = Application.ScreenUpdating Application.ScreenUpdating = False Untuk xFNum = 1 Ke xURg.Count Set xFRg = xURg.Item (xFNum) Jika (Bukan IsError (xFRg)) Kemudian Jika xFRg.Value <> "" Dan (Bukan IsError (xFRg)) Kemudian Untuk xFFNum = xFNum + 1 Ke xURg. Hitung Set xFFRg = xURg.Item (xFFNum) Jika Bukan IsError (xFFRg) Lalu Jika xFFRg .Value = xFRg.Value Lalu xDc (xFFRg.Address) = "" Akhiri Jika Berakhir Jika Akhir Berikutnya Jika Berakhir Jika Berikutnya xStr = "" xDc_keys = xDc.Keys Untuk xI = 1 Ke UBound (xDc_keys) Jika xStr = "" Lalu xStr = xDc_keys (xI) SetxURg = xWs.Range (xStr) Else xStr = xStr & "," & xDc_keys (xI) Set xURg = Application.Union (xWs.Range (xDc_keys (xI)), xURg) End If Next Debug.Print xStr xWs.Activate xURg.Select Selection.Delete Shift: = xlUp xWs.Range (xURgAddress). Pilih Application.ScreenUpdating = xBol End Sub

3. tekan F5 atau klik Run tombol untuk menjalankan VBA ini.

4. Dalam dialog popping out, tentukan rentang yang akan Anda hapus nilai duplikatnya, dan klik OK .

Kemudian semua nilai duplikat kecuali kejadian pertama akan segera dihapus dari kisaran yang ditentukan.

Catatan: Kode VBA ini case sensitive.

Kami dapat menyorot semua nilai duplikat termasuk kejadian pertama dalam kolom atau daftar dengan aturan pemformatan bersyarat, lalu memfilter semua nilai duplikat dengan warna penyorotan. Setelah itu, kita dapat memilih semua sel duplikat yang disaring dan kemudian menghapusnya secara massal.

1. Terapkan pemformatan bersyarat untuk menyorot nilai duplikat. (Klik untuk melihat caranya)

2. Pilih kolom tempat Anda akan menghapus nilai duplikat (termasuk kemunculan pertama), dan klik Data > Filter.

3. Klik ikon Filter  di tajuk kolom dari kolom yang ditentukan. Di menu drop-down, pilih Filter dengan Warna, lalu tentukan warna sorotan dari submenu.

Kemudian semua nilai duplikat disaring.

4. Pilih semua sel yang difilter, klik kanan, dan pilih Hapus Baris dari menu konteks. Dan di dialog konfirmasi ulang muncul keluar klik OK tombol untuk melanjutkan.

5. Kemudian semua nilai duplikat dihapus secara massal. Tetap pilih daftar yang difilter, dan klik Filter > Data sekali lagi untuk membatalkan filter.

Sampai sekarang, Anda akan melihat semua sel duplikat termasuk kejadian pertama dihapus secara massal, dan hanya nilai unik yang tersisa.

Catatan: Metode ini akan menghapus baris berdasarkan nilai duplikat di kolom tertentu.

Kami juga dapat menerapkan rumus untuk mengidentifikasi nilai duplikat termasuk kemunculan pertama di kolom pembantu, lalu memfilter nilai duplikat menurut hasil rumus, dan terakhir menghapus nilai duplikat yang difilter ini secara massal.

1. Tambahkan kolom pembantu di samping kolom yang ditentukan, masukkan rumus = COUNTIF ($ B $ 3: $ B $ 11, B3) ke dalam sel pertama kolom pembantu, lalu seret gagang IsiOtomatis ke bawah untuk menyalin rumus ini ke sel lain. Lihat tangkapan layar:

Note: Dalam rumus di atas, $ B $ 3: $ B $ 11 adalah kolom tertentu tempat Anda akan menghapus nilai duplikat, dan B3 adalah sel pertama di kolom yang ditentukan.

2. Pilih kolom pembantu, dan klik Data > Filter.

3. Klik ikon filter  di tajuk kolom pembantu, lalu di menu tarik-turun periksa semua nilai kecuali 1, dan klik OK tombol. Lihat tangkapan layar:

4. Sekarang semua nilai duplikat disaring. Pilih sel yang difilter di kolom pembantu, klik kanan, dan pilih Hapus Baris dalam menu konteks.

5. Pada dialog konfirmasi ulang yang muncul, klik OK tombol untuk melanjutkan.

6. Sekarang semua nilai duplikat dan barisnya dihapus secara massal. Silakan klik Data > Filter sekali lagi untuk membatalkan filter.

Kemudian Anda akan melihat semua nilai duplikat termasuk kejadian pertama dihapus secara massal.

Jika Anda memiliki Kutools untuk Excel diinstal, Anda juga dapat menerapkannya Pilih Sel Duplikat & Unik fitur untuk dengan cepat memilih dan menghapus nilai duplikat termasuk atau mengecualikan kejadian pertama dengan mudah di Excel.

1. Pilih kolom yang duplikatnya akan Anda hapus.

2. Klik Kutools > Pilih > Pilih Sel Duplikat & Unik.

3. Dalam dialog Select Duplicate & Unique Cells, centang Semua duplikat (Termasuk yang pertama) pilihan, dan klik Ok .

Catatan:
(1) Untuk memilih dan menghapus nilai duplikat tidak termasuk kejadian pertama, centang Duplikat (Kecuali yang pertama) .
(2) Untuk memilih dan menghapus baris berdasarkan nilai duplikat di kolom yang ditentukan, centang Pilih seluruh baris .
(3) Untuk memilih dan menghapus nilai duplikat dengan kasus yang cocok, centang Hal - hal sensitif .
(4) Untuk memilih, menyorot, dan menghapus sel atau baris duplikat, centang Isi warna latar or Isi warna font opsi dan tentukan warna isian atau font yang Anda butuhkan.

4. Lalu sebuah dialog meminta dan menunjukkan berapa banyak sel yang telah dipilih, klik OK tombol untuk menutupnya.

5. Klik kanan sel yang dipilih, dan pilih Delete dari menu konteks

6. Di dialog Hapus yang akan datang, centang Geser sel ke atas pilihan, dan klik OK .

Sejauh ini, semua nilai duplikat termasuk kejadian pertama telah dihapus secara massal.

Dalam kebanyakan kasus, kami mengidentifikasi nilai duplikat dalam kolom, lalu menghapus seluruh baris dengan nilai duplikat. Sejujurnya, operasi ini sangat mirip dengan menghapus nilai duplikat dari satu kolom. Hasilnya, kami dapat menggunakan solusi serupa untuk menghapus baris berdasarkan duplikat di kolom yang ditentukan.

Metode pertama adalah menerapkan built-in Hapus Duplikat fitur untuk menghapus baris dengan duplikat di kolom tertentu. Cukup pilih rentang baris yang akan Anda hapus, klik Data > Hapus Duplikat untuk mengaktifkan fitur tersebut, hanya centang kolom yang ditentukan dalam dialog Hapus Duplikat, lalu klik OK untuk menyelesaikan operasi pelepasan.

Kami juga dapat menerapkan fitur Pemformatan dan Filter Bersyarat untuk menghapus baris berdasarkan nilai duplikat di kolom yang ditentukan. Pertama-tama, sorot baris berdasarkan nilai duplikat di kolom tertentu dengan aturan formasi bersyarat (klik untuk melihat caranya). Kedua, filter rentang berdasarkan warna. Ketiga, hapus semua baris yang difilter dengan mudah. Terakhir, hapus atau batalkan filter, dan Anda hanya akan melihat baris dengan nilai unik di kolom tertentu yang tersisa.

Alternatifnya, Anda bisa menambahkan kolom pembantu, dan menerapkan rumus = COUNTIF ($ C $ 3: $ C $ 21, C3) untuk mengidentifikasi duplikat di kolom tertentu. Kemudian filter angka yang lebih besar dari 1 di kolom pembantu, dan hapus semua baris yang difilter dengan mudah. Setelah membersihkan filter, Anda akan melihat baris dengan nilai unik di kolom yang ditentukan hanya tersisa.

Add-in pihak ketiga Kutools untuk Excel juga menghadirkan fitur praktis yang luar biasa, Pilih Sel Duplikat & Unik, untuk memilih baris dengan cepat berdasarkan nilai duplikat di kolom yang ditentukan, lalu Anda dapat dengan cepat menghapus baris yang dipilih ini dengan mengklik kanan menu dengan mudah.

Kutools untuk Excel Lanjutan Gabungkan Baris Fitur juga dapat dengan cepat menghapus baris berdasarkan nilai duplikat di kolom kunci utama yang ditentukan.

Kita bisa menambahkan kolom pembantu dan menerapkan rumus untuk mengidentifikasi nilai duplikat antara dua kolom, lalu memfilter dan menghapus nilai duplikat dengan mudah.

1. Tambahkan kolom kosong di samping kolom tertentu yang akan Anda hapus nilai duplikatnya.

2. Di sel pertama kolom pembantu (tidak termasuk sel header), ketikkan rumus = IF (ISERROR (MATCH (C2, $ A $ 2: $ A $ 13,0)), "Unik", "Duplikat"), lalu seret gagang IsiOtomatis ke bawah untuk menyalin rumus ke sel lain.

Catatan: Dalam rumus di atas,
(1) C2 adalah sel pertama di kolom tertentu yang akan Anda hapus nilai duplikatnya;
(2) $ A $ 2: $ A $ 13 adalah kolom lain yang perlu kita bandingkan.
(3) Rumus ini akan kembali Duplikat jika nilai terkait duplikat dengan nilai di kolom lain, dan kembali Khas Papua jika berbeda dengan nilai di kolom lain.

3. Pilih kolom pembantu, dan klik Data > Filter.

4. Klik ikon filter  di tajuk kolom pembantu, lalu di menu drop-down hanya centang Duplikat, dan klik OK .

5. Sekarang semua nilai duplikat disaring. Pilih sel yang difilter, klik kanan, dan pilih Hapus Baris dari menu konteks. Lalu klik OK di dialog konfirmasi ulang yang muncul.

6. Kemudian semua nilai duplikat dihapus dari kolom yang ditentukan. Silakan klik Data > Filter sekali lagi untuk membatalkan filter.

Kemudian Anda hanya akan melihat nilai unik yang tersisa di kolom yang ditentukan. Anda dapat menghapus kolom pembantu sesuai kebutuhan.

Catatan: Metode ini akan menghapus seluruh baris berdasarkan nilai duplikat di kolom tertentu.

Jika Anda memiliki Kutools untuk Excel diinstal, Anda dapat menggunakan yang luar biasa Pilih Sel yang Sama & Berbeda fitur untuk memilih nilai duplikat dengan cepat di antara dua kolom, lalu menghapusnya dengan mudah.

1. Klik Kutools > Pilih > Pilih Sel yang Sama & Berbeda untuk mengaktifkan fitur ini.

2. Dalam dialog Select Same & Different Cells, tentukan kedua kolom di Temukan Nilai di dan Menurut kotak secara terpisah, centang Sel tunggal dan Nilai yang sama opsi, dan klik Ok tombol. Lihat tangkapan layar:

3. Kemudian semua nilai duplikat berjumlah dua kolom dipilih di kolom pertama (kolom yang Anda tentukan di Temukan nilai dalam kotak). Dan klik OK tombol di dialog yang muncul.

4. Anda bisa menekan Delete untuk menghapus nilai duplikat ini secara langsung, atau klik kanan dan pilih Delete dari menu konteks