常用函數(shù)補(bǔ)充課件

常用函數(shù)補(bǔ)充1公式為：=SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)式中：sum_range——需要求和的數(shù)值所在的區(qū)域；criteria_range1——用于做條件判斷的區(qū)域1；criteria1——條件1，可以為數(shù)字、文本或表達(dá)式；criteria_range2——用于做條件判斷的區(qū)域2；criteria2——條件2，可以為數(shù)字、文本或表達(dá)式；1.多條件求和函數(shù)SUMIFS()功能：如果criteria_range1里的數(shù)值滿足criteria1，criteria_range2里的數(shù)值滿足criteria2，則對Sum_range里的數(shù)值求和。2例：多條件統(tǒng)計手機(jī)銷量.xls下圖模擬了一份某通訊專賣場二季度手機(jī)的銷售明細(xì)表。請統(tǒng)計出工號“A001”銷售“iphone手機(jī)”的總銷量。3注意：系統(tǒng)將A001視為單元格引用。公式：SUMIFS(D3:J11,B3:H11,“A001”,C3:I11,“iphone手機(jī)”)4例：多條件統(tǒng)計員工工資表數(shù)據(jù).xls下圖是某公司員工工資明細(xì)表。請利用公式統(tǒng)計出各個部門按指定“姓氏”的員工工資匯總。5注意：系統(tǒng)將N3*視為一串文本。公式：在O3單元格輸入=SUMIFS($K$3:$K$17,$D$3:$D$17,M3,$C$3:$C$17,N3&"*“)62.VLOOKUP函數(shù)（查找與引用函數(shù)）7函數(shù)的語法為：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)V：vertical，垂直的，垂直線，垂直。lookup_value：查找值。支持使用通配符（“？”號和“*”號）進(jìn)行查詢。table_array：查找區(qū)域。查找值必須位于查找區(qū)域的首列。Col_index_num：列號。需要返回的數(shù)據(jù)在查找區(qū)域（table_array）中的第幾列。8函數(shù)的語法為：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)range_lookup：決定查找方式。=0（或false）

精確匹配。支持無序查找。=1（或true）

模糊匹配。要求第2個參數(shù)（table_array）的首列按升序排列。函數(shù)功能：在查找范圍(table_array)的首列縱向搜索某值(lookup_value)（確定返回值所在的行），然后根據(jù)指定的列號(col_index_num)返回對應(yīng)的值。9例：使用VLOOKUP函數(shù)進(jìn)行員工信息查詢.xls下圖展示了一份員工信息表。請針對不同的查詢條件，從信息表中進(jìn)行相應(yīng)的查詢。10B3單元格公式：=VLOOKUP(B2,D1:G9,2,0)或：=VLOOKUP(B2,D1:E9,2,0)注意查找區(qū)域的選取：查找值必須處于查找區(qū)域的首列。查找區(qū)域必須包含返回值所在的列。B6單元格公式：==VLOOKUP(B5,E1:F9,2,0)或：=VLOOKUP(B5,E1:G9,2,0)11B9單元格返回#N/A？注意：如果查找值與查找區(qū)域關(guān)鍵字的格式不一致（如文本型數(shù)字和數(shù)值），將導(dǎo)致查詢失敗。分析：B8單元格為文本型數(shù)字文本型數(shù)字：內(nèi)容是數(shù)值，但作為文本類型作為存儲。判別文本型數(shù)字：單元格左上角出現(xiàn)“錯誤檢查”標(biāo)識符（綠色三角形）?！板e誤檢查”標(biāo)識符：該單元格可能存在某些錯誤或需要注意的特點。如何將文本型數(shù)字轉(zhuǎn)換為數(shù)值？文本型數(shù)字可以作為數(shù)值直接參與四則運算。方法：+0 -0 *1 /1B9單元格公式：=VLOOKUP(B8+0,D1:E9,2,0)12函數(shù)的語法為：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)H：horizontal，水平的，水平線，水平。lookup_value：查找值。支持使用通配符（“？”號和“*”號）進(jìn)行查詢。table_array：查找區(qū)域。查找值必須位于查找區(qū)域的首行。row_index_num：行號。需要返回的數(shù)據(jù)在查找區(qū)域（table_array）中的第幾行。3.HLOOKUP函數(shù)（查找與引用函數(shù)）13函數(shù)的語法為：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)range_lookup：決定查找方式。=0（或false）

精確匹配。支持無序查找。=1（或true）

模糊匹配。要求第2個參數(shù)（table_array）的首行按升序排列。函數(shù)功能：在查找范圍(table_array)的首行橫向搜索某值(lookup_value)（確定返回值所在的列），然后根據(jù)指定的行號(row_index_num)返回對應(yīng)的值。14例：動態(tài)查詢員工年休假信息.xls下圖展示了某公司人力資源部門的員工信息表。請查詢指定員工的年假情況。15補(bǔ)充兩個日期與時間函數(shù)：TODAY()、NOW()TODAY()——返回當(dāng)前日期NOW()——返回當(dāng)期日期和時間說明：該函數(shù)不需要參數(shù)每當(dāng)用戶編輯任意單元格或重新打開包含該函數(shù)的工作簿時，公式都會重新計算。16步驟一：計算工齡G5單元格公式：=YEARFRAC(E5,TODAY())步驟二：計算年假F5單元格公式：=HLOOKUP(G5,$C$1:$F$2,2,1)當(dāng)Hlookup函數(shù)的最后一個參數(shù)為1或true時，表示實現(xiàn)模糊匹配。要求第2個參數(shù)（table_array，查找區(qū)域）的首行按升序排列。VLOOKUP函數(shù)和HLOOKUP函數(shù)對比17Vlookupvertical，垂直的，縱向的HlookupHorizontal，水平的，橫向的區(qū)別縱向查詢橫向查詢18例：在項目評測表中動態(tài)行次和列次查找.xls下圖所示是一份某企業(yè)員工項目評測表，請利用常規(guī)的查找函數(shù)綜合應(yīng)用來實現(xiàn)員工項目評測結(jié)果的動態(tài)查詢。19C13單元格公式：=VLOOKUP(A13,$A$1:$D$9,MATCH($B13,$A$1:$D$1,0),0)C14單元格公式：=HLOOKUP(B14,$A$1:$D$9,MATCH($A14,$A$1:$A$9,0),0)4.OFFSET函數(shù)（查找與引用函數(shù)）20OFFSET函數(shù)是Excel引用類函數(shù)中非常實用的函數(shù)之一，使用頻率非常高。函數(shù)的語法：OFFSET(reference,rows,cols,[height],[width])offset，偏移，平移。reference，引用。函數(shù)引用基點。rows，行偏移。正數(shù)表示向下偏移，負(fù)數(shù)表示向上偏移。cols，列偏移。正數(shù)表示向右偏移，負(fù)數(shù)表示向左偏移。height，高度。 width，寬度。OFFSET函數(shù)的功能：以指定的引用為參照，通過給定偏移量得到一個新的單元格區(qū)域引用。21例：OFFSET函數(shù)引用示例.xls公式1：=offset(B2,2,2,5,2)分析：以B2為基點，行偏移2（向下），列偏移2（向右）。從偏移點向下擴(kuò)展5行，向右擴(kuò)展2列，并返回該區(qū)域的值。公式2：=offset(G11,-3,-2,-5,-2)分析：以G11為基點，行偏移-3（向上），列偏移-2（向左）。從偏移點向上擴(kuò)展5行，向左擴(kuò)展2列，并返回該區(qū)域的值。22例：OFFSET函數(shù)引用示例.xls公式3：=offset(B2:C6,2,2)分析：以區(qū)域B2:C6的左上角單元格B2為基點，行偏移2（向下），列偏移2（向右）。因為省略了參數(shù)height和width，公式取height=原區(qū)域高度=5，取width=原區(qū)域?qū)挾?2；即從偏移點向下擴(kuò)展5行，向右擴(kuò)展2列。返回該區(qū)域的值。公式4：=offset(F10:G11,-2,-2,-5)分析：以區(qū)域F10:G11的左上角單元格F10為基點，行偏移-2（向上），列偏移-2（向左）。height=-5，即從偏移點向上擴(kuò)展5行。因為省略了參數(shù)width，公式取width=原區(qū)域?qū)挾?2；即從偏移點向左擴(kuò)展2列。返回該區(qū)域的值。23使用OFFSET函數(shù)的注意事項：如果指定的基點是一個區(qū)域，則函數(shù)以該區(qū)域左上角單元格為基點進(jìn)行偏移運算。如果省略參數(shù)height，則系統(tǒng)取height=原區(qū)域高度。如果省略參數(shù)width，則系統(tǒng)取width=原區(qū)域?qū)挾?。例：offset函數(shù)應(yīng)用.xlsF2單元格公式：

