Excel_第七课_数据库管理

授课教师 龙剑 第七课数据管理和分析 Excel具有数据库的特点 可以实现数据的排序 筛选 分类汇总 统计和查询等操作 具有数据库的组织 管理和处理数据的功能 因此Excel数据清单也称为Excel数据库 数据清单 一个典型的二维表 它是由工作表中单元格构成的一个矩形区域 矩形区域的每一列叫一个 字段 每个字段包含相同类型的数据 第一行为各字段标题 叫字段名 字段名不能相同 以后每一行的数据表示一条记录 所以数据清单是由多条记录组成的 数据清单 正确创建数据清单规则 列名唯一 纯数据 杜绝空行 非法数据 同列数据的数据类型和格式应完全相同 避免在一张工作表上放置多份数据清单数据清单可以是整个工作表 也可以是工作表的一部分 数据清单的处理功能只能在一个数据清单中使用 数据清单的作用 用户可以通过 数据清单 对表中的数据进行排序 筛选 分类和汇总等操作 以实现日常所需要的统计工作 是数据管理的基础 建立数据清单直接在单元格中输入记录单 数据 记录单 创建数据清单注意事项不能在数据清单中留空行或列 当数据清单中的数据被一个空行或列隔开时 excel会将它当作两个部分看待 在数据清单的第一行建立字段名称 excel将使用字段名建立报告以及进行排序等操作 字段名最好用文字 字段名只能使用255个字符以内的文字或文字公式 且要准确地表示出字段的含义 字段名只能有一个 如果在数据清单中打开了多个相应的字段名 则影响筛选等操作 工作表中只建立一个数据清单 虽然在excel中允许同时存在多个工作表 但是会显得比较混乱 而且容易影响筛选等操作 因此建议最好在一个工作表中建立一个数据清单 使用 数据 记录单 菜单命令 打开某数据清单的记录单 利用记录单可以进行下列操作 添加记录修改记录删除记录查找记录 使用数据清单 单击 条件 按钮 输入相关的查找条件 例如 列出 平时成绩 80 的学生 最后单击 上一条 或 下一条 按钮 可查看到符合给定条件的所有记录 数据排序 排序 依据某列或几列的值 按照一定的顺序将表中的记录重新排列称为排序 排序使表格数据更有条理 使用户观察数据更方便 排序方式 升序和降序排序规则 数字按照大小 文本按首字母英文顺序 笔画 日期按先后 早晚 文本和数字一起 数字在前文本在后 空格始终排在最后 排序方法 工具栏中的 按钮 数据 排序 注意 第一种方法只能采用单独排序方式 第二种可以采用两种排序方式同时进行 1 按单字段字排序工具栏按钮数据 排序2 按多关键字排序主关键字 次关键字第三关键字 选择表格 列区域 打开 排序 显示排序警告勾选 以当前选定区域排序 系统将默认以选择的列单独排序 升序 从小到大降序 从大到小 数据排序步骤 光标位于要排序的列 光标位于数据清单中 单击 数据 排序 命令 最多3个字段排序 选定范围 数据 排序 确定排序关键字和顺序 设置两个以上的排序约束条件 数据排序操作例题 数据筛选 筛选 实现在数据清单中提出满足筛选条件的数据 不满足条件的数据暂时被隐藏起来 从数据库中选一部分数据使用 筛选方法 自动筛选 高级筛选筛选基本操作步骤 选中数据区中的一单元格 数据 筛选 自动筛选 高级筛选 大表 明晰的小表 自动筛选 数据 筛选 自动筛选 设定条件 数据筛选 常用于简单条件的筛选 自动筛选自定义筛选 自动筛选 的 自定义 选项提供了对同一字段进行筛选的两个条件 并且这两个条件可以是 与 或 或 的关系 同时 该对话框还提供 等于 大于 起始于 等许多条件选项 注意 筛选只是暂时将不要的数据进行隐藏 没有删除 排序和筛选必须为数据清单格式的表 自定义 可以设置两个条件 与 两个条件必须同时满足 或 满足一个条件即可 对某个列应用两个以上的筛选条件 或者对多个列应用筛选条件 最好使用高级筛选 筛选数据 高级筛选 高级筛选的功能 实现对某个列或者多个列应用多个筛选条件 把满足条件的记录复制到当前表的其他位置 原数据清单保持不变 要使用高级筛选功能 必须在数据清单的上方或下方放置一个条件区域 该区域用来指定筛选出的数据必须满足的条件 条件区域应当遵守的规则是 条件区域与数据清单之间至少要留一个空行 条件区域中条件的逻辑关系是 写在同一行的条件互为 与 关系 写在不同行的条件互为 或 关系 条件区域中不能有空行 下图的条件可以理解为 系别等于 自动控制 并且考试成绩 70 或者系别等于 计算机 并且上机成绩 17的学生名单 需要注意的是 由于系别是文字型数据 在写条件是 应写成 自动控制 计算机 即自动控制和计算机必须用引号括起来 高级筛选 功能完成筛选那些需要匹配计算条件或筛选条件中含复杂的 与 和 或 关系的数据 或 与 1 确定筛选条件 并创建条件区 注意 要与数据清单用空行隔开 2 数据 筛选 高级筛选 命令 高级筛选 复杂条件 高级筛选数据 筛选 高级筛选 数据区域步骤 1 先在空白区域写上筛选条件 条件必须为两行 2 数据 筛选 高级筛选 3 将数据区域和条件区域选相应的框里 4 列表区域 选择表格区域 5 条件区域 将工作表中定义的条件区域选中 6 勾选 在原有区域中筛选 系统将自动在当前表格区域中筛选出结果 7 勾选 将结果复制到其它位置 系统将结果自动复制到所定位的单元格上 使用 高级筛选 必须先建立筛选条件区域 筛选条件区域类似于一个只包含条件的数据清单 由两部分构成 条件列标题和具体筛选条件 其中首行包含的列标题必须拼写正确 与数据清单中的对应列标题一模一样 具体条件区域中至少要有一行筛选条件 高级筛选注意 取消筛选自动筛选 定位于清单内 数据筛选 去除 自动筛选 高级筛选 定位于清单内 数据筛选 全部显示 指在原表格下筛选 高级筛选案例 条件区域 条件区域 筛选结果 条件区域 筛选结果 原则 先分类 排序 数据 分类汇总 按类汇总 对已排序的数据 将相同类别的数据 进行求和 计数 求平均值及最大 最小值等类的运算称为汇总运算 作用 分类汇总可以对数据清单中的各个字段分类逐级进行数据的汇总计算 并将结果自动显示出来 另外还具有分级显示功能 常用于的总结报告 分类汇总 指定分类字段 汇总方式等 注 分级符 3是最明细的数据 2是上一级的汇总数据 1就是最上级的汇总数据 数据汇总的相关概念 分类字段 在该下拉列表中可以指定进行分类汇总的字段 选定汇总项 在该列表中可以选择多个要执行汇总操作的字段 汇总方式 在该下拉列表中可以选择的汇总方式包括 求平均值 计数 求和等 替换当前分类汇总 如果已经执行过分类汇总操作 选中该复选框 会以本次操作的分类汇总结果替换上一次的结果 每组数据分页 如果选中该复选框 则分类汇总的结果按照不同的分类汇总结果分页 在打印的时也将分页打印 汇总结果显示在数据的下方 如果选中该复选框 则分类汇总的每一类会显示在本数据的下方 否则会显示在本类数据的上方 全部删除 单击该按钮 可以将当前的分类汇总结果清除 创建分类汇总要插入分类汇总 要先对数据清单进行排序 以便将进行分类汇总的数据行组合在一起 按照需要进行分类汇总的字段排序 单击数据清单中的任一数据单元格 选择 数据 分类汇总 命令 打开对话框 在 分类字段 下拉列表框中选择分类汇总的字段名 选择汇总方式和汇总项 下方复选框采用默认 点 确定 分级显示数据在创建分类汇总后 可以分级显示数据清单中的数据 每一级的数据都被分为若干组 单击代表级数的数字 可指定显示到该级的明细数据 每一组分类数据的左侧都有一个隐藏 或显示 明细数据按钮 单击隐藏明细数据按钮 即可隐藏该组的明细数据 单击显示明细数据按钮 即可显示 清除分类汇总选择 数据 分类汇总 打开对话框 单击 全部删除 即可 分级显示例题 需要创建两层以上的分类汇总 称为嵌套汇总 嵌套汇总命令的使用前提 数据已按两个以上关键字排序 这里要对 分公司 和 部门 进行排序 嵌套分类汇总步骤 1 首先针对两列或多列数据进行排序 2 在要分类汇总的数据清单中 单击任一单元格 选定该数据清单 使用 数据 分类汇总 命令 3 在 分类字段 框中 选择需要用来分类汇总的数据列 4 显示出对第一列的自动分类汇总后 对下一列进行分类设置5 清除 替换当前分类汇总 复选框 接着单击 确定 按钮 嵌套分类汇总 如 查看每分公司的人工费用支出情况和查看每一部门中最高薪水额 我们应使用 嵌套分类汇总 命令 例 查看每分公司 每一部门中的最高薪水 不选 错误警告 数据有效性 就是允许对单元格中所输入的值进行设置限制 称为数据有效性 在默认情况下 单元格中可以输入任何值 有效性设置方法 数据 菜单有效性设置条件 有效性案例 用数据有效性使基本工资满足 1000并且 2000的整数 1955年6月1日 出生日期 1985年6月1日 模拟运算表 快速地计算出随着公式某一参数或两个参数一系列的变化值的相应公式的结果技巧 选中数值勤区和结果区 公式单元格 数据 模拟运算表 对话框 行引用单元格列引用单元格模拟运算包括 单变量模拟运算表 只引用行或列单元格 公式中 双变量模拟运算表 同时引用行或列单元格 公式中 单变量模拟运算表 数据菜单 单变量模拟运算表 引用列的 利率 双变量模拟运算表 单变量求解 一元函数求解 已知公式的解 求解公式中某一参数的解技巧 工具 单变量求解 对话框目标单元格 公式所在单元格目标值 公式的值可变单元格 公式中的待求参数 单变量案例 工具菜单 目标单元格 纯利润 目标值 100000 可变单元格 销售总额 有分隔符的分列数据 分列 分隔符号 下一步后选择相应的符号 添加分列线数据 分列 固定宽度 添加分列线 分列 合并计算 不同的工作表间 工作表不的不同区域 工作表中相同性质的数据区的函数计算 不需要在合并之前排序技巧 选中结果区 数据 菜单 合并计算 对话框 选取按位置进行合并 不包含标题行 列 按分类合并 包含行 列标题 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格 数据透视表是一种特殊的工作表 它从工作表中抽取需要的信息 经处理后再汇总成新的交叉表 它广泛应用于数据分析 数据透视表和数据数据透视图报表 建立数据透视表 数据 菜单 数据透视表和数据透视图 根据向导进行布局 拖动数据字段进行设置 双击汇总方式可以修改它 数据透视表 可实现按多个字段分类汇总 汇总方式 行 列 页为分类的字段 数据清单字段 创建好了的数据透视表 单击系别 数据 性别旁的箭头可以更改显示内容 全部显示还是显示部分内容 例 在 职工登记表 中建立数据透视表来查询各部门各职称的平均年龄 即指定布局以 所属部门 为行字段 以 职称 为列字段 以 年龄 为平均值项 5 双击数据区域内的数据项 年龄 选择 平均值 1 数据 数据透视表和数据透视图 命令 选择 生成数据透视表 2 在 数据透视表和数据透视图向导 对话框中 单击 选定区域 右侧的按钮 在 数据源 工作表中选定数据区域 3 单击右侧的按钮 按 下一步 钮 进行布局4 单击 布局 钮 将所要字段拖到数据透视表中适当位置 将所属部门拖到 行 职称拖到 列 年龄拖到 数据 6 单击 确定 和 完成 钮 数据透视表编辑 单击鼠标右键修改透视表 字段设置 表格选项 显示字段列表 通过字段设置修改字段名称 汇总方式 通过表格选项确定是否需要行 列总计 通过字段列表增加页 行 列的分类字段 删除分类字段 直接拖走 修改数据透视表可在透视表内直接拖动字段名定位于透视表内 数据 数据透视表和图表 布局更新数据可将光标定位于透视表内数据 更新数据或单击 数据透视表 工具栏中 右击 按钮创建数据透视图单击 数据透视图 工具栏的图表向导按钮 数据透视表编辑 批注批注 是附加在单元格中 对单元格内容进行解释说明 添加批注右键 插入批注插入 批注Shift F2有批注的单元格右上角有小红三角形 但不能打印 方案 也叫规划求解 自动替换的组值 可来预测工作表模型的输出结果 技巧 工具 菜单 方案 方案案例 3 单击摘要 方案摘要 一 填空题 1 Excel2003有两种筛选数据的方法 即 和 2 要插入分类汇总 首先要对数据清单进行 3 使用高级筛选功能 必须先建立一个 用于指定筛选条件 作业 1 在编辑栏中输入公式 然后按 键进行确认 A EnterB Alt EnterC Ctrl EnterD Ctrl Alt Enter 2 Excel2003提供的图表类型有 种 而且每种图表还有若干个子类型 A 10B 12C 14D 15 二 选择题 3 图表是与生成它的工作表数据相连接的 因此 工作表数据发生变化时 图表会 A 自动更新B 断开链接C 保持不变D 随