任務常用函數(shù)及其應用

任務2-1Excel財務管理基本操作1-2-2常用函數(shù)及其應用公式和函數(shù)基礎一、公式的修改方法：選中公式所在的單元格，單擊編輯欄。Delete：刪除。編輯狀態(tài)下，刪除光標后面的內容。Backspace：退后一格。編輯狀態(tài)下，刪除光標前面的內容。二、Excel函數(shù)是一類特殊的公式。函數(shù)名不區(qū)分大小寫。公式和函數(shù)基礎三、使用公式記憶式鍵入手工輸入函數(shù)。設置：【Office按鈕】【Excel選項】【公式】勾選【使用公式】區(qū)域的【公式記憶式鍵入】復選框【確定】啟用“公式記憶式鍵入”功能后：當用戶輸入公式時，系統(tǒng)會自動顯示以輸入的字符開頭的所有函數(shù)。通過在擴展下拉菜單中移動上、下方向鍵，或鼠標選擇不同的函數(shù)，其右側將顯示此函數(shù)的功能簡介。雙擊鼠標，或者按<Tab>鍵，可添加此函數(shù)。1-2-2常用函數(shù)及其應用1-2-2-1SUM函數(shù)、SUMIF函數(shù)和SUMPRODUCT函數(shù)（數(shù)學與三角函數(shù)）（1）無條件求和SUM函數(shù)：求30個以內參數(shù)的和。公式為：=SUM（參數(shù)1，參數(shù)2，…，參數(shù)N）當對某一行或某一列的連續(xù)數(shù)據(jù)進行求和時，可以使用工具欄中的自動求和按鈕例：求全年的銷售量。數(shù)據(jù)文件：自動求和練習.xls4（2）條件求和SUMIF函數(shù)：根據(jù)指定條件對若干單元格求和。公式為：=SUMIF(range,criteria,sum_range)式中：range（范圍）——用于做條件判斷的區(qū)域；Criteria（標準，條件）——條件，可以為數(shù)字、文本或表達式；Sum_range——需要求和的數(shù)值所在的區(qū)域.5解釋：如果range里的數(shù)值滿足criteria，則對Sum_range里的數(shù)值求和。例1-4某商場2月份銷售的家電流水記錄如下圖，請按商品類別對銷售額匯總。6數(shù)據(jù)文件：P22頁例1-4.xlsF4：在絕對引用和相對引用之間切換。例

匯總指定商品的月銷量.xls下圖展示了一份某集團生成的所有飲品月度銷量情況表，請用sumif函數(shù)計算指定商品的月銷售量。請用sumif函數(shù)統(tǒng)計銷量>1000箱的飲品總銷量。7知識點：數(shù)據(jù)的篩選、數(shù)據(jù)有效性、sumif函數(shù)。公式：Sumif(B2:B10,F2,C2:C10)Sumif(C2:C10,”>1000”,C2:C10)例

