Độ lệch chuẩn - Excel và Google Trang tính

Hướng dẫn này trình bày cách sử dụng Hàm độ lệch chuẩn trong Excel trong Excel để tính toán độ lệch chuẩn cho toàn bộ tập hợp.

CHỮ VIẾT TẮT TIÊU CHUẨN Tổng quan về chức năng

Hàm STANDARD DEVIATION Tính toán độ lệch chuẩn cho toàn bộ tập hợp.

Để sử dụng Hàm trang tính Excel TIÊU CHUẨN, hãy chọn một ô và nhập:

(Chú ý cách các đầu vào công thức xuất hiện)

Hàm CHỮ VIẾT TẮT TIÊU CHUẨN Cú pháp và đầu vào:

1	= STDEV (number1, [number2],…)

con số- Giá trị để có được Phương sai Chuẩn

Cách tính độ lệch chuẩn trong Excel

Bất cứ khi nào bạn xử lý dữ liệu, bạn sẽ muốn chạy một số thử nghiệm cơ bản để giúp bạn hiểu nó. Bạn thường sẽ bắt đầu bằng cách tính giá trị trung bình, sử dụng Hàm AVERAGE trong Excel <>.

Điều này cung cấp cho bạn ý tưởng về vị trí "giữa" của dữ liệu. Và từ đó, bạn sẽ muốn xem mức độ lan tỏa của dữ liệu xung quanh điểm giữa này. Đây là nơi mà độ lệch chuẩn xuất hiện.

Excel cung cấp cho bạn một số hàm để tính toán độ lệch chuẩn - STDEV, STDEV.P, STDEV.S và DSTDEV. Chúng ta sẽ tìm hiểu tất cả chúng, nhưng trước tiên, hãy cùng tìm hiểu độ lệch chuẩn Là, chính xác.

Độ lệch chuẩn là gì?

Độ lệch chuẩn cung cấp cho bạn ý tưởng về khoảng cách các điểm dữ liệu của bạn so với giá trị trung bình. Lấy bộ dữ liệu sau đây về điểm kiểm tra trong số 100:

1	48,49,50,51,52

Giá trị trung bình của tập dữ liệu này là 50 (cộng tất cả các số và chia cho n, trong đó n là số giá trị trong phạm vi).

Bây giờ hãy xem tập dữ liệu tiếp theo này:

1	10,25,50,75,90

Giá trị trung bình của tập dữ liệu này là cũng 50 - nhưng hai phạm vi kể một câu chuyện rất khác nhau. Nếu bạn chỉ sử dụng giá trị trung bình, bạn có thể nghĩ rằng hai nhóm tương đương nhau về năng khiếu của họ - và trung bình, họ là như vậy.

Nhưng trong nhóm đầu tiên, chúng tôi có 5 người đạt điểm rất giống nhau, rất tầm thường. Và trong nhóm thứ hai, chúng tôi có một vài người đạt điểm cao bị cân bằng bởi một vài người đạt điểm kém, với một người ở giữa. Các Lan tràn của các điểm số rất khác nhau, làm cho việc giải thích dữ liệu của bạn cũng rất khác nhau.

Độ lệch chuẩn là thước đo của mức chênh lệch này.

Cách tính độ lệch chuẩn

Để hiểu độ lệch chuẩn là gì và nó hoạt động như thế nào, bạn có thể làm việc thông qua một ví dụ bằng tay. Bằng cách đó, bạn sẽ biết điều gì đang diễn ra "ngầm" sau khi chúng ta chuyển sang các hàm Excel mà bạn có thể sử dụng.

Để tính toán độ lệch chuẩn, bạn thực hiện qua quá trình này:

1) Tính giá trị trung bình

Hãy xem tập dữ liệu đầu tiên của chúng tôi ở trên: 48,49,50,51,52

Chúng tôi đã biết giá trị trung bình (50), mà tôi đã xác nhận ở đây với Hàm AVERAGE trong Excel <>:

1	= AVERAGE (C4: C8)

2) Trừ giá trị trung bình cho mỗi giá trị trong tập dữ liệu

Tôi đã thực hiện điều này với công thức sau:

1	= C4- $ H $ 4

Giá trị trung bình của chúng tôi là H4 và tôi đã “khóa” tham chiếu ô bằng cách đặt các ký hiệu đô la trước cột và hàng (bằng cách nhấn F4). Điều này có nghĩa là tôi có thể sao chép công thức xuống cột mà không cần cập nhật tham chiếu ô.

Kết quả:

Bây giờ, chúng ta hãy tạm dừng ở đây một chút. Nếu bạn nhìn vào cột mới - bạn sẽ thấy rằng các số ở đây cộng lại bằng không. Giá trị trung bình của những con số này, cũng bằng không.

