《EXCEL高级版教程.ppt》由会员分享，可在线阅读，更多相关《EXCEL高级版教程.ppt（88页珍藏版）》请在三一办公上搜索。
1、1,Excel 2003 高级教程,讲师：张少辉电话：邮件：,主要内容,EXCEL的规范化操作EXCEL函数与数据分析图表与数据透视表宏与VBA,EXCEL表格的规范化操作,构思和设置表结构建立和输入数据编辑和修饰表格简单使用和分析保存表格数据打印输出数据,制表的六个基本过程,管理项目与表格结构的关系,正确认识管理对象的独立性，准确拆分管理项目至最小管理单元，并建立管理项目名称与表格结构中“列”的对应关系。为数据管理提供合理的数据结构。此项工作的关健在于明确表格列与行的逻辑关系，以便有效组织一个独立的管理项目，形成可供分析的表格。,案例1:规范表格示例.xls,表格设计应注意的事项,管理项目与
2、表格结构的关系 凡须独立管理的项目，必须单独设置项目名称，独占一列，避免混合设置管理项目的独立性必须与扩展性结合考虑，以方便统计分析表格结构设计必须考虑后期统计分析的应用状态。,案例2:成绩表.xls,表格数据的输入与准确性控制,为什么要提出准确性 哪些数据容易出错控制原始数据输入准确性的解决方法用调用法控制原始数据的准确性用计算法控制加数据的准确性使用数据有效性使用校对工具,单元格的基本操作,单元格和单元格区域的选定方法 使用名称框选定和命名单元格区域单元格的编辑操作选择性粘贴的使用(演示转置和加法),原始数据的输入,在单元格中输入数值、文本、日期/时间(重点讲日期)序列填充的方法自定义序列
3、同时在多个单元格中输入数据数据有效性的设置使用下拉列表输入数据的方法使用多级下拉列表输入数据,表格文档的保存技巧,正确使用”保存”命令何时使用”另存为”命令使用”保存工作区”命令对文件加密的保存方法,正确使用”保存”命令,新文件的保存规律和方法“新文件:是通过”新建”命令打开EXCEL窗口,并在其中制作的表格文件.通常窗口”标题栏”中按新建顺序自动编号的文件名,book1.xls,book2.xls,对于新文件，无论使用“保存”还是“另存为”都显示另存为对话框旧文件保存与“保存”命令旧文件（有文件名称），每按一次保存，执行一次覆盖过程，新的替代旧的。设置备份保存,何时使用“另存为”命令,更名保
4、存将文件存到指定位置改变文件的保存类型使用模板文件,如何使用“保存工作区”,“工作区”也称“工作面”，是指WINDOWS桌面上正在使用的若干份电子表格文件保存工作区时，原文件按原名进行保存，工作区文件只保存这些文件的位置的存储路径，相当于快捷方式。,表格数据的打印与输出,常规表格的打印输出连续页面打印打印区域设置添加到打印区域超大表格打印,常规表格页面控制,小表格的打印设置面积较小，不足一张纸，可以使用居中大表格的打印设置超出一张纸，但超出有限，可以使用缩放的方式,有效处理超大表格,设置跨页打印表头设置屏幕冻结表头强制分屏显示（拆分窗口）强制分页打印（使用分页符）设置打印顺序（页面设置，工作表
5、）设置页眉和页脚,Excel菜单功能和必会命令,EXCEL函数与数据分析,公式和函数入门排序筛选分类汇总实用函数讲解,公式和函数,公式的使用 公式是用运算符将数据、单元格地址、函数等连接在一起的式子，以等号“=”开头=5*10-20 常数运算=A6+B1 对单元格A6和B1中的值相加=SQRT(10+A2)使用Excel函数，SQRT表示求开方根 1.运算符 算术运算符、文本运算符和关系运算符,公式举例=83*25%表示8的立方再乘以0.25，结果为128=5*8/23-B2/4 若单元格B2的值是12，则结果为2=”计算机”&”电脑”运算结果是”计算机电脑”=A1&A2 把单元格A1和A2中
6、的文本进行连接 而生成一个新的文本=B242 比较结果为TRUE(设B2的值是12)=B2=32 比较结果为FALSE=B212 比较结果为FALSE=A3+80 假设A3为日期型数据，则A3+80表 示日期A3加上80天,用于描述一个条件。例如 5=ABCD 或写成=ABCD=计算机 或写成 计算机 或写成 计算机,比较条件式,当在一个单元格中输入一个公式后，Excel会自动加以运算，并将运算结果存放在该单元中。以后当公式中引用的单元格数据发生变动时，公式所在单元格的值也会随之变动 例1 计算出学生的总评成绩(=机试40%+笔试60%)案例文件：计算机成绩.xls(1)单击总评成绩第一个单元
7、格E2，使之成为活动单元格(2)键入计算公式“=C2*0.4+D2*0.6”后按回车键，此时在单元格E2处显示出计算结果82.2(3)再次单击单元格E2使之成为活动单元格；单击“复制”按钮(4)选定区域E3:E6；单击“粘贴”按钮,2.公式的输入和复制,四种表示方式(1)相对地址：以列标和行号组成，如A1，B2，C3等 公式复制时，若引用公式的单元格地址发生变动，公式中的相对地址会随之变动(2)绝对地址：以列标和行号前加上符号“$”构成，如$A$1，$B$2等。公式复制时，公式中的绝对地址保持不变,下面式子表示引用d:book2.xls的sheet1的B5单元格=d:book2.xlsShee
8、t1!B5(3)混合地址：它是上述两种地址的混合使用方式 如$A1(绝对列相对行)，A$1(相对列绝对行)等。公式复制时，公式中相对行和相对列部分会随引用公式的单元格地址变动而变动，而绝对行和绝对列部分保持不变（4）名称地址：事先定义好的单元格区域(5)三级地址:演示 各部销售统计.xls 出错信息 当公式(或函数)表达不正确时，系统将显示出错信息,单元格地址表示方式,常见错误信息,格式：=(,.)举例：=SUM(C2:C6)=AVERAGE(A1:A3,C2:C6)SUM 求和数 AVERAGE 求平方值 MAX 求最大值 MIN 求最小值 COUNT 求数值数据的个数 COUNTIF 条件
9、求数值数据的个数 IF 条件选择,函数的使用,例2 计算基本工资总数，并将结果存放在单元格C7中(1)单击单元格C7，使之成为活动单元格(2)键入公式“=SUM(C2:C6)”，并按回车键 例3 计算补贴总数和扣除总数，并将结果分别存放在单元格D7和E7中(1)选定D2:E7区域(2)单击“自动求和”按钮，即可对这两列分别求和，和数分别存放在两列的最后一个单元格上,案例文件：工资表.xls,函数应用举例,例4 计算基本工资的平均值，并将结果存放在C8中 例5 利用函数MAX求出基本工资数的最大值，并将结果存放在单元格C9中 举例 假设单元格A1，A2，A3，A4的内容分别为1，2，空，“ABC
10、”，则COUNT(A1:A4)的值为2 举例 若要求出基本工资数大于等于500的职工人数，可以采用：=COUNTIF(C2:C6,”=500”)举例 在工作表(A1:D9)中，要求出班号为21的班学生人数，可以采用：=COUNTIF(D2:D9,”21”)或=COUNTIF(D2:D9,D3),函数应用举例,假设函数If(),格式：IF(logical_test,value_if_true,value_if_false)功能：本函数对比较条件式(logical_test)进行测试，如果条件成立，则取第一个值(value_if_true)，否则取第二个值(value_if_false)举例 已知
11、单元格E3中存放考试分数，现要根据该分数判断学生是否及格=IF(E360,”不及格”,”及格”)例如 成绩等级与分数的关系：成绩80优良；60成绩80中；成绩60不及格。假设成绩存放在单元格D3中，则可以采用如下函数来实现判断和取得等级信息：=IF(D3=80,”优良”,IF(D3=60,”中”,”不及格”),练习案例：if函数练习.xls,自动筛选 高级筛选 使用到各种运算符 西文符号 比较符:=,=,逻辑符:AND(与),OR(或)通配符:?(代表单个字符),*(代表多个字符),记录的筛选,举例:成绩小于60:成绩=90 成绩在6080之间:成绩=60 AND 成绩=80 OR 班号=11
12、 B.成绩=80 AND 班号=11 班11及班21的学生 A.班=11 OR 班号=21 B.班=11 AND 班号=21 班11所有不及格的学生 A.成绩60 OR 班号=11 B.成绩60 AND 班号=11,思考题,自动筛选采用简单条件来快速筛选记录，将不满足条件的记录暂时隐藏起来，而将满足条件的记录显示在工作表上 通过“数据”菜单中“筛选”命令的“自动筛选”来实现的 例18 在上述成绩表中，筛选出成绩大于等于80分的记录 选定数据清单，选择“数据”菜单中的“筛选”命令 从“筛选”级联菜单中选择“自动筛选”选项，此时在标题栏中每个字段名右边出现了一个下拉箭头 单击“成绩”字段名的下拉箭
13、头，从下拉列表单击“自定义”选项，打开“自定义自动筛选方式”对话框 本题筛选条件：成绩=80。设置如下,自动筛选,例19 在上述成绩表中，查找成绩在7090分之间的记录 本例题筛选条件是：成绩=70 AND 成绩=90，因此在“自定义自动筛选方式”对话框中设置如下,高级筛选采用复合条件来筛选记录，并允许把满足条件的记录复制到另外的区域，以生成一个新的数据清单。先建立条件区域。条件区域的第一行为条件标记行，第二行开始是条件行。(1)同一条件行的条件互为“与”(AND)的关系，表示筛选出同时满足这些条件的记录 例20 查找成绩大于等于80分，且班号为“11”的所有记录 筛选条件：成绩=80 AND
14、 班号=11，条件区域表示：,2.高级筛选,(2)不同条件行的条件互为“或”(OR)的关系，表示筛选出满足任何一个条件的记录 例21 查找英语和计算机课程中至少有一科成绩大于90分的记录 英语90 OR 计算机90，条件区域表示：(3)对相同的列(字段)指定一个以上的条件，或条件为一个数据范围，则应重复列标题 例22 查找成绩大于等于60分，并且小于等于90分的姓“林”的记录,例23 在成绩表中，查找11班及21班中成绩不及格的所有记录 筛选条件为：(成绩60 AND 班号=11）OR（成绩60 AND 班号=21),表格数据排序,表格数据的排序原则 数据表规则区域命名必须包括”表头”区域排序
15、前要添加连续编号,以保护原始序列多重排序时只有主要关健字重复时才进行次要关健字的排序排序规则升序和降序符号优先,数字其次,日期由远而近为升序,逻辑值false在前,包含公式按结果排序.排序报警出现扩展提示要小心操作.排序方法单一条件排序多重条件排序序列排序恢复,简单排序,按关键值从小到大(或从小到大)的顺序进行排列 排序时，最多可以同时使用三个关键字 例24 将成绩表按“成绩”从高到低进行排序(1)选定数据清单(2)选择“数据”菜单中的“排序”命令，打开“排序”对话框(3)设置对话框,序列排序举例,可以通过序列排序实现一些特殊的查找在排序对话框中选“选项”就可以进行序列排序，如图,使用分类汇总
16、处理数据,分类汇总操作的基本原则分类原则,要分类的项目必须单独一列凡是需要汇总的列,数据区域中不要有空白单元格,以免分类中遗漏数据区内格式应统一操作原则:先排序,再汇总如何处理结构性分类汇总如何处理筛选式分类汇总(例库统学.xls),先要对数据清单按分类的关键字进行排序 例25 在成绩表中，按“班号”字段分类汇总“成绩”的平均分(即统计出各个班的平均分),结构性分类汇总,筛选式分类汇总,1.数学函数 2.统计函数 3.文本函数 4.日期和时间函数 5.数据库函数 6.逻辑函数 IF函数举例：在工作表的C8单元格中存放学生身高数（厘米），现要挑选170到175的人选，凡符合条件的显示“符合条件”
17、，不符合条件的不显示，采用的公式为：=IF(AND(C8=170,C8=175),”符合条件”，”)7.财务函数 8.查找与引用函数,常用函数,文本函数,连接文本&文本转换数值Value()字符长度Len()取字符串Right()、Left()、Mid(),演示从身份证号中取生日,日期函数运算,当前日期（TODAY）当前时间（NOW）求年、月、日求天数(day360),统计函数,取整 Int()四舍五入 round()排名次 Rank()求余数 Mod(),条件函数,逻辑运算And()、Or()、Not()假设函数（If)条件求和(sumif)条件计数(countif),图表与数据透视表,工作
18、表的修饰创建图表用图表进行数据分析数据透视表操作,修饰表格,表格修饰的作用表格数据格式的作用和处理手段对齐方式和表体结构的可视手段边框背景和分割阅读的处理方法表格数据的安全控制和保护手段条件格式和预警信息的控制手段,为什么要修饰表格,缺乏修饰的缺陷无法突出表格数据间的内在关系可读性差保护性差表格修饰的种类,数字内容更加易读,精确值处理负值突出显示（赤字）千元格式货币型数字格式日期型数字格式文本型数字格式,数字格式深入,结构可视性更强,对齐方式修饰水平垂直方向对齐文字方向的设置特殊对齐方式文字格式的修饰为工作表标签添加颜色,对齐方式深入,区域划分使表格更易阅读,边框和背景的手工设置边框和背景的自
19、动设置绘图框线与Word功能类似,绘图边框,表格数据的安全性,保护表格的作用和对象设置表格的锁定保护设置保护格式和保护密码测试被保护数据撤销保护为锁定表格设置可编辑区域允许用户编辑保护区域设置单元格公式的隐藏保护,显示预警信息,条件格式的作用条件格式的使用测试显示预警信息,创建图表体验数据分析效果,明确图表分析的目标与正确选择表格数据的关系合理的确定图表类型以正确表现分析的目标图表的编辑、修饰及打印,直方图线性图饼形图组合图,图表在数据分析中的作用,在表格中创建分析图表,确定分析目标明确表格数据的取值范围图表向导,用直方图突出显示数据间差异的比较情况,可以从不同角度,审视比较同一数据可以反映不
20、同类型数据关系的差异,用线形图分析数据间趋势的变化,显示一段时间内,不同类型数据的变化趋势,用饼图描述数据间比例分配关系的差异,反映某一数据组中各个部分与整体的比例分配关系.,图表的编辑技巧,常规编辑技巧改变图表大小移动图表位置图表内其他对象编辑图表的特殊编辑向图表添加数据删除图表内数据,图表相关名词术语,图例,数据标记,类型轴(X),刻度线,值轴（Y）,图表编辑前的准备工作,向图表中添加和删除数据,向图表中添加数据使用菜单添加数据命令,选择数据区域直接用鼠标拖动数据删除图表中的数据,修饰图表增加表现力,文字数据修饰线和阴影修饰背景修饰,图表中的修饰对象和定位标志,“框”的定位,标题框,图例框
21、,图块框,出现8个顶点“线”和定位,框线,轴线,刻度线,图形线,被选中后,不但显示线型,还在线段两端显示控制点系列图块,显示数据关系的彩色图形(表格的数据)文本和数字,操作前要先选中,有效控制图表打印,打印浏览与工作表内容一起打印单独打印,图表制作的技巧和综合运用,创建图表的取值技巧间隔取值多个图形的组合编辑组合图表框的拆分,案例:各部销售统计.xls,用图表进行数据分析,图表在转置中的分析作用图表类型变化和分析应用两组数据组合图标与应用,图表的转置不同的角度审视,转置的目标常规转置方法图表的转置表格的转置注意事项将表格设置为三维视图效果组成要素改变观察视角,利用图表类型表现管理目标,图表类型
22、和分析目标图表类型变化实例,将两组数据在同一图表中比较,向图表中添加数据组设置第2Y轴利用两个刻度分离两组数据,图表应用综合练习,问题的提出与图表分析目标解题过程制表成图转置改变类型设2Y轴改2Y周数据类型分离数据修饰图表,数据透视表的使用,建立数据透视表报告在数据表中重组数据关系透视分析数据透视分析图表,数据透视表,数据透视表在原有的数据库清单的基础上，可以利用建立数据透视表的功能建立一份新的、经过数据分析的报表,数据透视表,数据透视表的建立过程选择数据源：选定数据库中任一单元格选择菜单数据数据透视表和数据透视图，进入数据透视表向导在步骤之一对话框中，指定数据源类型及报表类型在步骤之二对话框
23、中，选定建立数据透视表的数据源区域,数据透视表,在步骤之三对话框中，按布局按钮，定义出现在透视表中的字段将行字段拖拽到左边的“行(R)”标志区将列字段拖拽到左边的“列(C)”标志区将要统计字段拖拽到“数据(D)”标志区，修改统计字段的统计方式如求和、均值,数据透视表,在步骤之三对话框中，定义数据透视表的显示位置新建工作表：独立的数据透视表现有工作表：给出左上角单元格地址按完成按钮，数据透视表建立完毕,数据透视图表,其操作步与数据透视表相同，只是：在步骤之一中，选择所需创建的报表类型为“数据透视图（及数据透视表）”,案例演示,创建数据透视表重组数据关系透视分析数据,数据透视表,实例讲解：右图所示
24、的是一张数据清单，现在要以这张数据清单作为数据透视表的数据源来建立数据透视表。,图1,案例文件:例库统学.xls,数据透视表,单击数据清单中的任一单元格。从【数据】菜单中选择【数据透视表和图表报告】项，出现【数据透视表和数据透视图向导】对话框，如图所示。选择创建数据透视表的数据源为数据清单，然后选择需要创建的报表类型。单击【下一步】按钮。,数据透视表,“分页”区，用于分类显示透视统计结果。“上表头”区，用于重组表格时设置字段的列向分类依据。“左表头”区，用于重组表格时设置记录的横向分类依据。“统计”区，用于重组表格时放置交叉统计用字段“字段列表”窗口，用于控制透视表中重组字段工作。,数据透视表,据透视表的创建后，可根据不同需求,调整视图,图8,数据透视图,数据透视表,案例演示如何改变字段显示如何排序如何组合数据如何添加计算字段如何改变数据透视表样式,案例文件:透视表资料.xls,EXCEL宏与VBA编程,宏的录制宏的编辑宏的应用演示VBA编程示例,谢谢！,
