Cara Menghitung Nilai Unik di Excel: Hal yang Perlu Anda Ketahui

Dalam panduan ini, saya akan menunjukkan cara menghitung nilai unik di Excel. Saya juga akan menunjukkan kasus khusus, seperti cara menangani peka huruf besar/kecil (case-sensitive), atau cara menghitung nilai unik berdasarkan satu atau lebih kondisi.

Untuk terus membangun keterampilan Excel Anda, daftar di jalur keterampilan Excel Fundamentals kami untuk menjadi ahli.

Nilai Unik vs. Nilai Distinct di Excel

Keduanya kadang disamakan, tetapi nilai unik dan nilai distinct bukanlah hal yang sama.

• Nilai distinct adalah nilai yang berbeda dalam sebuah dataset, dengan duplikasi dihapus. Misalnya, dalam daftar A, A, B, C, C, D, nilai distinct-nya adalah A, B, C, D. 
• Nilai unik, di sisi lain, adalah nilai yang hanya muncul sekali dalam dataset. Menggunakan contoh yang sama, nilai uniknya adalah B dan D (karena A dan C muncul lebih dari sekali).

Pada paruh pertama artikel, saya akan fokus pada berbagai cara untuk menghitung nilai unik di Excel. Namun, jika Anda tiba di sini tanpa sengaja dan sebenarnya ingin menghitung nilai distinct, bagian selanjutnya juga membahasnya.

Dua Cara Menghitung Nilai Unik di Excel

Ada dua cara umum untuk menghitung nilai unik di Excel.

Cara menghitung nilai unik di Excel menggunakan fungsi UNIQUE()

Cara termudah untuk menghitung nilai unik adalah dengan menggunakan fungsi UNIQUE() dan fungsi COUNTA(). Fungsi UNIQUE() mengekstrak semua nilai unik dari sebuah rentang, dan COUNTA() menghitung berapa banyak nilai unik yang ada. 

COUNTA(UNIQUE(range, false, true))Didukung oleh

Sebagai contoh, saya menerapkan rumus berikut ke dataset kecil, dan rumus tersebut memberi saya nilai unik.

=COUNTA(UNIQUE(A2:A8, false, true))Didukung oleh

Jika Anda menggunakan sintaks ini, =COUNTA(UNIQUE(A2:A8)), yang tidak memiliki parameter ketiga yang kita setel sebagai TRUE, maka hasilnya adalah hitungan distinct, bukan unik. Menyetel TRUE pada argumen ketiga memberi tahu fungsi untuk mengembalikan hitungan unik. Keduanya berada dalam fungsi UNIQUE(), sehingga mungkin agak membingungkan pada awalnya.

Cara menghitung nilai unik di Excel dengan SUM() dan COUNTIF()

Jika Anda kurang yakin dengan seluk-beluk fungsi UNIQUE(), atau jika Anda tidak memiliki Excel 365, ketahuilah bahwa kita juga dapat menggabungkan SUM() dengan IF() dan COUNTIF() untuk menghitung nilai unik.

=SUM(IF(COUNTIF(range, range)=1,1,0))Didukung oleh

Sebagai contoh, saya memiliki data pada rentang A2:A8 dan ingin menghitung nilai uniknya, jadi saya menulis rumus berikut:

SUM(IF(COUNTIF(A2:A8, A2:A8)=1,1,0))Didukung oleh

Di sini COUNTIF() menelusuri daftar dan memeriksa berapa kali setiap nama muncul. Jika sebuah nama hanya muncul sekali, itu dihitung sebagai 1. Jika muncul lebih dari sekali, akan mendapat angka lebih tinggi. Lalu, IF() memfilter hasil tersebut untuk mempertahankan angka 1 apa adanya, tetapi mengubah yang lain menjadi 0. Terakhir, SUM() menjumlahkan semua angka 1 dan memberi kita total jumlah nilai unik.

Cara Menghitung Tipe Data Unik di Excel

Terkadang, dataset kita berisi tipe data campuran dan kita harus menganalisisnya secara terpisah. Ini mungkin tampak menantang pada awalnya, tetapi bisa dilakukan di Excel. Mari kita lihat dua metode — satu untuk menghitung nilai teks unik dan satu lagi untuk angka unik.

Cara menghitung nilai teks unik di Excel

Jika Anda ingin menghitung nilai teks unik, gabungkan fungsi ISTEXT(), COUNTIF(), dan SUM(). ISTEXT() memeriksa apakah sebuah nilai berupa teks, sementara COUNTIF() menghitung berapa kali setiap nilai muncul, dan SUM() menjumlahkan entri teks yang unik. 

