EXCEL身份证核对公式.docx
- 文档编号:12921346
- 上传时间:2023-04-22
- 格式:DOCX
- 页数:14
- 大小:18.60KB
EXCEL身份证核对公式.docx
《EXCEL身份证核对公式.docx》由会员分享,可在线阅读,更多相关《EXCEL身份证核对公式.docx(14页珍藏版)》请在冰豆网上搜索。
EXCEL身份证核对公式
EXCEL身份证查对公式
EXCEL中你输入的公民身份号码正确吗?
目录:
1、输入错误自动红色显示提示
2、15位身份证号码升位为18位公民身份号码
3、自动生成出诞辰期和性别
4、中华人民共和国国家标准GB11643-1999公民身份号码
一、输入错误自动红色显示提示
在公司报送的EXCEL电子表格中,常常碰到公民身份号码输入错误而给工作带来不用要的麻烦,降低了工作效率。
有没有方法在公民身份号码录入错误时进行自动提示呢?
其实【中华人民共和国国家标准码第18位校验码已作了详尽的规定而已。
GB11643-1999】对公民身份号
不过我们在电算工作中极少用到
可用以下方法在EXCEL中录入公民身份号码错误时自动红字显
示:
选择需要录入公民身份号码的单元格(如A1),选择菜单:
格式/条件格式。
①点击条件1左侧的下拉箭头,选择公式,在右边的公式栏中输入以下公式:
=AND(LEN(D4)<>0,LEN(D4)<>15,LEN(D4)<>18)
接着单击格式,点击字体,颜色选择红色,确立。
②在条件格式中点增添,点击条件2左侧的下拉箭头,选择公式,在右
边的公式栏中输入以下公式:
=MID("10X98765432",MOD(SUMPRODUCT(MID(D4,ROW($1:
$
17),1)*MOD(2^(18-ROW($1:
$17)),11)),11)+1,1)<>MID(D4,18,1)
接着单击条件2中的格式,点击字体,颜色选择红色,确立,确立。
右键按住A1单元格右下角的小黑点,向下拖至所需要录入公民身份号码的全部单元格,松开右键,选择仅填补格式。
在接着的录入中,凡是输入不是15位或18位,或许输入18位错误的公民身份号码都会红色显示。
身份证重复显蓝色的公式:
COUNTIF($D$4:
$D$65536,D4)>1二、
15位身份证号码升位为18位公民身份号码
要使单元格中A1中的15位身份证号码升位为18位公民身份号码,只需在单元格A2中输入以下公式即可:
=REPLACE(A1,7,,19)&MID("10X98765432",MOD(SUMPRODUCT(MID(REPLACE(A1,7,,19),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
^(18-ROW($1:
$17)),11)),11),{0,1,2,3,4,5,6,7,8,9,10},{1,0,"X",9,8,
7,6,5,4,3,2})
三、自动生成出诞辰期和性别
假如单元格A1为身份证号码或公民身份号码,单元格A2为性别,单元格A3为出诞辰期,则操
作以下:
在单元格A2中输入以下公式,则会自动生成性别:
=IF(LEN(A1)=18,IF(INT(MID(A1,17,1)/2)=MID(A1,17,1)/2,"女","
男"),IF(LEN(A1)=15,IF(INT(MID(A1,15,1)/2)=MID(A1,15,1)/2,"女","
男"),""))
在单元格A3中输入以下公式,则会自动生成出诞辰期:
=IF(LEN(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中多条件乞降、计数的4种方法
EXCEL中多条件乞降、计数的方法大概可概括为4种:
⒈自动挑选法
⒉归并条件法
⒊数组公式法
⒋调用函数法
先翻开上边的工作表,分别用这4种方法对同时知足“A2:
A15地区
为A,B2:
B15地区为10,C2:
C15地区为Ⅰ”条件的E2:
E15地区进行乞降、计数。
一、自动挑选法
利用EXCEL的自动挑选功能和分类汇总函数对工作表数据进行乞降、计数。
①选中数据地区A1:
E15,履行“数据→挑选→自动挑选”命令,进入
“自动挑选”状态。
②选中E16单元格,输入分类汇总公式:
=SUBTOTAL(9,E2:
E15),
用于对乞降列进行统计。
③点击“条件1”右边的下拉按钮,在随后弹出的下拉列表中选择“A”;
再点击“条件2”右
侧的下拉按钮,在随后弹出的下拉列表中选择“10”;再点击“条件3”
右边的下拉按钮,在随后弹出的下拉列表中选择“Ⅰ”。
④切合条件的数据被挑选出来,共计自动出此刻E16单元格中。
将SUBTOTAL(9,E2:
E15)中的参数9改为2或3,可对切合条件的记录进行计数。
(改正:
应在第一行前方插入一行输入分类汇总公式SUBTOTAL,否
则在挑选时会被挑选掉。
)
二、归并条件法
可将多个条件归并为一个条件,再利用条件乞降函数、条件计数函数分别进行单条件乞降、计数。
在D2单元格中输入归并公式:
=A2&B2&C2,选择D2:
D15,按Ctrl+D
向下填补。
在E16单元格中输入条件乞降公
式:
=SUMIF(D2:
D15,"A10Ⅰ",E2:
E15)
在E17单元格中输入条件计数公式:
=COUNTIF(D2:
D15,"A10Ⅰ")
三、数组公式法
利用数组公式进行多条件乞降。
数组公式输入达成后,不可以直接用“Enter键”进行确认,需要用
“Ctrl+Shift+Enter组合”键进行确认。
确认达成后,公式两头会出现一对数组公式标记(一对大括号)。
在E16单元格中输入数组公式:
=SUM((A2:
A15="A")*(B2:
B15=10)*(C2:
C15="Ⅰ")*E2:
E15)或:
=SUM(IF((A2:
A15="A")*(B2:
B15=10)*(C2:
C15="Ⅰ"),E2:
E15))
输入达成后,按下“Ctrl+Shift+Enter组合”键确认公式即可。
即确认后的公
式:
{=SUM((A2:
A15="A")*(B2:
B15=10)*(C2:
C15="Ⅰ")*E2:
E15)}。
关于有“或”条件的,可用+来达成。
好像时知足条件1=C,条件2=30,
条件3=Ⅱ或Ⅲ,数组公式以下:
=SUM((A2:
A15="C")*(B2:
B15=30)*((C2:
C15="Ⅱ")+(C2:
C15="Ⅲ
"))*E2:
E15)或:
=SUM(IF((A2:
A15="C")*(B2:
B15=30)*((C2:
C15="Ⅱ")+(C2:
C15=
"Ⅲ")),E2:
E15))
输入达成后,相同要按下“Ctrl+Shift+Enter组合”键。
四、调用函数法
调用SUMPRODUCT函数对数据进行乞降、计数。
SUMPRODUCT函数:
是在给定的几组数组中,将数组间对应的元
素相乘,并返回乘积之和。
在E16单元格中输入函数公式:
=SUMPRODUCT((A2:
A15="A")*(B2:
B15=10)*(C2:
C15="Ⅰ")*E2
:
E15)
关于有“或”条件的,也可用+来达成。
好像时知足条件1=C,条件
2=30,条件3=Ⅱ或Ⅲ,该函数使用以下:
=SUMPRODUCT((A2:
A15="C")*(B2:
B15=30)*((C2:
C15="Ⅱ")+(
C2:
C15="Ⅲ"))*E2:
E15)
也可用此函数来进行多条件计数:
=SUMPRODUCT((A2:
A15="A")*(B2:
B15=10)*(C2:
C15="Ⅰ"))
★SUMPRODUCT是“返回乘积之和”函数,为何可用来计数呢?
我们现以
=SUMPRODUCT((A2:
A4="A")*(B2:
B4=10)*(C2:
C4="Ⅰ"))为例来看
他的计算过程:
先看每个单元格和三个条件的真假关系:
A2=A,条件为TRUE
A3=C,条件为FALSE(由于A3不等于A)
A4=B,条件为FALSE(由于A4不等于A)
B2=10,条件为TRUE
B3=30,条件为FALSE(由于B3不等于10)
B4=20,条件为FALSE(由于B4不等于10)
C2=Ⅰ,条件为TRUE
C3=Ⅲ,条件为FALSE(由于C3不等于Ⅰ)
C4=Ⅱ,条件为FALSE(由于C4不等于Ⅰ)
所以,原函数可变为:
=SUMPRODUCT((TRUE,FALSE,FALSE)*(TRUE,FALSE,FALS
E)*(TRUE,FALSE,FALSE))
在EXCEL中,TRUE和FALSE分别用1和0表示。
所以函数又变
为:
=SUMPRODUCT((1,0,0)*(1,0,0)*(1,0,0))
而后接下来就是SUMPRODUCT的计算过程了:
=1*1*1+0*0*0+0*0*0=1
所以最后的结果等于1。
经过计算过程能够看出,对应位(即工作表的同一行或列,这里是同一行)只需有一个条件为0(即假,不切合条件),其乘积后就为0。
也就是说在前三条记录中,同时知足三种条件的只有1条记录。
同理,用SUMPRODUCT乞降的计算过程以下:
=SUMPRODUCT((A2:
A15="A")*(B2:
B15=10)*(C2:
C15="Ⅰ")*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)*
×(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。
怎样在EXCEL的A1中引用目前活动单元格的行号?
【问题】
怎样在EXCEL的A1中引用目前活动单元格的行号?
也就是说,我鼠标点到哪个单元格,那这个单元格的行号就会出此刻
A1中。
【解决方法】
可经过编写VBA来达成,方法以下:
翻开EXCEL,调用[工具/宏/visualbasic编写器],将下边代码放到
worksheet中即可。
PrivateSubWorksheet_SelectionChange(ByValTargetAsRange)
EndSub
为何重命名工作表以后EXCEL会自动在前方加上
文件名?
【问题】
近来编制了一个EXCEL电子表格,文件名称为“川劳社办[2008]185号超龄人员退休待遇计算”,把此中一个工作表名称命名为“待遇审批表”,但是EXCEL会自动在前方加上部分文件名,变为“185号超龄人员
退休待遇计算.xls]待遇审批表”,从头命名该工作表则出现错误,提示“在重命名工作表或图表时输入的名称无效”。
试重命名其余EXCEL电子表格中的工作表名称,不会出现这类现象!
!
!
【解决方法】
为何会出现这类现象呢?
试图经过搜寻网络获得解决,发现也有许多网友提出相同的问题,但是没有解决方法!
后经过察看发现:
为何
重命名后的工作表只自动在前方加上部分文件名呢?
而文件名“前]”面的内容没有?
是否是“在]”作乱?
于是把文件名中的“[]改”成“[]全”(角),重试问题解决了!
!
!
本来EXCEL电子表格的文件名称中不可以存在半角的“[]否”,则就会
致使上述问题出现,假如文件名中的确需要“[]请”,用全角“[]或”,者使用没有这类BUG的更高级版本Office软件。
本问题在MicrosoftOfficeXP(即MicrosoftExcel2002
(10.2614.2625))中存在。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 身份证 核对 公式