Penomoran Otomatis pada Auto Filter

Sering menggunakan Auto Filter? Jika jawabannya "IYA" mungkin tips ini dapat berguna.....

KASUS

Contoh pada tabel dibawah ini, kita ingin mem-filter data siswa yang mempunyai skor A saja...

Maka selanjutnya kita filter...

Sesudah kita filter, pengennya kita cetak, tetapi nomornya jadi tidak urut ya? Wah jadi pusing nih.....

Ada trik yang dapat digunakan untuk mengatasi permasalahan ini, dengan menggunakan Fungsi SUBTOTAL()

=SUBTOTAL(3;$C$2:C2)

Input-lah rumus ini pada Kolom No.

Coba perhatikan nomornya menjadi urut sesuai data yang ditampilkan

Penomoran menggunakan Fungsi SUBTOTAL() ini dapat berguna juga untuk penambahan baris data atau menghapus baris data, penomorannya akan secara otomatis mengikuti data yang ditampilkan. Dan anda tetap bisa mengembalikan data anda seperti semula dengan penomoran secara urut.

Mengambil Data Siswa Cumlaude

Kesempatan kali ini saya ingin memberikan penyelesaian kasus pengambilan data siswa Cumlaude.

 Tabel Data :

Tabel Penyelesaian :

 
Semoga Jelas dan Bermanfaat......

Download File Latihan

Mengatasi Pesan Error atau Error Trapping

Trik formula ini diperuntukkan bagi seseorang yang ingin datanya terlihat rapi pada saat belum ada data sama sekali yang di-input pada tabel yang berisi rumus.

Contoh pada Tabel di bawah ini :

Pada kolom penjualan terdapat rumus yang meng-kalkulasi penjualan para salesman yang data harga Barang terdapat pada tabel diatasnya.
Di saat belum ada data penjualan yang masuk, maka paa kolom penjualan ditampilkan error dengan tanda #N/A dimana data yang dibutuhkan untuk dihitung tidak ada alias kosong. Mungkin bagi beberapa orang hal ini mengganggu pemandangan. Nah dibawah terlihat Tabel kita menjadi lebih rapi dengan tidak ditampilkannya error code dengan penggunaan Fungsi IFERROR, ISERROR,  ISBLANK, COUNTA, COUNTBLANK dan penggunaan rumus logika menggunakan Fungsi IF.

Inputlah di cell D9 dan copas ke bawah :

Formula alternatif :
=IFERROR(1/(1/VLOOKUP(B9;A2:B5;2;FALSE)*C9);"")

Download File Latihan

Summing and Counting with Multiple Criteria

Bagi anda pengguna Excel 2007 dan 2010 pasti tidak asing dengan Fungsi SUMIFS() dan COUNTIFS(), yaitu fungsi yang dapat digunakan untuk menjumlahkan dan menghitung data dengan 2 atau lebih kriteria. Tetapi kali ini saya tidak membahas keduanya. Saya akan memberikan contoh yang dapat digunakan pada Excel Versi 2003 dan versi diatasnya untuk menjumlahkan dan menghitung data dengan multiple criteria.

Dengan Contoh Kasus Tabel Data diatas, kita ingin memecahkan beberapa Kasus :

1. Menghitung penjualan pada bulan Februari
    =SUMIF(C2:C11;"Februari";E2:E11) --> 1126

2. Berapa kali penjualan yang terjadi pada Bulan Februari ?
    =COUNTIF(C2:C11;"Februari") --> 4

3. Menghitung penjualan pada bulan SELAIN bulan Februari
    =SUMIF(C2:C11;"<>Februari";E2:E11) --> 2968

4. Menghitung penjualan pada bulan Februari dan bulan April
    =SUMIF(C2:C11;"Februari";E2:E11)+SUMIF(C2:C11;"April";E2:E11) --> 2193

5. Menghitung penjualan pada bulan Februari dan dijual oleh Garin
    =SUM((B2:B10="Garin")*(C2:C10="Februari")*E2:E10) --> 550
    Karena perhitungan ini menggunakan 2 kriteria maka Fungsi SUMIF() tidak bisa digunakan, dan jangan
    lupa untuk menekan Ctrl+Shift+Enter karena ini rumus Array.

