函數(shù)和公式，是Excel中的靈魂，也是非常實(shí)用的技巧，如果你對(duì)常用的函數(shù)公式能夠熟練地掌握，對(duì)于提高工作效率將會(huì)有很大的幫助！
一、“重復(fù)”類。
1、查找重復(fù)內(nèi)容，重復(fù)即提示。
方法：
在目標(biāo)單元格中輸入公式：=IF(COUNTIF(E:E,E3)>1,'重復(fù)','')。
解讀：
先用Countif函數(shù)統(tǒng)計(jì)當(dāng)前單元格的值在E列中的數(shù)量，然后用If函數(shù)進(jìn)行判斷，如果大于1，返回“重復(fù)”，否則返回空值。
2、重復(fù)內(nèi)容首次出現(xiàn)時(shí)不予提示。
方法：
在目標(biāo)單元格中輸入公式：=IF(COUNTIF(E$3:E3,E3)>1,'重復(fù)','')。
解讀：
將本示例中的公式和上一示例中的公式進(jìn)行對(duì)比，結(jié)果只是參數(shù)做了修改，將原來的范圍E:E變?yōu)镋$3：E3，而統(tǒng)計(jì)結(jié)果卻不相同，其實(shí)范圍E:E是絕對(duì)引用，暨統(tǒng)計(jì)范圍為E列的所有單元格，而為了達(dá)到“重復(fù)內(nèi)容”首次出現(xiàn)時(shí)不予提示的目的，我們將統(tǒng)計(jì)范圍定位從開始單元格到當(dāng)前單元格，所以，重復(fù)內(nèi)容首次出現(xiàn)時(shí)不予提示。
3、重復(fù)內(nèi)容首次出現(xiàn)時(shí)提示重復(fù)。
方法：
在目標(biāo)單元格中輸入公式：=IF(COUNTIF(E3:E99,E3)>1,'重復(fù)','')。
解讀：
1、從公示的計(jì)算過程請(qǐng)參閱示例2的解讀。
2、此公式在應(yīng)用時(shí)要區(qū)分對(duì)待，如果重復(fù)內(nèi)容進(jìn)出線一次，則重復(fù)內(nèi)容僅在首次出現(xiàn)時(shí)提示，如果有多次重復(fù)，僅最后一次不提示重復(fù)。
二、身份證號(hào)類。
1、根據(jù)出生年月計(jì)算年齡。
方法：
在目標(biāo)單元格中輸入公式：=DATEDIF(E3,TODAY(),'y')。
解讀：
Datedif函數(shù)為系統(tǒng)隱藏函數(shù)，其主要作用為按指定的統(tǒng)計(jì)方式統(tǒng)計(jì)兩個(gè)日期之間相差的值。其中“y”的意思為按年統(tǒng)計(jì)。
2、根據(jù)身份證號(hào)碼提取出生年月。
方法：
在目標(biāo)單元格中輸入公式：=TEXT(MID(D3,7,8),'00-00-00')。
解讀：
1、首先利用Mid函數(shù)提取D3字符串中從第7位開始，長度為8的字符。
2、利用Text函數(shù)將其轉(zhuǎn)換為“00-00-00”的格式。
3、根據(jù)身份證號(hào)碼判斷性別。
方法：
在目標(biāo)單元格中輸入公式：=IF(MOD(MID(D3,17,1),2),'男','女')。
解讀：
1、首先用Mid函數(shù)提取D3字符串中從17位開始，長度為1，暨第17位的數(shù)字。
2、利用Mod函數(shù)求余。
3、利用If函數(shù)判斷，余數(shù)為1，暨第17位的數(shù)為奇數(shù)，則返回“男”、如果余數(shù)為0，暨第17位的數(shù)為偶數(shù)，則返回“女”。
三、常用匯總函數(shù)。
方法：
在對(duì)應(yīng)的目標(biāo)單元格中輸入公式：=SUM(D:D)、=MAX(D:D)、=MIN(D:D)、=AVERAGE(D:D)、=COUNTA(B3:B9)。
四、成績排名。
1、美式排名。
方法：
在目標(biāo)單元格中輸入公式：=RANK.EQ(D3,D:D)。
解讀：
從排序的結(jié)果來看，出現(xiàn)了“跳躍”的情況，如果你對(duì)這一結(jié)果不滿意，請(qǐng)繼續(xù)往下閱讀。
2、中國式排名。
方法：
在目標(biāo)單元格中輸入公式：=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9)) 1。
解讀：
從排序的結(jié)果中我們可以看出，名次沒有“跳躍”的情況，是不是更符合我們的實(shí)際需要了？
五、個(gè)數(shù)類統(tǒng)計(jì)。
1、單條件計(jì)數(shù)。
方法：
在目標(biāo)單元格中輸入公式：=COUNTIF(D3:D9,'>='&G3)。
解讀：
Countif函數(shù)為單條件計(jì)數(shù)函數(shù)，其語法結(jié)構(gòu)為=Countif（統(tǒng)計(jì)范圍，統(tǒng)計(jì)條件）。
2、連續(xù)范圍計(jì)數(shù)。
方法：
1、在目標(biāo)單元格中輸入公式：=FREQUENCY(D3:D9,G3:G6)。
2、Ctrl Shift Enter填充。
解讀：
1、函數(shù)Frequency的作用為：以一列垂直數(shù)組返回一組數(shù)據(jù)的頻率分布。公式中D3:D9為數(shù)據(jù)，G3:G6為垂直數(shù)組。
2、因?yàn)橐h(huán)多次計(jì)算，所以填充時(shí)用Ctrl Shift Enter，而不是常規(guī)的Ctrl Enter哦！
3、結(jié)果解讀：30分以下的個(gè)數(shù)為0；31-40的個(gè)數(shù)為1；41-50的個(gè)數(shù)為：0；51-60的個(gè)數(shù)為：3；61-70的個(gè)數(shù)為：3人。
3、單條件統(tǒng)計(jì)平均值。
方法：
在目標(biāo)單元格中輸入公式：=AVERAGEIF(C3:C9,G3,D3:G9)。
解讀：
函數(shù)Averageif為按條件計(jì)算平均值函數(shù)，語法結(jié)構(gòu)為：=Averageif(條件范圍，條件，數(shù)值范圍）。
4、多條件統(tǒng)計(jì)平均值。
方法：
在目標(biāo)單元格中輸入公式：=AVERAGEIFS(D3:D9,C3:C9,G3,D3:D9,'>='&H3)。
解讀：
Averageif是函數(shù)為多條件計(jì)算平均值，其語法結(jié)構(gòu)為：=Averageifs(數(shù)值范圍，條件1范圍，條件1，條件2范圍，條件2……條件N范圍，條件N)。
六、統(tǒng)計(jì)不重復(fù)值。
方法：
在目標(biāo)單元格中輸入公式：=SUMPRODUCT(1/COUNTIF(E3:E9,E3:E9))。
七、提取不重復(fù)的內(nèi)容。
方法：
1、在目標(biāo)單元格中輸入公式：=INDEX(E:E,2 MATCH(,COUNTIF(F$2:F2,E$3:E$9),))&''。
2、用Ctrl Shift Enter填充。
3、拖動(dòng)填充柄填充其他單元格。
結(jié)束語：
本文針對(duì)不同的情況，對(duì)常用的函數(shù)公式進(jìn)行了羅列，對(duì)簡單的用法也進(jìn)行了講解和示范，如果能夠熟練掌握，對(duì)于工作效率的提高絕對(duì)不是一點(diǎn)點(diǎn)哦！
學(xué)習(xí)過程中如果有不同的見解，歡迎大家在留言區(qū)留言討論哦！
聯(lián)系客服
