EXCEL办公应用教程

《EXCEL办公应用教程》由会员分享，可在线阅读，更多相关《EXCEL办公应用教程（167页珍藏版）》请在金锄头文库上搜索。

1、Excel 高级应用高级应用_办公应用教程办公应用教程Excel高手高手VBA函函 数数分分 析析图图 表表操操 作作Excel 高级应用高级应用Excel高级应用基础高级应用基础Excel函数高级应用函数高级应用Excel图表高级应用图表高级应用数据透视表与数据透视图数据透视表与数据透视图Excel数据录入Excel数据处理Excel安全设置与数据保护Excel与其它应用程序的数据共享第一讲第一讲 Excel高级应用基础高级应用基础一、 Excel数据录入1、数据输入技巧相关知识：Excel支持的主要数据类型：N、T、D、L单元格格式自动更正数据有效性键盘快捷键第一讲第一讲 Excel高级应

2、用基础高级应用基础实例练习1：对“绩效考核”表快速录入数据工作表要求：某车间进行绩效考核，需要每天统计甲、乙、丙、丁四个班组的实绩产量和计划产量，并要标注是否完成任务。工作表包括：“日期”、“班组”、“实产量”、“计划产量”、“是否完成任务”五列。第一讲第一讲 Excel高级应用基础高级应用基础（1）选定单元格区域，右击）选定单元格区域，右击“设置单元格格式设置单元格格式”第一讲第一讲 Excel高级应用基础高级应用基础（2）简化重复录入文本：）简化重复录入文本：“工具工具”“自动更正自动更正选项选项”第一讲第一讲 Excel高级应用基础高级应用基础（3）设置数据有效性：）设置数据有效性：“数

3、据数据”“有效性有效性”日期列有效性日期列有效性班组列有效性班组列有效性第一讲第一讲 Excel高级应用基础高级应用基础（4）填充）填充“是否完成任务是否完成任务”列：选定单元格，在列：选定单元格，在编辑栏输入公式。编辑栏输入公式。如如:“=IF(C3D3,”是是”,”否否”)” 注意注意“文本型数据在文本型数据在IF函数中使用时要加双引号定界函数中使用时要加双引号定界第一讲第一讲 Excel高级应用基础高级应用基础一、 Excel数据录入2、数据导入技巧相关知识：Access数据库、数据表数据源导入外部数据数据刷新第一讲第一讲 Excel高级应用基础高级应用基础实例练习2：将Access数据

4、库表导入Excel中。(1)启动Access,建立“产品库.mdb”，并在其中利用向导建立“产品明细表”(2)在Excel中，“数据”“导入外部数据”“导入数据”第一讲第一讲 Excel高级应用基础高级应用基础定位数据源定位数据源二、 Excel数据处理1、数据高级筛选技巧相关知识：高级筛选条件区域条件区域的构造规则“数据”“筛选” “高级筛选”第一讲第一讲 Excel高级应用基础高级应用基础实例练习3：“或”条件筛选练习在“员工通讯簿”按下列条件筛选数据。(1)筛选条件是“部门”列中包括“人事部”或“营销部”的数据行。(2)筛选条件是“部门”为“开发部”，或“到职日期”为2003年的数据行。

5、第一讲第一讲 Excel高级应用基础高级应用基础构造条件区域构造条件区域 “数据数据”“筛选筛选” “高级筛选高级筛选”第一讲第一讲 Excel高级应用基础高级应用基础（1）同一列中的同一列中的“或或”条件条件第一讲第一讲 Excel高级应用基础高级应用基础（2）多列中的多列中的“或或”条件（条件条件（条件必须在不同行）必须在不同行）第一讲第一讲 Excel高级应用基础高级应用基础实例练习4：“与”条件筛选练习在“员工通讯簿”按下列条件筛选数据。筛选条件是“部门”列中包括 “营销部”，“性别”为“男”且“到职日期”为2003年1月1日以后的数据行。第一讲第一讲 Excel高级应用基础高级应用基

6、础同一行的多个同一行的多个“与与”条件条件第一讲第一讲 Excel高级应用基础高级应用基础实例练习5：“与”、“或”复合条件筛选练习在“员工通讯簿”按下列条件筛选数据。筛选条件是“部门”为 “营销部”且“到职日期”为2003年1月1日以后，“部门”为“开发部”且“到职日期”为2003年1月1日以前的数据行。第一讲第一讲 Excel高级应用基础高级应用基础条条 件件 区区 域域第一讲第一讲 Excel高级应用基础高级应用基础实例练习6：将公式作为条件的高级筛选练习在“员工信息簿”中按下列条件筛选数据。筛选条件:“部门”为 “营销部”或”编辑部” 工资值小于数据表中所有员工平均工资的员工信息。第一

