Trik Excel: Fungsi VLOOKUP dipadukan dengan COUNTIF dan IFNA

Spread the love

Dalam postingan kali ini saya akan membagikan sebuah trik Excel yaitu menggunakan fungsi VLOOKUP yang dipadukan dengan fungsi COUNTIF dan IFNA untuk mengambil suatu kelompok data dalam sebuah database excel. sebagai contoh kasus dalam trik ini, saya menggunakan data siswa hasil export dapodik yang memiliki data dengan jumlah kolom lebih dari 40 kolom dan baris datanya sebanyak sejumlah siswa pada sekolah tersebut. Disini kita akan memisahkan (mengambil) data siswa berdasarkan Rombelnya sehingga bisa digunakan untuk keperluan lain seperti pembuatan absensi, daftar nilai siswa dan lain-lain.

Sebelum masuk pada pokok pembahasan kita, disini saya menjelaskan dulu apa itu fungsi VLOOKUP, COUNTIF, dan IFNA.

    • Fungsi VLOOKUP digunakan untuk mengambil data tertentu pada suatu baris database dengan acuan kolom pertama dari dari database tersebut.
    • Fungsi COUNTIF digunakan untuk menghitung banyaknya data berdasarkan kriteria tertentu.
    • Fungsi IFNA digunakan untuk menggantikan error #N/A ketika data yang diinginkan tidak ditemukan.


Sekarang kita masuk pada bagian trik excel yaitu dengan membuka satu file excel hasil export dari data siswa pada aplikasi dapodik seperti tampak pada gambar berikut ini, yang terdiri dari 46 kolom dengan jumlah baris data 514.

Fungsi VLOOKUP

Sekarang kita akan menambil data siswa diatas berdasarkan rombel tertentu dengan langkah-langkah sebagai berikut:

Lanngkah I: Modifikasi database

  • Agar kita dapat dengan mudah menentukan nomor kolom pada database, sisipkan sebuah baris baru dibawah baris ke-4 dengan cara klik kanan pada sala satu cell pada baris ke-4 pilih insert lalu pilih Entire row lalu klik OK. pada baris yang baru ditambahkan tadi isi data dengan nomor 1 s/d seterusnya hingga kolom terakhir.
  • Pada kolom No (Kolom A) yang berisi nomor urut siswa, kita ganti isinya dengan dengan suatu data index yang dapat mengidentifikasi (membedakan) rombel dan anggota rombelnya. Ini sebagai patokan fungsi VLOOKUP dalam mengambil data, dengan cara pilih cell A7 ketikan rumus “=AQ8&”-“&COUNTIF(AQ$8:AQ8;AQ8)” tanpa tanda petik, lalu tekan Enter.
  • Kopi rumus diatas ke cell dibawahnya hingga baris terakhir.
  • Penjelasan rumus: AQ8 (merupakan cell pertama data rombel); & (untuk menggabungkan dengan fungsi/karakter lain); “-” (untuk memisakhkan indeks rombel dengan anggota rombelnya); fungsi COUNTIF (untuk mengindeks anggota rombel denggan nomor urut); AQ$8:AQ8 (data bagi fungsi COUNTIF untuk mengindeks, tanda $ digunakan agar ketika rumus dikopikan ke baris berikutnya maka cell acuan tidak berubah); AQ8 terakhir (kriteria yang digunakan fungsi COUNTIF).
  • setelah selesai maka databasenya akan tampak seperti gambar berikut:Fungsi VLOOKUP


Langkah II Membuat Sheet Data Rombel

  • Tambahkan sebuah sheet baru kediaan rubah namanya sesuai selera kamu misalkan di contoh ini saya namai ROMBEL.
  • Isilah sheet tersebut dengan data dan tabel seperti gambar berikut:Fungsi VLOOKUP
  • untuk kolom list data isinya sesuaikan dengan nama rombel yang anda gunakan (lihat kembali sheet daftar peserta didik) dan harus sama persis. Misalkan nama rombel yang kamu gunakan VIIA maka pada list data isi VIIA, VIIB, VIIC dan seterusnya hingga rombel terakhir.

Baca juga: Membuat Nomor Urut Otomatis pada Excel

Langkah III: Membuat List Data

  • Pada sheet ROMBEL, pilih cell C3 kemudian pada menu Data klik Data ValidationFungsi VLOOKUP
  • Pada Kotak Allow, pilh List, maka akan muncul kotak Source dibawah kotak Data
  • klik tombol disamping kanan kotak Source intuk memilih data list
  • Sort cell G6 sampai G25 (Klik tahan pada cell G6 dan tarik kebawah sampai cell terakhir yang berisi list data kemudian lepaskan), dan tekan enter sehingga pada kotak Source sudah terlihat isi seperti gambar berikut.Fungsi VLOOKUP
  • Klik OK, maka sekarang pada cell C3 sudah terdapat satu tombol dropdown bisa digunakan untuk memilih rombel yang datanya ingin ditampilkan.

Hosting Unlimited Indonesia


Langkah IV: Mengambil Data Rombel

  • Untuk memasukan data rombel yang dibutuhkan maka pada cell B6 ketikan rumus: “=IFNA(VLOOKUP($C$3&”-“&$A6;’Daftar Peserta Didik’!$A$8:$BF$519;2;FALSE);””)“. tanpa tanda petik. Kopikan rumus diatas ke cell B7 dan seterusnya.
  • Penjelasan Rumus: IFNA (untuk menghilangkan tulisan #N/A karena data tidak ditemukan); VLOOKUP (untuk mengambil data dari database); $C$3&”-“&$A6 (acuan yang digunakan fungsi VLOOKUP); Daftar Peserta Didik’!$A$8:$BF$519 (tabel database yang diambil datanya); 2 (kolom ke-2 dari database); FALSE (apabila indeks tidak berurutan); “” (apabila data tidak ditemukan maka isi cell menjadi kosong)
  • kopikan rumus diatas ke cell C6 lalu ganti 2 menjadi 3 (karena NIS ada pada kolom 3 database) untuk mengambil data NIS siswa sehingga rumusnya menjadi: “=IFNA(VLOOKUP($C$3&”-“&$A6;’Daftar Peserta Didik’!$A$8:$BF$519;3;FALSE);””)“. Lalu kopi ke cell C7 dan seterusnya.
  • lakukan langkah yang sama untuk cell kolom NISN (Kolom 5 pada database)  dan Jenis Kelamin (kolom 4 database).
  • Jika sudah selesai maka ketika kamu mengganti rombel pada cell C3, anggota rombel dan datanya akan ikut berubah secara otomatis.Fungsi VLOOKUP


Sebagai Contoh saya juga menyertakan file excel diatas yang bisa didownload DISINI. demikain postingan saya, semoga bermanfaat.

Insert math as
Block
Inline
Additional settings
Formula color
Text color
#333333
Type math using LaTeX
Preview
\({}\)
Nothing to preview
Insert
%d blogger menyukai ini: