Rumus Excel Mengambil Data Terakhir Dengan Kriteria Pada Sebuah Tabel
Bagaimanakah rumus excel yg digunakan untuk mengambil data terakhir pada sebuah tabel dengan kriteria tertentu?
Dalam kasus excel ini misalnya kita mempunyai tabel yg 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 slahkan perhatikan contoh tabel data excel brkut ini:
Nah, bagaimanakah pengambilan data terakhir pada excel untuk masing-masing nama anggota pada tabel tersebut?
Jika sebelumnya 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 dan membaca data terakhir untuk bulan tertentu bagi masing-masing anggota atau nasabah dari contoh tabel di atas.
Silahkan di baca & dipraktekkan.
Mengambil Data Terakhir Untuk Masing-masing Nama/ID
Kasus semacam ini biasa saya sebut sbgai rumus vlookup data terakhir dengan kriteria, meskipun pada prakteknya kita tidak harus menggunakan fungsi Vlookup untuk penyelesaiannya.
Rumus excel yg bisa digunakan untuk mencari nilai terakhir pada excel sbgaimana pada contoh tabel data di atas, salah satunya adalah sbgai brkut:
=LOOKUP(2;1/($C$2:$C$11=F4);$D$2:$D$11)Rumus di atas slahkan dituliskan untuk mengisi kolom G4 sprti pada contoh gambar brkut.
Range C2:C11 adalah kolom daftar nama penyetor, se&gkan range D2:D11 adalah 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 yg adalah kriteria dari nama yg akan kita ambil data setoran terakhirnya . Referensi ini biarkan tetap relatif & 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 brkut:
Mengenal Referensi Absolut ($) & Relatif pada Microsoft Excel
Untuk mengetahui cara kerja rumus ini tentunya anda harus memahami dulu tentang cara menggunakan fungsi Lookup yg sdh jauh hari kita bahas. Jika dirasa perlu slahkan menuju halaman brkut ini untuk mempelajari penjelasannya.
Fungsi LOOKUP Excel, Contoh & Cara Menggunakannya
Nilai 2 yg adalah Lookup Value yg dicari pada rumus tersebut sebenarnya hanya trik saja. Angka ini tidak baku yg 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 brkut 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 yg menunjukkan nomor urut bulan yg akan kita jadikan sbgai 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 adalah sebuah Array. untuk itu anda perlu merujuk pada pembahasan kita yg sdh lalau tentang penggunaan fungsi-fungsi perbandingan ini pada sebuah array yg bsia anda baca penjelasannya pada halaman brkut:
Menggunakan Fungsi Logika (AND-OR) Pada Array Formula Excel
Kenapa rumus excel untuk nama "" menghasilkan pesan kesalahan #NA?
Hal ini disebabkan untuk bulan 10 data setoran nama "" 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 adalah nilai-nilai unik (membuang data duplikat) dari kolom nama tabel data setoran. Untuk pembahasannya bisa anda temukan pada halaman brkut:
Rumus excel untuk menghilangkan data yg sama untuk mengambil data uniknya saja
Sebenarnya masih ada beberapa rumus lain yg bisa digunakan untuk mengambil data terakhir pada excel semacam kasus ini. Jika diperlukan lain waktu akan saya update lagi tutorial excel ini.
Sebelum pergi slahkan di share jika artikel tutorial excel ini bermanfaat & akhir kata sampai jumpa pada tutorial selanjutnya. Sumber https://www.kelasexcel.id
Belum ada Komentar untuk "Rumus Excel Mengambil Data Terakhir Dengan Kriteria Pada Sebuah Tabel"
Posting Komentar