7、讲第一讲 Excel高级应用基础高级应用基础（1）筛选条件）筛选条件，筛选出低于平均工资的员工信息筛选出低于平均工资的员工信息: 条件区域条件区域:$C$2:$D$3 在在D3D3单元格中输入单元格中输入“=AVERAGE(F11:F110)=AVERAGE(F11:F110)”在在C3C3单元格中输入单元格中输入“=F11$D$3=F11$D$3”第一讲第一讲 Excel高级应用基础高级应用基础（2）“数据数据”“筛选筛选” “高级筛选高级筛选”,在在高高级筛选对话框中设置列表区域和条件区域级筛选对话框中设置列表区域和条件区域. 第一讲第一讲 Excel高级应用基础高级应用基础（3）将筛选结

8、果复制到）将筛选结果复制到”输出表输出表”中中: 数据区域数据区域:A11:G59。 这时可以返回源数据表，执行这时可以返回源数据表，执行“数据数据”“筛筛选选” “全部显示全部显示”，将数据恢复全部显示状态。，将数据恢复全部显示状态。 第一讲第一讲 Excel高级应用基础高级应用基础（4）筛选条件）筛选条件，在输出表中构建条件区域在输出表中构建条件区域. “或或”条件条件第一讲第一讲 Excel高级应用基础高级应用基础（5）“数据数据”“筛选筛选” “高级筛选高级筛选”,在在高高级筛选对话框中设置列表区域和条件区域级筛选对话框中设置列表区域和条件区域. 第一讲第一讲 Excel高级应用基础高

9、级应用基础（6）筛选结果如下）筛选结果如下 第一讲第一讲 Excel高级应用基础高级应用基础二、 Excel数据处理2、数据有效性设定与规则相关知识：设置数据类型和有效范围有效性检验:对已有数据审核:圈释错误数据第一讲第一讲 Excel高级应用基础高级应用基础实例练习7：某单位要统计岗位考试成绩,为方便输入时更加准确,对表中的数据输入类型及范围设置相应的有效性.具体要求如下:考号为4位字符，不能多于或少于4位笔试和口试成绩为0100之间的数字，输入成绩时，显示输入提示信息输入错误时显示出错警告第一讲第一讲 Excel高级应用基础高级应用基础打开打开“实例表实例表岗位考试成绩岗位考试成绩.xls

10、”(1)在在”实例练习实例练习7”工作表中，选中考号区域（工作表中，选中考号区域（A3:A14)“数据数据” “有效性有效性”右击右击 “设置单元格格式设置单元格格式”“数数字字”第一讲第一讲 Excel高级应用基础高级应用基础（2）设定成绩的数据类型和有效性，选中）设定成绩的数据类型和有效性，选中(E3:F14)区域区域“数据数据” “有效性有效性”，分别设置最大值、最小值、提示、，分别设置最大值、最小值、提示、出错等信息出错等信息第一讲第一讲 Excel高级应用基础高级应用基础实例练习8：对“岗位考试成绩表”利用设置有效性规则，利用数据审核工具找出错误的数据并标明。设置笔试和口试成绩为01

11、00之间的数字找出已输入的错误数据第一讲第一讲 Excel高级应用基础高级应用基础打开打开“实例表实例表岗位考试成绩岗位考试成绩.xls”（1）在）在”实例练习实例练习8”工作表，选中工作表，选中(E3:F14)区域区域,第一讲第一讲 Excel高级应用基础高级应用基础（2）圈定错误数据）圈定错误数据.“工具工具” “公式审核公式审核” “显示显示公式审核公式审核工具栏，工具栏，单击单击”公式审核公式审核”工具栏中的工具栏中的”圈释无效数据圈释无效数据按钮按钮.第一讲第一讲 Excel高级应用基础高级应用基础二、 Excel数据处理3、数据分类汇总相关知识：排序求和、平均值、最大值、最小值分类

12、汇总合并计算第一讲第一讲 Excel高级应用基础高级应用基础实例练习9：对“3A公司员工基本信息表”进行分类汇总以统计各部门的收入情况,具体要求如下:l统计各个部门员工的平均工资注意注意:要先排序后汇总要先排序后汇总第一讲第一讲 Excel高级应用基础高级应用基础打开打开“实例表实例表3A公司员工基本信息表公司员工基本信息表.xls”(1)在在”实例练习实例练习9”工作表，对各部门员工工资分类汇总：工作表，对各部门员工工资分类汇总：单击单击“部门部门”列任一单列任一单元格，再单击常用工具元格，再单击常用工具栏上栏上“升序升序”按钮按钮 。单击数据表的任一单元格，单击数据表的任一单元格，“数据数

