Tính khoảng tin cậy trong Excel

Excel được sử dụng để thực hiện các nhiệm vụ thống kê khác nhau, một trong số đó là tính toán khoảng tin cậy, được sử dụng như một sự thay thế thích hợp nhất cho một ước tính điểm với kích thước mẫu nhỏ.

Chúng tôi muốn lưu ý ngay rằng quy trình tính khoảng tin cậy khá phức tạp, tuy nhiên, trong Excel có một số công cụ được thiết kế để hỗ trợ công việc này. Chúng ta hãy nhìn vào chúng.

Nội dung

Tính toán khoảng tin cậy

Khoảng tin cậy là cần thiết để đưa ra ước tính khoảng thời gian cho một số dữ liệu tĩnh. Mục đích chính của hoạt động này là để loại bỏ sự không chắc chắn của ước lượng điểm.

Có hai phương pháp để thực hiện tác vụ này trong Microsoft Excel:

  • Nhà điều hành BÃO MẬT - được sử dụng trong trường hợp đã biết độ phân tán;
  • Nhà điều hành TRUST.Studentkhi phương sai không xác định.

Dưới đây chúng tôi sẽ từng bước phân tích cả hai phương pháp trên trong thực tế.

Phương pháp 1: Câu lệnh TRUST.NORM

