Bagaimanakah rumus excel yang digunakan untuk mengambil data terakhir pada sebuah tabel dengan kriteria tertentu?
Dalam kasus excel ini misalnya kita mempunyai tabel yang berisi data setoran uang tabungan dari anggota atau nasabah. Kemudian kita ingin dari tabel data tersebut bisa diketahui setoran terakhir untuk masing-masing nasabah.
Untuk lebih jelasnya silahkan perhatikan contoh tabel data excel berikut ini:
Nah, bagaimanakah pengambilan data terakhir pada excel untuk masing-masing nama anggota pada tabel tersebut?
Jika sebelumnya Kelas Excel sudah memberikan tutorial tentang rumus excel untuk mencari nilai terdekat, pada tutorial kali ini kita akan mempelajari cara mengambil data terakhir sebuah kolom atau lebih tepatnya untuk menampilkan data setoran terakhir dengan kriteria nama serta membaca data terakhir untuk bulan tertentu bagi masing-masing anggota atau nasabah dari contoh tabel di atas.
Silahkan di baca dan dipraktekkan.
Mengambil Data Terakhir Dengan Sebuah Kriteria Untuk Masing-masing Nama/ID
Kasus semacam ini biasa saya sebut sebagai rumus vlookup data terakhir dengan kriteria, meskipun pada prakteknya kita tidak harus menggunakan fungsi Vlookup untuk penyelesaiannya.
Rumus excel yang bisa digunakan untuk mencari nilai terakhir pada excel sebagaimana pada contoh tabel data di atas, salah satunya adalah sebagai berikut:
=LOOKUP(2;1/($C$2:$C$11=F4);$D$2:$D$11)
Rumus di atas silahkan dituliskan untuk mengisi kolom G4 seperti pada contoh gambar berikut.
Range C2:C11
merupakan kolom daftar nama penyetor, sedangkan range D2:D11
merupakan kolom jumlah setoran. Pada kedua range ini sengaja saya buat absolut dengan memberikan tanda dollar ($) agar saat kita copy paste ke sel-sel lain dibawahnya tidak berubah.
Adapun referensi sel F4 yang merupakan kriteria dari nama yang akan kita ambil data setoran terakhirnya . Referensi ini biarkan tetap relatif dan tidak perlu kita absolutkan. Selanjutnya anda hanya perlu melakuka copy paste ke sel-sel lain dibawahnya.
Tutorial lebih lanjut tentang referensi absolut ini bisa anda baca pada halaman berikut:
Untuk mengetahui cara kerja rumus ini tentunya anda harus memahami dulu tentang cara menggunakan fungsi Lookup yang sudah jauh hari kita bahas. Jika dirasa perlu silahkan menuju halaman berikut ini untuk mempelajari penjelasannya.
Nilai 2 yang merupakan Lookup Value yang dicari pada rumus tersebut sebenarnya hanya trik saja. Angka ini tidak baku yang pasti harus lebih besar dari 1.
Pada rumus excel diatas kolom tanggal masih diabaikan, lalu bagaimana jika kita hanya ingin mengambil data pada bulan-bulan tertentu saja?
Silahkan dilanjutkan pada bagian kedua tutorial pengambilan data terakhir pada excel ini.
Mengambil Data Terakhir Untuk Masing-masing Nama/ID Pada Bulan Tertentu
Selain data terakhir untuk masing-masing nama jika anda ingin menerapkan kriteria tambahan, misalnya pada kasus ini kita hanya ingn mengamil data pada bulan 10 saja, maka anda bisa menerapkan rumus excel berikut pada sel G4:
=LOOKUP(2;1/(($C$2:$C$11=F4)*(MONTH($B$2:$B$11)=$G$2));$D$2:$D$11)
Pada rumus diatas sel G2 berisi data numeric/angka yang menunjukkan nomor urut bulan yang akan kita jadikan sebagai kriteria.
Kriteria-kriteria ini tentunya bisa nada sesuaikan dengan kebutuhan, namun perlu dicatat bahwa pada rumus ini anda tidak bisa menggunakan fungsi-fungsi perbandingan semisal fungsi OR atau AND. Sebab bagian kriteria pada rumus ini sebenarnya merupakan sebuah Array.
Untuk itu anda perlu merujuk pada pembahasan kita yang sudah lalau tentang penggunaan fungsi-fungsi perbandingan ini pada sebuah array yang bsia anda baca penjelasannya pada halaman berikut:
Kenapa rumus excel untuk nama "Kelas Excel" menghasilkan pesan kesalahan #NA?
Hal ini disebabkan untuk bulan 10 data setoran nama "Kelas Excel" memang tidak ditemukan. Untuk menghilangkan pesan kesalahan ini anda bisa menggunakan fungsi IFERROR atau IFNA.
Jika menggunakan fungsi IFERROR excel maka rumusnya akan menjadi begini:
=IFERROR(LOOKUP(2;1/(($C$2:$C$11=F4)*(MONTH($B$2:$B$11)=$G$2));$D$2:$D$11);"")
Sebagai catatan tambahan pada kolom F tabel data diatas bisa juga anda susun mengguakan rumus excel, dimana pada kolm ini merupakan nilai-nilai unik (membuang data duplikat) dari kolom nama tabel data setoran. Untuk pembahasannya bisa anda temukan pada halaman berikut:
Sebenarnya masih ada beberapa rumus lain yang bisa digunakan untuk mengambil data terakhir pada excel semacam kasus ini. Jika diperlukan lain waktu akan saya update lagi tutorial excel ini.
Sebelum pergi silahkan di share jika artikel tutorial excel ini bermanfaat dan akhir kata sampai jumpa pada tutorial selanjutnya.