范文EXCEL身份證核對公式

1、 身份證核對公式 EXCELEXCEL中你輸入的公民身份號(hào)碼正確嗎? 目錄:1、輸入錯(cuò)誤自動(dòng)紅色顯示提示 2、15位身份證號(hào)碼升位為18位公民身份號(hào)碼 3、自動(dòng)生成出生日期和性別 4、中華人民共和國國家標(biāo)準(zhǔn)GB 11643-1999 公民身份號(hào)碼 一、輸入錯(cuò)誤自動(dòng)紅色顯示提示 在企業(yè)報(bào)送的EXCEL電子表格中,經(jīng)常遇到公民身份號(hào)碼輸入錯(cuò)誤而給工作帶來不必要的麻煩,降低了工作效率。 有沒有辦法在公民身份號(hào)碼錄入錯(cuò)誤時(shí)進(jìn)行自動(dòng)提示呢? 其實(shí)【中華人民共和國國家標(biāo)準(zhǔn) GB 11643-1999】對公民身份號(hào)碼第18位校驗(yàn)碼已作了詳細(xì)的規(guī)定,只是我們在電算工作中很少用到而已。 可用以下方法在EXCEL

2、中錄入公民身份號(hào)碼錯(cuò)誤時(shí)自動(dòng)紅字顯示: 選擇需要錄入公民身份號(hào)碼的單元格(如A1),選擇菜單:格式/條件格式。 點(diǎn)擊條件1左邊的下拉箭頭,選擇公式,在右邊的公式欄中輸入以: 下公式 =AND(LEN(D4)0,LEN(D4)15,LEN(D4)18) 接著單擊格式,點(diǎn)擊字體,顏色選擇紅色,確定。 在條件格式中點(diǎn)添加,點(diǎn)擊條件2左邊的下拉箭頭,選擇公式,在右邊的公式欄中輸入以下公式: =MID(X98765432,MOD(SUMPRODUCT(MID(D4,ROW($1:$17),1)*MOD(2(18-ROW($1:$17),11) ),11)+1,1)MID(D4,18,1) 接著單擊條件2

3、中的格式,點(diǎn)擊字體,顏色選擇紅色,確定,確定。 右鍵按住A1單元格右下角的小黑點(diǎn),向下拖至所需要錄入公民身份號(hào)碼的所有單元格,松開右鍵,選擇僅填充格式。 在接著的錄入中,凡是輸入不是15位或18位,或者輸入18位錯(cuò)誤的公民身份號(hào)碼都會(huì)紅色顯示。 身份證重復(fù)顯藍(lán)色的公式:COUNTIF($D$4:$D$65536,D4)1 二、15位身份證號(hào)碼升位為18位公民身份號(hào)碼 要使單元格中A1中的15位身份證號(hào)碼升位為18位公民身份號(hào)碼,只需在單元格A2中輸入以下公式即可: =REPLACE(A1,7,19)&MID(X98765432,MOD(SUMPRODUCT(MID(REPLACE(A1,7,1

4、9),ROW($1:$1 7),1)*MOD(2(18-ROW($1:$17),11),11)+1,1)或 =REPLACE(A1,7,19)&LOOKUP(MOD(SUMPRODUCT(MID(REPLACE(A1,7,19),ROW($1:$17),1)*MOD(2 0,X,9,8,7,6,5,4,3,2) 三、自動(dòng)生成出生日期和性別 如果單元格A1為身份證號(hào)碼或公民身份號(hào)碼,單元格A2為性別,單元格A3為出生日期,則操 作如下: 在單元格A2中輸入以下公式,則會(huì)自動(dòng)生成性別: ?義?尩土尬女?男?義?尩土尬女?男),) 在單元格A3中輸入以下公式,則會(huì)自動(dòng)生成出生日期: =IF(LEN(

5、A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2),IF(LEN(A1)=15,DATE(MID(A 1,7,2),MID(A1,9,2),MID(A1,11,2),) 頂 EXCEL中多條件求和、計(jì)數(shù)的4種方法 EXCEL中多條件求和、計(jì)數(shù)的方法大致可歸納為4種: 自動(dòng)篩選法 合并條件法 數(shù)組公式法 調(diào)用函數(shù)法 先打開上面的工作表,分別用這4種方法對同時(shí)滿足“A2:A15區(qū)域?yàn)锳,B2:B15區(qū)域?yàn)?0,C2:C15區(qū)域?yàn)椤睏l件的E2:E15區(qū)域進(jìn)行求和、計(jì)數(shù)。 一、自動(dòng)篩選法 利用EXCEL的自動(dòng)篩選功能和分類匯總函數(shù)對工作表數(shù)據(jù)進(jìn)行求和、計(jì)

6、數(shù)。 選中數(shù)據(jù)區(qū)域A1:E15,執(zhí)行“數(shù)據(jù)篩選自動(dòng)篩選”命令,進(jìn)入“自動(dòng)篩選”狀態(tài)。 選中E16單元格,輸入分類匯總公式:=SUBTOTAL(9,E2:E15),用于對求和列進(jìn)行統(tǒng)計(jì)。 點(diǎn)擊“條件1”右側(cè)的下拉按鈕,在隨后彈出的下拉列表中選擇“A”;再點(diǎn)擊“條件2”右 側(cè)的下拉按鈕,在隨后彈出的下拉列表中選擇“10”;再點(diǎn)擊“條件3”右側(cè)的下拉按鈕,在隨后彈出的下拉列表中選擇“”。 符合條件的數(shù)據(jù)被篩選出來,合計(jì)自動(dòng)出現(xiàn)在E16單元格中。 將SUBTOTAL(9,E2:E15)中的參數(shù)9改為2或3,可對符合條件的記錄進(jìn)行計(jì)數(shù)。 (更正:應(yīng)在第一行前面插入一行輸入分類匯總公式SUBTOTAL,否

7、) 則在篩選時(shí)會(huì)被篩選掉。 二、合并條件法 可將多個(gè)條件合并為一個(gè)條件,再利用條件求和函數(shù)、條件計(jì)數(shù)函數(shù)分別進(jìn)行單條件求和、計(jì)數(shù)。 在D2單元格中輸入合并公式:=A2&B2&C2,選擇D2:D15,按Ctrl+D向下填充。 在E16單元格中輸入條件求和公式:=SUMIF(D2:D15,A10,E2:E15) 在E17單元格中輸入條件計(jì)數(shù)公式:=COUNTIF(D2:D15,A10) 三、數(shù)組公式法 利用數(shù)組公式進(jìn)行多條件求和。 數(shù)組公式輸入完成后,不能直接用“Enter”鍵進(jìn)行確認(rèn),需要用“Ctrl+Shift+Enter”組合鍵進(jìn)行確認(rèn)。 確認(rèn)完成后,公式兩端會(huì)出現(xiàn)一對數(shù)組公式標(biāo)志(一對大括

8、號(hào))。 在E16單元格中輸入數(shù)組公式: 匽?休?盡屁?)*E2:E15)或: 匽?自?至?盡),E2:E15) 輸入完成后,按下“Ctrl+Shift+Enter”組合鍵確認(rèn)公式即可。 即確認(rèn)后的公 。)*E2:E15)箋匽?休?盡屁?式 對于有“或”條件的,可用+來完成。如同時(shí)滿足條件1=C,條件2=30,條件3=或,數(shù)組公式如下: 匽?休?盡屃?至?盡?至?盡)*E2:E15)或: 匽?自?)+(C2:C15=),E2:E15) 輸入完成后,同樣要按下“Ctrl+Shift+Enter”組合鍵。 四、調(diào)用函數(shù)法 調(diào)用SUMPRODUCT函數(shù)對數(shù)據(jù)進(jìn)行求和、計(jì)數(shù)。 SUMPRODUCT函數(shù):

