Sabtu, 08 Maret 2014

Menggabung teks banyak baris berkriteria


Muatan :

  • Kilasan tentang menggabung teks dari banyak baris
  • Konsep tentang menggabung teks dari banyak baris
  • Contoh kasus data terurut berdasar kriteria penggabungan teks
  • Contoh kasus data tidak terurut


Kilasan

Terkadang, sebuah layout output menghendaki untuk menampilkan seluruh nama-nama item dalam sebuah grup data sebagai sebuah teks. Sedangkan sumber datanya berupa sebuah tabel berorientasi vertikal. Misalnya seperti menampilkan nama project disebuah cell, kemudian di cell sebelah kanannya berisi seluruh nama personel yang mengerjakan project tersebut. Padahal sumber data yang ada di sheet lain berupa daftar nama-nama personel beserta nama-nama project yang sedang dikerjakan setiap personel tersebut.

Pembahasan kali ini adalah tentang penyederhanaan kasus-kasus yang seperti ini.

Minggu, 15 September 2013

Menyingkat IF yang puanjuaaaang buanget


Muatan :

  • Kilasan tentang formula pengkondisian
  • Konsep tentang pengkondisian berdasar rentang nilai
  • Rentang nilai berdasar satu kriteria
  • Rentang nilai berdasar banyak kriteria


Kilasan

Sering kali ditemui dalam pekerjaan sehari-hari yang membutuhkan suatu komputasi berdasar suatu interval nilai tertentu untuk mendapatkan nilai output yang diinginkan. Misalnya seperti penentuan persentase bonus berdasar total nilai penjualan yang dicapai seseorang. Maka akan muncul banyak kondisi, seperti jika nilai sales lebih dari sama dengan sekian akan mendapat bonus sekian. Jika lebih dari atau sama dengan nilai tertentu tapi belum mencapai nilai batasan tertentu akan mendapat bonus tertentu, dan seterusnya. Umumnya, penyelesaian kasus seperti ini menggunakan fungsi IF bersarang atau IF di dalam IF sesuai banyaknya kondisi yang harus diperiksa. Hasilnya, formula akan menjadi sangat panjang ketika rentang-rentang nilai pembatas setiap persen bonus yang harus didapatkan relatif rapat.

Masalah akan menjadi lebih terasa menyebalkan ketika penentuan bonus tersebut bukan berdasar total nilai penjualan yang dicapai saja, tetapi juga harus mempertimbangkan produk yang terjual. Setiap produk memiliki rentang nilai penjualan yang berbeda untuk setiap nilai persentase bonusnya. Andaikan ada 5 produk saja yang dipasarkan dan masing-masing produk memiliki 4 rentang nilai penjualan per produk untuk mendapatkan nilai persentase bonus, maka sudah relatif sulit membayangkan bentuk formula IF yang harus dibuat. Bagaimana jika ternyata suatu saat nanti akan mempertimbangkan faktor cabang perusahaan ? Bisa jadi, setiap cabang akan memiliki rentang nilai penjualan per produk yang berbeda karena kelakuan pasar terhadap setiap produk di setiap cabang akan berbeda-beda, sedangkan penentuan bonus harus diusahakan memiliki keadilan antara daerah cabang yang respon pasarnya baik dengan daerah cabang yang relatif sulit untuk menguasai pasar.

Pembahasan kali ini adalah tentang penyederhanaan kasus-kasus yang seperti ini.

Senin, 09 September 2013

Pivot Table (3 - Pivot field)


Muatan :

  • Kilasan tentang pivot field
  • Menata field pada Row Labels atau Column Labels
  • Menata field pada Values (value field)


Kilasan

Pivot field adalah kolom data source yang bisa ditampilkan atau sedang ditampilkan dalam pivot table. Pemanfaatan pivot table pada umumnya adalah dengan menata setiap field yang dibutuhkan untuk membentuk suatu summary report tertentu. Penataan tersebut antara lain berupa pengaturan (setting), filter, penyusunan grup, pembuatan field baru melalui suatu formulasi tertentu (calculated item), pembuatan field nilai ukur (calculated field), dan sebagainya.

Kamis, 02 Mei 2013

Pivot Table (2 - Menata Data Source)


Muatan :

  • Kilasan tentang Pivot Table Data Source
  • Contoh data untuk penataan data source
  • Membuat nama range dinamis untuk data source
  • Mendetilkan data source


Kilasan

