7 cara menghitung nilai unik di excel
Misalkan Anda memiliki daftar nilai yang setiap nilainya dimasukkan beberapa kali.
Dan sekarang…
Anda ingin menghitung nilai unik dalam daftar ini untuk mendapatkan jumlah nilai sebenarnya di dalamnya.
Untuk melakukan ini, Anda perlu menggunakan metode yang hanya akan menghitung nilai satu kali dan mengabaikan semua kejadian lain dalam daftar.
Di Excel, Anda bisa menggunakan metode berbeda untuk mendapatkan sejumlah nilai unik. Itu tergantung pada jenis nilai yang Anda miliki sehingga Anda dapat menggunakan metode terbaik untuk ini.
Dalam artikel hari ini, saya ingin berbagi dengan Anda 6 metode berbeda untuk menghitung nilai unik dan menggunakan metode ini bergantung pada jenis nilai yang Anda miliki.
Filter lanjutan untuk mendapatkan sejumlah nilai unik
Menggunakan filter lanjutan adalah salah satu cara termudah untuk memeriksa jumlah nilai unik dan Anda bahkan tidak memerlukan rumus yang rumit. Di sini kami memiliki daftar nama dan dari daftar ini Anda perlu menghitung jumlah nama unik.
Berikut langkah-langkah untuk mendapatkan nilai unik:
- Pertama, pilih salah satu sel dalam daftar.
- Setelah itu, buka tab Data ➜ Sortir & Filter ➜ Klik Lanjutan .
- Setelah Anda mengkliknya, Anda akan mendapatkan jendela pop-up untuk menerapkan filter lanjutan.
- Sekarang dari jendela ini pilih “ Salin ke lokasi lain ”.
- Di “Salin ke”, pilih sel kosong tempat Anda ingin menempelkan nilai unik.
- Sekarang, centang kotak “ Catatan tunggal saja ” dan klik OK.
- Pada titik ini Anda memiliki daftar nilai unik .
- Sekarang pergi ke sel di bawah sel terakhir dalam daftar dan masukkan rumus berikut dan tekan Enter.
=COUNTA(B2:B10)Ini akan mengembalikan jumlah nilai unik dalam daftar nama ini.
Anda sekarang memiliki daftar nilai unik dan Anda juga menghitungnya. Metode ini sederhana dan mudah diikuti karena Anda tidak perlu menulis rumus rumit apa pun untuk melakukannya.
Menggabungkan SUM dan COUNTIF untuk menghitung nilai unik
Jika Anda ingin mencari jumlah nilai unik dalam satu sel tanpa mengekstrak daftar terpisah, Anda bisa menggunakan kombinasi SUM dan COUNTIF.
Dalam metode ini, Anda hanya perlu merujuk ke daftar nilai dan rumus akan mengembalikan jumlah nilai unik. Ini adalah rumus array, jadi Anda harus memasukkannya sebagai tabel, dan saat memasukkannya, gunakan Ctrl+Shift+Enter.
Dan rumusnya adalah:
=SUM(1/COUNTIF(A2:A17,A2:A17))Jika Anda memasukkan rumus ini dalam bentuk tabel, maka akan terlihat seperti ini.
{=SUM(1/COUNTIF(A2:A17,A2:A17))}
Bagaimana itu bekerja
Untuk memahami rumus ini Anda perlu memecahnya menjadi tiga bagian dan ingatlah bahwa kita telah memasukkan rumus ini dalam bentuk tabel dan ada total 16 nilai dalam daftar ini, tidak unik tetapi total.
Oke, jadi lihatlah.
Pada bagian pertama Anda menggunakan COUNIF untuk menghitung jumlah setiap nilai mulai dari 16 dan di sini COUNTIF mengembalikan nilai seperti di bawah ini.
Di bagian kedua , Anda membagi semua nilai dengan 1, yang mengembalikan nilai seperti ini.
Katakanlah jika suatu nilai ada dua kali dalam daftar maka akan menghasilkan 0,5 untuk kedua nilai sehingga pada akhir ketika Anda menjumlahkannya akan menjadi 1 dan jika suatu nilai ada tiga kali maka akan menghasilkan 0,333 untuk masing-masing nilai.
Dan, di bagian ketiga , Anda cukup menggunakan fungsi SUM untuk menjumlahkan semua nilai ini dan Anda memiliki sejumlah nilai unik.
Rumus ini cukup ampuh dan dapat membantu Anda menghitung dalam satu sel.
Gunakan SUMPRODUK + COUNTIF untuk mendapatkan hitungan nilai unik dari sebuah daftar
Pada cara terakhir, Anda menggunakan metode SUM dan COUNTIF. Namun, Anda juga bisa menggunakan SUMPRODUK sebagai pengganti SUM.
Dan saat menggunakan SUMPRODUK , Anda tidak perlu memasukkan rumus dalam bentuk tabel. Cukup edit sel dan masukkan rumus di bawah ini.
=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))Jika Anda memasukkan rumus ini dalam bentuk tabel, maka akan terlihat seperti ini.
{=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))}
Bagaimana itu bekerja
Rumus ini bekerja dengan cara yang persis sama seperti yang Anda pelajari pada metode di atas, perbedaannya adalah Anda menggunakan SUMPRODUK, bukan SUM.
Dan SUMPRODUK dapat mengambil array tanpa menggunakan Ctrl+Shift+Enter.
Hitung hanya nilai teks unik dari daftar
Sekarang, misalkan Anda memiliki daftar nama yang juga memiliki nomor ponsel dan Anda ingin menghitung nilai unik dari nilai teks saja. Jadi dalam hal ini, Anda bisa menggunakan rumus di bawah ini:
=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))Dan ketika Anda memasukkan rumus ini dalam bentuk tabel.
{=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}
Bagaimana itu bekerja
Dalam metode ini Anda telah menggunakan fungsi IF dan ISTEXT. ISTEXT pertama-tama memeriksa apakah semua nilai berupa teks atau bukan dan mengembalikan TRUE jika ada nilai yang berupa teks.
Setelah itu, IF menerapkan COUNTIF pada semua nilai teks yang bernilai TRUE dan nilai lainnya tetap kosong.
Dan pada akhirnya SUM mengembalikan jumlah semua nilai unik yang berupa teks dan dengan demikian Anda mendapatkan jumlah nilai teks unik.
Dapatkan jumlah nomor unik dari daftar
Dan jika Anda hanya ingin menghitung angka unik dari daftar nilai, Anda bisa menggunakan rumus di bawah ini.
=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))Masukkan rumus ini dalam bentuk tabel.
{=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}
Bagaimana itu bekerja
Dalam metode ini, Anda telah menggunakan fungsi IF dan ISNUM. ISNUMBER pertama-tama memeriksa apakah semua nilai berupa numerik atau bukan dan mengembalikan TRUE jika suatu nilai berupa angka.
Setelah itu IF menerapkan COUNTIF pada semua nilai numerik yang memiliki TRUE dan nilai lainnya tetap kosong.
Dan pada akhirnya SUM mengembalikan jumlah semua nilai unik yang berupa angka dan dengan demikian Anda mendapatkan jumlah angka unik.
Hitung nilai unik dengan UDF
Disini saya memiliki VBA (UDF) yang dapat membantu Anda menghitung nilai unik tanpa menggunakan rumus apa pun.
Function CountUnique(ListRange As Range) As Integer Dim CellValue As Variant Dim UniqueValues As New Collection Application.Volatile On Error Resume Next For Each CellValue In ListRange UniqueValues.Add CellValue, CStr(CellValue) ' add the unique item Next CountUnique = UniqueValues.Count End FunctionMasukkan fungsi ini ke dalam VBE Anda dengan memasukkan modul baru, lalu buka spreadsheet Anda dan masukkan rumus berikut.
=CountUnique(range)
Dapatkan file Excelnya
Kesimpulan
Menghitung nilai unik dapat berguna saat bekerja dengan kumpulan data yang besar.
Daftar nama yang Anda gunakan di sini memiliki nama duplikat dan setelah menghitung nomor uniknya kami mendapatkan ada 10 nama unik dalam daftar tersebut.
Nah, semua metode yang Anda pelajari di sini berguna dalam berbagai situasi dan Anda dapat menggunakan salah satu metode yang menurut Anda cocok untuk Anda.
Jika Anda bertanya kepada saya, filter lanjutan dan SUMPRODUK adalah metode favorit saya, tetapi sekarang Anda harus memberi tahu saya:
Yang mana favoritmu?
Silakan bagikan pandangan Anda dengan saya di bagian komentar, saya ingin mendengar pendapat Anda dan jangan lupa untuk membagikan tip ini kepada teman-teman Anda.