要成功學習Excel,就離不開函數公式的學習,當你與這幾個函數公式成功牽手後,就會發現,原來Excel也很有趣。
一、If+Countif:判斷指定的內容是否重複。
目的:判斷「員工姓名」是否重複!
方法:
在目標單元格中輸入公式:=IF(COUNTIF(B$3:B$12,B3)>1,"重複","")。
解讀:
判斷內容是否重複其實就是統計指定範圍內指定值的個數是否>1,如果>1,則通過If函數返回指定的值即可。
二、If+Countif:重複值首次出現時提示。
目的:判斷「員工姓名」是否重複,重複值首次出現時進行提示。
方法:
在目標單元格中輸入公式:=IF(COUNTIF(B$3:B3,B3)>1,"重複","")。
解讀:
與上一個示例中的公式進行對比,此公式唯一變化的地方就是Countif函數的參數,將參數範圍B$3:B$12變為了B$3:B3,其變化的本質就是上個示例中,統計指定值在全部範圍內的個數,而此示例中統計的是指定值在當前以上範圍內的個數,當指的值第一次出現時,其個數並不>1;再次出現時,>1,則予以提示。
三、Datedif:以指定的方式統計時間差。
目的:根據「出生年月」計算年齡。
方法:
在目標單元格中輸入公式:=DATEDIF(C3,TODAY(),"y")&"歲"。
解讀:
1、Dateif函數的語法結構為:=Datedif(開始日期,結束日期,統計方式),常用的統計方式有「Y」、「M」、「D」,即「年」、「月」、「日」。
2、第二個參數用Today()函數,而不用固定的日期,其原因在於保持年齡的自動更新。
四、TEXT+Mid:提取指定的值並進行格式設置。
目的:從身份證號碼中提取出生年月。
方法:
在目標單元格中輸入公式:=TEXT(MID(C3,7,8),"00-00-00")。
解讀:
用Mid函數提取指定位置的值後,用Text函數將其設置為日期格式。
五、If+Mod+Mid:提取指定的值,並對其進行屬性判斷。
目的:根據身份證號碼判斷性別。
方法:
在目標單元格中輸入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。
解讀:
用Mid函數提取身份證號碼中的第17位,然後判斷奇偶性,如果為奇數,則返回「男」,如果為偶數,則返回女。
六、Sumif:單條件求和。
目的:忽略錯誤值求和。
方法:
在目標單元格中輸入公式:=SUMIF(G3:G12,"<9e307")。
解讀:
「9e307」為Excel中最大的數值,小於此值的數值都在統計範圍內,所以就巧妙的忽略了錯誤值。
七、OFFSET+COUNTA:動態擴展。
目的:在指定的單元格區域中動態顯示內容。
方法:
1、選定目標單元格區域,即J3單元格。
2、單擊【數據】菜單中【數據工具】組中的【數據驗證】,選擇【允許】中的【序列】,在【來源】文本框中輸入公式:=OFFSET($H$3,0,0,COUNTA(H$3:H$12))並單擊右下角的【確定】。
3、在H3:H12單元格區域中輸入、刪除或編輯指定內容。
解讀:
Offset函數,以指定的應用為參照系,通過給定偏移量返回新的應用。
最美尾巴:
文中主要介紹了7個函數公式,如能成功牽手,你就會發現,原來Excel也很有趣,不僅可以判斷重複值的情況,還可以提取出生年月,計算年齡,判斷性別,並且可以忽略錯誤值求和,動態生成下拉菜單。如能熟練掌握,將會如虎添翼哦!