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