1. 查找重復(fù)值
公式：=IF(COUNTIF(A$2:A2,A2)=1,"","重復(fù)")
首選我們利用countif函數(shù)進(jìn)行條件計數(shù)，然后使用if函數(shù)進(jìn)行判斷當(dāng)其結(jié)果等于1時代表不重復(fù)，當(dāng)不等于1時候代表重復(fù)
2. 身份證號碼提取出生日期
公式：=--TEXT(MID(A2,7,8),"0-00-00")
首先利用mid函數(shù)提取出身份證號碼中的出生年月，然后利用text函數(shù)設(shè)置為日期格式，最后利用兩個減號，將文本格式轉(zhuǎn)換為日期格式，為什么還需要轉(zhuǎn)換格式呢？因為text是文本函數(shù)，經(jīng)過text函數(shù)轉(zhuǎn)換過的數(shù)據(jù)都是文本格式
3. 身份證號碼中提取性別
公式：=IF(MOD(MID(A2,17,1),2)=1,"男","女")
性別只與身份證號碼第17位有關(guān)系，當(dāng)?shù)?7位為奇數(shù)則為男，為偶數(shù)則為女，所以我們利用mid函數(shù)提取第17位數(shù)字，然后利用mod函數(shù)判斷奇偶，最后利用if函數(shù)判斷
4. 身份證號碼中提取年齡
公式：=DATEDIF(B2,TODAY(),"y")
DATEDIF函數(shù)是一個隱藏函數(shù)，用于計算兩個日期時間差
5. vlookup進(jìn)行多條件查找
公式：｛=VLOOKUP(E2&F2,IF({1,0},A2:A10&B2:B10,C2:C10),2,0)｝
第一參數(shù)：我們將查找值通過使用&字符合并為一項
第二參數(shù)：我們利用if函數(shù)和數(shù)組構(gòu)建一個二維數(shù)組，如下圖所示
第三參數(shù)：2，在二維數(shù)組中得分在第二列，所以為2，
第四參數(shù)為0，為精確匹配
6. 使用vlookup進(jìn)行反向查找
公式：=VLOOKUP(E2,IF({1,0},C2:C10,A2:A10),2,0)
我們都知道使用vlookup函數(shù)一般是從左往右查找，當(dāng)我們想從右往左查找就要用到vlooup函數(shù)的反向查找，與多條件查找類似，都是構(gòu)建二維數(shù)組進(jìn)行查找
7. 隔行求和
公式：=SUMPRODUCT((MOD(ROW(C2:L7),2)=1)*C2:L7)
首先我們利用mod函數(shù)判斷為奇數(shù)列的行號，然后在利用SUMPRODUCT的數(shù)組特性進(jìn)行求和
8. 隔列求和
公式：=SUMPRODUCT((MOD(COLUMN(B3:G12),2)=1)*B3:G12)
跟隔行求和類似，先判斷奇數(shù)列號，然后進(jìn)行求和
9. 統(tǒng)計不重復(fù)個數(shù)
公式：=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
首先利用countif條件計數(shù)，算出重復(fù)次數(shù)，然后利用1除以重復(fù)數(shù)，這里為什么用1除以呢，比如當(dāng)數(shù)據(jù)出現(xiàn)兩次，countif函數(shù)就會算出兩個2，然后用1分別除以兩個2結(jié)果為1/2最后利用SUMPRODUCT函數(shù)，兩個1/2會相加得到1
10. 中國式排名
公式：=SUMPRODUCT(($B$2:$B$8>B2)*1)+1
使用SUMPRODUCT判斷成績大于它自己本身的數(shù)據(jù)，因為沒有等于所有加1
11，單條件計數(shù)
公式：=SUMPRODUCT((B2:B16=$G$3)*1)
首先判斷部門所在區(qū)域等于成型車間的單元格，當(dāng)正確時返回true可以看做是1，錯誤時返回false可以看做是0，最后在結(jié)果后乘以1
12. 可見單元格求和
公式：=SUBTOTAL(109,B2:B10)
當(dāng)SUBTOTAL的第一參數(shù)為100以上的時候，就會僅對可見區(qū)域求和
13. 雙向查找
公式：=INDEX(A1:E10,MATCH(G2,A1:A10,0),MATCH(H2,A1:E1,0))
利用match函數(shù)分別找到姓名以及科目所在的行列標(biāo)號，然后利用index函數(shù)取出結(jié)果
14. 提取左邊的字符串
公式：=-LOOKUP(1,-LEFT(D2,ROW($1:$30)))
首先我們使用ROW($1:$30)構(gòu)建一個1到30的序列數(shù)組，如果你的數(shù)據(jù)比較長可以適當(dāng)增大，然后利用left函數(shù)對字符串提取30次，我們又在left函數(shù)前面添加了負(fù)號，而又在其前面添加了負(fù)號將提取數(shù)據(jù)轉(zhuǎn)換為數(shù)值當(dāng)提取出來的數(shù)據(jù)為文本是會返回錯誤值，因為使用lookup函數(shù)函數(shù)默認(rèn)是升序排列的所以函數(shù)會返回最后一個正確的值，而最后一個正確值恰恰是我們要提取的值，最后添加負(fù)號將負(fù)數(shù)轉(zhuǎn)換為正數(shù)
15. 提取右邊的數(shù)據(jù)
公式：=-LOOKUP(1,-RIGHT(A2,ROW($1:$30)))
這個跟從左區(qū)域數(shù)據(jù)是一樣的，只不過是將left函數(shù)換成了right函數(shù)
怎么樣，這15個函數(shù)套路你你都知道嗎
我是Excel從零到一，關(guān)注我持續(xù)分享更多excel技巧
你們的點贊關(guān)注和轉(zhuǎn)發(fā)是對我最大的肯定
聯(lián)系客服
