saftion 2012-11-3 00:39
EXCEL函數速查一覽13
4.職工性別統計
(1)函數分解
COUNTIF 函數計算區域中滿足給定條件的單格的個數。語法：COUNTIF(range,criteria) Range 為需要計算其中滿足條件的單格數目的單格區域；Criteria為確定哪些單格將被計算在內的條件，其形式可以為數位、運算式或文本。
(2)實例分析
假設上面使用的人事管理工作表中有599 條記錄，統計職工中男性和女性人數的方法是：選中單格D601(或其他用不上的空白單格)，統計男性職工人數可以在其中輸入公式“="男"&COUNTIF(D2:D600,"男")&"人"”；接著選中單格D602，在其中輸入公式“="女"&COUNTIF(D2:D227,"女")&"人"”。回車後即可得到“男399 人”、“女200 人”。
上式中D2:D600 是對“性別”列資料區域的引用，實際使用時必須根據資料個數進行修改。“男”或“女”則是條件判斷語句，用來判斷區域中符合條件的資料然後進行統計。“&” 則是字元連接符，可以在統計結果的前後加上“男”、“人”字樣，使其更具有可讀性。
5.年齡統計
在人事管理工作中，統計分佈在各個年齡段中的職工人數也是一項經常性工作。假設上面介紹的工作表的E2:E600 單格存放職工的工齡，我們要以5 年為一段分別統計年齡小於20 歲、20 至25 歲之間，一直到55 至60 歲之間的年齡段人數，可以採用下面的操作方法。
(1)函數分解
FREQUENCY 函數以一列垂直陣列返回某個區域中資料的頻率分佈。 語法：FREQUENCY(data_array,bins_array)
Data_array 為一陣列或對一組數值的引用，用來計算頻率。如果data_array 中不包含任何數值，函數FREQUENCY 返
回零陣列；Bins_array為間隔的陣列或對間隔的引用，該間隔用於對data_array 中的數值進行分組。如果bins_array
中不包含任何數值，函數FREQUENCY 返回data_array 中元素的個數。
(2)實例分析
首先在工作表中找到空白的I 列(或其他列)，自I2 單格開始依次輸入20、25、30 、35、40...60， 分別表示統計年齡小於20、20 至25 之間、25 至30 之間等的人數。然後在該列旁邊選中相同個數的單格，例如J2：J10 準備存放各年齡段的統計結果。然後在編輯欄輸入公式“=FREQUENCY(YEAR(TODAY())-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter 組合鍵即可在選中單格中看到計算結果。其中位於J2 單格中的結果表示年齡小於20 歲的職工人數，J3單格中的數值表示年齡在20 至25 之間的職工人數等。
6.名次值統計
在工資統計和成績統計等場合，往往需要知道某一名次(如工資總額第二、第三)的員工的工資是多少。這種統計的操
作方法如下。
(1)函數分解
LARGE 函數返回資料集中第K 個最大值。使用此函數可以根據相對標準來選擇數值。 語法：LARGE(array,k)
Array 為需要從中選擇第K 個最大值的陣列或資料區域； K 為返回值在陣列或資料單格區域中的位置(從大到小排)。SMALL 函數返回資料集中第K 個最小值。使用此函數可以返回資料集中特定位置上的數值。法：SMALL(array,k) Array 為需要找到第K 個最小值的陣列或數字型資料區域；K為返回的資料在陣列或資料區域裏的位置(從小到大)。
(2)實例分析
假設C2:C688 區域存放著員工的工資，首先在D 列選取空白單格D3，在其中輸入公式“=LARGE(C2:C688,D2)”。其中D2 作為輸入名次變數的單格，如果你在其中輸入3，公式就可以返回C2:C688 區域中第三大的數值。
如果我們把上述公式修改為“=SMALL(C2:C688,D1)”，然後在D1 單格中輸入6，就可以獲得C2:C688 區域倒數第六(小)的數值。 為方便起見，你可以給C2:C688 區域定義一個名稱“職工工資”。此後可以把上述公式修改為“=LARGE(職工工資,D2)”或“=SMALL(職工工資,D1)”。
7.位次閾值統計
與上例相似，在工資統計和成績統計等場合，需要知道排名達到總體的前1/3 的工資總額或分數(稱為“閾值”)是多
少。這種統計的操作方法如下：
(1)函數分解
PERCENTILE 函數返回區域中數值的第K 個百分點的值。可以使用此函數來建立接受閾值。 語法：PERCENTILE(array,k) Array 為定義相對位置的陣列或資料區域；K為0 到1 之間的百分點值，包含0和1。
(2)實例分析
假設C2:C200 區域存放著學生的考試成績，首先在D列選取空白單格D3，在其中輸入公式 “=PERCENTILE(C2:C200,D2)”。其中D2 作為輸入百分點變數的單格，如果你在其中輸入0.33，公式就可以返回名次達到前1/3 所需要的成績。
五、函數應用案例── 管理計算
企業、學校等單位均存在許多管理計算問題，例如計算一個學期有幾個授課日、企業在多少個工作日之後交貨等等。下面介紹有關問題的幾種計算方法。
1.授課日數
(1)函數分解
NETWORKDAYS 函數專門用於計算兩個日期值之間完整的工作日數值。這個工作日數值將不包括雙休日和專門指定的其他各種假期。 語法：NETWORKDAYS (Start_date,End_date,Holidays) Start_date 表示開始日期；End_date為終止日期，Holidays 表示作為特定假日的一個或多個日期。這些參數值既可以手工輸入，也可以對單格的值進行引用。
(2)實例分析
假設新學期從2003 年9 月1 日開始到2004 年1 月15 日結束，希望知道本學期有多少個授課日，也就是排除雙休日和國家法定假日外的授課工作日。這就是計算授課日數或工作日數的問題。首先打開一個空白工作表，在A1、B1、C1 單格輸入“開學時間”、“結束時間”、“法定節日”，然後在其下面的單格內輸入“2003-9-1”、“2004-1-15”、“2003-10-1” “2003-10-2”、“2003-10-3”和“2004-1-1”(後四項必須在C 列的“法定假日”下)。 接著可以選中D2 單格，輸入公式“=NETWORKDAYS(A2,B2,C2:C5)”。公式中A2 引用的是學期(或工作)的開始日期，B2引用的是學期結束的日期，C2:C5區域引用的是作為法定假日的多個日期。輸入結束回車即可獲得結果95， 即2003 年9 月1 日到2004 年1 月15 日，排除四個法定假日後的實際授課日是95 天。
2.折舊值計算
無論單位還是家庭，許多固定資產和耐用消費品都存在折舊問題，隨著使用時間的延長，其殘值在不斷減少。假設某單位有一批2000 年購進原價8 500 元/每台的電腦，預計使用壽命6 年， 壽命期結束時的資產殘值約為1 000 元，要求使用第二年內的折舊值。
(1)函數分解
DB 函數使用固定餘額遞減法，計算一筆資產在給定期間內的折舊值。 語法：DB(cost,salvage,life,period,month)
Cost 為資產原值；Salvage為資產在折舊期末的價值(也稱為資產殘值)；Life為折舊期限(有時也稱作資產的使用壽
命)；Period為需要計算折舊值的期間。Period必須使用與life 相同的單位；Month 為第一年的月份數，如省略，則假設為12。
(2)實例分析
為了在參數改變以後仍能進行計算，我們打開一個空白工作表，在A1、B1、C1、D1、E1 單格輸入“電腦原值”、“資產殘值”、“使用壽命”、“折舊時間”和“折舊值”，然後在其下面的單格內輸入“8500”、“1000”、“6”、“2 ”。
然後選中E2 單格在其中輸入公式“=DB(A2,B2,C2,D2)”，回車後即可得到結果“￥1,785.00”，就是說使用期第二年的折舊值為1 785 元。如果你要計算其他設備或財產的折舊值，只需改變A2、B2、C2、D2單格內的數值即可。
3.客流均衡度計算
假設某超市週一到週六的客流人數是16 359、17 254、18 654、15 398 、21 689 和220 867，總經理需要知道這種情
況下的客流分佈是否平坦。可以按如下方法計算：
(1)函數分解
KURT 函數返回資料集的峰值。峰值反映與正態分佈相比某一分佈的尖銳度或平坦度。正峰值表示相對尖銳的分佈。負峰值表示相對平坦的分佈。 語法：KURT(number1,number2,...) Number1,number2,...是用於計算峰值的1～30 個參數。也可以不使用這種用逗號分隔參數的形式，而用單個陣列或陣列引用的形式。
(2)實例分析
打開一個空白工作表，在A1 單格中輸入“一周客流統計”，然後將上述資料依次輸入A2、A3等單格。然後選中A8 單格，在其中輸入公式“=KURT(D2:D7)”，回車即可獲得結果“-1.719218897”，這說明超市的客流分佈與正態分佈相比是相對平坦的。假如星期天搞特價促銷，客流增加到了50 867 人，則計算結果就會變為“5.45379941”。說明超市的客流分佈與正態分佈相比比較尖銳了，特價促銷對客流的影響還是非常大的。 需要指出的是，KURT函數在教育統計等領域也有廣泛用途，假如把函數引用的區域修改為許多分數的集合，就可以知道考試成績的分佈是否尖銳或平坦。
4.銷售額預測
假設某超市週一到周日的日銷售額分別為13、17、16、15、19、21和22(萬元)，總經理需要預測今後一周內的日銷
售額的最高值和最低值。可以按如下方法進行預測：
(1)函數分解
TREND 函數返回一條線性回歸擬合線的值。即找到適合已知數組known_y"s 和known_x"s的直線(用最小二乘法)，並返回指定陣列new_x"s 在直線上對應的y 值。語法：TREND(known_y"s,known_x"s,new_x"s,const) Known_y"s 是關係運算式y=mx+b 中已知的y 值集合；Known_x"s 是關係運算式y=mx+b 中已知的可選x值集合；New_x"s 為需要函數 TREND返回對應y 值的新x值；Const 為一邏輯值，用於指定是否將常量b 強制設為0。
(2)實例分析
首先要打開一個空白工作表，在A1 單格中輸入“日銷售額”，然後將上述資料依次輸入A2、A3 至A8 單格。然後選中B2 至B8 區域，在Excel 的編輯欄輸入公式“=TREND(A2:A8)”，回車即可在B2 至B8 區域獲得7 個結果， 其中最高銷售額為21.64 萬元，最低銷售額為13.5 萬元。 與KURT 函數一樣，TREND函數可以用於教育統計中的學生入學數的峰值和低谷，鐵路運輸領域的客流高峰和低谷等的預測。
5.客流與營業額的相關分析
(1)函數分解
CORREL 函數返回單格區域array1 和array2 之間的相關係數。使用相關係數可以確定兩種屬性之間的關係。
語法：CORREL(array1,array2) Array1 為第一組數值單格區域；Array2為第二組數值單格區域。
(2)實例分析
假設一個超市要分析客流量與營業額是否相關。首先運行Excel 打開一個空白工作表，在A1 至A31 單格輸入八月份的每日客流人數，然後在B1 至B31 輸入八月份每日的營業額，再將上述兩個區域的名稱定義為“日客流人數”和“日營業額”。 接下來就可以選中工作表中的某個空白單元(例如B32)， 作為存儲運算結果的位置。在Excel 的編輯欄輸入公式“=CORREL(日客流人數,日營業額)”，回車後即可在公式所在單格看到相關係數的計算結果。 上式中CORREL 函數返回“日客流人數”和“日營業額”兩個資料集合的相關係數，實際應用中必須根據要分析的資料集合對引用區域進行修改。 與其他計算不同，CORREL函數計算出的相關係數必須進行分析，才能得出兩個數值之間是否相關的結論。統計理論根據各種因素(如“日客流人數”和“日營業額”)相互影響的關係，把相關分為正相關、負相關和零相關三種類型。
所謂正相關就是兩個因素的變化方向相同，即同時變大或變小，例如氣溫和冷飲銷量就是正相關；負相關就是兩個因素的變化方向相反，即一個變大(小)另一個變小(大)，例如氣溫上升和羽絨服銷量就是負相關；零相關就是兩個因素的變化方向無規律，即不存在相互之間影響的情況，例如學生的考試成績和麵粉的銷量就是零相關。
為了幫助不太熟悉統計理論的用戶掌握CORREL 函數的使用，這裏使用相關程度分析的理論修改公式“=CORREL(日客流人數,日營業額)”，使之成為下面這種形式，從而更加直觀的給出兩列資料相關程度的結論。 “=IF((ABS(CORREL(日客流人數,日營業額)))<=0.3,"相關程度低",IF((ABS(CORREL(日客流人數,日營業額)))<=0.5,"相關程度一般",IF((ABS(CORREL(日客流人數,日營業額)))<=0.7,"相關程度較高",IF((ABS(CORREL(日客流人數,日營業額)))<=0.9,"相關程度高",IF((ABS(CORREL(日客流人數,日營業額)))<=1,"相關程度極高")))))” 公式中的“CORREL(日客流人數,日營業額)”部分還是計算日客流人數和日營業額兩列資料的相關係數。
由於這裏只需要瞭解相關程度，所以使用ABS 函數返回相關係數的絕對值。整個公式中由左往右的下一個IF 語句就是上一個IF 語句的參數。例如第一個邏輯判斷運算式“(ABS(CORREL(日客流人數,日營業額)))<=0.3”為“真”(成立)，則公式所在單格就會被填入“相關程度低”；如果第一個邏輯判斷運算式“(ABS(CORREL(日客流人數,日營業額)))<=0.3”為“假”(不成立)，則計算第二個IF 語句“IF((ABS(CORREL(日客流人數,日營業額)))<=0.5”；以此類推直至計算結束。