13、据”“分类汇总分类汇总”第一讲第一讲 Excel高级应用基础高级应用基础（2）单击）单击 分级显示按钮分级显示按钮第一讲第一讲 Excel高级应用基础高级应用基础实例练习10：对“3A公司员工基本信息表”进行分类汇总以统计各部门的收入情况,具体要求如下:统计各部门男、女员工奖金的最大值将分类汇总结果分页打印出来注意注意:要先对部门分类汇总要先对部门分类汇总,再对性别分类汇总再对性别分类汇总第一讲第一讲 Excel高级应用基础高级应用基础打开打开“实例表实例表3A公司员工基本信息表公司员工基本信息表.xls”(1)在在”实例练习实例练习10”工作表，统计各部门男、女奖金工作表，统计各部门男、女奖

14、金的最大值：的最大值：单击单击“部门部门”列任一单元格，列任一单元格，“数据数据” “排序排序” 。第一讲第一讲 Excel高级应用基础高级应用基础（2）单击数据表的任一单元格，单击数据表的任一单元格，“数据数据”“分类汇总分类汇总”先对先对“部门部门”汇总最大值汇总最大值部门汇总结果部门汇总结果第一讲第一讲 Excel高级应用基础高级应用基础（3）再单击数据表的任一单元格，再单击数据表的任一单元格，“数据数据”“分类汇总分类汇总”再对再对“性别性别”汇总最大值汇总最大值“性别性别”汇总结果汇总结果第一讲第一讲 Excel高级应用基础高级应用基础（4）分页显示汇总结果）分页显示汇总结果再单击数

15、据表的任一非空单元格，再单击数据表的任一非空单元格，“数据数据”“分类汇总分类汇总”，选中选中 “每组数据分页每组数据分页”，单击，单击确定确定“按钮。按钮。第一讲第一讲 Excel高级应用基础高级应用基础（4）“文件文件”“页面设置页面设置” “工作表工作表”打印预览打印预览第一讲第一讲 Excel高级应用基础高级应用基础删除分类汇总表的方法：“数据”“分类汇总”，单击“全部删除”按钮第一讲第一讲 Excel高级应用基础高级应用基础二、 Excel数据处理4、数据的合并计算相关知识：链接“数据”“合并计算”第一讲第一讲 Excel高级应用基础高级应用基础实例练习11：对“R公司工资簿”通过合

16、并计算得出一季度工资汇总表，要求：当修改了某个月份某项工资数值时，工资汇总表的数据随之更新第一讲第一讲 Excel高级应用基础高级应用基础打开打开“实例表实例表R公司工资簿公司工资簿.xls”(1)将工资表的标记性文字复制到将工资表的标记性文字复制到“工资汇总单工资汇总单”表中，表中，并将表头更改为并将表头更改为“R公司一季度工资单公司一季度工资单 ”第一讲第一讲 Excel高级应用基础高级应用基础（2）单击汇总表中（单击汇总表中（C3：I20）单元格区域，）单元格区域，“数据数据”“合并计算合并计算”，确定要合并的数据源位置，并添，确定要合并的数据源位置，并添加到列表中，选中加到列表中，选中

17、“创建连至源数据的链接创建连至源数据的链接”复选框。复选框。“确定确定”通过折叠钮选择引用数通过折叠钮选择引用数据位置，单击据位置，单击“添加添加”按钮，依次处理三个月按钮，依次处理三个月的数据。的数据。第一讲第一讲 Excel高级应用基础高级应用基础三、 Excel安全设置与数据保护对工作表、工作簿、单元格的保护相关知识：文件保存加密保护单元格区域第一讲第一讲 Excel高级应用基础高级应用基础实例练习12：对7高炉8月硅数统计表进行保护，具体要求：全部工作表中只有D列数据可以修改对工作表设置密码（123）对整个工作簿设置密码（456）第一讲第一讲 Excel高级应用基础高级应用基础打开打开

