学习excel这么多年，不知道大家是否尝试过用excel来制作一个身份证查询系统？即输入身份证号码即可以查询出性别、年龄、出生年月日、生肖、星座、所以地区等信息，这样的身份证查询系统用excel来做难吗？

事实告诉我们，相当容易，只需要用到excel中一些常用函数即可搞定，不信且往下看！

首先，在开始制作时，我们了解清楚身份证中每一个号码所代表的信息：

1、身份证号码与一个人的性别、出生年月、籍贯等信息是紧密相连的，无论是15位还是18位的身份证号码，其中都保存了相关的个人信息。

2、15位身份证号码：第7、8位为出生年份(两位数)，第9、10位为出生月份，第11、12位代表出生日期，第15位代表性别，奇数为男，偶数为女。

3、18位身份证号码：第7、8、9、10位为出生年份(四位数)，第11、第12位为出生月份，第13、14位代表出生日期，第17位代表性别，奇数为男，偶数为女。

4、"中国大陆居民身份证号码中的地址码的数字编码规则为：

第一、二位表示省（自治区、直辖市、特别行政区）。

第三、四位表示市（地级市、自治州、盟及国家直辖市所属市辖区和县的汇总码）。其中，01-20，51-70表示省直辖市；21-50表示地区（自治州、盟）。

第五、六位表示县（市辖区、县级市、旗）。01-18表示市辖区或地区（自治州、盟）辖县级市；21-80表示县（旗）；81-99表示省直辖县级市。"

清楚上面身份证信息之后我们开始制表：

第一列：性别

分别用到函数IF，MOD和MID，其用法分别如下：

=IF(条件判断, 结果为真返回值, 结果为假返回值) 用来判断是男是女

=MOD（被除数，除数）

= MID(被截取的字符, 从左起第几位开始截取, 从左起向右截取的长度是多少)

综合上面三个函数，第一列性别=IF(MOD(MID(A2,17,1),2),"男","女")，第一17位开始截取一位数字求余后判断是男还是女，基数代表男，偶数代表女

第二列：年龄

分别用到datedif、text、len和mid四个函数，初看上面公式有点吓人，但拆分出来，理解每一个函数的用法就觉得难了，先分别看下这个三个函数的用法（mid函数已在上面介绍过）

=DATEDIF(start_date,end_date,unit)

=text(数据值,设置单元格格式中自己所要选用的文本格式）

=LEN(文本字符串)

综合上面四个函数，第二列年龄=DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"y")&"周岁零"&DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"ym")&"月"&DATEDIF(--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00"),TODAY(),"md")&"天"，大家可将这公式的每一部分单独拿出来理解，然后再合并到一个公式里即可

第三列：出生年月日

这一列用到的函数都已介绍过，具体参考上面，详细公式=TEXT(MID(A2,7,8),"0年00月00日")

第四列：生肖

这一列用到的函数都已介绍过，具体参考上面，详细公式=IF(LEN(A2)=15,MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD("19"&MID(A2,7,2)-4,12)+1,1),MID("鼠牛虎兔龙蛇马羊猴鸡狗猪",MOD(MID(A2,7,4)-4,12)+1,1))

第五列：星座

=VALUE(需要转换成数值格式的文本字符串)

=VLOOKUP（找什么，在哪找，找到后返回其右侧对应的第几列数据，精确还是模糊查找）

综合上面两个函数，第五列星座=VLOOKUP(VALUE("1900-"&TEXT(MID(A2,LEN(A2)/2+2,4),"#-##")),{1,"摩羯座";20,"水瓶座";50,"双鱼座";81,"白羊座";111,"金牛座";142,"双子座";174,"巨蟹座";205,"狮子座";236,"处女座";267,"天秤座";298,"天蝎座";328,"射手座";357,"摩羯座"},2,TRUE)

第六列：所在地区

这一列用到的函数都已介绍过，具体参考上面，详细公式=VLOOKUP(LEFT(A2,6),身份证地址码对照表!A:B,2,1)

至此，关于excel制作身份证查询系统的内容已全部介绍完，如果大家第一次没看懂可以多看几次，把每一个函数的语法搞清楚，然后根据身份证号码本身代表的含义用公式连接起来即能得到我们想要的答案。

在工作中几乎每个人都要编写相关工作计划，或是某个项目的计划管理表。大多数人计划表都是采用EXCEL制作，也有一些采用专业的软件Project等，我个人习惯用EXCEL。

如何规划管理表，简化相应工作，更有利管理，根据个人习惯都会有所不同。

1，公司LOGO

2，项目名称

3，题头

4，项目

5，计划与实际

6，自动进度跟进

7，自动周项目提示及，上周项目回顾

8，项目状态报告

9，自动绘制甘特图。

10，折叠和展开子项目，

EXCEL公式表格制定简易实用的【自动生成工资系统】管理工具

本文的自动生成工资系统结合了公式：VLOOKUP、IF、IFERROR、INDEX、SMALL、OR、AND、ROW( )等函数以及数组、绝对引用、相对引用以及超链接等功能。（本文适用于有一定的EXCEL应用基础的人）

关键点：

【工资表】、【全部工资条】、【查询表】、【查询工资条】、【系统介绍说明】等条框上面的矢量图标须与对应的表格进行超链接。

IFERROR函数的功能在于如果公式的计算结果为错误，则返回您指定的值；否则将返回公式的结果。作用与ISERROR与IF组合相类似。

数组“大扩号”的输入是：选择须要输入的单元格范围，按Ctrl+Shirt键，再敲Enter键即可。

第一步：建立一个EXCEL文档与表格，如下截图：

共分建立以下六个表格：【首页】、【工资表】、【全部工资条】、【查询表】、【查询工资条】、【系统介绍说明】等。

关键点：对应的表格顺序依上面截图排列。

第二步：设定每张表的格式或内容样式，先在【工资表】逐项填写年月及员工工资信息；如下截图：

注意关键点：

【工资表】的样式须与【工资条】的内容排列顺序完全一致。

【全部工资条】中的自动生成全体员工的工资条，如下截图：

注意关键点：

【全部工资条】的样式须与【查询工资条】的内容完全一样；

如上截图，全部工资条前面增加“2018年3月工资”字样的目的在于打印工资条后便于纸档的裁切。

【查询表】中，输入员工的姓名，可以查出该员工的工资条相关全部信息，如下截图：

并在【查询工资条】表格中自动生成该员工的工资条;输入部门名称，则可以查出该部门所有员工的工资，并在【查询工资条】表格中自动生成该部门所有员工的工资条。

注意关键点：个人所得税计算一列，可以依实际扣税标准并套用IF函数。

以上查询表正表显示的信息内容与工资料内容完全一样。

第三步：公式输入与链接

工资表公式链接，如下截图：

注意关键点：

上面A列平时在操作使用时可以隐藏；

这列公式的目的与【查询表】信息匹配。此表须注意函数AND（与）与OR（或）的结合使用以及绝对引用与返回行函数ROW( )的运用。

工资条公式链接，如下截图：

查询表公式链接，如下截图：

查询表第A列在平时操作时一般隐藏。

第五步：制作完成效果显示，如下截图：

只要依以上步骤操作，则可以实现简易的工资管理工具的制定，前提是须对相关公式函数有初步的了解与应用。

肯特最新力作二《仓库库存管理系统》，通过VBA编程的形式制作而成；它操作简易，可以帮助中小企业、个体经营者完善仓库库存管理，极力推荐！

................................................

主要功能介绍：

１、支持备份功能，再也不担心数据丢失了

２、基础档案建立后，可支持模糊查找，即根据关键字快速匹配要录入的商品信息

３、简易录入出入库数据后，可时时查询库存余额表及库存明细表

功能界面截图如下：

第七章 人事档案管理制度

1、凡公司一切职位员工均需建立一份人事基本档案，每人一袋，专袋专用，袋面记录档案人姓名，性别及档案内的资料的明细目录。

2、人事基本档案的主要内容有：居民身份证，毕业证、计生证，健康证，此类证件复印件及员工履历表，业绩评估资料，教育培训资料及因工作性质需担保人担保的有关资料。

3、人事档案由行政部人事部建立及管理，行政人事部档案管理员不得向外泄露任何人员的个人档案内容或遗失任何有效档案内容的资料。

4、所有人事档案由人事部保存至该员工离职一年后方可销毁。

注意事项: 需编号，记录，各类证件的真实性！培训，晋升，降职，奖励，处罚等记录均需完整清晰。

-------END-------

感谢大家关注薇薇，后续薇薇会持续分享办公软件使用，表格制作，ISO文件，薪酬管理，培训建设，企业文化建设文案等等。

以上内容：薇薇原家分享，未经许可，请勿私自转载他用，谢谢！

（图片来源注明：百度搜索）

人事管理人员避免不了的经常与员工档案打交道，查询变更等都是家常便饭，如果没有较好的管理方法，那将费时费力……今天我们来学习利用“自定义名称”和函数公式组合技巧来实现员工照片等信息的动态查询技巧。

