高级办公自动化4-办公中的数据处理.ppt

第四章 办公中的数据处理 主 要 内 容 数据库创建与数据录入 特殊格式与有效性设置 数据的计算和统计 数据的排序与筛选 分类汇总与透视分析 小结和作业 数据库创建与数据录入 一、数据库的创建 1、Excel中数据库表格的概念和结构： 概念：数据库是与特定主题和目标相联系的信息集合。是工作 表中的一个区域或二维表，可看作一组数据清单。 结结构：列为字段，其下的行为记录（数据）。 2、建库准则： 一库一表 一列一字段（1种信息类型） 一行 一记录 无空白行、列 无完全相同记录 关键 数据放中间 字段名与记录（数据）格式相区分 库名与字段名间应空行 3、建库实例： 学生成绩处理、产品销售汇总分析 二、数据的录入 1、数据表直接输入： 按字段输入。应先设置有效性和条件格式 2、记录单输入： 按记录输入。应用 “记录单”功能 。 “数据”-“记录 单” 3、注意： 记录单输入时，有效性设置可能不起作用 。防错法：先用数据表方法输入一条记录的 所有字段（含公式单元格中的公式），再用 记录单法输入基本数据即可，其它会自动算 出填上。 特殊格式与有效性设置 一、特殊格式设置 一般格式（字体、字号、字形、字色、对齐等）设置略。 1、条件格式设置 选取-“格式” - “条件格式” （输入条件）- “格式” （对满足条件的单元格进行格式 设置。注意：不能设置字体字号）-“添加 ” （最多可设3个条件） 2、底纹颜色间隔设置 （1）复制格式法（手动、机械） 先将前两行设置成间隔底纹效果，再用格式刷或 “选择性粘贴”的方法将其余记录设置成间隔底 纹效果。新增记录需要重新设置。 （2）条件格式法（自动、智能） 选定需要设置间隔底纹的整个数据区域-“格式 ”-“条件格式”-“公式”- “=MOD(ROW(),2)=0”(行号除以2的余数为0)- “格式”-“图案”-选定所需颜色-“确定”。新 增记录也有同样的效果。 二、有效性设置 为了保证数据库中输入的数据都在有效范围内，选择单元 格时给出提示信息，出错是时给出错误提示，需要对其进 行有效性设置。 选中-“数据”-“有效性”-“设置”（有效性条件）-“输 入信息”（钩上“选定单元格时显示输入信息”）-“ 出错警告”（钩上“输入无效数据时显示出错警告” ，一般为“中止”）-“选择输入法模式”（打开-中文 ，关闭-相反，随意-不限） 数据的计算和统计 数据的计算和统计需要利用公式和函数进行处理 一、数据计算 常用的函数：SUM、 AVERAGE、RANK（排名）、 ROUND（4舍5入）、IF、SUMIF、 MOD（求余） 等 二、数据统计 常用的函数：MAX、MIN、COUNT（计数，参数类 型不限）、COUNTA（计数，参数类型有限）、 COUNTIF（条件计数） IF(P,T,F) 其中，P为条件表达式 T为满足条件需执行的表达式 F为不满足条件的表达式 最多可嵌套7层 ROUND(A1,A2) A1为需要取整的数或表达式 A2为保留的小数位数 注意： 1、选用正确的函数；参见教材P90-91 2、正确使用函数的参数和格式 函数及公式利用中所有符号均为英 文状态的 公式中若用到其他工作簿或工作表 的数据，应加上相应的工作簿或工作表的名 称：工作簿名工作表名！单元格地址。 引用方式（相对、绝对、混合）。 数据的排序与筛选 一、数据排序 依据：以单元格数值及所在列为依据。 种类：升序、降序 方式： 一级字段（只以一列排序）； 多级字段（最多3列，分主次。 数据- -排序主、次、第三关键词 ，多级字段适用 于前面的关键字排序结果相同的情况。 ）； 其它（数据-排序-选项-自定义排序 字母或笔划）。 注意：RANK函数与数据排序用其一 二、数据筛选 含义：从众多数据中查找特定记录需要进行 数据筛选，显示符合条件的，隐藏不符合的。实 为检索、查询。 方法：自动筛选、高级筛选 步骤： 自动筛选：选中数据自 动筛选筛选器自定义筛选条件；只能 在原处显示筛选结果，不能作多字段“或”运 算。 高级筛选：选中数据高级筛选 源区域 条件区域 存放位置；可将结果 复制到他处，能作多字段“或”运算。 注意： 1、需要专门设置条件区域，同行内的条件 之间为“与”关系，不同行为“或”关系。 2、若它与数据库在同一工作表上，之间应 有空白行。 3、该区域与数据库的字段名完全相同。 4、筛选结果与库在同张表上，光标置于库 内可在“数据区域”自动填上其地址，不放同一表 中不能用此法，因筛选结果只能放活动表中。 5、只填目标位置的第一单元格。 分类汇总与数据透视 一、分类汇总 1、作用：按类别统计结果，使数据库清晰易 懂。应先排序，集中相同关键词的记录，然后再分 类汇总。 2、步骤：排序后，任意单元格，“数据”-“分 类汇总”-选“分类字段”、“汇总方式”、“汇总项”-“确 定”。 “数据”-“分类汇总”-“全部删除” 二、数据透视 1、作用：数据透视表（图）是能将数据排序、数 据筛选、数据分类汇总这三种数据整理方法结合在 一起的工具。可方便、全面地重新组织、整理数据 ，以详细反映数据间的关系。 2、相关概念： 透视表：对大量数据快速汇总建立交叉列表的动态工作表 透视图：据表的图表 透视报告：据透视表而作的报告（数据透视表工具栏 ） 三者结合可全面查看分析数据。 3、透视表： 创建步骤：任一单元格数据数据透视表和图表 报告选择类型下一步输入选定数据源区域 下一步选择显示存放位置完成（再将透视表工具 栏中的）字段拖放（到相应位置）注意：拖放可逆向操作。 组成： 页字段；页字段项；数据字段；数据项； 行字段；列字段；数据区域 注意：透视表中字段名的修改：选定-右键-字段设 置 用Excel数据库进行学生成绩处理 数据库框架的建立 数据库建库准则： 一库一表 一列一字段（1种信息类型） 一行一记录 无空白行、列 无完全相同记录 关键数据放中间 字段名与记录（数据）格式相区分 库名与字段名间应空行 数据库表格中数据的输入 1、选择“数据”“记录单” 2、在弹出的“记录单”界面 中，以次逐条输入记录 条件格式化设置 1、条件格式化最多可以设置3个条件； 2、选择要进行格式化设置单元格区域（ F4:L57）（假设有54个学生）； 3、选择“格式”“条件格式”，打开“条件格 式”对话框； 4、单击“格式”按钮，在弹出的界面中设置满 足条件的单元格的格式。 优秀 不及格 条件格式化方法设置底纹颜色 1、选定整个数据表区域； 2、选择“格式”“条件格式”命令； 3、在弹出的对话框左侧第一个下拉列表中选 择“公式”文本框中设置公式为“ MOD(ROW( ),2)=0”； 4、单击上述界面中的“格式”按钮，在弹出的 界面中选择需要的底纹颜色即可。 行号除以2，余数为“0”时就 执行设置的条件格式 数据有效性设置 1、选定要设置有效性的数据区域（F4:L57） 2、选择“数据”“有效性”； 3、在弹出的“数据有效性”对话框中，选择“ 设置”选择、录入相应的有效范围； 4、在“数据有效性”对话框中，选择“输入信 息”输入提示信息 设置“输入提示信息”后， 当用户选择对应单元 格，系统会自动出现 提示信息。 数据库中的排序操作 一级字段排序： 1、将光标置于待排列的 某一单元格； 2、单击工具栏上的“升序 ”或“降序”按钮。 多级字段排序： 适用于前面的关键字内 容相同时。 1、将光标定位在任一单元格 上； 2、选择“数据”“排序”； 3、依次选择“主关键字”、“ 次关键字”(最多可以设置3 个关键字)及其排序方式。 自动筛选 以筛选“英语成绩在8090分（包括80分 ）的记录”为例： 选择“数据”“筛选”“自动筛选” 选择“自定义” 高级筛选 1、新建一工作表（名为：优秀学生筛 选），在其中输入“优秀学生”筛选 条件: (1)“与”同行；(2)“或”不同行 ；(3)”与”、”或”不同行。 2、单击“优秀学生筛选”表中任一空白 单元格； 3、选择“数据”“筛选”“高级筛选” ，弹出对话框 从“评定结果”表中 选择原始数据 “高级筛选”条件所 在的区域 指定：筛选结果存 放的区域 产品销售汇总分析 按“品名” 汇总 “先物再人”的 数据透视表 销售数据的分类汇总 分类汇总前首先必须对所 依据的字段排序 1、选定数据库中的任一单元格 ； 2、选择“数据”“分类汇总”， 弹出对话框； 3、单击对话框中“分类”字段下 的“品名”作为分类汇总依据字 段； 4、在“汇总方式”下拉列表中选 择需要的统计函数（本例选“ 求和”）。 销售数据的透视分析 数据透视表的创建 1、将光标定位在销售数据 库表格中的任一单元； 2、选择“数据”“数据透 视表和图表报告” 建立数据透视表所依 据的数据源区域 页字段：被指定为页方向数 据源的数据库中的字段 。本例选“销售日期” 被指定为行方向数 据源的字段。 （先“业务员” ，后“品名”） 被指定为列方向数据源 的字段。（“客户名 称” 含有数据的源数据 库中的字段。 （“合计金额” ） 使用数据透视表筛选数据 只显示1月9号的记录 修改数据透视表的布局 行、列字段互换 在字段内移动数据项 隐藏和显示数据 添加字段 删除字段 设置数据透视表选项 数据透视表数据的更新 将行字段拖到列字 段位置处，列字 段拖动到行字段 位置处。 1、选中要移动的数据项； 2、鼠标移至选中的数据项上，当鼠标 指针变为白色箭头时按下左键，拖 动数据项到需要的位置。 隐藏： 1、光标定位在要隐藏的数 据所在单元格； 2、选择右键菜单中的“隐 藏”即可。 显示： 单击字段名单元格右边向 下箭头，将需要显示的 字段选中即可。 单击“显示域”按钮 （将显示出所有 字段名），将需 要添加的字段拖 动到适当的位置 。 直接将要删除的字 段拖到透视表数 据区以外即可。 选择“数据透视表” 工具栏“数据 透视表”“表选 项”，在弹出界 面中设置。 必须执行“数据更新” 操作 制作数据透视报告 1、首先要制作好数 据透视表； 2、单击“数据透视 表”工具栏“设 置报表格式” 创建数据透视图 1、数据透视表上单 击鼠标右键； 2、选择右键菜单上 的“数据透视图” 本章小结 掌握：数据库表格的创建（记录单录入法 ）、数据库表格的条件格式设置、数据有效 性设置、数据排序、数据筛选、数据透视表 、合