|
公式：=--TEXT(MID(A2,7,8),'0-00-00')使用方法：復(fù)制公式將A2更改為你表中的單元格位置即可原理概述：首先利用mid函數(shù)將身份證號(hào)碼中的出生日期提取出來(lái)，隨后利用text函數(shù)將提取出來(lái)的字符串設(shè)置為日期的顯示樣式，最后利用2個(gè)減號(hào)將其設(shè)置為常規(guī)格式。為什么要使用減號(hào)呢？這是因?yàn)閠ext函數(shù)是文本函數(shù)，它的結(jié)果也是文本格式，文本格式下的日期是不能使用函數(shù)計(jì)算的，所以需要轉(zhuǎn)換格式公式：=DATEDIF(TEXT(MID(A2,7,8),'0-00-00'),TODAY(),'y')使用方法：復(fù)制公式后，只需更改A2為表格中對(duì)應(yīng)的單元格即可原理概述：首先利用TEXT(MID(A2,7,8),'0-00-00')來(lái)提取出出生日期，隨后利用DATEDIF來(lái)計(jì)算出生日期與今天相差了多少年，TODAY()函數(shù)的作用是獲取今天的日期公式：=IF(MOD(MID(A2,17,1),2)=1,'男','女')使用方法：復(fù)制公式，更改A2為對(duì)應(yīng)的單元格位置即可原理概述：身份證號(hào)碼第17位是性別代碼，偶數(shù)為女性，奇數(shù)為男性。首先使用mid函數(shù)將17位的數(shù)字提取出來(lái)，隨后利用mod函數(shù)判斷其奇偶性，最后利用if判斷返回對(duì)應(yīng)的結(jié)果公式：=LOOKUP(1,0/((A2:A8=E2)*(B2:B8=F2)),C2:C8)使用方法：復(fù)制公式更改為對(duì)應(yīng)的單元格區(qū)域即可，格式為：=LOOKUP(1,0/(條件1*條件2*條件3),需要返回的結(jié)果列)原理概述：這是一個(gè)數(shù)組公式，講解起來(lái)比較復(fù)雜，主要是利用了邏輯值相乘，可以把true看做是1，false看著是0，兩兩相乘，結(jié)果為1對(duì)應(yīng)的就是我們需要查找的數(shù)據(jù) 五、通過(guò)關(guān)鍵字查找數(shù)據(jù) 公式：=VLOOKUP('*'&D2&'*',$A$1:$B$8,1,FALSE)使用方法：D2需要更改為表格中對(duì)應(yīng)的查找值，A1:B8需要更改表格中對(duì)應(yīng)的查找區(qū)域并絕對(duì)引用原理概述：*號(hào)是一個(gè)通配符，他表示任意多個(gè)字符，我們將查找值的前后分別連接兩個(gè)星號(hào)，這樣的話就可以實(shí)現(xiàn)通過(guò)關(guān)鍵字查找數(shù)據(jù)公式：=INDEX(A1:A10,MATCH(E2,B1:B10,0))使用方法：復(fù)制公式替換為自己表中的單元格數(shù)據(jù)即可，A1:A10是需要返回的結(jié)果列，E2是查找值，B1:B10是查找的數(shù)據(jù)區(qū)域原理概括：所謂的反向查找就是找到查找值左側(cè)的數(shù)據(jù)，利用vlookup是非常復(fù)雜的，但是利用index+match卻非常的簡(jiǎn)單，首先用match函數(shù)查找數(shù)據(jù)位置，隨后利用index函數(shù)返回對(duì)應(yīng)的結(jié)果即可七、統(tǒng)計(jì)重復(fù)數(shù)據(jù) 公式：=IF(COUNTIF($A$2:$A$10,A2)=1,'','是')使用方法：更改A2:A10與A2為你表中對(duì)應(yīng)的單元格位置即可，A2:A10是統(tǒng)計(jì)的數(shù)據(jù)區(qū)域，需要進(jìn)行絕對(duì)引用，A2是第一個(gè)數(shù)據(jù)原理概述：主要是利用countif進(jìn)行條件計(jì)數(shù)，如果數(shù)據(jù)不存在重復(fù)，他的計(jì)數(shù)結(jié)果就是1，否則的話結(jié)果就大于1，最后利用if函數(shù)做一個(gè)判斷即可八、統(tǒng)計(jì)不重復(fù)的個(gè)數(shù) 公式：=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))使用方法：復(fù)制公式后，只需要更改COUNTIF函數(shù)的第一第二參數(shù)為需要統(tǒng)計(jì)的數(shù)據(jù)區(qū)域即可原理概述：首先利用countif函數(shù)計(jì)算重復(fù)值，隨后用1除以這個(gè)結(jié)果，最后利用sumproduct求和，比如一個(gè)值重復(fù)出現(xiàn)3次，就會(huì)得到3個(gè)1/3，它們相加后結(jié)果是1公式：=LEN(B2)-LEN(SUBSTITUTE(B2,'，',''))+1使用方法：更改公式中的B2為你表格中的單元格，在SUBSTITUTE更換為你表格中的分符號(hào)。原理概述：在這里每個(gè)姓名都是利用逗號(hào)隔開的，首先先計(jì)算下每個(gè)單元格的字符數(shù)，隨后利用SUBSTITUTE函數(shù)將逗號(hào)都替換掉，然后再計(jì)算下字符數(shù)，兩者相減就是逗號(hào)的個(gè)數(shù)，然后再加1就會(huì)得到人數(shù)提取姓名：=LEFT(A2,LENB(A2)-LEN(A2))提取電話：=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))使用方法：將公式中的A2替換為自己表格中的單元格位置即可原理概括：中文在excel中會(huì)占據(jù)2個(gè)字節(jié)，而數(shù)值僅僅只會(huì)占用1個(gè)字節(jié)，先利用len函數(shù)得到字符數(shù)，再利用lenb計(jì)算字節(jié)數(shù)，兩者相減就會(huì)得到漢字的個(gè)數(shù)，隨后利用left或者right函數(shù)提取數(shù)據(jù)即可以上就是今天分享的所有的Excel公式，大家可以收藏下，以后遇到類似的問(wèn)題，直接套用即可我是Excel從零到一，關(guān)注我，持續(xù)分享更多Excel技巧
|