18、“实例表实例表7高炉高炉8月硅数统计月硅数统计.xls”(1)设置单元格保护设置单元格保护 选择选择D列数据，右击列数据，右击“设置单元格格式设置单元格格式” “保护保护”，取消，取消“锁定锁定”复选框，复选框，“确定确定”第一讲第一讲 Excel高级应用基础高级应用基础(2)设置单元格保护设置单元格保护 “工具工具”“保护保护”“允许用户编辑区域允许用户编辑区域”第一讲第一讲 Excel高级应用基础高级应用基础第一讲第一讲 Excel高级应用基础高级应用基础(3)设置工作簿保护设置工作簿保护 “工具工具”“选项选项”“安全性安全性”第一讲第一讲 Excel高级应用基础高级应用基础撤消对工作表

19、的保护（2）“工具”“保护” “撤消工作表保护”第一讲第一讲 Excel高级应用基础高级应用基础隐藏工作表的数据右击工作表名称“查看代码”第一讲第一讲 Excel高级应用基础高级应用基础四、 Excel与其它应用程序的数据共享相关知识：源数据、目标数据“选择性粘贴”、粘贴链接更新第一讲第一讲 Excel高级应用基础高级应用基础实例练习13：Excel与Word数据共享Excel与Powerpoint数据共享以绩效考核.xls为例第一讲第一讲 Excel高级应用基础高级应用基础Excel高级函数入门介绍Excel函数的应用第二讲第二讲 Excel函数高级应用函数高级应用一、 Excel高级函数入

20、门介绍1、函数及其结构2、输入公式与插入函数3、巧用Excel帮助理解函数第二讲第二讲 Excel函数高级应用函数高级应用一、 Excel高级函数入门介绍1、函数及其结构相关知识：函数的定义参数:常量、公式、函数、数组、单元格引用等嵌套函数结构：函数名（参数列表）第二讲第二讲 Excel函数高级应用函数高级应用函数函数逗号逗号等号等号左括号左括号常数常数 变量变量 嵌套函数嵌套函数 引用位置引用位置右括号右括号函数结构说明函数结构说明第二讲第二讲 Excel函数高级应用函数高级应用一、 Excel高级函数入门介绍第二讲练习表第二讲练习表函数结构说明函数结构说明.xls第二讲第二讲 Excel函

21、数高级应用函数高级应用一、 Excel高级函数入门介绍2、输入公式与插入函数相关知识：自动计算功能及公式的输入方法单元格引用：相对应用、绝对引用、混合引用函数向导第二讲第二讲 Excel函数高级应用函数高级应用一、 Excel高级函数入门介绍2、输入公式与插入函数自动计算功能及公式的输入方法Excel中公式以中公式以“=”开头开头.第二讲第二讲 Excel函数高级应用函数高级应用一、 Excel高级函数入门介绍2、输入公式与插入函数单元格引用：相对引用、绝对引用、混合引用引用方式和引用实例使用“$”符号来锁定引用。相对引用：A1、B5绝对引用：$A$5、$C$2 快捷键为F4混合引用：$A2、

22、D$1第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-1：在第二讲练习表.xls中，对“2-1”表中数据进行计算：要求计算进货数量和销货数量的小计值计算出每种产品的存货数量和销货金额统计表中产品的种类数第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-2：在第二讲练习表.xls中，对“2-2”表中数据进行计算：要求: 该表为一个对外投资收益表按所给公式计算, 填表其中对外投资比重、投资收益率数据格式设置为百分比形式，且保留两位小数将合计值添入第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-3：在第二讲练习表.xls中，在“2-3”中构造一个次幂表：要求：例：B

23、4单元格对应2.0的0次幂, C4对应2.0的1次幂D4单元格对应的是2.0的平方值, D8单元格对应2.4平方值, E6对应2.2的立方值以此类推, 利用公式计算，结果保留一位小数第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-4：在“2-1”表中，利用插入函数的方法插入打印日期。第二讲第二讲 Excel函数高级应用函数高级应用一、 Excel高级函数入门介绍3、巧用Excel帮助理解函数相关知识：F1键获取帮助Office助手搜索方式打开“帮助”系统第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用1、日期时间函数2、逻辑函数3、查找与引用函数第二讲第二

24、讲 Excel函数高级应用函数高级应用二、 Excel函数的应用1、日期与时间函数相关知识：DAY、MONTH、NOW、TODAY、YEAR、WEEKDAYHOUR、MINUTE、SECOND、TIME第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-5：在2-5表中，插入星期的数据。公式为：公式为：“=today=today（）（）”第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-6：在2-5表中，计算时间总计数据。编辑栏中输入编辑栏中输入“*60+”确定确定第二讲第二讲 Excel函数高级应用函数高级应用确定确定此例最简单的方法是：此例最简单的方法是：1 1）单击）单

