VARIANCE - Excel & Google Trang tính

Hướng dẫn này trình bày cách sử dụng Hàm VARIANCE trong Excel trong Excel để ước tính phương sai dựa trên một mẫu nhất định.

Tổng quan về hàm VARIANCE

Hàm VARIANCE Tính toán phương sai ước tính dựa trên một mẫu nhất định.

Để sử dụng Hàm VARIANCE Excel Worksheet, 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 VARIANCE Cú pháp và đầu vào:

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

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

Cách tính phương sai trong Excel

Phương sai cho bạn biết mức độ dàn trải các giá trị trong tập dữ liệu so với giá trị trung bình. Nói một cách toán học, phương sai là giá trị 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 (nhưng chúng ta sẽ sớm đạt được điều đó).

Excel cung cấp cho bạn một số hàm để tính toán phương sai - VAR.S, VAR.P, VARA, VARPA và hai hàm cũ hơn, VAR và VARP.

Trước khi chúng ta tìm hiểu các hàm này và tìm hiểu cách sử dụng chúng, hãy nói về phương sai và cách tính phương sai.

Phương sai là gì?

Khi phân tích dữ liệu, bước đầu tiên phổ biến là tính giá trị trung bình. Tất nhiên, đây là một thống kê hữu ích để tính toán, nhưng nó không cung cấp cho bạn bức tranh đầy đủ về những gì đang diễn ra với dữ liệu của bạn.

Lấy tập dữ liệu sau, có thể là một nhóm các kết quả kiểm tra được điểm trên 100:

1	48,49,50,51,52

Giá trị trung bình của phạm vi này là 50 (tính tổng các số và chia cho n, với n là số giá trị).

Tiếp theo, lấy bộ kết quả kiểm tra sau:

1	10,25,50,75,90

Giá trị trung bình của phạm vi này là cũng 50 - nhưng rõ ràng chúng tôi có hai phạm vi dữ liệu rất khác nhau ở đây.

Tự nó, trung bình không thể cho bạn biết bất cứ điều gì về mức độ dàn trải của điểm số. Nó không cho bạn biết nếu tất cả các giá trị được nhóm lại như trong ví dụ đầu tiên hay cách xa nhau như ví dụ thứ hai. Phương sai có thể giúp bạn tìm hiểu điều này.

Phương sai cũng được sử dụng làm điểm cơ sở cho một loạt các thủ tục thống kê phức tạp hơn.

Cách tính phương sai

Hãy làm việc với một ví dụ cơ bản và tính toán phương sai bằng tay. Bằng cách này, bạn sẽ biết những gì đang diễn ra ở hậu trường khi bạn thực sự bắt đầu đưa các hàm phương sai của Excel vào hoạt động.

Giả sử chúng ta có một tập dữ liệu đại diện cho ba thẻ chơi là 4, 6 và 8.

Để tính toán phương sai, bạn thực hiện quá trình này:

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

Đầu tiên, chúng tôi tính toán giá trị trung bình. Chúng tôi biết phạm vi dữ liệu của chúng tôi là 4, 6, 8, vì vậy giá trị trung bình sẽ là:

1	(6 + 4 + 8) / 3 = 6

Tôi đã xác nhận điều này bên dưới bằng Hàm AVERAGE trong Excel <>:

1	= AVERAGE (C4: C6)

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

Tiếp theo, chúng tôi trừ giá trị trung bình cho mỗi giá trị của chúng tôi.

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 lưu trữ trong H4, vì vậy tôi chỉ trừ giá trị đó cho mỗi giá trị trong bảng. Ký hiệu đô la ở đây chỉ "khóa" tham chiếu ô đó thành H4, để khi tôi sao chép nó xuống cột, nó vẫn giữ nguyên.

Kết quả:

Chúng tôi đã có:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Chúng ta cần lấy giá trị trung bình của những khác biệt này từ giá trị trung bình, nhưng giá trị trung bình của ba giá trị này bằng 0! Vì vậy, chúng ta cần nhấn mạnh sự khác biệt, điều mà chúng ta thực hiện bằng cách bình phương chúng.

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

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 rồi, điều đó tốt hơn. Bây giờ sự khác biệt không trung bình bằng 0, chúng tôi có thể tính toán phương sai.

