|
函數(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ù)做了修改，將原來(lái)的范圍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ì)范圍定位從開(kāi)始單元格到當(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ì)算過(guò)程請(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位開(kāi)始，長(zhǎng)度為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位開(kāi)始，長(zhǎng)度為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)。 四、成績(jī)排名。 1、美式排名。 方法： 在目標(biāo)單元格中輸入公式：=RANK.EQ(D3,D:D)。 解讀： 從排序的結(jié)果來(lái)看，出現(xiàn)了“跳躍”的情況，如果你對(duì)這一結(jié)果不滿意，請(qǐng)繼續(xù)往下閱讀。 2、中國(guó)式排名。 方法： 在目標(biāo)單元格中輸入公式：=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9)) 1。 解讀： 從排序的結(jié)果中我們可以看出，名次沒(méi)有“跳躍”的情況，是不是更符合我們的實(shí)際需要了？ 五、個(gè)數(shù)類統(tǒng)計(jì)。 1、單條件計(jì)數(shù)。 方法： 在目標(biāo)單元格中輸入公式：=COUNTIF(D3:D9,'>='&G3)。 解讀： Countif函數(shù)為單條件計(jì)數(shù)函數(shù)，其語(yǔ)法結(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ù)，語(yǔ)法結(jié)構(gòu)為：=Averageif(條件范圍，條件，數(shù)值范圍）。 4、多條件統(tǒng)計(jì)平均值。
方法： 在目標(biāo)單元格中輸入公式：=AVERAGEIFS(D3:D9,C3:C9,G3,D3:D9,'>='&H3)。 解讀： Averageif是函數(shù)為多條件計(jì)算平均值，其語(yǔ)法結(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é)束語(yǔ)： 本文針對(duì)不同的情況，對(duì)常用的函數(shù)公式進(jìn)行了羅列，對(duì)簡(jiǎn)單的用法也進(jìn)行了講解和示范，如果能夠熟練掌握，對(duì)于工作效率的提高絕對(duì)不是一點(diǎn)點(diǎn)哦！ 學(xué)習(xí)過(guò)程中如果有不同的見(jiàn)解，歡迎大家在留言區(qū)留言討論哦！ |
|
|