Saya menerapkan rumus berikut pada data contoh, dan langsung terlihat berapa banyak nilai teks unik yang ada.

=SUM(IF(ISTEXT(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))Didukung oleh

Cara menghitung nilai numerik unik di Excel

Jika Anda ingin menghitung angka unik alih-alih teks, gunakan rumus yang sama tetapi ganti ISTEXT() dengan ISNUMBER().

ISNUMBER() hanya akan mempertimbangkan nilai numerik, sementara COUNTIF() dan SUM() menangani keunikannya. Saya menyesuaikan rumus di atas pada data yang sama, dan sekarang hanya menampilkan jumlah nilai numerik unik:

=SUM(IF(ISNUMBER(A2:A9)*COUNTIF(A2:A9,A2:A9)=1,1,0))Didukung oleh

Cara Menghitung Nilai Unik dengan Kondisi di Excel

Sekarang kita telah membahas metode dasar untuk menghitung nilai unik, mari jelajahi beberapa teknik lanjutan.

Cara menghitung nilai unik berdasarkan kondisi

Untuk menghitung nilai unik berdasarkan kondisi tertentu, saya menggunakan sintaks ini:

=IFERROR(ROWS(UNIQUE(FILTER(range, criteria_range=criteria))), 0)Didukung oleh

Dalam rumus ini, FILTER() memfilter rentang berdasarkan kriteria tertentu, UNIQUE() menghapus duplikasi, ROWS() menghitung hasilnya, dan IFERROR() mencegah kesalahan dengan mengembalikan Tidak Ditemukan jika tidak ada kecocokan.

Misalnya, saya memiliki daftar Karyawan dan Departemen mereka, dan saya perlu menghitung berapa banyak karyawan unik yang bekerja di departemen tertentu. Saya akan menggunakan rumus berikut:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A20, B2:B20=F1))), "Not Found")Didukung oleh

Di sini, A2:A10 mewakili rentang nama karyawan yang ingin saya filter dan B2:B10=F1 memeriksa karyawan mana yang termasuk ke departemen yang ditulis di F1.

Cara menghitung baris unik berdasarkan beberapa kolom

Jika saya ingin menghitung karyawan unik di departemen tertentu dengan gaji kurang dari $50.000, saya menggunakan rumus ini:

=IFERROR(ROWS(UNIQUE(FILTER(A2:A10, (B2:B10=F1) * (C2:C10>F2)))), "Not Found")Didukung oleh

Di sini, A2:A10 mewakili rentang nama karyawan, B2:B10=F1 memeriksa karyawan mana yang termasuk ke departemen yang ditulis di F1, dan C2:C10>F2 memeriksa apakah gaji lebih kecil dari jumlah yang diberikan di F2.

Cara menangani hitungan unik yang peka huruf besar/kecil

Secara bawaan, Excel peka huruf besar/kecil. Misalnya, Apple dan APPLE dianggap berbeda. Untuk menemukan kasus seperti ini, saya membuat kolom bantu dengan rumus berikut:

=IF(SUM((--EXACT($A$2:$A$11,A2)))=1,"Unique","Duplicate")Didukung oleh

Dan sekarang, untuk mendapatkan jumlah nilai unik, saya menggunakan fungsi COUNTIF() seperti ini:

=COUNTIF(B2:B11,"Unique")Didukung oleh

Cara Menghitung Nilai Distinct sebagai Gantinya

Sekarang, sebagai bagian terakhir, jika Anda sebenarnya ingin menghitung nilai distinct, saya akan menunjukkan beberapa metode untuk menghitung nilai distinct. Namun terlebih dahulu, berikut tabel agar Anda dapat melihat perbedaannya:

Cara menghitung nilai distinct di Excel menggunakan COUNTIF() dan SUM()

Untuk menghitung nilai distinct di Excel, Anda dapat menggabungkan fungsi COUNTIF() dan SUM(). Fungsi COUNTIF() memeriksa berapa kali setiap nilai muncul dalam rentang tertentu. Lalu, SUM() menjumlahkan nilai yang dikembalikan oleh COUNTIF(), memberikan jumlah total entri distinct. 

=SUM(1/COUNTIF(range, range))Didukung oleh

Misalnya, saya ingin mencari nilai distinct dalam rentang A2:A8. Saya memasukkan rumus berikut:

=SUM(1/COUNTIF(A2:A8, A2:A8))Didukung oleh

