Độ lệch trung bình trong công thức excel. Tính toán độ lệch chuẩn trong Microsoft Excel

Hãy tính toán trongCÔEXCELphương sai và độ lệch chuẩn của mẫu. Chúng tôi cũng tính toán phương sai của một biến ngẫu nhiên nếu biết phân phối của nó.

Đầu tiên hãy xem xét sự phân tán, sau đó độ lệch chuẩn.

Phương sai mẫu

Phương sai mẫu (phương sai mẫu,mẫu vậtphương sai) đặc trưng cho sự lan truyền các giá trị trong mảng so với.

Cả 3 công thức đều tương đương về mặt toán học.

Có thể thấy từ công thức đầu tiên rằng phương sai mẫu là tổng các độ lệch bình phương của mỗi giá trị trong mảng từ mức trung bình chia cho cỡ mẫu trừ đi 1.

sự phân tán mẫu hàm DISP () được sử dụng, eng. tên của VAR, tức là VARIance. Kể từ MS EXCEL 2010, bạn nên sử dụng DISP.V () tương tự của nó, eng. tên VARS, tức là Phương sai mẫu. Ngoài ra, bắt đầu từ phiên bản MS EXCEL 2010, có một hàm DISP.G (), eng. Tên VARP, tức là VARIance tổng thể tính toán sự phân tán vì dân số. Toàn bộ sự khác biệt thuộc về mẫu số: thay vì n-1 như DISP.V (), DISP.G () chỉ có n ở mẫu số. Trước MS EXCEL 2010, hàm VARP () đã được sử dụng để tính phương sai tổng thể.

Phương sai mẫu
= SQUARE (Mẫu) / (COUNT (Mẫu) -1)
= (SUMSQ (Mẫu) -COUNT (Mẫu) * AVERAGE (Mẫu) ^ 2) / (COUNT (Mẫu) -1)- công thức thông thường
= SUM ((Mẫu -AVERAGE (Mẫu)) ^ 2) / (COUNT (Mẫu) -1) –

Phương sai mẫu chỉ bằng 0 nếu tất cả các giá trị đều bằng nhau và do đó, bằng nhau giá trị trung bình. Thông thường, giá trị càng lớn sự phân tán, mức độ lan truyền các giá trị trong mảng càng lớn.

Phương sai mẫu là một ước tính điểm sự phân tán phân phối của biến ngẫu nhiên mà từ đó mẫu vật. Về việc xây dựng khoảng tin cậy khi đánh giá sự phân tán có thể được đọc trong bài báo.

Phương sai của một biến ngẫu nhiên

Tính toán sự phân tán biến ngẫu nhiên, bạn cần biết nó.

Vì sự phân tán biến ngẫu nhiên X thường sử dụng ký hiệu Var (X). Sự phân tán bằng bình phương của độ lệch so với giá trị trung bình E (X): Var (X) = E [(X-E (X)) 2]

sự phân tánđược tính theo công thức:

trong đó x i là giá trị mà biến ngẫu nhiên có thể nhận, và μ là giá trị trung bình (), p (x) là xác suất để biến ngẫu nhiên nhận giá trị x.

Nếu biến ngẫu nhiên có, thì sự phân tánđược tính theo công thức:

Kích thước sự phân tán tương ứng với bình phương của đơn vị đo các giá trị ban đầu. Ví dụ, nếu các giá trị trong mẫu là số đo trọng lượng của bộ phận (tính bằng kg), thì thứ nguyên của phương sai sẽ là kg 2. Do đó, điều này có thể khó giải thích để mô tả sự lan truyền của các giá trị, một giá trị bằng căn bậc hai của sự phân tán – độ lệch chuẩn.

Một số tài sản sự phân tán:

Var (X + a) = Var (X), trong đó X là biến ngẫu nhiên và a là hằng số.

Var (aХ) = a 2 Var (X)

Var (X) = E [(X-E (X)) 2] = E = E (X 2) -E (2 * X * E (X)) + (E (X)) 2 = E (X 2) - 2 * E (X) * E (X) + (E (X)) 2 = E (X 2) - (E (X)) 2