25、击D4D4单元格，单元格，2 2）在编辑栏中输入公式：）在编辑栏中输入公式：“=HOUR(C4-B4)*60+MINUTE(C4-B4)=HOUR(C4-B4)*60+MINUTE(C4-B4)”3 3）将）将D4D4单元格的数字格式设置为单元格的数字格式设置为“常规常规”第二讲第二讲 Excel函数高级应用函数高级应用确定确定第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用2、逻辑函数相关知识：逻辑判断IF函数COUNTIF 函数SUMIF函数第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用2、逻辑函数uIF函数IF函数有三个参数，if（关

26、系或逻辑表达式，值1，值2）当表达式为真时，结果为值1 ，当表达式为假时，结果为值2 。第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-7：在2-7.xls中，判断月指标完成情况。要求:当实产量的和大于计划产量和时,标明”已完成”当实产量的和小于计划产量和时,标明”未完成”第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用2、逻辑求和函数uSUMIF函数计算符合条件的单元格的求和语法:SUMIF(,)第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-8：在第二讲练习表.xls的2-8表中,计算男女生的奖学金总计数值。Range:作为条件判断的单元格

27、区域作为条件判断的单元格区域Criteria:输入条件数据输入条件数据Sum_range:要计算合计的单元格范围要计算合计的单元格范围编辑栏公式为编辑栏公式为:第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用2、条件统计函数、条件统计函数uCOUNTIF函数函数用于单个条件的统计用于单个条件的统计COUNTIF(条件范围条件范围,统计条件统计条件)功能功能:对区域中满足单个指定条件的单元格进行计数。对区域中满足单个指定条件的单元格进行计数。说明说明条件范围只能是单元格引用。条件范围只能是单元格引用。统计条件可以使用引用、字符串表达式或数组，同时支持统计条件可以使用引用

28、、字符串表达式或数组，同时支持？和？和*的统计。的统计。第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-9：在”第二讲练习表.xls”的2-9countif练习表中,按要求进行统计。 =COUNTIF(B2:B10,A*)=COUNTIF(C:C,=5)-COUNTIF(C:C,=10)”第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数相关知识：LOOKUP、HLOOKUP、VLOOKUPINDEXOFFSET第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数u查找函数-LOO

29、KUP函数LOOKUP（查找值,查找范围,目标范围）通常情况下查找范围的首列或首行要求升序排列第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数u查找函数-VLOOKUP函数VLOOKUP（查找值,数据表,列序号,匹配条件）匹配条件:0:表示”精确查找”且支持无序查找;1:表示”近似查找且首列升序排列进行查找;第二讲练习表.xlsvlookup和lookup函数表第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-10：为“第二讲练习表.xls”中2-10表制作一个简单的成绩查询器。要求：在查询器的考号栏内输入职工考号，该职工的姓名、

30、职称、成绩就能够自动显示出来。原表原表在（在（A19：D20）构造查询表）构造查询表第二讲第二讲 Excel函数高级应用函数高级应用方法方法1：利用数组：利用数组形式形式B20输入：输入：C20输入：输入：D20输入：输入：方法方法2：利用向量：利用向量形式形式B20输入：输入：C20输入：输入：D20输入：输入：第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数u查找函数-HLOOKUP函数HLOOKUP（查找值,查找范围,行序号）通常用于水平方向查询第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-11：为“第二讲练习表.xls

31、”的2-11表按其下方的“评语对照表”为“评语”列填入评语。原表原表E3输入：输入：第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数u引用函数-INDEX函数功能：返回数组或单元格区域中指定单元格或单元格数组的数值。如引用的区域互不相连，需用括号括起来Array Array 数组数组Reference Reference 单元格区域单元格区域第二讲第二讲 Excel函数高级应用函数高级应用实例练习2-12：“第二讲练习表.xls”的2-12 表。要求：在F3中显示数据表第四行第二列的内容，即“李四”第二讲第二讲 Excel函数高级应用函数高

32、级应用二、 Excel函数的应用3、Excel查询与引用函数u引用函数-OFFSET函数将源引用根据制定的行、列偏移来产生新的单元格引用OFFSET（基点,行数,列数,高度,宽度）函数特征高度,宽度用于制定目标的单元格区域大小 高度,宽度允许用户使用负数作为参数第二讲第二讲 Excel函数高级应用函数高级应用公式公式=OFFSET(A5,2,2,5,3)=$C$7:$E$11)下图展示将下图展示将A5区域进行偏移后产生的新引用区域进行偏移后产生的新引用C7:E11公式公式=OFFSET(A5,2,2,5,3)=$C$7:$E$115行行3列列OFFSET的最终结果的最终结果第二讲练习表.xls

