=STDEV.S(B2:B9) trả về độ lệch chuẩn của các giá trị trong B2:B9 khi coi chúng là một mẫu, còn =STDEV.P(B2:B9) coi chúng là cả tổng thể. Độ lệch chuẩn cho biết các giá trị thường nằm cách trung bình của chúng bao xa: độ lệch chuẩn nhỏ nghĩa là các giá trị sát nhau.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Result | |
| 2 | Ana | 72 | STDEV.S | 12.82853961 | |
| 3 | Ben | 84 | STDEV.P | 12 | |
| 4 | Cleo | 84 | Average | 90 | |
| 5 | Dan | 84 | |||
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
Điểm trung bình là 90. STDEV.P cho đúng 12 và STDEV.S cho khoảng 12,83. Đổi điểm của Hal thành 90 và cả hai giảm mạnh: chỉ một giá trị nằm xa phần còn lại cũng làm độ lệch chuẩn thay đổi rất nhiều.
STDEV.S và STDEV.P: nên dùng hàm nào
Hai hàm chỉ khác nhau ở một bước. STDEV.P chia tổng bình phương các độ lệch cho số lượng giá trị, n. STDEV.S chia cho n trừ 1, nên kết quả lớn hơn một chút. Lý do: độ phân tán của một mẫu được đo quanh trung bình của chính mẫu đó, vốn nằm gần các giá trị của mẫu hơn trung bình thật, nên nếu chia cho n thì độ phân tán của cả nhóm sẽ bị đánh giá thấp.
- STDEV.P (tổng thể): vùng chứa mọi giá trị bạn muốn mô tả. Điểm của cả 8 học sinh trong lớp này, khi câu hỏi là về chính lớp này.
- STDEV.S (mẫu): vùng là một phần của một nhóm lớn hơn. 8 học sinh được chọn từ một trường 600 người, dùng để ước lượng độ phân tán của cả trường.
Khi còn phân vân, dùng STDEV.S. Phần lớn dữ liệu trong bảng tính là mẫu, và các công cụ thống kê (kiểm định t, khoảng tin cậy) đều cần phiên bản mẫu. Với hàng trăm giá trị, hai kết quả gần như bằng nhau; với 8 giá trị, chênh lệch khoảng 7%.
Các hàm cũ STDEV và STDEVP cho kết quả giống STDEV.S và STDEV.P và vẫn chạy được trên mọi phiên bản Excel. STDEVA và STDEVPA còn tính văn bản là 0 và TRUE là 1, điều mà hiếm khi bạn muốn.
Excel tính độ lệch chuẩn thế nào, từng bước một
Trang tính này làm thủ công đúng những gì STDEV.S làm trong một lần gọi: lấy từng giá trị trừ đi trung bình, bình phương các độ lệch, cộng chúng lại, chia cho n trừ 1 rồi lấy căn bậc hai.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Value | Difference | Squared | Result | ||
| 2 | 4 | -2 | 4 | Sum of squares | 34 | |
| 3 | 8 | 2 | 4 | n | 6 | |
| 4 | 6 | 0 | 0 | Variance (sample) | 6.8 | |
| 5 | 5 | -1 | 1 | Std dev (sample) | 2.607680962 | |
| 6 | 3 | -3 | 9 | STDEV.S | 2.607680962 | |
| 7 | 10 | 4 | 16 |
Tổng bình phương là 34, phương sai mẫu là 6,8, và căn bậc hai của nó (khoảng 2,61) khớp với STDEV.S ở F6. Đổi F4 thành =F2/F3 là bạn có phương sai tổng thể; căn bậc hai của nó chính là giá trị STDEV.P trả về.
Phương sai: VAR.S và VAR.P
Phương sai là độ lệch chuẩn trước khi lấy căn: =VAR.S(A2:A7) cho 6,8 với dữ liệu ở trên, còn =VAR.P(A2:A7) chia cho n thay vì n trừ 1. Phương sai có đơn vị bình phương (điểm bình phương, đô la bình phương), nên khi báo cáo, độ lệch chuẩn dễ đọc hơn. VAR và VARP là tên cũ.
Trung bình cộng trừ một độ lệch chuẩn
Một cách thường dùng để báo cáo độ phân tán là "trung bình ± SD", ví dụ 90 ± 12,8. Hai đầu của khoảng này là những công thức đơn giản, và một quy tắc định dạng có điều kiện có thể đánh dấu các giá trị nằm ngoài khoảng.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Value | |
| 2 | Ana | 72 | Mean | 90.0 | |
| 3 | Ben | 84 | SD | 12.8 | |
| 4 | Cleo | 84 | Low | 77.2 | |
| 5 | Dan | 84 | High | 102.8 | |
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
Quy tắc tô sáng Ana và Hal, hai điểm nằm ngoài khoảng từ 77,2 đến 102,8. Với dữ liệu phân phối chuẩn, khoảng hai phần ba giá trị nằm trong phạm vi một độ lệch chuẩn quanh trung bình, và khoảng 95% nằm trong phạm vi hai độ lệch chuẩn. Để hiển thị văn bản trong một ô dạng "90.0 ± 12.8", dùng =TEXT(E2,"0.0")&" ± "&TEXT(E3,"0.0").
Độ lệch chuẩn có điều kiện
Excel không có hàm STDEVIF. Hãy lồng IF vào trong STDEV.S: IF trả về điểm ở những dòng có khu vực khớp và FALSE ở các dòng còn lại, còn STDEV.S bỏ qua các giá trị FALSE.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Region | STDEV.S | |
| 2 | North | 120 | North | 25.61737691 | |
| 3 | South | 95 | South | 3.872983346 | |
| 4 | North | 150 | |||
| 5 | South | 101 | |||
| 6 | North | 90 | |||
| 7 | South | 98 | |||
| 8 | North | 135 | |||
| 9 | South | 104 |
Doanh số của North biến động nhiều hơn hẳn South. Trong Excel 365 và 2021, công thức này chạy ngay khi gõ. Trong Excel 2019 trở về trước, hãy kết thúc bằng Ctrl+Shift+Enter (Cmd+Shift+Enter trên Mac), nếu không nó sẽ trả về kết quả sai hoặc #VALUE!. Với Excel 365, bạn cũng có thể viết =STDEV.S(FILTER(B2:B9,A2:A9=D2)).
Thử ngay: độ phân tán của thời gian giao hàng
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Days | Std dev | ||
| 2 | A1 | 3 | |||
| 3 | A2 | 5 | |||
| 4 | A3 | 4 | |||
| 5 | A4 | 9 | |||
| 6 | A5 | 3 | |||
| 7 | A6 | 4 | |||
| 8 | A7 | 6 |
Đến lượt bạn: Các đơn hàng trong B2:B8 là một mẫu của mọi đơn hàng. Ở E2, tính độ lệch chuẩn của chúng.
Gợi ý: một mẫu nghĩa là hàm có đuôi .S.
Lỗi thường gặp: dòng tổng nằm trong vùng
Một vùng như B2:B10 có lấy luôn ô tổng hoặc ô trung bình ở cuối cột sẽ coi giá trị tóm tắt đó là thêm một điểm dữ liệu, và độ lệch chuẩn sẽ lớn quá mức. Chỉ chọn các dòng dữ liệu, hoặc để phần tóm tắt ở một cột khác, như các trang tính trên trang này. Ô trống và văn bản trong vùng bị bỏ qua, nhưng số 0 là một giá trị và được tính: một điểm bị thiếu mà gõ thành 0 sẽ làm độ phân tán rộng ra y như một số 0 thật. Để kiểm tra có bao nhiêu giá trị được dùng, đặt =COUNT(B2:B9) cạnh kết quả.
Câu hỏi thường gặp
Công thức tính độ lệch chuẩn trong Excel là gì?
=STDEV.S(B2:B9) cho một mẫu và =STDEV.P(B2:B9) cho cả tổng thể. Cả hai đều bỏ qua văn bản và ô trống trong vùng.
Nên dùng STDEV.S hay STDEV.P?
Chỉ dùng STDEV.P khi vùng chứa mọi thành viên của nhóm bạn đang mô tả, ví dụ cả 8 người trong một đội. Khi dữ liệu là một mẫu dùng để mô tả một nhóm lớn hơn (một số khách hàng, một số lần chạy thử), dùng STDEV.S. Với nhiều giá trị, hai kết quả gần nhau; với ít giá trị, STDEV.S lớn hơn rõ rệt.
STDEV và STDEV.S khác nhau thế nào?
Kết quả không khác gì. STDEV và STDEVP là tên trước năm 2010, được giữ lại để tương thích; STDEV.S và STDEV.P là tên hiện tại. Google Sheets chấp nhận cả hai bộ tên.
Làm sao để tính phương sai trong Excel?
Dùng =VAR.S(B2:B9) cho một mẫu và =VAR.P(B2:B9) cho tổng thể. Phương sai là bình phương của độ lệch chuẩn, nên =STDEV.S(B2:B9)^2 cho cùng con số với VAR.S.
Làm sao để tính sai số chuẩn trong Excel?
Excel không có hàm tính sai số chuẩn của trung bình. Lấy độ lệch chuẩn mẫu chia cho căn bậc hai của số lượng giá trị: =STDEV.S(B2:B9)/SQRT(COUNT(B2:B9)).
