1.多條件判斷(同時滿足)
在這裡要求出勤天數大於等於24天,考核得分大於等於85分同時滿足才能獲得500獎金,否則的話沒有獎金,公式為:=IF(AND(B3>=24,C3>=85),500,0)
在這裡將and函式作為了if函式的第一引數,and函式的結果是邏輯值,只有條件全部正確才會返回true,否則的話就會返回false
2.多條件判定(滿足一個即可)
如果我們將上述條件更改為出勤天數大於等於24天,考核得分大於等於85分滿足任意一個即可獲得500獎金,公式為:=IF(OR(B3>=24,C3>=85),500,0)
OR函式他的結果也是一個邏輯值,不同的是OR函式的特點是隻要有一個條件是正確的他就會返回true這個結果,只有條件全部是錯的才會返回false這個結果
3.身份證號碼提取出生日期
公式:=TEXT(MID(B3,7,8),"0-00-00")
在這裡我們是使用mid函式提取身份證號碼中出生年月的位置,然後再利用text函式來設定數字的顯示格式。如果你需要使用這個公式,只需要將B3更改為你需要單元格位置即可
4.身份證號碼計算性別
公式:=IF(MOD(MID(B3,17,1),2),"男","女")
身份證號碼的第十七位是性別程式碼,偶數為女性,奇數為男性,在這裡先利用mid函式將第十七位提取出來,然後利用mod函式判斷奇偶性,最後利用if函式輸出結果,如果你需要使用這個函式,只需要將B3單元格更改為你需要的單元格位置即可
5.身份證號碼計算年齡
公式:=DATEDIF(TEXT(MID(B3,7,8),"0-00-00"),TODAY(),"Y")
Datedif函式的作用計算兩個日期的差值,在這裡我們先利用TEXT(MID(B3,7,8),"0-00-00"))將出生日期提取出來將其作為第一引數開始的時間,利用today函式來獲取當天的日期作為第二引數,第三引數為y表示計算型別為年,如果需要套用公式,只需更改b3單元格即可
6.身份證號碼查詢重複
公式:=IF(B3="","",IF(COUNTIF($B$3:$B$9,B3&"*")>1,"重複",""))
在這裡如果身份證號碼不重複,函式會返回空值,如果重複的話就會返回重複兩個字,在這裡需要注意的是countif函式中的資料區域一定要進行絕對引用
7.統計不重複的個數
公式:=SUMPRODUCT(1/COUNTIF(A3:A13,A3:A13))
首先函式會利用countif函式計算出重複的個數,然後再用1除以這個結果,最後利用sumproduct函式求和,比如一個數據重複3次,那麼countif就會得到三個3,然後用1除以countif就會得到三個1/3,相加的結果就是1,想要使用函式只需要將A3:A13替換為你需要的資料區域即可
8.分離單位與數字
公式:=LEFT(C3,LEN(C3)-4)
在這裡C3是單元格的位置,4就是單位(kg/元)所佔的字元數個數
9.條件求和
比如在這裡想要求一下魯班總和的資料,公式:=SUMIF(A3:A13,D4,B3:B13)
A3:A13是條件求和判斷的資料區域,D4是求和條件,B3:B13是條件求和的區域
10.條件計數
比如在這裡想要魯班姓名出現的次數,公式:=COUNTIF(A3:A13,D4)
A3:A13是條件計數用於判斷的資料區域,D4是條件計數條件
11條件求最大值最小值
最大值公式:=MAXIFS(B3:B12,A3:A12,D4)
最小值公式: =MINIFS(B3:B12,A3:A12,D4)
這兩個函式的引數都是一樣的,第一引數:B3:B12要確定最大值的單元格,第二引數:A3:A12用於判斷的資料區域,第三引數:D4判斷的條件
12.設定手機號保護
公式:=SUBSTITUTE(B3,MID(B3,4,4),"****")
Subtitute這個函式的作用是將字串的某一部分替換為另一部分,在這裡我們利用mid函式將手機號碼中的一部分資料提取資料,然後再替換為四個星號
1.多條件判斷(同時滿足)
在這裡要求出勤天數大於等於24天,考核得分大於等於85分同時滿足才能獲得500獎金,否則的話沒有獎金,公式為:=IF(AND(B3>=24,C3>=85),500,0)
在這裡將and函式作為了if函式的第一引數,and函式的結果是邏輯值,只有條件全部正確才會返回true,否則的話就會返回false
2.多條件判定(滿足一個即可)
如果我們將上述條件更改為出勤天數大於等於24天,考核得分大於等於85分滿足任意一個即可獲得500獎金,公式為:=IF(OR(B3>=24,C3>=85),500,0)
OR函式他的結果也是一個邏輯值,不同的是OR函式的特點是隻要有一個條件是正確的他就會返回true這個結果,只有條件全部是錯的才會返回false這個結果
3.身份證號碼提取出生日期
公式:=TEXT(MID(B3,7,8),"0-00-00")
在這裡我們是使用mid函式提取身份證號碼中出生年月的位置,然後再利用text函式來設定數字的顯示格式。如果你需要使用這個公式,只需要將B3更改為你需要單元格位置即可
4.身份證號碼計算性別
公式:=IF(MOD(MID(B3,17,1),2),"男","女")
身份證號碼的第十七位是性別程式碼,偶數為女性,奇數為男性,在這裡先利用mid函式將第十七位提取出來,然後利用mod函式判斷奇偶性,最後利用if函式輸出結果,如果你需要使用這個函式,只需要將B3單元格更改為你需要的單元格位置即可
5.身份證號碼計算年齡
公式:=DATEDIF(TEXT(MID(B3,7,8),"0-00-00"),TODAY(),"Y")
Datedif函式的作用計算兩個日期的差值,在這裡我們先利用TEXT(MID(B3,7,8),"0-00-00"))將出生日期提取出來將其作為第一引數開始的時間,利用today函式來獲取當天的日期作為第二引數,第三引數為y表示計算型別為年,如果需要套用公式,只需更改b3單元格即可
6.身份證號碼查詢重複
公式:=IF(B3="","",IF(COUNTIF($B$3:$B$9,B3&"*")>1,"重複",""))
在這裡如果身份證號碼不重複,函式會返回空值,如果重複的話就會返回重複兩個字,在這裡需要注意的是countif函式中的資料區域一定要進行絕對引用
7.統計不重複的個數
公式:=SUMPRODUCT(1/COUNTIF(A3:A13,A3:A13))
首先函式會利用countif函式計算出重複的個數,然後再用1除以這個結果,最後利用sumproduct函式求和,比如一個數據重複3次,那麼countif就會得到三個3,然後用1除以countif就會得到三個1/3,相加的結果就是1,想要使用函式只需要將A3:A13替換為你需要的資料區域即可
8.分離單位與數字
公式:=LEFT(C3,LEN(C3)-4)
在這裡C3是單元格的位置,4就是單位(kg/元)所佔的字元數個數
9.條件求和
比如在這裡想要求一下魯班總和的資料,公式:=SUMIF(A3:A13,D4,B3:B13)
A3:A13是條件求和判斷的資料區域,D4是求和條件,B3:B13是條件求和的區域
10.條件計數
比如在這裡想要魯班姓名出現的次數,公式:=COUNTIF(A3:A13,D4)
A3:A13是條件計數用於判斷的資料區域,D4是條件計數條件
11條件求最大值最小值
最大值公式:=MAXIFS(B3:B12,A3:A12,D4)
最小值公式: =MINIFS(B3:B12,A3:A12,D4)
這兩個函式的引數都是一樣的,第一引數:B3:B12要確定最大值的單元格,第二引數:A3:A12用於判斷的資料區域,第三引數:D4判斷的條件
12.設定手機號保護
公式:=SUBSTITUTE(B3,MID(B3,4,4),"****")
Subtitute這個函式的作用是將字串的某一部分替換為另一部分,在這裡我們利用mid函式將手機號碼中的一部分資料提取資料,然後再替換為四個星號