EXCEL高级版教程实用教案

1、 数值计算 创建图表 组织列表 访问(fngwn)其他数据 创建图形和图表 自动化复杂的任务第1页/共90页第一页，共91页。主要(zhyo)内容 EXCEL的规范化操作 EXCEL函数与数据(shj)分析 图表与数据(shj)透视表 宏与VBA第2页/共90页第二页，共91页。任务(rn wu)一 创建第一个工作表 1 ctrl+N 创建工作簿 2 以填充输入日期和业绩 3 设置业绩的格式 4 插入 表|表 创建表后设置表样式 5 表工具 | 设计(shj) | 表样式选项 | 汇总行 6 插入 | 图表 | 柱状图 7 打印预览第3页/共90页第三页，共91页。第4页/共90页第四页，共9

2、1页。第5页/共90页第五页，共91页。正确认识管理对象的独立性，准确拆分管理项目至最小管理单元，并建立管理项目名称与表格结构中“列”的对应关系。为数据管理提供合理的数据结构。此项工作的关健在于明确表格列与行的逻辑关系，以便有效组织(zzh)一个独立的管理项目，形成可供分析的表格。案例1:规范(gufn)表格示例.xls, 第6页/共90页第六页，共91页。 管理项目与表格结构的关系 凡须独立管理的项目，必须单独设置项目名称，独占一列，避免混合设置 管理项目的独立性必须与扩展性结合考虑，以方便(fngbin)统计分析 表格结构设计必须考虑后期统计分析的应用状态。案例(n l)2:成绩表.xls

3、第7页/共90页第七页，共91页。第8页/共90页第八页，共91页。第9页/共90页第九页，共91页。 在单元格中输入数值、文本、日期/时间 (重点(zhngdin)讲日期) 序列填充的方法 自定义序列 同时在多个单元格中输入数据 数据有效性的设置 使用下拉列表输入数据的方法 使用多级下拉列表输入数据第10页/共90页第十页，共91页。第11页/共90页第十一页，共91页。第12页/共90页第十二页，共91页。第13页/共90页第十三页，共91页。第14页/共90页第十四页，共91页。第15页/共90页第十五页，共91页。第16页/共90页第十六页，共91页。第17页/共90页第十七页，共91

4、页。菜单名称分类原则必会命令文件与文件整体操作相关的命令新建（可通过模板学习制表）编辑与操作对象位置相关的命令无视图与屏幕显示相关的命令（状态和工具）无插入与添加对象相关的命令名称（定位和计算功能）分页符（强制分页）格式与对象形象相关的命令单元格（数字、对齐、保护）列（最佳列宽的路径）自动套用格式（快速、规范）条件格式（设置预警）工具与控制工具相关的命令保护（设置密码）公式审核（控制加工数据）自定义数据与数据处理相关的命令这一菜单中的命令属于提高内容筛选排序统计（分类汇总与透视报表）图表（隐藏）与图表操作相关的命令图表向导、类型窗口与窗口控制相关的命令冻结（表头）拆分（表格）帮助帮助学习和使用

5、，查找信息无第18页/共90页第十八页，共91页。第19页/共90页第十九页，共91页。 公式(gngsh)和函数 公式的使用 公式是用运算符将数据、单元格地址、函数等连接在一起的式子(sh zi)，以等号“=”开头 =5*10-20 常数运算 =A6+B1 对单元格A6和B1中的值相加 =SQRT(10+A2) 使用Excel函数，SQRT表示求开方根 1. 运算符 算术运算符、文本运算符和关系运算符 第20页/共90页第二十页，共91页。 公式举例公式举例 =83 =83* *25% 25% 表示表示8 8的立方再乘以的立方再乘以0.250.25，结果为，结果为128128 =5 =5*

6、*8/23-B2/4 8/23-B2/4 若单元格若单元格B2B2的值是的值是1212，则结果为，则结果为2 2 =” =”计算机计算机” & ” & ”电脑电脑” ” 运算结果是运算结果是”计算机电脑计算机电脑” = A1 & A2 = A1 & A2 把单元格把单元格A1A1和和A2A2中的文本进行连接中的文本进行连接 而生成一个而生成一个(y )(y )新的文本新的文本 = B2 = B242 42 比较结果为比较结果为TRUE (TRUE (设设B2B2的值是的值是12) 12) = B2 = B2=32 =32 比较结果为比较结果为FALSEFALSE

7、 = B2 = B212 12 比较结果为比较结果为FALSEFALSE = A3+80 = A3+80 假设假设A3A3为日期型数据，则为日期型数据，则A3+80A3+80表表 示日期示日期A3A3加上加上8080天天第21页/共90页第二十一页，共91页。 用于描述一个(y )条件。例如 5 = ABCD 或写成 =ABCD = 计算机 或写成 计算机 或写成 计算机比较(bjio)条件式第22页/共90页第二十二页，共91页。 当在一个单元格中输入一个公式后，Excel会自动加以运算，并将运算结果存放在该单元中。以后当公式中引用的单元格数据发生变动(bindng)时，公式所在单元格的值也

8、会随之变动(bindng) 例1 计算出学生的总评成绩(=机试40%+笔试60%)案例文件：计算机成绩.xls (1) 单击总评成绩第一个单元格E2，使之成为活动单元格 (2) 键入计算公式“=C2*0.4+D2*0.6”后按回车键，此时在单元格E2处显示出计算结果82.2 (3) 再次单击单元格E2使之成为活动单元格；单击“复制”按钮 (4) 选定区域E3:E6；单击“粘贴”按钮2. 公式的输入(shr)和复制第23页/共90页第二十三页，共91页。四种表示方式 (1) 相对地址：以列标和行号组成，如A1，B2，C3等 公式复制时，若引用公式的单元格地址发生变动，公式中的相对地址会随之变动

9、(2) 绝对地址：以列标和行号前加上符号“$”构成，如$A$1，$B$2等。公式复制时，公式中的绝对地址保持不变,下面式子表示引用d:book2.xls的sheet1的B5单元格=d:book2.xlsSheet1!B5 (3) 混合地址：它是上述两种地址的混合使用方式 如$A1(绝对列相对行)，A$1(相对列绝对行)等。公式复制时，公式中相对行和相对列部分会随引用公式的单元格地址变动而变动，而绝对行和绝对列部分保持不变 （4）名称地址：事先定义好的单元格区域 (5)三级地址:演示 各部销售统计.xls 出错信息 当公式(或函数)表达(biod)不正确时，系统将显示出错信息单元格地址(dzh)

10、表示方式第24页/共90页第二十四页，共91页。第25页/共90页第二十五页，共91页。 格式： =(, ,.) 举例： = SUM(C2:C6) = AVERAGE(A1:A3,C2:C6) SUM 求和数 AVERAGE 求平方值 MAX 求最大值 MIN 求最小值 COUNT 求数值数据的个数 COUNTIF 条件求数值数据的个数 IF 条件选择函数(hnsh)的使用第26页/共90页第二十六页，共91页。例2 2 计算基本工资总数，并将结果存放在单元格C7C7中 (1) (1) 单击单元格C7C7，使之成为活动单元格 (2) (2) 键入公式“=SUM(C2:C6)=SUM(C2:C6

11、)”，并按回车键 例3 3 计算补贴总数和扣除总数，并将结果分别存放在单元格D7D7和E7E7中 (1) (1) 选定D2:E7D2:E7区域 (2) (2) 单击“自动(zdng)(zdng)求和”按钮，即可对这两列分别求和，和数分别存放在两列的最后一个单元格上案例(n l)文件：工资表.xls函数(hnsh)应用举例第27页/共90页第二十七页，共91页。 例4 计算基本工资的平均值，并将结果存放在C8中 例5 利用函数MAX求出基本工资数的最大值，并将结果存放在单元格C9中 举例 假设单元格A1，A2，A3，A4的内容分别为1，2，空，“ABC”，则COUNT(A1:A4)的值为2 举例

12、 若要求出基本工资数大于等于500的职工人数，可以(ky)采用： = COUNTIF(C2:C6, ”=500”) 举例 在工作表(A1:D9)中，要求出班号为21的班学生人数，可以(ky)采用： = COUNTIF(D2:D9, ”21”) 或 = COUNTIF(D2:D9, D3)函数应用(yngyng)举例第28页/共90页第二十八页，共91页。格式： IF(logical_test,value_if_true,value_if_false) 功能：本函数对比较条件式(logical_test)进行测试，如果条件成立，则取第一个值(value_if_true)，否则取第二个值(valu

13、e_if_false) 举例 已知单元格E3中存放考试分数，现要根据该分数判断学生是否及格 =IF(E3, , =, : =, , , =, 逻辑符: AND(: AND(与), OR(), OR(或) ) 通配符: ?(: ?(代表单个字符), ), * *( (代表多个字符) )第30页/共90页第三十页，共91页。 举例: : 成绩小于60: 60: 成绩60=90=90 成绩在60608080之间: : 成绩=60 AND =60 AND 成绩=80=80 OR =80 OR 班号=11=11 B. B. 成绩=80 AND =80 AND 班号=11=11 班1111及班2121的学

14、生 A. A. 班=11 OR =11 OR 班号=21=21 B. B. 班=11 AND =11 AND 班号=21=21 班1111所有不及格的学生 A. A. 成绩60 OR 60 OR 班号=11=11 B. B. 成绩60 AND =80 11第34页/共90页第三十四页，共91页。 (2) 不同条件行的条件互为“或”(OR)的关系，表示筛选出满足任何一个条件的记录 例21 查找(ch zho)英语和计算机课程中至少有一科成绩大于90分的记录 英语90 OR 计算机90，条件区域表示： (3) 对相同的列(字段)指定一个以上的条件，或条件为一个数据范围，则应重复列标题 例22 查找

15、(ch zho)成绩大于等于60分，并且小于等于90分的姓“林”的记录 英语英语 计算机计算机 90 90 姓名姓名 成绩成绩 成绩成绩 林林* =60 =170, C8=175), ”符合条件”，”) 7. 财务函数 8. 查找与引用函数常用(chn yn)函数第43页/共90页第四十三页，共91页。文本(wnbn)函数 连接(linji)文本& 文本转换数值Value() 字符长度Len() 取字符串Right()、Left()、Mid()第44页/共90页第四十四页，共91页。日期函数(hnsh)运算 当前(dngqin)日期（TODAY） 当前(dngqin)时间（NOW） 求

16、年、月、日 求天数(day360)第45页/共90页第四十五页，共91页。统计(tngj)函数 取整 Int() 四舍五入(s sh w r) round() 排名次 Rank() 求余数 Mod()第46页/共90页第四十六页，共91页。条件(tiojin)函数 逻辑运算And()、Or()、Not() 假设函数（If) 条件(tiojin)求和(sumif) 条件(tiojin)计数(countif)第47页/共90页第四十七页，共91页。第48页/共90页第四十八页，共91页。修饰(xish)表格 表格修饰的作用 表格数据格式的作用和处理手段 对齐方式和表体结构的可视手段 边框背景和分割

17、阅读的处理方法 表格数据的安全控制和保护(boh)手段 条件格式和预警信息的控制手段第49页/共90页第四十九页，共91页。为什么要修饰(xish)表格 缺乏修饰的缺陷(quxin) 无法突出表格数据间的内在关系 可读性差 保护性差 表格修饰的种类第50页/共90页第五十页，共91页。数字内容(nirng)更加易读 精确值处理 负值突出显示（赤字(chz)） 千元格式 货币型数字格式 日期型数字格式 文本型数字格式数字格式(g shi)深入第51页/共90页第五十一页，共91页。结构(jigu)可视性更强 对齐方式修饰 水平垂直方向对齐 文字(wnz)方向的设置 特殊对齐方式 文字(wnz)格

18、式的修饰 为工作表标签添加颜色对齐(du q)方式深入第52页/共90页第五十二页，共91页。区域划分(hu fn)使表格更易阅读 边框和背景的手工设置 边框和背景的自动(zdng)设置 绘图框线与Word功能类似绘图(hu t)边框第53页/共90页第五十三页，共91页。表格(biog)数据的安全性 保护表格的作用和对象 设置表格的锁定(su dn)保护 设置保护格式和保护密码 测试被保护数据 撤销保护 为锁定(su dn)表格设置可编辑区域 允许用户编辑保护区域 设置单元格公式的隐藏保护第54页/共90页第五十四页，共91页。显示预警(y jn)信息 条件格式的作用 条件格式的使用 测试显

19、示预警(y jn)信息第55页/共90页第五十五页，共91页。第56页/共90页第五十六页，共91页。 直方图 线性图 饼形图 组合(zh)图第57页/共90页第五十七页，共91页。 确定分析目标 明确表格数据的取值范围(fnwi) 图表向导第58页/共90页第五十八页，共91页。 可以从不同角度,审视(shnsh)比较同一数据 可以反映不同类型数据关系的差异第59页/共90页第五十九页，共91页。 显示一段时间内,不同(b tn)类型数据的变化趋势第60页/共90页第六十页，共91页。 反映某一数据组中各个部分(b fen)与整体的比例分配关系.第61页/共90页第六十一页，共91页。 常规

20、编辑技巧 改变图表大小 移动图表位置 图表内其他对象编辑 图表的特殊编辑 向图表添加(tin ji)数据 删除图表内数据第62页/共90页第六十二页，共91页。图表(tbio)相关名词术语图例(tl)数据(shj)标记数据系列类型轴 (X)刻度线值轴（Y）第63页/共90页第六十三页，共91页。图表编辑(binj)前的准备工作图表标题图表标题绘图区绘图区图表区域图表区域数值轴数值轴 标题标题数值轴数值轴分类轴分类轴数据点数据点数据系列数据系列图表标题图表标题图例标示图例标示图例图例网格线网格线分类轴分类轴 标题标题第64页/共90页第六十四页，共91页。向图表中添加(tin ji)和删除数据

21、向图表中添加数据 使用菜单(ci dn)添加数据命令,选择数据区域 直接用鼠标拖动数据 删除图表中的数据第65页/共90页第六十五页，共91页。 文字数据(shj)修饰 线和阴影修饰 背景修饰第66页/共90页第六十六页，共91页。图表中的修饰对象和定位(dngwi)标志 “框”的定位,标题框,图例框,图块框,出现8个顶点 “线”和定位,框线,轴线,刻度线,图形线,被选中后,不但显示线型,还在线段两端显示控制点 系列图块,显示数据关系(gun x)的彩色图形(表格的数据) 文本和数字,操作前要先选中第67页/共90页第六十七页，共91页。有效控制图表(tbio)打印 打印浏览 与工作表内容(n

22、irng)一起打印 单独打印第68页/共90页第六十八页，共91页。第69页/共90页第六十九页，共91页。用图表进行(jnxng)数据分析 图表在转置中的分析作用 图表类型变化和分析应用 两组数据(shj)组合图标与应用第70页/共90页第七十页，共91页。图表的转置不同(b tn)的角度审视 转置的目标 常规(chnggu)转置方法 图表的转置 表格的转置 注意事项 将表格设置为三维视图效果 组成要素 改变观察视角第71页/共90页第七十一页，共91页。利用图表类型表现(bioxin)管理目标 图表类型和分析(fnx)目标 图表类型变化实例第72页/共90页第七十二页，共91页。将两组数据

23、在同一图表(tbio)中比较 向图表(tbio)中添加数据组 设置第2Y轴 利用两个刻度分离两组数据第73页/共90页第七十三页，共91页。图表应用综合(zngh)练习 问题的提出与图表分析目标解题过程制表成图转置改变类型设2Y轴改2Y周数据类型分离(fnl)数据修饰图表第74页/共90页第七十四页，共91页。第75页/共90页第七十五页，共91页。数据(shj)透视表 数据透视表数据透视表 在原有的数据库清单的基础上，可在原有的数据库清单的基础上，可以利用以利用(lyng)(lyng)建立数据透视表建立数据透视表的功能建立一份新的、经过数据分的功能建立一份新的、经过数据分析的报表析的报表第7

24、6页/共90页第七十六页，共91页。数据(shj)透视表 数据透视表的建立过程数据透视表的建立过程 选择选择(xunz)(xunz)数据源：选定数据库中任数据源：选定数据库中任一单元格一单元格 选择选择(xunz)(xunz)菜单菜单 数据数据数据透视表数据透视表和数据透视图和数据透视图 ，进入数据透视表向导，进入数据透视表向导 在步骤之一对话框中，指定数据源类型及报在步骤之一对话框中，指定数据源类型及报表类型表类型 在步骤之二对话框中，选定建立数据透视表在步骤之二对话框中，选定建立数据透视表的数据源区域的数据源区域第77页/共90页第七十七页，共91页。数据(shj)透视表 在步骤之三对话框

25、中，按在步骤之三对话框中，按 布局布局 按钮，定义出现在透视表中的字段按钮，定义出现在透视表中的字段 将行字段拖拽到左边的将行字段拖拽到左边的“行行(R)”(R)”标志区标志区 将列字段拖拽到左边的将列字段拖拽到左边的“列列(C)”(C)”标志区标志区 将要统计将要统计(tngj)(tngj)字段拖拽到字段拖拽到“数据数据(D)”(D)”标志区，修改统计标志区，修改统计(tngj)(tngj)字段的字段的统计统计(tngj)(tngj)方式如求和、均值方式如求和、均值第78页/共90页第七十八页，共91页。数据(shj)透视表 在步骤之三对话框中，定义数据透视表在步骤之三对话框中，定义数据透视

26、表的显示的显示(xinsh)(xinsh)位置位置 新建工作表：独立的数据透视表新建工作表：独立的数据透视表 现有工作表：给出左上角单元格地址现有工作表：给出左上角单元格地址 按按 完成完成 按钮，数据透视表建立完毕按钮，数据透视表建立完毕第79页/共90页第七十九页，共91页。数据(shj)透视图表 其操作步与数据透视表相同其操作步与数据透视表相同(xin(xin tn tn) )，只是：，只是： 在步骤之一中，选择所需创建的报表类型为在步骤之一中，选择所需创建的报表类型为“数据透视图（及数据透视表）数据透视图（及数据透视表）”第80页/共90页第八十页，共91页。案例(n l)演示 创建数据透视表 重组数据关系(gun x) 透视分析数据第81页/共90页第八十一页，共91页。数据(shj)透视表 实例讲解实例讲解(jingji)(jingji)： 右图所示的是一张数据清单，现右图所示的是一张数据清单，现在要以这张数据清单作为数据透在要以这张数据清单作为数据透视表的数据源来建立数据透视表。视表的数据源来建立数据透视表。 图1案例(n l)文件:例库统学.xls第82