Cách tính điểm Z bằng Microsoft Excel

Điểm Z là một giá trị thống kê cho bạn biết có bao nhiêu độ lệch chuẩn mà một giá trị cụ thể xảy ra so với giá trị trung bình của toàn bộ tập dữ liệu. Bạn có thể sử dụng các công thức AVERAGE và STDEV.S hoặc STDEV.P để tính độ lệch trung bình và độ lệch chuẩn của dữ liệu của bạn và sau đó sử dụng các kết quả đó để xác định Điểm Z của từng giá trị.

Điểm Z là gì và các chức năng AVERAGE, STDEV.S và STDEV.P làm gì?

Điểm Z là một cách đơn giản để so sánh các giá trị từ hai tập dữ liệu khác nhau. Nó được định nghĩa là số độ lệch chuẩn so với điểm trung bình của điểm dữ liệu. Công thức chung trông như thế này:

= (DataPoint-AVERAGE (Bộ dữ liệu)) / STDEV (Bộ dữ liệu)

Đây là một ví dụ để giúp làm rõ. Giả sử bạn muốn so sánh kết quả kiểm tra của hai học sinh Đại số được dạy bởi các giáo viên khác nhau. Bạn biết rằng học sinh đầu tiên đạt 95% trong bài kiểm tra cuối cùng ở một lớp và học sinh ở lớp kia đạt 87%.

Thoạt nhìn, điểm 95% ấn tượng hơn, nhưng nếu giáo viên của lớp hai làm bài kiểm tra khó hơn thì sao? Bạn có thể tính Điểm Z của điểm của từng học sinh dựa trên điểm trung bình của mỗi lớp và độ lệch chuẩn của điểm trong mỗi lớp. So sánh điểm Z của hai học sinh có thể tiết lộ rằng học sinh có điểm 87% so với phần còn lại của lớp so với học sinh có điểm 98% so với phần còn lại của lớp.

Giá trị thống kê đầu tiên bạn cần là hàm 'AVERAGE' của Excel có nghĩa là tính toán giá trị đó. Nó chỉ đơn giản là cộng tất cả các giá trị trong một phạm vi ô và chia tổng đó cho số ô chứa giá trị số (nó bỏ qua các ô trống).

Giá trị thống kê khác mà chúng tôi cần là 'độ lệch chuẩn' và Excel có hai hàm khác nhau để tính độ lệch chuẩn theo các cách hơi khác nhau.

Các phiên bản trước của Excel chỉ có chức năng STDEVv của Nhật Bản, tính toán độ lệch chuẩn trong khi coi dữ liệu là 'mẫu' của dân số. Excel 2010 đã chia thành hai hàm tính toán độ lệch chuẩn:

• STDEV.S: Hàm này giống hệt với hàm STDEV trước đó. Nó tính toán độ lệch chuẩn trong khi coi dữ liệu là "mẫu" của dân số. Một mẫu quần thể có thể giống như những con muỗi đặc biệt được thu thập cho một dự án nghiên cứu hoặc những chiếc xe được đặt sang một bên và được sử dụng để thử nghiệm an toàn khi va chạm.
• STDEV.P: Hàm này tính toán độ lệch chuẩn trong khi coi dữ liệu là toàn bộ dân số. Toàn bộ dân số sẽ giống như tất cả các loài muỗi trên Trái đất hoặc mọi chiếc xe trong một quá trình sản xuất của một mô hình cụ thể.

Những gì bạn chọn được dựa trên tập dữ liệu của bạn. Sự khác biệt thường sẽ là nhỏ, nhưng kết quả của hàm ST STVVPPP của STTVV sẽ luôn nhỏ hơn kết quả của hàm ST STVV.SV của STTVVV cho cùng một bộ dữ liệu. Đó là một cách tiếp cận thận trọng hơn để cho rằng có nhiều thay đổi trong dữ liệu.

Hãy xem một ví dụ

