EXCEL数据填充信息录入.docx

1、EXCEL数据填充信息录入第三章 EXCEL数据填充本章主要是撑握在Excel中实现等差序列、等比序列、日期序列等有规律的数据填充，以及一些特殊数据输入的技巧，能够把批量数据在短时间内录入到Excel中。3.1人员基本信息录入 案例描述内蒙古呼和浩特“企业”组建成功，该企业有3000员工，办公室人员根据每位员工的档案汇总人员基本信息。打开“EXCEL0301.XLSX”工作簿，完成以下操作：为单位的员工编制职工号，职工号的规律是以NMG开头然后分别是NMG0001、NMG0002，以此类推，一直到NMG3000。填充每位职工的来源。填充每位职工的身份证号。填充每位职工的家庭所在地区的电话区号。

2、 知识要点分析在日常办公中如果快速的完成上面的数据填充，需要掌握自动序列填充、向下填充、向右填充、记忆式键入以及数字变文本输入。1自动序列。自动序列是指有规律的数据只需要填充第1个数，然后会自动按规律进行按行或列进行填充。如图3-1所示（1）经常用到的是非数值的序列，如填充星期一，自采自动序列会填充到星期日；填充了一月，会自动填充到十二月等等。（2）另外自动序列填充还经常用到公式中的复制填充。如果数据规模比较大，采用拖动填充柄会发现拖动的速度并不是很快，如果采用自动填充会准确的在填充的范围内容完成填充。图3-1 “序列”对话框2向上填充、向下填充、向左填充和向右填充。如图3-2所示。这四种填充

3、方式是指在已经有一个数据的前提下，向上、向下、向左、向右填充相同的数据，实际上也就是复制一个已有的数据。根据我们填充数据的习惯，一般向下或向右填充用的比较多，而向上和向左填充相对比较少。向下填充可以采用快捷键Ctrl+D，向右填充也可以采用Ctrl+R来实现。不习惯使用快捷键的也可以通过工具栏中的填充实现。如图所示，在M2单元格中填充800后，可以在M2单元格的基础上向上、向下、向左、向右分别填充相同的数据800。 图3-2 填充3记忆式键入。这种填充方式是指填充的数据已经填充过，可以通过ALT+光标键打开键入列表，然后通过光标键进行选择来完成填充。4数字变文本输入。这种填充方式主要是指不需要

4、计算的数据，或计算完成后的结果不具有任何意义的数据可以采用这种填充方式。比如电话号码、身份证号、手机号等等，这些数据在实际应用过程中进行算术四则运算没有任何意义，因此在输入时先输入（单引号）然后在输入相应用的数据。 案例实现打开“EXCEL0301.XLSX”中的“企业人员基本信息录入”，从中分析可以看出职工号可以采用自动填充来实现，职工来源可以通过向下填充或记忆式键入实现，身份证号和电话区号可以采用数字变文本输入来实现。如图3-3所示：图3-3 企业人员基本信息1 通过名称框输入A2：A3001选中员工作所有职工号，然后选择系列填充中的自动序列就完成了3000员工的自动编号。2 姓名为A2职

5、工来源与A1职工来源相同，直接按Ctrl+D即可完成。姓名为A3和A4的职工正常输入职工来源为天津和上海，A5的职工假设不是“上海”而是“天津”或“内蒙古”，可以按Alt+光标键打开键入列表，直接进行选择。3 假设A1的身份证号为152*9216619，直接进行输入后变成了1.52326E+17,而不是我们预期的结果，原因是Excel把身份证当成数字处理，数字位数过多而采用了科学计数法。对于这样的数据可以在前面输入（单引号）然后在输入相应用的身份证号，如姓名为A2的职工采用的就是这种输入方式。4 姓名为A1的职工在输入电话区号为0471后，Excel系统就把0471当成数字来处理，因而就把0省

