Formula penyusun data unique dan penghitung jumlah cacah data unique

---

Muatan :

• Kilasan tentang data unique
• Memahami langkah penyusunan data unique
• Beberapa fungsi yang dapat digunakan dalam penyusunan data unique
• Contoh penerapannya pada sebuah kasus

---

Kilasan

Data unik (unique) adalah data yang tidak mengalami duplikasi berdasar suatu kriteria, seperti nama-nama produk pada sebuah tabel referensi. Data unik tidak selalu hanya pada tabel referensi. Banyak laporan yang menyuguhkan nilai agregat. Tentu saja sifat data laporan ini adalah unik. Ada laporan yang berupa summary yang berisi seluruh item tabel referensi yang telah bersifat unik. Ada pula yang berupa summary data penggalan data dalam kurun waktu tertentu.



Umumnya data yang memiliki dimensi waktu sangat besar kemungkinannya tidak bersifat unik disuatu kolom, meskipun sebagai sebuah record adalah unik. Misalnya, seorang nasabah sebuah bank menerima kiriman penghasilan dari tempatnya bekerja secara rutin sebulan sekali. Data ini dicatat oleh pihak bank. Data bank tentang nasabah ini pada kurun waktu triwulan terakhir akan berisi 3 baris data penerimaan. Laporan penerimaan tentang total penerimaan nasabah tersebut adalah 1 baris. Jadi ada sebuah proses penyusunan data berdasar nama nasabah yang bersifat unik dari sebuah sumber data yang tidak unik. Besar kemungkinan untuk terjadi, dalam suatu rentang waktu, tidak seluruh nasabah memiliki jumlah baris data sumber yang sama.

---

Beberapa fungsi yang dapat digunakan

Garis besar proses penyusunan data unik adalah mengelompokkan data sumber yang belum masuk ke dalam data hasil berdasar suatu kriteria. Frase yang digaris bawahi memberi gambaran bahwa terjadi proses pemeriksaan antara data hasil dengan data sumber. Jika sebuah data sumber belum ada di dalam data hasil, maka sebuah data sumber tersebut akan diambil untuk dimasukkan ke dalam data hasil. Proses pemeriksaan ini dapat dilakukan dengan menggunakan fungsi CountIF untuk mengkalkulasi jumlah baris hasil yang sama dengan setiap item kriteria keunikan di dalam data sumber.

Setelah diketahui jumlah masing-masing item kriteria data sumber di dalam data hasil, maka perlu didapatkan sebuah item data sumber yang ditemukan pertama kali, yang belum terdapat pada data hasil. Fungsi Match dan fungsi Index dapat digunakan. Fungsi Match adalah penentu posisi baris data sumber yang akan diambil sebagai sebuah item yang menambah jumlah data hasil. Fungsi Index dengan dibantu hasil fungsi Match bertugas sebagai pengambil data.

Proses untuk mendapatkan sebuah item pada data hasil selalu melalui kalkulasi terhadap seluruh item data sumber dan data hasil. Array formula dibutuhkan untuk proses penyusunan data hasil.

---

Penerapan

Gambar berikut ini adalah data nomor rekening nasabah (kolom A) dan contoh penerapannya.

Masalah 1 :

Permintaan jumlah nomor rekening yang melakukan kegiatan transaksi perbankan minggu ini

Setiap rekening bisa melakukan banyak transaksi. Kasus seperti ini muncul ketika tidak dibutuhkan detil data summary uniknya. Andai diminta bersama data summary uniknya, maka masalah ini dapat diselesaikan dengan formula
=COUNTIF(E3:E11,"<>#N/A")

Masalah ini tidak mengharapkan atau tidak tersedia data summary unik. Penyelesaian dilakukan menggunakan array formula yang prinsipnya adalah 1/n * n = 1. Array formula tersebut adalah :
=SUM(1/COUNTIF(A$2:A$18,A$2:A$18))

Step 1 :
mengetahui jumlah record kolom kriteria pada data sumber yang memiliki kriteria yang sama dengan setiap record dari kolom kriteria pada data sumber.
Step 2 :
mengkalkulasi nilai satu per masing-masing hasil step 1.
Step 3 :
menjumlahkan seluruh hasil step 2.
Gambar berikut ini adalah proses per step yang terjadi di dalam cell hasil (E1) ketika mengkalkulasi array formula di atas.

Agar tidak perlu meng-entry sebagai array formula, gunakan fungsi SumProduct menggantikan fungsi Sum. Proses kerja di dalam SumProduct juga seperti penjelasan di atas. Formula solusi masalah ini tanpa entry sebagai array formula adalah :
=SUMPRODUCT(1/COUNTIF(A$2:A$18,A$2:A$18))

Jika ada data blank dan akan diabaikan, maka nilai 1 pada 1/n diganti dengan proses perbandingan panjang teks setiap item data sumber lebih dari 0. Formula akan berubah menjadi
• array formula :
  =SUM((LEN(A$2:A$18)>0)/COUNTIF(A$2:A$18,A$2:A$18))
• formula : (bukan array formula)
  =SUMPRODUCT((LEN(A$2:A$18)>0)/COUNTIF(A$2:A$18,A$2:A$18))

Masalah 2 :

Menyusun daftar nomor rekening yang melakukan kegiatan transaksi minggu ini

Array formula untuk masalah ini adalah :
=INDEX(A$2:A$18, MATCH(, COUNTIF(E$2:E2,A$2:A$18) ,0) )
Copy formula ke baris data hasil berikutnya. Hasil berupa error value #N/A menjadi tanda bahwa seluruh data sumber telah diperiksa dan dikeluarkan hasil yang sesuai kriteria.

Proses kalkulasi di dalam setiap cell hasil adalah :
Step 1 : COUNTIF(E$2:E2,A$2:A$18)
kalkulasi jumlah setiap item data sumber (setiap cell pada A$2:A$18) pada data hasil (range E$2:E2). Hasilnya adalah sebuah array dengan jumlah element sebanyak jumlah data di data sumber (A$2:A$18). Nilai hasil adalah 0 (belum ada di data hasil) atau 1 (sudah ada di data hasil sebanyak 1 item).
Step 2 : MATCH(,[hasil_step_1],0)
mencari posisi nilai 0 (belum ada di data hasil) yang pertama kali ditemukan, pada array hasil step 1, dengan tipe pencarian 0 (exact match). Hasilnya adalah posisi data pada data sumber yang pantas menjadi data hasil berikutnya.
Step 3 : INDEX(A$2:A$18,[hasil_step_2])
mengambil data hasil berikutnya dari data sumber pada posisi baris ke-hasil step 2.
Gambar berikut ini adalah proses per step pada data hasil ke-4 (pada cell E6).

Urutan hasil mengikuti urutan data sumber.

---

File(s) :

Unique List dan Count Unique Item pada sheet Unique

---

Coretan terkait :

• Array formula
• LookUp ke kiri atau ke kanan untuk satu kriteria
• Menghilangkan Duplikasi Data. Mungkinkah Case Sensitive ?

---