9、是在給定的幾組數(shù)組中,將數(shù)組間對應(yīng)的元素相乘,并返回乘積之和。 在E16單元格中輸入函數(shù)公式: 匽?剮?自?至?盡)*E2:E15) 對于有“或”條件的,也可用+來完成。如同時(shí)滿足條件1=C,條件2=30,條件3=或,該函數(shù)使用如下: 匽?剮?自?)+(至?盡)*E2:E15) : 也可用此函數(shù)來進(jìn)行多條件計(jì)數(shù) 匽?剮?自?至?盡) SUMPROD UCT是“返回乘積之和”函數(shù),為什么可用來計(jì)數(shù)呢? 我們現(xiàn)以匽?剮?盡屁?至?)為例來看他的計(jì)算過程: 先看每個(gè)單元格和三個(gè)條件的真假關(guān)系: A2=A,條件為TRUE A3=C,條件為FALSE (因?yàn)锳3不等于A) A4=B,條件為FALSE (

10、因?yàn)锳4不等于A) B2=10,條件為TRUE B3=30,條件為FALSE (因?yàn)锽3不等于10) B4=20,條件為FALSE (因?yàn)锽4不等于10) C2=,條件為TRUE C3=,條件為FALSE (因?yàn)镃3不等于) C4=,條件為FALSE (因?yàn)镃4不等于) 因此,原函數(shù)可變?yōu)? =SUMPRODUCT(TRUE,FALSE,FALSE)*(TRUE,FALSE,FALSE)*(TRUE,FALSE,FALSE) 在EXCEL中,TRUE和FALSE分別用1和0表示。所以函數(shù)又變?yōu)? =SUMPRODUCT(1,0,0)*(1,0,0)*(1,0,0) 然后接下來就是SUMPROD

