1,、查找重復(fù)內(nèi)容

=IF(COUNTIF(A:A,A2)>1,'重復(fù)','')

COIUNTIF函數(shù)用于統(tǒng)計(jì)一個(gè)區(qū)域中符合條件的單元格個(gè)數(shù),。

先使用COIUNTIF函數(shù)計(jì)算出A列區(qū)域中有多少個(gè)與A2相同的姓名。然后使用IF函數(shù)判斷,，如果COIUNTIF函數(shù)的結(jié)果大于1,，就說(shuō)明有重復(fù)了。

2,、重復(fù)內(nèi)容首次出現(xiàn)時(shí)不提示

=IF(COUNTIF(A$2:A2,A2)>1,'重復(fù)','')

COUNTIF函數(shù)使用一個(gè)動(dòng)態(tài)擴(kuò)展的引用范圍A$2:A2,，隨著公式向下復(fù)制，這個(gè)區(qū)域會(huì)不斷擴(kuò)展,，依次變成A$2:A3,、A$2:A4、A$2:A5…… ,，也就是統(tǒng)計(jì)自A2單元格開(kāi)始,，到公式所在行這個(gè)范圍內(nèi)，有多少個(gè)與A列姓名相同的單元格,。

如果是某個(gè)姓名首次出現(xiàn),，則COUNTIF(A$2:A2,A2)的計(jì)算結(jié)果等于1，如果是重復(fù)出現(xiàn),，則結(jié)果必定大于1, 

最后再用IF函數(shù)判斷,，COUNTIF函數(shù)的結(jié)果是否大于1，如果大于1,，就返回指定的內(nèi)容“重復(fù)”,。

3,、根據(jù)出生年月計(jì)算年齡

=DATEDIF(A2,TODAY(),'y')

TODAY函數(shù)返回系統(tǒng)當(dāng)前的日期。

DATEDIF函數(shù)以A2的出生年月作為開(kāi)始日期,，以系統(tǒng)日期作為結(jié)束日期,，第三參數(shù)使用Y，表示計(jì)算兩個(gè)日期之間的整年數(shù),。

4,、根據(jù)身份證號(hào)提取出生年月

=--TEXT(MID(A2,7,8),'0-00-00')

先使用MID函數(shù)從A2單元格中的第7位開(kāi)始，提取表示出生年月的8個(gè)字符19720516,。然后使用TEXT函數(shù)將其變成具有日期樣式的文本“1972-05-16”,，最后加上兩個(gè)負(fù)號(hào)，也就是計(jì)算負(fù)數(shù)的負(fù)數(shù),，通過(guò)這樣一個(gè)數(shù)學(xué)計(jì)算,，把文本型的日期變成了真正的日期序列值。

如果單元格中顯示的是五位數(shù)值,，只要設(shè)置成日期格式就好,。

5、根據(jù)身份證號(hào)碼提取性別

=IF(MOD(MID(A2,17,1),2),'男','女')

先使用MID函數(shù),，從A2單元格的第17位開(kāi)始提取1個(gè)字符,，這個(gè)字符就是性別碼。

然后使用MOD函數(shù),，計(jì)算這個(gè)性別碼與2相除的余數(shù),。

如果IF函數(shù)的第一個(gè)參數(shù)等于0，IF函數(shù)將其按FALSE處理,，返回第三參數(shù)指定的內(nèi)容“女”。如果不等于0,，則按TRUE處理,，返回第二參數(shù)指定的內(nèi)容“男”。

6,、計(jì)算90分以上的人數(shù)

=COUNTIF(B1:B7,'>'&D1)

特別注意,，如果COUNTIF的第二參數(shù)使用大于、小于或是大于等于,、小于等于以及不等于的計(jì)算方式時(shí),，假如條件是指向某個(gè)單元格，就需要用連接符號(hào)將比較運(yùn)算符與單元格地址連接,。

本例中,，如果第二參數(shù)寫(xiě)成'>D1'，公式將無(wú)法得到正確結(jié)果,。

7,、統(tǒng)計(jì)各分?jǐn)?shù)段的人數(shù)

同時(shí)選中E2:E5，輸入以下公式，按Shift+Ctrl+Enter

=FREQUENCY(B2:B7,{70;80;90})

FREQUENCY的作用是計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率,。

第一參數(shù)B2:B7是數(shù)值所在區(qū)域,，第二參數(shù){70;80;90}是用于計(jì)算頻率的間隔。

返回的結(jié)果比指定間隔數(shù)會(huì)多出一個(gè),，因此本例中需要同時(shí)選中四個(gè)單元格,。

返回的結(jié)果分別是小于等于70的個(gè)數(shù),71~80的個(gè)數(shù),81~90的個(gè)數(shù)，最后一個(gè)是大于90部分的個(gè)數(shù),。

8,、按條件統(tǒng)計(jì)平均值

=AVERAGEIF(B2:B7,'男',C2:C7)

AV開(kāi)頭的這個(gè)函數(shù)用法與SUMIF函數(shù)類似，作用是計(jì)算指定條件的平均值,。

第一個(gè)參數(shù)是要要判斷條件的區(qū)域,，第二參數(shù)是指定的條件，第三參數(shù)是要計(jì)算平均值的區(qū)域,。

如果第一參數(shù)符合指定的條件,，就計(jì)算與之對(duì)應(yīng)的第三參數(shù)的平均值。

9,、多條件統(tǒng)計(jì)平均值

=AVERAGEIFS(D2:D7,C2:C7,'男',B2:B7,'銷售')

多條件計(jì)算平均值,。第一參數(shù)是用于計(jì)算平均值的區(qū)域，后面是成對(duì)出現(xiàn)的條件區(qū)域1/條件1,，條件區(qū)域2/條件2……

如果后面的多組條件全部符合,，就計(jì)算對(duì)應(yīng)的第一參數(shù)的平均值。

10,、統(tǒng)計(jì)不重復(fù)人數(shù)

=SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))

該公式中包含了一個(gè)數(shù)學(xué)邏輯：

任意一個(gè)數(shù)據(jù)重復(fù)出現(xiàn)N次,，N個(gè)1/N相加，結(jié)果就是為1,。

公式中“COUNTIF(A2:A9,A2:A9)”部分,，分別統(tǒng)計(jì)A2:A9單元格區(qū)域中每個(gè)元素出現(xiàn)的次數(shù)。

運(yùn)算過(guò)程相當(dāng)于：

=COUNTIF(A2:A9,A2)

=COUNTIF(A2:A9,A3)

……

=COUNTIF(A2:A9,A9)

返回內(nèi)存數(shù)組結(jié)果為：

{1;1;2;2;1;1;2;2}

再使用1除以這個(gè)內(nèi)存數(shù)組,，得到以下結(jié)果：

{1;1;0.5;0.5;1;1;0.5;0.5}

用1除,，即相當(dāng)于計(jì)算COUNTIF函數(shù)所返回內(nèi)存數(shù)組的倒數(shù)。

為便于理解,，把這一步的結(jié)果中的小數(shù)部分使用分?jǐn)?shù)代替,，結(jié)果為：

{1;1;1/2;1/2;1;1;1/2;1/2}

如果單元格的值在區(qū)域中是唯一值，這一步的結(jié)果是1,。

如果重復(fù)出現(xiàn)兩次,，這一步的結(jié)果就有兩個(gè)1/2。

如果單元格的值在區(qū)域中重復(fù)出現(xiàn)3次,，結(jié)果就有3個(gè)1/3…

即每個(gè)元素對(duì)應(yīng)的倒數(shù)合計(jì)起來(lái)結(jié)果仍是1,。

最后用SUMPRODUCT函數(shù)求和,，得出不重復(fù)的人員總數(shù)。