Tất nhiên, mức độ lan truyền dữ liệu của chúng tôi không thể bằng 0 - chúng tôi biết rằng có một số biến thể ở đó. Chúng ta cần một cách để biểu diễn sự biến đổi này, không để giá trị trung bình bằng không.

3) Bình phương sự khác biệt

Chúng ta có thể đạt được điều này bằng cách bình phương sự khác biệt. Vì vậy, hãy thêm một cột mới và bình phương các số trong cột D:

1

= D4 * D4

Cái này trông đẹp hơn. Bây giờ chúng ta có một số biến thể, và số lượng biến thể có liên quan đến việc mỗi điểm cách giá trị trung bình bao xa.

4) Tính phương sai - Trung bình của các sai khác bình phương

Bước tiếp theo, là lấy giá trị trung bình của những khác biệt bình phương đó. Thực tế có hai cách để làm điều này khi tính toán độ lệch chuẩn.

• Nếu bạn đang sử dụng dữ liệu dân số, bạn chỉ cần lấy giá trị trung bình (tổng các giá trị và chia cho n)
• Nếu bạn đang sử dụng dữ liệu mẫu, bạn lấy tổng các giá trị và chia cho n-1

Dữ liệu dân số có nghĩa là bạn có "tập hợp đầy đủ" dữ liệu của mình, ví dụ: bạn có dữ liệu về mọi học sinh trong một lớp nhất định.

Dữ liệu mẫu có nghĩa là bạn không có tất cả dữ liệu của mình, chỉ là một mẫu được lấy từ một nhóm lớn hơn. Thông thường, mục tiêu của bạn với dữ liệu mẫu là ước tính giá trị là gì trong tổng thể lớn hơn.

Một cuộc thăm dò ý kiến ​​chính trị là một ví dụ điển hình về dữ liệu mẫu - các nhà nghiên cứu khảo sát 1.000 người để có được ý tưởng về những gì cả một quốc gia hoặc tiểu bang đang nghĩ.

Ở đây chúng tôi không có mẫu. Chúng tôi chỉ có năm thành viên gia đình quan tâm đến thống kê, những người muốn tính toán độ lệch chuẩn của một bài kiểm tra mà tất cả họ đã thực hiện. Chúng tôi có tất cả các điểm dữ liệu và chúng tôi không đưa ra ước tính về một nhóm người lớn hơn. Đây là dữ liệu dân số - vì vậy chúng tôi có thể lấy mức trung bình ở đây:

1	= AVERAGE (E4: E8)

Được rồi, chúng tôi đã nhận được 2. Điểm này được gọi là "phương sai" và nó là điểm cơ bản cho nhiều bài kiểm tra thống kê, bao gồm cả độ lệch chuẩn. Bạn có thể đọc thêm về phương sai trên trang chính của nó: cách tính phương sai trong Excel <>.

5) Lấy căn bậc hai của phương sai

Chúng tôi đã bình phương các con số của mình trước đó, điều này rõ ràng là làm tăng các giá trị lên một chút. Vì vậy, để đưa con số trở lại phù hợp với sự khác biệt thực tế của điểm số so với giá trị trung bình, chúng ta cần căn bậc hai kết quả của bước 4:

1	= SQRT (H4)

Và chúng tôi có kết quả: độ lệch chuẩn là 1,414

Bởi vì chúng tôi đã căn bậc hai cho các số bình phương trước đây của mình, độ lệch chuẩn được cung cấp theo cùng đơn vị với dữ liệu ban đầu. Vì vậy, độ lệch chuẩn ở đây là 1.414 điểm kiểm tra.

Độ lệch chuẩn khi dữ liệu được dàn trải nhiều hơn

Trước đó, chúng tôi đã có phạm vi dữ liệu mẫu thứ hai: 10,25,50,75,90

Nói cho vui thôi, hãy xem điều gì sẽ xảy ra khi chúng tôi tính toán độ lệch chuẩn trên dữ liệu này:

Tất cả các công thức đều giống hệt như trước đây (lưu ý rằng Trung bình Tổng thể vẫn là 50).

Điều duy nhất thay đổi là sự chênh lệch của điểm số trong cột C. Nhưng hiện tại, độ lệch chuẩn của chúng tôi cao hơn nhiều, ở mức 29,832 điểm thi.

Tất nhiên, vì chúng tôi chỉ có 5 điểm dữ liệu, nên rất dễ dàng nhận thấy rằng chênh lệch điểm số giữa hai nhóm là khác nhau. Nhưng khi bạn có 100 hoặc 1.000 điểm dữ liệu, bạn không thể biết điều đó bằng cách quét nhanh dữ liệu. Và đó chính xác là lý do tại sao chúng tôi sử dụng độ lệch chuẩn.

Các hàm Excel để tính độ lệch chuẩn

Bây giờ bạn đã biết độ lệch chuẩn hoạt động như thế nào, bạn không cần phải trải qua toàn bộ quá trình đó để đạt được độ lệch chuẩn. Bạn chỉ có thể sử dụng một trong các hàm tích hợp sẵn của Excel.

Excel có một số hàm cho mục đích này:

• P tính toán độ lệch chuẩn cho dữ liệu dân số (sử dụng phương pháp chính xác mà chúng tôi đã sử dụng trong ví dụ trên)
• NS tính toán độ lệch chuẩn cho dữ liệu mẫu (sử dụng phương pháp n-1 mà chúng ta đã đề cập trước đó)
• STDEV hoàn toàn giống với STDEV.S. Đây là một chức năng cũ hơn đã được thay thế bằng STDEV.S và STDEV.P.
• STDEVA rất giống với STDEV.S, ngoại trừ việc nó bao gồm các ô văn bản và các ô Boolean (TRUE / FALSE) khi thực hiện phép tính.
• STDEVPA rất giống với STDEV.P, ngoại trừ việc nó bao gồm các ô văn bản và các ô Boolean (TRUE / FALSE) khi thực hiện phép tính.

Chà, rất nhiều lựa chọn ở đây! Đừng lo sợ - trong phần lớn các trường hợp, bạn sẽ sử dụng STDEV.P hoặc STDEV.S.

Chúng ta hãy lần lượt xem xét từng vấn đề này, bắt đầu với STDEV.P, vì đó là phương pháp mà chúng tôi vừa thực hiện.

Hàm STDEV.P trong Excel

STDEV.P tính toán độ lệch chuẩn cho dữ liệu dân số. Bạn sử dụng nó như thế này:

1	= STDEV.P (C4: C8)

Bạn xác định một đối số trong STDEV.P: phạm vi dữ liệu mà bạn muốn tính độ lệch chuẩn.

Đây là ví dụ tương tự mà chúng ta đã xem qua từng bước ở trên khi chúng ta tính toán độ lệch chuẩn bằng tay. Và như bạn thấy ở trên, chúng tôi nhận được kết quả chính xác - 1.414.

Lưu ý STDEV.P bỏ qua bất kỳ ô nào chứa giá trị văn bản hoặc Boolean (TRUE / FALSE). Nếu bạn cần bao gồm những điều này, hãy sử dụng STDEVPA.

Hàm STDEV.S trong Excel

STDEV.S tính toán độ lệch chuẩn cho dữ liệu mẫu. Sử dụng nó như thế này:

1	= STDEV.S (C4: C8)

Một lần nữa, cần một đối số - phạm vi dữ liệu mà bạn muốn biết độ lệch chuẩn.

Trước khi chúng ta đi vào một ví dụ, hãy thảo luận về sự khác biệt giữa STDEV.S và STDEV.P.

Như chúng ta đã thảo luận, STDEV.S nên được sử dụng trên dữ liệu mẫu - khi dữ liệu của bạn là một phần của tập hợp lớn hơn. Vì vậy, bây giờ hãy giả sử rằng trong ví dụ của chúng tôi ở trên, nhiều người đã thực hiện bài kiểm tra. Chúng tôi muốn ước tính độ lệch chuẩn của tất cả những người đã làm bài kiểm tra, chỉ sử dụng năm điểm số này. Bây giờ chúng tôi đang sử dụng dữ liệu mẫu.

Bây giờ, phép tính khác với bước (4) ở trên, khi chúng tôi đang tính phương sai - trung bình của sự khác biệt bình phương của mỗi điểm so với giá trị trung bình tổng thể.

Thay vì sử dụng phương pháp bình thường - tổng tất cả các giá trị và chia cho n, chúng tôi sẽ cộng tất cả các giá trị và chia cho n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

Trong công thức này:

• SUM nhận tổng các chênh lệch bình phương
• COUNT trả về n của chúng tôi, mà chúng tôi trừ đi 1 từ
• Sau đó, chúng tôi chỉ cần chia tổng của chúng tôi cho n-1 của chúng tôi

Lần này, giá trị trung bình của sự khác biệt bình phương là 2,5 (bạn có thể nhớ lại trước đây nó là 2, vì vậy nó cao hơn một chút).

Vậy tại sao chúng ta lại chia cho n-1 thay vì n khi xử lý dữ liệu mẫu?

Câu trả lời là khá phức tạp và nếu bạn chỉ đang cố gắng chạy các con số để hiểu dữ liệu của mình, thì đó không phải là điều bạn thực sự cần phải quan tâm. Chỉ cần đảm bảo rằng bạn đang sử dụng STDEV.S cho dữ liệu mẫu và STDEV.P cho dữ liệu dân số và bạn sẽ ổn.

Nếu bạn thực sự tò mò muốn biết lý do tại sao, hãy xem trang chính về cách tính phương sai trong Excel <>.

Được rồi, bây giờ chúng ta đã có phương sai cho mẫu, vì vậy, để có được độ lệch chuẩn cho mẫu, chúng ta chỉ cần lấy căn bậc hai của phương sai:

1	= SQRT (H4)

Chúng tôi nhận được 1.581.

STDEV.S thực hiện tất cả các phép tính trên cho chúng tôi và trả về độ lệch chuẩn mẫu chỉ trong một ô. Vì vậy, hãy xem nó sẽ ra sao…

1	= STDEV.S (C4: C8)

Đúng, 1.581 một lần nữa.

Hàm STDEV trong Excel

Hàm STDEV của Excel hoạt động theo cách giống hệt như STDEV.S - nghĩa là, nó tính toán độ lệch chuẩn cho một mẫu dữ liệu.

Bạn sử dụng nó theo cách tương tự:

1	= STDEV (C4: C8)

Một lần nữa chúng tôi nhận được kết quả tương tự.

Lưu ý quan trọng: STDEV là một "Chức năng tương thích", về cơ bản có nghĩa là Microsoft đang loại bỏ nó. Nó vẫn hoạt động cho đến bây giờ, vì vậy mọi bảng tính cũ hơn sẽ tiếp tục hoạt động như bình thường. Nhưng trong các phiên bản Excel trong tương lai, Microsoft có thể loại bỏ nó hoàn toàn, vì vậy bạn nên sử dụng STDEV.S thay vì STDEV nếu có thể.

Hàm STDEVA trong Excel

STDEVA cũng được sử dụng để tính toán độ lệch chuẩn cho một mẫu, nhưng nó có một số khác biệt quan trọng mà bạn cần biết:

• Giá trị TRUE được tính là 1
• Giá trị FALSE được tính là 0
• Chuỗi văn bản được tính là 0

Sử dụng nó như sau:

1	= STDEVA (C4: C8)

Thêm bốn người bạn và thành viên gia đình đã đưa ra điểm kiểm tra của họ. Chúng được hiển thị trong cột C và cột D cho biết cách STDEVA diễn giải dữ liệu này.

Bởi vì các ô này được hiểu là các giá trị thấp như vậy, điều này tạo ra sự lan truyền dữ liệu của chúng tôi rộng hơn nhiều so với những gì chúng tôi thấy trước đây, điều này đã làm tăng đáng kể độ lệch chuẩn, hiện là 26,246.

Hàm STDEVPA trong Excel

STDEVPA tính toán độ lệch chuẩn cho một tập hợp theo cách tương tự như STDEV.P. Tuy nhiên, nó cũng bao gồm các giá trị Boolean và chuỗi văn bản trong phép tính, được hiểu như sau:

• Giá trị TRUE được tính là 1
• Giá trị FALSE được tính là 0
• Chuỗi văn bản được tính là 0

Bạn sử dụng nó như thế này:

1	= STDEVPA (C4: C12)

Lọc dữ liệu trước khi tính độ lệch chuẩn

Trong thế giới thực, không phải lúc nào bạn cũng có dữ liệu chính xác mà bạn cần trong một bảng ngăn nắp đẹp mắt. Thông thường, bạn sẽ có một bảng tính lớn chứa đầy dữ liệu mà bạn sẽ cần lọc trước khi tính độ lệch chuẩn.

Bạn có thể thực hiện điều này rất dễ dàng với các hàm cơ sở dữ liệu của Excel: DSTDEV (đối với mẫu) và DSTDEVP (đối với quần thể).

Các chức năng này cho phép bạn tạo một bảng tiêu chí, trong đó bạn có thể xác định tất cả các bộ lọc bạn cần. Các chức năng áp dụng các bộ lọc này ở hậu trường trước khi trả về độ lệch chuẩn. Bằng cách này, bạn không cần phải chạm vào Bộ lọc tự động hoặc kéo dữ liệu ra một trang tính riêng - DSTDEV và SDTDEVP có thể làm tất cả những điều đó cho bạn.

Tìm hiểu thêm trên trang chính về Hàm DSTDEV và DSTDEVP <>.

Chức năng CHỮ VIẾT TẮT TIÊU CHUẨN trong Google Trang tính

Hàm CHỮ VIẾT TẮT TIÊU CHUẨN hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel: