|
Excel函數(shù)公式應(yīng)用（四） 在Excel中利用系統(tǒng)時間和出生年月計算年齡是人事管理、工資統(tǒng)計中經(jīng)常性遇到的工作，筆者由于工作關(guān)系對此有些研究，現(xiàn)將有關(guān)計算方法介紹如下，供讀者朋友們參考： 1、利用DAYS360、CEILING和TRUNC函數(shù) （1）函數(shù)簡介 a.DAYS360函數(shù) 它能按每年360天(每月30天)計算出兩個日期間的天數(shù)，作為計算工齡的工具非常方便。它的語法為： DAYS360(Start_date，end_date，method) 其中，Start_date是計算時間段的起始日期，end_date是計算時間段的結(jié)束日期，method用來指定計算方法的邏輯值(取FALSE或忽略使用美國方法，取TRUE則使用歐洲方法)。 另外，不同地方計算工齡的規(guī)則不盡相同。有的按“虛工齡”計算，如1998年6月1日至2000年12月31日工齡為3年；而有的則按“實工齡”計算，1998年6月1日至2000年12月31日工齡為2年；對此可使用CEILING函數(shù)或TRUNC函數(shù)處理。 b.CEILING函數(shù) 它的語法為： CEILING(number，significance) 其中number為待計算的數(shù)值，significance確定取整計算的倍數(shù)；該函數(shù)可將number沿著絕對值增大的方向，計算出一個最接近(或最小倍數(shù)significance)的整數(shù)。 c.TRUNC函數(shù) 它的作用是將數(shù)字的指定部分截去，計算出一個最接近的整數(shù)或小數(shù)，語法為： TRUNC(number，num_digits) 其中number為待計算的數(shù)值，num_digits用于指定小數(shù)部分的截取精度，取0時不保留小數(shù)、取1時保留一位小數(shù)(依次類推)。 （2）計算公式 a.“虛工齡” 根據(jù)計算要求和有關(guān)函數(shù)的特點，計算“虛工齡”的公式為：“=CEILING((DAYS360(A1，B1))/360，1)”。公式中的A1和 B1分別存放工齡的起止日期，“DAYS360(A1，B1)”計算兩個日期間的天數(shù)，(DAYS360(A1，B1))/360則按一年360天計算出工齡。由于工齡一般以年為單位，故用CEILING函數(shù)將上面的計算結(jié)果(沿絕對值增大的方向)取整，從而得出“虛工齡”。 b.“實工齡” 計算“實工齡”的公式為：“=TRUNC((DAYS360(A1，B1))/360，0)”，公式中計算工齡天數(shù)的方法與上面的相同。TRUNC函數(shù)將(DAYS360(A1，B1))/360的計算結(jié)果截去小數(shù)部分，從而得出“實工齡”。如果計算結(jié)果需要保留一位小數(shù)，只須將公式修改為“= TRUNC((DAYS360(A1，B1))/360，1)”即可。 2、YEAR和RIGHT函數(shù) (1)函數(shù)簡介 a.YEAR函數(shù) 它可以計算出日期序列數(shù)(如兩個日期相減的結(jié)果)所對應(yīng)的年份數(shù)，其語法為：YEAR(Serial_ number)，其中Serial_ number為待計算的日期序列數(shù)，既可以是一個具體的數(shù)值，也可以是一個表達式。 b.RIGHT函數(shù) 該函數(shù)用來提取字符串最右邊的若干個字符，因為YEAR函數(shù)的計算結(jié)果帶有19等字樣，必須利用RIGHT函數(shù)將它過濾掉。該函數(shù)的語法為： RIGHT(Text，Num_chars)，其中Text是待計算的字符串，Num_chars用來指定從右向左提取的字符串長度(忽略時取1)，例如 “=RIGHT(“電腦愛好者”，3)”的計算結(jié)果為“愛好者”。 （2）計算公式 由于YEAR和RIGHT函數(shù)的特點，它們組成的公式只能計算“實工齡”，具體形式為“=RIGHT(YEAR(A1-B1)，2)”。公式中的A1 和B1分別存放工齡的截止和起始日期(正好與DAYS360函數(shù)相反)，“YEAR(A1-B1)”計算出兩個日期間的年份數(shù)，RIGHT(YEAR (A1-B1)，2)則通過自右向左提取年份的后兩位(工齡一般都是一位或兩位數(shù))。計算出來的一位數(shù)工齡前有個“0”，與習(xí)慣不太相符，這是上述公式的缺點。 3、N和INT函數(shù) （1）函數(shù)簡介 a.N函數(shù) N函數(shù)屬于信息函數(shù)之列，它可以完成單元格對象的轉(zhuǎn)換。就是將數(shù)值轉(zhuǎn)換成數(shù)字，日期轉(zhuǎn)換成序列值，TRUE轉(zhuǎn)換成1，其它對象轉(zhuǎn)換成0。其語法為：N(value)，其中的value是待轉(zhuǎn)換的單元格對象，它可以是數(shù)值、日期等數(shù)據(jù)，也可以是一個表達式。 b.INT函數(shù) 該函數(shù)可以將一個數(shù)值向下取整為最接近的整數(shù)。語法為：INT(number)，其中number是待取整的一個實數(shù)或表達式，用它將計算結(jié)果取整為“實工齡”非常方便。 （2）計算公式 由于INT函數(shù)的固有特點，用N和INT函數(shù)構(gòu)成的公式只能計算“實工齡”，具體形式為“=INT((N(A1-B1))/365)”。公式中的A1 和B1分別存放工齡的截止和起始日期，“N(A1-B1)”計算工齡的序列數(shù)(其實就是工齡的天數(shù))，(N(A1-B1))/365將N(A1-B1)的計算結(jié)果轉(zhuǎn)換為工齡，由于工齡數(shù)一般是小數(shù)，于是通過INT函數(shù)將它向下取最接近的整數(shù)。 十一、關(guān)于VLOOKUP函數(shù)的用法詳解和TRUE和FALSE的應(yīng)用
“Lookup”的漢語意思是“查找”，在Excel中與“Lookup”相關(guān)的函數(shù)有三個：VLOOKUP、HLOOKUO和LOOKUP。下面介紹VLOOKUP函數(shù)的用法。 1、功能在表格的首列查找指定的數(shù)據(jù)，并返回指定的數(shù)據(jù)所在行中的指定列處的數(shù)據(jù)。 2、語法標準格式： VLOOKUP(lookup_value,table_array,col_index_num , range_lookup) 解釋： VLOOKUP(‘你要檢索的內(nèi)容或指定單元格’,‘你要檢索的范圍,檢索到內(nèi)容時返回你檢索表的第幾列中的內(nèi)容’, ‘真或假參數(shù)真代表查詢的表已經(jīng)排序，假代表沒有排序’) 例：VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE) 說 明：在表SHEET2中檢索當前表中A2中的內(nèi)容，如果檢索到，就返回表SHEET2中B2中的內(nèi)容，因為B2是表SHEET2中的第二列，所以 VLOOKUP的第三個參數(shù)，使用2，表示如果滿足條件，就返回查詢表的第二列，最后的參數(shù)FALSE表示‘假’，意思是被查詢的表，沒有排序，這種情況下，會從被查詢的表中第一行開始，一直查詢到結(jié)束。 3、語法解釋VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以寫為： VLOOKUP(需在第一列中查找的數(shù)據(jù),需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號,邏輯值True或False) （1）Lookup_value為“需在數(shù)據(jù)表第一列中查找的數(shù)據(jù)”，可以是數(shù)值、文本字符串或引用。 （2）Table_array 為“需要在其中查找數(shù)據(jù)的數(shù)據(jù)表”，可以使用單元格區(qū)域或區(qū)域名稱等。 a.如果 range_lookup 為 TRUE或省略，則 table_array 的第一列中的數(shù)值必須按升序排列，否則，函數(shù) VLOOKUP 不能返回正確的數(shù)值。 如果 range_lookup 為 FALSE，table_array 不必進行排序。 b.Table_array 的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值。若為文本時，不區(qū)分文本的大小寫。 （3）Col_index_num 為table_array 中待返回的匹配值的列序號。 Col_index_num 為 1 時，返回 table_array 第一列中的數(shù)值； Col_index_num 為 2 時，返回 table_array 第二列中的數(shù)值，以此類推。 如果Col_index_num 小于 1，函數(shù) VLOOKUP 返回錯誤值 #VALUE!； 如果Col_index_num 大于 table_array 的列數(shù)，函數(shù) VLOOKUP 返回錯誤值 #REF!。 （4）Range_lookup 為一邏輯值，指明函數(shù) VLOOKUP 返回時是精確匹配還是近似匹配。如果為 TRUE 或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value 的最大數(shù)值；如果 range_value 為 FALSE，函數(shù) VLOOKUP 將返回精確匹配值。如果找不到，則返回錯誤值 #N/A。 4、應(yīng)用例子AB C D 1編號姓名工資科室 22005001周杰倫2870辦公室 32005002蕭亞軒2750人事科 42005006鄭智化2680供應(yīng)科 52005010屠洪剛2980銷售科 62005019孫楠 2530財務(wù)科 72005036孟庭葦2200工 會 A列已排序（第四個參數(shù)缺省或用TRUE） VLOOKUP(2005001,A1:D7,2,TRUE) 等于“周杰倫” VLOOKUP(2005001,A1:D7,3,TRUE) 等于“2870” VLOOKUP(2005001,A1:D7,4,TRUE) 等于“辦公室” VLOOKUP(2005019,A1:D7,2,TRUE) 等于“孫楠” VLOOKUP(2005036,A1:D7,3,TRUE) 等于“2200” VLOOKUP(2005036,A1:D7,4,TRUE) 等于“工 會” VLOOKUP(2005036,A1:D7,4) 等于“工 會” 若A列沒有排序，要得出正確的結(jié)果，第四個參數(shù)必須用FALAE VLOOKUP(2005001,A1:D7,2,FALSE) 等于“周杰倫” VLOOKUP(2005001,A1:D7,3,FALSE) 等于“2870” VLOOKUP(2005001,A1:D7,4,FALSE) 等于“辦公室” VLOOKUP(2005019,A1:D7,2,FALSE) 等于“孫楠” VLOOKUP(2005036,A1:D7,3,FALSE) 等于“2200” VLOOKUP(2005036,A1:D7,4,FALSE) 等于“工 會” 5、TRUE和FALSE的應(yīng)用關(guān)于先舉個例子，假如讓你在數(shù)萬條記錄的表格中查找給定編號的某個人，假如編號已按由小到大的順序排序，你會很輕松地找到這個人；假如編號沒有排序，你只好從上到下一條一條地查找，很費事。 用VLOOKUP查找數(shù)據(jù)也是這樣，當?shù)谝涣幸雅判?，第四個參數(shù)用TRUE（或確省），Excel會很輕松地找到數(shù)據(jù)，效率較高。當?shù)谝涣袥]有排序，第四 個參數(shù)用FALSE，Excel會從上到下一條一條地查找，效率較低。 筆者覺得，若要精確查找數(shù)據(jù)，由于計算機運算速度很快，可省略排序操作，直接用第四個參數(shù)用FALSE即可。 十二、Lookup和Vlookup有哪些區(qū)別？Excel查詢函數(shù)中，Lookup和Vlookup有哪些區(qū)別？它們在應(yīng)用中應(yīng)該如何把握？請看本文講解。 1、Lookup——數(shù)與行列比Lookup的工作職責(zé)是什么呢?用一個數(shù)與一行或一列數(shù)據(jù)依次進行比較，發(fā)現(xiàn)匹配的數(shù)值后，將另一組數(shù)據(jù)中對應(yīng)的數(shù)值提取出來。 2、工資稅率表：用數(shù)值比較根據(jù)不同的工資進行不同的稅率計算是一個常見的應(yīng)用。我們來看這張“工資稅率查詢”表(見圖1)?，F(xiàn)在要在右側(cè)根據(jù)“收入”(F列)，直接得到對應(yīng)的“稅率”(G列)。在計算第1個“稅率”時，輸入函數(shù)公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”，回車，便可得到 “36.00%”。 這個結(jié)果是怎么來的?用F4中的第1個收入數(shù)“$123,409”，與左側(cè)表的“收入最低”各檔數(shù)據(jù)(“$B$3:$B$8”)進行對比，雖然 “$123,409”在“收入最低”各檔數(shù)中沒有完全一致的數(shù)據(jù)與之匹配，但是會與其中小于它的最大數(shù)“$58,501”相匹配。這樣，同一行對應(yīng)的 “36.00%”就提取出來了。 3、圖書銷售表：用文本比較Lookup函數(shù)的對比數(shù)還可以是文本。在這張圖書銷售查詢表中(見圖2)，用下表輸入的“編號”(A15單元格)文本當作查詢數(shù)，與上表的 “編號”一列($A$3:$A$11)進行對比，查詢到了匹配的文本后，將“教材名稱”一列($B$3:$B$11)對應(yīng)的數(shù)據(jù)提取出來。公式是 “=LOOKUP(A15,$A$3:$A$11,$B$3:$B$11)”。 4、Vlookup——數(shù)與表格比Lookup有一個大哥——Vlookup函數(shù)。兩兄弟有很多相似之處，但大哥本領(lǐng)更大。Vlookup用對比數(shù)與一個“表”進行對比，而不是Lookup函數(shù)的某1列或1行，并且Vlookup可以選擇采用精確查詢或是模糊查詢方式，而Lookup只有模糊查詢。 5、模糊匹配用Vlookup函數(shù)進行模糊查詢時，幾乎與Lookup的作用完全一致。我們用Vlookup函數(shù)來提取第1個例子中的工資稅率結(jié)果。函數(shù)公式為“=VLOOKUP(F4,$B$3:$D$8,3,TRUE)”。 在這個函數(shù)中，用第1個收入“$123,409”(F4單元格)當作對比數(shù)，用它與左側(cè)表(“$B$3:$D$8”)的第1列數(shù)進行對比，雖然 “$123,409”在“收入最低”各檔數(shù)中沒有完全一致的數(shù)據(jù)與之匹配，但是函數(shù)的最后一個參數(shù)是“TURE”(“TURE”就是模糊查詢)，所以它會 與其中小于它的最大數(shù)“$58,501”相匹配。并將表中第3列(函數(shù)的第3個參數(shù)為“3”)對應(yīng)的數(shù)據(jù)提取出來，所以結(jié)果同樣是“36.00%”。 6、訂單明細表：精確匹配有時候，我們需要精益求精。在下面這個“訂單明細表”(見圖3)中，最后一列“貨運費用”中的數(shù)據(jù)要通過“交貨方式”從左側(cè)“配送公司收費表”中進行匹配查詢。這是一個典型的精確查詢的例子，計算第1個數(shù)據(jù)的函數(shù)公式是“=VLOOKUP(H3,$B$2:$D$6,3,FALSE)”。 小提示： 把最后一個參數(shù)從“TRUE”變更成“FLASE”，就是精確匹配。而精確查詢，就是查詢數(shù)要與查詢表第1列中的數(shù)據(jù)完全一致才能匹配提取，否則結(jié)果返回錯誤值“#N/A”。 點評： Excel為我們提供了近20個有關(guān)“查找和引用”的函數(shù)，除了最常用的Lookup、Vlookup，還有Choos、Row、Colum、 Index和Match等，大家可以通過函數(shù)的幫助查看具體的功能。這些函數(shù)往往不是單獨使用，可以與其他函數(shù)和Excel中的一些功能進行配合。 十三、Excel的SUM函數(shù)9種公式設(shè)置范例1、數(shù)組求和：{=SUM((G12:G21>100)*G12:G21)}[公式說明]：本公式為數(shù)組公式，可以對G12:G21區(qū)域中大于100的數(shù)據(jù)進行求和，而排除小于等于100的數(shù)據(jù)。輸入公式時必須按【Ctrl+Shift+Enter】組合鍵結(jié)束，否則無法得到正確結(jié)果。 [使用注意]：1、公式中"G12:G21>100"部分表示求和條件，后跟實際求和區(qū)域"G12:G21"。如果有多個條件，可一并羅列出來。例如求大于100且小于115的數(shù)據(jù)之和，公式如下:=SUM(G12:G21>100)*(G12:G21<115)*G12:G21)。2、此數(shù)組公式只適用于單個區(qū)域求和，如果有多個區(qū)域，只能用多個SUM求和，然后相加。例如對G12:G21和H12:H21區(qū)域中大于100的數(shù)匯總，公式如下:=SUM(SUM((G12:G21>100)*G12:G21),SUM((H12:H21>100)*H12:H21))。3、對于SUM函數(shù)的數(shù)組公式，可以用SUMPRODUCT函數(shù)來代替，從而將數(shù)組公式轉(zhuǎn)換成普通公式。例如本案例的公式用SUMPRODUCT函數(shù)后，普通公式如下:=SUMPRODUCT((G12:G21>100)*G12:G21)。 2、數(shù)據(jù)類型轉(zhuǎn)換求和：
=SUM(VALUE(H5),H6:H10,J5:J10,L5:L10)；=SUM(--(H5),H6:H10,J5:J10,L5:L10)；=SUM((H5)*1,H6:H10,J5:J10,L5:L10)；=SUM((H5)/1,H6:H10,J5:J10,L5:L10) [公式說明]：SUM函數(shù)用于對單元格區(qū)域的數(shù)據(jù)或者邏輯值、表達式進行求和，它有1-255個參數(shù)。鑒于本題的特殊性，公式也可以改為"=SUM(區(qū)域1)"，函數(shù)會忽略區(qū)域中的文本。 [使用注意]：1、SUM函數(shù)有1-255個參數(shù)。參數(shù)可以是區(qū)域，也可以是表達式。如:=SUM(G5,10*25,G6*8)。2、如果是文本型數(shù)字參數(shù)，SUM函數(shù)是可以直接求和的；如果是引用單元格中的文本型數(shù)字(單元格的數(shù)字前添加半角單引號“'”),則求和時將忽略。例如G5的值是“'95”，那么以下公式的結(jié)果大不同。=SUM(10,"95")—結(jié)果為105,=SUM(10,G5)—結(jié)果為10。3、如果一定要對單元格中文本型數(shù)字進行求和，可以將它轉(zhuǎn)換成數(shù)值。轉(zhuǎn)換方法包括多種:利用VALUE函數(shù)轉(zhuǎn)換、利用"--"轉(zhuǎn)換和"*1"、"/1"方式轉(zhuǎn)換。例如:=SUM(10,VALUE(G5))、=SUM(10,--(G5))、=SUM(10,(G5)*1) 3、多條件求和1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)} [公式說明]：對于兩個范圍求和，即滿足兩個條件中任意一個條件都進行求和，這和同時滿足兩個條件才求和的公式思路不同。"案例2"的使用注意"中已提到，多個條件同時滿足才求和需要用"*"連接所有條件，表示同時滿足多條件，而本案例中對多個條件用"+"來連接，表示對于多條例中滿足任意一個條件就可以進行求和。本公式為數(shù)組公式。 [使用注意]： （1）多條件滿足任意條件即求和，在設(shè)置公式時對于所有條件需要用"+"連接，如本例中"(G23:G32<100)+(G23:G32>110)",然后對條件用括號括起來再與實際求和區(qū)相乘。如果缺少條件的外括號將得到錯誤結(jié)果。 （2）如果不只兩個條件，也用同樣方式將所有條件羅列出來并相加。例如求區(qū)域中小于100或者等于120的數(shù)據(jù)和，那么數(shù)組公式如下:=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32) 4、多條件求和2：{=SUM((H34:H43="一車間")*(I34:I43="男")*J34:J43)} [公式說明]：本公式以H、I兩列數(shù)據(jù)作為限制條件，對J列數(shù)據(jù)匯總。“(H34:H43="一車間")*(I34:I43="男")”表示同時滿足兩個條件，如果還有更多條件，也可以同時羅列出來，利用“*”符號連接，然后再與求和區(qū)相乘，得到最后的匯總結(jié)果。 [使用注意]：1、前三個案例是以求和數(shù)據(jù)自身為條件對目標區(qū)域進行求和，本案例以不同的區(qū)域作為限制，條件區(qū)有兩個，對第三區(qū)域進行求和。在設(shè)置公式時，三個區(qū)域的單元格個數(shù)必須一致。例如以下公式將產(chǎn)生錯誤結(jié)果:=SUM((H34:H43="一車間")*(I34:I44="男")*J34:J43)、=SUM((H34:H43="一車間")*(I35:I44="男")*J34:J43)。2、多條件求和時，條件錄用區(qū)域與求和區(qū)域可以在不同列，三個區(qū)域只需要寬度、高度一致即可。它們所在的列可以不同，起止行也可以不同，甚至三個區(qū)域可以分別位于不同的工作表中，仍然可以得到需要的結(jié)果。例如條件區(qū)在Sheet2工作表，求和區(qū)在當前工作表，公式可以修改為:=SUM((Sheet2!H34:H43="一車間")*(Sheet2!I34:I43="男")*J34:J43)。3、本公式可以用另一種簡化寫法，仍然可以返回正確結(jié)果:=SUM((H34:H43&I34:I43="一車間男")*J34:J43) 5、多工作表求和時表名稱快捷輸入：=SUM(附表一:附表五!B3:B10) [公式說明]：對多表相同區(qū)域求和，不需要錄入每個工作表的求和地址，只需將第一個工作表名和最后一個表名用冒號連接，后跟"!"與單元格地址，以此作為SUM函數(shù)的參數(shù)即可。其中"!"表示前面的字符為工作表名稱。 [使用注意]：1、本方法僅適用于每個工作表地址一致的情況，否則只能逐個輸入地址再求和。2、多表求和，SUM函數(shù)的參數(shù)，工作表與單元格地址可以手動輸入，也可以通過選擇工作表中單元格地址來產(chǎn)生引用。具體步驟如下:輸入"=SUM("，然后選擇待匯總的第一個工作表B3:B10區(qū)域，按住【Shift】鍵再單擊工作表標簽中待求和的最后一個工作表名，最后按【Enter】鍵結(jié)果結(jié)束即可。3、如果工作表名中包含一些特殊字符，例如"!"、"@"，公式中的工作表名需要用單引號括起來，例如:=SUM('A組:!E組'!B3:B10) 6、快速設(shè)置當前表以外的所有工作表相同區(qū)域的總和公式：=SUM('*'!B3) [公式說明]1、公式中兩個單引號中間加"*"表示除當前表以外的所有工作表。2、在輸入公式后，程序會自動將"'*'"轉(zhuǎn)換成實際的工作表名。當按下【Enter】鍵后，公式將變成:=SUM(附表一:附表五!B3) [使用注意]1、"'*'"只能手動輸入，而不能用鼠標選擇工作表來產(chǎn)生引用。2、公式中B3必須使用相對引用。3、本公式用法和"案例7"是不同的。在本例中雖然也可以用"案例7"的方法按住【Shift】鍵來選擇需要求和的工作表產(chǎn)生引用，但是操作煩瑣，特別是總表不在最后或者最前面，而是在待求和的工作表中間時，用"案例7"的方法需要選擇五次，而本例公式中的"*"可以自動的找到排除當前表之外的所有工作表數(shù)據(jù)。也就是說"總表"不管在任何位置，都可以用同樣的公式完成求和，而不需手動修改。4、公式中"*"左右的單引號必須是半角符號。 7、用SUM函數(shù)計數(shù)：{=SUM((H94:H102="男")*(I94:I102="漢"))} [公式說明]：SUM函數(shù)雖然是求和函數(shù)，在很多時候可以用來計數(shù)。本例中利用"H94:H101="男""這一表達式返回一串包含TRUE和FALSE的數(shù)組，最后用"*1"將邏輯值轉(zhuǎn)換成數(shù)值，并用SUM函數(shù)匯總。 [使用注意]：1、表達式"H94:H101="男""得到的是包含邏輯值的數(shù)組。SUM函數(shù)可以對邏輯參數(shù)進行求和，但是包含于數(shù)組中的邏輯值值卻會被忽略。例如以下公式:=SUM(TRUE,1)—結(jié)果為2，=SUM({TRUE,1})結(jié)果為1。但是通過"*1"將邏輯值轉(zhuǎn)換成數(shù)值后就可以直接求和了。例如:=SUM({TRUE,1}*1)—結(jié)果為2。2、將邏輯值轉(zhuǎn)換成數(shù)值，除了用"*1"之外，也可以改用"--"或者"/1"等手法，但是用VALUE函數(shù)卻不行，它只能將文本型數(shù)字轉(zhuǎn)換成數(shù)值。例如:=SUM(--({TRUE,1}))—結(jié)果等于2；=SUM(({TRUE,1})/1)—結(jié)果等于1；=SUM(VALUE({TRUE,1}))—結(jié)果為錯誤值，因為VALUE函數(shù)無法轉(zhuǎn)換成TRUE。 8、求1累加到100之和：{=SUM(ROW(1:100))} [公式說明]：本公式利用ROW函數(shù)返回1到100的序列，然后用SUM函數(shù)將這個序列匯總得到1—100的合計。 [使用注意]：1、ROW函數(shù)只能產(chǎn)生1到1048576之間的自然數(shù)(Excel2003版本為65536)，且不帶小數(shù)。但是通過換算也可以完成其他序列的求和。例如1/1、1/2、1/3、1/4……1/99、1/100之和。可以用以下公式:=SUM(1/ROW(1:100))。如果是求1開1次方，2開2次方，3開3次方至100開100次方的總和，那么可以用以下數(shù)組公式:{=SUM(ROW(1:100)^(1/ROW(1:100)))}。如果是對1—100之間的奇數(shù)進行求和，可以用以下數(shù)組公式:{=SUM((ROW(1:50)*2-1))}。2、ROW(1:100)產(chǎn)生的結(jié)果是一個包含1到100之間的自然數(shù)數(shù)組。用SUM函數(shù)對這種數(shù)組求和時必須用數(shù)組公式，否則只能對數(shù)組中的第一個數(shù)值進行求和。如果將"ROW(1:100)"寫成"{1,2,3,4}"這種形式的數(shù)組則可以按照普通公式處理。 9、多個工作表相同區(qū)域求前三名產(chǎn)量之和：{=SUM(LARGE(CHOOSE({1,2,3,4,5},附表一!B3:B10,附表二!B3:B10,附表三!B3:B10,附表四!B3:B10,附表五!B3:B10),ROW(1:3)))} [公式說明]：如以下公式:=SUM(LARGE(附表一:附表五!B3:B13,ROW(1:3)))。因為LARGE函數(shù)不支持多個工作表地址作為第一參數(shù)，即三維引用。所以本公式利用CHOOSE函數(shù)將三維引用轉(zhuǎn)換成二維數(shù)組后，就可以作為LARGE函數(shù)的參數(shù)進行運算了。用LARGE函數(shù)提取前三最大值后再用SUM函數(shù)匯總。 [使用注意]：公式的常量數(shù)組"{1,2,3,4,5}"可以轉(zhuǎn)換為"COLUMN(A:E)"，而不用"ROW(1:5)"。因為"ROW(1:5)"等于"{1;2;3;4;5})。區(qū)別為一個是橫向數(shù)組，一個是縱向數(shù)組。 Excel表格中根據(jù)身份證號碼自動填出生日期、性別、年齡的函數(shù)公式 18位身份證號碼轉(zhuǎn)換成出生日期的函數(shù)公式：如果E2中是身份證,在F2中求出出生日期，F2=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)) 自動錄入男女：=IF(MOD((IF(LEN(e2)=18,MID(e2,17,1),MID(e2,15,1))),2)=0,"女","男")15/18位都可以的公式：轉(zhuǎn)換出生日期：=IF(LEN(e2)=18,TEXT(MID(e2,7,8),"#-00-00"),"19"&TEXT(MID(e2,7,6),"#-00-00"))15/18位自動錄入男女：=IF(E2="","",IF(MOD(RIGHT(LEFT(E2,17),1),2)=0,"女","男")) 計算年齡（新舊身份證號都可以）：=IF(AND(E2=""),"",IF(MIDB(E2,7,2)="19",107-MIDB(E2,9,2),107-MIDB(E2,7,2))) Excel統(tǒng)計成績經(jīng)常用到的一個公式（根據(jù)分數(shù)給出優(yōu)，良，中，差） 因為本人從事教學(xué)工作，經(jīng)常要用到Excel對成績進行計算匯總，其中遇到的一個典型的問題是如何根據(jù)分數(shù)來給出優(yōu)、良、中、差的等級。 如果只有兩個分數(shù)段（如及格、不及格兩個級別）很好辦，假設(shè)成績數(shù)據(jù)已經(jīng)輸入單元格A1，直接用Excel公式輸入條件A1>=60，為真時填寫“及格”，為假時填寫“不及格”就可以了。這時顯示在Excel單元格里的公式是：=IF(A1>=60, “及格”,“不及格”)。 如果有多個分數(shù)段，比如要求: x<60-----------------不及格 x>=60 AND x<75--------及格 x>=75 AND x<85--------良 x>=85-----------------優(yōu) 其實很簡單，只要了解Excel里IF函數(shù)參數(shù)和返回值的用法，第一個參數(shù)是條件，第二個參數(shù)是條件為真的返回值，第三個參數(shù)是條件為假的返回值；而且IF函數(shù)可以嵌套(根據(jù)Excel2003的幫助文件，最多嵌套可達七層)，這樣上面的問題可以用如下公式來解決： =IF(A1<60,"不及格",IF(A1<75,"及格",IF(A1<85,"良","優(yōu)"))) (這時無法使用Excel的交互式的方法輸入，可以把這個公式修改后復(fù)制到單元格里) 因為Excel特有的單元格拖拽功能，很容易就把這個公式給復(fù)制到一整列上了，這樣就實現(xiàn)了一系列成績自動的給出等級，使用起來還是比較方便的。十三、在Excel中用數(shù)據(jù)有效性防止公式被修改 通常，在Excel中要保護單元格中的公式可以用保護工作表的方法來實現(xiàn)。但默認工作表中所有的單元格都是鎖定的，要讓部分單元格能夠輸入數(shù)據(jù)，同時又保護包含公式的單元格，需要解除一些單元格的鎖定狀態(tài)，操作起來稍微有些繁瑣。如果僅僅是要保護公式的結(jié)構(gòu)不被修改，或是由于不便使用工作表保護，可以用數(shù)據(jù)有效性的方法，雖然單元格中的公式仍然能被刪除，但操作起來相對比較簡單，不妨一試。在Excel 2003中方法如下：.首先選擇包含公式的單元格 如果要選擇工作表中所有包含公式的單元格，可以用下面的方法： （1）按F5鍵，打開“定位”對話框，單擊“定位條件”按鈕。 （2）在“定位條件”對話框中，選擇“公式”。單擊“確定”Excel將選擇工作表中所有包含公式的單元格。 （2）單擊菜單“數(shù)據(jù)→有效性→設(shè)置”，在“允許”下，選擇“自定義”，在“公式”下方的文本框中輸入一個英文的等于號（=），單擊“確定”按鈕。 也可以將“文本長度”設(shè)置為小于0，即在“允許”下選擇“文本長度”，在“數(shù)據(jù)”下選擇“小于”，在“最大值”下輸入“0”。 這樣，當修改單元格中的公式時，Excel會彈出警告，并禁止修改。 十四、電子表格excel公式使用大全詳解消除Excel中無意義的“0” 用SUM求和函數(shù)來處理考勤統(tǒng)計或合計工資、獎金和津貼的總數(shù)時，若引用的單元格區(qū)域沒有數(shù)據(jù)，Excel仍然會計算出一個結(jié)果“0”（圖1），這些“0”毫無意義，不僅影響表單數(shù)據(jù)的查看，而且打印出來也不符合財務(wù)和統(tǒng)計報表規(guī)定，該怎么辦？手動一個個地刪除顯然不可取，筆者這里提供幾種方法或許對你有用。 我們以教職工缺勤統(tǒng)計表為實例來看看具體實現(xiàn)方法： 方法一：去除視圖中的“0”值顯示 點擊“工具→選項”，在“選項”窗口中點擊“視圖”，找到“零值”，單擊前面的“√”即可迅速實現(xiàn)隱藏單元格中無意義的“0”值（圖2）。 方法二：利用條件格式隱藏“0”值 選中這個工作表，點擊“格式→條件格式”，在條件中單擊倒三角選取“等于”，再在條件取值框中輸入“0”，然后單擊“格式”，在單元格格式“顏色”選取框中選取“白色”再單擊“確定”回到條件格式框，單擊“確定”即可完成隱藏（圖3）。 提示：方法二不需要選中整個工作表，僅僅幾個步驟即可實現(xiàn)，個人認為簡單易行，建議大家采用。 方法三：單元格格式自定義法 用“Ctrl+A”選中整個工作表，右擊鼠標，在出現(xiàn)的快捷菜單中單擊“設(shè)置單元格格式”，在單元格格式對話框“數(shù)字”選項卡分類中點擊“自定義”，在“類型”欄輸入“0;-0;;@”（圖4），最后單擊“確定”即可實現(xiàn)。 提示：此種方法適用于單元格數(shù)值為整數(shù)的情況。 方法四：利用函數(shù)公式法 下面我們以教工獎金、課時津貼發(fā)放表為例（圖5）。 在總額E3欄輸入公式“=IF（（（C3+D3）〈〉0），SUM（C3:D3），“”）”，然后雙擊“填充柄”使公式應(yīng)用于所有的單元格，單擊“保存”即可達到消除“0”值的目的。 提示：公式中IF函數(shù)測試SUM 函數(shù)所引用的單元格區(qū)域是否全部不等于“0”，當不等于“0”時返回true（真），反之返回fales（假）。為真時IF函數(shù)執(zhí)行SUM（C3:D3），為假時在存放計算結(jié)果的E3單元格中填入空格，這樣即可避免在F1單元格中出現(xiàn)“0”。 [url=http://www./X_AdvCom_Get.asp?UserID=312114][IMG]http://www./images/logo.GIF[/IMG][/URL] 1、ABS函數(shù)函數(shù)名稱：ABS 主要功能：求出相應(yīng)數(shù)字的絕對值。 運用格式：ABS(number) 參數(shù)說明：number代表須要求絕對值的數(shù)值或引用的單元格。 使用舉例：如果在B2單元格中輸入公式：=ABS(A2)，則在A2單元格中無論輸入正數(shù)（如100）還是負數(shù)（如-100），B2中均顯示出正數(shù)（如100）。 特別提醒：如果number參數(shù)不是數(shù)值，而是一些字符（如A等），則B2中返回不正確值“#VALUE！”。 2、AND函數(shù)函數(shù)名稱：AND 主要功能：返回邏輯值：如果所有參數(shù)值均為邏輯“真（TRUE）”，則返回邏輯“真（TRUE）”，反之返回邏輯“假（FALSE）”。 運用格式：AND(logical1,logical2, ...) 參數(shù)說明：Logical1,Logical2,Logical3……：表示待測試的條件值或表達式，最多這30個。 使用舉例：在C5單元格輸入公式：=AND(A5>=60,B5>=60)，確認。如果C5中返回TRUE，說明A5和B5中的數(shù)值 笥詰扔?0，如果返回FALSE，說明A5和B5中的數(shù)值至少有一個小于60。 特別提醒：如果指定的邏輯條件參數(shù)中包含非邏輯值時，則函數(shù)返回不正確值“#VALUE!”或“#NAME”。 3、AVERAGE函數(shù)函數(shù)名稱：AVERAGE 主要功能：求出所有參數(shù)的算術(shù)平均值。 運用格式：AVERAGE(number1,number2,……) 參數(shù)說明：number1,number2,……：須要求平均值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。 使用舉例：在B8單元格中輸入公式：=AVERAGE(B7:D7,F7:H7,7,8)，確認后，即可求出B7至D7區(qū)域、F7至H7區(qū)域中的數(shù)值和7、8的平均值。 特別提醒：如果引用區(qū)域中包含“0”值單元格，則計算在內(nèi)；如果引用區(qū)域中包含空白或字符單元格，則不計算在內(nèi)。 4、COLUMN 函數(shù)函數(shù)名稱：COLUMN 主要功能：顯示所引用單元格的列標號值。 運用格式：COLUMN(reference) 參數(shù)說明：reference為引用的單元格。 使用舉例：在C11單元格中輸入公式：=COLUMN(B11)，確認后顯示為2（即B列）。 特別提醒：如果在B11單元格中輸入公式：=COLUMN()，也顯示出2；與之相對應(yīng)的還有一個返回行標號值的函數(shù)——ROW(reference)。 5、CONCATENATE函數(shù)函數(shù)名稱：CONCATENATE 主要功能：將多個字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個單元格中。 運用格式：CONCATENATE(Text1，Text……) 參數(shù)說明：Text1、Text2……為須要連接的字符文本或引用的單元格。 使用舉例：在C14單元格中輸入公式：=CONCATENATE(A14,"@",B14,".com")，確認后，即可將A14單元格中字符、@、B14單元格中的字符和.com連接成一個整體，顯示在C14單元格中。 特別提醒：如果參數(shù)不是引用的單元格，且為文本格式的，請給參數(shù)加上英文狀態(tài)下的雙引號，如果將上述公式改為：=A14&"@"&B14&".com"，也可以達到相同的目的。 6、COUNTIF函數(shù)函數(shù)名稱：COUNTIF 主要功能：統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目。 運用格式：COUNTIF(Range,Criteria) 參數(shù)說明：Range代表要統(tǒng)計的單元格區(qū)域；Criteria表示指定的條件表達式。 使用舉例：在C17單元格中輸入公式：=COUNTIF(B1:B13,">=80")，確認后，即可統(tǒng)計出B1至B13單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。 特別提醒：允許引用的單元格區(qū)域中有空白單元格出現(xiàn) 7、DATE函數(shù)函數(shù)名稱：DATE 主要功能：給出指定數(shù)值的日期。 運用格式：DATE(year,month,day) 參數(shù)說明：year為指定的年份數(shù)值（小于9999）；month為指定的月份數(shù)值（可以大于12）；day為指定的天數(shù)。 使用舉例：在C20單元格中輸入公式：=DATE(2003,13,35)，確認后，顯示出2004-2-4。 特別提醒：由于上述公式中，月份為13，多了一個月，順延至2004年1月；天數(shù)為35，比2004年1月的實際天數(shù)又多了4天，故又順延至2004年2月4日。 上頁我們介紹了ABS、AND、AVERAGE、COLUMN 、CONCATENATE、COUNTIF 、DATE 這七個常用函數(shù)，今天我們繼續(xù)介紹下面的七個常用函數(shù)： DATEDIF函數(shù)：計算返回兩個日期參數(shù)的差值。 DAY函數(shù)：計算參數(shù)中指定日期或引用單元格中的日期天數(shù)。 DCOUNT函數(shù)：返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。 FREQUENCY函數(shù)：以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。 IF函數(shù)：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)條件觸發(fā)的計算結(jié)果。 INDEX函數(shù)：返回列表或數(shù)組中的元素值，此元素由行序號和列序號的索引值執(zhí)行 確定。 INT函數(shù)：將數(shù)值向下取整為最接近的整數(shù)。 8、DATEDIF函數(shù)函數(shù)名稱：DATEDIF 主要功能：計算返回兩個日期參數(shù)的差值。 運用格式：=DATEDIF(date1,date2,"y")、=DATEDIF(date1,date2,"m")、=DATEDIF(date1,date2,"d") 參數(shù)說明：date1代表前面一個日期，date2代表后面一個日期；y（m、d）要求返回兩個日期相差的年（月、天）數(shù)。 使用舉例：在C23單元格中輸入公式：=DATEDIF(A23,TODAY(),"y")，確認后返回系統(tǒng)當前日期[用TODAY()表示）與A23單元格中日期的差值，并返回相差的年數(shù)。 特別提醒：這是Excel中的一個潛藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降?，可以直接輸入運用，對于計算年齡、工齡等非常有效。 9、DAY函數(shù)函數(shù)名稱：DAY 主要功能：求出指定日期或引用單元格中的日期的天數(shù)。 運用格式：DAY(serial_number) 參數(shù)說明：serial_number代表指定的日期或引用的單元格。 使用舉例：輸入公式：=DAY("2003-12-18")，確認后，顯示出18。 特別提醒：如果是給定的日期，請包含在英文雙引號中。 10、DCOUNT函數(shù)函數(shù)名稱：DCOUNT 主要功能：返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。 運用格式：DCOUNT(database,field,criteria) 參數(shù)說明：Database表示須要統(tǒng)計的單元格區(qū)域；Field表示函數(shù)所運用的數(shù)據(jù)列（在第一行必須要有標志項）；Criteria包含條件的單元格區(qū)域。 使用舉例：如圖1所示，在F4單元格中輸入公式：=DCOUNT(A1:D11,"語文",F1:G2)，確認后即可求出“語文”列中，成績大于等于70，而小于80的數(shù)值單元格數(shù)目（相當于分數(shù)段人數(shù)）。 特別提醒：如果將上述公式修改為：=DCOUNT(A1:D11,,F1:G2)，也可以達到相同目的。 11、FREQUENCY函數(shù)函數(shù)名稱：FREQUENCY 主要功能：以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。 運用格式：FREQUENCY(data_array,bins_array) 參數(shù)說明：Data_array表示用來計算頻率的一組數(shù)據(jù)或單元格區(qū)域；Bins_array表示為前面數(shù)組執(zhí)行 分隔一列數(shù)值。 使用舉例：如圖2所示，同時選中B32至B36單元格區(qū)域，輸入公式：=FREQUENCY(B2:B31,D2:D36)，輸入完成后按下“Ctrl+Shift+Enter”組合鍵執(zhí)行 確認，即可求出B2至B31區(qū)域中，按D2至D36區(qū)域執(zhí)行分隔的各段數(shù)值的出現(xiàn)頻率數(shù)目（相當于統(tǒng)計各分數(shù)段人數(shù)）。 特別提醒：上述輸入的是一個數(shù)組公式，輸入完成后，須要通過按“Ctrl+Shift+Enter”組合鍵執(zhí)行確認，確認后公式兩端出現(xiàn)一對大括號（{}），此大括號不能直接輸入。 12、IF函數(shù)函數(shù)名稱：IF 主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。 運用格式：=IF(Logical,Value_if_true,Value_if_false) 參數(shù)說明：Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯“真（TRUE）”時的顯示內(nèi)容，如果忽略返回“TRUE”；Value_if_false表示當判斷條件為邏輯“假（FALSE）”時的顯示內(nèi)容，如果忽略返回“FALSE”。 使用舉例：在C29單元格中輸入公式：=IF(C26>=18,"符合要求","不符合要求")，確信以后，如果C26單元格中的數(shù)值大于或等于18，則C29單元格顯示“符合要求”字樣，反之顯示“不符合要求”字樣。 特別提醒：本文中類似“在C29單元格中輸入公式”中指定的單元格，讀者在運用時，并不須要受其約束，此處只是配合本文所附的實例須要而給出的相應(yīng)單元格，具體請大家參考所附的實例文件。 13、MID函數(shù)函數(shù)名稱：MID 14、MIN函數(shù)函數(shù)名稱：MIN 主要功能：求出一組數(shù)中的最小值。 運用格式：MIN(number1,number2……) 參數(shù)說明：number1,number2……代表須要求最小值的數(shù)值或引用單元格（區(qū)域），參數(shù)不超過30個。 使用舉例：輸入公式：=MIN(E44:J44,7,8,9,10)，確認后即可顯示出E44至J44單元和區(qū)域和數(shù)值7，8，9，10中的最小值。 特別提醒：如果參數(shù)中有文本或邏輯值，則忽略。 們在運用 Excel打造表格整理數(shù)據(jù)的時候，常常要用到它的函數(shù)功能來統(tǒng)計處理表格中的數(shù)據(jù)。本文以Excel為例（其它版本請仿照操作），向大家介紹一些在Excel中運用頻率最高的函數(shù)的功能和運用要領(lǐng)。2003 Excel函數(shù)系列之后我們還會推出Excel常用函數(shù)實例文章系列，歡迎大家關(guān)注。 為方便大家閱讀，我們按函數(shù)名稱的字母執(zhí)行 排序。 前面我們介紹了以下函數(shù)的功能和用法： ABS、AND、AVERAGE、COLUMN 、CONCATENATE、COUNTIF 、DATE DATEDIF、DAY、DCOUNT、FREQUENCY、IF、INDEX、INT ISERROR、LEFT、LEN、MATCH、MAX、MID、MIN 我們繼續(xù)介紹下面的七個常用函數(shù)： MOD函數(shù)：求出兩數(shù)相除的余數(shù)。 MONTH函數(shù)：求出指定日期或引用單元格中的日期的月份。 NOW函數(shù)：給出當前系統(tǒng)日期和時間。 OR函數(shù)：僅當所有參數(shù)值均為邏輯“假（FALSE）”時返回結(jié)果邏輯“假（FALSE）”，否則都返回邏輯“真（TRUE）”。 RANK函數(shù)：返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位。 RIGHT函數(shù)：從一個文本字符串的最后一個字符開始，截取指定數(shù)目的字符。 SUBTOTAL函數(shù)：返回列表或數(shù)據(jù)庫中的分類匯總。 15、MOD函數(shù)函數(shù)名稱：MOD 主要功能：求出兩數(shù)相除的余數(shù)。 運用格式：MOD(number,divisor) 參數(shù)說明：number代表被除數(shù)；divisor代表除數(shù)。 使用舉例：輸入公式：=MOD(13,4)，確認后顯示出結(jié)果“1”。 特別提醒：如果divisor參數(shù)為零，則顯示不正確值“#DIV/0!”；MOD函數(shù)可以借用函數(shù)INT來表示：上述公式可以修改為：=13-4*INT(13/4)。 16、MONTH函數(shù)函數(shù)名稱：MONTH 主要功能：求出指定日期或引用單元格中的日期的月份。 運用格式：MONTH(serial_number) 參數(shù)說明：serial_number代表指定的日期或引用的單元格。 使用舉例：輸入公式：=MONTH("2003-12-18")，確認后，顯示出11。 特別提醒：如果是給定的日期，請包含在英文雙引號中；如果將上述公式修改為：=YEAR("2003-12-18")，則返回年份對應(yīng)的值“2003”。 17、NOW函數(shù)函數(shù)名稱：NOW 主要功能：給出當前系統(tǒng)日期和時間。 運用格式：NOW() 參數(shù)說明：該函數(shù)不須要參數(shù)。 主要功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。 運用格式：MID(text,start_num,num_chars) 參數(shù)說明：text代表一個文本字符串；start_num表示指定的起始位置；num_chars表示要截取的數(shù)目。 使用舉例：假定A47單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C47單元格中輸入公式：=MID(A47,4,3)，確認后即顯示出“天極網(wǎng)”的字符。 特別提醒：公式中各參數(shù)間，要用英文狀態(tài)下的逗號“,”隔開。 使用舉例：輸入公式：=NOW()，確認后即刻顯示出當前系統(tǒng)日期和時間。如果系統(tǒng)日期和時間發(fā)生了改動，只要按一下F9功能鍵，即可讓其隨之改動。 特別提醒：顯示出來的日期和時間格式，可以通過單元格格式執(zhí)行 重新配置。 18、OR函數(shù)函數(shù)名稱：OR 主要功能：返回邏輯值，僅當所有參數(shù)值均為邏輯“假（FALSE）”時返回函數(shù)結(jié)果邏輯“假（FALSE）”，否則都返回邏輯“真（TRUE）”。 運用格式：OR(logical1,logical2, ...) 參數(shù)說明：Logical1,Logical2,Logical3……：表示待測試的條件值或表達式，最多這30個。 使用舉例：在C62單元格輸入公式：=OR(A62>=60,B62>=60)，確認。如果C62中返回TRUE，說明A62和B62中的數(shù)值至少有一個大于或等于60，如果返回FALSE，說明A62和B62中的數(shù)值都小于60。 特別提醒：如果指定的邏輯條件參數(shù)中包含非邏輯值時，則函數(shù)返回不正確值“#VALUE!”或“#NAME”。 19、RANK函數(shù)函數(shù)名稱：RANK 主要功能：返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位。 運用格式：RANK（Number,ref,order） 參數(shù)說明：Number代表須要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域；order代表排序形式參數(shù)（如果為“0”或者忽略，則按降序排名，即數(shù)值越大，排名結(jié)果數(shù)值越小；如果為非“0”值，則按升序排名，即數(shù)值越大，排名結(jié)果數(shù)值越大；）。 使用舉例：如在C2單元格中輸入公式：=RANK(B2,$B$2:$B$31,0)，確認后即可得出丁1同學(xué)的語文成績在全班成績中的排名結(jié)果。 特別提醒：在上述公式中，我們讓Number參數(shù)采取了相對引用形式，而讓ref參數(shù)采取了絕對引用形式（添加了一個“$”符號），這樣配置后，選中C2單元格，將鼠標移至該單元格右下角，成細十字線狀時（通常稱之為“填充柄”），按住左鍵向下拖拉，即可將上述公式高速復(fù)制到C列下面的單元格中，完成其他同學(xué)語文成績的排名統(tǒng)計。 20、RIGHT函數(shù)函數(shù)名稱：RIGHT 主要功能：從一個文本字符串的最后一個字符開始，截取指定數(shù)目的字符。 運用格式：RIGHT(text,num_chars) 參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目。 使用舉例：假定A65單元格中保存了“我喜歡天極網(wǎng)”的字符串，我們在C65單元格中輸入公式：=RIGHT(A65,3)，確認后即顯示出“天極網(wǎng)”的字符。 特別提醒：Num_chars參數(shù)必須大于或等于0，如果忽略，則默認其為1；如果num_chars參數(shù)大于文本長度，則函數(shù)返回整個文本。 21、SUBTOTAL函數(shù)函數(shù)名稱：SUBTOTAL 主要功能：返回列表或數(shù)據(jù)庫中的分類匯總。 運用格式：SUBTOTAL(function_num, ref1, ref2, ...) 參數(shù)說明：Function_num為1到11（包含潛藏值）或101到111（忽略潛藏值）之間的數(shù)字，用來指定運用什么函數(shù)在列表中執(zhí)行分類匯總計算（如圖6）；ref1, ref2,……代表要執(zhí)行 分類匯總區(qū)域或引用，不超過29個。 使用舉例：如圖7所示，在B64和C64單元格中分別輸入公式：=SUBTOTAL(3,C2:C63)和=SUBTOTAL103,C2:C63)，并且將61行潛藏起來，確認后，前者顯示為62（包括潛藏的行），后者顯示為61，不包括潛藏的行。 特別提醒：如果采取自動篩選，無論function_num參數(shù)選用什么類型，SUBTOTAL函數(shù)忽略任何不包括在篩選結(jié)果中的行；SUBTOTAL函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域，不適用于數(shù)據(jù)行或水平區(qū)域。 我們在運用 Excel打造表格整理數(shù)據(jù)的時候，常常要用到它的函數(shù)功能來統(tǒng)計處理表格中的數(shù)據(jù)。本文以Excel為例（其它版本請仿照操作），向大家介紹一些在Excel中運用頻率最高的函數(shù)的功能和運用要領(lǐng)。文章末尾提供.xls文件供大家下載參考。2003 Excel函數(shù)系列之后我們還會推出Excel常用函數(shù)實例文章系列，歡迎大家關(guān)注。 為方便大家閱讀，我們按函數(shù)名稱的字母執(zhí)行 排序。 前面我們介紹了以下函數(shù)的功能和用法： ABS、AND、AVERAGE、COLUMN 、CONCATENATE、COUNTIF 、DATE DATEDIF、DAY、DCOUNT、FREQUENCY、IF、INDEX、INT ISERROR、LEFT、LEN、MATCH、MAX、MID、MIN MOD、MONTH、NOW、OR、RANK、RIGHT、SUBTOTAL 文章導(dǎo)讀 我們繼續(xù)介紹下面的七個常用函數(shù)： SUM 函數(shù)：計算所有參數(shù)數(shù)值的和。 SUMIF函數(shù)：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。 TEXT函數(shù)：根據(jù)指定的數(shù)值格式將相應(yīng)的數(shù)字轉(zhuǎn)換為文本形式。 TODAY函數(shù)：給出系統(tǒng)日期。 VALUE函數(shù)：將一個代表數(shù)值的文本型字符串轉(zhuǎn)換為數(shù)值型。 VLOOKUP函數(shù)：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值。 WEEKDAY函數(shù)：給出指定日期對應(yīng)的星期數(shù)。 22、SUM函數(shù)函數(shù)名稱：SUM 主要功能：計算所有參數(shù)數(shù)值的和。 運用格式：SUM（Number1,Number2……） 參數(shù)說明：Number1、Number2……代表須要計算的值，可以是具體的數(shù)值、引用的單元格（區(qū)域）、邏輯值等。 使用舉例：如圖7所示，在D64單元格中輸入公式：=SUM(D2:D63)，確認后即可求出語文的總分。 特別提醒：如果參數(shù)為數(shù)組或引用，只有其中的數(shù)字將被計算。數(shù)組或引用中的空白單元格、邏輯值、文本或不正確值將被忽略；如果將上述公式修改為：=SUM(LARGE(D2:D63,{1,2,3,4,5}))，則可以求出前5名成績的和。 23、SUMIF函數(shù)函數(shù)名稱：SUMIF 主要功能：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。 運用格式：SUMIF（Range,Criteria,Sum_Range） 參數(shù)說明：Range代表條件判斷的單元格區(qū)域；Criteria為指定條件表達式；Sum_Range代表須要計算的數(shù)值所在的單元格區(qū)域。 使用舉例：如圖7所示，在D64單元格中輸入公式：=SUMIF(C2:C63,"男",D2:D63)，確認后即可求出“男”生的語文成績和。 特別提醒：如果把上述公式修改為：=SUMIF(C2:C63,"女",D2:D63)，即可求出“女”生的語文成績和；其中“男”和“女”由于是文本型的，須要放在英文狀態(tài)下的雙引號（"男"、"女"）中。 24、TEXT函數(shù)函數(shù)名稱：TEXT 主要功能：根據(jù)指定的數(shù)值格式將相應(yīng)的數(shù)字轉(zhuǎn)換為文本形式。 運用格式：TEXT(value,format_text) 參數(shù)說明：value代表須要轉(zhuǎn)換的數(shù)值或引用的單元格；format_text為指定文字形式的數(shù)字格式。 使用舉例：如果B68單元格中保存有數(shù)值1280.45，我們在C68單元格中輸入公式：=TEXT(B68, "$0.00")，確認后顯示為“$1280.45”。 特別提醒：format_text參數(shù)可以根據(jù)“單元格格式”對話框“數(shù)字”標簽中的類型執(zhí)行 確定。 25、TODAY函數(shù)函數(shù)名稱：TODAY 主要功能：給出系統(tǒng)日期。 運用格式：TODAY() 參數(shù)說明：該函數(shù)不須要參數(shù)。 使用舉例：輸入公式：=TODAY()，確認后即刻顯示出系統(tǒng)日期和時間。如果系統(tǒng)日期和時間發(fā)生了改動，只要按一下F9功能鍵，即可讓其隨之改動。 特別提醒：顯示出來的日期格式，可以通過單元格格式執(zhí)行 重新配置（參見附件）。 26、VALUE函數(shù)函數(shù)名稱：VALUE 主要功能：將一個代表數(shù)值的文本型字符串轉(zhuǎn)換為數(shù)值型。 運用格式：VALUE(text) 參數(shù)說明：text代表須要轉(zhuǎn)換文本型字符串數(shù)值。 使用舉例：如果B74單元格中是通過LEFT等函數(shù)截取的文本型字符串，我們在C74單元格中輸入公式：=VALUE(B74)，確認后，即可將其轉(zhuǎn)換為數(shù)值型。 特別提醒：如果文本型數(shù)值不經(jīng)過上述轉(zhuǎn)換，在用函數(shù)處理這些數(shù)值時，常常返回不正確。 27、VLOOKUP函數(shù)函數(shù)名稱：VLOOKUP 主要功能：在數(shù)據(jù)表的首列查找指定的數(shù)值，并由此返回數(shù)據(jù)表當前行中指定列處的數(shù)值。 運用格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 參數(shù)說明：Lookup_value代表須要查找的數(shù)值；Table_array代表須要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號（當Col_index_num為2時,返回table_array第2列中的數(shù)值，為3時，返回第3列的值……）；Range_lookup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回不正確值#N/A。 使用舉例：參見圖7，我們在D65單元格中輸入公式：=VLOOKUP(B65,B2:D63,3,FALSE)，確認后，只要在B65單元格中輸入一個學(xué)生的姓名（如丁48），D65單元格中即刻顯示出該學(xué)生的語言成績。 特別提醒：Lookup_value參見必須在Table_array區(qū)域的首列中；如果忽略Range_lookup參數(shù)，則Table_array的首列必須執(zhí)行 排序；在此函數(shù)的向?qū)е校嘘P(guān)Range_lookup參數(shù)的用法是不正確的。 28、WEEKDAY函數(shù)函數(shù)名稱：WEEKDAY 主要功能：給出指定日期的對應(yīng)的星期數(shù)。 運用格式：WEEKDAY(serial_number,return_type) 參數(shù)說明：serial_number代表指定的日期或引用含有日期的單元格；return_type代表星期的表示形式 [當Sunday（星期日）為1、Saturday（星期六）為7時，該參數(shù)為1；當Monday（星期一）為1、Sunday（星期日）為7時，該參數(shù)為2（這種情況符合中國人的習(xí)慣）；當Monday（星期一）為0、Sunday（星期日）為6時，該參數(shù)為3]。 使用舉例：輸入公式：=WEEKDAY(TODAY(),2)，確認后即給出系統(tǒng)日期的星期數(shù)。 提示：如果是指定的日期，請放在英文狀態(tài)下的雙引號中，如=WEEKDAY("2003-12-18",2)。 |
|
|
