第02讲（第3章） Excel数据采集

模块一：EXCEL技术基础第三章Excel数据采集上节回顾工作簿与工作表工作窗口：快速访问工具栏、名称框基本设置：自动保存设置、模版、工作表默认数基本操作：打开方式、标签颜色、窗口拆分与冻结、自动套用格式Excel条件格式：条件格式5种规则保护Excel文档：加密与备份、保护工作表、保护工作簿上节回顾复习提问名称框能实现哪些功能？窗口冻结可以实现哪些操作？条件格式有哪些规则？保护Excel文档，上次课讲了哪几个？目录页CONTENTSPAGE外部数据导入数据填充数据验证数据采集相关应用本章内容学习目标LEARNINGTARGET外部数据导入：自文本、自网站、自数据库数据填充：自动填充、序列填充、自定义序列数据验证应用：制作收款凭证、差旅报销单、购置税汇总表重点掌握外部数据导入1外部数据导入Excel里面的数据，除了手工自动录入满足日常工作学习之用外，还可以导入文本文件、word、各种数据库(如Access)等不同来源的数据。操作：数据－获取外部数据获取外部数据外部数据导入例如：我们希望通过单击一个按钮，即可从Web站点上直接获取数据（如最新的股票报价信息），将其导入到Excel工作表中进行查看、分析，并希望以后更新数据以与Web页上的最新数据保持一致，可以使用Web查询来完成此操作。其操作步骤如下：在“数据”菜单上，点击“获取外部数据”，然后单击“自网站”，弹出“新建Web查询”对话框。在Web地址一栏输入你要导入的Web地址，选择要导入的部分及Web页的格式，按确定。在对话框中选择数据导入的位置，例如“=$A$7”，那么满足条件的数据便会从数据库中自动提取并从单元格$A$7开始向下填充，如果选择新表，则自动创建一张新工作表用于存放导入的数据，按“确定”就可以完成网页数据的导入。此后，要更新数据与Web页上的最新数据保持一致，单击“数据”菜单上的“全部刷新”命令，或右键“刷新”。1.自网站外部数据导入四种web查阅选项，选择导入Excel的格式：【无】指只导入文字【仅RTF格式】指导入格式但不包含链接【完全HTML格式】指导入文字、格式和链接。1.自网站外部数据导入Excel里面的数据，除了手工自动录入满足工作学习之用外，还可以导入文本文件、word、各种数据库(如Access)等不同来源的数据。2.自文本外部数据导入Excel中有个专门的工具用于将数据库中的数据导入Excel中：MicrosoftQuery是用于将数据从外部数据源检索到Excel中的一种程序。通过使用Query可以从企业的数据库和文件中检索数据，而不必重新键入需要在Excel中分析的数据。也可以在每次更新数据库时，自动通过源数据库中的数据来更新Excel报表和汇总数据。

MicrosoftQuery可访问的数据库类型有：MicrosoftAccess、MicrosoftSQLServer和Oracle等数据库。也可以从Excel清单和文本文件中检索数据。3.自数据库外部数据导入当从外部导入的外部数据源有变时，Excel需要从不同数据源重新读取数据，这就需要用到Excel的功能“全部刷新”。该功能菜单：数据-全部刷新-全部刷新。4.刷新数据外部数据导入上机实践完成实验报告：第1-2题操作数据填充2数据填充在Excel表格中填写数据时，经常会遇到一些在结构上有规律的数据，例如1997、1998、1999；星期一、星期二、星期三等。对这些数据我们可以采用填充技术，让它们自动出现在一系列的单元格中。填充功能是通过“填充柄”或“填充序列对话框”来实现的。填充柄：用鼠标单击一个单元格或拖曳鼠标选定一个连续的单元格区域时，框线的右下角会出现一个黑点，这个黑点就是填充柄。填充序列对话框：单击“编辑”菜单下的“填充”中的“序列”即可。序列填充填充柄数据填充1.序列填充用鼠标单击一个单元格时，框线的右下角会出现一个黑点，这个黑点就是填充柄。拖动填充柄完成序列填充。输入数据类型直接拖动填充柄按下CTRL同时拖动填充柄文本相同数值填充相同数值填充数字相同数值填充序列填充，步长为1日期相同数值填充以日序列填充，步长为1时间相同数值填充以时序列填充，步长为1数据填充1）等差等比序列填充在单元格中输入第一个数据，开始-编辑-填充-序列，打开序列对话框中根据输入数据选择“等差序列”或“等比序列”，再根据情况设置“步长值”（递增：正数，递减：负数），最后单击“确定”退出。1.序列填充数据填充2）日期序列填充在单元格中输入第一个日期，右键拖动该单元格填充柄到最后一个单元格，快捷菜单中选择“序列”，打开的对话框中选择“日期”，继续选择“日期单位：年、月、日、工作日”再根据情况设置“步长值”（递增：正数，递减：负数），最后单击“确定”退出。1.序列填充数据填充3）自定义序列填充使用Excel录入数据时，经常会需要输入一系列具有相同特征的数据。例如周一到周日、一组按一定顺序编号的产品名称等。如果经常用到同样的数据，可以将其添加到填充序列列表中，以方便日后的使用。1.序列填充数据填充1）不同单元格输入相同数据选中多个单元格–输入数据–快捷键“Ctrl+Enter”快速填充。2）记忆式输入选项-高级-编辑选项-为单元格值启用记忆式键入选中单元格按右键–从下拉列表中选择ALT+向下箭头2.其他填充方式数据填充3）分数输入如输入分数1/2，则必须在单元格中输入“0½”才可以显示效果。4）填充至同组工作表如果想在不同工作表的相同单元格中输入相同数据时，则先选中这些工作表，点击“开始－编辑－填充－成组工作表”。这样可在这些表中输入一次数据，不同的工作表的对应位置也会产生相同的数据。2.其他填充方式数据填充上机实践完成实验报告：第3-4题20分钟数据验证（检验）3数据验证EXCEL中数据有效性的设置数据制作过程中需要对某些单元格设置下拉列表,便于数据的选择和限制其他无效数据的输入，需要对数据的有效性进行设置。数据－数据验证1.数据验证数据验证1）直接自定义序列有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：优秀，良好，合格，不合格。单元格能够像下拉框一样，让输入者在下拉菜单中选择就可以实现输入。操作步骤：在“允许”下拉菜单中选择“序列”；在“数据来源”中输入“优秀，良好，合格，不合格”（注意用英文状态下的逗号分隔）；选上"忽略空值"和"提供下拉菜单"两个复选框。点击【输入信息】选项卡，选上“选定单元格显示输入信息”，在“输入信息”中输入“请在这里选择”。

