12組Excel常用公式,3小時才整理好的,直接套用就行了

天空之城 2022/12/27 檢舉 我要評論

Hello,大家好,很久沒跟大家盤點函數公式了,今天跟大家分享幾組工作中常用的函數公式,大家以后遇到類似的問題直接套用即可,可以快速的提高工作效率,建議大家收藏保存,以免想用的時候找不到了,廢話不多說,讓我直接開始吧!

1.計算退休年齡

公式:=EDATE(C4,IF(B4=「男」,60,55)*12)+1

EDATE函數的作用是返回指定日期之前或者之后的月份

首先我們使用IF函數判斷出性別,然后再根據性別返回對應的退休年齡,退休年齡乘以12會得到總的月份

2.身份證號碼判斷重復

公式:=IF(COUNTIF($B$4:$B$11,B4&「*」)=1,「」,「是」)

因為身份證號碼超過了15位,就不能用常規的方法判斷重復了,在這里關鍵是B4&「*」,它的作用是讓身份證號碼依然保持文本格式進行計數。

如果COUNTIF結果大于1則表示數據重復,然后使用IF函數輸出是,就表示存在重復

3.計算不重復數據個數

公式:=COUNTA(UNIQUE(B4:B12))

UNIQUE它會提取不重復的數據,得到不重復的數據后,我們再使用COUNTA函數來統計下數據的個數即可

4.自動排序

我們需要用到SORT函數,它是一個新函數,作用就是用來排序的

語法:=SORT(排序區域,排序的列數,設置排序方式)

SORT函數會根據第二參數指定的列數進行排序,第三參數用于指定排序的方法

公式:=SORT(A4:B13,2,-1)

5.多條件判斷

公式:=IF(AND(C3>80,D3<2),200,0)

所謂多條件判斷就是存在多個判斷的條件,如下圖,【只有得分大于80分,遲到小于2次獲得200獎金】在這里就是2個條件,它是一個且的關系,我們可以借助and函數來構建條件

6.根據關鍵字求和

公式:=SUMIF(B4:B15,「*車間*」,D4:D15)

如下圖,我們項要求車間總的薪資,這個公式本質上還是一個sumif的單條件求和函數,關鍵是求和的條件【「*車間*」】,星號是一個通配符,表示任意多個字符,在車間的前后各鏈接一個星號就能實現關鍵字求和的效果了

7.計算工齡

公式:=DATEDIF(C4,TODAY(),「Y」)

這個公式就是DATEDIF的常規用法,它的作用就是計算2個日期之間的差值,第三參數為Y就表示計算年份的差值。

8.一對多查詢

公式:=FILTER(B4:B18,A4:A18=D4)

FILTER函數是一個新函數,它的作用是根據條件篩選結果,一對多查詢其實它的本質就是一個篩選問題,FILTER函數的出現完美的解決了這個Excel難題

9.身份證號碼計算性別

公式:=IF(ISEVEN(MID(B4,17,1)),「女」,「男」)

性別只與身份證號碼的第17位有關,奇數則表示為男性,偶數則表示為女性。

在這里我們使用MID函數將第17位提取出來,之后利用ISEVEN函數判斷下數字的奇偶性,最后利用IF函數來輸出結果。

10.身份證號計算年齡

公式:=DATEDIF(TEXT(MID(B4,7,8),「0000-00-00」),TODAY(),「Y」)

這個公式有一些長,它的本質就是一個DATEDIF,用它來計算下2個日期之間的差值,只不過我們需要在身份證號碼中提取下出生日期,TEXT(MID(B4,7,8),「0000-00-00」)這一串函數的作用就是提取身份證號碼中的出生日期。

11.合并同類項

公式:=TEXTJOIN(「,」,TRUE,IF($B$4:$B$18=E8,$C$4:$C$18,「」))

這個公式的主體是一個TEXTJOIN函數,我利用IF函數來找到每個班級對用的姓名,然后再根據分隔符號將它們都連接在一起即可

12.不計算篩選的數據

不計算篩選的數據,一個快捷鍵就能輕松搞定,就是Ctrl+T鍵,把數據轉換為超級后,為表格添加下匯總行,這個時候篩選數據,就會不計算隱藏的區域,僅僅計算篩選的結果了

以上就是今天分享的12組函數,你用過幾個呢?

搶先看最新趣聞請贊下面專頁
用戶評論