小夥伴們好啊,今天咱們一起學習幾個常用函式公式的用法,點滴積累,也能提高工作效率。
1、查詢重複內容
如下圖所示,要判斷A列姓名是否重複出現過,B2輸入公式:
=IF(COUNTIF(A:A,A2)>1,“重複”,“”)
COIUNTIF函式用於統計一個區域中符合條件的單元格個數。
先使用COIUNTIF函式計算出A列區域中有多少個與A2相同的姓名。然後使用IF函式判斷,如果COIUNTIF函式的結果大於1,就說明有重複了。
2、重複內容首次出現時不提示
如下圖所示,要判斷A列姓名是不是重複出現,B2輸入以下公式:
=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,就返回指定的內容“重複”。
3、根據出生年月計算年齡
如下圖所示,需要根據B列出生年月計算年齡,C2單元格輸入以下公式:
=DATEDIF(B2,TODAY(),“y”)
TODAY函式返回系統當前的日期。
DATEDIF函式以B2的出生年月作為開始日期,以系統日期作為結束日期,第三引數使用“Y”,表示計算兩個日期之間的整年數。
4、根據身份證號提取出生年月
如下圖所示,要根據B列的身份證號碼,來提取出生年齡,C2單元格公式為:
=——TEXT(MID(B2,7,8),“0-00-00”)
先使用MID函式從B2單元格中的第7位開始,提取表示出生年月的8個字元19850216。然後使用TEXT函式將其變成具有日期樣式的文字“1985-02-16”,最後加上兩個負號,也就是計算負數的負數,透過這樣一個數學計算,把文字型的日期變成了真正的日期序列值。
如果單元格中顯示的是五位數值,只要設定成日期格式就好。
5、根據身份證號碼提取性別
如下圖所示,要根據B列的身份證號碼,判斷性別。D2單元格公式為:
=IF(MOD(MID(B2,17,1),2),“男”,“女”)
先使用MID函式,從B2單元格的第17位開始提取1個字元,這個字元就是性別碼。
然後使用MOD函式,計算這個性別碼與2相除的餘數。
如果IF函式的第一個引數等於0,IF函式將其按FALSE處理,返回第三引數指定的內容“女”。如果不等於0,則按TRUE處理,返回第二引數指定的內容“男”。
6、填充合併單元格
如下圖所示,B列姓名使用了合併單元格,使用以下公式可以得到完整的填充:
=LOOKUP(“做”,B$2:B2)
7、忽略錯誤值求和
如下圖所示,C列資料區域中包含有錯誤值,如何進行求和呢?
E2單元格公式為:
=SUMIF(C:C,“<9e307”)
SUMIF函式的求和引數省略,表示以條件區域C:C作為求和區域。
求和條件是<9e307,相當於對所有數值求和。
8、動態擴充套件的下拉選單
如下圖所示,要根據A列的對照表,在D列生成下拉選單,要求能隨著A列資料的增減,下拉選單中的內容也會自動調整。
選中要輸入內容的D2:D10單元格區域,資料→資料驗證→序列,輸入以下公式。
=OFFSET($A$2,0,0,COUNTA($A:$A)-1)
公式表示以A2作為基點,向下偏移0行,向右偏移0列,新引用的行數為COUNTA函式統計到的A列非空單元格個數,結果-1,是因為A1是表頭,計數要去掉。
這樣就是A列有多少個非空單元格,下拉選單中就顯示多少行。
好了,今天為大家分享的內容就是這些,祝各位一天好心情!
圖文製作:祝洪忠