統(tǒng)計第二季度方便面的銷售情況.xls下圖展示了一份某超市賣場第二季度的方便面銷量情況表。請用sumif函數(shù)統(tǒng)計“康師傅”方便面的總銷量。請用sumif函數(shù)統(tǒng)計B組員工的總銷量。9SUMIF函數(shù)按照條件判斷區(qū)域，進行相對位置的偏移，來對求和區(qū)域進行統(tǒng)計求和。Excel支持兩個通配符：星號“*”和問號“?”（1）*：可代替任意數(shù)目的字符，可以是單個字符、多個字符或者沒有字符。（2）?：可代替任意單個字符。公式：Sumif(C3:I11,”康師傅”,D3:J11)Sumif(B3:H11,”B*”,D3:J11) 或 Sumif(B3:H11,”B???”,D3:J11)（3）多組數(shù)據(jù)相乘求和sumproduct函數(shù)Sum：summary的縮寫，求和。Product：乘積。公式為：=sumproduct(array1,array2,array3,…)式中，array1,array2,array3,…——1至30個數(shù)組。功能：在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘，并返回乘積之和。注意：數(shù)組參數(shù)必須具有相同的維數(shù)；否則，函數(shù)sumproduct將返回錯誤值”#VALUE!”.非數(shù)值型的數(shù)組元素將作為0處理。11（3）多組數(shù)據(jù)相乘求和sumproduct函數(shù)例：求三個數(shù)組對應元素的乘積之和。數(shù)據(jù)文件：sumprocut函數(shù)示例.xls12（一）計數(shù)函數(shù)COUNT功能：計算給定區(qū)域內數(shù)值型參數(shù)的數(shù)目.公式為：=COUNT(參數(shù)1,參數(shù)2,…,參數(shù)N)例：下圖展示了一份某班學生的月考成績表，請統(tǒng)計各個科目的參考人數(shù)。131-2-2-4Count函數(shù)和Countif函數(shù)（統(tǒng)計函數(shù)）數(shù)據(jù)文件：count函數(shù)示例.xls思考：COUNT(A1:E16)=?（二）條件計數(shù)函數(shù)COUNTIF功能：計算給定區(qū)域內滿足特定條件的單元格（可以是非數(shù)值型）的數(shù)目.公式為：=COUNTIF(range,criteria)式中：range（范圍）——需要計數(shù)的單元格區(qū)域；criteria（標準，條件）——條件，可以為數(shù)字、表達式或文本。14解釋：如果range里的數(shù)據(jù)滿足criteria，則對range里的數(shù)據(jù)計數(shù)。即：用做判斷的區(qū)域和需要計數(shù)的區(qū)域是同一區(qū)域?；貞洠簵l件求和函數(shù)SUMIF。

公式為：=SUMIF(range,criteria,sum_range)例：下圖展示了一份某公司員工工資表，請用countif函數(shù)統(tǒng)計各個部門人數(shù)。15數(shù)據(jù)文件：countif函數(shù)示例.xls1-2-2-5IF函數(shù)（邏輯函數(shù)）IF函數(shù)，也稱條件函數(shù)。用途：根據(jù)參數(shù)條件的真假，返回不同的結果。公式為：=IF(logical_test,value_if_true,value_if_false)式中，logical_test：條件表達式，其結果要么為真，要么為假；Value_if_true：當logical_test為真時，返回的值。Value_if_false：當logical_test為假時，返回的值。16分析：如果logical_test是真的，則單元格=Value_if_true；如果logical_test是假的，則單元格=Value_if_false。1-2-2-5IF函數(shù)例：下表列出了某企業(yè)各個銷售部門2011年的銷售業(yè)績，請按評價標準對各個部門的業(yè)績進行評價。數(shù)據(jù)文件：例1-5-1--練習.xls17步驟：在單元格C3中輸入公式=IF(B3<300000,“不合格”,”合格”)或者=IF(B3>=300000,“合格”,”不合格”)1-2-2-5IF函數(shù)例：下表列出了某企業(yè)各個銷售部門2011年的銷售業(yè)績，請按評價標準對各個部門的業(yè)績進行評價。數(shù)據(jù)文件：例1-5-2--練習.xls18分析19300000400000不合格合格優(yōu)秀判斷過程：IF銷售額<300000

則：返回“不合格”

否則：IF銷售額<400000

則：返回”合格”

否則：返回”優(yōu)秀”公式：=IF(B3<300000,“不合格”,IF(B3<400000,“合格”,“優(yōu)秀”))函數(shù)的嵌套銷售額1-2-2-5IF函數(shù)例：下表列出了某企業(yè)各個銷售部門2011年的銷售業(yè)績，請按評價標準對各個部門的業(yè)績進行評價。數(shù)據(jù)文件：例1-5-3--練習.xls20分析21300000400000合格好很好判斷過程：IF銷售額<100000

則：返回“差”

否則：IF銷售額<300000

則：返回”合格”

否則：IF銷售額<400000

則：返回”好”

