excel函數(shù)應(yīng)用

1、工作中常用excel函數(shù)集合制作人：王超峰vlookup函數(shù)Sumif和Countif函數(shù)IF函數(shù)Sumifs和Countifs函數(shù)Round函數(shù)、INT函數(shù)Left、Right和Mid函數(shù)Datedif函數(shù)IFERROR函數(shù)1.vlookup函數(shù)用途:數(shù)據(jù)查找、表格核對(duì)、表格合并、用途：關(guān)聯(lián)不同表格，使數(shù)據(jù)在一個(gè)表格中使用、查看。備注：可以多個(gè)表關(guān)聯(lián)到一個(gè)表中。用法：=vlookup(查找的值，查找區(qū)域，返回值所在列數(shù)，精確還是模糊查找)應(yīng)用方法示例1：1.商品表1,看有商品代號(hào)。2.商品表2,也有商品代號(hào)。4.接著下面用公式引用。在商品表1中選中單元格,再點(diǎn)擊上面的公式,找到vlookup

2、.點(diǎn)確定3.把商品表2中的其它兩個(gè)分類(lèi)標(biāo)題欄目復(fù)制到商品表1中。關(guān)于Vlookup函數(shù)內(nèi)容解析：Lookup_value“唯一值”為需要在數(shù)組第一列中查找作參考的關(guān)系數(shù)值，它可以是數(shù)值或文字符串。比如:舉例中的商品代碼列，一般是第一列的數(shù)值，作為唯一數(shù)據(jù)參考而用的。（本表格中）Table_array“區(qū)域”：數(shù)值區(qū)域，如“A:D”，就是所有想引用過(guò)來(lái)的數(shù)據(jù)（想要引用數(shù)據(jù)的表格）Col_index_unm“列序號(hào)”：引用區(qū)域值里面的第一列，比如從A到D共有4列，想引用B列數(shù)值過(guò)來(lái)，那么就選擇2，因?yàn)锽列在區(qū)域的第二列，其它也是這么推算Range_lookup“邏輯值”：一般填TRUE或FALSE

3、。指函數(shù) VLOOKUP 返回時(shí)是精確匹配還還是近似匹配。 通常都是寫(xiě)false。5.另一列的品牌也是照著上面的方法,重復(fù)第4與第5步,也可以直接插入復(fù)制第前一列的粘貼操作.只是需要把那個(gè)第三個(gè)數(shù)值的,第二列2改成第三列3.完成再把公式拉下來(lái)。6.展現(xiàn)的效果就是如圖下,結(jié)合了兩個(gè)表格的數(shù)據(jù).舉例的數(shù)據(jù)不是很多,但數(shù)據(jù)一龐大時(shí),用這個(gè)方法很合適。小技巧：使用vlookup函數(shù)時(shí)，=vlookup(查找的值，查找區(qū)域，返回值所在列數(shù)，精確還是模糊查找)，其中”查找的值“可以設(shè)定為一空白單元格，可以手動(dòng)輸入要查找的內(nèi)容（查找的值須具有唯一性）。應(yīng)用方法示例2：A3:B7單元格區(qū)域?yàn)樽帜傅燃?jí)查詢(xún)表，表

