agwebrunner.com

Đ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ông thức AVERAGE và STDEV.S hoặc STDEV.P để tính giá trị trung bình và độ lệch chuẩn của dữ liệu, sau đó sử dụng các kết quả đó để xác định Điểm Z của mỗi 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 giá trị trung bình của một điểm dữ liệu. Công thức chung trông như thế này:

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

Dưới đâ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ố do các giáo viên khác nhau dạy. Bạn biết rằng học sinh đầu tiên đạt 95% trong bài kiểm tra cuối kỳ ở một lớp, và học sinh ở lớp kia đạt 87%.

Nhìn sơ qua thì điểm 95% là ấn tượng hơn, nhưng nếu cô giáo dạy lớp 2 ra đề thi khó hơn thì sao? Bạn có thể tính điểm Z của mỗi học sinh dựa trên điểm trung bình trong 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ể thấy rằng học sinh có điểm 87% đã làm tốt hơn so với phần còn lại trong lớp của họ so với học sinh có điểm 98% so với các học sinh còn lại trong lớp.

Giá trị thống kê đầu tiên bạn cần là "giá trị trung bình" và hàm "AVERAGE" của Excel sẽ 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 các giá trị số (nó bỏ qua các ô trống).

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

Các phiên bản Excel trước chỉ có hàm “STDEV”, hàm này tính toán độ lệch chuẩn trong khi coi dữ liệu là một "mẫu" của một tập hợp. Excel 2010 đã chia điều đó thành hai hàm tính toán độ lệch chuẩn:

• STDEV.S: Chức năng này giống với chức năng “STDEV” trước đó. Nó tính toán độ lệch chuẩn trong khi coi dữ liệu là "mẫu" của một tập hợp. Một mẫu quần thể có thể giống như những con muỗi cụ thể được thu thập cho một dự án nghiên cứu hoặc những chiếc ô tô được đặt riêng và dùng để kiểm tra độ 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ộ tổng thể. Toàn bộ dân số sẽ giống như tất cả muỗi trên Trái đất hoặc mọi chiếc ô tô trong quá trình sản xuất của một mô hình cụ thể.

Mà bạn chọn dựa trên tập dữ liệu của bạn. Sự khác biệt thường sẽ nhỏ, nhưng kết quả của hàm “STDEV.P” sẽ luôn nhỏ hơn kết quả của hàm “STDEV.S” cho cùng một tập dữ liệu. Đó là một cách tiếp cận thận trọng hơn khi cho rằng dữ liệu có nhiều biến động hơn.

Hãy xem một ví dụ

Ví dụ: chúng tôi có hai cột (“Giá trị” và “Điểm Z”) và ba ô “trợ giúp” để lưu trữ kết quả của các hàm “AVERAGE”, “STDEV.S” và “STDEV.P”. Cột "Giá trị" chứa mười số ngẫu nhiên tập trung vào khoảng 500 và cột "Điểm Z" là nơi chúng tôi sẽ tính Điểm Z bằng cách sử dụng kết quả được lưu trữ trong các ô "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 hàm “AVERAGE”. Chọn ô nơi bạn sẽ lưu kết quả của hàm “AVERAGE”.

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

= AVERAGE (E2: E13)

Để truy cập chức năng thông qua menu "Công thức", hãy chọn menu thả xuống "Chức năng khác", chọn tùy chọn "Thống kê", sau đó nhấp vào "AVERAGE."

Trong cửa sổ Đối số Hàm, hãy chọn tất cả các ô trong cột “Giá trị” làm đầu vào cho trường “Number1”. Bạn không cần phải lo lắng về trường "Number2".

Bây giờ nhấn “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” hoặc “STDEV.P”. 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 bằng “STDEV.S.” 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”, 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”).

= STDEV.S (E3: E12)

Để truy cập chức năng thông qua menu “Công thức”, hãy chọn menu thả xuống “Chức năng khác”, chọn tùy chọn “Thống kê”, cuộn xuống một chút, sau đó nhấp vào lệnh “STDEV.S”.

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

Bây giờ nhấn “OK”.

Tiếp theo, chúng tôi sẽ tính toán độ lệch chuẩn bằng cách sử dụng hàm “STDEV.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.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”).

= STDEV.P (E3: E12)

Để truy cập chức năng thông qua menu “Công thức”, hãy chọn menu thả xuống “Chức năng khác”, chọn tùy chọn “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ố Hàm, hãy chọn tất cả các ô trong cột “Giá trị” làm đầu vào cho trường “Number1”. Một lần nữa, bạn sẽ không cần phải lo lắng về trường “Number2”.

Bây giờ nhấn “OK”.

Bây giờ chúng tôi đã tính được giá trị trung bình và độ lệch chuẩn của dữ liệu, 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 đến các ô chứa kết quả của các hàm “AVERAGE” và “STDEV.S” hoặc “STDEV.P”.

Chọn ô đầu tiên trong cột "Điểm Z". Chúng tôi sẽ sử dụng kết quả của hàm “STDEV.S” cho ví dụ này, nhưng bạn cũng có thể sử dụng kết quả từ “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. Nhấp vào ô F3 và nhập =(
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 rồi nhấn F4 để thêm các ký tự “$” để tạo tham chiếu ‘tuyệt đối’ cho ô (nó sẽ xoay vòng qua “G3”> “$G$3 ">" G$3″ > “$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 “STDEV.P”) và nhấn F4 để thêm hai ký tự “$”.
7. nhấn nút Enter

Điểm Z đã được tính cho giá trị đầu tiên. Nó là 0,15945 độ lệch chuẩn dưới mức 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 chênh lệch giữa giá trị và giá trị trung bình (499-500) hay không. Cả hai kết quả đều bằng nhau, vì vậy giá trị có ý nghĩa.

Hãy tính điểm Z của các giá trị còn lại. Đánh dấu toàn bộ cột 'Điểm Z' bắt đầu bằng ô 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 ô đã chọn khác.

Bây giờ công thức đã được "điền vào" tất cả các ô và mỗi ô sẽ luôn tham chiếu đến các ô "AVERAGE" và "STDEV.S" hoặc "STDEV.P" chính xác 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 bao gồm trong công thức bạn đã nhập.

Tính Z-Score mà không sử dụng các Ô 'Người trợ giúp'

Các ô của trình trợ giúp lưu trữ một kết quả, giống như những ô lưu trữ kết quả của các hàm “AVERAGE”, “STDEV.S” và “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 hoàn toàn khi tính Z-Score bằng cách sử dụng các công thức tổng quát sau.

Đây là một cách sử dụng chức năng “STDEV.S”:

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

Và một cái sử dụng chức năng “STEV.P”:

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

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

Nếu bạn có một tập dữ liệu lớn, sử dụng ô trợ giúp có thể hiệu quả hơn vì nó không tính toán kết quả của các hàm “AVERAGE” và “STDEV.S” hoặc “STDEV.P” mỗi lần, tiết kiệm tài nguyên của bộ xử lý và đẩy nhanh thời gian tính toán kết quả.

Ngoài ra, “$ G $ 3” cần ít byte hơn để lưu trữ và ít RAM hơn để tải 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).