一、效果展示。

从实际效果中我们可以看出，只需要选择相应的员工工号，即可查询其它相关信息，包括照片信息……该如何去实现了？

二、实现方法。

1、准备员工信息表。

在其中的一个工作簿中准备带照片的员工信息表，表格内容可以根据实际情况自定义。

2、获取【工号】下拉列表框内容。

方法：

1、选中目标单元格。

2、【数据】-【数据验证】，选取【允许】下的【序列】，单击【来源】右侧的箭头，单击【员工信息表】并选取【工号】列的内容。再次单击箭头，并【确定】。（或者在来源中直接输入公式：=员工信息表!$B$3:$B$9即可）。

3、获取【照片】等内容。

方法：

1、单击目标单元格。

2、【公式】-【定义名称】，在【名称】对话框中输入名称，例如【照片】。

3、在【引用位置】对话框中输入公式：=INDEX(员工信息表!$G:$G,MATCH(员工档案!$B$2,员工信息表!$B:$B,))并【确定】。

4、打开【员工信息表】工作簿，复制任意一张照片，并粘贴到【员工档案】工作簿的目标单元格中，调整大小等。（文本内容可省略此步骤）

5、在地址栏中输入公式：=照片（等号后面的名称必须和第2步名称对话框中输入的内容一致）。

6、单击下拉列表查询内容。

重点解读：

公式：=INDEX(员工信息表!$G:$G,MATCH(员工档案!$B$2,员工信息表!$B:$B,))是实现此功能的重要内容。INDEX函数是根据位置筛选相应的内容，而MATCH函数是定位函数。首先利用MATCH函数来定位“员工档案!$B$2”内容在“员工信息表”中的位置，然后利用INDEX提取其内容并予以显示。

office办公应用三大件：Word、Excel、PowerPoint，几乎每台电脑都会装这三个办公软件，而Excel在办公场景中更是必不可少。

办公族都要用Excel来处理数据，例如记录各种数据，客户订单、产品资料、人事档案等。

实际上很多单位要求熟悉Excel的程度，也就仅止于会用Excel录数据、做表单罢了。

最熟悉的快捷键就是Ctrl+c/Ctrl+v，最常用的Excel操作就是合并单元格、求和，更多的工作就是用公司原有的表单模板干活。

比如一个跟单文员，每天的工作就是录入订单数据到总表，用文件夹里的制造工单模板、送货单模板、送货标签模板填入总表的数据，复制粘贴，再修改一下边框等，就是一天。

数据多的时候，那就别想准时下班了。

同样都是会用Excel的人，却有人用Excel技巧做出了办公自动化的管理软件，轻松完成每天的工作。

为什么能想到做这么一个软件呢？因为都是重复性的工作啊，制造工单、送货单、送货标签的数据都是取自于总表，让软件自动传送呗。

为什么用Excel技巧就搭建办公自动化软件？不需要学编程？是的，无需编程、用Excel技巧也能做办公软件。

别人正是利用了这样一款类似于Excel的表格软件，完成了办公管理软件的搭建。

它就是云表，类似于Excel的操作界面，通过中文的业务逻辑就能实现自动化办公。

像用Excel一样设定表单的填写规范，还可以设置自动生成一定格式的编号，如按日期自动编号。

用最基础的Excel技巧，加以办公管理经验，用中文描述业务逻辑，无需编程就可以用云表做出一套好用的办公管理软件了。

还在等什么呢？还想继续加班吗，别人都用Excel技巧搭建办公管理软件，而你还在用它做表格！

云表能做的功能还有更多，像自动更新操作记录、库存最低预警、工资考勤自动计算等，甚至可以做出完整的ERP管理软件、仓库管理软件、财务出纳软件.....

虽然云表功能如此强大，但它仍然提供了免费版，还有详细教程和技术客服助你快速入门！！！

转载自百家号作者：优品资源

通过准备数据源以及查询表格两个步骤我们准备好了图片并批量导入了数据源表中。本篇我们就做出查询表。查询表的最终效果如下：

我们根据上一步骤完成的带有图片的数据源，做一个动态查询档案，输入姓名即可查询到照片、性别、出生日期等。做好了之后是这样的：怎么操作呢？步骤如下：（1）首先创建以下表格。

并且准备好数据源

（2）在姓名对应的B3单元格输入“张飞”。

（3）接下来“性别”“出生年月”等其他信息的获取，我们根据姓名“张飞”采用一个公式来完成。在性别对应的B5单元格输入

性别： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!A5,数据源!$1:$1,0)-1),"")