11、UCT的計(jì)算過程了: =1*1*1+0*0*0+0*0*0=1 所以最后的結(jié)果等于1。 通過計(jì)算過程可以看出,對應(yīng)位(即工作表的同一行或列,這里是同一行)只要有一個(gè)條件為0(即假,不符合條件),其乘積后就為0。 也就是說在前三條記錄中,同時(shí)滿足三種條件的只有1條記錄。 同理,用SUMPRODUCT求和的計(jì)算過程如下: 匽?剮?自?至?盡)*E2:E15) =SUNPRODUCT( 1,0,0,1,1,1,0,0,0,1,0, 0, 0, 0 )* ( 1,0,0,0,1,1,0,0,0,0,0, 0, 0, 0 )* ( 1,0,0,1,1,1,0,0,0,0,0, 0, 1, 0 )* (

12、1,2,3,4,5,6,7,8,9,10,11,12,13,14) - 1+0+0+0+5+6+0+0+0+0+0 +0 +0 +0 =12 。12即最后的求和結(jié)果等于 如何在EXCEL的A1中引用當(dāng)前活動(dòng)單元格的行號(hào)? 【問題】 如何在EXCEL的A1中引用當(dāng)前活動(dòng)單元格的行號(hào)? 也就是說,我鼠標(biāo)點(diǎn)到哪個(gè)單元格,那這個(gè)單元格的行號(hào)就會(huì)出現(xiàn)在A1中。 【解決辦法】 可通過編寫VBA來完成,方法如下: 打開EXCEL,調(diào)用工具/宏/visual basic 編輯器,將下面代碼放到worksheet中即可。 Private Sub Worksheet_SelectionChange(ByVal T

13、arget As Range) Range(A1) = ActiveCell.Row End Sub 為什么重命名工作表之后 EXCEL 會(huì)自動(dòng)在前面加上 文件名? 【問題】 最近編制了一個(gè)EXCEL電子表格,文件名稱為“川勞社辦2008185號(hào)超齡人員退休待遇計(jì)算”,把其中一個(gè)工作表名稱命名為“待遇審批號(hào)超齡人員“185變成,會(huì)自動(dòng)在前面加上部分文件名EXCEL可是”,表 退休待遇計(jì)算.xls待遇審批表”,重新命名該工作表則出現(xiàn)錯(cuò)誤,提示“在重命名工作表或圖表時(shí)輸入的名稱無效”。試重命名其他EXCEL電子表格中的工作表名稱,不會(huì)出現(xiàn)這種現(xiàn)象! 【解決辦法】 為什么會(huì)出現(xiàn)這種現(xiàn)象呢?試圖通過搜索網(wǎng)絡(luò)得到解決,發(fā)現(xiàn)也有不少網(wǎng)友提出同樣的問題,但是沒有解決辦法!后通過觀察發(fā)現(xiàn):為什么重命名后的工作表只自動(dòng)在前面加上部分文