Cách Tính độ Lệch Chuẩn, Phương Sai Trong Excel

Tự học Excel dễ dàng bằng cách dùng hướng dẫn này để tính độ lệch chuẩn trong Excel trong nháy mắt.

Trong thống kê, Độ lệch chuẩn được sử dụng để tìm xem mỗi giá trị trong tập dữ liệu khác nhau bao xa so với giá trị trung bình hoặc giá trị trung bình. Điều này giúp chúng tôi hiểu mỗi số được nhóm chặt chẽ như thế nào xung quanh giá trị trung bình.

Có một số kiểu chuyên gia sử dụng độ lệch chuẩn làm thước đo rủi ro cơ bản, chẳng hạn như nhà phân tích bảo hiểm, nhà quản lý danh mục đầu tư, nhà thống kê, nhà nghiên cứu thị trường, đại lý bất động sản, nhà đầu tư chứng khoán, v.v.

Tuy nhiên, tính toán Độ lệch chuẩn trong Excel có thể là một quá trình khó khăn đối với những người mới làm quen với Excel hoặc chưa quen với nó. Đó là lý do tại sao, trong bài viết này, chúng tôi sẽ giải thích Độ lệch chuẩn là gì và cách tính nó trong Excel.

Mục lục

• Độ lệch chuẩn là gì?
• Population so với Sample
• Tính toán độ lệch chuẩn bằng tính toán thủ công
  • Độ lệch tiêu chuẩn Population
  • Độ lệch chuẩn mẫu
  • 1. Tính giá trị trung bình
  • 2. Đối với mỗi số, tính khoảng cách đến trung bình :
  • 3. Bình phương sự khác biệt
  • 4. Tính tổng chênh lệch bình phương
  • 5. Tính phương sai
  • 6. Lấy căn bậc hai của phương sai
• Tính toán lỗi chuẩn trong Excel
• Tính độ lệch chuẩn bằng các hàm tích hợp trong Excel
  • Hàm STDEV.P trong Excel cho độ lệch chuẩn Population
  • Hàm STDEV.S trong Excel cho độ lệch tiêu chuẩn mẫu
  • Hàm STDEVA trong Excel cho độ lệch tiêu chuẩn mẫu
  • Hàm STDEVPA trong Excel cho độ lệch chuẩn Population
  • Hàm STDEV trong Excel
  • Hàm STDEVP trong Excel
• Tính toán độ lệch chuẩn trong Excel bằng cách sử dụng chức năng chèn
• Nhận độ lệch chuẩn bằng cách sử dụng Công cụ phân tích dữ liệu trong Excel
• Cách tính độ lệch chuẩn với tiêu chí IF
  • Hàm DSTDEV cho độ lệch chuẩn mẫu
  • DSTDEV P Hàm cho Độ lệch Chuẩn Population
  • Tiêu chí nhiều hàng
• Tính độ lệch chuẩn có trọng số trong Excel
• Thêm thanh lệch chuẩn trong Excel
• Tính toán phương sai trong Excel

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

Giá trị trung bình cho biết giá trị trung bình trong tập dữ liệu. Và Độ lệch chuẩn thể hiện sự khác biệt giữa các giá trị của tập dữ liệu và giá trị trung bình của chúng. Nói cách khác, độ lệch chuẩn cho bạn biết liệu dữ liệu của bạn có gần với giá trị trung bình hay thay đổi nhiều hay không.

Ví dụ, nếu một giáo viên nói với bạn rằng điểm trung bình của học sinh là 60 (trung bình). Và nếu bạn có danh sách điểm của các học sinh của cô ấy, bạn có thể sử dụng độ lệch chuẩn để tìm ra mức độ chính xác của cô ấy.

Có hai loại độ lệch chuẩn: độ lệch chuẩn tổng thể và độ lệch chuẩn mẫu. Độ lệch chuẩn mẫu – Sample standard deviation (SSD) được tính toán từ các mẫu ngẫu nhiên của tổng thể hoặc dữ liệu trong khi độ lệch chuẩn tổng thể – POopulation standard deviation (PSD) được tính từ toàn bộ dữ liệu tổng thể.

Độ lệch chuẩn càng cao, dữ liệu càng trải rộng khỏi giá trị trung bình và độ lệch chuẩn càng thấp, các giá trị càng gần với giá trị trung bình / giá trị trung bình. Nếu độ lệch chuẩn là 0, tất cả các điểm dữ liệu trong tập dữ liệu đều bằng nhau. Ngoài ra, độ lệch chuẩn cao hơn có nghĩa là giá trị trung bình kém chính xác hơn và độ lệch chuẩn thấp hơn cho thấy giá trị trung bình đáng tin cậy hơn.

Có hai cách khác nhau để tính độ lệch chuẩn trong Excel bằng công thức hoặc hàm tích hợp.

Population so với Sample