33、Offset函数练习第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数uROW函数被用来返回给定引用的行号ROW（参数）函数特征参数为希望得到行号的单元格或者单元格区域第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数uCOLUMN函数被用来返回给定引用的列标COLUMN（参数）函数特征参数为希望得到列标的单元格或者单元格区域第二讲第二讲 Excel函数高级应用函数高级应用二、 Excel函数的应用3、Excel查询与引用函数uROUNDUP函数向上舍入数字,远离零值ROUNDUP（数值,小

34、数位数）函数特征将数值按给定的小数位数向上舍入如ROUNDUP(3.2,0)的结果为4第二讲第二讲 Excel函数高级应用函数高级应用函数综合练习：生成工资条主要利用引用函数借助ROW函数产生循环序列的方式来实现,效果如下图.第二讲练习表第二讲练习表.xls生成工资条生成工资条第二讲第二讲 Excel函数高级应用函数高级应用Excel图表基础Excel动态图表第三讲第三讲 Excel图表高级应用图表高级应用一、 Excel图表基础建立和编辑图表相关知识:嵌入式和独立图表图表包括图表区和绘图区图表向导使用第三讲第三讲 Excel图表高级应用图表高级应用实例练习3-1：利用”第三讲练习表.xls”

35、中3-1数据制作三维饼图要求要求:为为去年销售额去年销售额制作三维饼图制作三维饼图,并添加并添加类别名称类别名称数据标数据标志志选择数据区域选择数据区域(A2:F3)第三讲第三讲 Excel图表高级应用图表高级应用单击单击”图表向导图表向导”按钮按钮第三讲第三讲 Excel图表高级应用图表高级应用效果图效果图右击图表的某个对象区右击图表的某个对象区,可以进行细致编辑可以进行细致编辑如下图右击图表区域快捷菜单如下图右击图表区域快捷菜单第三讲第三讲 Excel图表高级应用图表高级应用实例练习3-2：为”第三讲练习表.xls”中3-2表中的已有图表添加新数据列要求要求:为为“去年销售额去年销售额”图

36、表添加图表添加”今年预测销售量今年预测销售量”数据数据行行使用使用”源数据源数据”对话框添加数据对话框添加数据:在图表区内右击在图表区内右击”源数据源数据” ”系列系列” 单击单击”添加添加”按钮按钮第三讲第三讲 Excel图表高级应用图表高级应用在在”名称名称”文本框中选择文本框中选择A5单元格单元格(即即”今年预测销售量今年预测销售量”),在在”值值”文本框中文本框中选择选择B5:F5单元格区域单元格区域.单击单击”确定确定”按钮按钮第三讲第三讲 Excel图表高级应用图表高级应用二、 Excel动态图表一般的图表是静态图表,如何动态地显示一行或一列数据?1、自动筛选方法2、利用组合框选项

37、实现第三讲第三讲 Excel图表高级应用图表高级应用实例练习3-3：在“第三讲练习表.xls”的3-3表以“北分区店”列为类别制作动态柱形图。选择选择A2：A9单元格区域单元格区域“数据数据”“筛选筛选”“自动筛选自动筛选”第三讲第三讲 Excel图表高级应用图表高级应用选择选择A2：G9单元格区域单元格区域单击图表向导按钮单击图表向导按钮“完成完成”按钮按钮第三讲第三讲 Excel图表高级应用图表高级应用在自动筛选列选择某个项，图表数据动态改变在自动筛选列选择某个项，图表数据动态改变第三讲第三讲 Excel图表高级应用图表高级应用实例练习3-4：在“第三讲练习表.xls”的3-4表制作动态变

38、化的饼图。右击右击Excel工具栏任意处，选择工具栏任意处，选择“窗体窗体”命令命令在在“窗体窗体”工具栏中选择工具栏中选择“组合框。组合框。在图中位置拖动得到一个组合框在图中位置拖动得到一个组合框第三讲第三讲 Excel图表高级应用图表高级应用右击组合框右击组合框”设置控件格式设置控件格式“确定数据源区域确定数据源区域“$A$3:$A$9” 单元格链接框单元格链接框”$A$15”，单击，单击确定确定效果图效果图第三讲第三讲 Excel图表高级应用图表高级应用将将B2:G2的数据标签复制到的数据标签复制到B17:G17,如图如图在在A18单元格输入单元格输入”=INDEX(A3:A9,$A$1

39、5),回车确认回车确认,效果如下效果如下:第三讲第三讲 Excel图表高级应用图表高级应用将公式通过拖动复制到将公式通过拖动复制到B18:G18选择选择A17:G18区域区域,单击单击”图表向导图表向导”按钮按钮,在图表向导在图表向导对话框中对话框中,选择选择”饼图饼图”单击单击”完成完成”按钮按钮.第三讲第三讲 Excel图表高级应用图表高级应用通过组合框选择不同店名通过组合框选择不同店名,可动态显示该店的饼图可动态显示该店的饼图.第三讲第三讲 Excel图表高级应用图表高级应用小结:图表向导自动筛选制作动态图表组合框控制图表数据第三讲第三讲 Excel图表高级应用图表高级应用数据透视：数据

