Cách tính phương sai trong Excel Cách tính phương sai mẫu bằng hàm VAR, VAR.S và VARA trong Excel Cách tính phương sai dân số trong Excel
Trong hướng dẫn này, chúng ta sẽ xem xét cách thực hiện phân tích phương sai trong Excel và sử dụng công thức nào để xác định phương sai của mẫu và tổng thể.

Bạn đang xem: Cách tính phương sai trong excel

Phương sai là một trong những công cụ hữu ích nhất trong lý thuyết xác suất và thống kê. Trong khoa học, nó mô tả mức độ chênh lệch của mỗi số trong một tập dữ liệu so với giá trị trung bình. Trong thực tế, nó thường cho thấy bao nhiêu đã thay đổi. Ví dụ, nhiệt độ gần xích đạo có ít phương sai hơn so với các vùng khí hậu khác. Trong bài viết này, chúng ta sẽ thảo luận về cách tính phương sai trong Excel.

Phương sai là gì?

phân tán là thước đo độ biến thiên của một tập dữ liệu cho biết mức độ lan truyền của sự không giống nhau. Về mặt toán học, giá trị này được định nghĩa là giá trị trung bình của bình phương chênh lệch so với giá trị trung bình.

Để hiểu rõ hơn những gì bạn đang thực sự tính toán với phương sai, hãy xem xét ví dụ đơn giản này.

Giả sử sở thú địa phương của bạn có 5 con hổ tuổi 14, 10, 8, 6 và 2 tuổi.

Thực hiện theo các bước đơn giản sau để tìm các giá trị ngoại lệ:

Tính trung bình cộng (trung bình đơn giản) của năm số:

Trừ trung bình từ mỗi số để tìm sự khác biệt. Để hình dung điều này, hãy vẽ sự khác biệt trên biểu đồ:

Bình phương mỗi chênh lệch Tính giá trị trung bình của các chênh lệch bình phương.

Vậy phương sai là 16. Nhưng con số đó thực sự có ý nghĩa gì?

Trong thực tế, phương sai chỉ cung cấp cho bạn một ý tưởng rất chung chung về phương sai của một tập dữ liệu. Giá trị 0 có nghĩa là không có biến thể, nghĩa là tất cả các số trong tập dữ liệu đều giống nhau. Số càng lớn, dữ liệu càng phổ biến.

Ví dụ này là về phương sai dân số (tức là 5 con hổ là toàn bộ nhóm mà bạn quan tâm). Nếu dữ liệu của bạn là mẫu từ một tổng thể lớn hơn, thì bạn cần tính phương sai mẫu bằng công thức hơi khác.

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

Excel có 6 hàm tích hợp để thực hiện phương sai: VAR, VAR.S, VARP, VAR.P, VARA và VARPA.

Sự lựa chọn của bạn về công thức phương sai được xác định bởi các yếu tố sau:

Phiên bản Excel bạn đang sử dụng. Bất kể bạn đang tính phương sai mẫu hay phương sai dân số. Bạn có muốn đánh giá hoặc bỏ qua các giá trị văn bản và boolean không.

Hàm phương sai trong Excel

Bảng bên dưới cung cấp thông tin tổng quan về các tùy chọn hàm có sẵn trong Excel để giúp bạn chọn công thức phù hợp nhất với nhu cầu của mình.

Tên	phiên bản Excel	Loại dữ liệu	Văn bản và logic
VAR	2000 – 2019 năm	Vật mẫu	Làm ngơ
VAR.S	2010 – 2019 năm	Vật mẫu	Làm ngơ
VARA	2000 – 2019 năm	Vật mẫu	Đánh giá nó
VARP	2000 – 2019 năm	Mọi người	Làm ngơ
VAR.P	2010 – 2019 năm	Mọi người	Làm ngơ
VARPA	2000 – 2019 năm	Mọi người	Đánh giá nó

VAR.S kiện VARA và VAR.P kiện VARPA

VARA và VARPA chỉ khác với các hàm phương sai khác ở cách chúng xử lý các giá trị boolean và văn bản trong tham chiếu. Bảng sau đây tóm tắt biểu diễn văn bản của các số và giá trị logic sẽ được đánh giá.

loại đối số	VAR, VAR.S, VARP, VAR.P	VARA VÀ WARPA
Giá trị Boolean trong mảng và tham chiếu	Làm ngơ	Đánh giá (TRUE = 1, FALSE = 0)
Biểu diễn văn bản của các số trong mảng và tham chiếu	Làm ngơ	Đánh giá là không
Các giá trị logic và biểu diễn văn bản của các số được nhập trực tiếp vào các đối số	Đánh giá (TRUE = 1, FALSE = 0)
ô trống	Làm ngơ

Cách tính phương sai mẫu bằng hàm VAR, VAR.S và VARA trong Excel

Một vật mẫu là một tập hợp dữ liệu được rút ra từ toàn bộ dân số. Phương sai tính được từ mẫu được gọi là phương sai mẫu.

Ví dụ, nếu bạn muốn biết mọi người cao bao nhiêu, về mặt kỹ thuật, bạn sẽ không thể đo được tất cả mọi người trên trái đất. Giải pháp là lấy một mẫu dân số, chẳng hạn 1000 người và ước tính chiều cao của toàn bộ dân số dựa trên mẫu đó.

Phương sai mẫu được tính theo công thức sau:

Ở đâu:

x̄ là giá trị trung bình (trung bình đơn giản) của các giá trị mẫu, n là kích thước mẫu, tức là số lượng giá trị trong mẫu.

Có 3 hàm để tìm phương sai mẫu trong Excel: VAR, VAR.S và VARA.

Hàm VAR trong Excel

Đây là hàm Excel lâu đời nhất để ước tính phương sai dựa trên một mẫu. Hàm VAR có sẵn trong tất cả các phiên bản Excel 2000-2019.

VAR (#1, Thương)

Ghi chú. Trong Excel 2010, hàm VAR đã được thay thế bằng VAR.S, cung cấp độ chính xác cao hơn. Mặc dù VAR vẫn có sẵn để tương thích ngược nhưng bạn nên sử dụng VAR.S trong các phiên bản Excel hiện tại.

Hàm VAR.S trong Excel

Đây là một bản sao hiện đại của hàm VAR trong Excel. Sử dụng hàm VAR.S để tìm phương sai mẫu trong Excel 2010 trở lên.

Xem thêm: Cách tính độ dài cạnh huyền tam giác vuông (cực dễ), Công thức tính cạnh huyền tam giác vuông

VAR.S (Số 1, Thương)

Hàm VARA trong Excel

Hàm Excel VARA trả về phương sai mẫu dựa trên một tập hợp các số, văn bản và giá trị boolean, như được hiển thị trong bảng này.

VAR (giá trị 1, Thương)

Công thức phương sai mẫu trong Excel

Khi làm việc với một tập hợp dữ liệu số, bạn có thể sử dụng bất kỳ hàm nào ở trên để tính phương sai mẫu trong Excel.

Ví dụ, hãy tìm phương sai của một mẫu gồm 6 phần tử (B2:B7). Để làm điều này, bạn có thể sử dụng một trong các công thức sau:

=VAR(B2:B7)

=VAR.S(B2:B7)

=VARA(B2:B7)

Như được hiển thị trong ảnh chụp màn hình, tất cả các công thức trả về cùng một kết quả (làm tròn đến 2 chữ số thập phân):

Để kiểm tra kết quả, hãy tính toán biến theo cách thủ công:

Tìm giá trị trung bình bằng cách sử dụng hàm AVERAGE:=AVERAGE(B2:B7) Giá trị trung bình đi đến bất kỳ ô trống nào, chẳng hạn như B8. Trừ giá trị trung bình của mỗi số trong mẫu:=B2-$B$8Chênh lệch đi đến cột C, bắt đầu từ C2. Bình phương từng chênh lệch và đặt kết quả vào cột D, bắt đầu từ D2:=C2^2Bình phương sự khác biệt và chia cho kết quả bằng số phần tử trong mẫu trừ đi 1: =SUM(D2:D7)/(6-1 )

Như bạn có thể thấy, kết quả tính toán biến theo cách thủ công hoàn toàn giống với số được trả về bởi các hàm có sẵn của Excel:

Nếu tập dữ liệu của bạn chứa Hợp lý và/hoặc chữ thì hàm VARA sẽ trả về một kết quả khác. Lý do là VAR và VAR.S bỏ qua bất kỳ giá trị nào khác ngoài số trong tham chiếu, trong khi VARA đánh giá giá trị văn bản là 0, TRUE là 1 và FALSE là 0. Vì vậy, hãy cẩn thận khi chọn hàm phương sai cho tính toán của bạn phụ thuộc vào việc bạn muốn xử lý hay bỏ qua văn bản và logic.

Cách tính phương sai dân số trong Excel

Mọi người tất cả đều là thành viên của một nhóm nhất định, nghĩa là tất cả các quan sát trong lĩnh vực nghiên cứu. Phương sai dân số mô tả cách các điểm dữ liệu được phân phối trong toàn bộ dân số.

Phương sai dân số có thể được tìm thấy bằng cách sử dụng công thức này:

Ở đâu:

x̄ là giá trị trung bình của tổng thể, n là kích thước của tổng thể, tức là tổng số giá trị trong tổng thể.

Có 3 hàm để tính phương sai tổng thể trong Excel: VARP, VAR.P và VARPA.

Hàm VARP trong Excel

Hàm Vkv của Excel trả về phương sai dân số dựa trên toàn bộ bộ số. Nó có sẵn trong tất cả các phiên bản Excel 2000 đến 2019.

Vpeg (#1, Thương)

Ghi chú. Trong Excel 2010, VARP đã được thay thế bằng VAR.P, nhưng được giữ lại để tương thích ngược. Bạn nên sử dụng VAR.P trong các phiên bản Excel hiện tại vì không có gì đảm bảo rằng các hàm VARP sẽ sẵn dùng trong các phiên bản Excel trong tương lai.

Hàm VAR.P trong Excel

Đây là phiên bản nâng cao của hàm VARP có sẵn trong Excel 2010 trở lên.

VAR.P (Số 1, Thương)

Hàm VARPA trong Excel

Hàm VARPA tính toán phương sai tổng thể dựa trên toàn bộ tập hợp số, văn bản và giá trị logic. Nó có sẵn trong tất cả các phiên bản Excel 2000 đến 2019.

VAR (giá trị 1, Thương)

Công thức phương sai dân số trong Excel

Trong ví dụ tính toán một biến mẫu, chúng tôi đã tìm thấy phương sai của 5 điểm kiểm tra, giả định rằng những điểm số này được chọn bởi một nhóm học sinh lớn hơn. Nếu bạn thu thập dữ liệu về tất cả học sinh trong một nhóm, dữ liệu này sẽ đại diện cho toàn bộ dân số và bạn sẽ tính toán phương sai dân số bằng cách sử dụng các hàm trên.

Giả sử chúng ta có kết quả kiểm tra của một nhóm gồm 10 sinh viên (B2:B11). Ước tính tạo nên toàn bộ dân số chung, vì vậy chúng tôi sẽ phạm sai lầm với các công thức sau:

=VARP(B2:B11)

=VAR.P(B2:B11)

=VARPA(B2:B11)

Và tất cả các công thức sẽ trả về cùng một kết quả:

Để đảm bảo rằng Excel đã tạo phương sai chính xác, bạn có thể kiểm tra điều này bằng công thức biến thủ công được hiển thị trong ảnh chụp màn hình bên dưới:

Nếu một số học sinh trượt bài kiểm tra và có điểm N/A thay vì điểm, hàm VARPA sẽ trả về một kết quả khác. Lý do là VARPA đánh giá null trên giá trị văn bản, trong khi VARP và VAR.P bỏ qua giá trị văn bản và boolean trong tham chiếu. Vui lòng xem VAR.P v. VARPA để biết đầy đủ chi tiết.

Công thức phương sai trong Excel – lưu ý khi sử dụng

Để phân tích đúng phương sai trong Excel, hãy làm theo các quy tắc đơn giản sau:

Cung cấp đối số dưới dạng giá trị, mảng hoặc tham chiếu ô. Trong Excel 2007 trở lên, bạn có thể cung cấp tối đa 255 đối số tương ứng với một mẫu hoặc tổng thể; trong Excel 2003 và các phiên bản mới hơn – tối đa 30 đối số. Chỉ để đánh giá con số trong tham chiếu, bỏ qua khoảng trắng, văn bản và giá trị boolean, hãy sử dụng hàm VAR hoặc VAR.S để tính phương sai mẫu và VARP hoặc VAR.P để xác định phương sai tổng thể. Để đánh giá hợp lý Và chữ giá trị trong liên kết, hãy sử dụng chức năng VARA hoặc VARPA. Cung cấp ít nhất hai giá trị số theo công thức phương sai tiêu chuẩn và ít nhất giá trị số vào công thức phương sai tổng thể trong Excel, nếu không thì #DIV/0! Đã xảy ra lỗi. Các đối số chứa văn bản không thể hiểu là số gây ra lỗi #VALUE! lỗi.

Phương sai so với Độ lệch chuẩn trong Excel

Phương sai chắc chắn là một khái niệm hữu ích trong khoa học, nhưng nó cung cấp rất ít thông tin thực tế. Ví dụ: chúng tôi đã tìm thấy độ tuổi của quần thể hổ tại một sở thú địa phương và tính phương sai là 16. Câu hỏi đặt ra là làm thế nào chúng ta có thể thực sự sử dụng con số này?

Bạn có thể sử dụng phương sai để tính toán độ lệch chuẩn, đây là thước đo tốt hơn nhiều về mức độ biến thiên trong tập dữ liệu.

Độ lệch chuẩn được tính là căn bậc hai của phương sai. Vì vậy, chúng tôi lấy căn bậc hai của 16 và chúng tôi nhận được độ lệch chuẩn là 4.

Kết hợp với giá trị trung bình, độ lệch chuẩn có thể xác định độ tuổi của hầu hết các con hổ. Ví dụ: nếu giá trị trung bình là 8 và độ lệch chuẩn là 4, thì hầu hết hổ trong vườn thú đều từ 4 tuổi (8–4) đến 12 tuổi (8+4).

Microsoft Excel có các chức năng đặc biệt để xác định độ lệch chuẩn của mẫu và tổng thể. Bạn có thể tìm thấy lời giải thích chi tiết về tất cả các hàm trong hướng dẫn này: Cách tính Độ lệch chuẩn trong Excel.

Đây là cách tạo phương sai trong Excel. Để xem kỹ hơn các công thức được thảo luận trong hướng dẫn này, bạn có thể tải xuống sổ làm việc mẫu của chúng tôi để tính phương sai trong Excel. Cảm ơn bạn đã đọc và tôi hy vọng sẽ gặp bạn trên blog của tôi Dịch vụ kế toán tại Hà Nội – bdkhtravinh.vn tuần tới!