1、1,Excel 数据 管理与分析,第四章 数据管理与数据分析,2019/4/29,2,本章针对的问题,1. Excel 数据录入的容错性 2. 数据有序性管理 3. 快速的数据定位与编辑 4. 数据的分类与基本统计 5. 不同数据表的合并,2019/4/29,3,第四章 数据管理与分析,数据有效性设置 数据排序 数据筛选 数据查找与替换 数据分类汇总 数据合并计算,2019/4/29,4,4.1 数据有效性,系统的容错性 在系统遇到错误数据时，系统能够做出正常反应的能力。 系统的可用性、可靠性 保证进入系统的数据都有效的情况下，系统在有限时间内能够提供正确结果 Excel提高系统容错性的手段
2、通过设置数据有效性检验，使系统具有抵制非法和无效数据的能力。,2019/4/29,5,4.1 数据有效性,数据有效性设置包括四部分内容： 数据有效性条件设置（数据类型与格式设置） 数据录入操作提示设置（当光标指定单元格时所显示提示信息的设置） 数据录入错误警告提示设置（当录入数据违反有效条件要求时，提示信息设置） 输入法设置（指定单元格使用的输入法）目的：降低数据录入过程中的错误,2019/4/29,6,4.1 数据有效性,数据有效性设置的关系 数据有效性条件设置：决定有效的数据类型和数据格式，是系统判定数据有效的依据。 数据录入操作提示设置：当光标指定单元格时，为使用者显示关于输入数据格式内
3、容等提示信息，“友好型”设置。 数据录入错误警告提示设置：当录入数据违反有效条件要求时，所显示的警告信息和拒绝设置。,2019/4/29,1-3的关系,7,4.1 数据有效性,数据有效性条件设置（数据类型、范围、格式） 数据卡/数据工具组/数据有效性按钮,日期类型,2019/4/29,类型,条件,范围,8,4.1 数据有效性,数据有效性条件设置（数据类型与格式） 数据卡/数据工具组/数据有效性按钮/对话框,整数类型,2019/4/29,9,4.1 数据有效性,数据录入操作提示设置（当光标指定单元格时所显示提示信息的设置）,2019/4/29,09-01-02,10,4.1 数据有效性,数据录入
4、错误警告提示设置（当录入数据违反有效条件要求时，提示信息设置）,2019/4/29,警告！信息i 停止x,警告：提示、询问 信息：提示、询问 停止：拒绝继续输入,11,4.1 数据有效性,数据输入法设置,2019/4/29,随意：当前任意输入法 打开：系统首选输入法 关闭：进入英文模式，意味着进行半角录入,12,4.1 数据有效性,数据输入法设置,2019/4/29,中文文本字段：选择打开英文文本字段：选择关闭数字字段：选择关闭,数据有效性条件设置-演示,问题：创建班级成绩录入数据表 结构：班级，姓名，数学，计算机，各门成绩录入日期，平均分 班级：1-3 整数（拒绝） 成绩：0-100之间（拒
5、绝） 日期：2009-2010之间（警告） 平均分自动计算 学生人数：12人,2019/4/29,13,选择条件设置区域 进入条件设置窗口 根据要求设置条件,14,4.1 数据有效性,录入限制性内容的需求,2019/4/29,15,4.1 数据有效性,2019/4/29,录入限制性内容的解决方案：下拉菜单,16,4.1 数据有效性,录入限制性内容的解决方案：下拉菜单,2019/4/29,17,4.1 数据有效性,录入限制性内容的解决方案：下拉菜单,2019/4/29,限制性数据录入-演示,问题：录入学生姓名、学科、专业 学科：理学、工学、农学 理学：数学、物理、化学 工学：计算机、水利、建筑、
6、机械 农学：植保、气象、畜牧,2019/4/29,18,设置学科 建立学科录入下拉菜单 录入10-20条记录,19,4.1 数据有效性,二级下拉菜单的应用 二级下拉菜单的应用需求,2019/4/29,需要根据学科字段的内容，在专业字段中选择显示限制性可录入内容,20,4.1 数据有效性,二级下拉菜单的应用 二级下拉菜单的设置,2019/4/29,21,4.1 数据有效性,二级下拉菜单的应用 二级下拉菜单的设置,2019/4/29,22,4.1 数据有效性,二级下拉菜单的应用 二级下拉菜单的设置,2019/4/29,23,4.2 数据排序,问题 什么是数据表关键字 单关键字排序 多关键字排序数据
7、库：表、记录、字段、元素、关键字 Excel：工作表、数据行、数据列、单元格 Excel关键字：指排序所依据的数据列。,2019/4/29,24,4.2 数据排序,单关键字排序 选中关键字数据列中的一个单元格 选择排序指令，排序指令的选择有三种方式： 开始卡/编辑组/选择排序与筛选按钮； 数据卡/排序与筛选组； 单击鼠标右键，应用排序命令。 对话框、菜单中指定升序或降序；,2019/4/29,25,4.2 数据排序,单关键字排序 在排序与筛选组中指定升序；,2019/4/29,26,4.2 数据排序,多关键字排序: 在指定关键字的内容有重复的情况下，需要指定同一关键字内，记录排序的要求 数据：
8、小组，姓名，课程，成绩 统计需求：同一小组内成绩从低到高同一课程内小组从低到高同一课程内分数从高到低,2019/4/29,27,4.2 数据排序,数据：小组，姓名，课程，成绩 统计需求：同一小组内成绩从低到高 第一关键字：小组 第二关键字：成绩 统计需求：同一课程内小组从低到高 第一关键字： 第二关键字：,2019/4/29,28,4.2 数据排序,多关键字排序:小组内成绩从低到高,2019/4/29,29,4.2 数据排序,多关键字排序,2019/4/29,30,4.2 数据排序,多关键字排序结果,2019/4/29,完成指定的排序任务,双关键字排序,2019/4/29,31,选择区域 指定
9、排序命令 进入排序对话窗口 添加排序关键字 确定，开始排序,32, Excel自定义序,自定义数据序的需求,2019/4/29,职称按文本降序排序的结果,33,Excel中文排序,自定义数据序,2019/4/29,34,Excel中文排序,自定义数据序,2019/4/29,内容可以使用逗号或【Enter】作为分隔符,1,2,3,4,35,Excel中文排序,自定义数据序,2019/4/29,排序 - 演示,显示成绩降序排序的结果 要求实现课程内，成绩从高到低的排序结果 要求实现课程按：数学计算机代数英语的排序结果,2019/4/29,36,37,4.3 数据筛选,问题 什么是筛选：在大量数据当
10、中，通过设定显示数据的逻辑条件，达到只显示满足指定条件的数据记录的目标。 掌握： 数据限制的逻辑条件描述 筛选操作方式 简单筛选 高级筛选,2019/4/29,38,4.3 数据筛选,简单筛选操作过程 进入数据表，指定筛选数据区 在【数据】标签下，单击数据筛选按钮 数据表进入筛选状态，各个数据列显示筛选下拉按钮； 单击需要指定筛选条件的数据列的筛选下拉按钮，系统自动列出了本数据列中所有可选的数据元素，通过多选打勾的方式指定筛选条件； 显示的数据表的筛选结果；,2019/4/29,39,4.3 数据筛选,简单筛选操作过程： 原始数据 筛选状态 筛选条件 筛选结果,2019/4/29,40,4.3
11、 数据筛选,筛选条件设定： 原始数据 筛选状态 筛选条件成绩70-89 筛选结果,2019/4/29,41,4.3 数据筛选,字符字段筛选操作：万用符？,2019/4/29,42,4.3 数据筛选,字符字段筛选操作：万用符？,2019/4/29,筛选命令： 数？,筛选命令： ？数,筛选命令： 数,43,4.3 数据筛选,简单筛选操作过程,2019/4/29,2019/4/29,44,低于平均分筛选,高于平均分筛选,45,4.3 数据筛选,高级筛选操作过程 进入数据表； 在数据表的空白位置创建各数据列的筛选条件； 数据卡 / 排序与筛选组 / 高级按钮； 进入高级筛选窗口，确定原始数据区、确定筛
12、选条件区域、确定筛选数据保存区域；掌握筛选条件区域的描述方式！,2019/4/29,46,4.3 数据筛选,高级筛选操作过程,2019/4/29,47,4.3 数据筛选,高级筛选操作过程,2019/4/29,48,4.3 数据筛选,高级筛选操作过程要求条件同时成立即逻辑与 要求至少满足一个条件，即逻辑或,2019/4/29,49,4.3 数据筛选,高级筛选应用实例,2019/4/29,数据筛选-演示,问题：创建班级成绩录入数据表 结构：小组，姓名，课程，成绩 简单筛选：组号1-2，成绩90 高级筛选：小组号1-2, 课程:计算机 + 姓名程实 高级筛选： 英语成绩高于85以及数学成绩高于90,
13、2019/4/29,50,51,4.4 数据查找与替换,问题 指定数据的浏览 指定数据的编辑定位 指定数据的修改,2019/4/29,52,4.4 数据查找与替换,数据查找操作过程： 进入数据表，点击数据查找区域的第一条记录； 单击【开始】/编辑组/查找与选择按钮/查找命令； 进入查找对话窗口，单击“选项”按钮，使得查找窗口显示查找操作功能的细节，通过查找窗口指定查找内容、查找的匹配方式（是否严格区分大小写字母）等等； 查找的过程包括两种方式：其一通过单击“查找全部”按钮实现，其二通过“查找下一个”按钮实现，单击“查找下一个”按钮，光标会进入查找对象所在的记录，每单击一次按钮，光标向数据表末尾
14、方向移动一个查找到的记录。,2019/4/29,53,4.4 数据查找与替换,数据查找操作过程：,2019/4/29,54,4.4 数据查找与替换,数据查找操作过程：（全部查找）,2019/4/29,单击记录行进入数据表对应记录,55,4.4 数据查找与替换,数据替换操作过程：,2019/4/29,56,4.4 数据查找与替换,数据替换操作过程：,2019/4/29,57,4.4 数据查找与替换,数据查找与替换技巧与原则： 查找操作需要选定区域，单击某一个单元格，表示要求在整个工作表（sheet）中进行查找。 查找操作的特点，允许用户一边在查找窗口操作，一边对工作表的数据进行编辑。 Excel
15、的查找可以针对单元格的数据内容，也可以针对单元格的格式属性进行。,2019/4/29,58, 数值的精确查找,数值的精确查找：,2019/4/29,59,公式计算结果的查找,原始数据基本状态：,2019/4/29,公式,数值,文本,单元格属性,查找,公式-无单元格匹配-单元格匹配值 - 无单元格匹配- 单元格匹配,2019/4/29,60,61,公式计算结果的查找,原始数据基本状态：,2019/4/29,2019/4/29,62,2019/4/29,63,按值查找 无单元格匹配,2019/4/29,64,按公式查找 指定单元格匹配,2019/4/29,65,按公式查找 无单元格匹配,66,单元
16、格属性查找,原始数据基本状态：,2019/4/29,单元格背景、字体、颜色等属性,67,单元格属性查找,原始数据基本状态：,2019/4/29,单元格背景、字体、颜色等属性,数据查找 - 演示,问题：创建班级成绩录入数据表 结构：姓名，学科，专业，实习次按实习次数比较查找：公式单元格匹配/无单元格匹配值单元格匹配/无单元格匹配,2019/4/29,68,69,4.5 数据分类汇总,问题 什么是 Excel 数据分类 什么是 Excel 数据汇总Excel 分类汇总的结果 分类的前提条件排序,2019/4/29,70,4.5 数据分类汇总,数据分类汇总操作过程 进入数据表； 对实验员数据列按升序
17、或降序进行排序处理； 单击数据选项卡/分级显示组/分类汇总按钮； 进入分类汇总对话窗，指定分类汇总依据数据列； 得到的分类汇总的结果，应用数据表左侧分类汇总显示的标志，可以得到分类汇总的显示结果，单击+/-号，可以调整到所需要的显示状态。原始数据如下：,2019/4/29,71,4.5 数据分类汇总,数据分类汇总操作过程 进入数据表； 对实验员数据列按升序或降序进行排序处理； 单击数据选项卡/分级显示组/分类汇总按钮； 进入分类汇总对话窗，指定分类汇总依据数据列； 得到的分类汇总的结果，应用数据表左侧分类汇总显示的标志，可以得到分类汇总的显示结果，单击+/-号，可以调整到所需要的显示状态。原始
18、数据如下：,2019/4/29,72,4.5 数据分类汇总,数据分类汇总操作过程,2019/4/29,73,4.5 数据分类汇总,数据分类汇总操作过程,2019/4/29,按实验员排序为前提！ 实验员字段有序为前提！,74,4.5 数据分类汇总,数据分类汇总操作过程,2019/4/29,75,4.5 数据分类汇总,分类汇总的处理，总是首先依据一个已经有序的数据列！,2019/4/29,1,2,76,4.5 数据分类汇总,按实验员分类汇总的结果,2019/4/29,3,77,4.5 数据分类汇总,按实验员排序，按物质分类汇总（按实验员计数）的结果 。,2019/4/29,78,4.5 数据分类汇
19、总,分类汇总的撤销： 单击分类汇总工作区任意单元格 数据卡/分级显示组/分类汇总按钮 对话框中“全部删除”按钮,2019/4/29,分类汇总演示,数据结构：小组，姓名，课程，成绩分类汇总： 小组总平均分 小组内各门课程的平均分,2019/4/29,79,80,4.6 数据合并计算,问题 数据合并的目的 数据合并的范围按位置的合并计算 按分类的合并计算 利用公式进行合并计算（在公式中引用其他数据表内单元格）,2019/4/29,81,4.6 数据合并计算,合并计算支持在同一或不同工作簿中的工作表数据的合并计算，这一功能以简单的操作方法支持了数据按对应位置、对应标题进行合并与计算的数据处理需求。,
20、2019/4/29,82,位置合并计算,原始数据,2019/4/29,83,位置合并计算,原始数据 多数据表、数据关键字名称及内容排列顺序一致 合并计算完成不同数据表内四门成绩的合并计算 处理过程 选择合并数据结果所在数据区的起始单元格（左上角单元格）； 【数据】选项卡/数据组/合并计算按钮 进入合并计算对话窗口，指定合并计算使用的方法和进行合并计算的数据对象 确定完成,2019/4/29,84,位置合并计算,处理过程 ”数据”选项卡/数据组/合并计算按钮 进入合并计算对话窗口，指定合并计算使用的方法和进行合并计算的数据对象 确定完成,2019/4/29,通过区域拾取器可以在不同数据表内选择被
21、合并的数据对象,1,2,3,4,5,85,位置合并计算,在标题“函数（F）”下，选择指定合并计算函数（即指定合并计算的计算方法）； 在标题“引用位置（R）”下，利用区域拾取按钮选择需要进行合并计算的原始数据区，添加,2019/4/29,86,位置合并计算,在标题“函数（F）”下，选择指定合并计算函数（即指定合并计算的计算方法）； 在标题“引用位置（R）”下，利用区域拾取按钮选择需要进行合并计算的原始数据区，添加,2019/4/29,sheet2 ! D1:D18 sheet2 ! E1:E18 sheet1 ! F11:F28 sheet1 ! H11:H28 每个区域的大小相同,87,位置合
22、并计算,在区域拾取工作的状态下，可以通过sheet标签选择不同的数据表。重复上述过程，当所有需要引用的数据区指定完毕，单击确定。,2019/4/29,选择数据列进行合并操作,88,位置合并计算,单击确定，在指定的结果区域产生合并计算结果，四门课程的平均分。,2019/4/29,合并计算结果,复制粘贴的关键字,52,分类合并计算,前提： 确保每个数据区域都采用数据列组织，格式要求：第一行中为需要合并的每一列都设置标题，在列表中没有空行或空列。 将每个区域分别置于单独的工作表中。不要将任何区域放在需要放置合并结果的工作表中 确保要合并的列或行的标签具有相同的拼写次序和大小写处理,2019/4/29
23、,90,分类合并计算操作过程,在包含要显示合并数据的在工作表中的单元格区域中，单击数据存放位置左上角的单元格。设置合并数据的名称，确保在名称的右下方有足够的区域存放合并数据 在“数据”选项卡上的“数据工具”组中，单击“合并计算”。 指定计算方法、指定计算区域 确定，完成计算 系统只对名称相同的数据列进行合并计算，标题不同的数据列自动以单独数据列的方式带入合并区域,2019/4/29,91,分类合并计算结果分析,2019/4/29,数据区必须带有标题,92,分类合并计算结果分析,2019/4/29,选择数据区域操作,93,分类合并计算结果分析,2019/4/29,选择数据区域操作,分类合并：按标
24、题合并,练习,1. 创建专业数据表 2. 确定数据录入的约束条件 3. 完成数据排序整理 4. 完成数据的修改替换 5. 完成数据分类与基本统计 6. 不同数据表的合并计算,94,2019/4/29,95,公式的审核（2章）,追踪单元格 错误检查 公式求值,2019/4/29,96,公式的审核（2章）,追踪单元格： 追踪引用单元格单击公式所在单元格公式选项卡/公式审核组/追踪引用单元格显示公式与被引用单元格连接关系 追踪从属单元格单击单元格公式选项卡/公式审核组/追踪从属单元格显示单元格与引用该单元的公式的连接关系,2019/4/29,97,公式的审核（2章）,错误检查： 公式选项卡/公式审核
25、组/错误检查按钮 可选：错误检查错误追踪,2019/4/29,98,公式的审核（2章）,显示公式与公式求值： 公式选项卡/公式审核组/显示公式按钮 公式选项卡/公式审核组/公式求值按钮,2019/4/29,显示当前工作表中的所有公式,在窗口中对指定的公式显示计算结果,99,Sumifs（2章）,格式 SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Sum_range: 求和数据区 Criteria_range_: 求和条件检验数据区，要求位置与求和数据区相对应 Criteria_：求和条件描述,2019/4/29,100,Sumifs（2章）,格式 SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Sum_range: 求和数据区 Criteria_range_: 求和条件检验数据区，要求位置与求和数据区相对应 Criteria_：求和条件描述,2019/4/29,计算结果 66,条件1,条件2,
