Tips-tips Rumus Excel: Tes TPA dan TOEFL

Kasus 1 (Update Otomatis Data Skor TPA) :

Seorang staf admisi di Program S2 Magister kebingungan karena memproses banyak data Tes TPA (Tes Potensi Akademik) sebagai syarat masuk sebagai mahasiswa. Dia mempunyai Tabel Data seperti ini.

Skor minimal yang dapat diterima adalah 500. Terlihat ada 3 Tabel, yang pertama adalah Tabel Tes Penerimaan, Tabel ke-2 adalah Tabel Tes Perbaikan 1 dan ketiga adalah Tabel Tes Perbaikan 2.
Petugas admisi tersebut menginginkan update otomatis data skor TPA bagi yang belum memenuhi syarat, setelah calon mahasiswa yang belum lulus mengulang tes tersebut, dan tes perbaikan diberikan maksimal 2 kali.

Setelah data skor TPA di-input menjadi seperti ini :

Pada tabel skor TPA diatas, terlihat ada 4 calon mahasiswa yang mengulang sampai 2 kali karena skornya dibawah 500. Yaitu :
1. Sule
2. Bondan
3. Siti
4. Sumi

Ada juga yang mengulang 1 kali saja, yaitu :
1. Randi

Dan pada Tabel Update terlihat data Skor TPA terkini yang otomatis ter-update mengikuti input data pada ketiga Tabel Skor TPA.

Inilah formulanya (Cell B18 - B25) :

=IF(AND(COUNTIF($D$7:$D$11;A25)=0;COUNTIF($G$7:$G$10;$A25)=0);INDEX($B$7:$B$14;MATCH($A25;$A$7:$A$14;0));IF(COUNTIF($G$7:$G$10;$A25)=0;INDEX($E$7:$E$11;MATCH($A25;$D$7:$D$11;0));INDEX($H$7:$H$10;MATCH($A25;$G$7:$G$10;0))))

Anda bisa menambah Fungsi Error Trapping pada rumus ini:

=IFERROR(IF(AND(COUNTIF($D$7:$D$14;A25)=0;COUNTIF($G$7:$G$14;$A25)=0);INDEX($B$7:$B$14;MATCH($A25;$A$7:$A$14;0));IF(COUNTIF($G$7:$G$14;$A25)=0;INDEX($E$7:$E$14;MATCH($A25;$D$7:$D$14;0));INDEX($H$7:$H$14;MATCH($A25;$G$7:$G$14;0))));"")

Silahkan anda gunakan rumus ini untuk kasus-kasus yang serupa. Be Creative....

Kasus 2 (Mengambil Data Calon Mahasiswa Yang Lulus Seleksi) :

Petugas Admisi ingin mengambil data calon mahasiswa yang lulus dengan memenuhi skor minimal TPA (500) dan TOEFL (500).

Dan kriteria yang diterapkan adalah :

Formula yang diterapkan kali ini :

Formula yang pertama adalah menghitung jumlah calon mahasiswa yang lulus, berjumlah 3 orang. Formula ini berkaitan dengan formula kedua, yang akan menampilkan sesuai data yang ditampilkan oleh formula pertama.

Untuk mengambil data digunakan Array Formula (CTRL-SHIFT-ENTER) :

Penjelasan :
1. Bagian =IF(ROWS(E$10:E10)<=$G$4 akan mengecek apakah current row sekarang lebih kecil atau sama dengan jumlah calon mahasiswa yang lulus (3 orang). Jika "Ya", teruskan eksekusi yang nomor 2 dibawah ini.

2. Bagian INDEX(A$3:A$9;SMALL(IF($B$3:$B$9>=$E$4;IF($C$3:$C$9>=$F$4;ROW($B$3:$B$9)-ROW($B$3)+1));ROWS(E$10:E10))) akan mengambil data baris demi baris pada Kolom "Nama", "TPA" dan TOEFL".

3. Bagian =IF(ROWS(E$10:E10)<=$G$4 jika bernilai salah maka akan menampilkan Cell kosong (;"")

Kasus 3 (Mengambil Data Mahasiswa Yang Memenuhi Syarat Wisuda) :

Petugas Alumni S2 ingin mengambil data dari mahasiswa yang memenuhi syarat wisuda. Kriteria yang harus dipenuhi sebanyak 3 macam, yaitu Ujian Tesis, Dokumen Wisuda Lengkap dan SPP Lunas.