Pada pembahasan lalu, yaitu Pivot Table (1 - Kenalan) sudah dibahas tentang cara membuat pivot table beserta yang dimulai dari mendefinisikan data source untuk pivot table. Informasi yang dapat diambil dari suatu data menggunakan pivot table sangat tergantung pada data source pivot table. Bentuk data source yang sederhana dan berkaidah database adalah bentuk yang relatif sangat sesuai untuk dijadikan data source, meskipun pada hakikatnya data source pivot table adalah sebuah tabel fakta.

Rabu, 24 April 2013

Pivot Table (1 - Kenalan)


Muatan :

  • Kilasan tentang pivot table
  • Konsep pivot table
  • Data untuk contoh kasus
  • Sumber data untuk pivot table (Pivot Table Data Source)
  • Membuat pivot table
  • Menata pivot table


Kilasan

Pivot table di dalam Excel adalah sebuah fitur untuk membentuk suatu summary data sederhana. Fitur ini sangat ringan dan bisa relatif dinamis untuk membentuk berbagai macam summary data. Diluar kekurangan yang dimilikinya, tetap saja fitur ini bisa menjadi alat yang sangat bermanfaat, terutama untuk membentuk summary data sederhana. Tingkat kerumitan yang bisa dilakukan oleh fitur ini tetaplah sesuatu yang bersifat sederhana.

Coretan kali ini mencoba untuk membahas pivot table dari nol dan bukan sekedar untuk bisa membuat pivot table. Coretan ini berusaha untuk menjabarkan hal-hal dasar agar lebih mudah dalam mengembangkan kreativitas dalam menggunakan fitur pivot table ini. Coretan ini menyertakan file yang bisa diunduh, sehingga bisa dipraktekkan langsung.

Rabu, 26 September 2012

Formula Pendistribusian Persediaan


Muatan :

  • Kilasan tentang formula distribusi
  • Konsep pendistribusian persediaan dengan formula
  • Cerita tentang sebuah kasus
  • Penyusunan formula


Kilasan

Salah satu kasus yang berkaitan dengan permintaan dan persediaan adalah mengkomputasi pendistribusian persediaan sesuai dengan permintaan yang ada. Hasil akhir yang diharapkan adalah diketahuinya dengan jelas item-item permintaan yang masih belum terpenuhi karena keterbatasan persediaan. Jumlah kekurangan persediaan setiap item permintaan yang belum terpenuhi bisa menjadi informasi penting dalam tahap pengadaan persediaan berikutnya. Tentu masih banyak informasi lain yang bisa didapatkan dari terpetakannya permintaan dan persediaan tersebut.

Jumat, 11 Mei 2012

Pemeringkatan data (Ranking)


Muatan :

  • Kilasan tentang pemeringkatan data
  • Macam-macam pemeringkatan data
  • Formula berdasar macam-macam pemeringkatan data


Kilasan

Pemeringkatan data adalah proses untuk mengurutkan data berdasar suatu kriteria tertentu, yang diikuti proses pemberian nomor peringkat data. Peringkat data adalah hubungan sebuah data dengan data yang lain yang sama-sama memenuhi suatu kriteria.
Misal ada sebuah data berdasar suatu kriteria bernilai {95,63,58,63}. Pemeringkatan data secara menaik (ascending) akan menghasilkan {(58,1),(63,2),(63,2),(95,3)}, yang artinya bahwa setiap data diurutkan secara ascending dan diberi peringkat data. Peringkat data 95 adalah 3 yang artinya nilai 95 adalah terendah (diurutkan ascending) ke-3 dari 4 record data.
Penetapan peringkat data untuk data yang bernilai sama sangat mungkin berbeda antar kasus. Misalnya setiap data harus mendapatkan peringkat data yang unik. Pembahasan kali ini akan difokuskan pada beberapa macam pemeringkatan data yang sering digunakan dengan menggunakan formula.

Kamis, 10 Mei 2012

Formula peringkat data teks (Rank data text)


Muatan :

  • Kilasan tentang pemeringkatan data teks
  • Contoh kasus
  • Konversi data teks menjadi data numerik
  • Penyusunan Composite Key
  • Kalkulasi rank


Kilasan

Data hasil penilaian secara kualitatif adalah sebuah contoh data teks yang sering ditemui sebagai data yang akan dicari peringkatnya. Banyak juga ditemui adanya skala prioritas kode tertentu sebagai kriteria pemeringkatan. Padahal banyak sekali kode yang tersusun sebagai data bertipe teks.

Fungsi rank, yang hanya bekerja pada data bertipe numerik saja, tidak dapat digunakan untuk mengkomputasi peringkat data yang bertipe teks. Umumnya dibutuhkan sebuah proses untuk mengkonversi data teks, yang menjadi kriteria pemeringkatan tersebut, menjadi suatu data bertipe numerik. Hal ini tentu saja membutuhkan suatu tabel referensi yang memetakan kesetaraan nilai antara data teks dengan suatu data numerik tertentu.

Rabu, 09 Mei 2012

Formula peringkat data (Rank multi kriteria 03)


Muatan :

  • Kilasan tentang pengaruh karakteristik data terhadap composite key
  • Contoh kasus
  • Penyusunan Composite Key
  • Rank dengan multi kriteria


Kilasan

Pada coretan sebelumnya, yaitu Formula peringkat data (multi kriteria 02) telah digambarkan tentang penyusunan composite key yang memerlukan beberapa tahap proses. Telah ditekankan pula bahwa karakteristik data setiap kriteria pemeringkatan akan mempengaruhi bentuk composite key. Yang tampak jelas pada coretan tersebut adalah kriteria pemeringkatan yang berisi nilai dengan jumlah digit yang bervariasi. Pembahasan kali ini akan membahas contoh varian nilai data kriteria pemeringkatan yang memiliki jumlah digit yang tetap.

Senin, 07 Mei 2012

Formula peringkat data (Rank multi kriteria 02)


Muatan :

  • Kilasan tentang penyusunan composite key
  • Contoh kasus
  • Penyusunan Composite Key
  • Rank dengan multi kriteria


Kilasan

Pada coretan sebelumnya, yaitu Formula peringkat data (multi kriteria 01) telah diketahui bahwa kebutuhan terhadap composite key bergantung pada jumlah kriteria pemeringkatan. Sedangkan penyusun composite key tersebut sangat spesifik tergantung karakteristik setiap kolom kriteria pemeringkatan. Penyusunan composite key dengan susunan aaa ... zzz yang bertipe numerik terbatasi oleh jumlah digit presisi maksimal sebuah cell yang sebanyak 15 digit. Dengan begitu, akan terbuka kemungkinan terjadinya beberapa kolom composite key agar bisa menghasilkan sebuah kolom composite akhir yang benar-benar mewakili seluruh kriteria pemeringkatan.

Formula peringkat data (Rank multi kriteria 01)


Muatan :

  • Kilasan tentang kriteria pemeringkatan
  • Contoh kasus
  • Penyusunan Composite Key
  • Rank dengan multi kriteria


Kilasan

Fungsi rank mencari sebuah nilai dalam satu kumpulan data. Kriteria pemeringkatan bisa jadi terdiri dari banyak kolom dengan cara pengurutan per kriteria yang juga berbeda-beda. Untuk itu, dibutuhkan suatu proses untuk menyusun sebuah nilai yang telah mewakili seluruh kriteria pemeringkatan. Nilai ini sering disebut dengan composite key. Fungsi rank pada suatu data yang pemeringkatannya berdasar banyak kriteria bisa dikatakan sebagai sebuah proses mencari sebuah nilai composite key dalam sekumpulan nilai-nilai composite key.

Minggu, 06 Mei 2012

Formula peringkat data (Rank satu kriteria)


Muatan :

  • Kilasan tentang mencari peringkat sebuah data
  • Contoh kasus
  • Rank dengan satu kriteria


Kilasan

Peringkat sebuah data relatif terhadap data yang lain dapat diperoleh dengan menggunakan fungsi Rank. Syntax fungsi Rank adalah :
    =RANK( nilai_yang_dicari_peringkatnya , referensi_data_semesta_peringkat , [cara_mengurutkan] )
  • nilai_yang_dicari_peringkatnya
      Sebuah nilai data yang akan dicari nilai peringkatnya pada sekumpulan data.
  • range_data_semesta_peringkat
      Sekumpulan data yang menjadi area penyusunan pemeringkatan. Sekumpulan data ini dapat berupa range maupun array.
  • [cara_mengurutkan]
      opsi cara mengurutkan dalam menyusun pemeringkatan seluruh data. Sifatnya optional dengan nilai opsi yang ada adalah :
    • 0 -> untuk cara mengurutkan menurun (descending) [nilai default]
    • 1 -> untuk cara mengurutkan menaik (ascending)
  • Jika nilai_yang_dicari_peringkatnya tidak ditemukan dalam referensi_data_semesta_peringkat, maka akan menghasilkan error value.

Minggu, 03 Juli 2011

Koneksi data ke Text File 04 - Text file butuh perlakuan khusus

(4 dari 4)

Muatan :

  • Kilasan tentang data text file yang membutuhkan perlakuan khusus
  • Data text file dan susunan file schema.ini yang digunakan
  • Perlakuan khusus terhadap data text menggunakan query


Kilasan tentang data text file yang membutuhkan perlakuan khusus

Pada coretan pertama tentang koneksi data ke text file, telah dijelaskan sekilas mengenai data text file hasil suatu report sistem aplikasi. Hasil report tersebut menyertakan format bilangan yang dilengkapi pemisah ribuan. Penulisan data tanggal juga berbeda antara data kolom DueDate dengan kolom InvDate.

Coretan kali ini akan difokuskan pada salah satu cara menyusun hasil koneksi yang tidak dipengaruhi oleh locale setting komputer user. Koneksi memanfaatkan Microsoft Jet 4.0 OLE DB. Pada penggunaan Microsoft Text ODBC harus membuat DSN User dan koneksi melalui MS Query.

Koneksi data ke Text File 03 - Membuat koneksi data text file

(3 dari 4)

Muatan :

  • Kilasan tentang koneksi yang akan dilakukan
  • Koneksi memanfaatkan Microsoft Text ODBC Driver
  • Koneksi memanfaatkan Microsoft Jet 4.0 OLE DB


Kilasan tentang koneksi yang akan dilakukan

Pada koneksi yang akan dilakukan ini, data berupa text file yang memiliki kondisi ideal. Bilangan diformat general dan data tanggal dalam format YYYYMMDD. Proses persiapan berupa penyusunan schema.ini dan pembuatan DSN untuk koneksi dengan Microsoft Text ODBC telah dilakukan.

Koneksi data ke Text File 02 - Persiapan

(2 dari 4)

Muatan :

  • Kilasan tentang langkah koneksi data ke text file
  • Memanfaatkan file schema.ini sebagai katalog tabel
  • Persiapan untuk menggunakan Microsoft Text ODBC


Kilasan tentang langkah koneksi data ke text file

Penyusunan koneksi data ke text file melalui proses-proses :
  1. Mempersiapkan file schema.ini
    • File schema.ini bisa disusun menggunakan aplikasi text editor seperti NotePad.exe
    • Jika memanfaatkan Microsoft Text ODBC, maka file schema.ini bisa dibuat melalui form Define Text Format saat membuat User DSN
  2. Khusus koneksi yang memanfaatkan Microsoft Text ODBC, harus membuat User DSN jika belum ada dari daftar
  3. Membuat koneksi data ke text file

Koneksi data ke Text File 01 - Introduction

(1 dari 4)

Muatan :

  • Kilasan tentang data pada sebuah text file
  • Text File Data Provider
  • Contoh text file, schema.ini, dan hasil koneksi


Kilasan tentang data pada sebuah text file

Text file yang tersturktur dapat dianggap sebagai sebuah tabel data. Kumpulan text file dalam sebuah folder seakan-akan membentuk sebuah database. Maka folder yang berisi text file dapat dianggap sebagai sebuah database. Konsep inilah yang digunakan dalam memandang text file terstruktur sebagai sebuah tabel data.

Rabu, 29 Juni 2011

Koneksi data ke MySQL


Muatan :

  • Kilasan tentang koneksi data ke MySQL
  • Langkah-langkah membuat koneksi data ke MySQL


Kilasan tentang koneksi data ke MySQL

Koneksi data ke MySQL membutuhkan data provider bernama Connector/ODBC yang dapat diunduh dari website MySQL Downloads disini. Pilih OS platform yang sesuai dan install pada komputer.

Koneksi data ke MySQL dilakukan melalui MS Query menggunakan sebuah DSN. Jika belum memiliki DSN untuk koneksi ke MySQL, maka harus dilakukan pembuatan DSN untuk MySQL lebih dulu.

Get External Data - 04 - Koneksi melalui Data Connection Wizard

(4 dari 4)

Muatan :

  • Kilasan tentang koneksi data dengan connection wizard
  • Langkah-langkah koneksi data dengan connection wizard
  • Pengaturan output hasil koneksi


Kilasan

Data connection wizard adalah sebuah fitur untuk koneksi data secara DSN-less. DSN-less artinya koneksi dilakukan serta merta dengan mengatur connection string secara langsung setiao membentuk koneksi. DSN adalah akronim dari Data Source Name yang berisi informasi untuk sistem tentang cara membuat koneksi. Penggunaan DSN memudahkan user yang masih awam dengan koneksi data, karena tidak perlu memahami connection string. Connection string adalah kalimat informasi tentang jalur koneksi.

Senin, 25 April 2011

Get External Data - 03 - Koneksi melalui MS Query

(3 dari 4)

Muatan :

  • Kilasan tentang koneksi data memanfaatkan MS Query
  • Penjelasan tentang beberapa proses dari diagram alir


Kilasan

Koneksi data pada sebuah database atau file data yang dapat berlaku layaknya database seperti Excel, dapat dilakukan dengan bantuan aplikasi Microsoft Query (MS Query). MS Query adalah aplikasi yang mampu menjembatani komunikasi antara aplikasi Microsoft Office dengan database, melalui sebuah koneksi data. Query juga dapat disusun sesuai kebutuhan. Driver data provider jenis ODBC maupun OLEDB dapat digunakan oleh MS Query.

Minggu, 24 April 2011

Get External Data - 02 - Web Query

(2 dari 4)

Muatan :

  • Kilasan tentang Web Query
  • Persiapan membuat koneksi Web Query baru
  • Langkah-langkah membuat koneksi Web Query baru
  • Hal-hal yang berkaitan dengan hasil web query


Kilasan

Web Query adalah koneksi yang dilakukan terhadap tabel yang ada di-web. Diperlukan alamat url sumber dan bentuk tabel. Koneksi ke Internet adalah keharusan.

Get External Data - 01 - Introduction

(1 dari 4)

Muatan :

  • Kilasan tentang fitur Get External Data
  • Hal-hal yang perlu disiapkan untuk melakukan koneksi data
  • Jalur menu yang berkaitan dengan properti


Kilasan

Excel memiliki keterbatasan ruang data dan terkadang membutuhkan data dari aplikasi lain yang lebih fokus pada tata kelola data yang dibutuhkan. Misalnya dari file output aplikasi lain yang tidak berformat Excel. Setidaknya Excel mampu membuka dan membaca format file yang terdaftar saat open file. Beberapa format memerlukan proses parse melalui fitur Text To Columns, yang umumnya akan otomatis terbuka dan meminta user untuk melakukan setting yang diperlukan. Hal ini akan berulang untuk file lainnya, karena sifatnya adalah mengimpor data tanpa adanya suatu koneksi data.

Jumat, 22 April 2011

Formula filter data (filtering formula)


Muatan :

  • Kilasan tentang memfilter data dengan formula
  • Konsep filtering dengan formula
  • Cerita tentang sebuah kasus
  • Filtering satu kriteria
  • Memberi nilai default pada kriteria yang blank
  • Filtering banyak kriteria


Kilasan

Sebuah laporan umumnya adalah cuplikan data tertentu, yaitu berdasarkan suatu kriteria. Kadang kala, yang dibutuhkan adalah sebuah tabel hasil cuplikan yang memberi keleluasaan dalam memilih kriteria cuplikan data. Umumnya, bagian ini berada pada sisi penyusun kriteria.

Minggu, 03 April 2011

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) :



Coretan terkait :



Kamis, 31 Maret 2011

Formula sort data pada data teks


Muatan :

  • Kilasan tentang sort data
  • Perlakuan Excel terhadap karakter
  • Prinsip sort dengan formula
  • Contoh kasus disertai per langkah proses kalkulasi formula di sebuah cell


Kilasan

Penyusunan laporan sering berbentuk data terurut (sorted), baik menaik (ascending) maupun menurun (descending). Pada laporan yang bersumber dari data terurutkan, tentu akan menghasilkan data hasil yang terurutkan juga. Permasalahan bisa timbul ketika kriteria pengurutan data hasil di laporan bukan lagi seperti kriteria pengurutan pada data sumber. Misalnya laporan yang berupa summary berisi agregat dari data sumber akan diurutkan secara menurun berdasar nilai deviasi dua kolom laporan. Sedangkan nilai deviasi adalah hasil suatu kalkulasi terhadap data sumber.

Selasa, 29 Maret 2011

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.

Kamis, 24 Maret 2011

LookUp banyak kriteria


Muatan :


  • Sekilas tentang beberapa komponen utama dalam lookup
  • Lookup banyak kriteria secara exact
  • Lookup banyak kriteria dengan referensi berupa interval kelas (grup)


Kilasan

Kegiatan lookup memiliki 4 komponen utama, yaitu :
  1. Nilai yang akan di-lookup (lookup_value)
  2. Data/referensi pencarian nilai yang di-lookup (lookup_array)
  3. Cara pencariannya (sama persis ~ exact ~ atau pendekatan ~ approximate ~ )
  4. Data/referensi hasil output (result_array)
Susunan nomor 2 dan nomor 3 akan mempengaruhi cara penyusunan formula.

Senin, 21 Maret 2011

LookUp ke kiri atau ke kanan untuk satu kriteria


Muatan :


  • Sekilas tentang kegiatan lookup dan beberapa fungsi yang dapat digunakan
  • Lookup secara exact (sama persis antara nilai lookup dengan referensi)
  • Lookup secara approximate pada referensi yang berupa interval-interval kelas atau kelompok


Kilasan

Kegiatan me-lookup data sering dilakukan. Excel menyediakan grup fungsi LookUp & Reference. Fungsi yang sering digunakan antara lain VLookUp atau HLookUp, LookUp, kombinasi Offset dengan Match, kombinasi Index dengan Match. Masih banyak kombinasi beberapa fungsi yang bisa digunakan dalam kegiatan lookup, tetapi pembahasan akan dibatasi pada fundamental kegiatan lookup itu sendiri, sehingga dapat menyusun kombinasi beberapa fungsi untuk kegiatan lookup.

Minggu, 20 Maret 2011

Array Formulas. Kenalan yuuk !


Muatan :


  • Sekilas tentang array formula, cara membuat, input dan output yang dibutuhkan, cara penulisan ke cell(s)
  • Kondisi-kondisi umum yang membutuhkan array formula
  • Contoh penggunaan array formula beserta step-by-step proses kalkulasi dalam sebuah array formula


Kilasan

Array formula adalah formula yang bekerja dengan input berupa array dan mengkalkulasi setiap item array. Array adalah kumpulan item. Array formula ditandai dengan adanya kurung kurawal yang melingkupi formula.
Contohnya {=Average(IF( A2:A5 = "Jakarta" , B2:B5 ))}

Cara membuatnya adalah dengan menulis formula seperti biasa dan diakhiri dengan menekan 3 (tiga) tombol bersamaan, yaitu tombol CTRL SHIFT ENTER (CSE) sebagai pengganti penekanan tombol ENTER pada umumnya penulisan formula.

Senin, 07 Februari 2011

Menghilangkan Duplikasi Data. Mungkinkah Case Sensitive ?


Muatan :


  • Kilasan tentang duplikasi dan fitur Excel
  • Fitur Advanced Filter
  • Fitur Remove Duplicates
  • Dampak spasi berlebih di sebelum, di sesudah, atau di dalam teks
  • Menghilangkan duplikasi data dengan mempertimbangkan case sensitive


Kilasan

Umumnya suatu data tidak dikehendaki adanya duplikasi record yang tidak sesuai dengan kenyataan. Contohnya adalah data nasabah. Tidak tepat jika terdapat lebih dari satu record yang dimiliki seorang nasabah dengan seluruh nilai kolomnya sama. Sedangkan untuk transaksi yang dilakukan nasabah dengan mengabaikan waktu transaksi, sangat mungkin terjadi lebih dari satu transaksi dengan nilai transaksi yang sama. Untuk jenis seperti data transaksi tersebut, duplikasi dihilangkan disertai dengan penjumlahan nilai transaksi. Data transaksi seperti ini bisa saja disimpan apa adanya. Pada data referensi seperti data nasabah, proses entry data memungkinkan terjadinya duplikasi, tetapi penyimpanan data tetaplah dituntut untuk unique records.

Minggu, 06 Februari 2011

Susun data sebagai tabel yang baik. Kenapa ?


Muatan :


  • Kilasan tentang tabel
  • Bentuk tabel sederhana
  • Bentuk tabel turunan


Kilasan

Penyusunan data yang baik akan memudahkan proses pengolahan. Sering kali dijumpai sebuah data disusun dalam sebuah tabel yang sekaligus menjadi tabel input maupun tabel output. Hal ini membuat susunan tabel menjadi tidak sederhana lagi, sehingga proses pengolahan data menjadi terhambat akibat kompleksnya susunan tersebut.