40、透视表用来分析、汇整大量数据的互动数据分析工具。包括：分页字段、数据项、行字段、列字段、项目等元素数据项数据项列字段列字段页字段页字段行字段行字段第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图数据透视表的建立数据透视表的编辑数据透视图的建立与查看数据透视表应用实例第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图一、数据透视表的建立以“4-1.xls”为例：操作方法如下：(1)鼠标定位在数据表中,“数据”“数据透视表及数据透视图”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)(3)(4)第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(5)出现出

41、现“数据透视表字段列表任务数据透视表字段列表任务”窗格后，分别拖窗格后，分别拖动字段名称到工作表的列、行、数据项位置，如图动字段名称到工作表的列、行、数据项位置，如图通过此表：我们通过此表：我们可以知道每个销可以知道每个销售人员销售产品售人员销售产品的日期，及当天的日期，及当天的销售金额的销售金额页字段页字段:类别类别;行字段行字段:销售日期销售日期;列字段列字段:销售人员销售人员第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图删除数据透视表的方法删除数据透视表的方法：利用数据透视表工具栏的：利用数据透视表工具栏的“数据透视表数据透视表”按钮按钮“选定选定”“整张表格整张表格”，再利

42、用，再利用“编辑编辑” “清除清除” “全部全部”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑1、将项目组成组合：以“4-2.xls”为例：操作方法如下：(1)选择A5:A8，右击“组及显示明细数据” “组合”选择要组选择要组合的项目合的项目第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)在数据组1中输入组合的名称“2003年11月”，在销售日期处输入字段的名称 “月份”输入组合输入组合的名称的名称输入此字输入此字段的名称段的名称第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(3)重复(1)(2)步,分别将同一月份的日期设定成组合,

43、并依序命名.设定为组设定为组合的数据合的数据按相同的步骤按相同的步骤,选择选择”取消组合取消组合”命令命令,即可显示所有的个别项目了即可显示所有的个别项目了第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑2、新建和删除字段：以“4-3.xls”为例：操作方法如下：(1)选择要新建的字段名称选择要新建的字段名称,拖拖动到数据透视表动到数据透视表新添加的字段新添加的字段第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑2、新建和删除字段：以“4-3.xls”为例：操作方法如下：(2)删除选择要删除的字段名称选择要删除的字段名称,拖动到数据透

44、视拖动到数据透视表以外的地方表以外的地方第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑3、显示和隐藏明细数据：以“4-4.xls”为例：操作方法如下：(1)右击要显示明细数据的字段”组合及显示明细数据” ”显示明细数据”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)出现”显示明细数据”窗口,在明细数据的字段列表中选择要显示的字段名,“确定”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图隐藏明细数据的操作:(1)右击要隐藏明细数据的字段”组合及显示明细数据” ”隐藏明细数据”选择要隐藏选择要隐藏明细的字段明细的字段第四讲第四讲 数据透

45、视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑4、分页查看数据透视表：以“4-6.xls”为例：操作方法如下：(1)单击页字段上类别字段右边的 按钮,选择要查看的类别名称单击确定第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)如下为”化妆品”类别的数据显示在数据透视表中第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑5、显示多个页面的数据透视表：以“4-6.xls”为例：操作方法如下：(1)单击数据透视表工具栏的 按钮,选择”分页显示”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2) 在“分页显示”窗口中,选择要分页显示

46、的字段名称,单击确定第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视表的编辑二、数据透视表的编辑6、改变数据汇总的方式：、改变数据汇总的方式：以以“4-7.xls”为例，求出每位销售人员每月平均销售的金额。为例，求出每位销售人员每月平均销售的金额。操作方法如下：操作方法如下：(1)在在“求和项：金额求和项：金额”字段上，右击字段上，右击“字段设置字段设置”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)在“数据透视表字段”窗口中，选择汇总方式为“平均值”，单击确定设置数字格式设置数字格式第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图二、数据透视

