辦公自動(dòng)化培訓(xùn)

EXCEL函數(shù)應(yīng)用人力資源部Duney第一頁，共四十四頁。EXCEL函數(shù)1.時(shí)間函數(shù)2.數(shù)學(xué)函數(shù)3.統(tǒng)計(jì)、求和函數(shù)3.字符處理函數(shù)4.邏輯函數(shù)5.地址函數(shù)6.自動(dòng)化常用函數(shù)第二頁，共四十四頁。EXCEL函數(shù)的結(jié)構(gòu)Excel函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。Excel函數(shù)結(jié)構(gòu):也有一些函數(shù)是沒有參數(shù)的,如ROW()左右括號(hào)成對(duì)出現(xiàn)單一結(jié)構(gòu)嵌套結(jié)構(gòu)參數(shù)與參數(shù)之間使用半角逗號(hào)進(jìn)行分隔第三頁，共四十四頁。函數(shù)參數(shù)常用符號(hào)或表示方法函數(shù)公式中的文本必須用半角引號(hào)，如：〝東南汽車〞;而非直接輸入東南汽車或“東南汽車”連接符:&如：〝東南〞&〝汽車〞的值為東南汽車空值/空格的表示法:

空值：〝〞

空格：〝〞相關(guān)數(shù)學(xué)符號(hào):<>（不等于）;>=（大于等于）;<=（小于等于）單元格引用表示法:A2;A$2;$A2;A$2;A2:B7；1:1；1:5；F:F；A：N第四頁，共四十四頁。1.today():求今天現(xiàn)在的日期2.now():求現(xiàn)在3.year():求年

例：YEAR(“2012-12-31”)=20124.month():求“月”

例：MONTH(“2012-12-31”)=125.day():返回天

例：DAY(“2012-12-31”)=316.hour():求小時(shí)數(shù)7.minute()：求分鐘8.second():求秒9.date()：求日期10.datedif()例：datedif(“2012-1-1”,”2013-1-1”,”Y”)=111.TIMEVALUE()例：12:00和8:00相差TIMEVALUE(“12：00”)-TIMEVALUE(“8:00”)時(shí)間函數(shù)第五頁，共四十四頁。1.將“20060501”轉(zhuǎn)換為“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))2.將文本“2004.01.02”轉(zhuǎn)換為日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))3.將2005-8-6轉(zhuǎn)換為2005年8月6日格式=TEXT(A1,"yyyy""年""m""月""d""日"";@")4.將“1968年6月12日”轉(zhuǎn)換為“1968/6/12”格式=YEAR(A1)&"/"&MONTH(A1)&"/"&DAY(A1)顯示：1968/6/12=TEXT(A1,"yyyy/mm/dd")顯示：1968/06/125.用公式算出除去當(dāng)月星期六、星期日以外的天數(shù)=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&":"&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)>1))第六頁，共四十四頁。6.如何對(duì)日期進(jìn)行上、中、下旬區(qū)分=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})7.什么函數(shù)可以顯示當(dāng)前星期如：星期二10:41:56=TEXT(NOW(),"aaaa

hh:mm:ss")8.將“二○○三年十二月二十五日”轉(zhuǎn)為“2003-12-25”格式:｛=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),“[DBNum1]0000”),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)))｝9.自動(dòng)排序=SUBTOTAL(3,$B$2:B2)*1=IF(A2<>A1,1,N(C1)+1)10.怎樣按奇數(shù)順序然后再按偶數(shù)順序排序=IF(MOD(A1,2),0,1)=IF(ROW()>50,(ROW()*2)-100,(ROW()*2)-1)=ROW()*2-1-(ROW()>50)*99第七頁，共四十四頁。第八頁，共四十四頁。有一個(gè)日期比如:2007/02/12,想知道它減去一個(gè)固定天數(shù)比如6后,最接近它的一個(gè)星期四(只能提前)是多少號(hào)2007/02/12的答案應(yīng)該是2007/02/01而不是2007/02/08日期在A1處，B1處輸入：=MAX((WEEKDAY(A1-6-{1,2,3,4,5,6,7},2)=4)*(A1-6-{1,2,3,4,5,6,7}))A1

=2007/02/12B1,輸入公式:=A1-6-MOD(WEEKDAY(A1-6,2)+3,7)求最接近某一天的星期數(shù)第九頁，共四十四頁。1.ABS():取絕對(duì)值

例：ABS(-9)=92.int():取整數(shù)

例：INT(9.8)=93.round():

例：round(3.1415,3)=3.142求小數(shù)位數(shù)，四舍五入4.mod():返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同。

例：MOD(8,5)=3MOD(1,8)=1MOD(2,8)=2MOD(7,8)=75.max():

例：MAX(A:A)求A列中最大值6.min():

例：min(A:A)求A列中最小數(shù)7.large()：求第m大的數(shù)

例：LARGE(A:A,2)求A列中第二大的數(shù)8.small(2,A:A):

例：small(A:A,2):求A列中第2小的數(shù)9.rank(2,A:A):例rank(2,A:A):求2在A列中的排位（第幾大的）10.ISEVEN（）測(cè)試是否偶數(shù)，例：ISEVEN（4）=TRUE數(shù)學(xué)函數(shù)例：取前五名，后五名的方法{=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}{=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW())}第十頁，共四十四頁。第十一頁，共四十四頁。Count():統(tǒng)計(jì)Countif()：條件統(tǒng)計(jì)例：Countif(A:A,B1)A列中等于B1值的個(gè)數(shù)，判斷是否重復(fù)：例：Countif(A:A,“男”)；判斷A列（性別列）中為男性的人數(shù)例：Countblank（A:A）統(tǒng)計(jì)A列空格數(shù)目例：統(tǒng)計(jì)區(qū)域A1:C3中互不相同的數(shù)據(jù)個(gè)數(shù),

=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1))

Countifs()：多條件統(tǒng)計(jì)例如：COUNTIFS(A:A,“男”,B:B,“>20”,C:C,“黨員”)統(tǒng)計(jì)大于20歲的男性黨員的個(gè)數(shù)Countblank（）統(tǒng)計(jì)、求和、計(jì)算函數(shù)第十二頁，共四十四頁。Product():求乘積Sumproduct():條件統(tǒng)計(jì)Sum():求和Sumif():條件求和Sumifs():多條件求和例：SUMFS(H:H,A:A,“男”,B:B,“>20”,C:C,“黨員”)計(jì)算大于20歲的男性黨員的工資和（H列為工資列）Everage():求平均值QUARTILE()求四分位例：QUARTILE(B:B,1)求B列中前25%的四分位，1：前25%,2,50%,3,75%第十三頁，共四十四頁。高級(jí)統(tǒng)計(jì)類函數(shù)（部分）第十四頁，共四十四頁。第十五頁，共四十四頁。PERCENTRANK()PERCENTRANK()求百分比排位，可用于查看數(shù)據(jù)在數(shù)據(jù)集中所處的位置。例：PERCENTRANK(A:A，B1）:求B1在A列數(shù)據(jù)的百分位(要按CTRL+SHIFT+ENTER)第十六頁，共四十四頁。字符及處理函數(shù)Find()：查字符串中是否含有某字符（串）；區(qū)分大小寫，不能使用通配符

例：FIND("O","ILOVEYOU",4)=3find(“O”,“ILOVEYOU”，5)=9

從第5個(gè)字符開始找O，O在ILOVEYOU整個(gè)字符串中的位置是9

Search():查字符串中是否含有某字符（串）；不區(qū)分大小寫，能使用通配符（其他同F(xiàn)IND)replace(老字符串,開始位置,替代次數(shù)，新字符串):字符串替代函數(shù)

例：replace(“我是張三”,3,2,“李四”）=我是張三SUBSTITUTE(A1,“A”,B1)

例：SUBSTITUTE(“我是張三”,”“張三”,“李四”）=我是李四

統(tǒng)計(jì)某字符在字符串中出現(xiàn)的次數(shù)LEN(A1)-LEN(SUBSTITUTE(A1,6,“”)):統(tǒng)計(jì)字符6在字符串中出現(xiàn)的次數(shù)Rept():重復(fù)某一文本幾次例：rept(“*”,5)=*****第十七頁，共四十四頁。Lower():大寫轉(zhuǎn)小寫例：lower(“A”)=aUpper():小寫轉(zhuǎn)大寫例：Upper(“a”)=A:Len()：求字符串長(zhǎng)度例：Len(“ILOVEYOU”)=10Left():取字符串左邊第幾個(gè)字符例：left(“bs-400防水?dāng)z像機(jī)”,2)=bsRight():取字符串右邊第幾個(gè)字符例：right(“bs-400防水?dāng)z像機(jī)”,2)=bsMid():取字符串中間第幾個(gè)字符例：mid(“bs-400防水?dāng)z像機(jī)”,4,3)=400Trim():去掉單詞之間的其他空格例：Trim(“Iloveyou”)=IloveyouExact():判斷兩個(gè)字符串是否完全相同例：Exact(“a”,“A”)=false&：字符串連接函數(shù)例：“總數(shù)量：”&1+1&“人”=總數(shù)量：2人★

例：計(jì)算出一段話（在A1中）中，字符abc出現(xiàn)的個(gè)數(shù)：公式=(LEN(A1)-LEN(SUBSTITUTE(A1,"abc","")))/LEN("abc")

第十八頁，共四十四頁。第十九頁，共四十四頁。第二十頁，共四十四頁。is():判斷是否為——ISBLANK(value)、ISERR(value)、ISERROR(value)、ISLOGICAL(value)、ISNA(value)、ISNONTEXT(value)、ISNUMBER(value)、ISREF(value)、ISTEXT(valueN():轉(zhuǎn)換為數(shù)值TYPE(value)And():并且Or()或者Not():否定，相反If():如果邏輯函數(shù)例如：自動(dòng)生成序號(hào),比如在第二列中輸入內(nèi)容回車后第一列的下一行自動(dòng)生成序列號(hào)。=IF(B2<>"",A2+1,"")第二十一頁，共四十四頁。地址函數(shù)第二十二頁，共四十四頁。第二十三頁，共四十四頁。indirect():

例如：若B10單元格等于15，則=indirect(“B”&row(A10))=B10index()index(引用區(qū)域，行，列)Match():查找匹配第二十四頁，共四十四頁。CHOOSE()

例：CHOOSE(3,”1A”,”AW”,”D”,”T”,)=D例：求生肖CHOOSE(MOD(YEAR(出生日期)-4,12)+1,"鼠","牛","虎","兔","龍","蛇","馬","羊","猴","雞","狗","豬"))第二十五頁，共四十四頁。辦公自動(dòng)化的幾個(gè)最重要函數(shù)1.OFFSET($A$1，MATCH(,1,2,3),COLUMN())$A$1:坐標(biāo)原點(diǎn)，即“A”列為0列，“1”行為0行2.SUMIFS(D:D,A,A1,B,B1,C,C1)D:D:對(duì)D列求和；條件1：A列中值=A1;

條件2:B列中值=B1;

條件3:C列中值=C1;3.Countifs(區(qū)域1,值1,區(qū)域2,值2…)

條件1：區(qū)域1中值=值1;

條件2:區(qū)域2中值=值2;

……第二十六頁，共四十四頁。自動(dòng)化實(shí)例第二十七頁，共四十四頁。1.C-N列公式：SUMIFS(訂單跟蹤匯總表!$Q:$Q,訂單跟蹤匯總表!$E:$E,$A4,訂單跟蹤匯總表!$C:$C,YEAR($B$2),訂單跟蹤匯總表!$D:$D,LEFT(C$3,LEN(C$3)-1))2.R列公式：RANK(O4,O:O,0)-13.S列公式：COUNTIF(訂單跟蹤匯總表!G:G,客戶資料表!B2)4.T列公式;LOOKUP(2,1/(訂單跟蹤匯總表!$G$3:$G$10000=客戶資料表!B2),訂單跟蹤匯總表!$Q$3:$Q$10000)5.U列公式：LOOKUP(2,1/(訂單跟蹤匯總表!$G$3:$G$10000=客戶資料表!B2),訂單跟蹤匯總表!$B$3:$B$10000)6.如何自動(dòng)標(biāo)注底色提醒？？？？——條件格式設(shè)置第二十八頁，共四十四頁。第二十九頁，共四十四頁。1.F/I/J/K列公式：OFFSET(產(chǎn)品代碼表!$A$1,MATCH(E3,產(chǎn)品代碼表!$A$2:$A$30000,0),N)2.N列公式：SUMIFS(M:M,E:E,E3)3.0列公式：SUMIFS(庫(kù)存!C:C,庫(kù)存!A:A,訂單跟蹤匯總表!E3)4.P列公式：IF(N3<=O3,"足夠","差"&N3-O3&"個(gè)")5.U列公式：IF(ISERROR(DATEDIF(TODAY(),T3,"D")),-DATEDIF(T3,TODAY(),"D"),DATEDIF(TODAY(),T3,"D"))6.AA列公式：IF(Z3="OK","OK",IF(P3="足夠","OK","須跟進(jìn)"))第三十頁，共四十四頁。1.B列公式：OFFSET(進(jìn)銷存匯總表!$A$3,MATCH($A5,進(jìn)銷存匯總表!$A$4:$A$1101,0),1)2.I-R列公式：SUMIFS(辦公用品領(lǐng)用登記表!$J:$J,辦公用品領(lǐng)用登記表!$E:$E,$A5,辦公用品領(lǐng)用登記表!$K:$K,產(chǎn)品使用明細(xì)1!I$4)3S列公式：SUM(C5:R5)4.T列公式：S5/$S$1745.U列公式：RANK(S5,S$5:S$173)第三十一頁，共四十四頁。其他應(yīng)用技巧1.自動(dòng)變色提醒：右擊選中列→開始→條件格式→突出顯示/新建規(guī)則…2.下拉框選擇按鈕：選中單元格→數(shù)據(jù)→數(shù)據(jù)有效性→允許中選擇“序列”→勾選忽略空值→來源填寫下拉框內(nèi)容所在的表和區(qū)域3.插入名稱函數(shù)：例：選中B1單元格→公式→定義名稱→名稱欄輸入“SIDE”→引用位置輸入EVALUEATE($A$1)→確定→在B1單元格輸入“=SIDE”,回車第三十二頁，共四十四頁。如何做多級(jí)聯(lián)動(dòng)下拉菜單1.選中單元格K2→數(shù)據(jù)→數(shù)據(jù)有效性→允許中選擇“序列”→勾選忽略空值→來源填寫：=檔案!$A$2:$E$2或=INDIRECT(“檔案!A2:AD2”)→確定;注:第2行是部門標(biāo)題欄2.選中單元格L2→數(shù)據(jù)→數(shù)據(jù)有效性→允許中選擇“序列”→勾選忽略空值→來源填寫=OFFSET(INDIRECT("檔案!A2"),1,MATCH($A2,INDIRECT("檔案!2:2"),)-1,30)→確定檔案表下拉表中人員數(shù)據(jù)有效性填充（二級(jí)下拉框）下拉表中部門數(shù)據(jù)有效列填充（一級(jí)下拉框）第三十三頁，共四十四頁。其他重要函數(shù)挑出本月離職人員名單，形成單獨(dú)表格：

INDEX(離職人員!B:B,SMALL(IF((離職人員!BJ$1:BJ$30000>=MONTH(NOW())-1)*(離職人員!BK$1:BK$30000=YEAR(NOW())),ROW($1:$30000)),ROW(1:1)))&"“

按CTRL+SHIFT+ENTER挑出本月入職人員，形成單獨(dú)表格：INDEX(在職人員!B:B,SMALL(IF(在職人員!$AD$1:$AD$9997>=DATE(YEAR(NOW()),MONTH(NOW()),1),ROW($B$1:$B$10000),65535),ROW(2:2)))&""判斷單元格(A1～A12)單元格數(shù)據(jù)是否重復(fù)并統(tǒng)計(jì)出重復(fù)的行號(hào),在B1中輸入：

=IF(COUNTIF($A$1:$A$13,VLOOKUP(A1,A2:$A$13,1,0))>1,CONCATENATE("重復(fù)行號(hào):",MATCH(A1,A2:A$13,0)+ROW(A1)),"")

B1單元格中會(huì)顯示與A1數(shù)據(jù)重復(fù)的行號(hào)。下面，選擇區(qū)域B1～B12，點(diǎn)擊菜單欄“編輯”→“填充”→“序列”，在彈出對(duì)話框中查看“類型”項(xiàng)目，在此選擇“自動(dòng)填充”，其余選項(xiàng)保持默認(rèn)設(shè)置。確認(rèn)操作后，B2～B12之間的重復(fù)行號(hào)均會(huì)自動(dòng)填充（如圖1）。第三十四頁，共四十四頁。1.求“您好”第n次出現(xiàn)的行號(hào):SMALL(IF($A$1:$A$10="您好",ROW($A$1:$A$10),4^8),ROW(1:1))2.自動(dòng)從大到小、從小到大排序：H2中輸入“=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0))”，最后按CTRL+SHIFT+ENTER，3.陽歷轉(zhuǎn)陰歷IF(ISERROR(MONTH(TEXT(A2,"[$-130000]e-m-d"))),SUBSTITUTE(TEXT(A2,"[$-130000]e-m-d"),13,12),TEXT(A2,"[$-130000]e-m-d"))★4.自動(dòng)挑出出現(xiàn)頻率最多的數(shù)值，并由大排列形成新的表假設(shè)：表1中的E列中是電話號(hào)碼（有很多號(hào)碼是重復(fù)的），如把打的最多的電話號(hào)碼挑選出來，并由大到校排列（第一行是標(biāo)題）：

1.在O2中輸入：countif(E:E,E1)，拉下去，——>統(tǒng)計(jì)每個(gè)號(hào)碼出現(xiàn)的次數(shù)

2.P2列中輸入：INDEX(O:O,SMALL(IF(MATCH(IF(O:O="",1,O:O),IF(O:O="",1,O:O),)=ROW(O:O),ROW(O:O),65536),ROW(A2)))按CTRL+SHIFT+ENTER,數(shù)組公式，拉下去，——>挑出不重復(fù)的出現(xiàn)次數(shù)

第三十五頁，共四十四頁?！?.自動(dòng)挑選排序填的列中輸入（新的表，新的號(hào)碼列）：=OFFSET(表1$A$1,MATCH(LARGE(表1!P:P,ROW(B1)),表1!O:O,0)-1,5)回車

——>LARGE(表1!P:P,ROW(B1))：求出現(xiàn)次數(shù)中第一大的（即出現(xiàn)最多的次數(shù)）

——>MATCH(LARGE(表1!P:P,ROW(B1)),表1!O:O,0)-1:求(P列)最大次數(shù)在次數(shù)列(O列）中找，找到后返回行號(hào)，減1為減標(biāo)題欄

——>OFFSET(表1$A$1，，5）以表1的A1單元格為坐標(biāo)遠(yuǎn)點(diǎn)，引用返回出現(xiàn)撥打第N多的電話號(hào)碼所在的行列號(hào)中的值。第三十六頁，共四十四頁?！?.從含有重復(fù)值的列中挑選不重復(fù)的值形成另一個(gè)列方法1：（數(shù)組公式，內(nèi)存占用大，但是中間沒空格）INDEX(F:F,SMALL(IF(MATCH(IF(F:F="",1,F:F),IF(F:F="",1,F:F),)=ROW(F:F),ROW(F:F),65536),ROW(A2)))數(shù)組公式，按CTRL+SHIFT+ENTER方法2：（函數(shù)，內(nèi)存占用小，中間有空格）IF(ISERROR(MATCH(F2,$F$1:$F1,0)),INDEX(F:F,ROW(F2)),"")往下拖公式。方法3：非公式法

選中數(shù)據(jù)列--數(shù)據(jù)篩選高級(jí)將將篩選結(jié)果復(fù)制到其他位置

光標(biāo)移到“復(fù)制到”框勾選“選擇不重復(fù)的記錄”確定第三十七頁，共四十四頁。★7.自動(dòng)顯示重復(fù)行行號(hào)（根據(jù)行號(hào)可自動(dòng)把重復(fù)數(shù)據(jù)形成報(bào)表）若A列是數(shù)據(jù)，有重復(fù)數(shù)據(jù)，可在B列輸入下列公式并按CTRL+SHIFT+ENTERIF(COUNTIF($A$1:$A$13,VLOOKUP(A2,A3:$A$13,1,0))>1,MATCH(A2,A3:A$13,0)+ROW(A2),"")第三十八頁，共四十四頁。如何自動(dòng)標(biāo)示A欄中的數(shù)字大小排序？=RANK(A1,$A$1:$A$5)=RANK(A1,A:A)如何設(shè)置自動(dòng)排序,A列自動(dòng)變成從小到大排列B=SMALL(A$2:A$28,ROW(1:1))A列自動(dòng)變成從大到小排列B=LARGE(A$2:A$28,ROW(1:1))重復(fù)數(shù)據(jù)得到唯一的排位序列想得到數(shù)據(jù)的出現(xiàn)總數(shù)嗎（{1,2,2,3,4,4,5}數(shù)據(jù)的出現(xiàn)總數(shù)為5）？

=RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1怎樣才能讓數(shù)列自動(dòng)加數(shù)怎樣做才能讓數(shù)列自動(dòng)加數(shù)，例：A000X公式為=A1&“000”&COUNTIF(A$1:A1,A1)向下拖對(duì)于普通排名分?jǐn)?shù)相同時(shí)，按順序進(jìn)行不重復(fù)排名=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1如何實(shí)現(xiàn)快速定位（篩選出不重復(fù)值）=IF(COUNTIF($A$2:A2,A2)=1,A2,"")=IF((COUNTIF($A$2:A2,A2)=1)=TRUE,A2,"")第三十九頁，共四十四頁。在工作表里有連續(xù)10行數(shù)據(jù),現(xiàn)在要每行間格2行=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z