Excel標準差計算的6個函數的用法及區別，含用Stdev/Stdev.S/StdevA/StdevP等求樣本與總體標準差（含帶條件

標準差分為樣本標準差和總體標準差，前者是指從一組數據中抽取一些數據作為樣本計算標準差，后者是指把所有數據都計算標準差，而在現實中取得所有數據常常不可能，所以一般都是計算樣本標準差。Excel標準差計算的函數共有 6 個，實際上只有 4 個，因為有兩個新增的只是用于取代舊函數，它們與舊函數作用一樣。其中計算樣本標準差的有 Stdev、Stdev.S、StdevA，計算總體標準差的有 StdevP、Stdev.P、StdevPA。

計算標準差除計算一組數值的外，還可以計算滿足兩個或多個條件的標準差。另外，還可以把計算標準差函數與 OffSet、Match 函數組合實現添加數據后自動計算標準差和計算最近幾天的標準差。

一、Excel標準差的計算函數

（一）樣本標準差的計算函數

1、表達式

Stdev函數表達式：STDEV(Number1,[Number2],...)

Stdev.S函數表達式：STDEV.S(Number1,[Number2],...)

StdevA函數表達式：STDEVA(Value1, [Value2], ...)

2、說明：

A、Stdev函數與Stdev.S函數都用于計算數值的樣本標準差，它們至少要用一個參數，最多只能 255 個參數；Stdev.S函數是 Excel 2010 新增的，用于取代Stdev函數，在以后的 Excel 版本中，Stdev函數可能不會再支持。

B、StdevA函數除用于計算數值的樣本標準差外，還用于計算邏輯（True 或 False）和文本數值的樣本標準差；它也至少要有一個 Value、最多只能有 255 個 Value。

C、Stdev函數（Stdev.S函數）與StdevA函數的區別：它們的區別僅限于如何處理邏輯值與文本數值，對于邏輯值和文本數值，如果直接輸入到參數列表中，Stdev函數計入計算標準差，True 被轉為 1、False 被轉為 0；如果是在數組或引用單元格中，Stdev函數把它們忽略。而StdevA函數，無論把邏輯值與文本數值輸入到哪里都把它們計入計算標準差。另外，如果參數列表中有無法轉為數值的文本，它們都返回錯誤；如果數組或引用單元要格有無法轉為數值的文本，它們都忽略。

（二）總體標準差的計算函數

1、表達式

StdevP函數表達式：STDEVP(Number1,[Number2],...)

Stdev.P函數表達式：STDEV.P(Number1,[Number2],...)

StdevPA函數表達式：STDEVPA(Value1, [Value2], ...)

2、說明：

A、StdevP函數與Stdev.P函數作用也一樣都用于計算數值的總體標準差，它們至少要用一個參數，最多只能 255 個參數；Stdev.P函數也是 Excel 2010 新增的，用于取代StdevP函數，在以后的 Excel 版本中，StdevP函數可能被放棄。

B、StdevPA函數除用于計算數值的總體標準差外，還用于計算邏輯（True 或 False）和文本數值的總體標準差；它也至少要有一個 Value、最多只能有 255 個 Value。

C、StdevP函數（Stdev.P函數）與StdevPA函數的區別：它們的區別同樣僅限于怎么處理邏輯值與文本數值，對于邏輯值和文本數值，如果直接輸入到參數列表中，StdevP函數計入計算標準差，True 被轉為 1、False 被轉為 0；如果是在數組或引用單元格中，StdevP函數將它們忽略。而StdevPA函數，無論把邏輯值與文本數值輸入到哪里都把它們計入計算標準差。另外，如果參數列表中有無法轉為數值的文本，它們也都返回錯誤；如果數組或引用單元格有無法轉為數值的文本，它們也都忽略。

二、Excel標準差（樣本）計算函數的使用方法及實例

（一）用 Stdev（Stdev.S）計算數值的標準差

（1）用 Stdev 與 Stdev.S 計算的實例

1、假如要計算英語成績的樣本標準差。雙擊 B2 單元格，把公式 =STDEV(B2:B29) 復制到 B2，按回車，返回 7.494883616；雙擊 B3，把公式 =STDEV.S(B2:B29) 復制到 B3，按回車，也返回 7.494883616；框選 B2:B3，當前選項卡為“開始”，單擊“常規”右邊的黑色小倒三角，在彈出的選項中選擇“數字”，則標準差保留兩位小數；操作過程步驟，如圖1所示：

圖1

2、公式說明：

A、B2:B29 為計算標準差的引用單元格，兩個公式返回的結果一致，說明 Stdev 與 Stdev.S 作用一樣。

B、公式返回的結果默認保留多位小數，演示中只設置了保留兩位小數，如果要保留其它小數位數，可按 Ctrl + 1（需要關閉中文輸入法），打開“設置單元格格式”窗口，選擇“數字”選項卡，再選擇左邊的“數值”，然后在右邊的“小數位數”輸入具體的數值，單擊“確定”即可。

（2）True 與 False 計入與忽略的實例

1、True 與 False 計入計算標準差

A、雙擊 A1 單元格，把公式 =STDEV(24,27) 復制到 A1，按回車，返回 2.121320344；雙擊 A2，把公式 =STDEV(24,27,TRUE) 復制到 A2，按回車，返回 14.2243922；雙擊 A3，把公式 =STDEV(24,27,FALSE) 復制到 A3，按回車，返回 14.79864859；操作過程步驟，如圖2所示：

圖2

B、公式說明：

公式 =STDEV(24,27,TRUE) 和 =STDEV(24,27,FALSE) 返回的結果與 =STDEV(24,27) 不一樣，說明 TRUE 和 FALSE 都計入了計算標準差，由此可知，邏輯值直接寫到Stdev函數的參數列表中被計入求標準差。

2、True 與 False 計算標準差時被忽略

A、雙擊 C2 單元格，把公式 =STDEV(B2:B3) 復制到 C2，按回車，返回 2.121320344；雙擊 C3，把同一公式復制到 C3，把公式改為 =STDEV(B2:B4)，按回車，也返回 2.121320344；選中 B4，把 TRUE 改為 false，單擊 C3，C3 中的值未變；雙擊 C6，把公式 =STDEV({24,27,TRUE,FALSE}) 復制到 C6，按回車，同樣返回 2.121320344；操作過程步驟，如圖3所示：

圖3

B、公式說明：

從演示中可知，不管是否包含 True 或 False，每個公式都返回一樣的結果，說明 True 和 False 在單元格或數組中時，Stdev函數都把它們忽略。

（3）有能轉為數值的文本計入與忽略的實例

1、雙擊 A1 單元格，把公式 =STDEV(2.58,2.39,"2.67") 復制到 A1，按回車，返回 0.142945211，雙擊 A1，把“,"2.67"”刪除，按回車，返回 0.134350288；雙擊 A2，把公式 =STDEV(B1:B3) 復制到 A2，按回車，返回 0.134350288；操作過程步驟，如圖4所示：

圖4

2、公式說明：

公式 =STDEV(2.58,2.39,"2.67") 與 =STDEV(2.58,2.39) 返回的結果不一樣，說明，數值文本 "2.67" 計入了計算標準差；而公式=STDEV(B1:B3) 與 =STDEV(2.58,2.39) 的結果一樣，說明 B3 中的 2.67 沒有計入計算標準差，因為它是文本。

（4）有無法轉為數值的文本返回錯誤與忽略的實例

1、雙擊 A1 單元格，把公式 =STDEV(2.58,2.39,"十") 復制到 A1，按回車，返回值錯誤 #VALUE!；雙擊 A2，把公式 =STDEV(B1:B3) 復制到 A2，按回車，返回 0.134350288；操作過程步驟，如圖5所示：

圖5

2、公式說明：

兩個公式計算的是同樣數值和文本的標準差，公式 =STDEV(2.58,2.39,"十") 返回錯誤，說明，無法轉為數值的文本不能寫到參數列表中；而公式 =STDEV(B1:B3) 能返回正確的值，說明數組或引用單元格有無法轉為數值的文本，這些文本被忽略而不影響計算。

（二）用 StdevA 計算數值、文本數值和邏輯值的標準差

（1）引用單元格有數值、文本數值和邏輯值的實例

1、雙擊 B2 單元格，把公式 =STDEVA(A2:A6) 復制到 B2，按回車，返回 47.00850987；選中 A4，把 86 刪除，B2 中的值變為49.47305664，選中 A5，把 True 刪除，B2 中的值變為 49.81298358，再把 A6 中的 False 刪除，B2 中的值變為 5.656854249；操作過程步驟，如圖6所示：

圖6

2、公式說明：

A2 和 A3 中的數值為數值型，A4 中的數值為文本型，當把 A4 中的數值刪除后，標準差結果由 47.00850987 變為 49.47305664，說明StdevA函數把單元格中的文本計入計算標準差；當依次把 True 和 False 刪除后，標準差結果也發生變化，說明StdevA函數把邏輯值記入求標準差。

（2）StdevA 與 Stdev 對文本數值計入與忽略比較實例

1、StdevA 對于文本數值，無論是直接寫到參數列表還是寫到單元格，都把它計入求標準差，但對于小數的處理不一樣。如 E3、E4、E5 中的計算結果不一樣，說明 StdevA 把文本數值計入了求標準差；當把 "2.67" 直接寫到參數列表時，文本型 "2.67" 被轉為數值 2.67，而單元格中的 2.67 為文本時，它先被取代小數點后再轉為數值，轉為數值后的結果為 267，這一點可以從把 C5 中的 2.67 改為 267 后 E5 中的結果不變得到證實，演示如圖7所示：

圖7

2、StdevA 與 Stdev 對文本數值的區別為：Stdev 僅對直接寫到參數列表中的文本數值 "2.67" 計入求標準差，StdevA 對參數列表和單元格中的文本數值 "2.67" 都計入求標準差。圖7中，顏色相同的計算結果相同，即對文本數值的處理方式相同。

三、Excel標準差（總體）計算函數的使用方法及實例

（一）用 StdevP 與 Stdev.P 計算總體標準差的實例

1、雙擊 B2 單元格，把公式 =STDEVP(A2:A6) 復制到 B2，按回車，返回 0.681791757；雙擊 B3，把同一公式復制到 B3，再在 P 前加一點，公式變為 =STDEV.P(A2:A6)，按回車，返回一樣的結果；操作過程步驟，如圖8所示：

圖8

　2、公式說明：

公式 =STDEVP(A2:A6) 與 =STDEV.P(A2:A6) 返回一樣的結果，說明它們作用一樣都用于計算數值的總體標準差。

（二）用 StdevPA 計算包含數值、文本數值和邏輯值的總體標準差實例

1、雙擊 B2 單元格，把公式 =STDEVPA(A2:A6) 復制到 B2，按回車，返回 3.851360279；選中 A4，把文本數值 8.16 刪除，B2 中的值變為 3.846526842；把 A5 中的 False 刪除，B2 中的值變為 3.377049995；再把 A6 中的 True 刪除，B2 中的值變為 0.205；操作過程步驟，如圖9所示：

圖9

2、公式說明：

當把 A4 中的文本數值刪除后，標準差結果有變化，說明StdevPA函數把文本數值計入求標準差；再把 True 和 False 刪除，標準差結果也有變化，說明StdevPA函數也把邏輯值計入求標準差。

提示：StdevP函數與StdevPA函數對數值文本、邏輯值和無法轉為數值的文本的處理區別，請參考Stdev函數與StdevA函數的區別。

四、Excel標準差計算函數的應用實例

（一）計算滿足兩個條件的樣本標準差

1、假如要計算“毛衣”在上海銷量的樣本標準差。雙擊 D12 單元格，把公式 =STDEV((B2:B11="毛衣")*(C2:C11="上海")*(D2:D11)) 復制到 D12，按 Ctrl + Shift + 回車，返回結果 456.5364；操作過程步驟，如圖10所示：

圖10

2、公式 =STDEV((B2:B11="毛衣")*(C2:C11="上海")*(D2:D11)) 說明：

A、B2:B11="毛衣" 為公式的第一個條件，意思是：在 B2:B11 中查找為“毛衣”的服裝，如果為“毛衣”，返回 True，否則返回 False。執行時，第一次從 B2:B11 中取出 B2，由于 B2 為“羽絨服”，因此返回 False；第二次從 B2:B11 中取出 B3，由于 B3 為“休閑西服”，也返回 False；其它的以此類推，最后返回數組 {FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}。

B、C2:C11="上海" 為公式的第二個條件，意思是：在 C2:C11 中查找為“上海”的“地區”，如果為“上海”，返回 True，否則返回False，最后返回數組 {TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}。

