應(yīng)用技巧實(shí)例2

1、Excel 2007 應(yīng) 用 技 巧 實(shí) 例（二）函數(shù)的應(yīng)用2011-06-18 CH LIGHTING ZHANG YU1目錄常用基本函數(shù)：1、SUM （求和）， SUMIF（條件求和），SUMIFS(多條件求和）2、PRODUCT （乘積）3、AVERAGE（平均值）4、ROUND（取整，按指定小數(shù)位）5、INT（四舍五入取整）6、MOD（相除后的余數(shù)）邏輯函數(shù)：7、IF，AND ,OR8、MAX， MIN，9、RANK （排位）時(shí)期和時(shí)間函數(shù)：10、YEAR（），MONTH（），TODAY（）, datedif(),查找與引用函數(shù)：11、COUNT（含數(shù)字的單元格個(gè)數(shù)），COUNTA（文

2、本單元格個(gè)數(shù)），COUNTBLANK（空白單元格個(gè)數(shù)），COUNTIF，12、LOOKUP， HLOOKUP，VLOOKUP13、MATCH， MID，OFFSET，COLUMN,2常用基本函數(shù)：1，SUM， 求和2，SUMIF，根據(jù)條件求和，SUMIF(range,criteria,sum_range) 1）range 為用于條件判斷的單元格區(qū)域。 2）criteria 為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達(dá)式或文本。3）sum_range 是需要求和的實(shí)際單元格。實(shí)例：銷售業(yè)績(jī)表3常用基本函數(shù)：SUMIFS， 根據(jù)多個(gè)條件求和SUMIFS對(duì)某一區(qū)域內(nèi)滿足多重條件的單元格

3、求和。 SUMIFS 和 SUMIF 的參數(shù)順序不同。具體而言，sum_range 參數(shù)在 SUMIFS 中是第一個(gè)參數(shù)，而在 SUMIF 中則是第三個(gè)參數(shù)。語(yǔ)法：SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Sum_range 是要求和的一個(gè)或多個(gè)單元格，其中包括數(shù)字或包含數(shù)字的名稱、數(shù)組或引用?？罩岛臀谋局禃?huì)被忽略。Criteria_range1, criteria_range2, 是計(jì)算關(guān)聯(lián)條件的 1 至 127 個(gè)區(qū)域。Criteria1, criteria2, 是數(shù)字、表達(dá)式、單元格引用或文本

4、形式的 1 至 127 個(gè)條件，用于定義要對(duì)哪些單元格求和實(shí)例：求一班的男生的語(yǔ)文總分?jǐn)?shù)4常用基本函數(shù)：2、PRODUCT （乘積）3、AVERAGE（平均值）4、ROUND（取整，按指定小數(shù)位）實(shí)例：學(xué)生成績(jī)表2求平均分并保留一位小數(shù)5常用基本函數(shù)：5、INT（四舍五入）6、MOD（相除后的余數(shù)）實(shí)例：?jiǎn)T工工資明細(xì)表計(jì)算發(fā)工資時(shí)所需的人民幣張數(shù)，6常用基本函數(shù)：語(yǔ)法RANK(number, ref, order) Number 必須有一個(gè)數(shù)字值 ； ref 必須是一個(gè)數(shù)組或單元格區(qū)域包含數(shù)字?jǐn)?shù)據(jù)值 ； 是可選的 order。如果省略 order，或者將它分配一個(gè)值為 0 （零） number

5、 排位是數(shù)字的數(shù)字的在 ref 中 number 位置，如果 ref 數(shù)字的數(shù)字的按降序順序排序。如果 order 分配任何非零值，假定 ref 為排名按升序排序。實(shí)例：銷售業(yè)績(jī)表3使用RANK函數(shù)對(duì)銷售業(yè)績(jī)排名7邏輯函數(shù)：MAX（） ， MIN（）返回指定區(qū)域中的最大值、最小值實(shí)例：項(xiàng)目計(jì)劃表8邏輯函數(shù)：IF 函數(shù)IF(logical_test,value_if_true,value_if_false) Logical_test 表示計(jì)算結(jié)果為 TRUE 或 FALSE 的任意值或表達(dá)式。Value_if_true logical_test 為 TRUE 時(shí)返回的值。Value_if_fal

6、se logical_test 為 FALSE 時(shí)返回的值。實(shí)例1：圖書(shū)借閱表實(shí)例2：?jiǎn)T工工齡及年假（自動(dòng)計(jì)算員工工齡及休假天數(shù)）實(shí)例3：商品銷售信息2（if 與sum 組合使用）9邏輯函數(shù)：AND ，OR 函數(shù)AND(logical1,logical2, .) Logical1, logical2, . 表示待檢測(cè)的 1 到 30 個(gè)條件值，各條件值可為 TRUE 或 FALSE。所有參數(shù)的邏輯值為真時(shí)，返回 TRUE；只要一個(gè)參數(shù)的邏輯值為假，即返回 FLASE。實(shí)例：學(xué)生成績(jī)表5使用IF函數(shù)和AND函數(shù)評(píng)定三好學(xué)生10時(shí)期和時(shí)間函數(shù)：YEAR（），MONTH（），TODAY（）實(shí)例：商品

7、銷售報(bào)表在銷售報(bào)表中自動(dòng)填寫當(dāng)前年份和月份11時(shí)期和時(shí)間函數(shù)：datedif函數(shù)DATEDIF(start_date,end_date,unit) Excel隱藏函數(shù)，在幫助和插入公式里面沒(méi)有。DATEDIF(start_date,end_date,unit) Start_date 為一個(gè)日期，它代表時(shí)間段內(nèi)的第一個(gè)日期或起始日期。 End_date 為一個(gè)日期，它代表時(shí)間段內(nèi)的最后一個(gè)日期或結(jié)束日期。 Unit 為所需信息的返回類型。 Y 時(shí)間段中的整年數(shù)。 M 時(shí)間段中的整月數(shù)。 D 時(shí)間段中的天數(shù)。 MD start_date 與 end_date 日期中天數(shù)的差。忽略日期中的月和年。

8、YM start_date 與 end_date 日期中月數(shù)的差。忽略日期中的日和年。 YD start_date 與 end_date 日期中天數(shù)的差。忽略日期中的年。12時(shí)期和時(shí)間函數(shù)：實(shí)例1： 題目： 計(jì)算出生日期為1956-4-1人的年齡 公式： =DATEDIF(1956-4-1,TODAY(),Y) NETWORKDAYS（計(jì)算兩個(gè)日期之間的工作日）NETWORKDAYS(start_date,end_date,holidays) HOUR()， MINUTE()13查找與引用函數(shù)：COUNT(value1,value2, .) 查找區(qū)域中數(shù)字個(gè)數(shù)COUNTA（alue1,valu

9、e2, .）查找區(qū)域中文本個(gè)數(shù)COUNTBLANK（range）查找區(qū)域中空值個(gè)數(shù)COUNTIF(range,criteria)查找區(qū)域中符合條件的值的個(gè)數(shù)使用COUNT函數(shù)統(tǒng)計(jì)數(shù)字個(gè)數(shù) 例：加班統(tǒng)計(jì)表使用COUNTIF函數(shù)按性別統(tǒng)計(jì)員工人數(shù)例：?jiǎn)T工基本信息表14查找與引用函數(shù)：LOOKUP函數(shù)LOOKUP(lookup_value,lookup_vector,result_vector)lookup_value函數(shù)LOOKUP在第一個(gè)向量中所要查找的數(shù)值，它可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用； lookup_vector只包含一行或一列的區(qū)域lookup_vector 的數(shù)值可以

10、為文本、數(shù)字或邏輯值； LOOKUP_vector 的數(shù)值必須按升序排序。result_vector只包含一行或一列的區(qū)域其大小必須與lookup_vector 相同。實(shí)例：銷售業(yè)績(jī)表6使用LOOKUP函數(shù)查詢各地區(qū)銷售情況15查找與引用函數(shù)：HLOOKUP函數(shù)HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)實(shí)例：業(yè)績(jī)獎(jiǎng)金核算表16查找與引用函數(shù)：VLOOKUP函數(shù)VLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 17查找與引用函數(shù)：18查找與引用函數(shù)

11、：實(shí)例2：?jiǎn)T工工資表使用VLOOKUP函數(shù)批量生成工資條COLUMN(reference)公式 說(shuō)明 （結(jié)果） =COLUMN() 公式所在的列 (1) =COLUMN(A10) 引用的列 (1)19查找與引用函數(shù)：Index函數(shù)（使用INDEX函數(shù)引用指定區(qū)域的內(nèi)容）語(yǔ)法：INDEX(array，row_num，column_num)參數(shù)：Array為單元格區(qū)域或數(shù)組常數(shù)；Row_num為數(shù)組中某行的行序號(hào)，函數(shù)從該行返回?cái)?shù)值。如果省略row_num，則必須有column_num；Column_num是數(shù)組中某列的列序號(hào)，函數(shù)從該列返回?cái)?shù)值。如果省略column_num，則必須有row_num。實(shí)例：日