Excel thống kê giành cho giáo viên nhân năm học mới

Đây là bài viết Excel thống kê giành cho giáo viên nhân năm học mới trong box Office, một box của chuyên mục Thủ Thuật; Tất nhiên là có nhiều chương trình để quản lý điểm học sinh. Trong phạm vi thread này mình xin ...

kết quả từ 1 tới 2 trên 2
  1. simplemind - 08-09-2007 07:51 AM
    Tất nhiên là có nhiều chương trình để quản lý điểm học sinh.
    Trong phạm vi thread này mình xin giới thiệu bản tính excel, các hàm thống kê cơ bản, rất đơn giản. Giáo viên chỉ cần có kiến thức cơ bản về excel thôi cũng có thể áp dụng để quản lý lớp học hiệu quả nhất. Giáo viên có thể quản lý nhiều lớp học trên một file excel.


    1, Hàm MAX: Tìm số điểm cao nhất
    2,Hàm MIN: Tìm số điểm thấp nhất
    3,Hàm trung bình AVERAGE: Tìm điểm trung bình
    4, Hàm tìm Điểm giữa MEDIAN
    5, Hàm xếp thứ tự học sinh, RANK
    6, Format có điều kiện để giúp xác định các học sinh có điểm kém, hay chưa có bài kiểm tra cần giúp đỡ.


    Trong ví dụ này, tiến hành phân tích tại cột kiểm tra giò bài M1.

    *Số điểm chạy từ ô C2 đến ô C22, hay Range: (C2:C22). Vậy chỉ cần đặt công thức trong ô C23: =Max(C2:C22), là ta có kết quả ngay lập tức là 10. Hiểu nôm na là giá trị tối đa của dãy số chạy từ C2 đến C22.
    *Dạng công thức này áp dụng tương tự cho hàm MIN, AVERAGE, MEDIAN
    Trong ví dụ này là
    Ô C23: =Max(C2:C22)
    Ô C24: =Min(C2:C22)
    Ô C25: =Average(C2:C22)
    Ô C26: =Median(C2:C22)

    Để phân tích các cột điểm còn lại, chúng ta cũng chọn từ ô C23:C26 và chọn copy kéo sang trái ta cũng có ngay lập tức kết quả của các ô còn lại.

    Tính điểm trung bình trong cột L

    Ví dụ tính điểm của trò A, và muốn để kết quả trong ô L2, trong ô L2 ta đăt công thức sau
    =(AVERAGE(C2:G2)+2*AVERAGE(H2:J2)+3*K2)/6. Như vậy trung bình của trò A là 9,2.
    Muốn tính điểm trung bình của các trò còn lại thì chỉ việc, chọn và kéo dạng copy là chúng ta có kết qủa của tất cả các em.

    Còn muốn tính thứ tự của trò A, và đặt kết qủa tại ô M2. Vậy trong ô M2 ta để công thức: =RANK(L2,L2:L22,0)
    giải thích:
    L2, nghĩa là số điểm tại ô L2, đem so sánh với tất cả số điểm từ, L2:L22(hay số điểm của tất cả các học sinh trong lớp). 0 là xếp theo thứ tự tăng dần.
    Còn nếu để số 1 thì sẽ cho chiều ngược lại, sẽ không đúng. Trong ví dụ này trò A đứng thứ 9.
    Các bạn sẽ băng khoăn, tại sao trò J và K cùng 10,0 mà lại một trò đứng 1, một trò đứng 4. Đó là do phép làm tròn số của excel. Tuy nhiên khi so sánh, excel vẫn so sánh giá trị gôc nên cho kết quả chính xác. Nếu bạn muốn biết thì format lại cột đó, chọn số thập phân tăng lên 2 hoặc 3 thì sẽ rõ.

    Format có điều kiện
    _Trong trường hợp này, Giáo viên muốn theo dõi điểm số học sinh dưới trung bình và không có bài kiểm tra. Các thầy muốn các ô đó có một màu đặc biệt. Thì các bước tiến hành như sau:

    *Chọn cột điểm cần kiểm tra ví dụ cột Heso_I_2: Ta chọn bằng cách rê chuôt từ ô F2 đến ô F22
    *Chon Conditional Formatting trên thanh menu
    *Chọn Highlight cells rules
    *Chon Less than
    Lúc đó cửa sổ xuất hiện, các thầy chỉ cần điền vào số 5, và ô bên cạnh chọn light red fill with dark red text, hoặc bất kỳ dạng format nào mà thích.
    Và nhấn nút OK, sẽ có kết quả ngay lập tức
    Xem ảnh minh họa.

    1343120299 695741e916 o

    Link để download file excel làm ví dụ
    http://rapidshare.com/files/54116329/bandiem.xlsx
    Đây là cách đánh giá một học sinh trong một lớp qua các bài kiểm tra cụ thể. Còn khi so sánh một cách tổng thể với tất cả các số học sinh trong cùng một khóa học, của thành phố, của tỉnh thì sao? Chúng ta có thể lấy số điểm trung bình để so sánh vói nhau hay không?

    Có thể lấy 8,2 so sánh 8,3 hay không. Tất nhiên là có thể được với ý nghĩa tuyệt đối của con số. Nhứng xét về ý nghĩa xếp loại thì chẳng có ý nghĩa gì lớn.

    Ngày nay, ở các kỳ thi đại học chúng ta vẫn dùng bản điểm theo kiểu bao nhiêu phần trăm trên trung bình, bao nhiều con 10 bao nhiêu con 0. Ý nghĩa thống kê cho thấy chỉ một mặt nào đó của thi cử.

    Nhưng đối với nhà tuyển dụng hay các trường đại học nhằm so sánh thì khó có thể đánh giá rõ hơn vai trò của học sinh, khi họ chỉ có bản số báo điểm 24, hay 25 điểm. Và đó chỉ là con số so sánh với điểm chuẩn mà thôi.

    Để so sánh chính xác hơn, ngày nay các trừong đại học lớn thường bổ sung percentile để so sánh. Lúc đó họ sẽ biết, với số điểm đó, học sinh đứng trong số bao nhiêu phần trăm của tổng số năm đó.

    Kỳ đến xin giới thiệu các bạn percentile và excel, và các hàm thống kê khác trong excel.

    Các bài viết liên quan tới Excel thống kê giành cho giáo viên nhân năm học mới:

  2. simplemind - 09-09-2007 10:05 AM
    Các hàm thống kê cơ bản, và công cụ phân tích thống kê toolpak trong excel 2007.

    Trong mục này gồm có:
    *Hàm SMALL
    *Hàm NORDIST
    *Hàm STDEV
    *Hàm PERCENTILE
    *Vẽ đồ thị phân phối chuẩn của một tập số liệu (data)
    *Giới thiệu công cụ add-in toolpak, và so sánh với phân tích thống kê mô tả dùng thủ công(dùng tay)
    *Cách đánh giá và phân laọi học sinh hiện nay.

    1, Hàm SMALL, hàm này nhằm xác định giá trị nhỏ nhất của một dãy số
    trong ví dụ này tại ô nhập công thức AG2:=SMALL($L$2:$L$22,ROW()-ROW($AG$1))
    _ Ý nghĩa công thức này giúp sắp xếp trật tự của một dãy số từ nhỏ cho đến lớn của L2:L22, trong trường hợp dãy số rời rạc, có xen lân các khỏang trắng, đây là công thức sắp xếp hiệu quả không làm thay đổi các vị trí khác trong bảng.
    _ Như vậy chỉ cần copy kéo nó từ AG2 đến AG22, ta sẽ có một dãy số soi gương của L2 :L22 nhưng được xếp theo thứ tự từ nhỏ đến lớn.
    2, Hàm STDEV,
    Hàm này dùng để tìm độ lệch chuẩn của một dãy số
    Trong ví dụ này tại ô L27:=STDEV(L2:L22). Nghĩa là tìm độ lệch chuẩn của dãy L2:L22, hay độ lệch chuẩn của phân phối điểm số trung bình của học sinh. Ta có kết quả là 1. Do đó sự phân phối điểm số này có trung bình là 8,5, và độ lệch chuẩn là 1, (8,5,1)
    3, Hàm NORDIST, hàm này đưa các giá trị của một dãy số theo phân phối chuẩn có trung bình bằng 0, và độ lệch chuẩn bằng 1 (0,1)
    Hàm này trong thống kê gọi là hàm f(z) quen thuộc của các bạn.
    Đặt công thức tại ô AH2: =NORMDIST(AG2,$L$25,$L$27,FALSE). Có nghĩa là trả giá trị ô AG2 theo phân phối chuẩn. Copy và kéo xuống đên AH22 sẽ tự động biến đổi toàn bộ điểm trung bình học sinh sang phân phối chuẩn.
    Cái này nhằm mục đích vẻ đồ thị hình chuông (bell shape) của phân phối chuẩn sau này.
    4, PERCENTILE, nhằm xác định thứ tự phần trăm của một điểm trong một dãy số.
    Trong ví dụ này, xét đến trò R, được trung bình 9,0. Xếp thứ 9, và nằm trong nhóm 60% dưới của lớp.Vậy trong dãy số từ L2 đến L22, muốn tìm số điểm ứng vào khỏan 60% của dãy số đó thì thế nào. PERCENTILE sẽ làm điều đó. Tại ô AL37:=PERCENTILE(L2:L22,0.6)
    5, Vẽ đồ thị phân phối chuẩn của dãy số từ L2:L22.
    _ Dãy số đó đã được chuẩn hóa qua AH2:AH22. Như vậy chỉ cần chọn AH1:AH22 và insert graph, các bạn ngay lập tức sẽ có hình ảnh phân phối chuẩn giống như hình quả chuông. Rất tiêc, số liệu chưa đủ lớn để cho ra hình chuông.
    _ Còn theo cách thông thường, để xác định phân phối điểm của học sinh, và qua kỳ thi đại học kết quả học sinh được đăng trên các website và báo chí, dựa vào bản điểm trực tiếp (xem hình Sơ đồ phân phối điểm)
    6, ToolPak
    Trong excel 2007, Microsoft đã cho một bộ công cụ phân tích thống kê rât hiệu qủa đó là Toolpak. Công cụ này giúp giải quyết thông kê mô tả đơn giản và các test khác. Mặc dù không thể nào sánh vai được với các phần mềm chuyên dụng về phân tích không kê, tuy nhiên đây là công cụ hữu ích để chúng ta giải quyết các vấn đề cơ bản hằng ngày.
    Muốn cài đặt add-in này, thì click vào nút MS(nút màu góc cao bên phải màn hình), chọn Excel options, chọn Add-ins, chon Analyis Toolpak, chọn Go. Một cửa sổ xuất hiện yêu cầu bạn install add-in này.
    Sau khi install xong, trên menu bar của excel xuất hiện Data Analyis. Click vào đó, một cửa sổ xuất hiện giới thiệu nhiều tool. Mình xin giới thiệu Descriptive statictics(Thống kê mô tả), Rank và Percent, Biểu đồ Histogram.

    1, Để thực hiện thống kê mô tả của dãy L2:L22,
    Chọn từ L2:L22, => Chọn Data analysis, => chọn Descriptive statictics=>OK
    Sau đó cửa sổ xuất hiện yêu cầu bạn đặt vị trí cho kết quả, có thể trong cùng sheet, hay khác sheet.
    Và OK, bạn sẽ có kết quả giống Column 1 tại column ẠJ,AK. Bạn sẽ thấy tất cả giá trị mà chúng ta đã làm trước đây, như trung bình, độ lệch chuẩn....
    2, Thực hiện xếp loại và xác định percentile(vị thứ và thứ tự phần trăm)
    Cũng tương tự như các bước trên
    Chọn từ L2:L22, => Chọn Data analysis, => chọn Rank and Percentile => OK
    Sau đó cửa sổ xuất hiện yêu cầu bạn đặt vị trí cho kết quả, có thể trong cùng sheet, hay khác sheet.
    Bạn sẽ có kết qủa giống như bản Dùng toolpak:percentile and rank

    So sánh với các bước thực hiện của hàm RANK và PERCENTILE đã giới thiệu khi làm bằng tay.

    [IMAGE]http://farm2.static.flickr.com/1270/1348010757_c9fb73a8f9_o.jpg[/IMAGE]
    http://rapidshare.com/files/54351296/bandiem1.xlsx
    Vậy cách đánh giá học sinh của hệ thống giáo dục chúng ta thế nào?

    _ Thứ nhất là dựa vào số điểm cụ thể ví dụ, 9,0 hay 10,0.. để phân lạoi giỏi, khá, trung bình....
    Sự phân laọi nào cũng mang tính tương đối cả, tuy nhiên ở sự phân loại học sinh của ta hiện nay, đã thúc đẩy phụ huynh, học sinh, và giáo viên cố chạy theo thành tích để đạt Loại giỏi 9,0. Trong ví dụ này mình muốn giới thiệu một lớp học theo thành tích, tức là phần lớn là điểm số rất cao. Ai cũng gắng đạt loại giỏi. Em R này, đạt 9,0 nhưng thực chất là chỉ nằm trong tốp 60% của lớp.

    Lấy một ví dụ khác, nếu không chạy theo thành tích giỏi, khá.... Có một học sinh chỉ đạt điểm 6.5 thôi, nhưng trong lớp thầy cho điểm thật và gắt gao, đúng thực lưc học sinh. Bạn này khi xếp loại chỉ đạt trung bình thôi, nhưng xét theo thứ tự phần trăm thì nằm trong tốp 20% chảng hạn.

    Rõ ràng khi ra trường cầm mãnh bằng xin việc, anh bạn 9,0 lọai giỏi chắc chắn là sáng giá hơn nhiều. Nhưng thực tế lại là khác nữa.

    Vì vậy để tạo một nền giáo dục công bằng và thúc đẩy cạnh tranh trong học tập, phát triển sự nghiệp giáo dục của nước nhà, không gì khác hơn, cần phải xem lại cách đánh giá phân loại học sinh hiện nay.

    Nghĩa là, phải có một hệ thống đánh giá chung, chuẩn toàn quốc. Còn nếu không, thì tiến hành từng phần. Học sinh ra trường, bên cạnh việc phân laọi giỏi, khá... cần có các con số về tổng số điểm, và phân loại theo phần trăm của toàn khóa học. Có như vậy, các doanh nghiệp, các nhà đầu tư mới đánh giá chính xác, và tin tưởng vào sản phẩm giáo dục nước nhà được.

    _ Còn về báo cáo kết qủa của bộ giáo dục hiện nay, dựa theo biểu đồ phân phối điểm(xem hình), tức bao nhiêu phần trăm 0, bao nhiêu phần trăm trên 15 điểm trong thi đại học, điểm chuẩn, điểm sàn.... Cũng giúp đánh giá học sinh một phần nào, chứ chưa thật thiết thực, theo ý nghĩa thống kê lắm. Qua đó sẽ khó đánh giá so sánh học sinh, tỷ lệ đỗ, rồi đề khó dễ của các năm.
    Ví dụ, điểm đổ vào đại học BK Hà nội là 9.4x3 chẳng hạn, trong ví dụ này trung bình là một môn là 9.4. Nhưng xét ra theo percentile chỉ khoảng 75%. Tức là để ra dễ hơn mọi năm. Chứ thực ra năm trước điểm đỗ của trường chỉ 8x3 thôi nhưng cũng đã khỏang 75% rồi.

    Trong thống kê ngày nay, ít khi báo cáo dựa vào các phân bố điểm rời rạc, như chúng ta hiện nay. Cách đánh giá phân lạoi học sinh, sinh viên hiện nay sẽ vẫn là cái gốc cho bệnh thành tích. Và rất khó cho các doanh nghiệp nhận xét đúng năng lực của sinh viên mới ra trường.

    Vậy chuẩn hóa toàn quốc về thi cử, và đánh giá học sinh qua percentile và điểm đi kèm, thay đổi cách nhìn nhận và báo cáo kết quả của bộ giáo dục là một trong nhũng yếu tố quan trọng không thể thiếu trong đánh giá chất lượng giáo dục và học sinh.