Excel用於執行各種統計任務,其中之一是置信區間的計算,它被用作樣本量較小的點估計的最合適的替代品。
我們想立即指出,計算置信區間的過程相當複雜,但是,在 Excel 中,有許多工具旨在促進這項任務。 讓我們來看看它們。
內容
置信區間計算
需要置信區間才能對某些靜態數據進行區間估計。 該操作的主要目的是消除點估計的不確定性。
在 Microsoft Excel 中執行此任務有兩種方法:
- 操作者 信心標準 – 用於已知分散的情況;
- 操作者 信任學生當方差未知時。
下面我們將在實踐中逐步分析這兩種方法。
方法一:TRUST.NORM 語句
此功能在 Excel 2010 版中首次引入程序庫(在此版本之前,它已被操作符替換為“值得信賴”)。 運算符包含在“統計”類別中。
函數公式 信心標準 看起來像這樣:
=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)
如我們所見,該函數具有三個參數:
- “Α” 是顯著性水平的指標,作為計算的依據。 置信水平計算如下:
1-"Альфа"
. 如果值,則此表達式適用 “Α” 表示為係數。 例如, 1-0,7 0,3 =, 其中 0,7=70%/100%。(100-"Альфа")/100
. 如果我們考慮具有值的置信水平,則將應用此表達式 “Α” 百分比。 例如, (100-70) / 100 = 0,3.
- “標準偏差” – 分別為分析數據樣本的標準偏差。
- “尺寸” 是數據樣本的大小。
注意: 對於此函數,所有三個參數的存在是先決條件。
操作員 ”值得信賴”,在程序的早期版本中使用,包含相同的參數並執行相同的功能。
函數公式 值得信賴 如下所示:
=ДОВЕРИТ(Альфа;Станд_откл;Размер)
公式本身沒有區別,只是運算符的名稱不同。 在 Excel 2010 及更高版本中,此運算符屬於兼容性類別。 在舊版本的程序中,它位於靜態函數部分。
置信區間邊界由以下公式確定:
X+(-)ДОВЕРИТ.НОРМ
哪裡 Х 是指定範圍內的平均值。
現在讓我們看看如何在實踐中應用這些公式。 因此,我們有一個表格,其中包含來自 10 次測量的各種數據。 在這種情況下,數據集的標準差為 8。
我們的任務是獲得置信區間為 95% 的值。
- 首先,選擇一個單元格來顯示結果。 然後我們點擊按鈕 “插入功能” (在公式欄的左側)。
- 函數嚮導窗口打開。 通過單擊當前功能類別,展開列表並單擊其中的行 “統計”.
- 在建議的列表中,單擊運算符 “信心標準”,然後按 OK.
- 我們將看到一個帶有函數參數設置的窗口,我們按下按鈕進行填充 OK.
- 在該領域 “Α” 表示顯著性水平。 我們的任務假設置信度為 95%。 將此值代入我們上面考慮的計算公式,我們得到表達式:
(100-95)/100
. 我們將其寫入參數字段(或者您可以立即將計算結果寫入等於 0,05)。 - 在該領域 “標準關閉” 根據我們的條件,我們寫數字 8。
- 在“大小”字段中,指定要檢查的元素數量。 在我們的例子中,進行了 10 次測量,所以我們寫了數字 10。
- 在該領域 “Α” 表示顯著性水平。 我們的任務假設置信度為 95%。 將此值代入我們上面考慮的計算公式,我們得到表達式:
- 為了避免在數據更改時重新配置函數,您可以將其自動化。 為此,我們使用函數“查看”. 將指針放在參數信息的輸入區 “尺寸”,然後單擊公式欄左側的三角形圖標並單擊該項目 “更多功能……”.
- 因此,函數嚮導的另一個窗口將打開。 通過選擇一個類別 “統計”,點擊功能“查看”, 然後確定。
- 屏幕將顯示另一個窗口,其中包含函數參數的設置,用於確定給定範圍內包含數字數據的單元格數量。
函數公式 CHECK 它是這樣寫的:
=СЧЁТ(Значение1;Значение2;...)
.此函數的可用參數數量最多為 255 個。您可以在此處輸入特定數字、單元格地址或單元格範圍。 我們將使用最後一個選項。 為此,請單擊第一個參數的信息輸入區域,然後按住鼠標左鍵,選擇我們表格中一列的所有單元格(不包括標題),然後按下按鈕 OK.
- 作為所採取行動的結果,運算符的計算結果將顯示在選定的單元格中 信心標準. 在我們的問題中,它的值等於 4,9583603.
- 但這還不是我們任務的最終結果。 接下來,您需要計算給定時間間隔內的平均值。 為此,您需要使用“心”執行在指定數據范圍內計算平均值的任務。
算子公式是這樣寫的:
=СРЗНАЧ(число1;число2;...)
.選擇我們計劃插入函數的單元格並按下按鈕 “插入功能”.
- 在類別中 “統計” 選擇一個無聊的運營商 “心” 並點擊 OK.
- 在參數值中的函數參數中 “數字” 指定範圍,其中包括具有所有測量值的所有單元格。 然後我們點擊 好的。
- 作為採取行動的結果,平均值將被自動計算並顯示在新插入函數的單元格中。
- 現在我們需要計算 CI(置信區間)界限。 讓我們從計算右邊框的值開始。 我們選擇要顯示結果的單元格,然後使用運算符“將獲得的結果相加”心” 和“信心標準”. 在我們的例子中,公式如下所示:
A14+A16
. 輸入後按 Enter. - 結果,將執行計算,結果將立即顯示在帶有公式的單元格中。
- 然後,以類似的方式,我們進行計算,得到 CI 左邊界的值。 只有在這種情況下,結果的值“信心標準” 您不需要添加,而是從使用運算符“獲得的結果中減去”心”. 在我們的例子中,公式如下所示:
=A16-A14
. - 按 Enter 後,我們將使用公式在給定單元格中獲得結果。
注意: 在上面的段落中,我們試圖盡可能詳細地描述所有步驟和使用的每個功能。 但是,所有規定的公式都可以寫在一起,作為一個大公式的一部分:
- 要確定 CI 的右邊界,一般公式如下所示:
=СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11))
. - 類似地,對於左邊框,只需要放一個減號而不是加號:
=СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11))
.
方法 2:TRUST.STUDENT 運算符
現在,讓我們熟悉用於確定置信區間的第二個運算符 - 信任學生. 這個函數是最近才引入到程序中的,從 Excel 2010 版本開始,旨在使用學生分佈確定所選數據集的 CI,方差未知。
函數公式 信任學生 如下所示:
=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)
下面我們來分析一下這個算子在同一張表的例子上的應用。 只是現在我們不知道根據問題條件的標準差。
- 首先,選擇我們計劃顯示結果的單元格。 然後點擊圖標 “插入功能” (在公式欄的左側)。
- 眾所周知的功能嚮導窗口將打開。 選擇一個類別 “統計”,然後從建議的函數列表中,單擊運算符 “值得信賴的學生”, 然後 - OK.
- 在下一個窗口中,我們需要設置函數參數:
- 在 “Α” 與第一種方法一樣,指定值 0,05(或“100-95)/100”)。
- 讓我們繼續討論。 “標準關閉”. 因為根據問題的條件,它的值我們是未知的,我們需要做相應的計算,其中算子“STDEV.B”. 單擊添加功能按鈕,然後單擊項目 “更多功能……”.
- 在函數嚮導的下一個窗口中,選擇運算符“STDEV.B” 在類別中 “統計” 並點擊 OK.
- 我們進入函數參數設置窗口,其公式如下所示:
=СТАНДОТКЛОН.В(число1;число2;...)
. 作為第一個參數,我們指定了一個範圍,該範圍包括“值”列中的所有單元格(不包括標題)。 - 現在你需要回到帶有函數參數的窗口“信任的學生”. 為此,請單擊公式輸入字段中的同名題詞。
- 現在讓我們繼續討論最後一個參數“Size”。 與第一種方法一樣,您可以在此處簡單地指定單元格範圍,或插入運算符 “查看”. 我們選擇最後一個選項。
- 填寫完所有參數後,單擊按鈕 OK.
- 選中的單元格會根據我們指定的參數顯示置信區間的值。
- 接下來,我們需要計算CI邊界的值。 為此,您需要獲取所選範圍的平均值。 為此,我們再次應用函數“心”. 動作的算法類似於第一種方法中描述的算法。
- 收到值“心”,您可以開始計算 CI 邊界。 公式本身與“信心標準”:
- 右邊框 CI=AVERAGE+STUDENT CONFIDENCE
- 左邊界 CI=AVERAGE-STUDENT CONFIDENCE
結論
Excel 的工具庫非常龐大,除了常用功能外,該程序還提供多種特殊功能,使處理數據變得更加容易。 乍一看,對於某些用戶來說,上述步驟可能看起來很複雜。 但是在對問題和行動順序進行詳細研究之後,一切都會變得容易得多。