否則：返回”很好”公式：=IF(B3<100000,“差”,IF(B3<300000,“合格”,

IF(B3<400000,“好”,“很好”)

)

)100000差銷售額公式：=IF(B3<100000,“差”,IF(B3<300000,“合格”,

IF(B3<400000,“好”,“很好”)

)

)22分析23300000400000合格好很好判斷過程：IF銷售額<300000

則：IF銷售額<100000

則：返回”差”

否則：返回”合格”

否則：IF銷售額<400000

則：返回”好”

否則：返回”很好”公式：=IF(B3<300000,IF(B3<100000,“差”,“合格”)

IF(B3<400000,“好”,“很好”)

)100000差銷售額第二種方法：第一次判斷第二次判斷1-2-2-5IF函數(shù)例：下表列出了某企業(yè)各個銷售部門2011年的銷售業(yè)績，請按評價標準對各個部門的業(yè)績進行評價。數(shù)據(jù)文件：例1-5--練習.xls24更正教材P24頁圖1-31錯誤300000400000一般好很好判斷過程：IF銷售額<100000

則：返回“差”

否則：IF銷售額<200000

則：返回”一般”

否則：IF銷售額<300000

則：返回”較好”

否則：IF銷售額<400000

則：返回”好”

否則：返回”很好”公式：=IF(B3<100000,“差”,IF(B3<200000,“一般”,

IF(B3<300000,“較好”,

IF(B3<400000,“好”,”很好”))

)

)100000差200000較好25銷售額第一次判斷第二次判斷第三次判斷第四次判斷思考：還有別的方法么？1-2-2-6AND函數(shù)、OR函數(shù)、NOT函數(shù)（邏輯函數(shù)，常和IF函數(shù)結合使用）邏輯值只有兩個：TRUE、FALSE.26AND函數(shù)公式：=AND(條件1,條件2,…,條件N)AND函數(shù)表示邏輯“與”。當N個條件都滿足時，AND()=TRUE只要有一個條件不滿足，AND()=FALSE舉例說明：AND(5>3,4>6)=FALSE，AND(5>3,4<>6)=TRUE例1-6某企業(yè)根據(jù)各銷售部門的銷售額及銷售費用確定獎金提取比例及提取額。若銷售額>300000元且銷售費用占銷售額的比例不超過1%，則獎金提取比例為15%，否則為10%。27數(shù)據(jù)文件：例1-6.xls分析：先在D3計算部門A的獎金提取比例IFB3>300000與C3/B3<1%

都滿足

則：返回15%

否則：返回10%28公式：=IF(AND(B3>300000,C3/B3<1%),15%,10%)1-2-2-6AND函數(shù)、OR函數(shù)、NOT函數(shù)29OR函數(shù)公式：=OR(條件1,條件2,…,條件N)OR函數(shù)表示邏輯“或”。只要有一個條件滿足，OR()=TRUE當所有條件都不滿足時，OR()=FALSE舉例說明：OR(5>3,4>6)=TRUE，OR(5<3,4=6)=FALSE1-2-2-6AND函數(shù)、OR函數(shù)、NOT函數(shù)30NOT函數(shù)公式：=NOT(條件)當條件為真時，NOT(條件)=NOT(TRUE)=FALSE當條件為假時，NOT(條件)=NOT(FALSE)=TRUE舉例說明：NOT(4>6)=TRUE，NOT(4<>6)=FALSE練習如下圖所示，表中B列為職工年齡，C列為職稱。公司提拔干部，需要篩選出年齡在35周歲以下，職稱為工程師的人選。職工中，擁有“工程師”或“高工”職稱的一共有多少人？31數(shù)據(jù)文件：邏輯關系運算.xls32問題1分析：對第一位職工，判斷他是否滿足條件“年齡在35周歲以下，職稱為工程師”。判斷過程IFB3<35且C3=“工程師”

則：在D3返回”滿足”