2.下拉菜单的实现数据验证2）利用表内数据作为序列源有时候序列值较多，直接在表内打印区域外把序列定义好，然后引用。操作步骤：先在同一工作表内的打印区域外要定义序列填好(假设在D2:D3），如“男，女”，选择【设置】选项卡，在"允许"下拉菜单中选择"序列"；“来源”栏点击右侧的展开按钮（有一个红箭头），用鼠标拖动滚动条，选中序列区域在D2:D3；选上"忽略空值"和"提供下拉菜单"两个复选框。点击【输入信息】选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。2.下拉菜单的实现数据验证3）联动实现：思考动手。实验报告3（2）2.下拉菜单的实现5分钟数据验证3.数据唯一性校验员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。操作步骤：选中需要建立输入身份证号码的单元格区域（如C2至C16列），执行“数据→数据验证”命令，在【设置】标签下，按“允许”右侧的下拉按钮，选择“自定义”选项，然后在下面“公式”方框中输入公式：=COUNTIF(C:C,C2)=1，确定返回。点击【出错警告】选项卡，在“样式”列表框选上“警告”，在“错误信息”中输入“身份证号码有重复，请重新输入”。为了将这个设置应用到整个C列（除了字段名称所在的单元格即C2单元格），可用填充柄工具向下拖动将公式复制到C列其他的单元格。注：当有多个条件时，可用AND/OR：=and(COUNTIF(C:C,C2)=1,len(C2)=18)=true数据验证4.自动实现输入法中英文转换有时需在不同行或不同列之间要分别输入中文和英文。我们希望Excel能自动实现输入法在中英文间转换。操作步骤：假设在A列输入学生的中文名，B列输入学生的英文名。先选定B列，点击进入“数据→数据验证”，进入【输入法模式】，在“模式”下拉菜单中选择“关闭（英文模式）”；然后再“确定”。数据验证5.圈释无效数据有时，输入了错误的数据，需要醒目注释时，可以使用该功能。数据验证上机实践完成实验报告：第5题15分钟4数据采集相关应用实际应用1.制作会计凭证背景知识1：企业收款凭证收款凭证：是用来记录现金和银行存款收款业务的会计凭证，它是由出纳人员根据审核无误的原始凭证收款后填制的。在借贷记账法下，在收款凭证左上方所填列的借方科目，应是“现金”或“银行存款”科目。在凭证内所反映的贷方科目，应填列与“现金”或“银行存款”相对应的科目。金额栏填列经济业务实际发生的数额，在凭证的右侧填写所附原始凭证张数，并在出纳及制单处签名或盖章。实际应用1.制作会计凭证背景知识1：企业收款凭证实际应用1.制作会计凭证问题知识点会计凭证表格基本操作单元格合并边框列宽文字方向格式扩充：科目下拉数据验证合计公式实际应用2.差旅费报销单问题知识点报销单表格基本操作数据验证保护单元格公式单元格格式填充职别：“助教、讲师、副教授、教授”所有金额：保留2为小数的数字格式合计公式，调整合计金额格式为“会计专用”在其他费用中批量输入5笔记录，金额均为1元实际应用3.汽车购置税计算问题知识点购置税汇总表购置税购置税额是征收汽车售价（不含税价）的10%，公式为：新车购置税额=计税价格*10%汽车经销商对外提供的价格往往是含税价格。因此，计算新车购置税前需要先去掉增值税1