4、示60分以下為E級(jí)、6069分為D級(jí)、7079分為C級(jí)、8089分為B級(jí)、90分以上為A級(jí)。D:G列為初二年級(jí)1班語(yǔ)文測(cè)驗(yàn)成績(jī)表，如何根據(jù)語(yǔ)文成績(jī)返回其字母等級(jí)？在H3:H13單元格區(qū)域中輸入=VLOOKUP（G3，$A$3:$B$7,2）（表：應(yīng)用方法2.xls）輸入名字后自動(dòng)查找出后面內(nèi)容（表應(yīng)用示例3）應(yīng)用示例32、Sumif和Countif函數(shù)用途：按條件求和，按條件計(jì)數(shù)，很多復(fù)雜的數(shù)據(jù)核對(duì)也需要用到這2個(gè)函數(shù)。用法：=Sumif(判斷區(qū)域,條件，求和區(qū)域)=Counif(判斷區(qū)域,條件）sumif函數(shù)示例;統(tǒng)計(jì)A產(chǎn)品的總金額公式：F2=SUMIF(A:A,E2,C:C)表sumif函

5、數(shù)公式：=countif(a:a,e2)3、IF函數(shù)用途：根據(jù)條件進(jìn)行判斷用法：=IF(判斷條件,條件成立返回的值,條件不成立返回的值） 示例1詳見(jiàn)表if函數(shù)-1注意：輸入法的選擇，輸入函數(shù)中符號(hào)時(shí)，要用英文輸入法示例2If函數(shù)可以嵌套，最多可嵌套七層將成績(jī)分為三個(gè)等級(jí)-不及格、中等、優(yōu)秀的編程方式小提示：If函數(shù)應(yīng)用中，使用的and或or函數(shù),and是指包括的條件都滿(mǎn)足，or是指包括的條 件只要滿(mǎn)足一個(gè)就返回真值判斷條件可以為計(jì)算公式。例：=IF(D4*B7=5，D4*B7,5)If函數(shù)可以通過(guò)函數(shù)嵌套，來(lái)進(jìn)行一些復(fù)雜操作。例：=IF(COUNTIF(C3:E3,=90)=3,一等獎(jiǎng),IF(

6、COUNTIF(C3:E3,=85)=3,二等獎(jiǎng),IF(COUNTIF(C3:E3,=75)=3,三等獎(jiǎng),)4、Sumifs和Countifs函數(shù)用途：多條件求和、多條件計(jì)數(shù)，數(shù)據(jù)分類(lèi)匯總利器用法：=Sumifs(求和區(qū)域,判斷區(qū)域1，條件1，判斷區(qū)域2，條件2.）=Countifs(判斷區(qū)域1，條件1，判斷區(qū)域2，條件2.） sumifs函數(shù)示例1: 求女生語(yǔ)文成績(jī)總和，=sumifs(c2:c8,b2:b8,”女”)表sumifs函數(shù)示例2：求語(yǔ)文和數(shù)學(xué)得分都大于90分的學(xué)生總分之和使用函數(shù)=sumifs(f2:f8,c2:c8,”=90”,d2:d8,”=90”)示例3 （擴(kuò)展應(yīng)用，可借

7、鑒）項(xiàng)目一：客戶(hù)A的銷(xiāo)售額=SUMIFS(C2:C10,A2:A10,A2)項(xiàng)目二：客戶(hù)A的1月份銷(xiāo)售額=SUMIFS(C2:C10,A2:A10,A2,B2:B10,B2)項(xiàng)目三：客戶(hù)A的1月份和3月份銷(xiāo)售額=SUM(SUMIFS(C2:C10,A2:A10,A2,B2:B10,1,3)項(xiàng)目四：客戶(hù)A和C的銷(xiāo)售額=SUM(SUMIFS(C2:C10,A2:A10,A,C)項(xiàng)目五：客戶(hù)A和C的1月份銷(xiāo)售額合計(jì)=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,B2)項(xiàng)目六：客戶(hù)A的1月份和客戶(hù)C的3月份銷(xiāo)售額合計(jì)=SUM(SUMIFS(C2:C10,A2:A10,A,C,

8、B2:B10,1,3)項(xiàng)目七：客戶(hù)A和客戶(hù)C的1月份和3月份銷(xiāo)售額合計(jì)=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,1;3)項(xiàng)目八：客戶(hù)A和客戶(hù)C的1月份3月份4月份銷(xiāo)售額合計(jì)=SUM(SUMIFS(C2:C10,A2:A10,A,C,B2:B10,1;3;4)項(xiàng)目九：客戶(hù)ABC的1月份3月份4月份銷(xiāo)售額合計(jì)=SUM(SUMIFS(C2:C10,A2:A10,A,B,C,B2:B10,1;3;4)注意逗號(hào)和分號(hào)的區(qū)別。countifs函數(shù)=Countifs(判斷區(qū)域1，條件1，判斷區(qū)域2，條件2.）示例1： 統(tǒng)計(jì)1月績(jī)效大于70分，2月績(jī)效大于80分的有多少人？=c

9、ountifs(c:c,”70”，d:d,”80”)示例2統(tǒng)計(jì)1月績(jī)效大于70分，2月績(jī)效大于80分，總分大于230的人數(shù)=countifs(c:c,”70”，d:d,”80”,f:f,”230”)5、Round函數(shù)、INT函數(shù)用途：數(shù)值四舍五入和取整函數(shù)用法：四舍五入 =Round(數(shù)值,保留的小數(shù)位數(shù)） 取整 =INT(數(shù)值)示例: A1的值為256.235，要求取整=INT(A1)保留兩位小數(shù)=Round(A1.2)6、Left、Right和Mid函數(shù)用途：字符串的截取用法：=Left(字符串,從左邊截取的位數(shù))=Right(字符串,從右邊截取的位數(shù))=Mid(字符串,從第幾位開(kāi)始截,截

10、多少個(gè)字符）示例：截取身份證前六位、后五位、出生日期=LEFT(B2,6)=RIGHT(B2,5)=MID(B2,7,8)=MID(B2,7,4)&年&MID(B2,11,2)&月&MID(B2,13,2)&日7、Datedif函數(shù)用途：日期的間隔計(jì)算。用法：=Datedif(開(kāi)始日期,結(jié)束日期.y) 間隔的年數(shù)=Datedif(開(kāi)始日期,結(jié)束日期.M) 間隔的月份=Datedif(開(kāi)始日期,結(jié)束日期.D) 間隔的天數(shù)示例：A1是開(kāi)始日期（2011-12-1），B1是結(jié)束日期(2013-6-10)。計(jì)算：相隔多少天？=datedif(A1,B1,d) 結(jié)果：557相隔多少月?=datedif(A1,B1,m) 結(jié)果：18相隔多少年? =datedif(A1,B1,Y) 結(jié)果：1不考慮年相隔多少月？=datedif(A1,B1,Ym) 結(jié)果：6不考慮年相隔多少天？=datedif(A1,B