出生年月： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!A8,数据源!$1:$1,0)-1),"")

血型： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!A10,数据源!$1:$1,0)-1),"")

星座： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!D8,数据源!$1:$1,0)-1),"")

职业： =IFERROR(OFFSET(数据源!$A$3,MATCH($A$3,数据源!$A:$A,0)-1,MATCH(查询!D10,数据源!$1:$1,0)-1),"")

解析：

MATCH（查找内容，查找区域，0）：表示查找第一个参数在第二个参数的位置，第三个参数为0代表精确匹配。这里分别返回的是B2单元格“张飞”在数据源A列（姓名列）对应的位置2和A3单元格“性别”在数据源第1行（标题行）对应的位置2。

OFFSET（参照位置，偏移的行位置，偏移的列位置）：表示以第一个参数为位置参照，偏移到第二参数定义的行数和第三参数定义的列数所在的单元格，返回其值。这里的含义是以“数据源”表里的A1单元格为准，向下偏移2-1行向右偏移2-1列，获取到B3单元格值“男”。

在上述OFFSET函数中，如果B3单元格为空，则返回错误信息“N/A”。我们利用IFERR0R函数，当单元格返回错误“N/A”则输出为空值。因为后续还要查询“出生年月”“星座”等，所以公式中“查询!A10”这个是相对引用，其他都采用了绝对引用。 然后把这个公式复制应用到“出生年月”“星座”等对应的单元格里。注意修改相对引用项。

（4） 接下来我们要把图片动态引用过来。单击【公式】选项卡下的名称管理器旁边的“定义名称”。=INDEX(数据源!$G:$G,MATCH(查询!$B$3,数据源!$A:$A,0))在在弹出的对话菜单中，【名称】处输入“照片”，【引用位置】输入公式：=INDEX(数据源!$G:$G,MATCH(查询!$B$2,数据源!$A:$A,0))

解析：

MATCH：表示查找第一个参数，也就是姓名“张飞”单元格在第二个参数数据源姓名列的位置，返回3。INDEX（数据区域，数据位置）：表示用第二个参数给出的位置在第一个参数中查找对应的值。上述公式的意思就是利用INDEX函数返回数据源G列（图片列）中对应行号（由MATCH函数获取）位置的图片。

（5）复制数据源表任意一张照片，粘贴到“查询”表的D3单元格。单击该照片，在编辑栏中输入公式：=照片，点击Enter。这样当B3单元格输入姓名后点击确定，对应的照片和其他信息就会一起动态更新了。注意：使用这种方法时，当姓名为空的时候或者姓名错误的时候，仍然会显示上一次操作之后的照片。 Ok，整个查询系统就建立好了。简单回顾一下：利用PS的动作批处理实现图像不变形下统一大小；利用表格标签table代码实现图像批量插入；利用INDEX函数定义“照片”实现照片的动态查询。其他信息的动态查询则是利用OFFSET函数实现的。

假如你学习到了这个新技能不妨转发推荐给你的小伙伴。并动动小指头收藏，以免下次走丢。

办公室 人事部通用 公司多岗位自动考勤管理系统excel表格模板，

办公室 人事部通用 公司多岗位自动考勤管理系统excel表格模板，

有需要模板的，关注我后，收藏转发下文章，可以私信我。免费下载，

办公室 人事部通用 公司多岗位自动考勤管理系统excel表格模板。

第9. 制作员工业绩评测表

一个基本信息表

数据合并计算，取季度平均值，四个季度全部添加

季度平均

工具选项

选中区域排序，数据按部门排序

rank函数，前面有排名的讲述，这里不再提及。

插入公式 IF函数

第10. 制作员工个人薪资明细表

效果图

插入员工工资表

制作基本表

年份输入公式=YEAR(NOW())

姓名按钮及窗体按钮：

单击视图，工具栏，窗体，弹出窗体工具栏，点击组合框按钮，拖动姓名右边的单元格出现倒黑三角按钮，如上图，右键点击组合框按钮（黑三角按钮），弹出菜单，选择设置控件格式，在此对话框中，数据源区，要选择员工工资表，绝对地址引用的姓名列。确定弹出对象格式。

在当前表点击任意空格，如$G$1

这样名字出现

选择唐僧，G1单元格出现1，

INDEX函数公式，点击H1空格

单击部门右边空格框，插入函数，函数，常用函数，查找与引用，vlookup函数，《高效办公工具excel.....和查询相关的vlookup函数》里已讲述。

不同名字出现不同的部门

如工资总额：

第11. 员工档案管理系统表格设计

11种表格制作已完结，是不是学到很多啊！