EXCEL数据填充信息录入

第三章 EXCEL 数据填充本章主要是撑握在 Excel 中实现等差序列、等比序列、日期序列等有规律的数据填充，以及一些特殊数据输入的技巧，能够把批量数据在短时间内录入到 Excel 中。3.1 人员基本信息录入 案例描述内蒙古呼和浩特“企业”组建成功，该企业有 3000 员工，办公室人员根据每位员工的档案汇总人员基本信息。打开“EXCEL0301.XLSX”工作簿，完成以下操作：为单位的员工编制职工号，职工号的规律是以 NMG 开头然后分别是NMG0001、NMG0002，以此类推，一直到 NMG3000。填充每位职工的来源。填充每位职工的身份证号。填充每位职工的家庭所在地区的电话区号。 知识要点分析在日常办公中如果快速的完成上面的数据填充，需要掌握自动序列填充、向下填充、向右填充、记忆式键入以及数字变文本输入。1自动序列。自动序列是指有规律的数据只需要填充第 1 个数，然后会自动按规律进行按行或列进行填充。如图 3-1 所示（1）经常用到的是非数值的序列，如填充星期一，自采自动序列会填充到星期日；填充了一月，会自动填充到十二月等等。（2）另外自动序列填充还经常用到公式中的复制填充。如果数据规模比较大，采用拖动填充柄会发现拖动的速度并不是很快，如果采用自动填充会准确的在填充的范围内容完成填充。图 3-1 “序列”对话框2向上填充、向下填充、向左填充和向右填充。如图 3-2 所示。这四种填充方式是指在已经有一个数据的前提下，向上、向下、向左、向右填充相同的数据，实际上也就是复制一个已有的数据。根据我们填充数据的习惯，一般向下或向右填充用的比较多，而向上和向左填充相对比较少。向下填充可以采用快捷键 Ctrl+D，向右填充也可以采用 Ctrl+R 来实现。不习惯使用快捷键的也可以通过工具栏中的填充实现。如图所示，在 M2 单元格中填充 800 后，可以在 M2 单元格的基础上向上、向下、向左、向右分别填充相同的数据800。图 3-2 填充3记忆式键入。这种填充方式是指填充的数据已经填充过，可以通过 ALT+光标键打开键入列表，然后通过光标键进行选择来完成填充。4数字变文本输入。这种填充方式主要是指不需要计算的数据，或计算完成后的结果不具有任何意义的数据可以采用这种填充方式。比如电话号码、身份证号、手机号等等，这些数据在实际应用过程中进行算术四则运算没有任何意义，因此在输入时先输入（单引号）然后在输入相应用的数据。案例实现打开“EXCEL0301.XLSX”中的 “企业人员基本信息录入” ，从中分析可以看出职工号可以采用自动填充来实现，职工来源可以通过向下填充或记忆式键入实现，身份证号和电话区号可以采用数字变文本输入来实现。如图 3-3 所示：图 3-3 企业人员基本信息1 通过名称框输入 A2：A3001 选中员工作所有职工号，然后选择系列填充中的自动序列就完成了 3000 员工的自动编号。2 姓名为 A2 职工来源与 A1 职工来源相同，直接按 Ctrl+D 即可完成。姓名为 A3和 A4 的职工正常输入职工来源为天津和上海， A5 的职工假设不是 “上海”而是“天津”或“内蒙古” ，可以按 Alt+ 光标键打开键入列表，直接进行选择。3 假设 A1 的身份证号为直接进行输入后变成了1.52326E+17,而不是我们预期的结果，原因是 Excel 把身份证当成数字处理，数字位数过多而采用了科学计数法。对于这样的数据可以在前面输入 （单引号）然后在输入相应用的身份证号，如姓名为 A2 的职工采用的就是这种输入方式。4 姓名为 A1 的职工在输入电话区号为 0471 后，Excel 系统就把 0471 当成数字来处理，因而就把 0 省略，看到的输入结果为 471。在输入时也是采用先输入（单引号） ，然后再输入 0471 才能达到预期的输入效果如 E3 单元格。 问题思考 什么样的数据采用自动序列？ 什么样的数据在输入时候前面要加 （单引号）？ 什么样的数据按 Ctrl+D 输入，什么样的数据不能按 Ctrl+D，而是按 Alt+进行选择？ 企业人员基本信息录入中 3000 职工的姓名分别是 A1，A2 ，到 A3000 是通过什么方式填充完成的？3.2 办公室人员值班表设计 案例描述内蒙古呼和浩特“企业”办公室有 6 位工作人员，企业要求这 6 名职工每周一到周五的晚上轮回值班。周六日休息，不作值班安排。打开“EXCEL0302.XLSX”工作簿,设计并安排 2009 年全年值班表。表 3-1 办公室工作人员 知识要点分析在企业的日常办公中进行值班安排是很常见的办公处理，如果不采用数据填充技巧与方法，制作该案例中的值班表也非常麻烦。这个案例涉及以下几个知识点。1日期序列。日期序列的填充包含按日填充、按工作日填充、按月填充、按年填充等。在实际操作过程中可以使用序列填充，如图 3-4 所示，也可以通过左键填充柄或右键填充柄实现。(1) 按左键拖动填充柄系统默认是按日填充，按住 Ctrl+左键拖动填充柄是复制日期。如图所示，在 A1 单元格输入 2008-1-1 后，按住左键拖动填充柄到 A9 后日期是以日作为单进行填充的。在 B1 单元格输入 2008-1-1 后按住 Ctrl+左键拖动填充柄到 B9 后，日期是进行了复制。如图 3-5 所示：图 3-4 “序列”对话框 图 3-5 日期填充(2)右键拖动填充柄后会弹出快捷菜单。如图 3-6 所示。在图中的 A 列选择的是以天数填充；在 B 列选择的是以工作日进行填充，其中 2008 年的 1 月 5 日和 1 月 6 日分别是星期五和星期六而不进行填充；C 列是以月进行填充；D 列是以年进行的填充。如果选择快菜单如图 3-7 中的最后一项序列，则会转到图 3-4 所示，通过设置来进行日期填充。图 3-6 日期填充图 3-7 快捷菜单2自定义序列。自定义序列是这些数据没有规律，但这些数据在使用上又有一定的规律。自定义序列可以采用手动输入来定义，也可以导入单元格数据来定义。具体操作：1 单击“Microsoft Office 按钮 ” ，然后单击“Excel 选项” 。2 单击“常用”类别，然后在 “使用 Excel 时采用的首选项”下单击“编辑自定义列表” 。图 3-8 自定义序列在使用自定义序列时，只要输入自定义序列中的某一个数据，如数学，然后通过自动填充或拖动填充柄就可以自动填充出 治、英语、计算机等数据。如图 3-8 所示案例实现打开“EXCEL0302.XLSX”工作簿，其中有 “办公室工作人员”和“2009 年度值班表”两个工作表。从案例分析中可知道值班日期不填充星期六与星期日，在填充时选择的是按工作日填充。值班人姓名和值班人手机首先确定谁第一个值班，假设齐文山值第一个班，以后按办公室工作人员的顺序依次值班。如图 3-9 所示。在 “办公室工作人员”工作表中可以看到，姓名数据已经存在，而每个人的手机并没有输入。从中分析可以知道姓名可以通过导入数据直接变为自定义序列，而手机号码可以通过输入来转换为自定义序列。图 3-9 2009 年度值班表1打开“办公室工作人员”工作表，将所有人的姓名通过自定义序列中的导入序列将手动输入按回车分隔各个数据按此处导入 EXCEL 中现有数据为自定义序列姓名定义为自定义序列。2通过手工在输入序列中输入办公室所有人员的手机，手机号码的顺序要与办公室人员的顺序一致。3回到“2009 年度值班表”中选中 A2 单元格后，选择序列中的日期序列，如图所示，序列产生在选列，类型选择日期，日期单位选择工作日，终止值输入 2009-12-31，然后确定就可以将 2009 年度除星期六和星期日所有的日期填充到 A 列。如图 3-10 所示。图 3-10 工作日的填充 图 3-11 值班人姓名填充4 填充后按 CTRL+可以看到 2009-12-31 的单元格作标为 A262，这样可以知道值班人姓名的填充是 B2 到 B262，值班人手机填充是 C2 到 C262。5 在名称框中输入 B2：B262，然后选择序列填充中的自动序列。如图 3-11 所示。6 在名称框中输入 C2：C262，然后选择序列填充中的自动序列。通过上面的操作，就可以完成一个办公室人员的全年值班表设计制作。问题思考 日期序列中如果不填充“年月日”形式的日期，而是填充“月日”形式或者是“星期”或者是“月份”能否完成自动填充？ 自动序列如果不用回车作为数据间的分隔还可以用什么分隔？ 在填充每个值班人员的手机号码时，手机号码采用数值输入或采用文本输入后，在自动填充后的结果是否一样，原因是什么？ 如果把手机号码输入到“办公室工作人员”工作表中后，能否通过导入数据形式转换成自定义序列？3.3 人员考勤表的制作 案例描述内蒙古呼和浩特“企业”有 30 位员工，每天早上要对员工进行考勤，考勤方法是通过考勤表进行登记，旷工采用进行标记，迟到采用标记，早退采用，病假采用标记，事假采用标记。打开“EXCEL0303.XLSX ”工作簿,完成下列操作:制作一个考勤表，纸张采用 A4 纸横向。每页有职工号、姓名、及 31 天的考勤记录，30 位员工都在一页纸上，一年的考勤总计 12 页纸。 知识要点分析这个案例需要填充每个月的天数，因为一个月最多 31 天，其中有的月份可能是 30 天或 28 天等情况，因此在制作时，可以不考虑具体的月份，每个月都按 31 天制作，不足 31天的空闲不使用。另外在这 31 天要求都在一页纸上显示，这就需要对表格的列的宽度进行调整，使整个表都在一页纸上能够显示出来。案例实现1打开“考勤表”工作簿，选择页面布局选项卡，如图 3-12 所示。设置纸张大小为 A4纸，纸张方向为横向。图 3-12 页面布局选项卡2在 C1 单元格输入 1，然后在开始选项卡 编辑填充系列，在序列对话框中终止值输入 31 后确定。如图 3-13 所示。图 3-13 “序列”对话框3在名称框中输入 C1：AG1 后，将 31 天全部选择，然后在开始单元格列宽中输入相应的数字进行打印预览，看 31 天是否在一页纸上显示。列宽的值经过多次试验性输入才能确定一个具体的数字，如图 3-14 所示。图 3-14 列宽4在名称框中输入 A1：AG31 选中所有单元格，加上边框。5在行号 1 上单击，选中第第一行，然后点右键插入一行。6在 A1 单元格输入“人员考勤表 ”。7在名称框中输入 A1：AG1 选中单元格后点右键选择设置单元格格式。8在设置单元格格式对齐水平对齐中选择跨列居中。如图 3-15 所示。图 3-15 设置单元格跨列居中结果如图 3-16 所示图 3-16 人员考勤表 问题思考 选中 C1：AG1 后通过开始单元格格式自动调整列宽能否实现最终效果，有什么区别？ 进行打印预览后在 AG 列的左侧出现了一条纵向虚线是什么？ 在进行打印预览时，如何通过鼠标拖动来调整纸张的边距。 职工号 1001 到 1030 是如何完成填充的？3.4 企业营业税模板制作 案例描述内蒙古呼和浩特“企业”由专人进行企业纳税管理，在纳税管理中每个月需要交纳营业税，该企业的营业税税率为 5%，每个月份只需要输入企业营业收入，营业税的自动计算完成。打开“EXCEL0304.XLSX ”工作簿，完成下列操作：企业除营业额数据外其它的所有数据不允许修改和删除。企业的营业额如果输入负数据系统拒绝接收，并显示“输入错误” 。输入数据和计算后的营业税采用货币格式。全年汇总自动计算。制作企业管理 12 个月的营业税的模板，每年计算企业营业税只需通过模板生成即可。 知识要点分析在企业管理过程中，完成上面的表格设计与制作需要掌握下面知识点。1.去掉单元格的锁定。选中允许修改的数据的单元格后，单击右键设置单元格格式保护把锁定的对勾去掉。如图