47、表的编辑二、数据透视表的编辑7、自定义公式：、自定义公式：以以“4-8.xls”为例，按销售人员的销售金额多少，设定为例，按销售人员的销售金额多少，设定“奖金百奖金百分比分比”的值，金额大于的值，金额大于5000的设为的设为10%，否则为，否则为5%。操作方法。操作方法如下：如下：(1)单击单击 按钮按钮“公式公式” “计算字段计算字段” 第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)在“插入计算字段”窗口中，输入名称:奖金百分比，输入公式：=IF（金额5000,10%,5%),单击确定结果结果第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(3)设置”奖金百分比”

48、字段数字格式:在”奖金百分比”项上右击”字段设置”命令第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(4)最后效果第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图三、数据透视图的建立与查看以“4-1.xls”为例：操作方法如下：(1)鼠标定位在数据表中,“数据”“数据透视表及数据透视图”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(2)(3)(4)第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(5)出现出现“数据透视表字段列表任务数据透视表字段列表任务”窗格后，分别拖窗格后，分别拖动字段名称到数据透视图中适当的位置，页字段：动字段名称到数据透视图

49、中适当的位置，页字段：“类类别别”;列字段列字段:“销售人员销售人员”；行字段：；行字段：“销售日期销售日期”；数据项：数据项：“产品产品”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(6)数据透视图效果第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图(7)单击页字段上类别字段右边的 按钮,选择要查看的类别名称：“化妆品”，单击确定第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图四、数据透视表应用实例根据网上的问卷调查，我们收集了300人的关于性别、居住区、学历、年资、年所得及每年旅游预算等项目，现在Excel中对这些数据进行分析第四讲第四讲 数据透视表和数据

50、透视图数据透视表和数据透视图应用实例1：计算各地区的男女人数（性别1代表男生，性别2代表女生）,以“4-9A.xls”为例操作步骤如下：操作步骤如下：（1）行字段）行字段:“居住区居住区”、列字段：、列字段：“性别性别”、数据项：、数据项：“性别性别”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（2）右击）右击“求和项：性别求和项：性别”“字段设置字段设置”（3）“汇总方式汇总方式”选择选择“计数计数”，确定确定第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（4）统计效果如下：）统计效果如下：“性别性别”列中，列中，1男；男；2 女女“居住区居住区”列中，列中，15表

51、示表示5个地区个地区第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图应用实例2：“年所得”最佳前五名的女性编号,以“4-9B.xls”为例操作步骤如下：操作步骤如下：（1）行字段）行字段:“编号编号”、列字段：、列字段：“性别性别”、 数据项：数据项：“年所得（万元）年所得（万元）”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（2）单击）单击 下拉箭头，保留选项下拉箭头，保留选项2勾选状态，勾选状态，确定确定（3）双击）双击“编号编号”栏栏第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（4）弹出）弹出“数据透视表数据透视表”字段窗口，单击字段窗口，单击 按钮

52、。按钮。 （5）如下图设置，）如下图设置，确定确定第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（6）回到）回到“数据透视表字段数据透视表字段”窗口，窗口，确定确定（7）完成效果如下图：）完成效果如下图：第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图应用实例3：计算出男女的年所得落在各区域的人数, 以“4-9C.xls”为例所得等级对照表所得等级对照表第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图操作步骤如下操作步骤如下： （sheet1表）表）（1）行字段）行字段:“所得等级所得等级”、列字段：、列字段：“性别性别” 数据项：数据项：“年所得（万元）年所得

53、（万元）”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（2）双击双击“年所得（万元）年所得（万元）”栏栏（3）设置汇总方式：）设置汇总方式：“计数计数”，确定确定（5）结果：）结果：第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图应用实例4：计算出各地区的“年所得”平均数,以“4-9D.xls”为例操作步骤如下：操作步骤如下：（1）行字段）行字段:“居住区居住区”、 数据项：数据项：“年所得（万元）年所得（万元）”第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图（2）双击双击“年所得（万元）年所得（万元）”栏栏（3）弹出）弹出“数据透视表字段数据透视表字段”窗口，选择窗口，选择 汇总方式为汇总方式为“平均值平均值”，确定确定（4）在）在“数据透视表字段数据透视表字段”窗口，单击窗口，单击 按钮。按钮。 （5）数值类型，小数位数为）数值类型，小数位数为0，确定确定（6）完成效果如下图：）完成效果如下图：第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图小结:数据透视表的建立和编辑数据透视图的建立应用第四讲第四讲 数据透视表和数据透视图数据透视表和数据透视图