常用Excel函數公式解析

09-07

小夥伴們好啊，今天老祝和大家分享幾個常用公式，並進行簡單的解析，希望對小夥伴有所幫助。

查找重複內容

=IF(COUNTIF(A:A,A2)>1,"

重複

","")

COIUNTIF函數用於統計一個區域中符合條件的單元格個數。

先使用

COIUNTIF函數計算出A列區域中

有多少個

與A2相同的姓名。然後使用IF函數判斷，如果

COIUNTIF函數的結果大於1，就說明有重複了。

重複內容

首次出現時

不提示

=IF(COUNTIF(A$2:A2,A2)>1,"

重複

","")

COUNTIF函數使用一個動態擴展的引用範圍

A$2:A2

，隨著公式向下複製，這個區域會不斷擴展，依次變成

A$2:A3、

A$2:A4、

A$2:A5…

…

，也就是統計自A2單元格開始，到公式所在行這個範圍內，有多少個與A列姓名相同的單元格。

如果是某個姓名首次出現，則

COUNTIF(A$2:A2,A2)

的計算結果等於1，如果是重複出現，則結果必定大於1,

最後再用IF函數判斷，COUNTIF函數的結果是否大於1，如果大於1，就返回指定的內容「重複」。

根據出生年月計算年齡

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

TODAY函數返回系統當前的日期。

DATEDIF函數以A2的出生年月作為開始日期，以系統日期作為結束日期，第三參數使用Y，表示計算兩個日期之間的整年數。

根據身份證號碼提取出生年月

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

先使用MID函數從A2單元格中的第7位開始，提取表示出生年月的8個字元19720516。然後使用TEXT函數將其變成具有日期樣式的文本「1972-05-16」，最後加上兩個負號，也就是計算負數的負數，通過這樣一個數學計算，把文本型的日期變成了真正的日期序列值。

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

根據身份證號碼提取性別

=IF(MOD(MID(A2,17,1),2),"

男

","

女

先使用MID函數，從A2單元格的第17位開始提取1個字元，這個字元就是性別碼。

然後使用MOD函數，計算這個性別碼與2相除的餘數。

如果IF函數的第一個參數等於0，IF函數將其按FALSE處理，返回第三參數指定的內容「女」。如果不等於0，則按TRUE處理，返回第二參數指定的內容「男」。

90分以上的人數

=COUNTIF(B1:B7,">"&D1;)

特別注意，如果COUNTIF的第二參數使用大於、小於或是大於等於、小於等於以及不等於的計算方式時，假如條件是指向某個單元格，就需要用連接符號將比較運算符與單元格地址連接。

本例中，如果第二參數寫成

"，

公式將無法得到正確結果。

各分數段的人數

同時選中

E2:E5

，輸入以下公式，按

Shift+Ctrl+Enter

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

FREQUENCY的作用是計算數值在某個區域內的出現頻率。

第一參數

B2:B7

是數值所在區域，第二參數

{70;80;90}

是用於計算頻率的間隔。

返回的結果比指定間隔數會多出一個，因此本例中需要同時選中四個單元格。

返回的結果分別是小於等於70的個數,71~80的個數,81~90的個數，最後一個是大於90部分的個數。

按條件統計平均值

=AVERAGEIF(B2:B7,"

男

",C2:C7)

AV開頭的這個函數用法與SUMIF函數類似，作用是計算指定條件的平均值。

第一個參數是要要判斷條件的區域，第二參數是指定的條件，第三參數是要計算平均值的區域。

如果第一參數符合指定的條件，就計算與之對應的第三參數的平均值。

多條件統計平均值

=AVERAGEIFS(D2:D7,C2:C7,"

男

",B2:B7,"

銷售

多條件計算

平均值。第一參數是用於計算平均值的區域，後面是成對出現的條件區域1/條件1，條件區域2/條件2……

如果後面的多組

條件

全部符合，就計算對應的第一參數的平均值。

統計不重複的個數

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

公式解釋可參照這裡COUNTIF函數高能應用，老祝就不再贅述了。

好了，今天老祝為大家分享的內容就是這些，祝各位一天好心情！

圖文製作：祝洪忠