Trong tập hôm nay, tôi đã nói về một ứng dụng sổ điểm đơn giản trong Excel. Mặc dù khái niệm đơn giản, có một số công thức trong sổ điểm có thể giúp bạn cải thiện. Sổ điểm trông như thế này:
Tiêu đề dọc
Các tiêu đề trong hàng 2 được lật ngang bằng cách sử dụng Định dạng - Ô - Căn chỉnh. Trên tab Căn chỉnh, thay đổi hướng thành 90 độ.
Hàng điểm có thể
Điều quan trọng là phải có một hàng trên bảng tính liệt kê tổng số điểm có thể cho mỗi bài tập. Không điền vào điểm có thể cho một bài tập cho đến khi bạn đã nhập điểm cho bài tập đó. Trong hình đầu tiên ở trên, các điểm có thể nằm ở hàng 17.
Tổng công thức trong cột H
Nhập hàm = SUM vào H4 để tổng điểm cho học sinh đầu tiên. Sao chép công thức này xuống cột H cho mỗi học sinh và cho các dòng điểm có thể. Excel tự động thay đổi các tham chiếu tương đối trong công thức để trỏ đến hàng tiếp theo, v.v.
Tricky: Công thức trung bình trong cột I
Công thức cho I4 có vẻ đơn giản. Bạn có thể thử nhập =H4/H17, nhưng điều này sẽ gây ra sự cố.
Khi bạn sao chép công thức này xuống một vài hàng, nó sẽ ngừng hoạt động. Ở đây trong hàng 6, công thức đã thay đổi thành =H6/H19. Đúng là Excel đã thay đổi H4 thành H6, nhưng bạn muốn Excel luôn hoàn toàn trỏ đến H17 làm số chia trong phép tính.
Quay lại và chỉnh sửa công thức trong I4. Bạn có thể chọn I4 và nhấn F2 hoặc chỉ cần nhấp đúp vào I4.
Khi điểm chèn ở ngay sau H17, hãy nhấn phím F4. Excel sẽ chèn các ký hiệu đô la để thay đổi H17 thành $ H $ 17.
Ký hiệu đô la trước chữ H nói rằng bất kể bạn sao chép công thức theo hướng nào, bạn luôn muốn trỏ đến cột H.
Ký hiệu đô la trước số 17 nói rằng bất kể bạn sao chép công thức theo hướng nào, bạn luôn muốn trỏ đến hàng 17.
Đây được gọi là tham chiếu tuyệt đối.
Khi bạn sao chép công thức này cho tất cả học sinh, Excel luôn chia đúng cho tổng điểm có thể có trong hàng 17.
Phần trăm chuyển đổi thành điểm chữ cái
Bạn sẽ sử dụng hàm VLOOKUP để chuyển đổi tỷ lệ phần trăm sang loại chữ cái. Có hai cách để sử dụng hàm VLOOKUP và 90% thời gian trên trang web này, tôi sử dụng phiên bản mà Excel phải tìm một kết quả khớp chính xác trong bảng tra cứu. Tuy nhiên, phiên bản này sử dụng phiên bản VLOOKUP trong đó bảng chứa các phạm vi dữ liệu.
Trong phiên bản này, bảng tra cứu phải được sắp xếp theo thứ tự từ thấp đến cao. Như bạn có thể thấy bên dưới, điều này đòi hỏi bạn phải lấy thang điểm xếp loại trường của mình và lật ngược nó lại. Trong bảng dưới đây, hàng 4 nói rằng bất kỳ điểm nào trên 0 sẽ nhận được điểm F. Hàng 5 tiếp tục và nói rằng bất kỳ điểm nào trên 65% sẽ nhận được điểm D. Trong Hàng 6, bất kỳ điểm nào ở mức 69% trở lên là D + . Ý tưởng cơ bản là Excel sẽ tìm số bằng hoặc thấp hơn điểm của học sinh.
- Để xây dựng hàm VLOOKUP trong J4, hãy bắt đầu với
=VLOOKUP( - Đối số đầu tiên là điểm của học sinh
I4. - Đối số tiếp theo là phạm vi với bảng tra cứu của bạn. Trong ví dụ này, bảng có trong
L4:M12. - Bạn muốn công thức luôn trỏ đến cùng một phạm vi này, vì vậy hãy nhấn phím F4 để thay đổi nó thành
$L$4:$M$12. - Đối số cuối cùng là số cột từ bảng của bạn với loại chữ cái. Trong ví dụ này, nó là một
2. - Công thức là
=VLOOKUP(I4,$L$4:$M$12,2).
- Sao chép công thức xuống cho tất cả học sinh.
Phiên bản mà tôi sử dụng trong chương trình là nhỏ - với ít học sinh và chỉ có một số bài tập. Bạn có thể tải xuống phiên bản nhỏ hơn này để xem các công thức hoạt động như thế nào. Nhấp chuột phải vào CFHGrades.zip .
