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