Formula penyusun data unique yang terurut (sorted)

---

Muatan :

• Kilasan tentang unique data dan sort data
• Contoh penyusunan unique data yang terurutkan
• Langkah-langkah kalkulasi pada salah satu cell hasil

---

Kilasan

Penyusunan laporan yang berisi nilai agregat umumnya memiliki kolom-kolom (minimal 1 kolom) yang menjadi dasar penyusunan agregat tersebut. Kolom-kolom ini menjadikan baris-baris data laporan bersifat unik.

Pada laporan jenis tertentu membutuhkan pengurutan berdasar prioritas tertentu, seperti laporan keterlambatan proses. Laporan ini membutuhkan pengurutan berdasar total waktu keterlambatan dari masing-masing proses. Total waktu keterlambatan menjadi prioritas penyusunan laporan yang terurut dari paling terlambat hingga mendekati terlambat.

Contoh lainnya adalah laporan pencapaian penjualan per produk yang berasal dari data sumber yang tersusun berdasar kegiatan transaksi penjualan (invoice). Laporan akan disusun berdasar produk yang volume penjualannya terurut dari volume tertinggi sampai volume terendah. Hasil laporan akan berupa data unik per produk mulai dari volume penjualan tertinggi sampai terendah.

Penyusunan keunikan hasil laporan membutuhkan kriteria yang menyertakan jumlah total volume penjualan sebagai prioritas pertama (kriteria pertama). Kriteria ini sering disebut sebagai composite key.

---

Penyusunan data unik terurut

Data pada gambar berikut (kolom A) adalah contoh composite key yang akan menjadi dasar penyusunan data hasil (laporan). Laporan bersifat unik dengan prioritas pengurutan hasil berdasar suatu urutan tertentu yang menjadi struktur dari composite key.

Kolom Asc (kolom C) adalah laporan hasil pengurutan data (kolom A) secara menaik (ascending), sedangkan pengurutan secara menurun (descending) ditampilkan pada kolom Desc. Keduanya ditampilkan sebagai bahan perbandingan. Umumnya yang dibutuhkan adalah salah satu dari keduanya saja. Tidak menutup kemungkinan, hasil dituntut memberi keluwesan untuk memilih secara menaik, menurun, atau apa adanya. Hal tersebut dapat dibuat dengan mengembangkan konsep yang ada pada bahasan ini.

Duplikasi data tampak pada kolom A, yang kemudian diolah menjadi keluaran bersifat unik dan terurut. Hasil pengolahan akan menghasilkan error value #N/A (baris 10 Excel) yang menandakan bahwa seluruh data telah diolah dan tidak ada yang dapat ditampilkan sebagai data keluaran. Hal ini memberi tanda batas baris yang harus berisi formula, sehingga proses Copy formula menjadi lebih mudah untuk disesuaikan dengan kebutuhan.

Cell yang diberi warna latar biru adalah cell yang akan dibahas lebih detil setiap proses kalkulasi array formula yang digunakan untuk penyusunan data unik terurut ini. Cell tersebut adalah cell C4 yang merupakan item ke-3 dari data hasil. Array formula yang digunakan pada cell ini adalah :
=INDEX($A$2:$A$18,
MATCH( SUM( COUNTIF($A$2:$A$18,C$1:C3) ),
COUNTIF($A$2:$A$18,"<" & $A$2:$A$18), 0 ) )

---

Garis besar proses pengolahan data unik terurut

Pokok pertama yang diperhatikan adalah proses pengurutan. Pengurutan data selalu berdasar proses perbandingan antar item data, dalam hal ini adalah data sumber. Secara sederhana, berdasar jumlah cacah seluruh item data sumber yang kurang dari item tertentu dari data sumber dapat menjadi dasar pengurutan.

Pada data yang bersifat unik, maka penjumlahan jumlah cacah seluruh item hasil (yang telah diperoleh di data hasil) dalam data sumber, menghasilkan nilai yang sama dengan jumlah cacah seluruh item data sumber dalam data sumber yang kurang dari item tertentu dari data sumber.

Item hasil yang baru adalah item pada data sumber yang memiliki jumlah cacah seluruh item data yang kurang dari item tertentu dari data sumber yang sama nilainya dengan penjumlahan jumlah cacah seluruh item hasil (yang telah diperoleh di data hasil) dalam data sumber. Fungsi COUNTIF dapat digunakan untuk 2 (dua) hal, yaitu :

1. Menyusun array jumlah cacah masing-masing item data sumber dalam data sumber. Susunan fungsi CountIF berupa :
   COUNTIF( data_sumber , "<" & data_sumber )
2. Menyusun array jumlah cacah masing-masing item hasil (yang telah diperoleh di data hasil) dalam data sumber. Susunan fungsi CountIF berupa :
   COUNTIF( data_sumber , data_hasil_yang_ada )
   dengan data_hasil_yang_ada adalah range yang dimulai dari header sampai data terakhir hasil yang telah diperoleh. Kemudian array hasil ini akan dijumlahkan setiap itemnya menjadi total jumlah dari array dengan susunan :
   SUM( COUNTIF( data_sumber , data_hasil_yang_ada ) )

Hasil fungsi SUM pada no 2 sebagai lookup_value. Hasil no 1 yang berupa array sebagai lookup_array. Keduanya menjadi input parameter fungsi Match, dengan tipe pencarian exact (match_type = 0). Hasil fungsi Match menunjukkan nomor index data item hasil berikutnya yang ada di dalam data sumber. Nilai data sumber diambil menggunakan fungsi Index.

---

Langkah-langkah kalkulasi pada cell C4

Array formula untuk cell C4 (data hasil ke-3) adalah
=INDEX($A$2:$A$18,
MATCH( SUM( COUNTIF($A$2:$A$18,C$1:C3) ),
COUNTIF($A$2:$A$18,"<" & $A$2:$A$18), 0 ) )
Proses kalkulasi array formula ini melalui 3 tahap utama, yaitu :

1. Penyusunan lookup_value (Step1 dan Step2)
   Tahap ini mengkalkulasi bagian :
   SUM( COUNTIF( $A$2:$A$18 , C$1:C3 ) )
   seperti gambar dibawah ini.
   
   Step1 menghasilkan sebuah array sejumlah hasil yang telah diperoleh ditambah 1 baris header. Pada item hasil ke-3 untuk cell C4 ini, telah dimiliki 2 item hasil, maka array terdiri dari 3 item.
   
   Step2 adalah proses menjumlahkan seluruh item array hasil Step1. Hasilnya adalah sebuah nilai. Nilai hasil Step2 ini adalah lookup_value yang akan digunakan pada tahap berikutnya.
2. Penyusunan lookup_array (Step3)
   Tahap ini mengkalkulasi bagian :
   COUNTIF( $A$2:$A$18 , "<" & $A$2:$A$18 ) dan hasilnya berupa array sebanyak item data sumber, dengan nilai berupa jumlah masing-masing item pada data sumber yang kurang dari (<) item masing-masing, seperti gambar dibawah ini.
   
   Jika data sumber tidak bersifat unik, maka akan ditemui bahwa untuk item yang sama memiliki nilai jumlah yang sama. Misal seperti item array hasil ke-1 dan ke-9 memiliki nilai 4. Item ke-1 adalah data sumber yang nilainya 'C'.
3. Mencari nomor index data yang akan menjadi item hasil dan mengambil nilai datanya (Step4 dan Step5)
   Tahap ini adalah proses pengambilan data menggunakan fungsi INDEX dan MATCH. Gambar berikut ini adalah detil dari proses tahap ini.
   
   Step4 adalah proses lookup mencari nomor index data lookup_array hasil Step3 yang urutannya adalah sesuai data sumber, berdasar lookup_value hasil Step2, dengan tipe pencarian yang exact (match_type = 0).
   
   Step5 adalah proses pengambilan nilai data sumber sesuai nomor index data hasil Step4.

---

Closing :

Step4 bisa dipisah dengan Step5 dalam 2 kolom, yaitu Step4 sebagai kolom bantu berisi nomor index data sumber yang akan menjadi item hasil berikutnya. Step5 sebagai kolom bantu berisi composite key yang digunakan oleh Step4. Dengan begitu, maka untuk menampilkan kolom-kolom lain dapat langsung merujuk pada hasil Step4 yang telah terpisah dari hasil Step5.

Secara tidak langsung, Step4 membutuhkan hasil Step5 hasil proses untuk item hasil sebelumnya, karena Step4 menggunakan hasil Step1, yang membutuhkan secara langsung hasil Step5 item hasil sebelumnya. Jadi hasil Step5 saat ini, akan digunakan oleh Step1 proses kalkulasi item hasil berikutnya (cell C5).

Perbedaan antara pengurutan menaik (Asc) dengan menurun (Desc) adalah pada penggunaan karakter yang berfungsi sebagai pembanding pada Step3, yaitu :

• karakter '<' untuk pengurutan menaik
• karakter '>' untuk pengurutan menurun

---

File(s) :

Sorted Unique List pada sheet Unique_Sorted

---

Coretan terkait :

• Array Formulas. Kenalan yuuk !
• LookUp ke kiri atau ke kanan untuk satu kriteria
• Formula penyusun data unique dan penghitung jumlah cacah data unique
• Formula sort data pada data teks

---