Ví dụ của chúng tôi, chúng tôi có hai cột (các giá trị của chanh và giá trị Z) và ba ô của người trợ giúp trực tuyến để lưu trữ các kết quả của các chức năng AVERAGE, mật vụ STDEV.S, và và STDEV.P. Cột của Giá trị xếp hạng có chứa mười số ngẫu nhiên tập trung vào khoảng 500 và cột Số điểm Z của Số điểm là nơi chúng tôi sẽ tính toán Điểm Z bằng cách sử dụng các kết quả được lưu trữ trong các ô của 'người trợ giúp'.

Đầu tiên, chúng tôi sẽ tính giá trị trung bình của các giá trị bằng cách sử dụng chức năng AV AVAGEAGE. Chọn ô nơi bạn sẽ lưu trữ kết quả của chức năng AV AVAGEAGE.

Nhập công thức sau và nhấn đi vào -hoặc - sử dụng menu Công thức của Wap.

= AVERAGE (E 2: E13)

Để truy cập chức năng thông qua menu Công thức ăn vặt, hãy chọn trình đơn thả xuống của More More Chức năng, chọn tùy chọn Số liệu thống kê, sau đó nhấp vào AVERAGE.

Trong cửa sổ Đối số chức năng, chọn tất cả các ô trong cột Giá trị của Wap làm đầu vào cho trường Số Number1. Bạn không cần phải lo lắng về trường Số Number2..

Bây giờ hãy nhấn vào OK.

Tiếp theo, chúng ta cần tính toán độ lệch chuẩn của các giá trị bằng cách sử dụng hàm STDEV.S. Trong ví dụ này, chúng tôi sẽ chỉ cho bạn cách tính cả hai giá trị, bắt đầu với ST STVV., Hãy chọn ô nơi kết quả sẽ được lưu trữ.

Để tính toán độ lệch chuẩn bằng cách sử dụng chức năng STDEV.S của Trực tiếp, hãy nhập công thức này và nhấn Enter (hoặc truy cập nó thông qua menu Công thức của Công thức).

= STDEV.S (E3: E12)

Để truy cập chức năng thông qua menu Công thức ăn vặt, hãy chọn trình đơn thả xuống của Chức năng khác, chọn tùy chọn Số liệu thống kê, đặt xuống một chút, sau đó nhấp vào lệnh ST STVV..

Trong cửa sổ Đối số chức năng, chọn tất cả các ô trong cột Giá trị của Wap làm đầu vào cho trường Số Number1. Bạn cũng không cần phải lo lắng về trường Số Number2 ở đây.

Bây giờ hãy nhấn vào OK.

Tiếp theo, chúng tôi sẽ tính toán độ lệch chuẩn bằng cách sử dụng chức năng ST STVV.P. Chọn ô nơi kết quả sẽ được lưu trữ.

Để tính toán độ lệch chuẩn bằng cách sử dụng chức năng STDEV.Pv, hãy gõ vào công thức này và nhấn Enter (hoặc truy cập nó thông qua menu Công thức của Công thức)..

= STDEV.P (E3: E12)

Để truy cập chức năng thông qua menu Công thức ăn vặt, hãy chọn trình đơn thả xuống của Chức năng khác, chọn tùy chọn Số liệu thống kê, cuộn xuống một chút, sau đó nhấp vào công thức STDEV.P.

Trong cửa sổ Đối số chức năng, chọn tất cả các ô trong cột Giá trị của Wap làm đầu vào cho trường Số Number1. Một lần nữa, bạn sẽ không cần phải lo lắng về trường Số Number2..

Bây giờ hãy nhấn vào OK.

Bây giờ chúng tôi đã tính toán trung bình và độ lệch chuẩn của dữ liệu của chúng tôi, chúng tôi có tất cả những gì chúng tôi cần để tính Z-Score. Chúng ta có thể sử dụng một công thức đơn giản để tham chiếu các ô chứa kết quả của các hàm AV AVAGEAGE và ST STVV..

Chọn ô đầu tiên trong cột Z-Score Cảnh. Chúng tôi sẽ sử dụng kết quả của hàm STDEV.S của Lốc đá cho ví dụ này, nhưng bạn cũng có thể sử dụng kết quả từ Lốc STDEV.P.

Nhập công thức sau và nhấn Enter:

= (E3- $ G $ 3) / $ H $ 3

Ngoài ra, bạn có thể sử dụng các bước sau để nhập công thức thay vì nhập:

1. Bấm vào ô F3 và gõ = (
2. Chọn ô E3. (Bạn có thể nhấn phím mũi tên trái một lần hoặc sử dụng chuột)
3. Nhập dấu trừ -
4. Chọn ô G3 sau đó nhấn F4 để thêm các ký tự của $ đô la để tạo một tham chiếu 'tuyệt đối' cho ô (nó sẽ chuyển qua chu kỳ của G3 G3$G$3 "> Quảng G$3 "> hoàng$G3 "> Truyền G3 G3 nếu bạn tiếp tục nhấn F4)
5. Kiểu ) /
6. Chọn ô H3 (hoặc I3 nếu bạn đang sử dụng ST STVVPPP) và nhấn F4 để thêm hai ký tự.
7. bấm phím Enter

Điểm Z đã được tính cho giá trị đầu tiên. Nó là 0.15945 độ lệch chuẩn dưới giá trị trung bình. Để kiểm tra kết quả, bạn có thể nhân độ lệch chuẩn với kết quả này (6.271629 * -0.15945) và kiểm tra xem kết quả có bằng với chênh lệch giữa giá trị và giá trị trung bình (499-500). Cả hai kết quả đều bằng nhau, vì vậy giá trị có ý nghĩa.

Hãy tính Z-Scores của phần còn lại của các giá trị. Đánh dấu toàn bộ cột 'Điểm Z' bắt đầu bằng ô có chứa công thức.

Nhấn Ctrl + D, sao chép công thức ở ô trên cùng xuống qua tất cả các ô được chọn khác.

Bây giờ công thức đã được 'điền vào' cho tất cả các ô và mỗi ô sẽ luôn tham chiếu đúng các ô AVERAGE và và STDEV.Siêu hoặc ST STVV.P vì các ký tự $ $. Nếu bạn gặp lỗi, hãy quay lại và đảm bảo các ký tự của $ $ được đưa vào công thức bạn đã nhập.

Tính điểm Z mà không cần sử dụng các ô 'Người trợ giúp'

Các tế bào của trình trợ giúp lưu trữ một kết quả, giống như các ô lưu trữ các kết quả của các hàm AVERAGE, STỌN STDEV.S, Lốc và Lốc STDEV.P. Chúng có thể hữu ích nhưng không phải lúc nào cũng cần thiết. Thay vào đó, bạn có thể bỏ qua chúng khi tính toán Điểm Z bằng cách sử dụng các công thức tổng quát sau đây.

Đây là một trong những cách sử dụng chức năng STDEV.S.

= (Giá trị-AVERAGE (Giá trị)) / STDEV.S (Giá trị)

Và một bằng cách sử dụng chức năng của STE STE.P.

= (Giá trị-AVERAGE (Giá trị)) / STDEV.P (Giá trị)

Khi nhập các phạm vi ô cho các Giá trị của Nhóm trong các chức năng, hãy đảm bảo thêm các tham chiếu tuyệt đối (Điên $ bằng cách sử dụng F4) để khi bạn 'điền vào', bạn không tính được độ lệch trung bình hoặc tiêu chuẩn của một phạm vi khác của các tế bào trong mọi công thức.

Nếu bạn có một tập dữ liệu lớn, việc sử dụng các ô của trình trợ giúp có thể hiệu quả hơn vì nó không tính được kết quả của các chức năng AV AVAGEAGE và Hồi STDEV.S và hoặc STDEV.P. tăng tốc thời gian cần thiết để tính kết quả.

Ngoài ra, khác, $ G $ 3, nhà cung cấp ít byte hơn để lưu trữ và tải ít RAM hơn so với AVERAGE ($ E $ 3: $ E $ 12). Điều này rất quan trọng vì phiên bản Excel 32 bit tiêu chuẩn bị giới hạn ở 2GB RAM (phiên bản 64 bit không có bất kỳ giới hạn nào về số lượng RAM có thể được sử dụng).