Di sini, fungsi COUNTIF() memeriksa berapa kali setiap nilai muncul dalam daftar.

1/COUNTIF(A2:A8, A2:A8) membagi 1 dengan jumlah kemunculan setiap nilai. Misalnya, jika sebuah angka muncul dua kali, setiap kemunculannya menjadi 0,5 (1/2). Jika muncul tiga kali, akan menjadi 0,33 (1/3). 

Karena kita tidak menginginkan pecahan ini, kita dapat menggunakan SUM() atau SUMPRODUCT() untuk menjumlahkan semua nilai. Pecahan milik duplikasi akan berjumlah 1, dan nilai distinct tetap 1 karena 1/1 = 1:

=SUMPRODUCT(1/COUNTIF(A2:A8, A2:A8))Didukung oleh

Cara menghitung nilai distinct di Excel menggunakan PivotTable

Excel 2013 dan Excel 2016 menyertakan fitur bawaan untuk menghitung nilai distinct dalam PivotTable. Untuk menggunakannya, terlebih dahulu Anda harus membuat PivotTable dengan memilih data: Buka Insert dan pilih PivotTable. Kotak dialog akan muncul — dari kotak dialog ini, pilih Existing Worksheet dan centang kotak Add this data to the Data Model.

Selanjutnya, seret kolom yang diinginkan ke bidang Values. Dalam kasus saya, saya menyeret kolom Fruits karena saya ingin menghitung nilai distinct-nya. Lalu, klik Value Field Settings dari menu tarik-turun. Jendela pop-up akan muncul — dari sana, pilih Distinct Count. Anda bahkan dapat memberi nama kustom untuk kolom ini, tetapi itu opsional.

Cara menghitung nilai distinct di Excel menggunakan filter

Jika Anda tidak suka bekerja dengan rumus, gunakan filter lanjutan sebagai gantinya. 

Pada contoh ini, saya memiliki dataset dan ingin menemukan nilai distinct menggunakan filter lanjutan. Untuk itu, saya memilih rentang tempat saya ingin mencari nilai tertentu. Lalu, saya pergi ke tab Data dan klik Advanced. 

Sekarang, di kotak dialog Advanced Filter, saya memilih Copy to another location, dan di bidang Copy to, saya memasukkan sel tujuan tempat saya ingin daftar tersebut muncul. Lalu centang kotak Unique Records Only dan klik OK.

Setelah saya memiliki daftar nilai unik, saya kemudian menghitung jumlah nilai unik ini dengan menggunakan fungsi ROWS():

=ROWS(D2:D5)Didukung oleh

Meskipun opsi pada gambar di bawah berbunyi Unique records only, sebenarnya opsi tersebut mengekstrak nilai distinct. 

Cara menghitung nilai distinct di Excel menggunakan makro VBA

Jika Anda ingin menghitung nilai distinct secara berulang, gunakan VBA (Visual Basic for Applications), di mana kita menulis kode sekali, dan itu mengotomatiskan seluruh proses setiap kali kita memanggil fungsinya dengan namanya.

Begini cara Anda menggunakannya: tekan Alt + F11 dan buka opsi Module di bawah tab Insert. Modul baru akan muncul, dan Anda dapat menempelkan kode Anda di sini. 

Ini adalah kode yang saya buat untuk menghitung nilai distinct:

Function CountUnique(rng As Range) As Integer
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    
    Dim cell As Range
    For Each cell In rng
        If Not dict.exists(cell.Value) And cell.Value <> "" 

Then,
            dict.Add cell.Value, Nothing
        End If
    Next cell
    
    CountUnique = dict.Count
End FunctionDidukung oleh

Lalu, tekan Ctrl + S untuk menyimpannya dan ALt + Q untuk menutup editor VBA. Sekarang, Anda dapat memanggil fungsi khusus ini di mana saja di lembar dan menentukan rentang dari mana akan mencari nilai distinct. Dalam kasus saya, namanya CountUnique() sehingga setiap kali saya memanggil fungsi ini dengan rentang tertentu, fungsi ini memberikan nilai hitungan:

=CountUnique(A2:A8)Didukung oleh

Penutup

Saya telah membahas rumus utama, PivotTable, dan makro VBA untuk menghitung nilai unik dan distinct. Setiap metode memiliki kelebihan, tetapi Anda sebaiknya memilih yang sesuai dengan kebutuhan Anda dan didukung oleh versi Excel Anda.

Jika Anda ingin mempertajam keterampilan Excel, lihat jalur keterampilan Excel Fundamentals dan kursus Data Preparation in Excel.