6、略，看到的输入结果为471。在输入时也是采用先输入（单引号），然后再输入0471才能达到预期的输入效果如E3单元格。 问题思考 什么样的数据采用自动序列？ 什么样的数据在输入时候前面要加 （单引号）？ 什么样的数据按Ctrl+D输入，什么样的数据不能按Ctrl+D，而是按Alt+进行选择？ 企业人员基本信息录入中3000职工的姓名分别是A1，A2，到A3000是通过什么方式填充完成的？3.2办公室人员值班表设计 案例描述内蒙古呼和浩特“企业”办公室有6位工作人员，企业要求这6名职工每周一到周五的晚上轮回值班。周六日休息，不作值班安排。打开“EXCEL0302.XLSX”工作簿,设计并安排200

7、9年全年值班表。 表3-1 办公室工作人员 知识要点分析在企业的日常办公中进行值班安排是很常见的办公处理，如果不采用数据填充技巧与方法，制作该案例中的值班表也非常麻烦。这个案例涉及以下几个知识点。1日期序列。日期序列的填充包含按日填充、按工作日填充、按月填充、按年填充等。在实际操作过程中可以使用序列填充，如图3-4所示，也可以通过左键填充柄或右键填充柄实现。(1) 按左键拖动填充柄系统默认是按日填充，按住Ctrl+左键拖动填充柄是复制日期。如图所示，在A1单元格输入2008-1-1后，按住左键拖动填充柄到A9后日期是以日作为单进行填充的。在B1单元格输入2008-1-1后按住Ctrl+左键拖动

8、填充柄到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 单

9、击“Microsoft Office 按钮” ，然后单击“Excel 选项”。2 单击“常用”类别，然后在“使用 Excel 时采用的首选项”下单击“编辑自定义列表”。图3-8 自定义序列在使用自定义序列时，只要输入自定义序列中的某一个数据，如数学，然后通过自动填充或拖动填充柄就可以自动填充出 治、英语、计算机等数据。如图3-8所示 案例实现打开“EXCEL0302.XLSX”工作簿，其中有“办公室工作人员”和“2009年度值班表”两个工作表。从案例分析中可知道值班日期不填充星期六与星期日，在填充时选择的是按工作日填充。值班人姓名和值班人手机首先确定谁第一个值班，假设齐文山值第一个班，以后按办

10、公室工作人员的顺序依次值班。如图3-9所示。在“办公室工作人员”工作表中可以看到，姓名数据已经存在，而每个人的手机并没有输入。从中分析可以知道姓名可以通过导入数据直接变为自定义序列，而手机号码可以通过输入来转换为自定义序列。图3-9 2009年度值班表1打开“办公室工作人员”工作表，将所有人的姓名通过自定义序列中的导入序列将姓名定义为自定义序列。2通过手工在输入序列中输入办公室所有人员的手机，手机号码的顺序要与办公室人员的顺序一致。3回到“2009年度值班表”中选中A2单元格后，选择序列中的日期序列，如图所示，序列产生在选列，类型选择日期，日期单位选择工作日，终止值输入2009-12-31，然

11、后确定就可以将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，然后选择序列填充中的自动序列。通过上面的操作，就可以完成一个办公室人员的全年值班表设计制作。 问题思考 日期序列中如果不填充“年月日”形式的日期，而是填充“月日”形式或者是“星期”或者是“月份”能否完

12、成自动填充？ 自动序列如果不用回车作为数据间的分隔还可以用什么分隔？ 在填充每个值班人员的手机号码时，手机号码采用数值输入或采用文本输入后，在自动填充后的结果是否一样，原因是什么？ 如果把手机号码输入到“办公室工作人员”工作表中后，能否通过导入数据形式转换成自定义序列？3.3人员考勤表的制作 案例描述内蒙古呼和浩特“企业”有30位员工，每天早上要对员工进行考勤，考勤方法是通过考勤表进行登记，旷工采用进行标记，迟到采用标记，早退采用，病假采用标记，事假采用标记。打开“EXCEL0303.XLSX”工作簿,完成下列操作:制作一个考勤表，纸张采用A4纸横向。每页有职工号、姓名、及31天的考勤记录，3

