Cara Mengambil Nama Siswa dengan Nilai Tertinggi di Excel

Mengambil nama siswa dengan nilai tertinggi dalam sebuah kelas adalah tugas yang sering dilakukan oleh guru atau pihak administrasi sekolah. Dengan menggunakan formula array Excel, Anda dapat dengan mudah menemukan dan menampilkan nama siswa dengan nilai tertinggi dari daftar nilai.

Sebenarnya jika Anda menggunakan Microsoft Excel terbaru atau Office 365, kasus di atas bisa diselesaikan dengan mudah menggunakan rumus FILTER. Tapi jika Microsoft Excel yang Anda gunakan adalah versi lama maka cara mengambil nama siswa dengan nilai tertinggi bisa gunakan formula array. Berikut adalah langkah-langkah detailnya:

Menyiapkan Data Nilai Siswa

Pertama-tama, siapkan data dalam format tabel di Excel. Misalnya, Anda memiliki daftar nama siswa di dengan urutan kolom sebagai berikut: Kolom B untuk Nomor Urut, kolom C untuk NIS, kolom D untuk Nama Siswa dan kolom E untuk Total Nilai, seperti contoh berikut:

Mengambil Nama Siswa dengan Nilai Tertinggi

Untuk menemukan dan mengambil nama siswa dengan nilai tertinggi, kita akan menggunakan formula array. Formula array memungkinkan kita untuk melakukan operasi yang kompleks pada data dalam satu langkah. Berikut adalah langkah-langkahnya:

a. Menggunakan LARGE untuk Mengambil Nilai Tertinggi

Pertama, kita akan menggunakan fungsi LARGE untuk mendapatkan 5 nilai tertinggi. Di sel J3 sampai J7, masukkan formula berikut di sel J3:

=LARGE($E$3:$E$19;G3)

Salin formula ini ke bawah hingga sel J7. Formula ini akan mengambil 5 nilai tertinggi dari kolom E (E3 sampai E19). Dan berikut hasilnya:

Penjelasan Rumus

Berikut adalah penjelasan rinci tentang rumus di atas:

1. Fungsi LARGE:

• LARGE adalah fungsi Excel yang digunakan untuk mengembalikan nilai terbesar ke-n dari sebuah kumpulan data. Bentuk umum fungsi ini adalah: excel LARGE(array, k)
  • array: Rentang sel yang berisi data numerik yang ingin Anda evaluasi.
  • k: Posisi nilai terbesar yang ingin Anda temukan dalam array. Misalnya, k=1 untuk nilai terbesar pertama, k=2 untuk nilai terbesar kedua, dan seterusnya.

2. $E$3:$E$19 (Array):

• $E$3:$E$19 adalah rentang sel yang berisi data yang akan dievaluasi oleh fungsi LARGE. Penggunaan tanda dolar ($) sebelum nomor kolom dan baris menunjukkan bahwa ini adalah referensi absolut, artinya referensi sel ini tidak akan berubah saat rumus disalin ke sel lain.

3. G3 (K):

• G3 adalah sel yang berisi angka yang menentukan nilai terbesar keberapa yang ingin kita temukan. Nilai dalam G3 menunjukkan posisi (k) dari nilai terbesar yang ingin kita ambil dari array.

4. Cara Kerja Rumus

• Langkah 1: Fungsi LARGE mengevaluasi rentang sel yang ditentukan ($E$3:$E$19).
• Langkah 2: Fungsi LARGE mencari nilai terbesar ke-n dalam rentang tersebut, di mana n adalah nilai yang terdapat di sel G3.

Dengan demikian, rumus tersebut mengembalikan nilai terbesar berdasarkan urutan yang ditentukan oleh nilai di sel G3 dari rentang sel yang ditentukan.

b. Menggunakan INDEX dan MATCH untuk Mengambil Nama

Setelah mendapatkan nilai tertinggi, kita perlu menemukan dan mengambil nama siswa dengan nilai tertinggi yang sesuai dengan nilai tersebut. Tapi sebelumnya kita akan mengambil NIS dari siswa dengan nilai tertinggi tersebut. Untuk kebutuhan ini kita akan menggunakan kombinasi INDEX, SMALL dan COUNTIF sebelum akhirnya kombinasi fungsi tersebut kita jadikan array formula. Di sel G3, masukkan formula berikut:

=IFERROR(INDEX(C$3:C$19;SMALL(IF($E$3:$E$19=$J3;ROW(C$3:C$19)-ROW(C$3)+1);COUNTIF($J$3:J3;$J3)));"")

Dan karena ini array formula, maka akhiri penulisan rumus tersebut dengan menekan Ctrl + Shift dan Enter. Jika benar maka akan ada tanda kurung kurawal yang mengapit rumusnya. Salin formula ini ke sel yang ada disampingnya (kolom Nama Siswa) dan kemudian salin juga ke bawah. Formula ini akan menampilkan 5 NIS dan Nama Siswa dengan nilai tertinggi.

Berikut hasil akhir mengambil nama siswa dengan nilai tertinggi ini:

Penjelasan Rumus

Formula array Excel ini adalah formula yang kompleks yang digunakan untuk menemukan dan mengembalikan nilai dari kolom C berdasarkan kriteria tertentu di kolom E. Mari kita pecah dan jelaskan setiap bagian dari formula ini secara rinci:

Bagian-Bagian Formula

1. IFERROR:

• IFERROR(value, value_if_error) adalah fungsi yang mengembalikan value jika tidak ada kesalahan, dan value_if_error jika ada kesalahan. Di sini, ini digunakan untuk mengembalikan string kosong "" jika terjadi kesalahan dalam perhitungan.

2. INDEX:

• INDEX(array, row_num) adalah fungsi yang mengembalikan nilai dari array berdasarkan nomor baris yang diberikan. Dalam formula ini:
  • array adalah C$3:C$19, yaitu rentang sel dari mana kita ingin mengambil nilai.
  • row_num adalah hasil dari fungsi SMALL.

3. SMALL:

• SMALL(array, k) adalah fungsi yang mengembalikan nilai ke-k terkecil dari array. Dalam formula ini:
  • array adalah hasil dari fungsi IF.
  • k adalah hasil dari fungsi COUNTIF.

4. IF:

• IF(logical_test, value_if_true, value_if_false) adalah fungsi yang mengembalikan value_if_true jika logical_test bernilai TRUE, dan value_if_false jika bernilai FALSE. Dalam formula ini:
  • logical_test adalah $E$3:$E$19=$J3, yang memeriksa apakah setiap nilai dalam rentang E3:E19 sama dengan nilai di J3.
  • value_if_true adalah ROW(C$3:C$19)-ROW(C$3)+1, yang mengembalikan nomor baris relatif dari rentang C3:C19.

5. ROW:

• ROW(reference) mengembalikan nomor baris dari referensi. Dalam formula ini:
  • ROW(C$3:C$19) mengembalikan nomor baris dari rentang C3:C19.
  • ROW(C$3) mengembalikan nomor baris dari C3, yaitu 3.
  • ROW(C$3:C$19)-ROW(C$3)+1 mengembalikan array nomor baris relatif (1, 2, 3, …).

6. COUNTIF:

• COUNTIF(range, criteria) adalah fungsi yang menghitung jumlah sel dalam range yang memenuhi criteria. Dalam formula ini:
  • range adalah $J$3:J3.
  • criteria adalah $J3, yang menghitung berapa kali nilai di J3 muncul dalam rentang $J$3:J3.

Cara Kerja Formula

1. IF:

• IF($E$3:$E$19=$J3;ROW(C$3:C$19)-ROW(C$3)+1) menghasilkan array nomor baris relatif dari rentang C3:C19 di mana nilai di kolom E sama dengan nilai di J3. Jika kondisinya benar, akan menghasilkan nomor baris relatif, jika tidak, menghasilkan FALSE.

2. SMALL:

• SMALL(IF($E$3:$E$19=$J3;ROW(C$3:C$19)-ROW(C$3)+1);COUNTIF($J$3:J3;$J3)) mengambil nilai ke-k terkecil dari array yang dihasilkan oleh IF. Nilai k ditentukan oleh COUNTIF($J$3:J3;$J3), yang menghitung jumlah kemunculan nilai di J3 dalam rentang $J$3:J3.

3. INDEX:

• INDEX(C$3:C$19; SMALL(...)) mengembalikan nilai dari kolom C berdasarkan nomor baris yang dihitung oleh SMALL.

4. IFERROR:

• Jika ada kesalahan dalam proses ini (misalnya, jika tidak ada nilai yang cocok ditemukan), IFERROR akan mengembalikan string kosong "" alih-alih menampilkan pesan kesalahan.

Secara keseluruhan, formula untuk mengambil nama siswa dengan nilai tertinggi di Excel ini mencari nilai di kolom C yang sesuai dengan kriteria di kolom E dan mengembalikan hasilnya dalam urutan yang sesuai dengan kemunculannya di kolom J. Jika ada kesalahan, formula akan mengembalikan string kosong.

Kesimpulan

Menggunakan formula array di Excel untuk mengambil nama siswa dengan nilai tertinggi adalah cara yang efisien dan cepat untuk menganalisis data. Dengan langkah-langkah di atas, Anda dapat dengan mudah menemukan dan mengambil nama siswa dengan nilai tertinggi dan menampilkannya di lembar kerja Excel Anda. Formula array tidak hanya membantu menghemat waktu, tetapi juga mengurangi kemungkinan kesalahan dalam pengolahan data secara manual. Cobalah langkah-langkah di atas dan lihat betapa mudahnya mengelola data siswa dengan Excel!

Pranala Luar

File Excel Cara Mengambil Nama Siswa dengan Nilai Tertinggi di Excel ini bisa Anda download jika memang memerlukannya dengan cara klik tautan di bawah ini:

Atau juga jika ingin mempelajari langkah demi langkahnya secara visual, bisa kunjungi Youtube Channel Depot Excel dengan judul video: Cara Mengambil Nama Siswa dengan Nilai Tertinggi di Excel. Di sana Anda akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!