Dalam episod hari ini, saya bercakap mengenai aplikasi buku lulusan sederhana di Excel. Walaupun konsepnya sederhana, terdapat beberapa formula dalam buku nilai yang dapat membuat anda kecewa. Buku graduan kelihatan seperti ini:
Tajuk menegak
Tajuk pada baris 2 dihidupkan di sisi mereka dengan menggunakan Format - Sel - Penjajaran. Pada tab Penjajaran, ubah orientasi menjadi 90 darjah.
Titik Kemungkinan Baris
Penting untuk mempunyai baris pada hamparan yang menyenaraikan jumlah mata yang mungkin untuk setiap tugasan. Jangan mengisi mata yang mungkin untuk tugasan sehingga anda telah memasuki gred untuk tugasan itu. Pada rajah pertama di atas, mata yang mungkin terdapat pada baris ke-17.
Jumlah Formula dalam Lajur H
Masukkan fungsi = SUM di H4 untuk jumlah markah bagi pelajar pertama. Salin formula ini di lajur H untuk setiap pelajar dan untuk garis mungkin. Excel secara automatik mengubah rujukan relatif dalam formula untuk menunjuk ke baris seterusnya, dll.
Rumit: Purata Formula di Lajur I
Rumus untuk I4 mungkin kelihatan mudah. Anda mungkin cuba memasukkannya =H4/H17, tetapi ini akan menimbulkan masalah.
Apabila anda menyalin formula ini ke beberapa baris, ia berhenti berfungsi. Di sini pada baris 6, formula berubah menjadi =H6/H19. Memang betul bahawa Excel menukar H4 menjadi H6, tetapi anda mahu Excel selalu menunjukkan H17 sebagai pembahagi dalam pengiraan.
Kembali dan edit formula di I4. Anda boleh memilih I4 dan tekan F2, atau hanya klik dua kali I4.
Apabila titik penyisipan tepat selepas H17, tekan kekunci F4. Excel memasukkan tanda dolar untuk menukar H17 menjadi $ H $ 17.
Tanda dolar sebelum H mengatakan bahawa tidak kira arah mana anda menyalin formula, anda selalu ingin menunjuk ke ruangan H.
Tanda dolar sebelum 17 mengatakan bahawa tidak kira arah mana pun anda menyalin formula, anda selalu ingin menunjukkan ke baris ke-17.
Ini dipanggil rujukan mutlak.
Apabila anda menyalin formula ini kepada semua pelajar, Excel selalu membahagi dengan jumlah mata yang mungkin di baris 17.
Menukar Peratusan ke Nilai Huruf
Anda akan menggunakan fungsi VLOOKUP untuk menukar peratus kepada gred huruf. Terdapat dua cara untuk menggunakan fungsi VLOOKUP, dan 90% masa di laman web ini, saya menggunakan versi di mana Excel harus mencari padanan tepat dalam jadual pencarian. Walau bagaimanapun, versi ini menggunakan versi VLOOKUP di mana jadual mengandungi julat data.
Dalam versi ini, jadual pencarian mesti disusun mengikut urutan rendah ke tinggi. Seperti yang anda lihat di bawah, ini memerlukan anda mengambil skala penggredan sekolah dan membalikkannya. Dalam jadual di bawah, baris 4 menyatakan bahawa sebarang skor di atas 0 akan menerima F. Row 5 mengambil alih dan mengatakan bahawa sebarang skor di atas 65% akan menerima D. Di Baris 6, apa-apa pada 69% atau lebih tinggi adalah D + . Idea asasnya ialah Excel akan mencari nombor yang sama dengan atau lebih rendah daripada skor pelajar.
- Untuk membina VLOOKUP di J4, mulakan dengan
=VLOOKUP( - Hujah pertama ialah skor pelajar dalam
I4. - Argumen seterusnya adalah julat dengan jadual carian anda. Dalam contoh ini, jadual masuk
L4:M12. - Anda mahu formula selalu menunjukkan julat yang sama, jadi tekan kekunci F4 untuk menukarnya
$L$4:$M$12. - Argumen terakhir adalah nombor lajur dari jadual anda dengan nilai huruf. Dalam contoh ini, ia adalah
2. - Rumusannya ialah
=VLOOKUP(I4,$L$4:$M$12,2).
- Salin formula ke bawah untuk semua pelajar.
Versi yang saya gunakan pada rancangan itu kecil - dengan sedikit pelajar dan hanya beberapa tugasan. Anda boleh memuat turun versi yang lebih kecil ini untuk melihat bagaimana formula berfungsi. Klik kanan CFHGrades.zip .
