Excel2003函數(shù)教程.doc

Excel2003函數(shù)教程：函數(shù)應(yīng)用案例 信息統(tǒng)計函數(shù) 使用Excel管理人事信息，具有無須編程、簡便易行的特點。假設(shè)有一個人事管理工作表，它的A1、B1、C1、D1、E1、F1、G1和H1單元格分別輸入“序號”、“姓名”、“身份證號碼”、“性別”、“出生年月”等。自第2行開始依次輸入職工的人事信息。為了盡可能減少數(shù)據(jù)錄入的工作量，下面利用Excel函數(shù)實現(xiàn)數(shù)據(jù)統(tǒng)計的自動化。1.性別輸入根據(jù)現(xiàn)行的居民身份證號碼編碼規(guī)定，正在使用的18位的身份證編碼。它的第17位為性別(奇數(shù)為男，偶數(shù)為女)，第18位為效驗位。而早期使用的是15位的身份證編碼，它的第15位是性別(奇數(shù)為男，偶數(shù)為女)。(1)函數(shù)分解LEN函數(shù)返回文本字符串中的字符數(shù)。語法：LEN(text) Text是要查找其長度的文本。空格將作為字符進行計數(shù)。MOD函數(shù)返回兩數(shù)相除的余數(shù)。結(jié)果的正負號與除數(shù)相同。語法：MOD(number,divisor) Number為被除數(shù)；Divisor為除數(shù)。 MID函數(shù)返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。語法：MID(text,start_num,num_chars)Text為包含要提取字符的文本字符串；Start_num為文本中要提取的第一個字符的位置。文本中第一個字符的start_num為1，以此類推；Num_chars指定希望MID從文本中返回字符的個數(shù)。(2)實例分析為了適應(yīng)上述情況，必須設(shè)計一個能夠適應(yīng)兩種身份編碼的性別計算公式，在D2單元格中輸入“=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,男,女),IF(MOD(MID(C2,17,1),2)=1,男,女)”?；剀嚭蠹纯稍趩卧瘾@得該職工的性別，而后只要把公式復(fù)制到D3、D4等單元格，即可得到其他職工的性別。為了便于大家了解上述公式的設(shè)計思路，下面簡單介紹一下它的工作原理：該公式由三個IF函數(shù)構(gòu)成，其中“IF(MOD(MID(C2,15,1),2)=1,男,女)”和“IF(MOD(MID(C2,17,1),2)=1,男,女)”作為第一個函數(shù)的參數(shù)。公式中“LEN(C2)=15”是一個邏輯判斷語句，LEN函數(shù)提取C2等單元格中的字符長度，如果該字符的長度等于15，則執(zhí)行參數(shù)中的第一個IF函數(shù)，否則就執(zhí)行第二個IF函數(shù)。在參數(shù)“IF(MOD(MID(C2,15,1),2)=1,男,女)”中。MID函數(shù)從C2的指定位置(第15位)提取1個字符，而MOD函數(shù)將該字符與2相除，獲取兩者的余數(shù)。如果兩者能夠除盡，說明提取出來的字符是0(否則就是1)。邏輯條件“MOD(MID(C2,15,1),2)=1”不成立，這時就會在D2單元格中填入“女”，反之則會填入“男”。如果LEN函數(shù)提取的C2等單元格中的字符長度不等于15，則會執(zhí)行第2個IF函數(shù)。除了MID函數(shù)從C2的指定位置(第17位，即倒數(shù)第2位)提取1個字符以外，其他運算過程與上面的介紹相同。2.出生日期輸入(1)函數(shù)分解CONCATENATE函數(shù)將幾個文本字符串合并為一個文本字符串。語法：CONCATENATE(text1,text2,.)Text1,text2,.為130個要合并成單個文本項的文本項。文本項可以為文本字符串、數(shù)字或?qū)蝹€單元格的引用。(2)實例分析與上面的思路相同，我們可以在E2單元格中輸入公式“=IF(LEN(C2)=15,CONCATENATE(19,MID(C2,7,2),年,MID(C2,9,2),月,MID(C2,11,2),日),CONCCTENCTE(MID(C2,7,4),年,MID(C2,11,2),月,MID(C2,13,2),日)”。其中“LEN(C2)=15”仍然作為邏輯判斷語句使用，它可以判斷身份證號碼是15位的還是18位的，從而調(diào)用相應(yīng)的計算語句。對15位的身份證號碼來說，左起第7至12個字符表示出生年、月、日，此時可以使用MID函數(shù)從身份證號碼的特定位置，分別提取出生年、月、日。然后用CONCATENATE函數(shù)將提取出來的文字合并起來，就能得到對應(yīng)的出生年月日。公式中“19”是針對早期身份證號碼中存在2000年問題設(shè)計的，它可以在計算出來的出生年份前加上“19”。對“18”位的身份證號碼的計算思路相同，只是它不存在2000年問題，公式中不用給計算出來的出生年份前加上“19”。注意：CONCATENATE函數(shù)和MID函數(shù)的操作對象均為文本，所以存放身份證號碼的單元格必須事先設(shè)為文本格式，然后再輸入身份證號。3.職工信息查詢Excel提供的“記錄單”功能可以查詢記錄，如果要查詢?nèi)耸鹿芾砉ぷ鞅碇械哪硹l記錄，然后把它打印出來，必須采用下面介紹的方法。(1)函數(shù)分解INDEX函數(shù)返回數(shù)據(jù)清單或數(shù)組中的元素值，此元素由行序號和列序號的索引值給定。 INDEX函數(shù)有兩種語法形式：數(shù)組和引用。數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組，引用形式通常返回引用。當(dāng)函數(shù)INDEX的第一個參數(shù)為數(shù)組常數(shù)時，使用數(shù)組形式。語法1(數(shù)組形式)：INDEX(array,row_num,column_num)Array為單元格區(qū)域或數(shù)組常量。如果數(shù)組只包含一行或一列，則相對應(yīng)的參數(shù)row_num或column_num為可選。如果數(shù)組有多行和多列，但只使用row_num或c olumn_num，函數(shù)INDEX返回數(shù)組中的整行或整列，且返回值也為數(shù)組；Row_num為數(shù)組中某行的行序號，函數(shù)從該行返回數(shù)值。如果省略row_num，則必須有column_num；Column_num為數(shù)組中某列的列序號，函數(shù)從該列返回數(shù)值。如果省略column_num，則必須有row_num。語法2(引用形式)：INDEX(reference,row_num,column_num,area_num)Reference表示對一個或多個單元格區(qū)域的引用。如果為引用輸入一個不連續(xù)的區(qū)域，必須用括號括起來。如果引用中的每個區(qū)域只包含一行或一列，則相應(yīng)的參數(shù)row_num或column_num分別為可選項；Row_num引用中某行的行序號，函數(shù)從該行返回一個引用；Column_num引用中某列的列序號，函數(shù)從該列返回一個引用；Area_num選擇引用中的一個區(qū)域，并返回該區(qū)域中row_num和column_num的交叉區(qū)域。選中或輸入的第一個區(qū)域序號為1，第二個為2，以此類推。如果省略area_num，函數(shù)INDEX使用區(qū)域1。 MATCH函數(shù)返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。語法：MATCH(lookup_value,lookup_array,match_type)Lookup_value為需要在數(shù)據(jù)表中查找的數(shù)值；Lookup_value為需要在Look_array中查找的數(shù)值；Match_type為數(shù)字-1、0或1。(2)實例分析如果上面的人事管理工作表放在Sheet1中，為了防止因查詢操作而破壞它(必要時可以添加只讀保護)，我們可以打開另外一個空白工作表Sheet2，把上一個數(shù)據(jù)清單中的列標(biāo)記復(fù)制到第一行。假如你要以“身份證號碼”作為查詢關(guān)鍵字，就要在C2單元格中輸入公式“=INDEX(Sheet1!C2:C600,MATCH(SC S5,Sheet1! SC S2: SC S600,0),1)”。其中的參數(shù)“ SC S5”引用公式所在工作表中的C5單元格(也可以選用其他單元格)，執(zhí)行查詢時要在其中輸入查詢關(guān)鍵字，也就是待查詢記錄中的身份證號碼。參數(shù)“Sheet1!C2:C600”設(shè)定INDEX函數(shù)的查詢范圍，引用的是數(shù)據(jù)清單C列的所有單元格。MATCH函數(shù)中的參數(shù)“0”指定它查找“Sheet1! SC S2: SC S600”區(qū)域中等于 SC S5的第一個值，并且引用的區(qū)域“Sheet1! SC S2: SCS600,0”可以按任意順序排列。上面的公式執(zhí)行數(shù)據(jù)查詢操作時，首先由MATCH函數(shù)在“Sheet1! SCS2: SC S600”區(qū)域搜索，找到“ SC S5”單元格中的數(shù)據(jù)在引用區(qū)域中的位置(自上而下第幾個單元格)，從而得知待查詢數(shù)據(jù)在引用區(qū)域中的第幾行。接下來INDEX函數(shù)根據(jù)MATCH函數(shù)給出的行號，返回“Sheet1!C2:C600”區(qū)域中對應(yīng)行數(shù)單元格中的數(shù)據(jù)。假設(shè)其中待查詢的“身份證號碼”是“3234567896”，它位于“Sheet1! SC S2: SC S600”區(qū)域的第三行，MATCH函數(shù)就會返回“3”。接著INDEX函數(shù)返回“Sheet1!C2:C600”區(qū)域中行數(shù)是“3”的數(shù)據(jù)，也就是“3234567896”。然后，我們將光標(biāo)放到C2單元格的填充柄上，當(dāng)十字光標(biāo)出現(xiàn)以后向右拖動，從而把C2中的公式復(fù)制到D2、E2等單元格(然后再向左拖動，以便把公式復(fù)制到B2、A2單元格)，這樣就可以獲得與該身份證號對應(yīng)的性別、籍貫等數(shù)據(jù)。注意：公式復(fù)制到D2、E2等單元格以后，INDEX函數(shù)引用的區(qū)域就會發(fā)生變化，由C2:C600變成D2 :D600、E2:E600等等。但是MATCH函數(shù)返回的(相對)行號仍然由查詢關(guān)鍵字給出，此后INDEX函數(shù)就會根據(jù)MATCH函數(shù)返回的行號從引用區(qū)域中找到數(shù)據(jù)。在Sheet2工作表中進行查詢時只要在查詢輸入單元格中輸入關(guān)鍵字，回車后即可在工作表的C2單元格內(nèi)看到查詢出來的身份證號碼。如果輸入的身份證號碼關(guān)鍵字不存在或輸入錯誤，則單元格內(nèi)會顯示“#N/A”字樣。4.職工性別統(tǒng)計(1)函數(shù)分解COUNTIF函數(shù)計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：COUNTIF(range,criteria) Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域；Criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。(2)實例分析假設(shè)上面使用的人事管理工作表中有599條記錄，統(tǒng)計職工中男性和女性人數(shù)的方法是：選中單元格D601(或其他用不上的空白單元格)，統(tǒng)計男性職工人數(shù)可以在其中輸入公式“=男&COUNTIF(D2:D600,男)&人”；接著選中單元格D602，在其中輸入公式“=女&COUNTIF(D2:D227,女)&人”。回車后即可得到“男399人”、“女200人”。上式中D2:D600是對“性別”列數(shù)據(jù)區(qū)域的引用，實際使用時必須根據(jù)數(shù)據(jù)個數(shù)進行修改?！澳小被颉芭眲t是條件判斷語句，用來判斷區(qū)域中符合條件的數(shù)據(jù)然后進行統(tǒng)計。“&”則是字符連接符，可以在統(tǒng)計結(jié)果的前后加上“男”、“人”字樣，使其更具有可讀性。5.年齡統(tǒng)計在人事管理工作中，統(tǒng)計分布在各個年齡段中的職工人數(shù)也是一項經(jīng)常性工作。假設(shè)上面介紹的工作表的E2:E600單元格存放職工的工齡，我們要以5年為一段分別統(tǒng)計年齡小于20歲、20至25歲之間，一直到55至60歲之間的年齡段人數(shù)，可以采用下面的操作方法。(1)函數(shù)分解FREQUENCY函數(shù)以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。語法：FREQUENCY(data_array,bins_array)Data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來計算頻率。如果data_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回零數(shù)組；Bins_array為間隔的數(shù)組或?qū)﹂g隔的引用，該間隔用于對data_array中的數(shù)值進行分組。如果bins_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回data_array中元素的個數(shù)。(2)實例分析首先在工作表中找到空白的I列(或其他列)，自I2單元格開始依次輸入20、25、30、35、40.60，分別表示統(tǒng)計年齡小于20、20至25之間、25至30之間等的人數(shù)。然后在該列旁邊選中相同個數(shù)的單元格，例如J2：J10準(zhǔn)備存放各年齡段的統(tǒng)計結(jié)果。然后在編輯欄輸入公式“=FREQUENCY(YEAR(TODAY()-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter組合鍵即可在選中單元格中看到計算結(jié)果。其中位于J2單元格中的結(jié)果表示年齡小于20歲的職工人數(shù)，J3單元格中的數(shù)值表示年齡在20至25之間的職工人數(shù)等。6.名次值統(tǒng)計在工資統(tǒng)計和成績統(tǒng)計等場合，往往需要知道某一名次(如工資總額第二、第三)的員工的工資是多少。這種統(tǒng)計的操作方法如下。(1)函數(shù)分解LARGE函數(shù)返回數(shù)據(jù)集中第K個最大值。使用此函數(shù)可以根據(jù)相對標(biāo)準(zhǔn)來選擇數(shù)值。語法：LARGE(array,k)Array為需要從中選擇第K個最大值的數(shù)組或數(shù)據(jù)區(qū)域；K為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置(從大到小排)。SMALL函數(shù)返回數(shù)據(jù)集中第K個最小值。使用此函數(shù)可以返回數(shù)據(jù)集中特定位置上的數(shù)值。法：SMALL(array,k)Array為需要找到第K個最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域；K為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從小到大)。(2)實例分析假設(shè)C2:C688區(qū)域存放著員工的工資，首先在D列選取空白單元格D3，在其中輸入公式“=LARGE(C2:C688,D2)”。其中D2作為輸入名次變量的單元格，如果你在其中輸入3，公式就可以返回C2:C688區(qū)域中第三大的數(shù)值。如果我們把上述公式修改為“=SMALL(C2:C688,D1)”，然后在D1單元格中輸入6，就可以獲得C2:C688區(qū)域倒數(shù)第六(小)的數(shù)值。為方便起見，你可以給C2:C688區(qū)域定義一個名稱“職工工資”。此后可以把上述公式修改為“=LARGE(職工工資,D2)”或“=SMALL(職工工資,D1)”。7.位次閾值統(tǒng)計與上例相似，在工資統(tǒng)計和成績統(tǒng)計等場合，需要知道排名達到總體的前1/3的工資總額或分?jǐn)?shù)(稱為“閾值”)是多少。這種統(tǒng)計的操作方法如下：(1)函數(shù)分解PERCENTILE函數(shù)返回區(qū)域中數(shù)值的第K個百分點的值?？梢允褂么撕瘮?shù)來建立接受閾值。語法：PERCENTILE(array,k) Array為定義相對位置的數(shù)組或數(shù)據(jù)區(qū)域；K為0到1之間的百分點值，包含0和1。(2)實例分析假設(shè)C2:C200區(qū)域存放著學(xué)生的考試成績，首先在D列選取空白單元格D3，在其中輸入公式“=PERCENTILE(C2:C200,D2)”。其中D2作為輸入百分點變量的單元格，如果你在其中輸入0.33，公式就可以返回名次達到前1/3所需要的成績。五、函數(shù)應(yīng)用案例管理計算企業(yè)、學(xué)校等單位均存在許多管理計算問題，例如計算一個學(xué)期有幾個授課日、企業(yè)在多少個工作日之后交貨等等。下面介紹有關(guān)問題的幾種計算方法。1.授課日數(shù)(1)函數(shù)分解NETWORKDAYS函數(shù)專門用于計算兩個日期值之間完整的工作日數(shù)值。這個工作日數(shù)值將不包括雙休日和專門指定的其他各種假期。語法：NETWORKDAYS(Start_date,End_date,Holidays) Start_date表示開始日期；End_date為終止日期，Holidays表示作為特定假日的一個或多個日期。這些參數(shù)值既可以手工輸入，也可以對單元格的值進行引用。(2)實例分析假設(shè)新學(xué)期從2003年9月1日開始到2004年1月15日結(jié)束，希望知道本學(xué)期有多少個授課日，也就是排除雙休日和國家法定假日外的授課工作日。這就是計算授課日數(shù)或工作日數(shù)的問題。首先打開一個空白工作表，在A1、B1、C1單元格輸入“開學(xué)時間”、“結(jié)束時間”、“法定節(jié)日”，然后在其下面的單元格內(nèi)輸入“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引用的是學(xué)期(或工作)的開始日期，B2引用的是學(xué)期結(jié)束的日期，C2:C5區(qū)域引用的是作為法定假日的多個日期。輸入結(jié)束回車即可獲得結(jié)果95，即2003年9月1日到2004年1月15日，排除四個法定假日后的實際授課日是95天。2.折舊值計算無論單位還是家庭，許多固定資產(chǎn)和耐用消費品都存在折舊問題，隨著使用時間的延長，其殘值在不斷減少。假設(shè)某單位有一批2000年購進原價8 500元/每臺的電腦，預(yù)計使用壽命6年，壽命期結(jié)束時的資產(chǎn)殘值約為1 000元，要求使用第二年內(nèi)的折舊值。(1)函數(shù)分解DB函數(shù)使用固定余額遞減法，計算一筆資產(chǎn)在給定期間內(nèi)的折舊值。語法：DB(cost,salvage,life,period,month)Cost為資產(chǎn)原值；Salvage為資產(chǎn)在折舊期末的價值(也稱為資產(chǎn)殘值)；Life為折舊期限(有時也稱作資產(chǎn)的使用壽命)；Period為需要計算折舊值的期間。Period必須使用與life相同的單位；Month為第一年的月份數(shù)，如省略，則假設(shè)為12。(2)實例分析為了在參數(shù)改變以后仍能進行計算，我們打開一個空白工作表，在A1、B1、C1、D1、E1單元格輸入“電腦原值”、“資產(chǎn)殘值”、“使用壽命”、“折舊時間”和“折舊值”，然后在其下面的單元格內(nèi)輸入“8500”、“1000”、“6”、“2”。然后選中E2單元格在其中輸入公式“=DB(A2,B2,C2,D2)”，回車后即可得到結(jié)果“￥1,785.00”，就是說使用期第二年的折舊值為1 785元。如果你要計算其他設(shè)備或財產(chǎn)的折舊值，只需改變A2、B2、C2、D2單元格內(nèi)的數(shù)值即可。3.客流均衡度計算假設(shè)某超市周一到周六的客流人數(shù)是16 359、17 254、18 654、15 398、21 689和220 867，總經(jīng)理需要知道這種情況下的客流分布是否平坦?？梢园慈缦路椒ㄓ嬎悖?1)函數(shù)分解KURT函數(shù)返回數(shù)據(jù)集的峰值。峰值反映與正態(tài)分布相比某一分布的尖銳度或平坦度。正峰值表示相對尖銳的分布。負峰值表示相對平坦的分布。語法：KURT(number1,number2,.)Number1,number2,.是用于計算峰值的130個參數(shù)。也可以不使用這種用逗號分隔參數(shù)的形式，而用單個數(shù)組或數(shù)組引用的形式。(2)實例分析打開一個空白工作表，在A1單元格中輸入“一周客流統(tǒng)計”，然后將上述數(shù)據(jù)依次輸入A2、A3等單元格。然后選中A8單元格，在其中輸入公式“=KURT(D2:D7)”，回車即可獲得結(jié)果“-1.719218897”，這說明超市的客流分布與正態(tài)分布相比是相對平坦的。假如星期天搞特價促銷，客流增加到了50 867人，則計算結(jié)果就會變?yōu)椤?.45379941”。說明超市的客流分布與正態(tài)分布相比比較尖銳了，特價促銷對客流的影響還是非常大的。需要指出的是，KURT函數(shù)在教育統(tǒng)計等領(lǐng)域也有廣泛用途，假如把函數(shù)引用的區(qū)域修改為許多分?jǐn)?shù)的集合，就可以知道考試成績的分布是否尖銳或平坦。4.銷售額預(yù)測假設(shè)某超市周一到周日的日銷售額分別為13、17、16、15、19、21和22(萬元)，總經(jīng)理需要預(yù)測今后一周內(nèi)的日銷售額的最高值和最低值。可以按如下方法進行預(yù)測：(1)函數(shù)分解TREND函數(shù)返回一條線性回歸擬合線的值。即找到適合已知數(shù)組known_ys和known_xs的直線(用最小二乘法)，并返回指定數(shù)組new_xs在直線上對應(yīng)的y值。語法：TREND(known_ys,known_xs,new_xs,const)Known_ys是關(guān)系表達式y(tǒng)=mx+b中已知的y值集合；Known_xs是關(guān)系表達式y(tǒng)=mx+b中已知的可選x值集合；New_xs為需要函數(shù) TREND返回對應(yīng)y值的新x值；Const為一邏輯值，用于指定是否將常量b強制設(shè)為0。(2)實例分析首先要打開一個空白工作表，在A1單元格中輸入“日銷售額”，然后將上述數(shù)據(jù)依次輸入A2、A3至A8單元格。然后選中B2至B8區(qū)域，在Excel的編輯欄輸入公式“=TREND(A2:A8)”，回車即可在B2至B8區(qū)域獲得7個結(jié)果，其中最高銷售額為21.64萬元，最低銷售額為13.5萬元。與KURT函數(shù)一樣，TREND函數(shù)可以用于教育統(tǒng)計中的學(xué)生入學(xué)數(shù)的峰值和低谷，鐵路運輸領(lǐng)域的客流高峰和低谷等的預(yù)測。5.客流與營業(yè)額的相關(guān)分析(1)函數(shù)分解CORREL函數(shù)返回單元格區(qū)域array1和array2之間的相關(guān)系數(shù)。使用相關(guān)系數(shù)可以確定兩種屬性之間的關(guān)系。語法：CORREL(array1,array2)Array1為第一組數(shù)值單元格區(qū)域；Array2為第二組數(shù)值單元格區(qū)域。(2)實例分析假設(shè)一個超市要分析客流量與營業(yè)額是否相關(guān)。首先運行Excel打開一個空白工作表，在A1至A31單元格輸入八月份的每日客流人數(shù)，然后在B1至B31輸入八月份每日的營業(yè)額，再將上述兩個區(qū)域的名稱定義為“日客流人數(shù)”和“日營業(yè)額”。接下來就可以選中工作表中的某個空白單元(例如B32)，作為存儲運算結(jié)果的位置。在Exce