Tính toán mẫu là phổ biến vì đôi khi không thể tính toán toàn bộ tập dữ liệu. Trước khi bắt đầu tính độ lệch chuẩn, bạn phải biết bạn có loại dữ liệu nào – toàn bộ dữ liệu hoặc mẫu dữ liệu. Bởi vì bạn phải sử dụng các công thức và hàm khác nhau cho dữ liệu mẫu và dữ liệu Population.

• Dữ liệu Population đề cập đến toàn bộ tập dữ liệu. Nó có nghĩa là dữ liệu có sẵn cho tất cả các thành viên của một nhóm. Độ lệch chuẩn tổng thể tính toán khoảng cách của mỗi giá trị trong tập dữ liệu từ giá trị trung bình của tổng thể. Một ví dụ về dữ liệu Population là điều tra Population.
• Mặt khác, dữ liệu mẫu là tập hợp con của tổng thể. Nó là một tập hợp con đại diện cho toàn bộ Population. Mẫu là một nhóm dữ liệu nhỏ hơn được lấy từ tổng thể. Dữ liệu mẫu được sử dụng khi toàn bộ tổng thể không có sẵn hoặc không đủ để thống kê. Một ví dụ điển hình về mẫu là một cuộc khảo sát.

Tính toán độ lệch chuẩn bằng tính toán thủ công

Tính toán độ lệch chuẩn theo cách thủ công là một quá trình hơi dài, nhưng có thể dễ dàng thực hiện bằng cách sử dụng các công thức. Đầu tiên, bạn cần tính phương sai của dữ liệu và sau đó tìm căn bậc hai của phương sai.

Độ lệch tiêu chuẩn Population

Công thức tính độ lệch chuẩn Population được đưa ra dưới đây:

Công thức

• μ là trung bình cộng
• Xi là các giá trị riêng lẻ trong tập dữ liệu
• N là tổng số giá trị X (tập hợp) trong tập dữ liệu

Độ lệch chuẩn mẫu

Công thức tính độ lệch chuẩn mẫu được đưa ra dưới đây:

Công thức

Quảng cáo

• μ là trung bình cộng
• Xi là các giá trị riêng lẻ trong tập dữ liệu mẫu
• N là tổng số giá trị X (mẫu) trong tập dữ liệu

Khi tính toán độ lệch chuẩn mẫu, chỉ mẫu của tập dữ liệu được xem xét từ toàn bộ tập dữ liệu. Do đó, hiệu chỉnh Bessel (N-1) được sử dụng thay vì N để đưa ra ước lượng tốt hơn về độ lệch chuẩn.

Để tính toán độ lệch chuẩn cho tập dữ liệu dưới đây, hãy làm theo các bước sau:

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

Đầu tiên, bạn cần tính giá trị trung bình mean/average (μ) của tất cả các giá trị trong tập dữ liệu. Để làm điều đó, bạn cộng tất cả các giá trị rồi chia tổng cho số giá trị trong tập dữ liệu. Bạn có thể tìm giá trị trung bình bằng cách sử dụng biểu thức số học thủ công:

96+69+99+57+35+89+55+93+41+21/10=65.5

Bạn cũng có thể sử dụng hàm AVERAGE trong Excel để tính giá trị trung bình:

=AVERAGE(B2:B11)

Ở đây, B2: B11 trong công thức đại diện cho các ô (được biểu thị là số cột theo sau là số hàng) chứa dữ liệu mà chúng ta định tính giá trị trung bình. Thay thế các giá trị này bằng các ô trong trang tính của bạn khi sử dụng công thức.

2. Đối với mỗi số, tính khoảng cách đến trung bình :

Sau đó, bạn cần tìm độ lệch hoặc khoảng cách đến giá trị trung bình bằng cách trừ Giá trị trung bình cho mỗi giá trị trong tập dữ liệu. Để làm điều đó, hãy nhập công thức dưới đây:

=B2-$B$13

hoặc

=(B2-AVERAGE(B2:B11))

$B$13 là giá trị trung bình của dữ liệu. Công thức trên được nhập vào ô D2 và sau đó được áp dụng cho toàn bộ cột bằng cách kéo nó xuống dưới để tìm độ lệch cho cả cột (D2:D13). Đặt con trỏ của bạn ở góc dưới cùng bên phải của ô, tại đây D2. Biểu tượng ‘+’ sẽ xuất hiện; kéo nó xuống.

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

Bây giờ, bình phương sự khác biệt cho từng giá trị bằng cách sử dụng công thức dưới đây:

=D2^2

Sau đó, áp dụng công thức cho toàn bộ cột bằng cách kéo nó xuống. Bình phương sự khác biệt cũng sẽ biến các giá trị âm thành giá trị dương.

4. Tính tổng chênh lệch bình phương

Tiếp theo, tính tổng tất cả các bình phương của độ lệch về giá trị trung bình (Xi-μ) 2 . Đây là công thức để cộng các chênh lệch bình phương:

=SUM(E2:E11)

Nếu bạn có tập dữ liệu lớn, bạn có thể tính toán số lượng giá trị bằng cách:

=B14/10

5. Tính phương sai

Để tính toán phương sai, bạn cần chia sự khác biệt bình phương cho số giá trị.

Cho đến nay, các bước tính toán mẫu và độ lệch chuẩn Population là như nhau. Trong bước này, các công thức sẽ thay đổi một chút đối với cả độ lệch chuẩn, như đã giải thích trước đây.

Đối với phương sai mẫu , bạn cần chia tổng các chênh lệch bình phương cho số giá trị trừ đi 1:

=B14/(10-1)

hoặc

=SUM(E2:E11)/(COUNT(B2:B11)-1)

Bạn có thể sử dụng một trong các công thức để tính phương sai mẫu.

Đối với phương sai tổng thể , bạn cần chia tổng bình phương chênh lệch chỉ cho số giá trị:

=B14/10

hoặc

=SUM(E2:E11)/(COUNT(B2:B11)

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

Cuối cùng, bạn cần lấy căn bậc hai của phương sai trên để có Độ lệch chuẩn.

Độ lệch chuẩn mẫu:

Để có được độ lệch chuẩn của mẫu, hãy lấy căn bậc hai của phương sai mẫu:

=SQRT(B15)

hoặc

=SQRT(SUM(E2:E11)/(COUNT(B2:B11)-1))

Độ lệch tiêu chuẩn Population:

Để có được độ lệch chuẩn tổng thể, hãy tính căn bậc hai của phương sai tổng thể:

=SQRT(B16)

hoặc

=SQRT(SUM(E2:E11)/COUNT(B2:B11))

Tính toán lỗi chuẩn trong Excel

Sai số chuẩn của giá trị trung bình hay đơn giản là sai số chuẩn là một phép đo khác của độ biến thiên, rất giống với độ lệch chuẩn, nhưng khác. Nó là một thước đo về khoảng cách mà mỗi trung bình Population có khả năng so với trung bình mẫu.

Sự khác biệt giữa sai số chuẩn và độ lệch chuẩn là sai số chuẩn sử dụng số liệu thống kê (dữ liệu mẫu) trong khi độ lệch chuẩn sử dụng các tham số (dữ liệu Population). Độ lệch chuẩn thường đo độ biến thiên trong một mẫu đơn trong khi Độ lệch chuẩn đo độ biến thiên trên nhiều mẫu của một tập hợp. Đây là cách bạn có thể tính toán Lỗi chuẩn (SE):

Công thức chung cho sai số chuẩn là độ lệch chuẩn chia cho căn bậc hai của số giá trị trong tập dữ liệu.

= Sample Standard Deviation / Square root of number of values (n)

Để tính toán Lỗi chuẩn (SE), hãy nhập công thức dưới đây:

=STDEV(B2:B11)/SQRT(COUNT(B2:B11))

Ở đây, STDEV(B2:B11)tìm độ lệch chuẩn của mẫu (B2: B11) và SD được chia cho căn bậc hai của số giá trị (n) trong B2: B11. Thay vì COUNT (B2: B11), bạn cũng có thể nhập trực tiếp số lượng giá trị trong tập dữ liệu (10).

Tính độ lệch chuẩn bằng các hàm tích hợp trong Excel

Microsoft Excel bao gồm sáu chức năng khác nhau để tính toán độ lệch chuẩn. Các hàm này giúp tính toán Độ lệch chuẩn trong Excel cực kỳ dễ dàng, cắt giảm thời gian bạn cần dành cho các phép tính. Điểm duy nhất là bạn cần biết chức năng nào sẽ sử dụng khi nào. Chức năng bạn cần sử dụng phụ thuộc vào dữ liệu bạn có – mẫu hoặc Population.

Khi bạn nhập =STDEV vào một ô Excel trống, nó sẽ hiển thị cho bạn sáu phiên bản sau của các hàm độ lệch chuẩn:

• STDEV : Nó được sử dụng để tính toán độ lệch chuẩn cho dữ liệu mẫu. Hàm này là hàm Excel lâu đời nhất (trước Excel 2007) để tìm độ lệch chuẩn. Điều này vẫn tồn tại trong các phiên bản Excel mới nhất cho mục đích tương thích.
• STDEVP : Đây là một phiên bản cũ hơn của độ lệch chuẩn tồn tại để tương thích với Excel 2007 trở về trước. Nó tính toán độ lệch chuẩn dựa trên dữ liệu Population.
• STDEV.S : Đây là phiên bản mới hơn của hàm STEDV (khả dụng từ Excel 2010). Nó được sử dụng để tính toán độ lệch chuẩn cho dữ liệu mẫu.
• STDEV.P : Đây là phiên bản mới hơn của hàm STEDVP trong Excel (khả dụng từ Excel 2010). Nó được sử dụng để tính toán độ lệch chuẩn cho toàn bộ dữ liệu tổng thể.
• STDEVA: Công thức này tính toán độ lệch chuẩn mẫu của tập dữ liệu bằng cách bao gồm văn bản và các giá trị lôgic. Nó rất giống với STDEV.S. Tất cả các giá trị FALSE và giá trị Văn bản được lấy là “0” và TRUE được lấy là “1”.
• STDEVPA : Công thức này tính toán độ lệch chuẩn tổng thể bằng cách bao gồm văn bản và các giá trị lôgic. Nó tương tự như STDEV.P. Tất cả các giá trị FALSE và giá trị Văn bản được lấy là “0” và TRUE được lấy là “1”.

STDEV, STDEVP, STDEV.S, STDEV.P bỏ qua văn bản và giá trị lôgic (TRUE hoặc FALSE) trong tập dữ liệu. Trong hầu hết các trường hợp, bạn sẽ chỉ cần STDEV.P hoặc STDEV.S để thực hiện các phép tính độ lệch chuẩn.

Để bạn hiểu rõ hơn, đây là bản tóm tắt về sáu chức năng:

TÊN CHỨC NĂNG	ĐỘ LỆCH CHUẨN	XỬ LÝ CÁC GIÁ TRỊ LÔGIC VÀ VĂN BẢN	PHIÊN BẢN EXCEL
STDEV.S	Độ lệch chuẩn mẫu	Làm ngơ	2010 – 2021
STDEV.P	Độ lệch tiêu chuẩn Population	Làm ngơ	2010 – 2021
STDEV	Độ lệch chuẩn mẫu	Làm ngơ	2003 – 2021(Có sẵn để tương thích với 2007 trở về trước)
STDEVP	Độ lệch tiêu chuẩn Population	Làm ngơ	2003 – 2021(Có sẵn để tương thích với 2007 trở về trước)
STDEVA	Độ lệch chuẩn mẫu	Đã đánh giá (TRUE = 1, FALSE = 0, Văn bản = 0)	2003 – 2021
STDEVPA	Độ lệch tiêu chuẩn Population	Đã đánh giá (TRUE = 1, FALSE = 0, Văn bản = 0)	2003 – 2021

Hàm STDEV.P trong Excel cho độ lệch chuẩn Population

Nếu tập dữ liệu của bạn đại diện cho toàn bộ tập hợp, bạn có thể sử dụng hàm STDEV.P để tính độ lệch chuẩn tập hợp.

Cú pháp cho hàm STDEV.P là:

=STDEV.P(number1,[number2],…)

Công thức

• number1 là đối số số đầu tiên tương ứng với điểm dữ liệu đầu tiên của dữ liệu Population.
• [number2], .. là đối số số thứ hai tương ứng với điểm dữ liệu thứ hai của dữ liệu Population, v.v.

Hàm phải chứa hai hoặc nhiều giá trị trong các đối số và hàm có thể nhận tối đa 255 đối số số. Bạn có thể nhập số, mảng và tham chiếu ô làm đối số.

Ví dụ:

=STDEV.P(B2:B11)

Đây, B2:B11là phạm vi ô chứa dữ liệu Population. Công thức trên sẽ trả về độ lệch chuẩn cho tổng thể đã cho. Như bạn thấy, chúng tôi nhận được kết quả chính xác là 26,58289 (độ lệch chuẩn Population) như phương pháp thủ công ở trên. Chức năng này tự động thực hiện tất cả các tính toán từng bước từ phương pháp thủ công ở trên trong nền và cung cấp cho bạn kết quả.

Trong trường hợp dữ liệu của bạn chứa bất kỳ giá trị boolean nào (TRUE hoặc FALSE) hoặc giá trị văn bản, hàm này sẽ bỏ qua các giá trị đó và tính toán độ lệch chuẩn với các giá trị còn lại.

Như bạn có thể thấy bên dưới, cùng một công thức ở trên đã tạo ra một kết quả khác vì nó đã bỏ qua các giá trị trong ô B8 và B11.

Hàm STDEV.S trong Excel cho độ lệch tiêu chuẩn mẫu

Nếu tập dữ liệu của bạn đại diện cho tổng thể mẫu, bạn có thể sử dụng hàm STDEV.S để tính toán độ lệch chuẩn của mẫu. Ví dụ, bạn đã tiến hành một bài kiểm tra cho một số lượng lớn học sinh nhưng bạn chỉ có điểm kiểm tra là 10 học sinh, vì vậy bạn có thể sử dụng STDEV.S để tìm độ lệch chuẩn mẫu và áp dụng nó cho toàn bộ tập hợp.

Cú pháp cho hàm STDEV.S là:

=STDEV.S(number1,[number2],…)

Công thức

• number1 là đối số số đầu tiên tương ứng với điểm dữ liệu đầu tiên của dữ liệu mẫu.
• [number2],… là đối số số thứ hai tương ứng với điểm dữ liệu thứ hai của dữ liệu mẫu, v.v.

Bạn có thể nhập số, mảng và tham chiếu ô làm đối số.

Ví dụ:

=STDEV.S(B2:B11)

Công thức trên tính tổng tất cả các bình phương của độ lệch về giá trị trung bình và chia nó cho số trừ 1 (n-1) trong nền và trả về kết quả bên dưới.

Hàm STDEV.S cũng bỏ qua văn bản và giá trị lôgic nếu có bất kỳ giá trị nào trong tập dữ liệu như hình dưới đây.

Hàm STDEVA trong Excel cho độ lệch tiêu chuẩn mẫu

STDEVA là một hàm khác được sử dụng để tính toán độ lệch chuẩn cho một mẫu, nhưng nó khác với STDEV.S chỉ ở cách nó xử lý các giá trị logic và văn bản.

Trong tất cả các hàm trên, giá trị logic và giá trị văn bản bị bỏ qua, nhưng hàm STDEVA chuyển đổi các giá trị đó thành 1s và 0s.

• Các giá trị logic TRUE được tính là “1” và FALSE được tính là “0”. Các giá trị có thể được chứa trong ô, mảng hoặc được nhập trực tiếp vào hàm dưới dạng đối số.
• Tất cả các chuỗi văn bản bao gồm chuỗi trống (“”), các biểu diễn văn bản của số và bất kỳ văn bản nào khác đều được đánh giá là ‘0’.

Để đánh giá độ lệch chuẩn cho một mẫu, bao gồm các giá trị lôgic và văn bản, hãy sử dụng công thức sau:

=STDEVA(B2:B11)

Nếu không có giá trị lôgic hoặc giá trị văn bản nào trong tập dữ liệu, nó sẽ trả về độ lệch chuẩn thông thường.

Hàm STDEVPA trong Excel cho độ lệch chuẩn Population

Excel cũng có một chức năng được gọi là STDEVPA để tính toán độ lệch chuẩn cho một tập hợp bằng cách bao gồm văn bản và các giá trị lôgic. Chức năng này tương tự như STDEVA trong việc xử lý các giá trị văn bản và boolean.

• Các giá trị logic TRUE được tính là “1” và FALSE được tính là “0”.
• Tất cả các chuỗi văn bản bao gồm chuỗi trống (“”), các biểu diễn văn bản của số và bất kỳ văn bản nào khác đều được đánh giá là ‘0’.

Để đánh giá độ lệch chuẩn cho một tập hợp, bao gồm các giá trị lôgic và văn bản, hãy sử dụng công thức dưới đây:

=STDEVPA(B2:B11)

Hàm STDEV trong Excel

Hàm STDEV của Excel rất giống với hàm STDEV.S, nó có thể tính toán độ lệch chuẩn cho dữ liệu mẫu. Nếu bạn đang làm việc trong Excel 2007 hoặc phiên bản cũ hơn, bạn cần sử dụng hàm STDEV để tính độ lệch chuẩn.

Cú pháp cho hàm STDEV:

=STDEV(number1, [number 2],...)

Ví dụ:

=STDEV(B2:B11)

Hàm STDEV tồn tại trong các phiên bản Excel mới hơn cho mục đích tương thích, có nghĩa là nó sẽ bị loại bỏ trong tương lai. Vì vậy, Microsoft đang khuyến nghị người dùng sử dụng STDEV.S thay vì STDEV.

Hàm STDEVP trong Excel

Hàm STDEVP của Excel hoạt động giống hệt như hàm STDEV.P. Nếu bạn đang làm việc trong Excel 2007 hoặc phiên bản cũ hơn, bạn phải sử dụng hàm STDEVP để tính toán độ lệch chuẩn cho dữ liệu Population.

Cú pháp cho hàm STDEVP:

=STDEVP(number1, [number 2],...)

Ví dụ:

=STDEVP(B2:B11)

STDEVP cũng có thể bị xóa khỏi phiên bản Excel trong tương lai.

Tính toán độ lệch chuẩn trong Excel bằng cách sử dụng chức năng chèn

Nếu tất cả các hàm này khó nhớ, bạn có thể sử dụng tùy chọn Chèn Hàm để tính nhanh độ lệch chuẩn. Ngoài ra, bạn có thể tránh các lỗi khi viết công thức bằng cách sử dụng tính năng chức năng Chèn để tự động chèn công thức mong muốn vào ô kết quả bạn đã chọn. Đây là cách bạn có thể làm điều đó:

Đầu tiên, hãy chọn ô mà bạn muốn đầu ra. Sau đó, chuyển đến tab ‘Formulas’ và chọn nút ‘Insert Function’ trong ruy-băng.

Thao tác này sẽ mở hộp thoại Chèn Hàm. Tại đó, hãy tìm kiếm ‘Standard Deviation’ trong trường ‘Search for a function’ hoặc chọn danh mục ‘Statistical’ từ menu thả xuống ‘Or select a category’ và nhấp vào ‘Go’.

Sau đó, cuộn xuống danh sách các chức năng trong cửa sổ ‘Chọn một chức năng’, chọn một chức năng độ lệch chuẩn (STDEV.P, STDEV.S, STDEVA hoặc STDEPA) và nhấp vào ‘OK’.

Thao tác này sẽ mở ra cửa sổ hộp thoại Đối số Hàm với hai trường Số 1 và Số 2. Trong trường Số 1, hãy nhập phạm vi mà bạn muốn tính độ lệch chuẩn. Hoặc, bấm vào mũi tên hướng lên trong trường văn bản và đánh dấu phạm vi từ trang tính. Mỗi đối số số chỉ có thể chiếm tối đa 255 số ô. Nếu số ô vượt quá 255, bạn có thể sử dụng Số 2, Số 3, v.v.

Sau đó, nhấp vào ‘OK’.

Khi bạn nhấp vào OK, nó sẽ tính toán độ lệch chuẩn bằng cách sử dụng hàm đã chọn và hiển thị cho bạn kết quả trong ô bạn đã chọn ban đầu.

Nhận độ lệch chuẩn bằng cách sử dụng Công cụ phân tích dữ liệu trong Excel

Bạn cũng có thể nhận được độ lệch chuẩn như một phần của bản tóm tắt Thống kê mô tả về dữ liệu của mình bằng cách sử dụng công cụ phân tích dữ liệu. Công cụ Phân tích Dữ liệu của Excel có thể tự động tạo ra các giá trị thống kê chính khác nhau, bao gồm giá trị trung bình, giá trị trung bình, Phương sai, độ lệch chuẩn, lỗi chuẩn, v.v.

Đối với tập dữ liệu dưới đây, chúng tôi muốn tính toán thống kê mô tả. Đây là cách bạn có thể làm điều đó:

Để nhận Thống kê Mô tả, hãy chuyển đến tab ‘Dữ liệu’ và nhấp vào công cụ ‘Phân tích Dữ liệu’ từ phần Phân tích.

Trong cửa sổ hộp thoại Phân tích Dữ liệu, hãy chọn ‘Thống kê Mô tả’ trong Công cụ Phân tích và nhấp vào ‘OK’.

Thao tác này sẽ mở hộp thoại Thống kê mô tả trong đó bạn cần định cấu hình các tùy chọn Đầu vào và Đầu ra.

Tùy chọn đầu vào

Đầu tiên, nhập phạm vi biến / giá trị bạn muốn phân tích trong trường ‘Phạm vi đầu vào’.

Bạn có thể nhập phạm vi vào trường theo cách thủ công hoặc nhấp vào nút mũi tên hướng lên ở cuối trường để chọn phạm vi.

Sau đó, chọn phạm vi từ trang tính và nhấp vào nút mũi tên xuống để xác nhận phạm vi.

Tiếp theo, chọn cách bạn muốn tổ chức các biến của mình (hàng hoặc cột). Ở đây, chúng tôi đang chọn ‘Cột’ vì phạm vi đầu vào của chúng tôi nằm trong cột.

Nếu bạn đã chọn hoặc nhập phạm vi (trong Phạm vi đầu vào) có tiêu đề, bạn nên đánh dấu vào tùy chọn ‘Nhãn ở hàng đầu tiên’.

Tùy chọn đầu ra:

Trong trường ‘Phạm vi đầu ra’, hãy nhập phạm vi mà bạn muốn hiển thị kết quả thống kê. Nếu bạn muốn hiển thị kết quả trong trang tính hiện tại hoặc trang tính khác trong sổ làm việc hiện tại, hãy nhấp vào nút radio ‘Phạm vi đầu ra’ và chỉ định phạm vi trong trường bên cạnh.

Nếu bạn muốn hiển thị kết quả trong một bảng tính mới, chỉ cần chọn nút radio ‘New Worksheet Ply’. Hoặc, nếu bạn muốn hiển thị kết quả trong một sổ làm việc mới, hãy chọn tùy chọn ‘Sổ làm việc Mới’.

Cuối cùng, kiểm tra tùy chọn ‘Thống kê tóm tắt’ và nhấp vào ‘OK’.

Và bạn sẽ nhận được tất cả các thống kê cần thiết mà bạn muốn bao gồm Lỗi chuẩn, Độ lệch chuẩn, Phương sai mẫu, v.v.

Cách tính độ lệch chuẩn với tiêu chí IF

Bên cạnh sáu hàm Độ lệch chuẩn nêu trên, Excel còn có thêm hai hàm nữa là DSTDEV và DSTDEVP để tính độ lệch chuẩn với điều kiện IF.

• Hàm DSTDEV được sử dụng để tính toán độ lệch chuẩn của dữ liệu được trích xuất từ ​​tập dữ liệu mẫu phù hợp với tiêu chí đã cho.
• Hàm DSTDEVP được sử dụng để tính toán độ lệch chuẩn của dữ liệu được trích xuất từ ​​tập dữ liệu tổng hợp phù hợp với tiêu chí đã cho.

Hàm DSTDEV cho độ lệch chuẩn mẫu

Cú pháp cho hàm DSTDEV:

=DSTDEV(database, field, criteria)

• Cơ sở dữ liệu – Phạm vi ô (bảng) nơi chứa các mục nhập dữ liệu có giá trị bạn muốn tính toán độ lệch chuẩn. Phạm vi phải bao gồm các tiêu đề trong hàng đầu tiên.
• Trường: Trường này chỉ định trường hoặc cột chứa các số bạn muốn tính độ lệch chuẩn. Bạn cần chỉ định tên trường (tức là nhãn cột hoặc tiêu đề) được đặt trong dấu ngoặc kép hoặc số trường (tức là số cột) trong bảng.
• Tiêu chí : Phạm vi ô chứa tiêu chí của bạn ở đâu. Phạm vi tiêu chí phải chứa ít nhất một nhãn cột phù hợp với tiêu đề cơ sở dữ liệu của bạn và một ô bên dưới nhãn cột chỉ định điều kiện từ cột. Nó có thể bao gồm nhiều hàng để chỉ định nhiều điều kiện.

Ví dụ:

Giả sử bạn có tập dữ liệu dưới đây, nơi bạn cần tính độ lệch chuẩn dựa trên các điều kiện:

Ví dụ:

Ví dụ: để tìm độ lệch chuẩn mẫu của điểm số thu được trong môn toán của tất cả học sinh, hãy nhập một trong các công thức dưới đây:

=DSTDEV(A1:E14,"Score",G1:H2)

hoặc

=DSTDEV(A1:E14,5,G1:H2)

Công thức trên tìm tất cả các điểm tương ứng với môn Toán và tính độ lệch chuẩn cho các điểm đó. Bạn cần tạo một phạm vi tiêu chí riêng biệt (G1: H2) như hình dưới đây và nhập công thức vào ô trống.

DSTDEV P Hàm cho Độ lệch Chuẩn Population

Cú pháp cho hàm DSTDEVP:

=DSTDEVP(database, field, criteria)

Công thức

• Cơ sở dữ liệu – Phạm vi ô (bảng) nơi chứa các mục nhập dữ liệu có giá trị bạn muốn tính toán độ lệch chuẩn.
• Trường: Điều này chỉ định trường hoặc cột nơi đặt các giá trị bạn muốn tính độ lệch chuẩn.
• Tiêu chí : Phạm vi ô chứa tiêu chí của bạn ở đâu.

Ví dụ 1:

Ví dụ: để tìm độ lệch chuẩn Population của điểm số thu được trong môn toán của tất cả học sinh, hãy nhập các công thức dưới đây:

=DSTDEVP(A1:E14,"Score",G1:H2)

Ví dụ 2:

Để tìm độ lệch chuẩn Population của điểm số thu được trong môn toán của học sinh từ 14 tuổi trở lên, hãy nhập công thức dưới đây:

=DSTDEVP(A1:E14,"Score",G1:I2)

Ký tự đại diện

Bạn cũng có thể sử dụng các ký tự đại diện sau trong tiêu chí liên quan đến văn bản để tính toán độ lệch chuẩn:

• * – Phù hợp với bất kỳ ký tự nào với bất kỳ số tiền nào
• ?– Khớp với bất kỳ ký tự đơn nào
• ~– Tìm kiếm * hoặc? nhân vật trong tìm kiếm.

Tiêu chí nhiều hàng

Bạn có thể bao gồm nhiều hơn một hàng hoặc cột trong phạm vi tiêu chí để tính toán độ lệch chuẩn bằng cách sử dụng logic OR hoặc AND. Nếu bạn thêm nhiều hơn một hàng trong phạm vi tiêu chí, các hàm sẽ sử dụng logic HOẶC (TRUE nếu ít nhất một trong các điều kiện là ĐÚNG) để tính toán độ lệch chuẩn. Trong khi nếu bạn thêm nhiều hơn một cột trong phạm vi tiêu chí, các hàm sẽ sử dụng logic AND (TRUE nếu tất cả các điều kiện là ĐÚNG) để đánh giá.

=DSTDEV(A1:E14,"Score",G1:G3)

Tính độ lệch chuẩn có trọng số trong Excel

Thông thường, khi chúng tôi tính toán độ lệch chuẩn cho một tập dữ liệu, tất cả các giá trị trong tập dữ liệu đều có trọng số hoặc tầm quan trọng bằng nhau. Tuy nhiên, trong một số trường hợp, mỗi giá trị có trọng số khác nhau trong tập dữ liệu và một số giá trị có trọng số cao hơn những giá trị khác vì một số giá trị quan trọng hơn những giá trị khác.

Trong những trường hợp như vậy, bạn không thể sử dụng các hàm tích hợp ở trên để tính độ lệch chuẩn. Vì vậy, bạn cần sử dụng các hàm SUM, SUMPRODUCT và SQRT khác để tính toán độ lệch chuẩn có trọng số theo cách thủ công. Hãy cùng chúng tôi xem cách tính độ lệch chuẩn có trọng số trong Excel.

Giả sử bạn có tập dữ liệu trong đó cột đầu tiên chứa các giá trị dữ liệu và cột thứ hai chứa trọng số của từng giá trị đó:

Đầu tiên, bạn cần tính giá trị trung bình hoặc giá trị trung bình có trọng số cho dữ liệu đã cho. Bạn có thể làm điều đó với công thức sau:

=SUMPRODUCT(value_range, weight_range)/SUM(weight_range)

Công thức,

• value_range – Phạm vi hoặc mảng số.
• weight_range – Phạm vi ô có trọng số

Ví dụ:

=SUMPRODUCT(A2:A10, B2:B10)/SUM(B2:B10)

Trong công thức trên, hàm SUMPRODUCT nhân từng giá trị (cột A) với trọng số của nó (cột B) và tính tổng kết quả. Sau đó, kết quả SUMPRODUCT được chia cho tổng trọng số dữ liệu để tạo ra giá trị trung bình có trọng số.

Khi bạn có giá trị trung bình có trọng số, bạn có thể tính toán độ lệch chuẩn có trọng số.

Để tính toán độ lệch chuẩn cho dữ liệu Population , hãy nhập công thức dưới đây:

=SQRT(SUMPRODUCT((value_range - weighted_mean)^2, weight_range)/SUM( weight_range))

Ví dụ:

=SQRT(SUMPRODUCT((A2:A10-B12)^2, B2:B10)/SUM(B2:B10))

Trong công thức trên, giá trị trung bình có trọng số tính toán được trừ cho mỗi giá trị dữ liệu (cột A) và mỗi kết quả được bình phương. Sau đó, mỗi kết quả bình phương được nhân với trọng số dữ liệu (cột B) bằng cách sử dụng hàm SUMPRODUCT.

Sau đó, kết quả SUMPRODUCT được chia cho SUM của các trọng số. Sau đó, căn bậc hai của kết quả được tính bằng hàm SQRT để tìm giá trị độ lệch chuẩn.

Để tính toán độ lệch chuẩn cho dữ liệu mẫu , hãy nhập công thức dưới đây:

=SQRT(SUMPRODUCT((value_range - weighted_mean)^2, weight_range)/SUM( weight_range)-1)

Ví dụ:

=SQRT(SUMPRODUCT((A2:A10-B12)^2, B2:B10)/SUM(B2:B10)-1)

Sự khác biệt duy nhất trong công thức này so với công thức trên là chúng tôi đã thêm ‘-1’ vào tổng trọng số của dữ liệu Population.

Thêm thanh lệch chuẩn trong Excel

Bạn cũng có thể thêm các thanh độ lệch chuẩn để hình dung biên độ lệch chuẩn của mình bằng cách sử dụng các thanh lỗi Excel. Lỗi Excel là một phần của các phần tử biểu đồ cho phép bạn thể hiện sự thay đổi và đo lường của dữ liệu. Để thêm các thanh độ lệch chuẩn vào biểu đồ của bạn, hãy làm theo các bước sau:

Đầu tiên, hãy tạo biểu đồ hoặc đồ thị cho tập dữ liệu của bạn. Để làm điều đó, hãy chọn phạm vi ô, chuyển đến tab ‘Chèn’ và chọn một tùy chọn đồ thị từ nhóm Biểu đồ.

Khi biểu đồ được chèn, hãy chọn biểu đồ bằng cách nhấp vào bất kỳ đâu trên biểu đồ, sau đó nhấp vào nút ‘Thành phần biểu đồ’ (+).

Sau đó, nhấp vào tùy chọn ‘Thanh Lỗi’ và chọn ‘Độ lệch Chuẩn’.

Kết quả là, các thanh độ lệch chuẩn sẽ được chèn cho tất cả các điểm dữ liệu như hình dưới đây.

Tính toán phương sai trong Excel

Khi tính toán độ lệch chuẩn của dữ liệu, bạn cũng cần phải tính cả phương sai. Phương sai là sự thay đổi trong dữ liệu, cũng là bình phương của độ lệch chuẩn. Tương tự như độ lệch chuẩn, nó cũng có 6 hàm tích hợp mà bạn có thể sử dụng để tính toán phương sai của dữ liệu của mình. Tuy nhiên, Microsoft khuyến nghị người dùng sử dụng VAR.S và VAR.P để tính toán phương sai của dữ liệu mẫu và dữ liệu Population tương ứng.

Để tính toán phương sai dựa trên dữ liệu mẫu , hãy sử dụng công thức dưới đây:

=VAR.S(B2:B15)

Công thức trên tính toán phương sai mẫu cho phạm vi B2: B15 và hiển thị kết quả trong ô E2.

Để tính toán phương sai dựa trên dữ liệu tổng hợp , hãy sử dụng công thức dưới đây:

=VAR.P(B2:B15)

Đó là nó. Đó là khóa học cơ bản duy nhất bạn cần để tính độ lệch chuẩn trong Excel. Bây giờ, hãy tiếp tục và thể hiện các kỹ năng mới có được của bạn!