否則：在D3返回””公式：在D3單元格輸入=IF(AND(B3<35,C3=“工程師”),“滿足”,“”)空文本33問題2分析：對第一位職工，判斷他是否滿足條件“擁有“工程師”或“高工”職稱”。判斷過程IFC3=“工程師”或C3=“高工”

則：在E3返回”滿足”

否則：在E3返回””公式：在E3單元格輸入=IF(OR(C3=“工程師”,

C3=“高工”),“滿足”,“”)空文本34問題2的另一種解法：“職稱”有3個取值：工程師、高工、助工判斷過程IFC3<>“助工”

則：在E3返回”滿足”

否則：在E3返回””公式：在E3單元格輸入=IF(NOT(C3=“助工”),“滿足”,“”)或者：=IF(C3<>“助工”,“滿足”,“”)空文本1-2-2-7Match函數(shù)常用的定位函數(shù)Match：匹配是Excel常用的查找定位函數(shù)之一，主要用于確定查找值在查找范圍中的位置序號。公式：=MATCH(lookup_value,lookup_array,match_type)式中：Lookup_value：需要在lookup_array中查找的值。Lookup_array：要搜索的單元格區(qū)域。即：在Lookup_array中找Lookup_value這個值。Match_type：如何查找?？扇∪齻€值：-1,0,1.當match_type=-1時：Lookup_array必須按降序排列，MATCH函數(shù)從大往小查，查找到≥Lookup_value的最小數(shù)值。例：在降序排列的數(shù)據(jù)列中，查找20的相對位置。數(shù)據(jù)文件：match函數(shù).xls35查什么在哪里查以什么方式查公式：=MATCH(lookup_value,lookup_array,match_type)式中：Lookup_value：需要在lookup_array中查找的值。Lookup_array：要搜索的單元格區(qū)域。Match_type：如何查找?？扇∪齻€值：-1,0,1.當match_type=1時：Lookup_array必須按升序排列，MATCH函數(shù)從小往大查，查找到≤Lookup_value的最大數(shù)值。例：在升序排列的數(shù)據(jù)列中，查找20的相對位置。數(shù)據(jù)文件：match函數(shù).xls36當match_type=0時：Lookup_array可以按任何順序排列，MATCH函數(shù)查找到=Lookup_value的第一個數(shù)值。例：在原始數(shù)據(jù)列中，分別查找20和23的相對位置。數(shù)據(jù)文件：match函數(shù).xls沒找到：返回”#N/A”，notapplicable.Applicable，合適的。37P26頁課本上的示例MATCH(12,{23,43,12,55},0)=3MATCH(40,{23,43,12,55},1)=1注意：MATCH

函數(shù)會返回lookup_value在lookup_array

中匹配值的位置而不是匹配值本身。例如，MATCH("b",{"a","b","c"},0)

會返回2，即“b”在數(shù)組{"a","b","c"}中的相對位置。查找文本值時，MATCH函數(shù)不區(qū)分大小寫字母。如果MATCH函數(shù)查找匹配項不成功，它會返回錯誤值#N/A。38MATCH（）一邊查找lookup_value，一邊檢查Lookup_array是否升序排列。1-2-2-8INDEX函數(shù)公式：=INDEX

(array,row_num,column_num)式中：INDEX—索引；array—單元格區(qū)域或數(shù)組常數(shù)；Row_num—行序號；Column_num—列序號.用途：根據(jù)Row_num（行號）和Column_num（列號），返回array（數(shù)組）中的數(shù)值。常與MATCH函數(shù)結合使用。例：根據(jù)指定的行號和列號，找到數(shù)組（黃色凸顯）中對應的元素值。數(shù)據(jù)文件：INDEX初步.xls39提供索引MATCH函數(shù)：定位，返回數(shù)據(jù)的位置。INDEX函數(shù)：根據(jù)位置返回實際的數(shù)值。實際工作中，常常將兩者組合使用。例：下圖是某組員工的工作量明細表。要求，根據(jù)B2中的姓名和B3中的項目，從明細表中查詢對應的工作量。數(shù)據(jù)文件：多條件組合查詢員工工作量.xls40如何用公式實現(xiàn)這一過程？確定行號、列號行號：MATCH(B2,D2:D7,0) 此時=3列號：MATCH(B3,E1:H1,0) 此時=3根據(jù)行號和列號在數(shù)組中查詢數(shù)值。INDEX(E2:H7,MATCH(B2,D2:D7,0),MATCH(B3,E1:H1,0))41人工如何查？根據(jù)“查找姓名”確定在哪一行，根據(jù)“項目號”確定在哪一列。行列交叉處的數(shù)值就是員工工作量。例：下表是某公司的一份設備分配信息表。請根據(jù)設備ID查詢設備使用人姓名。數(shù)據(jù)文件：根據(jù)設備ID查詢設備使用人.xls42查詢步驟：根據(jù)設備ID在F2：F9區(qū)域查詢行號。MATCH(B2,F2:F9,0)根據(jù)行號在E2：E9區(qū)域查詢設備使用人。公式：=INDEX(E2:E9,MATCH(B2,F2:F9,0)

)如果數(shù)組只包含一列，則相對應的參數(shù)column_num為可選參數(shù)。教材上示例：INDEX({1,2;3,4},0,2)={2;4}數(shù)據(jù)文件：用INDEX返回整行或整列.xls解釋：如果將row_num設置為0（行號不作限制），函數(shù)INDEX則返回整個列的數(shù)組數(shù)值。要返回一個數(shù)組，INDEX函數(shù)必須以數(shù)組公式形式輸入：43輸入步驟：

返回一個結果選定一個單元格數(shù)組公式

返回多個結果選定單元格區(qū)域輸入數(shù)組公式。同時按【Ctrl+Shift+Enter】組合鍵。這時，Excel自動在公式的兩邊加上大括號{}。行號不做限制思考：如何用INDEX函數(shù)在{1,2;3,4}數(shù)組中返回第2行？數(shù)據(jù)文件：用INDEX返回整行或整列.xls44回憶：公式：=INDEX

(array,row_num,column_num)如果數(shù)組只包含一列，則相對應的參數(shù)column_num為可選參數(shù)。如果數(shù)組只包含一行，則相對應的參數(shù)

row_num為可選參數(shù)。如果將

column_num設置為0（列號不作限制），函數(shù)INDEX則返回整個行的數(shù)組數(shù)值。如果將row_num設置為0（行號不作限制），函數(shù)INDEX則返回整個列的數(shù)組數(shù)值。要返回一個數(shù)組，INDEX函數(shù)必須以數(shù)組公式形式輸入。1-2-2-9ADDRESS函數(shù)（查找與引用函數(shù)）ADDRESS函數(shù)的功能：按照給定的行號和列標，返回文本類型的單元格地址。45公式為：=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中：row_num：行號；Column_num：列標；Abs_num：abs，absolute，絕對的。何種引用類型？abs_num返回的引用類型1或省略絕對引用2行絕對列相對引用3行相對列絕對引用4相對引用1-2-2-9ADDRESS函數(shù)46公式為：=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中：a1：是否A1引用樣式。邏輯值。a1返回的引用樣式TRUE或省略A1樣式FALSER1C1樣式補充：A1引用樣式和R1C1引用樣式（1）A1引用樣式（默認）：使用字母A～XFD表示列標，用數(shù)字1～1048576表示行號，單元格地址由列標與行號組合而成。根據(jù)表示方式的不同，單元格引用可分為A1引用樣式和R1C1引用樣式。單元格引用A1引用樣式（默認）R1C1引用樣式（2）R1C1引用樣式如何啟用R1C1引用樣式：【office按鈕】→【Excel選項】→【公式】選項卡→在【使用公式】區(qū)域中勾選【R1C1】引用樣式。練習R1C1引用樣式：R，ROW（行）。C，Column（列）。行號和列標都用數(shù)字表示。例：R2C3 表示：第2行第3列交叉處位置。