Hàm này lần đầu tiên được đưa vào kho của chương trình trong phiên bản Excel 2010 (trước phiên bản này, nó đã được thay thế bằng toán tử “ĐÁNG TIN CẬY"). Toán tử được bao gồm trong danh mục "thống kê".

Công thức hàm BÃO MẬT trông như thế:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Như chúng ta có thể thấy, hàm có ba đối số:

  • “Alpha” là chỉ tiêu đánh giá mức độ ý nghĩa, được lấy làm cơ sở cho việc tính toán. Mức độ tin cậy được tính như sau:
    • 1-"Альфа". Biểu thức này có thể áp dụng nếu giá trị “Alpha” được trình bày dưới dạng hệ số. Ví dụ, 1-0,7 0,3 =, trong đó 0,7 = 70% / 100%.
    • (100-"Альфа")/100. Biểu thức này sẽ được áp dụng nếu chúng ta xem xét mức độ tin cậy với giá trị “Alpha” tính bằng phần trăm. Ví dụ, (100-70) / 100 = 0,3.
  • "Độ lệch chuẩn" - tương ứng là độ lệch chuẩn của mẫu dữ liệu được phân tích.
  • "Kích thước" là kích thước của mẫu dữ liệu.

Lưu ý: Đối với hàm này, sự hiện diện của cả ba đối số là điều kiện tiên quyết.

Nhà điều hành "ĐÁNG TIN CẬY", được sử dụng trong các phiên bản trước của chương trình, chứa các đối số giống nhau và thực hiện các chức năng giống nhau.

Công thức hàm ĐÁNG TIN CẬY như sau:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

Không có sự khác biệt trong chính công thức, chỉ có tên của toán tử là khác nhau. Trong Excel 2010 và các phiên bản mới hơn, toán tử này nằm trong danh mục Tương thích. Trong các phiên bản cũ hơn của chương trình, nó nằm trong phần chức năng tĩnh.

Ranh giới khoảng tin cậy được xác định theo công thức sau:

X+(-)ДОВЕРИТ.НОРМ

Ở đâu Х là giá trị trung bình trên phạm vi được chỉ định.

Bây giờ chúng ta hãy xem làm thế nào để áp dụng các công thức này trong thực tế. Vì vậy, chúng tôi có một bảng với nhiều dữ liệu khác nhau từ 10 phép đo được thực hiện. Trong trường hợp này, độ lệch chuẩn của tập dữ liệu là 8.

Tính khoảng tin cậy trong Excel

Nhiệm vụ của chúng ta là lấy giá trị của khoảng tin cậy với độ tin cậy 95%.

  1. Trước hết, chọn một ô để hiển thị kết quả. Sau đó, chúng tôi nhấp vào nút "Chèn chức năng" (bên trái thanh công thức).Tính khoảng tin cậy trong Excel
  2. Cửa sổ Trình hướng dẫn chức năng mở ra. Bằng cách nhấp vào danh mục chức năng hiện tại, hãy mở rộng danh sách và nhấp vào dòng trong đó "Thống kê".Tính khoảng tin cậy trong Excel
  3. Trong danh sách được đề xuất, hãy nhấp vào toán tử “BÌNH THƯỜNG NIỀM TIN”, sau đó nhấn OK.Tính khoảng tin cậy trong Excel
  4. Chúng ta sẽ thấy một cửa sổ với các cài đặt của các đối số hàm, điền vào đó chúng ta nhấn nút OK.
    • trên đồng ruộng “Alpha” cho biết mức độ ý nghĩa. Nhiệm vụ của chúng tôi giả định mức độ tin cậy 95%. Thay giá trị này vào công thức tính mà chúng ta đã xem xét ở trên, chúng ta thu được biểu thức: (100-95)/100. Chúng tôi viết nó trong trường đối số (hoặc bạn có thể viết ngay kết quả của phép tính bằng 0,05).
    • trên đồng ruộng “Std_off” Theo điều kiện của chúng tôi, chúng tôi viết số 8.
    • trong trường "Kích thước", chỉ định số phần tử cần kiểm tra. Trong trường hợp của chúng tôi, 10 phép đo đã được thực hiện, vì vậy chúng tôi viết số 10.Tính khoảng tin cậy trong Excel
  5. Để tránh phải cấu hình lại chức năng khi dữ liệu thay đổi, bạn có thể tự động hóa nó. Đối với điều này, chúng tôi sử dụng chức năng “KIỂM TRA". Đặt con trỏ vào vùng đầu vào của thông tin đối số "Kích thước", sau đó nhấp vào biểu tượng tam giác ở bên trái của thanh công thức và nhấp vào mục “Các tính năng khác…”.Tính khoảng tin cậy trong Excel
  6. Kết quả là, một cửa sổ khác của Trình hướng dẫn chức năng sẽ mở ra. Bằng cách chọn một danh mục "Thống kê", nhấp vào chức năng “KIỂM TRA", rồi OK.Tính khoảng tin cậy trong Excel
  7. Màn hình sẽ hiển thị một cửa sổ khác với cài đặt của các đối số của hàm, được sử dụng để xác định số ô trong một phạm vi nhất định có chứa dữ liệu số.

    Công thức hàm KIỂM TRA nó được viết như thế này: =СЧЁТ(Значение1;Значение2;...).

    Số lượng đối số có sẵn cho hàm này có thể lên đến 255. Tại đây bạn có thể viết các số cụ thể hoặc địa chỉ ô hoặc phạm vi ô. Chúng tôi sẽ sử dụng tùy chọn cuối cùng. Để thực hiện việc này, hãy nhấp vào vùng nhập thông tin cho đối số đầu tiên, sau đó, giữ nút chuột trái, chọn tất cả các ô của một trong các cột của bảng của chúng tôi (không tính tiêu đề), rồi nhấn nút OK.Tính khoảng tin cậy trong Excel

  8. Theo kết quả của các hành động được thực hiện, kết quả của các phép tính cho toán tử sẽ được hiển thị trong ô đã chọn BÃO MẬT. Trong vấn đề của chúng tôi, giá trị của nó hóa ra bằng 4,9583603.Tính khoảng tin cậy trong Excel
  9. Nhưng đây vẫn chưa phải là kết quả cuối cùng trong nhiệm vụ của chúng tôi. Tiếp theo, bạn cần tính giá trị trung bình trong một khoảng thời gian nhất định. Để làm điều này, bạn cần sử dụng chức năng “TRÁI TIM"A thực hiện nhiệm vụ tính giá trị trung bình trên một phạm vi dữ liệu được chỉ định.

    Công thức toán tử được viết như thế này: =СРЗНАЧ(число1;число2;...).

    Chọn ô mà chúng tôi định chèn hàm và nhấn nút "Chèn chức năng".Tính khoảng tin cậy trong Excel

  10. Trong danh mục "Thống kê" chọn một nhà điều hành nhàm chán "TRÁI TIM" và nhấp OK.Tính khoảng tin cậy trong Excel
  11. Trong đối số hàm trong giá trị đối số "Con số" chỉ định phạm vi, bao gồm tất cả các ô có giá trị của tất cả các phép đo. Sau đó, chúng tôi nhấp vào ĐƯỢC CHỨ.Tính khoảng tin cậy trong Excel
  12. Kết quả của các hành động được thực hiện, giá trị trung bình sẽ được tự động tính toán và hiển thị trong ô có hàm mới được chèn vào.Tính khoảng tin cậy trong Excel
  13. Bây giờ chúng ta cần tính toán giới hạn CI (khoảng tin cậy). Hãy bắt đầu bằng cách tính toán giá trị của đường viền bên phải. Chúng tôi chọn ô mà chúng tôi muốn hiển thị kết quả và thực hiện việc bổ sung các kết quả thu được bằng các toán tử “TRÁI TIM" và "CONFIDENCE NORMS ”. Trong trường hợp của chúng tôi, công thức trông giống như sau: A14+A16. Sau khi nhập nó, nhấn đăng ký hạng mục thi.Tính khoảng tin cậy trong Excel
  14. Kết quả là, phép tính sẽ được thực hiện và kết quả sẽ được hiển thị ngay lập tức trong ô có công thức.Tính khoảng tin cậy trong Excel
  15. Sau đó, theo cách tương tự, chúng ta thực hiện phép tính để thu được giá trị của đường viền bên trái của CI. Chỉ trong trường hợp này giá trị của kết quả “CONFIDENCE NORMS ” bạn không cần phải cộng, nhưng trừ vào kết quả thu được bằng cách sử dụng toán tử “TRÁI TIM". Trong trường hợp của chúng tôi, công thức trông giống như sau: =A16-A14.Tính khoảng tin cậy trong Excel
  16. Sau khi nhấn Enter, chúng ta sẽ nhận được kết quả trong ô đã cho có công thức.Tính khoảng tin cậy trong Excel

Lưu ý: Trong các đoạn trên, chúng tôi đã cố gắng mô tả càng chi tiết càng tốt tất cả các bước và từng chức năng được sử dụng. Tuy nhiên, tất cả các công thức được chỉ định có thể được viết cùng nhau, như một phần của một công thức lớn:

  • Để xác định đường viền bên phải của CI, công thức chung sẽ giống như sau:

    =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

  • Tương tự, đối với đường viền bên trái, chỉ thay vì dấu cộng, bạn cần đặt dấu trừ:

    =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Phương pháp 2: Toán tử TRUST.STUDENT

Bây giờ, chúng ta hãy làm quen với toán tử thứ hai để xác định khoảng tin cậy - TRUST.Student. Hàm này đã được đưa vào chương trình tương đối gần đây, bắt đầu từ phiên bản Excel 2010, và nhằm xác định CI của tập dữ liệu đã chọn bằng cách sử dụng phân phối Student, với một phương sai không xác định.

Công thức hàm TRUST.Student như sau:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

Hãy phân tích ứng dụng của toán tử này trên ví dụ của cùng một bảng. Chỉ bây giờ chúng ta không biết độ lệch chuẩn theo các điều kiện của bài toán.

  1. Đầu tiên, chọn ô mà chúng ta định hiển thị kết quả. Sau đó bấm vào biểu tượng "Chèn chức năng" (bên trái thanh công thức).Tính khoảng tin cậy trong Excel
  2. Cửa sổ Trình hướng dẫn chức năng đã nổi tiếng sẽ mở ra. Chọn một danh mục "Thống kê", sau đó từ danh sách các chức năng được đề xuất, hãy nhấp vào toán tử “HỌC VIÊN TIN TƯỞNG”, sau đó - OK.Tính khoảng tin cậy trong Excel
  3. Trong cửa sổ tiếp theo, chúng ta cần thiết lập các đối số của hàm:
    • Trong tạp chí “Alpha” như trong phương pháp đầu tiên, chỉ định giá trị 0,05 (hoặc “100-95) / 100”).
    • Hãy chuyển sang phần tranh luận. “Std_off”. Bởi vì theo điều kiện của bài toán, giá trị của nó là không xác định cho chúng tôi, chúng tôi cần phải thực hiện các tính toán thích hợp, trong đó toán tử "STDEV.B ”. Nhấp vào nút thêm chức năng và sau đó vào mục “Các tính năng khác…”.Tính khoảng tin cậy trong Excel
    • Trong cửa sổ tiếp theo của Trình hướng dẫn chức năng, hãy chọn toán tử “STDEV.B ” trong danh mục "Thống kê" và nhấp OK.Tính khoảng tin cậy trong Excel
    • Chúng ta vào cửa sổ cài đặt đối số hàm, công thức của nó trông giống như sau: =СТАНДОТКЛОН.В(число1;число2;...). Là đối số đầu tiên, chúng tôi chỉ định một phạm vi bao gồm tất cả các ô trong cột "Giá trị" (không tính tiêu đề).Tính khoảng tin cậy trong Excel
    • Bây giờ bạn cần quay lại cửa sổ với các đối số hàm “TRUST.STUDENT ”. Để thực hiện việc này, hãy nhấp vào dòng chữ cùng tên trong trường nhập công thức.Tính khoảng tin cậy trong Excel
    • Bây giờ chúng ta hãy chuyển sang đối số cuối cùng “Kích thước”. Như trong phương pháp đầu tiên, ở đây bạn có thể chỉ cần chỉ định một dải ô hoặc chèn toán tử "ĐÁNH DẤU". Chúng tôi chọn tùy chọn cuối cùng.
    • Khi tất cả các đối số được điền vào, hãy nhấp vào nút OK.Tính khoảng tin cậy trong Excel
  4. Ô được chọn sẽ hiển thị giá trị của khoảng tin cậy theo các tham số mà chúng tôi đã chỉ định.Tính khoảng tin cậy trong Excel
  5. Tiếp theo, chúng ta cần tính toán các giá trị của các ranh giới CI. Và đối với điều này, bạn cần lấy giá trị trung bình cho phạm vi đã chọn. Để làm điều này, chúng tôi lại áp dụng chức năng “TRÁI TIM". Thuật toán của các hành động tương tự như được mô tả trong phương pháp đầu tiên.Tính khoảng tin cậy trong Excel
  6. Đã nhận được giá trị “TRÁI TIM", bạn có thể bắt đầu tính toán các ranh giới CI. Bản thân các công thức không khác gì các công thức được sử dụng với “CONFIDENCE NORMS ”:
    • Đường viền bên phải CI = AVERAGE + NIỀM TIN HỌC SINH
    • Ràng buộc bên trái CI = AVERAGE-SỰ TỰ TIN CỦA HỌC SINHTính khoảng tin cậy trong Excel

Kết luận

Kho công cụ của Excel vô cùng lớn và cùng với các hàm phổ biến, chương trình cung cấp nhiều hàm đặc biệt giúp làm việc với dữ liệu dễ dàng hơn nhiều. Có lẽ thoạt nhìn các bước được mô tả ở trên có vẻ phức tạp đối với một số người dùng. Nhưng sau khi nghiên cứu chi tiết vấn đề và trình tự hành động, mọi thứ sẽ trở nên dễ dàng hơn rất nhiều.

Bình luận