=AVERAGE(OFFSET(C1,COUNT(C:C),0,-E2,1))查找與引用函數(shù)的綜合應(yīng)用2425例：查詢申購基金的申購手續(xù)費.xls下圖模擬了一份基金購買的申購費率表，其中左表為購買基金的費率標(biāo)準(zhǔn)，右表D1:E4區(qū)域為根據(jù)標(biāo)準(zhǔn)轉(zhuǎn)換得到的數(shù)值關(guān)系表。請根據(jù)購買者的申購金額來計算應(yīng)付的申購基金手續(xù)費。26回憶：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)range_lookup：決定查找方式。=0（或false）

精確匹配。支持無序查找。=1（或true）

模糊匹配。要求第2個參數(shù)（table_array）的首列按升序排列。B8單元格的公式：=B6*VLOOKUP(B6,D2:E4,2,1)回憶：公式：=MATCH(lookup_value,lookup_array,match_type)式中：Match_type：如何查找。可取三個值：-1,0,1.當(dāng)match_type=1時：Lookup_array必須按升序排列，MATCH函數(shù)從小往大查，查找到≤Lookup_value的最大數(shù)值。27查什么在哪里查以什么方式查C8單元格的公式：=B6*INDEX(D2:E4,MATCH(B6,D2:D4,1),2)28Match()和Vlookup()的聯(lián)系與區(qū)別MATCH(lookup_value,lookup_array,match_type)——在lookup_array中搜索lookup_value，返回其位置序號。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)——在table_array的首列縱向搜索lookup_value，然后根據(jù)col_index_num返回對應(yīng)的值。Match()Vlookup()搜索區(qū)域lookup_array是一維的（一行或一列）table_array至少包含兩列返回值lookup_value的相對位置序號根據(jù)col_index_num，返回與lookup_value在同一行的某個值。29例：利用查找函數(shù)進(jìn)行多表數(shù)據(jù)標(biāo)識.xls下圖展示了一份某行政部門的員工分工明細(xì)表，各員工的數(shù)據(jù)表結(jié)構(gòu)相同，為了便于部門經(jīng)理了解各位員工的工作分配情況，請將各員工的工作明細(xì)表匯總到部門工作一覽表。30一、補(bǔ)充：邏輯函數(shù)IFERROR(value,value_if_error)error：錯誤。解釋：在第一個參數(shù)“value”的位置放入一個公式，如果公式能順利計算出結(jié)果，則返回該結(jié)果；如果公式計算結(jié)果為錯誤，則返回value_if_error。功能：屏蔽錯誤值。例：iferror()示例31C3單元格公式：=MATCH(B3,張三!$B$3:$B$6,0)二、分析1.張三是否做了該項工作？返回位置序號：張三做了該工作。返回錯誤值#N/A：張三沒有做該工作。2.屏蔽錯誤值，讓其顯示為0C3單元格公式：=IFERROR(MATCH(B3,張三!$B$3:$B$6,0),0)3.用”√”代替非0，用空文本替代0.C3單元格公式：=IF(IFERROR(MATCH(B3,張三!$B$3:$B$6,0),0),"√","")非零（正數(shù)、負(fù)數(shù)）為真，零為假32例：通過身份證號提取個人相關(guān)信息.xls33一、新編號UPPER(text)：將一個字符串中的所有小寫字母轉(zhuǎn)換為大寫字母。upper：上面的，上部的；較高的。LOWER(text)：將一個字符串中的所有大寫字母轉(zhuǎn)換為小寫字母。lower：下游的，下級的，下等的。PROPER(text)：將字符串的首字母轉(zhuǎn)換為大寫，將其余的字母轉(zhuǎn)換為小寫。proper：適當(dāng)?shù)摹Ｑa(bǔ)充：3個文本函數(shù)B3單元格公式：=PROPER(A3)34二、出生日期（1）補(bǔ)充：文本函數(shù)MID(text,start_num,num_chars)MID：middle，中間的。Start_num：text中要提取的第一個字符的位置。num_chars：character，字符。提取字符的數(shù)量。分析：增添輔助列“年”、“月”、“日”。（2）補(bǔ)充：日期與時間函數(shù)DATE(year,month,day)DATE：日期。功能：根據(jù)指定的年份、月份和日期返回具體的日期值。例：=DATE(K3,M3+1,L3)E3單元格公式：=DATE(MID(F3,7,4),MID(F3,11,2),MID(F3,13,2))35三、年齡補(bǔ)充：日期與時間函數(shù)DATEDIF()——一個隱藏的、但功能非常強(qiáng)大的日期函數(shù)。沒有在函數(shù)列表中顯示出來。幫助文件中沒有相關(guān)說明。（1）函數(shù)的語法：=DATEDIF(start_date,end_date,unit)Start_date、end_date：分別為起始日期和結(jié)束日期。可以是單元格引用可以是帶引號的日期，如”2010-7-3”或”2010/7/3”可以是其他公式或函數(shù)的結(jié)果，如DATE(2010,8,3)36（1）函數(shù)的語法：=DATEDIF(start_date,end_date,unit)unit代碼函數(shù)返回值“y”時間段中的整年數(shù)“m”時間段中的整月數(shù)“d”時間段中的整天數(shù)“md”start_date與end_date日期中天數(shù)的差。忽略日期中的年和月“ym”start_date與end_date日期中月數(shù)的差。忽略日期中的年和日“yd”start_date與end_date日期中月數(shù)的差。忽略日期中的年例：datedif函數(shù)示例.xlsG3單元格公式：=DATEDIF(E3,TODAY(),"y")37四、資歷補(bǔ)充：文本合并函數(shù)concatenate(text1,[text2],…)concatenate：連結(jié)。功能：將幾個文本項合并為一個文本項。連接項可以是文本、數(shù)字、單元格地址。=A1&B1=concatenate(A1,B1)功能相同J3單元格公式：=CONCATENATE(DATEDIF(I3,TODAY(),"y"),"年",DATEDIF(I3,TODAY(),"ym"),"月")38例：統(tǒng)計員工相關(guān)數(shù)據(jù).xls39一、員工總?cè)藬?shù)補(bǔ)充：統(tǒng)計函數(shù)COUNTA(value1,[value2],…)功能：統(tǒng)計指定區(qū)域中不為空的單元格的個數(shù)。C3單元格公式：=COUNTA(檔案!A4:A21)二、女性員工人數(shù)C4單元格公式：=COUNTIF(檔案!D4:D21,"女")40三、學(xué)歷為本科的男性員工人數(shù)補(bǔ)充：多條件計數(shù)函數(shù)COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)功能：統(tǒng)計指定區(qū)域內(nèi)符合多個給定條件的單元格的數(shù)量。C5單元格公式：=COUNTIFS(檔案!H4:H21,"本科",檔案!D4:D21,"男")說明：criteria_range1、criteria_range2、…必須具有相同的列數(shù)和行數(shù)。四、基本工資總額C6單元格公式：=SUM(檔案!J4:J21)41五、管理人員工資總額C7單元格公式：=SUMIF(檔案!G4:G21,"管理",檔案