Thuộc tính phân tán này được sử dụng trong bài viết về hồi quy tuyến tính.

Var (X + Y) = Var (X) + Var (Y) + 2 * Cov (X; Y), trong đó X và Y là các biến ngẫu nhiên, Cov (X; Y) là hiệp phương sai của các biến ngẫu nhiên này.

Nếu các biến ngẫu nhiên là độc lập, thì hiệp phương sai là 0, và do đó Var (X + Y) = Var (X) + Var (Y). Thuộc tính này của phương sai được sử dụng trong đầu ra.

Hãy chứng minh rằng đối với các đại lượng độc lập Var (X-Y) = Var (X + Y). Thật vậy, Var (X-Y) = Var (X-Y) = Var (X + (- Y)) = Var (X) + Var (-Y) = Var (X) + Var (-Y) = Var (X) + (- 1) 2 Var (Y) = Var (X) + Var (Y) = Var (X + Y). Thuộc tính này của phương sai được sử dụng để vẽ biểu đồ.

Độ lệch chuẩn mẫu

Độ lệch chuẩn mẫu là thước đo mức độ phân tán của các giá trị trong mẫu so với chúng.

A-priory, độ lệch chuẩn bằng căn bậc hai của sự phân tán:

Độ lệch chuẩn không tính đến độ lớn của các giá trị trong lấy mẫu, nhưng chỉ mức độ phân tán của các giá trị xung quanh chúng ở giữa. Hãy lấy một ví dụ để minh họa điều này.

Hãy tính độ lệch chuẩn cho 2 mẫu: (1; 5; 9) và (1001; 1005; 1009). Trong cả hai trường hợp, s = 4. Rõ ràng là tỷ lệ của độ lệch chuẩn so với các giá trị của mảng là khác nhau đáng kể đối với các mẫu. Đối với những trường hợp như vậy, hãy sử dụng Hệ số biến đổi(Hệ số biến thiên, CV) - tỷ lệ độ lệch chuẩnđến mức trung bình Môn số học, được biểu thị dưới dạng phần trăm.

Trong MS EXCEL 2007 và các phiên bản trước đó để tính toán Độ lệch chuẩn mẫu hàm = STDEV () được sử dụng, eng. tên STDEV, tức là độ lệch chuẩn. Kể từ MS EXCEL 2010, bạn nên sử dụng analog = STDEV.B (), eng. tên STDEV.S, tức là Độ lệch chuẩn mẫu.

Ngoài ra, bắt đầu từ phiên bản MS EXCEL 2010, có một hàm STDEV.G (), eng. tên STDEV.P, tức là Dân số Tiêu chuẩn DEViation tính toán độ lệch chuẩn vì dân số. Toàn bộ sự khác biệt thuộc về mẫu số: thay vì n-1 như STDEV.V (), STDEV.G () chỉ có n ở mẫu số.

Độ lệch chuẩn cũng có thể được tính toán trực tiếp từ các công thức bên dưới (xem tệp ví dụ)
= SQRT (SQUADROTIV (Mẫu) / (COUNT (Mẫu) -1))
= SQRT ((SUMSQ (Mẫu) -COUNT (Mẫu) * AVERAGE (Mẫu) ^ 2) / (COUNT (Mẫu) -1))

Các biện pháp phân tán khác

Hàm SQUADRIVE () tính toán với umm độ lệch bình phương của các giá trị so với ở giữa. Hàm này sẽ trả về kết quả giống như công thức = VAR.G ( Mẫu vật)*KIỂM TRA( Mẫu vật) , ở đâu Mẫu vật- một tham chiếu đến một phạm vi chứa một mảng các giá trị mẫu (). Các phép tính trong hàm QUADROTIV () được thực hiện theo công thức:

Hàm SROOT () cũng là một thước đo độ phân tán của một tập dữ liệu. Hàm SIROTL () tính giá trị trung bình của các giá trị tuyệt đối của độ lệch giá trị từ ở giữa. Hàm này sẽ trả về kết quả giống như công thức = SUMPRODUCT (ABS (Mẫu-AVERAGE (Mẫu))) / COUNT (Mẫu), ở đâu Mẫu vật- một tham chiếu đến một phạm vi chứa một mảng các giá trị mẫu.

Các phép tính trong hàm SROOTKL () được thực hiện theo công thức:

Trong bài viết này, tôi sẽ nói về cách tìm độ lệch chuẩn. Tài liệu này cực kỳ quan trọng đối với sự hiểu biết đầy đủ về toán học, vì vậy một gia sư toán học nên dành một hoặc thậm chí một số bài học riêng biệt để nghiên cứu nó. Trong bài viết này, bạn sẽ tìm thấy liên kết đến video hướng dẫn chi tiết và dễ hiểu giải thích độ lệch chuẩn là gì và cách tìm nó.

độ lệch chuẩn giúp bạn có thể ước tính mức độ lan truyền của các giá trị thu được do đo lường một tham số nhất định. Nó được biểu thị bằng một ký hiệu (chữ cái Hy Lạp "sigma").

Công thức tính toán khá đơn giản. Để tìm độ lệch chuẩn, bạn cần lấy căn bậc hai của phương sai. Vì vậy, bây giờ bạn phải hỏi, "Phương sai là gì?"

Sự phân tán là gì

Định nghĩa của phương sai như sau. Độ phân tán là trung bình cộng của các độ lệch bình phương của các giá trị so với giá trị trung bình.

Để tìm phương sai, hãy thực hiện tuần tự các phép tính sau:

• Xác định giá trị trung bình (trung bình cộng đơn giản của một loạt giá trị).
• Sau đó, trừ trung bình của mỗi giá trị và bình phương cho sự khác biệt kết quả (chúng tôi nhận được bình phương chênh lệch).
• Bước tiếp theo là tính giá trị trung bình cộng của các bình phương chênh lệch thu được (Bạn có thể tìm hiểu tại sao lại có chính xác các bình phương bên dưới).

Hãy xem một ví dụ. Giả sử bạn và bạn bè của bạn quyết định đo chiều cao của những chú chó của bạn (tính bằng milimét). Theo kết quả của các phép đo, bạn nhận được các số đo chiều cao sau (tính đến cả vai): 600 mm, 470 mm, 170 mm, 430 mm và 300 mm.

Hãy tính giá trị trung bình, phương sai và độ lệch chuẩn.

Trước tiên hãy tìm giá trị trung bình. Như bạn đã biết, đối với điều này, bạn cần phải cộng tất cả các giá trị đo được \ u200b \ u200band chia cho số lần đo. Tiến độ tính toán:

Trung bình mm.

Vì vậy, trung bình (trung bình cộng) là 394 mm.

Bây giờ chúng ta cần xác định độ lệch của chiều cao của mỗi con chó so với mức trung bình:

Cuối cùng, để tính toán phương sai, mỗi sự khác biệt thu được được bình phương, và sau đó chúng tôi tìm giá trị trung bình cộng của các kết quả thu được:

Độ phân tán mm 2.

Do đó, độ phân tán là 21704 mm 2.

Cách tìm độ lệch chuẩn

Vì vậy, làm thế nào bây giờ để tính toán độ lệch chuẩn, biết phương sai? Như chúng ta nhớ, hãy lấy căn bậc hai của nó. Đó là, độ lệch chuẩn là:

mm (làm tròn đến số nguyên gần nhất, tính bằng mm).

Sử dụng phương pháp này, chúng tôi nhận thấy rằng một số con chó (ví dụ như Rottweilers) là những con chó rất lớn. Nhưng cũng có những con chó rất nhỏ (ví dụ như chó dachshunds, nhưng bạn không nên nói với chúng điều này).

Điều thú vị nhất là độ lệch chuẩn mang thông tin hữu ích. Bây giờ chúng ta có thể chỉ ra kết quả thu được của việc đo lường sự tăng trưởng nằm trong khoảng mà chúng ta nhận được nếu chúng ta loại bỏ độ lệch chuẩn trung bình (ở cả hai phía của nó).

Nghĩa là, bằng cách sử dụng độ lệch chuẩn, chúng tôi nhận được một phương pháp "tiêu chuẩn" cho phép bạn tìm ra giá trị nào là bình thường (trung bình thống kê) và giá trị nào là cực kỳ lớn hoặc ngược lại, nhỏ.

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

Nhưng ... mọi thứ sẽ khác một chút nếu chúng ta phân tích lấy mẫu dữ liệu. Trong ví dụ của chúng tôi, chúng tôi đã xem xét dân số chung.Đó là, 5 con chó của chúng tôi là những con chó duy nhất trên thế giới khiến chúng tôi thích thú.

Nhưng nếu dữ liệu là một mẫu (các giá trị được chọn từ một tập hợp lớn), thì các phép tính cần được thực hiện theo cách khác.

Nếu có các giá trị, thì:

Tất cả các phép tính khác được thực hiện theo cách tương tự, bao gồm cả việc xác định giá trị trung bình.

Ví dụ: nếu năm con chó của chúng ta chỉ là một mẫu của một quần thể chó (tất cả các loài chó trên hành tinh), chúng ta phải chia cho 4 thay vì 5 cụ thể là:

Phương sai mẫu = mm 2.

Trong trường hợp này, độ lệch chuẩn của mẫu bằng mm (làm tròn đến số nguyên gần nhất).

Chúng tôi có thể nói rằng chúng tôi đã thực hiện một số "điều chỉnh" trong trường hợp giá trị của chúng tôi chỉ là một mẫu nhỏ.

Ghi chú. Tại sao chính xác là các hình vuông của sự khác biệt?

Nhưng tại sao chúng ta lại lấy bình phương của sự khác biệt khi tính phương sai? Hãy thừa nhận khi đo một số tham số, bạn đã nhận được bộ giá trị sau: 4; 4; -4; -4. Nếu chúng ta chỉ thêm các độ lệch tuyệt đối từ giá trị trung bình (chênh lệch) giữa chúng ... các giá trị âm sẽ bị loại bỏ bằng các giá trị dương:

.

Nó chỉ ra rằng tùy chọn này là vô ích. Sau đó, có lẽ nó đáng để thử các giá trị tuyệt đối của độ lệch (nghĩa là, mô-đun của những giá trị này)?

Thoạt nhìn, nó hóa ra không tệ (nhân tiện, giá trị kết quả được gọi là độ lệch tuyệt đối trung bình), nhưng không phải trong mọi trường hợp. Hãy thử một ví dụ khác. Cho kết quả của phép đo trong tập giá trị sau: 7; một; -6; -2. Khi đó, độ lệch tuyệt đối trung bình là:

Blimey! Chúng tôi lại nhận được kết quả 4, mặc dù sự khác biệt có mức chênh lệch lớn hơn nhiều.

Bây giờ chúng ta hãy xem điều gì sẽ xảy ra nếu chúng ta bình phương các chênh lệch (và sau đó lấy căn bậc hai của tổng của chúng).

Đối với ví dụ đầu tiên, bạn nhận được:

.

Đối với ví dụ thứ hai, bạn nhận được:

Bây giờ nó là một vấn đề hoàn toàn khác! Độ lệch gốc-trung bình-bình phương càng lớn, mức độ lan truyền của sự khác biệt càng lớn ... đó là những gì chúng tôi đang phấn đấu.

Trên thực tế, phương pháp này sử dụng ý tưởng tương tự như khi tính khoảng cách giữa các điểm, chỉ được áp dụng theo một cách khác.

Và từ quan điểm toán học, việc sử dụng bình phương và căn bậc hai hữu ích hơn chúng ta có thể lấy trên cơ sở các giá trị tuyệt đối của độ lệch, do đó độ lệch chuẩn có thể áp dụng cho các vấn đề toán học khác.

Sergey Valerievich đã cho bạn biết cách tìm độ lệch chuẩn

Chương trình Excel được đánh giá cao bởi cả chuyên gia và nghiệp dư, bởi vì người dùng ở bất kỳ trình độ đào tạo nào cũng có thể làm việc với nó. Ví dụ: bất kỳ ai có kỹ năng tối thiểu về "giao tiếp" với Excel đều có thể vẽ một biểu đồ đơn giản, tạo một dấu hiệu đẹp, v.v.

Đồng thời, chương trình này thậm chí còn cho phép bạn thực hiện nhiều loại tính toán khác nhau, chẳng hạn như tính toán, nhưng điều này đã yêu cầu một mức độ đào tạo hơi khác. Tuy nhiên, nếu bạn mới bắt đầu làm quen với chương trình này và quan tâm đến mọi thứ sẽ giúp bạn trở thành người dùng nâng cao hơn, thì bài viết này là dành cho bạn. Hôm nay tôi sẽ cho bạn biết công thức độ lệch chuẩn trong excel là gì, tại sao nó lại cần thiết và trên thực tế, nó được áp dụng khi nào. Đi!

Nó là gì

Hãy bắt đầu với lý thuyết. Độ lệch chuẩn thường được gọi là căn bậc hai, thu được từ trung bình cộng của tất cả các chênh lệch bình phương giữa các giá trị có sẵn, cũng như trung bình cộng của chúng. Nhân tiện, giá trị này thường được gọi là chữ cái Hy Lạp "sigma". Độ lệch chuẩn được tính bằng công thức STDEV, tương ứng, chương trình thực hiện điều đó cho chính người dùng.

Bản chất của khái niệm này là xác định mức độ thay đổi của công cụ, theo cách riêng của nó, là một chỉ số từ thống kê mô tả. Nó cho thấy những thay đổi về sự biến động của công cụ trong bất kỳ khoảng thời gian nào. Sử dụng công thức STDEV, bạn có thể ước tính độ lệch chuẩn của một mẫu, trong khi giá trị boolean và văn bản bị bỏ qua.

Công thức

Giúp tính toán độ lệch chuẩn trong công thức excel, được cung cấp tự động trong Excel. Để tìm nó, bạn cần tìm phần công thức trong Excel, và chọn phần công thức có tên STDEV, rất đơn giản.

Sau đó, một cửa sổ sẽ xuất hiện trước mặt bạn, trong đó bạn sẽ cần nhập dữ liệu để tính toán. Đặc biệt, hai số nên được nhập vào các trường đặc biệt, sau đó chương trình sẽ tự động tính toán độ lệch chuẩn cho mẫu.

Không nghi ngờ gì nữa, các công thức và phép tính toán học là một vấn đề khá phức tạp và không phải người dùng nào cũng có thể giải quyết nó ngay lập tức. Tuy nhiên, nếu bạn đào sâu hơn một chút và hiểu vấn đề chi tiết hơn một chút, hóa ra không phải mọi thứ đều đáng buồn như vậy. Tôi hy vọng bạn bị thuyết phục về điều này bằng ví dụ về tính toán độ lệch chuẩn.

Video để giúp đỡ

Hướng dẫn

Giả sử có một số số đặc trưng - hoặc các đại lượng đồng nhất. Ví dụ, kết quả của các phép đo, cân, quan sát thống kê, v.v. Tất cả các đại lượng được trình bày phải được đo bằng cùng một phép đo. Để tìm độ lệch chuẩn, hãy làm như sau.

Xác định trung bình cộng của tất cả các số: cộng tất cả các số và chia tổng cho tổng các số.

Xác định độ phân tán (tán xạ) của các số: cộng các bình phương của các độ lệch tìm được trước đó và chia tổng thu được cho số lượng các số.

Khoa có bảy bệnh nhân với nhiệt độ 34, 35, 36, 37, 38, 39 và 40 độ C.

Yêu cầu xác định độ lệch trung bình so với giá trị trung bình.
Quyết định:
"trong phường": (34 + 35 + 36 + 37 + 38 + 39 + 40) / 7 = 37 ºС;

Độ lệch nhiệt độ so với mức trung bình (trong trường hợp này là giá trị bình thường): 34-37, 35-37, 36-37, 37-37, 38-37, 39-37, 40-37, hóa ra là: -3, -2, -1, 0, 1, 2, 3 (ºС);

Chia tổng các số có được trước đó cho số của chúng. Để tính chính xác của phép tính, tốt hơn là sử dụng máy tính bỏ túi. Kết quả của phép chia là trung bình cộng của các tổng.

Hãy chú ý đến tất cả các giai đoạn của phép tính, vì một sai sót trong ít nhất một trong các phép tính sẽ dẫn đến chỉ số cuối cùng không chính xác. Kiểm tra các phép tính đã nhận ở mỗi giai đoạn. Trung bình số học có cùng thước đo với tổng và các số, nghĩa là, nếu bạn xác định được số người tham gia trung bình, thì tất cả các chỉ số sẽ là “người”.

Phương pháp tính toán này chỉ được sử dụng trong các phép tính toán học và thống kê. Vì vậy, ví dụ, trung bình cộng trong khoa học máy tính có một thuật toán tính toán khác. Trung bình cộng là một chỉ số rất có điều kiện. Nó cho thấy xác suất của một sự kiện, với điều kiện là nó chỉ có một yếu tố hoặc chỉ báo. Để phân tích chuyên sâu nhất, phải tính đến nhiều yếu tố. Đối với điều này, việc tính toán các đại lượng tổng quát hơn được sử dụng.

Trung bình cộng là một trong những thước đo có xu hướng trung tâm, được sử dụng rộng rãi trong toán học và tính toán thống kê. Việc tìm giá trị trung bình cộng của một số giá trị rất đơn giản, nhưng mỗi công việc lại có những sắc thái riêng, những điều này đơn giản cần biết để thực hiện các phép tính chính xác.

Kết quả định lượng của các thí nghiệm đó.

Cách tìm giá trị trung bình cộng

Tính năng làm việc với số âm

1. Tìm trung bình cộng theo phương pháp chuẩn;
2. Tìm trung bình cộng của số âm.
3. Tính trung bình cộng của các số dương.

Câu trả lời của mỗi hành động được viết cách nhau bằng dấu phẩy.

Phân số tự nhiên và thập phân

Khi làm việc với các phân số tự nhiên, chúng nên được quy về một mẫu số chung, nhân với số lượng các số trong mảng. Tử số của câu trả lời sẽ là tổng các tử số đã cho của các phần tử của phân số ban đầu.

Một trong những công cụ chính của phân tích thống kê là tính toán độ lệch chuẩn. Chỉ số này cho phép bạn ước tính độ lệch chuẩn cho một mẫu hoặc cho tổng thể chung. Hãy cùng tìm hiểu cách sử dụng công thức độ lệch chuẩn trong Excel.

Hãy ngay lập tức xác định độ lệch chuẩn là gì và công thức của nó trông như thế nào. Giá trị này là căn bậc hai của trung bình cộng của bình phương hiệu giữa tất cả các giá trị của chuỗi và trung bình cộng của chúng. Có một tên giống hệt cho chỉ số này - độ lệch chuẩn. Cả hai tên là hoàn toàn tương đương.

Nhưng, tất nhiên, trong Excel, người dùng không phải tính toán điều này, vì chương trình thực hiện mọi thứ cho anh ta. Hãy cùng tìm hiểu cách tính độ lệch chuẩn trong Excel.

Tính toán trong Excel

Bạn có thể tính toán giá trị được chỉ định trong Excel bằng cách sử dụng hai hàm đặc biệt STDEV.V(theo mẫu) và STDEV.G(theo dân số chung). Nguyên tắc hoạt động của chúng là hoàn toàn giống nhau, nhưng chúng có thể được gọi theo ba cách, mà chúng ta sẽ thảo luận dưới đây.

Phương pháp 1: Trình hướng dẫn hàm

Phương pháp 2: Tab công thức

Phương pháp 3: Nhập công thức theo cách thủ công

Cũng có một cách mà bạn không cần phải gọi cửa sổ đối số. Để làm điều này, hãy nhập công thức theo cách thủ công.

Như bạn thấy, cơ chế tính độ lệch chuẩn trong Excel rất đơn giản. Người dùng chỉ cần nhập các số từ tập hợp hoặc liên kết đến các ô chứa chúng. Tất cả các tính toán được thực hiện bởi chính chương trình. Việc hiểu chỉ số tính toán là gì và kết quả tính toán có thể áp dụng vào thực tế sẽ khó hơn nhiều. Nhưng hiểu điều này đã thuộc về lĩnh vực thống kê hơn là học cách làm việc với phần mềm.