4) Tính giá trị trung bình của chênh lệch bình phương

Ở đây chúng tôi gặp một ngã ba trên đường. Có hai cách để tính toán phương sai và cách bạn sử dụng tùy thuộc vào loại dữ liệu bạn có.

• 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 là bình thường (tí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 tính tổng các giá trị và chia cho n-1

Dữ liệu dân số có nghĩa là bạn có toàn bộ dữ liệu bạn cần, ví dụ: nếu bạn muốn độ tuổi trung bình của giáo viên trong một trường cụ thể và bạn có dữ liệu tuổi của mỗi giáo viên tại trường đó, bạn có dữ liệu dân số.

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. Vì vậy, nếu bạn muốn tuổi trung bình của giáo viên trong cả nước và bạn chỉ có dữ liệu về giáo viên ở một trường, bạn có dữ liệu mẫu.

Trong ví dụ của chúng tôi, chúng tôi có dữ liệu dân số. Chúng tôi chỉ quan tâm đến ba thẻ của mình - đó là dân số và chúng tôi chưa lấy mẫu từ chúng. Vì vậy, chúng ta chỉ có thể lấy giá trị trung bình của các chênh lệch bình phương theo cách thông thường:

1	= AVERAGE (E4: E8)

Vì vậy, phương sai của dân số của chúng ta là 2,666.

Nếu điều này là dữ liệu mẫu (có lẽ chúng tôi đã rút ba thẻ này ra khỏi một tập hợp lớn hơn), chúng tôi sẽ tính ra mức trung bình như sau:

1	Phương sai mẫu = (4 + 0 + 4) / (3 - 1)

Hoặc:

1	Phương sai mẫu = 8/2 = 4

Tại sao Chia cho n-1 Với Dữ liệu Mẫu, Thay vì Chỉ cho n?

Câu trả lời ngắn gọn cho câu hỏi này là "Bởi vì nó đưa ra câu trả lời đúng". Nhưng tôi tưởng tượng bạn sẽ muốn nhiều hơn thế một chút! Đây là một chủ đề phức tạp nên tôi sẽ chỉ giới thiệu tổng quan ngắn gọn ở đây.

Hãy nghĩ về nó như thế này: nếu bạn lấy một mẫu dữ liệu từ một tập hợp, những giá trị đó sẽ có xu hướng gần hơn với giá trị trung bình của mẫu vật so với ý nghĩa của dân số.

Điều này có nghĩa là nếu bạn chỉ chia cho n, bạn sẽ đánh giá thấp phương sai tổng thể một chút. Chia cho n-1 sẽ sửa lỗi cho điều này một chút.

Với bộ ba thẻ của chúng tôi, chúng tôi đang ở một nơi tốt để kiểm tra lý thuyết này. Bởi vì chỉ có ba thẻ, chúng tôi có thể lấy một số lượng nhỏ các mẫu.

Hãy lấy mẫu của hai thẻ. Chúng tôi sẽ chọn một thẻ, đặt lại, xáo trộn và sau đó chọn một thẻ khác. Điều đó có nghĩa là có chín sự kết hợp của hai thẻ mà chúng ta có thể chọn.

Chỉ với chín mẫu khả thi, chúng ta có thể tính toán mọi phương sai mẫu có thể có bằng cả hai phương pháp (chia cho n và chia cho n-1), lấy giá trị trung bình của chúng và xem phương pháp nào cho chúng ta câu trả lời đúng.

Trong bảng dưới đây, tôi đã trình bày mọi thứ. Mỗi hàng của bảng là một mẫu khác nhau và cột B và C hiển thị hai thẻ đã được chọn trong mỗi mẫu. Sau đó, tôi đã thêm hai cột nữa: một cột trong đó tôi tính phương sai của mẫu hai thẻ đó bằng cách chia cho n và một cột khác ở đó tôi chia cho n - 1.

Hãy xem:

Ở bên phải của bảng, tôi đã hiển thị giá trị trung bình của cột D và E.

Giá trị trung bình của cột D, khi chia cho n, cho chúng ta phương sai là 1,333.

Giá trị trung bình của cột E, khi chia cho n-1, cho chúng ta phương sai là 2,666.

Từ ví dụ trước, chúng ta đã biết rằng phương sai của tổng thể là 2,666. Vì vậy, chia cho n-1 khi sử dụng dữ liệu mẫu cho chúng ta ước tính chính xác hơn.

Các hàm Excel để tính phương sai

Bây giờ bạn đã thấy một ví dụ về cách tính phương sai, hãy chuyển sang các hàm Excel.

Bạn có một số tùy chọn ở đây:

• P trả về phương sai cho dữ liệu tổng hợp (sử dụng phương pháp chia cho n)
• NS trả về phương sai cho dữ liệu mẫu (chia cho n-1)
• VAR là một hàm cũ hơn hoạt động theo cách giống hệt như VAR.S
• VARA giống như VAR.S, ngoại trừ nó bao gồm các ô văn bản và giá trị Boolean
• VARPA giống như VAR.P, ngoại trừ nó bao gồm các ô văn bản và giá trị Boolean

Hãy đi qua từng cái một.

Hàm VAR.P trong Excel

VAR.P tính toán phương sai cho dữ liệu tổng thể (sử dụng phương pháp chia cho n). Sử dụng nó như thế này:

1	= VAR.P (C4: C6)

Bạn chỉ xác định một đối số trong VAR.P: phạm vi dữ liệu mà bạn muốn tính phương sai. Trong trường hợp của chúng tôi ở đây, đó là các giá trị thẻ trong C4: C6.

Như bạn thấy ở trên VAR.P trả về 2,666 cho bộ ba thẻ của chúng tôi. Đây là giá trị tương tự mà chúng tôi đã tính toán bằng tay trước đó.

Lưu ý rằng VAR.P hoàn toàn bỏ qua các ô chứa văn bản hoặc giá trị Boolean (TRUE / FALSE). Nếu bạn cần bao gồm những điều này, hãy sử dụng VARPA để thay thế.

Hàm VAR.S trong Excel

VAR.S tính toán phương sai cho dữ liệu mẫu (chia cho n-1). Bạn sử dụng nó như thế này:

1	= VAR.S (C4: C6)

Một lần nữa, chỉ có một đối số - phạm vi dữ liệu của bạn.

Trong trường hợp này VAR.S trả về 4. Chúng ta có cùng một con số ở bước 4 khi chúng ta thực hiện phép tính thủ công ở trên.

VAR.S hoàn toàn bỏ qua các ô 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 VARA để thay thế.

Hàm VAR trong Excel

VAR hoàn toàn tương đương với VAR.S: nó tính toán các phương sai cho dữ liệu mẫu (sử dụng phương pháp n-1). Đây là cách sử dụng nó:

1	= VAR (C4: C6)

VAR là một "chức năng tương thích". Điều này có nghĩa là Microsoft đang trong quá trình loại bỏ chức năng này khỏi Excel. Hiện tại, nó vẫn có sẵn để sử dụng, nhưng bạn nên sử dụng VAR.S để thay thế, để các bảng tính của bạn vẫn tương thích với các phiên bản Excel trong tương lai.

Hàm VARA trong Excel

VARA cũng trả về phương sai của dữ liệu mẫu, nhưng nó có một số khác biệt chính đối với VAR và VAR.S. Cụ thể, nó bao gồm các giá trị Boolean và văn bản trong tính toán của nó:

• 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

Đây là cách bạn sử dụng nó:

1	= VARA (C4: C11)

Chúng tôi đã thêm năm hàng nữa vào bảng: J, Q, K, TRUE và FALSE. Cột D hiển thị cách VARA diễn giải các giá trị này.

Bởi vì chúng tôi có một loạt giá trị thấp mới trong bảng của mình hiện tại, phương sai đã tăng lên 10,268.

Hàm VARPA trong Excel

VARPA tính toán phương sai cho dữ liệu dân số. Nó tương tự như VAR.P, ngoại trừ nó cũng bao gồm các giá trị Boolean và chuỗi văn bản trong phép tính:

• 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	= VARPA (C4: C12)

Chúng tôi đã thêm năm hàng nữa vào bảng: J, Q, K, TRUE và FALSE. Cột D hiển thị cách VARPA diễn giải các giá trị này.

Kết quả của việc thêm nhóm các giá trị thấp hơn này vào dữ liệu, phương sai đã tăng lên 8,984.

Hàm VARIANCE trong Google Trang tính

Hàm CORREL hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel: