Excel函數公式:含金量超高的財會人員必備函數公式
財會人員在日常的工作中,用到Excel的地方非常的多,當然用到Excel函數、Excel公式的地方就更多了,如果對於常用的Excel函數都能夠掌握,對於提高工作效率將有很大的幫助。
一、判斷「姓名」等短欄位是否重複。
方法:
1、選定目標單元格。
2、輸入公式:=IF(COUNTIF(B$3:B$9,B3)>1,"重複","")。
3、Ctrl+Enter填充。
二、判斷「身份證號碼」等長欄位是否重複。
方法:
1、選定目標單元格。
2、輸入公式:=IF(COUNTIF(C$3:C$9,C3&"*")>1,"重複","")。
3、Ctrl+Enter填充。
解讀:
對比判斷重複的公式,發現長欄位的多了「&」,原因在於在Excel中只能判斷12位以下的數字,對12位以以後的數字,全部當做0處理。加上「&」強制將數字轉換成了文本。然後進行對比。
三、根據身份證號計算年齡。
方法:
1、選定目標單元格。2、輸入公式:=DATEDIF(TEXT(MID(C3,7,8),"0-00-00"),TODAY(),"y")。
3、Ctrl+Enter填充。
四、根據身份證號計算出生日期。
方法:
1、選定目標單元格。2、輸入公式:=TEXT(MID(C3,7,8),"0-00-00")或=TEXT(MID(C7,7,8),"0!/00!/00")。
3、Ctrl+Enter填充。
五、根據身份證號計算性別。
方法:
1、選定目標單元格。
2、輸入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。
3、Ctrl+Enter填充。
六、根據身份證號計算退休時間(男:60歲,女:50歲)。
方法:
1、選定目標單元格。2、輸入公式:=EDATE(TEXT(MID(C3,7,8),"0!/00!/00"),MOD(MID(C3,17,1),2)*120+600)。
3、Ctrl+Enter填充。
七、計算合同到期時間。
方法:
1、選定目標單元格。2、輸入公式:=EDATE(D3,E3)。
3、Ctrl+Enter填充。
八、條件求和。
方法:
1、在目標單元格中輸入公式:=SUMIF(C3:C9,G3,D3:D9)。
九、多條件求和。
方法:
1、在目標單元格中輸入公式:=SUMIFS(D3:D9,C3:C9,G3,D3:D9,H3)。十、計算日期所屬季度。
方法:
1、選定目標單元格。2、輸入公式:=LEN(2^MONTH(D3))&"季度"。
3、Ctrl+Enter填充。
十一、特定條件下的最小值。
方法:
1、在目標單元格中輸入公式:=MIN(IF(C3:C9=G3,D3:D9))。2、Ctrl+Shift+Enter填充。
十二、指定條件下的最大值。
方法:
1、在目標單元格中輸入公式: =MAX(IF(C3:C9=G3,D3:D9))。
2、Ctrl+Shift+Enter填充。
十三、指定條件下的平均值。
方法:
1、在目標單元格中輸入公式:=AVERAGE(IF(C3:C9=G3,D3:D9))。2、Ctrl+Shift+Enter填充。
十四、多條件計數。
方法:
1、在目標單元格中輸入公式:=COUNTIFS(C3:C9,G3,D3:D9,H3)。2、Ctrl+Enter填充。
十五、多條件求和。
方法:
1、在目標單元格中輸入公式:=SUMPRODUCT((C3:C9=G3)*(D3:D9>H3),D3:D9)。2、Ctrl+Enter填充。
十六、動態查詢數據。
方法:
1、在目標單元格中輸入公式:=VLOOKUP(G3,B3:D9,3,0)。
2、Ctrl+Enter填充。
3、選定數據源-【開始】-【條件格式】-【新建規則】-【使用公式確定要設置的單元格格式】。
4、在【為符合此公式的值設置格式】中輸入:=($g$3=$b3)。
5、單擊【格式】-【填充】,選取需要的顏色,【確定】-【確定】。
推薦閱讀: