Hướng dẫn sử dụng Excel để quản lý và tổng hợp KQ thi Học sinh giỏi

Quản lý và tổng hợp kết quả thi HSG bằng Excel chỉ bằng các lệnh cơ bản(IF,AND,VLOOKUP, CONNTIF)

Trần Mạnh Hùng, Phòng Nội vụ, huyện Đoan Hùng, tỉnh Phú Thọ.
[email protected]

Trong quá trình làm việc. Đôi khi các bạn phải tổng hợp dữ liệu một cách triệt để nhưng khó thực hiện được. Để cụ thể tôi có một ví dụ sau:

Đây là bảng phân phòng thi của một đợt thi HSG(Chắc ai cũng biết cách làm rồi)



Và đây là bản tổng hợp kết quả khi phân xong hoặc khi vào điểm xong



Và tổng hợp cả Giải


Nếu in ra và đếm rồi điền vào bảng tổng hợp thì chẳng nói làm gì. Vấn đề đặt ra là máy tổng hợp tự động. Để làm được điều này chúng ta phải đặt ra hỏi sau:
Trong E (tôi viết tắt của Excel cho tiện) có thể dùng hàm đếm được Môn Toán, Lý... có bao nhiêu em dự thi nhưng nếu đếm xem Môn toán trường Đại Nghĩa chẳng hạn có bao nhiêu em dự thi thì quả là quá khó(ở đây không nói khi sử dụng hàm Visual Basic).
Tôi đã làm và thật đơn giản chỉ với các hàm như trên. Các bạn cùng tham khảo nhé
B1: Tạo bảng phòng thi như ban đầu.
B2: Chọn Sheef2(tuỳ ý nhưng nên chọn Sh ngay bên cạnh Sh phòng thi ban đầu) đổi tên là Ma( Mã- Sh này dùng làm Sh phụ để tạo mã đếm dữ liệu)



B3: Mở Sh Ma ra và bắt đầu làm
Trước hết tạo List tên các trường và đánh thứ tự


Tạo các tiêu đề đầu của bảng như sau


Ô đầu tiên của hàng đầu tiên phải có toạ độ tương ứng với toạ độ của ô và hàng đầu tiên bên Sh PHONGTHI
+ Trong ô B6 (cột mã môn- mamon) là đánh mã cho môn học các bạn đánh như sau, dùng hàm IF:
=IF(`DSDANG KY`!J6="Toán",1,IF(`DSDANG KY`!J6="Lý",2,IF(`DSDANG KY`!J6="Hoá",3,IF(`DSDANG KY`!J6="Sinh",4,IF(`DSDANG KY`!J6="Văn",5,IF(`DSDANG KY`!J6="Sử",6,IF(`DSDANG KY`!J6="Địa",7,IF(`DSDANG KY`!J6="Anh",8,0))))))))
+ Trong ô C6(matruong- Mã trường) là đánh mã cho trường, thực ra đã làmB3 rồi, Các bạn đánh lệnh tìm kiếm VLOOKUP
=VLOOKUP(`DSDANG KY`!K6,MA!$G$5:$H$29,2,2)
((Câu này có nghĩa: Tìm trong bảng DSDANG KY ô K6 và đối chiếu sang cột thứ 2 tương ứng với vùng G5:H29- Tức là nếu ô K6 c ủa Sh DSDANG KY là Đại Nghĩa thì hiện số 1))
+ Ô Montruong( môn trường) đếm môn của từng trường: đơn giản là kết hợp của 2 ô trên =B6&C6
Tạm thời dừng ở chỗ này đã. Các bạn sẽ biết cách làm ở các bước tiếp theo
B4: Lập một Sh tổng hợp theo ý các bạn như thế này chẳng hạn:


Tôi bắt đầu bằng Môn Toán, các môn khác làm tương tự. Ví dụ Môn toán của trường Đại Nghĩa tại ô đầu tiên:
Các bạn quan sát điều kiện cần đếm là cột D trong bảng Ma tức là cột Montruong; khu vực đếm các bạn cứ cho thật rọng ra
=COUNTIF(MA!$D$6:$D$800,11)
(Đếm điều kiện ở bảng MA vùng D6 đên D800, đếm số 11; các bạn nhớ dùng địa chỉ tuyệt đối nhé (bấm F4); 11 tức là môn Toán(đánh mã là1) và trường Đại Nghĩa (1))
Các bạn Copy lệnh này cho các trường khác và các môn khác và thay số cuói đi là xong, nhớ với trường thứ 2 môn Toán Đoan Hùng thì lệnh phải là =COUNTIF(MA!$D$6:$D$800,12) nhé. Môn lý trường Đại nghĩa phải là =COUNTIF(MA!$D$6:$D$800,21) (môn 2 là lý trường 1)
Lưu ý:
1- Đôi khi làm mất hay xoá nhầm file Mã là rất phức tạp, hơn nữa cho đệp các bạn làm xong thì dáu nó đi như sau: Kích pahỉ lên thanh công cụ bất kỳ chọn



Tiếp hiện bảng

chọn
hiện bảng



Sh MA sẽ biến mất. Khi muốn hiện ra các bạn làm tương tự và chọn –1
2- Nhớ khoá tệp tổng hợp vào kẻo nhỡ cái là sai

Thật đơn giản phải không các bạn. Chúc thành công, tôi gửi cho các bạn toàn bộ tệp EX để các bạn