16個(gè)必須掌握的函數(shù)公式,一般人我不告訴哦!

1、合格的財(cái)會(huì)人員肯定是合格的Excel使用人員，如果你是職場(chǎng)小白，不妨先從下文財(cái)會(huì)人員必須掌握的函數(shù)公式公式。一、財(cái)會(huì)必備技能：一般值的重復(fù)判斷 目的：判斷地區(qū)”是否重復(fù)。F3-:六 力 在A&CDE1Excel函數(shù)公式：財(cái)會(huì)人以必備的個(gè)函數(shù)公式2No姓名性別銷量地區(qū)備注31男66北京42小西咒56上海53小小女67蘇州64小北女56上海75小中男33天津S6小王女57上海97小李20上海1C重點(diǎn):=JF(COUNTEFE$3:E$9fE3)>1:'重復(fù)在目標(biāo)單元格中輸入公式：=IF(COUNTIF(E$3:E$9E3)>1"重復(fù)""&q

2、uot;)解讀：1、Countif函數(shù)是單條件計(jì)數(shù)函數(shù)，作用為計(jì)算指定區(qū)域中滿足條件的單元格個(gè)數(shù)；語(yǔ)法結(jié)構(gòu)為：=Countif(條件范圍，條件)。2、公式：=IF(COUNTIF(E$3:E$9E3)>1"重復(fù)""")首先用Countif函數(shù)統(tǒng)計(jì)范圍中指定值的個(gè)數(shù), 然后用IF函數(shù)判斷，如果值的個(gè)數(shù) >1,則返回 量復(fù)”，否則返回空值。二、財(cái)會(huì)必備技能：大于等于15位值的重復(fù)判斷。目的：判斷身份證是否重復(fù)。E3.X *z 內(nèi)ABCDIE_L1E工cd函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式2No姓名7份證號(hào)出生H期備注31E東411712196

3、5082141011965-08-2142小西4117221975052112341975-05-2153小小411712196sl 詠21400。198(1-10-2364小北4117221 <*67(*92010181967-09-2®15小中4117221990(191220341990-09-12S6小王r 4117221 98510084111)1985-10-0897小李r4117221975052112302011-09-201C重點(diǎn):=IF(O)UN*nF(C$3:C$9,C3&f A1J 重復(fù)方法：在目標(biāo)單元格中輸入公式：=IF(COUNTIF(C$3

4、:C$9C3&"*")>1" 重復(fù)""")。解讀：1、從示例中可以看出，所有的身份證號(hào)并沒有重復(fù)值，但為什么公式：=IF(COUNTIF(C$3:C$9C3)>1"重復(fù)""")的判斷結(jié)果有重復(fù)值？因?yàn)樵?Excel中，能最多存儲(chǔ)的 數(shù)據(jù)位數(shù)為15位，15位之后的數(shù)值全部按“0處理。對(duì)比身份證號(hào)，發(fā)現(xiàn)判斷重復(fù)的值都是末尾幾個(gè)值不同，被按照“0處理，所以顯示重復(fù)。2、公式：=IF(COUNTIF(C$3:C$9C3&"*")>1" 重復(fù)

5、""")中，在Countif函數(shù)的判斷條件后面添 加了 “*”(星號(hào))，就能得到正確的結(jié)果，是因?yàn)樘砑恿恕?”(星號(hào))之后將原本的數(shù)值強(qiáng)制轉(zhuǎn)換為文本，所以得到了正確的結(jié)果。三、財(cái)會(huì)必備技能：提取出生年月。目的：從身份證號(hào)碼中提取出生年月DRABcDE1E工cd函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式2No姓匕年份證號(hào)出生H期備注31E東41171219651 第 21410142小西4117221975115211234r53小南4117121965118214mmF64小北411722196711920101815小中4117221990(19122034F-86

6、小王"41172219851008411097小李4117221 (*7505211230點(diǎn) nTEXT(Mn)(C378'<M)-0QTMr)方法：在目標(biāo)單元格中輸入公式：=TEXT(MID(C378)"00-00-00")。解讀：1、Mid函數(shù)的作用為：從指定字符串的指定位置提取指定長(zhǎng)度的字符。語(yǔ)法結(jié)構(gòu)為：=Mid(字符串，開始位置，長(zhǎng)度)。而身份證號(hào)中的從第 7位開始，長(zhǎng)度為8的字符正好為出 生年月。2、Text函數(shù)的作用為：根據(jù)指定的格式將數(shù)值轉(zhuǎn)換為文本。語(yǔ)法結(jié)構(gòu)為：=Text(字符串，格式代碼)。3、公式：=TEXT(MID(C378)&q

7、uot;00-00-00")首先利用Mid函數(shù)提取出生年月的 8位數(shù)字，然后 用Text函數(shù)將其設(shè)置為：XXXX-XX-XX 的形式。四、財(cái)會(huì)必備技能：計(jì)算年齡。目的：根據(jù)身份證號(hào)碼計(jì)算年齡。E3X %/ AACDEF1Excel函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式2No姓名身份證號(hào)出生H期年的備注311凍"4117121965082141011965-08-21r42小quot;1975-05-21廣53小#39;1965-08-21L64小 1967

8、-09-20L75小中411722199009122IM41990-09-12F86小E"41172219851008411019S5-10-0Srg7小李4117221975052112301975f 5-21r1C嶗:=DATEDIF(TEXT(MID(C3,7,C00-00-00)TODAYOy，方法：在目標(biāo)單元格中輸入公式：=DATEDIF(TEXT(MID(C378)"00-00-00")TODAY()"y")。解讀：1、Datedif函數(shù)為系統(tǒng)隱藏函數(shù)，其作用為按照指定的類型計(jì)算兩個(gè)日期之間的差值。語(yǔ)法結(jié)構(gòu)為：=Datedif(開始

9、日期，結(jié)束日期，統(tǒng)計(jì)方式 )，常見的統(tǒng)計(jì)方式為：Y:年；M:月;D:日。2、公式：=DATEDIF(TEXT(MID(C378)"00-00-00")TODAY()"y")首先提取出生年月，然后和當(dāng)前的(Today。)的日期相比，計(jì)算相差的年份(Y),暨計(jì)算出年齡。五、財(cái)會(huì)必備技能：提取性別。目的：從身份證號(hào)中提取性別。D3,! | X a K |ABCDEF1EkN函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式2No姓名身份證號(hào)性別年聆備注31上東"quot;5442小西4

10、4F53小#39;5464小北F 4117221967092010185115小中"4117221990091220(1028r86小33rg7小李44r重點(diǎn)；=IBMOD(MID(C3J7以尸力F女、 »10方法：在目標(biāo)單元格中輸入公式：=IF(MOD(MID(C3171)2)"男""女")。解讀：1、Mod函數(shù)的作用為求余數(shù)，語(yǔ)法結(jié)構(gòu)為：=Mod(被除數(shù)，除數(shù))。2、身份證號(hào)碼中的第17位代表的是性別，如果為計(jì)數(shù)

11、，則為男性，否則為女性。3、利用Mid函數(shù)提取第17位，然后用Mod函數(shù)求余數(shù)，最后IF函數(shù)判斷，如果為奇數(shù), 返回男，否則返回女。六、財(cái)會(huì)必備技能：計(jì)算退休時(shí)間。目的：根據(jù)身份證號(hào)計(jì)算退休時(shí)間。E3/ J |xV &ABCDEF 11EkN函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式2No姓名身份證號(hào)性別退休時(shí)間備注31上東"女42小西F53小女64小北F男15小中"4117221990091220(10女r86小

12、男rg7小李女r10幣：點(diǎn)：=EDATE(TEXKMDl( 3,7.8k'I/nOl/lHr hMODcMlDtCn,bJUd+AOO)方法：在目標(biāo)單元格中輸入公式：=EDATE(TEXT(MID(C378)"0!/00!/00")MOD(MID(C3171)2)*120+600)。解讀：1、Edate函數(shù)的作用為：返回起始日期之前或之后的月份。語(yǔ)法結(jié)構(gòu)為：=Edate(起始日期，月份)。2、公式：=EDATE(TEXT(MID(C378)"0!/00!/00")MO

13、D(MID(C3171)2)*120+600) 中，首先獲取 出生年月，然后判斷性別，如果為男性，則在出生年月的基礎(chǔ)上加上720個(gè)月(60年)，如果為女性，則在出生年月的基礎(chǔ)上加上 600個(gè)月(50年)。暨男同志60歲退休，女同志50 歲退休哦！七、財(cái)會(huì)必備技能：合同到期時(shí)間。目的：根據(jù)實(shí)際情況，計(jì)算合同到期時(shí)間日tJrABCDtF1Excel函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式2No項(xiàng)目等約M問(wèn)"約時(shí)K（年）到期時(shí)間儕汴21項(xiàng)目A2018 年5月 1H4L42項(xiàng)目B2010 年5月 1"6L53項(xiàng)目C2016 隼3 月20113F64頊目口2019年1月1日175項(xiàng)目E

14、2IH5年 10 月 23116Lg6項(xiàng)目F2014 年月 Ml4L97項(xiàng)目G2012 年4月4114LIC重點(diǎn)：=EDATE(C3J)3*12)K方法：在目標(biāo)單元格中輸入公式：=EDATE（C3D3*12）。解讀：Edate函數(shù)中，第二個(gè)參數(shù)為月份，而在簽約時(shí)長(zhǎng)”中的時(shí)間為年份，所以需要*12 （乘以12）。八、財(cái)會(huì)必備技能：?jiǎn)螚l件求和 目的：按地區(qū)”統(tǒng)計(jì)銷量。13*6ACDEIFGH1|1 .Excel函數(shù)公式；財(cái)會(huì)人（4必儕的16個(gè)函數(shù)公式2No姓各性別銷量地區(qū)備注地區(qū)銷錄和31上東男66北京天律42小西9J56上海53小南女67蘇州64小北女56上海75小中r 33天津e6小王女57上

15、海97小李5J20上海1C重點(diǎn)：=SUMIF(E3：E9.H3J)3：D9)方法：在目標(biāo)單元格中輸入公式：=SUMIF（E3:E9H3D3:D9）。解讀：Sumif函數(shù)的作用為：統(tǒng)計(jì)指定范圍內(nèi)符合條件的和值。語(yǔ)法結(jié)構(gòu)為：=Sumif（條件范圍，條件，求和范圍）。當(dāng)條件范圍和求和范圍相同時(shí)，可以省略求和范圍。 九、財(cái)會(huì)必備技能：多條件求和。目的：按地區(qū)”中銷量大于50的銷量和。J3： I 久 , AA I B I C I D E F G HExcel函數(shù)公式：財(cái)會(huì)人憂必箔的16個(gè)函數(shù)公式2No姓各性別銷量地區(qū)備注地區(qū)銷錄銷量和31上東男66北京北京504小西5J56上海53小南女£7蘇

16、州64小北女56上海75小中yjr 33天津S6小王女57上海g7小李5J20上海重點(diǎn)：=SUMIFS(D3:D95E3:E9,H3,D3:D9,n>l,&B)10方法：在目標(biāo)單元格中輸入公式：=SUMIFS(D3:D9E3:E9H3D3:D9">"&I3)。解讀：Sumifs為多條件求和函數(shù)，暨統(tǒng)計(jì)符合多條件的下的和值。語(yǔ)法結(jié)構(gòu)為：=Sumifs(求和范圍,條件1范圍，條件1,條件2范圍，條件2條件N范圍，條件N)。 十、財(cái)會(huì)必備技能：計(jì)算日期所屬季度。D3AW:JrB6CDE1E*rd函數(shù)公式；財(cái)會(huì)人公必備的16個(gè)函數(shù)公式2No項(xiàng)目簽約時(shí)間季

17、度備注31項(xiàng)F1A2018 年5月IIIF42項(xiàng)FIB201(1 年5月 1H/53項(xiàng)目C2016 年3 月 2nHr64項(xiàng)目D2019年1月11175項(xiàng)目E20IS 年 10 月 23H廠e6項(xiàng)目F2014 年*月6 HF97項(xiàng)目G2012 年4 月4II1C重點(diǎn):二LEN季度”方法：在目標(biāo)單元格中輸入公式：=LEN(2AMONTH(C3)&"季度"。十一、財(cái)會(huì)必備技能：特定條件下的最小值。目的：計(jì)算指定 地區(qū)”的最小銷量。13x v, A12ABCDEF G H1|Excd函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式No姓名性別銷量地區(qū)備注地區(qū)最小銷量31上東男66北

18、京上海42小西_ 9J _56上海53小南一女一P 67蘇州64小北女56上海75小I133天津S6小王女57上海97小李5J20上海10重式：=MIN(IF，E3;E9=H3.D3;D9 肌 Ctrl+Sliift+Eiitcr1、在目標(biāo)單元格中輸入公式：=MIN(IF(E3:E9=H3D3:D9)2、Ctrl+Shift+Enter 填充。解讀：由于需要多次判斷，所以采用 Ctrl+Shift+Enter填充。十二、財(cái)會(huì)必備技能：特定條件下的最大值。目的：計(jì)算指定 地區(qū)”的最高銷量。1312ABCDEF G H1|Excd函數(shù)公式：財(cái)會(huì)人員必備的16個(gè)函數(shù)公式No姓名性別銷量地地備注地區(qū)最

19、小銷量31上東男66北京上海42小西_ 9J _56上海53小南一女一P 67蘇州64小北女56上海75小I133天津g6小王女57上海g7小李5J20上海重點(diǎn):=MAX(IF(E3:E9=H3,D3:D9n. CtrkShifVEnter10N 方法:1、在目標(biāo)單元格中輸入公式：=MAX(IF(E3:E9=H3D3:D9)。2、Ctrl+Shift+Enter 填充。解讀：由于需要多次判斷，所以采用 Ctrl+Shift+Enter填充。十三、財(cái)會(huì)必備技能：特定條件下的平均值。目的：按照 地區(qū)”計(jì)算平均銷量。13:為 5 法ABCDEFCH111Excd函數(shù)公式；財(cái)會(huì)人戰(zhàn)必備的16個(gè)函數(shù)公式

20、2No姓名性別銷量地區(qū)備注地區(qū)均銷量31上東男66北京上海42小西_ 9J _56上海53小南一女一P 67蘇州64小北女56上海75小中33天津S6小王女57上海97小李5J20上海重乂1：=AVERAGEIFS(D3：D9,E3;EH3)10在目標(biāo)單元格中輸入公式：=AVERAGEIFS(D3:D9E3:E9H3)。解讀：Averageifs函數(shù)為：對(duì)條件計(jì)算平均值函數(shù)。語(yǔ)法結(jié)構(gòu)為：=Averageifs(設(shè)置范圍，條件1范圍，條件1,條件2范圍，條件2條件N范圍，條件N)。 十四、財(cái)會(huì)必備技能：多條件計(jì)數(shù)。 目的：按 地區(qū)”統(tǒng)計(jì)銷量大于50的筆數(shù)。J3T : XA| A B C D E

21、F G H I . JExcel函數(shù)公式:財(cái)會(huì)人其必備的16個(gè)函數(shù)公式2No姓各性別銷量地區(qū)備注地區(qū)銷最筆數(shù)31上東男66北京北京5042小西男56上海Vs53小南女67蘇州64小北女56上海75小中33天津e6小王女57上海97小李5J20上海重點(diǎn)：二COUNTIFS(E3：E9,H3.D3;D9J>H&I3)10 _方法：在目標(biāo)單元格中輸入公式： =COUNTIFS(E3:E9H3D3:D9">"&I3)。解讀：Countifs函數(shù)為多條件計(jì)數(shù)函數(shù)，語(yǔ)法結(jié)構(gòu)為：=Countifs(條件1范圍，條件1,條件2范圍,條件2條件N范圍，條件N)。十五、財(cái)會(huì)必備技能：多