C、則 (B2:B11="毛衣")*(C2:C11="上海") 變為 {FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}*{TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}，接著，取第一個數組的第一個元素與第二個數組的第一個元素相乘，結果為0（因為 True 被轉為 1，False 被轉為 0）；第二次取第一個數組的第二個元素與第二個數組的第二元素相乘，結果為 0；其它的以此類推，最后返回數組 {0;0;0;1;0;1;0;0;0;0}。

D、D2:D11 以數組返回 D2 到 D11 中的數值，即返回 {763;692;1090;969;583;1286;990;686;960;986}。

E、則公式變為 =STDEV({0;0;0;1;0;1;0;0;0;0}*{763;692;1090;969;583;1286;990;686;960;986})，接著，把兩數組對應元素相乘，則公式進一步變為 =STDEV({0;0;0;969;0;1286;0;0;0;0})，最后用 Stdev 求樣本標準差。

提示：以上是計算滿足兩個條件的樣本標準差，如果要計算滿足三個或更多個條件的樣本標準差，可以在公式的第二個 * 后繼續加條件。另外，如果要計算總體標準差，把 Stdev 改為 StdevP 即可。

（二）Stdev + OffSet + Match 組合實現添加新數據后自動計算標準差和計算最近幾天的標準差

（1）計算所有數據的樣本標準差

1、假如要求每添加一行“指數”自動計算指數的樣本標準差。雙擊 C3 單元格，把公式 =STDEV(OFFSET(B1,1,,MATCH(9E+307,B:B)-1)) 復制到 C3，按回車，返回結果 71.0624；選中 A8，輸入“7日”，按 Tab 鍵把光標移到 B8，輸入 837，按回車，C3 中的值自動變為64.9300，說明添加一行后自動重新計算了包含新添加數據的標準差；繼續在 A9 和 B9 輸入數據，按回車，C3 的值又自動變為 68.3013；操作過程步驟，如圖11所示：

圖11

2、公式 =STDEV(OFFSET(B1,1,,MATCH(9E+307,B:B)-1)) 說明：

A、9E+307 是 Excel 允許輸入的最大值，這里用于表示最后一行。MATCH(9E+307,B:B) 用于在 B 列返回最后一行的位置，當表格只有 7 行時，它返回 7；Match 省略了最后一個參數，默認取 1，因為第二個參數 B:B 后無逗號，意思是：在 B 列查找小于等于 9E+307 的最大值并返回它所在 B 列的位置。

B、則 OFFSET(B1,1,,MATCH(9E+307,B:B)-1) 變為 OFFSET(B1,1,,7-1)，接著，以 B1 為基準，返回 B1 下 1 行 0 列且高度為 6 寬度為1 的單元格引用，即返回 $B$2:$B$7。OffSet 函數省略了第三個參數列號，默認取 0；還省略了第五個參數寬度，默認取與 B1 一樣的寬度，即取 1。

C、則公式變為 =STDEV($B$2:$B$7)，最后對 B2:B7 中所有數值計算樣本標準差。

D、當在表格后添加一行后，MATCH(9E+307,B:B) 返回 8，OFFSET(B1,1,,8-1) 返回 $B$2:$B$8，恰好包含了新增加的行，因此添加新數據后會自動把新數據計入求標準差。

（2）計算最近 7 天的樣本標準差

1、雙擊 D3 單元格，把公式 =STDEV(OFFSET(B1,MATCH(9E+307,B:B)-1,,-7)) 復制到 D3，按回車，返回結果 58.5800；操作過程步驟，如圖12所示：

圖12

2、公式 =STDEV(OFFSET(B1,MATCH(9E+307,B:B)-1,,-7)) 說明：

A、MATCH(9E+307,B:B) 也用于在 B 列返回表格最后一行的位置，結果為 9。

B、則 OFFSET(B1,MATCH(9E+307,B:B)-1,,-7) 變為 OFFSET(B1,9-1,,-7)，接著，也以 B1 為基準，返回 B1 下 8 行 0 列且高度為 -7 寬度為 1 的單元格引用，即返回 $B$3:$B$9。B1 下 8 行即到了 B9，-7 是指從下往上，在這里的意思是：從 B9 往上 7 行，即 B3。

C、則公式變為 =STDEV($B$3:$B$9)，最后對 B3:B9 中的數值計算樣本標準差，即實現計算最近 7 天的標準差。如果要計算 5 天或 14 天，把 -7 改為 -5 或 -14 即可。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。