6. Menghitung penjualan yang dilakukan oleh Salesman selain "Kurnia" pada Bulan April
    =SUM((B2:B11<>"Kurnia")*(C2:C11="April")*E2:E11) --> 811
    (Rumus Array)

7. Berapa kali penjualan yang dilakukan oleh Garin pada Bulan Februari ?
    =SUM(IF(B2:B11="Garin";IF(C2:C11="Februari";1;0);0))
    (Rumus Array)
    atau
    =SUM((B2:B11="Garin")*(C2:C11="Februari")) --> 2
    (Rumus Array)

8. Berapa kali penjualan yang dilakukan selama Bulan Agustus yang laku >=500 pcs ?
    =SUM((C2:C11="Agustus")*(E2:E11>=500)) --> 3
    (Rumus Array)

9. Berapa pcs barang yang laku dijual dengan Range >=300 pcs dan <=700 pcs
    =SUM((E2:E11>=300)*(E2:E11<=700)*(E2:E11)) --> 2500
   (Rumus Array)

10.  Berapa kali penjualan barang yang terjadi dengan Range >=300 pcs dan <=700 pcs ?
    =SUM((E2:E11>=300)*(E2:E11<=700)) --> 5
    (Rumus Array)

Silahkan berkreasi dengan contoh rumus-rumus diatas dengan contoh tabel diatas atau memakai data anda sendiri.

Serba Serbi Fungsi RANK

Apakah Anda pernah menggunakan Fungsi Rank() untuk menentukan urutan ranking pada data anda? Jika sudah pasti anda akan menemukan data ranking yang aneh, yaitu sebagai berikut :
1. Jika data mempunyai skor yang sama maka ranking-nya pun sama (dobel)
2. Bahkan ada nomor urutan ranking yang tidak muncul

Misalnya saya mempunyai data yang akan diurutkan menggunakan Fungsi Rank() seperti ini :

Anda pasti akan menemukan keanehan pada angka score yang sama. Score Andi dan Rosi adalah 25, tetapi mempunyai Ranking yang berurutan.
Score Manik, Samsul, Borneo dan Afifa sama-sama mempunyai score 45 dan mempunyai Ranking yang tidak urut. Bahkan ada nomor Urut 2, 7, 8 ,9 dan 12 tidak muncul. Agak kacau bukan?

Fungsi Rank() masih dapat kita gunakan untuk membuat Ranking suatu data dengan sedikit modifikasi.

Perhatikan di Tabel selanjutnya....


Formula 1

Disini Fungsi Rank() ditambah dengan Fungsi COUNTIF() untuk menghitung Score yang sama dalam satu kolom, mulai baris pertama sampai current row kemudian dikurangi 1.
Coba perhatikan, kita berhasil membuat suatu Ranking yang berurutan walaupun di data tersebut ada beberapa angka score yang sama.

Formula 2

Disini kita mencoba dengan Formula yang sedikit berbeda dengan contoh sebelumnya. Fungsi Rank() ditambah dengan Fungsi COUNTIF() untuk menghitung score yang sama dimulai dari baris pertama sampai 1 baris diatas current row, kemudian ditambah 1.

Semoga dapat berguna ;) Happy & Fun with MS Excel.....

Bonus : Rumus dibawah ini juga bisa untuk mencari posisi ranking dengan mengabaikan data yang sama (dobel) :

 Input di Cell C2 :
=COUNTIF($B$2:$B$13; ">"&B2)+SUM(IF(B2=$B$2:B2; 1; 0))+COUNTIF(B$1:B1;B2)
atau
=RANK(B2;$B$2:$B$13;0)+SUMPRODUCT(--($B$2:$B$13=B2);--($A$2:$A$13<A2))
atau
=COUNTIF($B$2:$B$13; ">"&B2)+SUM(IF(B2=$B$2:B2; 1; 0))


CONTOH LAIN :
Tabel 2 Kolom Nilai dengan Ranking :

Contoh diatas manakala data skor tidak ada yang dobel, jika terjadi data dobel maka rumus ranking diatas akan menampilkan ranking yang sama pula. How to solve this problem? Look example down below.........

Tabel 2 Kolom dengan Ranking (Data Skor ada yang sama) :

Ranking dengan Penambahan Akhiran Urutan (Bahasa Inggris) :

Download File Latihan