13、0位员工都在一页纸上，一年的考勤总计12页纸。 知识要点分析这个案例需要填充每个月的天数，因为一个月最多31天，其中有的月份可能是30天或28天等情况，因此在制作时，可以不考虑具体的月份，每个月都按31天制作，不足31天的空闲不使用。另外在这31天要求都在一页纸上显示，这就需要对表格的列的宽度进行调整，使整个表都在一页纸上能够显示出来。 案例实现1打开“考勤表”工作簿，选择页面布局选项卡，如图3-12所示。设置纸张大小为A4纸，纸张方向为横向。图3-12 页面布局选项卡 2在C1单元格输入1，然后在开始选项卡编辑填充系列，在序列对话框中终止值输入31后确定。如图3-13所示。图3-13 “序列

14、”对话框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后通过开

15、始单元格格式自动调整列宽能否实现最终效果，有什么区别？ 进行打印预览后在AG列的左侧出现了一条纵向虚线是什么？ 在进行打印预览时，如何通过鼠标拖动来调整纸张的边距。 职工号1001到1030是如何完成填充的？3.4企业营业税模板制作 案例描述内蒙古呼和浩特“企业”由专人进行企业纳税管理，在纳税管理中每个月需要交纳营业税，该企业的营业税税率为5%，每个月份只需要输入企业营业收入，营业税的自动计算完成。打开“EXCEL0304.XLSX”工作簿，完成下列操作：企业除营业额数据外其它的所有数据不允许修改和删除。企业的营业额如果输入负数据系统拒绝接收，并显示“输入错误”。输入数据和计算后的营业税采用货

16、币格式。全年汇总自动计算。制作企业管理12个月的营业税的模板，每年计算企业营业税只需通过模板生成即可。 知识要点分析在企业管理过程中，完成上面的表格设计与制作需要掌握下面知识点。1.去掉单元格的锁定。选中允许修改的数据的单元格后，单击右键 设置单元格格式 保护 把锁定的对勾去掉。如图3-17所示。图3-17 去掉单元格锁定2.保护工作表。在功能区中选择审阅选项卡 更改 保护工作表。如图3-18用户也可以加入保护密码。图3-18 保护工作表 案例实现打开 “EXCEL0304.XLSX”工作簿，按下列顺序完成设计与制作。1在名称框中输入B2:B13，选中允许修改的单元格，然后去掉锁定的对勾。2在

17、功能区选择数据选项卡 数据工具 数据有效性，设置有效条件，如图3-19所示。图3-19 “数据有效性”对话框（一）在数据有效性选项卡中选择出错警告选项卡，按图3-20所示输入。图3-20 “数据有效性”对话框（二）3在名称框中重新输入B2:C13重新选择单元格，然后在功能区选择开始 样式 单元格样式 数字格式中的货币。4选中A14和B14两个单元格，在功能区中选择开始 对齐方式 合并后居中。5选中C14单元格定义公式SUM(C2:C13),如图3-21所示图3-21 企业营业税模板6给表格加上表线7点开Office按钮，选择保存或另存为，文件名输入“企业营业税”，保存类型选择“Excel模板”

18、然后保存。如图3-22所示。图3-22 保存为模板8保存后，关闭模板，选择Office按钮，选择新建 我的模板，就能看到已经制作的“企业营业税”模板，然后确定。如图3-23所示图3-23 新建9确定后，输入企业的营业额就能完成某个年度各个月的营业税的计算。到下一个年度，再选择新建 我的模板 企业营业税就可以开始一个新年度的企业营业税的计算。如图3-24所示。图3-24 企业营业税 问题思考 工作表如何设置保护？与工作簿保护有什么区别？ 通过快捷键CTRL+N能否使用创建的模板？ 日期数据有效该如何设置？3.5商品销售表的制作 案例描述打开“EXCEL0305.XLSX”工作簿，为内蒙古呼和浩特

19、“企业”商品销售单价如图3-25所示，要求根据商品销售单价，商品数量，如图3-26所示，计算商品销售金额。图3-25 商品销售单价图3-26 商品数量商品名通过下拉菜单选择输入。单价依据所输入的商品名自动添加。根据数量列（E列）中产品销售数量，在金额列（F列）中自动计算产品的销售金额，若产品销售数量为0，金额为空。 知识要点分析在Excel的数据填充中，除了上面讲到的几种填充方法外，为单元格添加下拉箭头选择输入的数据也经常使用，例如，性别的输入。如图3-27所示：图3-27 通过下拉菜单输入具体操作如下：选中A列，在功能区选择数据选项卡数据工具数据有效性，弹出“数据有效性”对话框，有效性条件选

20、择“序列”，来源的输入方式有两种：1. 直接在文本框中输入“男，女”。如图3-28所示：图3-28 “数据有效性”对话框2. 数据区域定义名称。在B1,B2单元格分别输入“男”，“女”。选中B1,B2，在名称框中输入“性别”，即对B1,B2这个数据区域定义了一个名称。如图3-29所示。图3-29 定义名称选中A列，打开数据有效性对话框，有效性条件选择“序列”，来源中输入“=性别”，如图3-30所示：图3-30 “数据有效性”对话框 单击确定，即可实现对A列单元格添加下拉箭头。 案例实现打开“EXCEL0305.XLSX”中的“商品销售表的制作”。1 在“商品单价”工作表中，通过名称框输入B2：

21、B6选中所有商品，然后在名称框中输入“商品名”，即为B2：B6这个数据区域定义名称。如图3-31所示：图3-31 定义名称框2 在“商品数量”工作表中，在名称框中输入C2：C6，选中单元格，在功能区选择数据选项卡数据工具数据有效性，有效性条件选择“序列”，来源中输入“=商品名”（或输入“电视机，电冰箱，自行车，数码相机，MP3”），单击确定。为选中单元格添加下了箭头。3 单击D2单元格，在编辑栏中输入公式“=IF($C2=电视机,商品!$C$2,IF($C2=电冰箱,商品!$C$3,IF($C2=自行车,商品!$C$4,IF($C2=数码相机,商品!$C$5,IF($C2=MP3,商品!$C$

22、6)”，实现单价依据所输入的商品名自动添加。如图图3-32所示：图3-32 计算单价4 单击F1单元格，在编辑栏中输入公式“=IF($E20,$D2*$E2,)”，实现销售额根据输入产品销售数量的计算。计算结果如图3-33 所示：图3-33 商品销售金额 问题思考 EXCEL中已经存在的数据在数据有效性中如何定义为序列？ 3.6外部数据的导入 案例描述内蒙古呼和浩特“企业”职工信息现有多种数据格式，现要求将数据导入Excel中，打开“EXCEL0306.XLSX”完成下列操作：从Access导入外部数据到Excel从文本文件导入外部数据到Excel。从现有连接导入外部数据到Excel。结果如图

23、3-34所示：（图中5至21行被隐藏）图3-34 职工信息 案例实现在日常工作中，经常会遇到把Excel以外的其他格式的数据导入Excel中。具体处理方法如下：1. 从Access导入外部数据到Excel中。1在功能区选择数据选项卡获取外部数据自Access，弹出“选择数据源”对话框，选择需要导入的Access文件“职工.mdb”，单击“打开”；如图3-35所示：图3-35 “选择数据源”对话框2在弹出的“导入数据”对话框中可以选择数据在工作簿中的显示方式，选择数据的存放位置现有工作表或新工作表，单击确定，如图3-36所示：图3-36 “导入数据”对话框3单击确定后导入的数据，Excel自动加

24、了表样式，并处于数据筛选状态，对此可以根据个人对数据的要求自行调整。2. 从文本文件导入外部数据到Excel中1在功能区选择数据选项卡获取外部数据自文本，弹出“导入文本文件”对话框，选择需要导入的文本文件“职工.txt”，单击“打开”，如图3-37所示：图3-37 “导入文本文件”对话框2进入文本导入向导，如图3-38、3-39、3-40所示：步骤一：默认选择，单击下一步；图3-38 文本导入向导步骤一步骤二：可以选择文本中数据分隔符号（Tab健，分号，逗号，空格，其他），本题中文本文件中数据以逗号作为间隔，选择逗号，单击下一步；图3-39 文本导入向导步骤二步骤三：可以选择数据导入Excel

25、后的格式（常规，文本，日期），本题中选择“常规”，单击完成。图3-40 文本导入向导步骤三3弹出“导入数据”对话框，选择数据放置位置，单击确定，即可导入文本数据。如图3-41所示。图3-41 “导入数据”对话框3. 从现有连接导入外部数据到Excel中1在功能区选择数据选项卡获取外部数据现有连接，弹出“现有连接”对话框，单击“浏览更多”，如图3-42所示。图3-42 “现有连接”对话框2弹出“选取数据源”对话框，单击“新建源”，如图3-43所示。图3-43 “选择数据源”对话框3弹出“数据连接向导”对话框，选择“ODBC DSN”,单击下一步，如图3-44所示。图3-44 “数据连接向导”对话

26、框（一）4本题数据是Visual FoxPro数据，选择Visual FoxPro Tables项，单击下一步。如图3-45所示。图3-45 “数据连接向导”对话框（二）5弹出“Configure Connection”对话框，Path文本框中输入地址“c:外部数据”，单击Ok。如图3-46所示。图3-46 “Configure Connection”对话框6在“数据连接向导”对话框中可以看到要添加的“职工.dbf”,单击完成。如图3-47所示。图3-47 “数据连接向导”对话框（三）7弹出“导入数据”对话框，选择数据在工作簿中的显示方式，数据的存放位置。如图3-48所示。图3-48 “导入数

27、据”对话框8单击确定后导入的数据，Excel自动加了表样式，并处于数据筛选状态，对此可以根据个人对数据的要求自行调整。 问题思考 本章课外实验一、打开“KS0301.XLSX”，按下列要求完成上机实验。(一) 在Sheet1中完成下列操作。1. 在A1单元格输入2，通过等差序列填充到A9，步长是1。2. 在B1单元格输入2，通过等比序列填充到B9，步长是2。3. 在C1单元格输入2，通过复制填充到C9。4. 将B1单元格设为红底黄字，然后填充格式到B9。5. 在D1单元格输入2，分别向下和向右填充2。6. 在E1单元格输入2，设为蓝底黄字，通过值填充到E9。(二) 在Sheet2中完成下列操作

28、。1. 在A1单元格输入1998-1-1，然后用填充柄填充到第8行。2. 通过日期序列将A1单元格拖动到E1。3. 通过日期序列中的天数将A2单元格拖动到E2。4. 通过日期序列中的月将A3单元格拖动到E3。5. 通过日期序列中的年将A4单元格拖动到E4。6. 通过日期序列中工作日将A5单元格拖动到E5。(三) 在Sheet3中完成下列操作。1. 在A1单元格输入1月，用填充柄拖动到A15。2. 在B1单元格输入星期一，用填充柄拖动到B15。3. 在C1单元格输入A1，用填充柄拖动到C15。4. 在D1单元格输入“蒙A1001”，用填充柄拖动到D15。5. 从E1单元格开始向下分别输入1/2、

29、3 1/2 。6. 在F1输入自己的身份证号。7. 在F2输入电话的区号。(四) 在Sheet4中完成下列操作1. 将第一行和第一列全部输入1。2. 自定义一个“数学、语文、政治、计算机”序列进行填充。3. 输入一个1/2转换成小数。4. 输入一个0.2转换成分数，百分数。5. 输入一个2007-1-1转换成2007年1月1日。6. 输入123，在前面加入￥符号。二、打开“KS0302.XLSX”，按下列要求完成上机实验。1. 在Sheet1中制作一个5行5列的表，要求外框为蓝色，内边框为红色。2. 如果制作完成后，填充到剩余的9个工作表中如何完成？3. 新建一个工作簿，要求工作簿有自动产生100个工作表。4. 在新建立的工作簿内的Sheet1中制作上面5行5列的表，制作完成以后要求100个工作表中全都有制作的表格。将其存为“KS0303.XLSX”。5. 6.