excel表格定期采集(每天学一点Excel201024)
excel表格定期采集(每天学一点Excel201024)7)点击“信息采集表”标签回到工作表。选择B列,点击“数据”选项卡的“数据有效性”,“设置”标签下“允许”中选择“序列”,确保选中“提供下拉箭头”复选框,来源中输入“男 女”,确定。6)命名单元格区域,选择C2:C13(注意,多选了一个空格),在左上角的名称框输入“民族”,同样将G2:G15命名为学历,H2:H33命名为出生地,I2:I10命名为部门。点击“公式”选项卡的“名称管理器”可以看到定义的名称,可以新建、编辑、删除、筛选操作。3)在第一行逐列输入采集信息的标题,包括:姓名,性别,民族,身份证,出生日期,年龄,学历,出生地,部门,备注。4)为了防止填错数据,我们要用到前面学到数据验证,固定某些序列。右键点击“信息采集表”标签,选择“移动或复制”,勾选“建立副本”后确定,双击新标签改为“序列”。5)输入设定好的序列内容,如图:
036 示例:个人数据采集表
下面更我从头开始一步一步学做:
1)新建一个空白工作簿,另存为“信息采集表”;
2)双击“sheet1”(标签上点右键选择“重命名”),命名为“信息采集表”。可以随手把送sheet2、3删除。
3)在第一行逐列输入采集信息的标题,包括:姓名,性别,民族,身份证,出生日期,年龄,学历,出生地,部门,备注。
4)为了防止填错数据,我们要用到前面学到数据验证,固定某些序列。右键点击“信息采集表”标签,选择“移动或复制”,勾选“建立副本”后确定,双击新标签改为“序列”。
5)输入设定好的序列内容,如图:
6)命名单元格区域,选择C2:C13(注意,多选了一个空格),在左上角的名称框输入“民族”,同样将G2:G15命名为学历,H2:H33命名为出生地,I2:I10命名为部门。点击“公式”选项卡的“名称管理器”可以看到定义的名称,可以新建、编辑、删除、筛选操作。
7)点击“信息采集表”标签回到工作表。选择B列,点击“数据”选项卡的“数据有效性”,“设置”标签下“允许”中选择“序列”,确保选中“提供下拉箭头”复选框,来源中输入“男 女”,确定。
8)点击B列的单元格,右侧出现下拉箭头进行输入的选择(手动输入也可),输入其他内容均报错。
9)选择C列,同样进入“数据有效性”对话框,来源中输入“=民族”后确定。C列可以选择输入民族了(注意到最后一个空格了吧,这就是“常规”格式,你可以输入任何值,当然你可以把56个民族都列上,但比较累,也考虑不了世界上的所有民族)。
民族序列
10)对G列,H列,I列重复第9)步操作,来源分别输入“=学历”、“=出生地”、“=部门”。G列可以选择“其它”(也是设定值之一),H列、I列则局限在设定值中。所有的序列均可照此办理,比如:婚否,健康状况,政治面貌,职称等等。
11)选择D列,“单元格设置”中定为“文本”格式,在“数据有效性”中选择“文本长度”、“等于”、“18”,可以添加“输入信息”和“出错警告”,但“出错警告”必须选择“停止”样式,必须输入18位,因为我们要计算使用。D2中输入身份证号测试一下,适当调整列宽以全显示,不要影响E列。
12)选择E2,输入提取公式“=iferror(date(mid(d2 7 4) mid(d2 11 2) mid(d2 13 2)) "")”,将身份证的出生日期提取出来。插入iferror函数是因为没有输入身份证时,公式显示的“#VALUE!”不好看。
13)选择F2,输入公式“=iferror(year(today())-mid(d2 7 4) "")”,用今天日期的年减去出生年。
14)在备注中输入“无”,第一列便填写完成了,把此行当做示例。选择A~J列,“设置单元格格式”,选择水平对齐“居中”,勾选自动换行后确定。在“保护”标签,把“锁定”的勾去掉。
15)现在我们要防止别人破坏,首先在“序列”工作表标签上点击右键,选择“隐藏”。
16)选择A1:J2,“设置单元格格式”中把“锁定”的再加上。
17)点击“审阅”选项卡的“保护工作表”,可以设置一个密码,然后确定。上述锁定的区域被保护,不得进行修改了。但E2,F2的公式可以向下填充,或者“选择性粘贴”把公式粘贴过来使用。
18)更高级的做法是把E、F列的公式提前填充好,并把E、F列设定设为“锁定”一块儿保护起来,别人就动不了了,保护之前自己美化吧。还可以按上节的“条件格式”找找最老的3个人和最年轻的3个人,找找姓名有没有同名同姓的(重复值)
结果
(待续)