Cách tính khoảng tin cậy trong Excel

Tổng quan về hàm tính khoảng tin cậy trong Excel

Trong Microsoft Excel, CONFIDENCE ban đầu là hàm trả về khoảng tin cậy cho trung bình tổng thể. Tuy nhiên, hàm này đã bị loại bỏ và được thay thế bằng hai hàm khác, chính xác hơn tùy theo mục đích. Chúng ta nên dùng hàm mới vì hàm cũ có thể không còn tồn tại trong các phiên bản Excel tương lai.

Cú pháp và tham số của hàm CONFIDENCE.NORM

Hàm CONFIDENCE.NORM trong Excel được dùng để tính khoảng tin cậy cho trung bình tổng thể, giả định phân phối chuẩn.

Cú pháp của hàm CONFIDENCE.NORM như sau:

=CONFIDENCE.NORM(alpha, standard_dev, size)

Các tham số gồm:

• alpha: Mức ý nghĩa (1 — mức tin cậy). Ví dụ, với khoảng tin cậy 95%, alpha sẽ là 0,05.
• standard_dev: Độ lệch chuẩn của mẫu.
• size: Cỡ mẫu.

Cú pháp và tham số của hàm CONFIDENCE.T

Hàm CONFIDENCE.T trong Excel được thiết kế để tính khoảng tin cậy cho trung bình tổng thể, sử dụng phân phối t của Student.

Cú pháp của hàm CONFIDENCE.T là:

=CONFIDENCE.T(alpha, standard_dev, size)

Các tham số gồm:

• alpha: Mức ý nghĩa (1 — mức tin cậy).
• standard_dev: Độ lệch chuẩn của mẫu.
• size: Cỡ mẫu.

Khi nào dùng hàm khoảng tin cậy nào?

Trong thực tế, nếu chúng ta không chắc về phân phối của tổng thể hoặc khi cỡ mẫu nhỏ, an toàn hơn là dùng hàm CONFIDENCE.T. Khi độ lệch chuẩn của tổng thể chưa biết, tức là chúng ta phải tự tính độ lệch chuẩn, tốt nhất là dùng CONFIDENCE.T.

Chúng ta thường dùng CONFIDENCE.NORM khi:

• Cỡ mẫu lớn.
• Độ lệch chuẩn của tổng thể đã biết (không phải tự tính).
• Chúng ta tin rằng phân phối của tổng thể là chuẩn, hoặc chúng ta áp dụng Định lý giới hạn trung tâm.

Những hướng dẫn này sẽ giúp bạn hiểu rõ hơn khi nào nên dùng hàm nào.

Giờ chúng ta đã nắm được cách tính khoảng tin cậy trong Excel, hãy áp dụng vào một ví dụ.

Cách tính khoảng tin cậy trong Excel: Ví dụ minh họa

Giả sử bạn, với vai trò nhà phân tích dữ liệu, được giao nhiệm vụ ước lượng thời gian phản hồi trung bình (tính theo giờ) của một nhóm hỗ trợ khách hàng. Các bên liên quan cấp cao muốn đánh giá hiệu quả của đội hỗ trợ.

Là nhà phân tích, bạn thu thập dữ liệu thời gian phản hồi cho một mẫu ngẫu nhiên gồm 40 yêu cầu của khách hàng. Sau đó bạn lập bảng dữ liệu trong một trang tính Excel như dưới đây (đơn vị: giờ):

Dữ liệu thời gian phản hồi trung bình (Ảnh này & tất cả ảnh chụp màn hình bên dưới do tác giả cung cấp)

Hãy lần lượt tìm khoảng tin cậy:

• Bước 1: Tính trung bình mẫu. Tại một ô mới, giả sử F6, dùng hàm AVERAGE để tính trung bình mẫu.

=AVERAGE(A2:A41)

Tính trung bình mẫu.

• Bước 2: Tính độ lệch chuẩn. Giả sử tại ô F8, dùng hàm STDDEV.S để tính độ lệch chuẩn mẫu.

=STDEV.S(A2:A41)

Lý do dùng STDEV.S (độ lệch chuẩn mẫu) thay vì STDEV.P (độ lệch chuẩn tổng thể) là vì chúng ta đang làm việc với một mẫu dữ liệu (như nêu trong tình huống) chứ không phải toàn bộ tổng thể.

Tính độ lệch chuẩn mẫu.

• Bước 3: Tính sai số biên. Giả sử tại ô F10, dùng hàm CONFIDENCE.T để tính sai số biên cho khoảng tin cậy 95%.

=CONFIDENCE.T(0.05, F8, 40)

Lý do dùng hàm CONFIDENCE.T trong ví dụ này là vì chúng ta không biết độ lệch chuẩn của tổng thể trước khi phân tích và phải tự tính.

Chúng ta cũng không có thông tin rằng phân phối là chuẩn. Do đó, CONFIDENCE.T là hàm phù hợp nhất cho tình huống này.

Tính sai số biên.

• Bước 4: Xác định khoảng tin cậy. Khoảng tin cậy cuối cùng được xác định bằng cách tìm cận dưới và cận trên.

Cận dưới được tính bằng:

LOWER BOUND =(SAMPLE MEAN - MARGIN OF ERROR)

Tính cận trên.

Tương tự, cận trên được tính bằng:

UPPER BOUND =(SAMPLE MEAN + MARGIN OF ERROR)

Tính cận dưới

Giờ đây, chúng ta có thể báo cáo rằng với mức tin cậy 95%, thời gian phản hồi trung bình của đội hỗ trợ khách hàng nằm trong khoảng từ 3,00 giờ đến 3,77 giờ, thay vì chỉ đưa ra giá trị trung bình là 3,39 giờ.

Thông tin này hữu ích để ban quản lý đánh giá hiệu suất của đội hỗ trợ và đưa ra quyết định về nhân sự, đào tạo, cũng như cải tiến quy trình nhằm nâng cao mức độ hài lòng của khách hàng với sự tự tin cao hơn.

Lỗi thường gặp & Cách khắc phục

Khi dùng hàm CONFIDENCE trong Excel, bao gồm các biến thể CONFIDENCE.NORM và CONFIDENCE.T, bạn có thể gặp các vấn đề phổ biến sau:

Lỗi #NUM!

Lỗi #NUM! trong Excel cho thấy vấn đề số học với công thức. Lỗi này xảy ra nếu:

• độ lệch chuẩn nhỏ hơn hoặc bằng 0,
• cỡ mẫu nhỏ hơn 2, hoặc
• alpha (mức ý nghĩa) nhỏ hơn hoặc bằng 0 hoặc lớn hơn hoặc bằng 1.

Độ lệch chuẩn bằng 0 ngụ ý mọi điểm dữ liệu đều giống nhau, điều này không có ý nghĩa khi tính khoảng tin cậy. Độ lệch chuẩn âm là bất khả về mặt toán học. Nếu bạn dùng độ lệch chuẩn đã tính sẵn, hãy bảo đảm đó là số dương.

Với cỡ mẫu nhỏ hơn 2, không thể tính một ước lượng có ý nghĩa cho trung bình tổng thể hoặc mức biến thiên của nó. Vì vậy, hãy chắc chắn rằng bạn nhập đúng cỡ mẫu.

Chọn giá trị alpha lớn hơn 0 và nhỏ hơn 1. Các lựa chọn thường gặp là 0,05 cho mức tin cậy 95%, 0,01 cho mức tin cậy 99%, v.v. Hãy nhớ alpha thường là một giá trị nhỏ vì nó được lấy sau khi trừ mức tin cậy khỏi 100%.

Lỗi #VALUE!

Lỗi #VALUE! trong Excel cho thấy có vấn đề với các giá trị dùng trong công thức. Đối với hàm khoảng tin cậy, lỗi này xảy ra nếu bất kỳ đối số nào truyền vào hàm không phải số.

Hãy kiểm tra tất cả đối số (alpha, độ lệch chuẩn và cỡ mẫu) đều là giá trị số, và nếu bạn tham chiếu ô, hãy bảo đảm chúng chứa dữ liệu số.

Dĩ nhiên lỗi bạn gặp có thể khác so với trên và mang tính đặc thù — trong trường hợp đó, lựa chọn tốt nhất là kiểm tra diễn đàn Microsoft Answers cho các câu hỏi đã được giải đáp. Bạn cũng có thể đặt câu hỏi trong cùng diễn đàn để xử lý lỗi cụ thể của mình.

Kết luận

Hướng dẫn này đã giới thiệu khái niệm về khoảng tin cậy và tầm quan trọng của nó trong phân tích dữ liệu. Sau đó, chúng ta tìm hiểu cú pháp các hàm CONFIDENCE có trong Excel và thời điểm sử dụng chúng thông qua một hướng dẫn từng bước cho một kịch bản thực tế có thể gặp.

Các hàm có thể được cập nhật trong tương lai, vì vậy tài liệu chính thức của Microsoft là nguồn thông tin tốt nhất cho bạn.

Cuối cùng, để học toàn diện cách tận dụng Excel cho kỹ năng phân tích dữ liệu, hãy xem lộ trình Excel Fundamentals, nơi bạn sẽ bắt đầu từ con số 0 đến tự xây dựng dự án phân tích rời bỏ khách hàng trong Excel.

Chúng tôi khuyến nghị bạn xem hướng dẫn về các công thức Excel cơ bản và bảng ghi chú công thức Excel để tra cứu nhanh.