Office高级应用（第2版 · 微课版 · 含OfficeAI助手）课件 第6章-数据的准备

第2篇Excel2016数据处理与分析

—数据的准备Office2016高级应用湖南师范大学·刘相滨内容提要第6章数据的准备6.1

数据的录入6.2

数据的有效性验证6.3

数据的导入6.1数据的录入数据格式与常规录入数据格式：常规、数值、分数、文本、日期、时间、会计专用、货币等单元格的数据格式决定了其内数据的类型及显示方式自动序列填充复制填充、递增填充、填充柄等可实现连续、有规律的数据的录入自定义序列填充没有规律而又经常输入的数据先定义序列，再使用例如壹、贰、叁、……主管、综合员、组长、员工6.2数据的有效性验证设置验证规则，减少数据的录入错误，提高工作效率限定数据的类型、数据的取值范围，规范数据，…如果录入错误数据，进行提醒，拒绝录入不小心输入了一个错误的日期值，例如“2023/17/1”输入商品的类别时，输入“优盘”还是“U盘”？对商品进行编号时，出现了重复的编号？在进货清单、销售清单中录入数据时，录入一个不存在的商品编号，例如UD-KS-001，错录入为UD-SK-001（不存在），系统是否能自动判别出来？序列输入录入商品的类别“电子称”OR“电子秤”？“存储卡”OR“储存卡”？“U盘”OR“优盘”？预先定义预先定义一个取值序列然后设置“数据验证”，限定用户只能在此序列中选择一项作为输入通过下拉列表框的形式提供选择，简捷、方便序列输入的方法直接定义序列单元格引用定义序列利用名称创建联动下拉列表利用表格创建动态下拉列表1.直接定义序列“数据验证”对话框，在“序列”数据的“来源”中直接输入各数据项名称，并以半角英文逗号分隔2.单元格引用定义序列预先在某连续的单元格中录入序列数据（按列存放），然后在“数据验证”对话框中定义序列时，引用该单元格区域灵活、方便，可改动数据，调整顺序，自动更新3.利用名称创建联动下拉列表问题不同类别商品的品牌是不同的，如何为每个类别的商品定义品牌序列？不同的商品类别在不同的单元格区域分别定义相应的品牌序列？数据验证怎么设置？将所有商品的品牌定义为一个序列？如何区分某个品牌是哪类商品？品牌太多了，不方便选择，效率低下使用名称，创建联动序列商品类别的选择发生变化时，品牌的下拉列表项随之自动变化，仅列出与选定类别相对应的品牌什么是名称？名称用来标识单元格、单元格区域、表格或常量值的单词或字符串若定义名称“商品类别”来引用区域“基本资料表!$B$2:$B$9”，则名称“商品类别”就是区域“基本资料表!$B$2:$B$9”的一个别名好处引用方式简捷便于理解、记忆、书写、使用和维护特别是，在公式中，如果善用名称，则可以极大地提高公式的可读性定义名称先定义，后使用标准定义法选定一个名称区域→定义名称→“新建名称”对话框批量定义法选定多个名称区域→根据所选内容创建→“根据所选内容创建”对话框名称框定义法选定一个名称区域→在名称框中直接输入名称再回车查看定义的名称创建名称联动序列定义好名称后，就可以创建名称联动序列假设已为商品类别和品牌分别定义好了名称在商品信息表中分别为商品类别（C列）和品牌（D列）创建名称联动序列=INDIRECT($C2&"品牌")

INDIRECT函数返回由文本字符串（参数）指定的引用（名称、单元格区域等），并立即对引用进行计算，显示其内容参数说明$C2：取单元格C2的值（某个商品类别，假设为“手机”）如果仅为D2单元格设置，可为C2或$C2、$C$2（因为行、列均不变），如果为D列所有单元格设置，则应为C2或$C2（因为列不变，但行会变化）&：字符串联接运算符"品牌"：常量字符串运算结果：手机品牌而“手机品牌”为已定义好的名称，引用位置“=基本资料表!$D$2:$D$7”，为各种手机品牌所在的单元格区域因此，在D2单元格的下拉列表中，将显示“手机品牌”序列当C2单元格选取的商品类别发生变化时，INDIRECT函数运算结果相应变化，从而联动=INDIRECT($C$2&"品牌")

4.利用表格创建动态下拉列表新问题随着业务的拓展，可能会淘汰一些品牌、新增一些新的受顾客欢迎的品牌因此，需要构造一个动态变化的序列单元格引用、名称：序列内容可删除、修改、调整顺序，但不能增加因为引用的区域是固定的，所以项数是固定的而表格可以动态增、减项数表格表格：包含相关数据的一系列数据行，第一行一般是标题行表格可以看成是一个规则的二维表格区域创建表格首先，选择整个表格内容的数据区域，包括标题行两种方法插入法：默认表格样式套用样式法：套用预定义的表格样式创建表格的要求为每一列设置列标题，且必须是文本格式，唯一每一列的数据除列标题外应具有相同的数据类型表格中不能随意放置空行和空列如果在一张工作表中，除表格外还有其它数据，表格与其他数据间至少要留出一个空列和一个空行，以便在执行排序、筛选或进行分类汇总等操作时检测和选定表格表格中信息的引用可以使用表格名称引用表格的数据区域在编辑公式时，若输入表格名称+[，例如“商品信息表[”，则会给出提示信息：引用表格中的某一列数据引用表格的全部数据引用表格的标题行引用表格的汇总行（若存在）引用编辑单元格所在的当前行@=COUNTIF(商品信息表[商品编号],商品信息表[@商品编号])4.利用表格创建动态下拉列表假设已为商品类别、品牌定义表格：商品类别表、手机品牌表等修改名称“手机品牌”的引用位置“=手机品牌表”所有品牌的名称定义其引用位置采用表格的引用方式更新后，则通过名称定义“数据验证”的单元格，其下拉列表就能自动更新相应表格中增加的数据自定义有效性条件新问题新增商品、进货记录等，如何确保所设计的商品编号、进货单号是合法且唯一的呢？例如：商品编号PH-HW-001商品编号唯一、长度为9位第1-2位为类别代码，第4-5位是品牌代码，最后3位是序号在设置“数据验证”时，无法实现如此复杂的限制要求需要自定义有效性条件才能检查出非法输入自定义有效性条件6.3数据的导入问题有时，有些需要处理的批量数据，其来源不是Excel表或者Word表格Excel支持从文本文件、网站，以及Access、S