Dalam episode hari ini, saya berbicara tentang aplikasi buku nilai sederhana di Excel. Meski konsepnya sederhana, ada beberapa rumus di buku nilai yang bisa membuat Anda tersandung. Buku nilai terlihat seperti ini:
Judul Vertikal
Judul di baris 2 dibalik dengan menggunakan Format - Sel - Perataan. Pada tab Alignment, ubah orientasi menjadi 90 derajat.
Poin Kemungkinan Baris
Penting untuk memiliki baris pada spreadsheet yang mencantumkan poin total yang mungkin untuk setiap tugas. Jangan mengisi poin yang mungkin untuk tugas sebelum Anda memasukkan nilai untuk tugas itu. Pada gambar pertama di atas, poin yang memungkinkan ada di baris 17.
Rumus Total di Kolom H
Masukkan fungsi = SUM di H4 untuk menjumlahkan skor untuk siswa pertama. Salin rumus ini ke bawah kolom H untuk setiap siswa dan untuk kemungkinan titik garis. Excel secara otomatis mengubah referensi relatif dalam rumus untuk mengarah ke baris berikutnya, dll.
Rumit: Rumus Rata-rata di Kolom I
Rumus untuk I4 mungkin terlihat sederhana. Anda mungkin mencoba masuk =H4/H17, tetapi ini akan menyebabkan masalah.
Saat Anda menyalin rumus ini ke bawah beberapa baris, rumus berhenti berfungsi. Di sini, di baris 6, rumus diubah menjadi =H6/H19. Benar bahwa Excel mengubah H4 menjadi H6, tetapi Anda ingin Excel selalu benar-benar menunjuk ke H17 sebagai pembagi dalam perhitungan.
Kembali dan edit rumus di I4. Anda dapat memilih I4 dan menekan F2, atau cukup klik dua kali I4.
Ketika titik penyisipan tepat setelah H17, tekan tombol F4. Excel menyisipkan tanda dolar untuk mengubah H17 menjadi $ H $ 17.
Tanda dolar sebelum H mengatakan bahwa ke arah mana Anda menyalin rumus, Anda selalu ingin menunjuk ke kolom H.
Tanda dolar sebelum angka 17 mengatakan bahwa ke arah mana pun Anda menyalin rumus, Anda selalu ingin menunjuk ke baris 17.
Ini disebut referensi absolut.
Saat Anda menyalin rumus ini ke semua siswa, Excel dengan benar selalu membagi dengan total poin yang mungkin di baris 17.
Mengonversi Persentase ke Nilai Huruf
Anda akan menggunakan fungsi VLOOKUP untuk mengubah persentase menjadi nilai huruf. Ada dua cara untuk menggunakan fungsi VLOOKUP, dan 90% dari waktu di situs ini, saya menggunakan versi di mana Excel harus menemukan kecocokan persis di tabel pencarian. Namun, versi ini menggunakan versi VLOOKUP yang tabelnya berisi rentang data.
Dalam versi ini, tabel pemeta harus diurutkan dalam urutan rendah-ke-tinggi. Seperti yang dapat Anda lihat di bawah, ini mengharuskan Anda untuk mengambil skala penilaian sekolah Anda dan mengubahnya menjadi terbalik. Pada tabel di bawah ini, baris 4 mengatakan bahwa setiap skor di atas 0 akan menerima F. Baris 5 mengambil alih dan mengatakan bahwa skor di atas 65% akan menerima D. Di Baris 6, apa pun di 69% atau lebih adalah D + . Ide dasarnya adalah Excel akan menemukan angka yang sama atau lebih rendah dari nilai siswa.
- Untuk membangun VLOOKUP di J4, mulailah dengan
=VLOOKUP( - Argumen pertama adalah nilai siswa dalam
I4. - Argumen berikutnya adalah rentang dengan tabel pencarian Anda. Dalam contoh ini, tabel dalam format
L4:M12. - Anda ingin rumus selalu menunjuk ke kisaran yang sama ini, jadi tekan tombol F4 untuk mengubahnya
$L$4:$M$12. - Argumen terakhir adalah nomor kolom dari tabel Anda dengan nilai huruf. Dalam contoh ini, ini adalah
2. - Rumusnya adalah
=VLOOKUP(I4,$L$4:$M$12,2).
- Salin rumus untuk semua siswa.
Versi yang saya gunakan di acara itu kecil - dengan sedikit siswa dan hanya beberapa tugas. Anda dapat mengunduh versi yang lebih kecil ini untuk melihat cara kerja rumus. Klik kanan CFHGrades.zip .
