|
本帖最后由 你猜？ 于 2013-7-25 20:53 編輯 <籍貫、性別、出生年月日、農(nóng)歷年、屬相、生日、星座、出生至今年數(shù)、出生至今天數(shù)和判斷證件有效性> 通過身份證提取籍貫、農(nóng)歷年、屬相、星座和出生至今天數(shù)是獨家報道哦~ 
等我研究透了，給你們計算生辰八字。{:soso_e151:} [d1000] 文件中公式說明： 本帖隱藏的內(nèi)容原籍貫：
此運算運用了iferror邏輯函數(shù)，主要判斷后面的函數(shù)計算結(jié)果，如果經(jīng)過后面所有函數(shù)的判斷都無法得到結(jié)果的，就在此單元格顯示"證件無效"。（此函數(shù)在后面的單元格（除判斷身份證有效單元格外）都用到此函數(shù)，同理，后面就不一一解釋此函數(shù)的用法。 if函數(shù)主要是判斷a2單元格的字符數(shù)（len函數(shù)）是否滿足15位，如果是15位的話，則從a2單元格里提取（mid函數(shù)）前6位數(shù)字（從第一位提取，6個字符）在數(shù)據(jù)庫中查找（vlookup函數(shù)）滿足此條件的原籍貫所在地，此單元中后面的vlookup函數(shù)也是此用途。 中間運用了value函數(shù)，主要是因為數(shù)據(jù)庫的數(shù)據(jù)都是數(shù)值型數(shù)字，函數(shù)中提取的是文本型數(shù)字，value函數(shù)就是把文本型數(shù)值激活為數(shù)值型函數(shù)。 性別：
此運算中主要運用了循環(huán)余數(shù)（mod）函數(shù)，將a2單元格中從第15位數(shù)字起提取1位，并除以2，如果得到1，就是男性，否則就是女性。<mod函數(shù)也是判斷數(shù)字是否為奇偶數(shù)，或被某是否整除，此單元格中就運用了判斷奇偶數(shù)，農(nóng)歷年和屬相中運用了被60整除，在我的視頻教程：wps2013視頻教程：wps表格制作工資條的四種方法和技巧：http://bbs.wps.cn/thread-22377957-1-1.html> 出生年月日：
此運算中運用了轉(zhuǎn)換文本函數(shù)（text），指定提?。╩id函數(shù)）的信息轉(zhuǎn)化為指定的格式。 本帖隱藏的內(nèi)容農(nóng)歷年：
此運算中遇到的所有函數(shù)在前面都已經(jīng)提到了，在這里就不一一詳解，在此只解釋下思路，在15位身份證號碼在提取(mid)后在前面加上"19"，先用用value強制轉(zhuǎn)化為數(shù)值型數(shù)字參與計算，然后運用循環(huán)余數(shù)函數(shù)（mod）和查找引用函數(shù)結(jié)合在數(shù)據(jù)中，查找對應(yīng)的農(nóng)歷年份。 屬相：
此運算與農(nóng)歷年的運算類似，在數(shù)據(jù)庫中查找相對應(yīng)的屬相。 生日：
此運算與提取出生年月日類似，在這里只是提?。╩id）月份和年份，然后用文本函數(shù)（text)強制轉(zhuǎn)化為日期格式。 本帖隱藏的內(nèi)容星座：
此運算中運用了查找引用函數(shù)（index函數(shù)嵌套match函數(shù)），與前面的vlookup函數(shù)類似，只不過vlookup函數(shù)如果要逆向查找需要嵌套if函數(shù)和數(shù)組公式： =IFERROR(IF(LEN(A2)=15,VLOOKUP(VALUE(TEXT(MID(A2,9,4),"0月00日")),IF({1,0},Sheet2!$N$1:$N$13,Sheet2!$M$1:$M$13),2,1),VLOOKUP(VALUE(TEXT(MID(A2,11,4),"0月00日")),IF({1,10},Sheet2!$N$1:$N$13,Sheet2!$M$1:$M$13),2,1)),"證件無效") 用index函數(shù)嵌套match函數(shù)就不用這些了此法是vlookup函數(shù)的弊端。在此運用index函數(shù)嵌套match函數(shù)，主要是讓大家更多的了解其他的查找引用函數(shù)。（其實此數(shù)據(jù)庫中的日期，是可以放在星座前面的，然后用vlookup函數(shù)就簡單多了） [此處運用了查找引用函數(shù)的模糊匹配，絕對匹配：用index函數(shù)嵌套match函數(shù)<match第三參數(shù)更改為0（false)，或默認不寫>或vlookup函數(shù)<第四參數(shù)更為0（false）>；如果是模糊匹配，在match函數(shù)中的第三個參數(shù)更改為1(true)(第三參數(shù)為1升序排列，-1為降序排列），vlookup第四個參數(shù)為1（true)] 如圖： 
出生至今年數(shù)：
此運算運用了日期函數(shù)（year、today函數(shù)），year函數(shù)是提取日期函數(shù)中的年份，然后用今天（today函數(shù)）所在的年份減去a2身份證日期中提取（mid)日期中的年份。（在15位身份證中是從第7位提取6位，并在前面加上"19"，然后將其強制轉(zhuǎn)化為數(shù)字（text函數(shù)）參與計算。 出生至今天數(shù)：
此運算與出生至今年數(shù)類似，只不過此函數(shù)是將其強制轉(zhuǎn)換（text)為天數(shù)然后參與計算的。<提示：時間日期函數(shù)有個特征：時間函數(shù)是小數(shù)（把當前時間除以24得到的），日期函數(shù)是整數(shù)（是從1900年1月1日算起的天數(shù)），所以許多網(wǎng)友在在表格中經(jīng)常遇到，明明輸入時間，卻顯示小數(shù)，明明輸入日期，卻顯示數(shù)字，遇到此種情況，只要設(shè)置單元格就行，如果在函數(shù)運算中出現(xiàn)的話，就用text轉(zhuǎn)化。> 判斷證件有效性：
此運算是運用（逆向思維）邏輯函數(shù)（if和or）判斷前面單元格（b2,c2,d2,e2,f2,g2,h2,i2和j2）如果有一個顯示為"證件無效"，則說明此身份證證件無效，否則此身份證就有效。 此法也可以使用函數(shù)（順向思維）：
本帖隱藏的內(nèi)容如圖：
總結(jié)： 運用函數(shù)類型： 邏輯函數(shù)：iferrror,if,and,or, 2. 文本字節(jié)函數(shù)：mid，len，text，value 3. 查找引用函數(shù)：vlookup，index，match 4. 時間日期函數(shù)：year，today 5. 數(shù)學(xué)函數(shù)：mod 特別強調(diào)下： 如果大家在今后遇到將文本型數(shù)數(shù)字強制轉(zhuǎn)化為數(shù)字值型數(shù)字用value函數(shù)。 如果大家在今后遇到格式中有限考慮用單元格設(shè)置，如果在運算中，首選text函數(shù)。 如果大家在統(tǒng)計單元格字符，優(yōu)先選擇len函數(shù)，統(tǒng)計單元格字節(jié)就用lenb函數(shù)。 如果是遇到需要循環(huán)處理數(shù)據(jù)，就用mod函數(shù)。 如果大家要絕對查找數(shù)據(jù)，用index函數(shù)嵌套match函數(shù)<match第三參數(shù)更改為0（false)，或默認不寫>或vlookup函數(shù)<第四參數(shù)更為0（false）>；如果是模糊匹配，在match函數(shù)中的第三個參數(shù)更改為1(true)(第三參數(shù)為1升序排列，-1為降序排列），vlookup第四個參數(shù)為1（true)。 文件撤銷保護密碼：123 
通過身份證提取信息.xls
(868 KB, 下載次數(shù): 211)
|
|
|