也就是A1引用樣式中的C2.回到A1引用樣式。1-2-2-9ADDRESS函數(shù)例：ADDRESS(2,3,2,FALSE)=”R2C[3]”解釋：行絕對列相對，R1C1引用樣式。49Sheet_text：外部工作表的名稱。文本值?？墒÷?。教材上的例子：P27ADDRESS(2,3)=“$C$2” 絕對引用，A1樣式。ADDRESS(2,3,2)=“C$2” 行絕對列相對，A1樣式。公式為：=ADDRESS(row_num,column_num,abs_num,a1,sheet_text)式中：[]:R1C1引用樣式的相對引用符。例：ADDRESS(2,3,,,”sheet2”)=”sheet2!$C$2”解釋：絕對引用，A1引用樣式50練習：如下圖所示，在B2:B5依次鍵入以下4個公式：=ADDRESS(4,5)；=ADDRESS(4,5,3)；

=ADDRESS(4,5,3,FALSE)；=ADDRESS(4,5,3,FALSE,”sheet3”)。觀察返回的結果。數(shù)據(jù)文件：ADDRESS函數(shù)練習1-2-2-9INDIRECT函數(shù)（查找與引用函數(shù)）Indirect，間接的。51公式為：=INDIRECT(ref_text,a1)式中：ref_text：ref，reference，引用（vi.或vt.）。text，文本?？梢允菃卧褚没蛎Q。a1：邏輯值。Ref_texta1A1樣式TRUE或省略R1C1樣式FALSEINDIRECT函數(shù)的功能：第1個參數(shù)將給定一個字符串，這個字符串可以是直接給出的，也可以是由單元格引用返回的。函數(shù)根據(jù)該字符串表示的引用返回對應的數(shù)據(jù)。練習數(shù)據(jù)文件：indirect函數(shù)示例1.xls52要求：在E1～E4單元格依次輸入公式“=indirect(“A1”,TURE)”、“=indirect(B1,TURE)”、“=indirect(B2,TURE)”、“=indirect(B3,TURE)”，觀察計算結果。indirect函數(shù)的運算邏輯：書上的例子：P28頁數(shù)據(jù)文件：INDIRECT函數(shù)示例2.xls如果單元格A1包含文本”B2”，且單元格B2包含數(shù)值”1.333”，則INDIRECT($A$1)=INDIRECT(“B2”)=1.33353練習：分別在E3、E5、E7單元格輸入公式“=indirect(”$A$1”)”、“=indirect(B2)”、“=indirect(“B2”)”，觀察計算結果。想一想，為什么是這個結果。例：利用indirect函數(shù)動態(tài)統(tǒng)計工作量.xls下圖中有一份公司某項目員工研發(fā)功能點的工作量明細表，請利用indirect函數(shù)實現(xiàn)根據(jù)功能或員工進行工作量匯總。步驟一：根據(jù)所選內容批量創(chuàng)建名稱選擇A2:E8單元格區(qū)域?！竟健俊靖鶕?jù)所選內容創(chuàng)建】，彈出【以選定區(qū)域創(chuàng)建名稱】對話框。勾選【首行】和【最左列】復選框，取消其他復選框勾選，單擊【確定】。步驟一：根據(jù)所選內容批量創(chuàng)建名稱4.查看名稱?！竟健俊久Q管理器】。共11個名稱。以首行命名的4個名稱：功能一、功能二、功能三、功能四。以最左列命名的6個名稱：羅明、劉靜、張李科、陳靖、和平、趙世杰。以左上角單元格命名的1個名稱：員工工作量，引用B3：E8的數(shù)據(jù)區(qū)域。步驟二：根據(jù)功能進行工作量匯總查詢功能：數(shù)據(jù)有效性該功能工作量明細：=indirect(G3) 數(shù)組公式該功能工作量匯總：=sum(indirect(G3))步驟三：根據(jù)員工進行工作量匯總（略）56公式為：=INDIRECT(ref_text,a1)INDIRECT函數(shù)的功能：