Gambar di atas merupakan contoh kasus dan pertanyaan dari teman satu instansi tempat saya bekerja mengenai nomor urut pada hasil filter data pada Microsoft Excel. Dari pertanyaan dalam gambar sepertinya sudah jelas yaitu bagaimana agar setelah dilakukan pemfilteran data, nomor urut pada kolom No tetap terurut, bukan mengambil nilai nomor urut sebelum dilakukannya pemfilteran data.
Sebenarnya saya sendiri belum pernah menghadapi langsung kasus tersebut, jadi dengan keterbatasan pengetahuan Excel saya, saya mencoba mencari bagaimana caranya sampai akhirnya berhasil.
Pertama-tama, pemfilteran tidak mengikutkan kolom No., jadi hanya kolom Nama dan Nilai saja. Jika memang pada setiap baris dua kolom ini tidak ada yang kosong seperti soal di atas, maka ini bisa dilakukan dengan menyorot cell B1 sampai C1 kemudian pilih Data – Filter, dan jika ada satu baris saja yang kosong maka perlu menyorot dari nama kolom sampai isi data kedua kolom ini baru memilih Data – Filter.
Selanjutnya, pada cell A2 yang pada urutan sebelum pemfilteran adalah nomor 1, isikan dengan formula sebagai berikut:
=SUBTOTAL(3;$B$2:$B2)
Copy dan Paste cell A2 ke cell-cell di bawahnya yaitu A3, A4, dan seterusnya sampai baris data terakhir. Untuk cell A3 nantinya akan berisi formula =SUBTOTAL(3;$B$1:$B3), cell A4 akan berisi formula =SUBTOTAL(3;$B$1:$B4), dan seterusnya.Karena pada formula yang digunakan pada kolom A menggunakan kolom B, sebagai acuan, maka harus dipastikan bahwa pada setiap baris pada kolom B harus terisi data. Jika memang kolom C yang pasti terisi data maka modifikasi formula pada kolom A tersebut menjadi =SUBTOTAL(3;$C$2:$C2) untuk cell A2. Penggunaan satu baris sebagai penamaan kolom dengan cara ini juga mutlak diperlukan.
Penggunaan fungsi SUBTOTAL yang mempunyai sintaks SUBTOTAL(function_num; ref1; ref2; …) yaitu function_num=3 berarti menerapkan fungsi COUNTA pada list/database/area ref1 yang dijadikan referensi, hanya saja dengan penggunaan fungsi SUBTOTAL di sini maka tidak akan memperhitungkan nilai-nilai pada cell yang tersembunyi (hidden) seperti pada hasil pemfilteran. Seperti yang Anda ketahui, Fungsi COUNTA sendiri digunakan untuk menghitung banyaknya cell yang tidak kosong saja.
Sumber : http://maseko.com/
