Office 2010高级应用案例教程第二篇 Excel高级应用

Excel高级应用教程目录第一节EXCEL公式及函数的高级应用 (1)1.1 数组公式及其应用 (1)1.1.1 数组公式的输入、编辑及删除 (1)一．数组公式的输入 (1)二．编辑数组公式 (2)三．删除数组公式 (3)1.1.2 数组公式的应用 (3)一．用数组公式计算两个数据区域的乘积 (3)二．用数组公式计算多个数据区域的和 (3)三．用数组公式同时对多个数据区域进行相同的计算 (3)1.2 常用函数及其应用 (4)1.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数 (4)一．无条件求和SUM函数 (4)二．条件求和SUMIF函数 (4)三．SUMPRODUCT函数 (5)1.2.2 AVERAGE函数 (5)1.2.3 MIN函数和MAX函数 (5)1.2.4 COUNT函数和COUNTIF函数 (5)1.2.5 IF函数 (6)1.2.6 AND函数、OR函数和NOT函数 (6)1.2.7 LOOKUP函数、VLOOKUP函数和HLOOKUP函数 (7)一．LOOKUP函数 (7)二．VLOOKUP函数 (7)三．HLOOKUP函数 (8)1.2.8 MATCH函数 (8)1.2.9 INDEX函数 (8)一、返回数组中指定单元格或单元格数组的数值。

(8)二、返回引用中指定单元格。

(9)1.2.10 ADDRESS函数 (9)1.2.11 INDIRECT函数 (9)1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数 (10)一．TRANSPOSE函数 (10)二．MINVERSE函数 (10)三．MMULT函数 (10)1.2.13 ROUND函数 (10)第二节EXCEL数据分析处理 (11)2.1 数据排序 (11)2.1.1 数据排序的规则 (11)2.1.2 数据排序步骤 (11)2.1.3 自定义排序 (12)2.2 数据的查找与筛选 (12)2.2.1 记录单查找 (13)一．查找数据记录 (13)二．修改或删除记录 (13)三．添加新的记录 (14)2.2.2 自动筛选与自定义筛选 (14)一．自动筛选 (14)二．自定义筛选方式 (14)2.2.3 高级筛选 (15)一．一般情况下的高级筛选 (15)二．计算条件情况下的高级筛选 (16)2.3 数据的分类与汇总 (16)2.3.1 进行分类汇总 (16)2.3.2 分类汇总的撤消 (17)2.4 数据透视表 (17)2.4.1 建立数据透视表 (17)2.4.2 数据的透视分析 (19)第三节EXCEL图表处理 (19)3.1 图表类型 (19)3.2 图表的建立 (19)3.3 图表的编辑、修改及格式化 (21)一．设置坐标、标题、图例等的格式 (21)二．改变图表大小 (21)三．移动或复制图表 (21)四．添加数据标志 (21)五．改变图表颜色、图案、边框 (22)3.4 地区销售分布图表的建立 (22)3.5 动态图表的建立 (24)第四节EXCEL数据分析工具的应用 (24)4.1 模拟运算表 (24)4.1.1 单变量模拟运算表 (24)4.1.2 双变量模拟运算表 (25)4.2 单变量求解 (25)4.3 规划求解 (25)4.3.1 求解优化问题 (26)4.3.2 求解方程组 (27)4.4 方案分析 (28)4.4.1 建立方案 (28)4.4.2 显示方案 (29)4.4.3 修改、删除或增加方案 (29)4.4.4 建立方案报告 (30)4.5 数据分析工具库 (30)第一节EXCEL公式及函数的高级应用公式和函数是Excel最基本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。

《Office 2010办公高级应用》配套教学教案第1章“通知”文档效果“会议日程安排表”文档效果小结1、掌握文本地输入与编辑,字符格式,段落格式,下划线等地设置方法。

2、掌握编号与项目符号,段落缩进,录制与使用宏,拼写与语法检查,发送文档等操作。

3、掌握创建与编辑表格,表格内容,表格边框,页面页眉页脚等地设置方法。

：制作员工档案表公司需求更新员工档案,请根据前面所学知识制作一份公司员工档案表完成后地效果如下图所示。

第2章“公司简介”文档效果“招聘流程”文档效果小结1、掌握页边距,纸张大小,文本格式,页面底纹,插入与设置艺术字,文档页面设置,分栏等地操作防范。

2、掌握插入形状,设置形状,插入图片,图片排列,图片效果,图片调整,插入SmartArt图形,编辑SmartArt图形等地操作方法。

3、掌握表格地创建,单元格格式设置等操作方法。

想一想：？如何设置文档地页边距与纸张方向？并进行编辑？地项目,根据项目地特点与项目周围地环境制作一个楼要求制作地文档给人清新自然地感觉。

参考效果如图所示。

：制作周工作计划公司要求员工在每周五下午提交一份下周地周工作计划。

请以公司员工地身份制作一份周工作计划文档,并作为以后地周工作计划模板来使用。

参考效果如下图所示。

第3章“公司管理制度”文档效果“邀请函”文档效果小结1、掌握样式地新建,修改与应用方法;掌握题注,封面与目录地添加方法。

2、掌握邀请函地制作方法,文档地排版,审阅与比较方法。

3、掌握长文档地浏览,拆分与模板地定义与更新方法。

思考与作业想一想：（1）怎样新建并设置样式？（2）如何在文档中应用设置好地样式？（3）如何为文档添加封面与目录？（4）怎么制作邀请函？（5）如何为文档添加批注并进行修订？（6）如何自定义文档模板并进行更新？练一练：练习1：制作项目调查报告公司想进行一个大学生就业调查,根据最后地报告分析大学生与社会工作者之间地区别,以便更好为公司发展服务。

Excel1）：在考生文件夹%testdir%中，完成下列操作。

文涵是大地公司的销售部助理，负责对全公司的销售情况进行统计分析，并将结果提交给销售经理。

年底，她根据各门店提交销售报表进行统计分析。

打开“计算机设备全年销量统计表.xlsx”，帮助文涵完成以下操作。

1．将“sheet1”工作表命名为“销售情况”，将“sheet2”命名为“平均单价”。

2．在“店铺”列左侧插入一个空列，输入列标题为“序号”，并以001、002、003……的方式向下填充该列到最后一个数据行。

3．将工作表标题跨列合并后居中并适当调整其字体、加大字号，并改变字体颜色，适当加大数据表行高和列宽，设置对齐方式及销售额数据列的数值格式（保留2位小数），并为数据区域增加边框线。

4．将工作表“平均单价”中的区域B3:C7定义名称为“商品均价”（范围为工作簿）。

运用公式计算工作表“销售情况”中F列的销售额，要求在公式中通过VLOOKUP函数自动在工作表“平均单价”中查找相关商品的单价，并在公式中引用所定义的名称“商品均价”。

5．为工作表“销售情况”中的销售数据创建一个数据透视表，放置在一个名为“数据透视分析”的新工作表中，要求针对各类商品比较各门店每个季度的销售额。

其中：商品名称为报表筛选字段，店铺为行标签，季度为列标签，并对销售额求和。

最后对数据透视表进行格式设置，使其更加美观。

6．根据生成的数据透视表，在透视表下方创建一个簇状柱形图，图表中仅对各门店四季度笔记本的销售额进行比较。

7．保存“计算机设备全年销量统计表.xlsx”文件。

2）：在考生文件夹%testdir%中，完成下列操作。

小蒋是一位中学教师，在教务处负责初一年级学生的成绩管理。

由于学校地处偏远地区，缺乏必要的教学设施，只有一台配置不太高的PC可以使用。

他在这台电脑中安装了Microsoft Office，决定通过Excel来管理学生成绩，以弥补学校缺少数据库管理系统的不足。

实验二 Excel 2010的高级操作一、实验目的通过一系列的操作实验，要求学生熟练掌握电子表格的常用操作。

1．熟练掌握Excel 2010中图表的制作、更改、修饰等操作。

2．熟练掌握Excel 2010中数据的排序、筛选、分类汇总等数据管理和分析。

3．熟练掌握Excel 2010的综合应用。

二、实验内容1.图表的创建和修饰操作。

2.数据的排序操作。

3.数据的自动筛选和高级筛选操作。

4.数据的分类汇总操作。

5.综合实例操作。

三、实验步骤(一)图表的创建和修饰操作1.创建簇状柱形图工作表打开工作簿“cunkuan.xls”，利用“家庭存款帐本”工作表中的数据建立一个“簇状柱形图”图表工作表，实验的操作结果如图2.1所示。

图2.1 簇状柱形图工作表示例1)操作要求（1）类轴：“存款日期”；数值轴：“金额”和“本息”。

（2）图表标题：金额、本息对照图。

（3）分类轴标题：存款日期。

（4）数值轴标题：（元）。

（5）图例位置：靠上。

（6）将图表工作表命名为“金额、本息对照图”。

（7）将图表工作表保存到工作薄中。

2)操作方法（1）打开工作簿“cunkuan.xls”，单击“家庭存款帐本”工作表标签，使之成为当前工作表。

（2）用鼠标拖动的方法，先选定“存款日期”列的数据，然后按住Ctrl键再分别选定“金额”和“本息”列的数据，如图2.2所示。

提示：在选定数据区域时（不论是连续的，还是不连续的），都要一次性准确选择有效的数据区域，否则将会造成图表数据源引用错误，无法正确建立图表。

图2.2 选定数据区域示例（3）单击“插入”｜“图表”功能区中的“柱形图”命令按钮，选择“二维柱形图”中的“簇状柱形图”，即可生成对应的图表，如图2.3和图2.4所示。

图2.3 选择图表类型示例图2.4 簇状柱形图表（4）如果在插入图表之前没有选定数据或选定的数据不准确，可以单击图表，在“图表工具”｜“设计”功能区中单击“选择数据”命令按钮，弹出“选择数据源”对话框，如图2.5和2.6所示。

office2010办公软件高级应用教案Office2010办公软件高级应用教案课程名称：Office2010办公软件高级应用教学目的：1.了解Office2010办公软件的高级功能2.掌握Office2010办公软件的高级应用技巧3.培养学生高效率的办公能力教学重点：1.Office2010办公软件高级功能的介绍和操作2.Office2010办公软件高级应用技巧的掌握教学难点：1.Office2010办公软件高级应用的实际操作2.Office2010办公软件高级应用的技术细节教学方法：1.理论讲解与实践操作相结合2.小组讨论3.问题解答4.案例分析教学内容：第一节：Excel2010高级应用1.目标设定：如何使Excel数据更加精准和合理2.高级函数：VLOOKUP、HLOOKUP、MATCH、INDEX、IF等3.Excel高级图表制作4.Excel高级筛选技巧第二节：Word2010高级应用1.Word高级格式技巧2.Word高级合并技巧3.Word高级目录制作技巧4.Word高级表格应用技巧第三节：PowerPoint2010高级应用1.制作高质量PPT演示文稿2.PPT动画制作技巧3.PPT图表制作技巧4.PPT幻灯片设置技巧教学过程：第一节：Excel2010高级应用一、目标设定1.选定目标：目标应当是特定、可量化、具有挑战性、符合现实可能性和期望价值的。

2.设置绩效指标：确定实现目标所需要达到的结果和绩效指标，作为检测目标实现的衡量标准。

二、高级函数1. VLOOKUP函数：用于在数据表中按照关键字查找数据，返回指定范围内与关键字匹配的第一个单元格的数值。

2. HLOOKUP函数：与VLOOKUP函数作用相同，只不过是在行方向上查找。

3. MATCH函数：返回指定单元格在表格中的位置。

4. INDEX函数：返回列表或数组中指定位置的值。

5. IF函数：IF函数可以进行条件判断，如果满足条件，则返回指定值。

Microsoft Office Specialist认证篇Excel 2010专家级应用单元1 Excel公式与函数的高级应用 (2)任务1-1应用COUNTIF函数进行条件统计 (2)任务1-2应用COUNTIFS函数对符合条件的数据计数 (2)任务1-3应用SUMIFS函数对符合条件的数据求和 (3)任务1-4应用AVERAGEIFS函数统计符合条件的数据平均值 (4)任务1-5应用HLOOKUP函数进行数据查询 (4)任务1-6应用VLOOKUP函数进行数据查询 (5)任务1-7设置EXCEL选项——更改公式错误标识 (6)任务1-8设置Excel选项——启用迭代计算 (6)任务1-9 追踪单元格的公式引用 (7)任务1-10查找表格中不一致的公式引用 (8)任务1-11应用公式求值工具更正公式错误 (9)单元2 应用Excel分析和展示数据 (9)任务2-1合并多个区域中的数据 (9)任务2-2创建方案模拟分析数据 (11)任务2-3应用数据透视表分类汇总数据 (12)任务2-4应用切片器筛选数据 (13)任务2-5创建数据透视图 (13)任务2-6应用数据透视图展示分析数据 (14)任务2-7修改图表的样式 (15)任务2-8修改图表的数据源 (16)任务2-9为图表添加趋势线 (17)单元3应用宏和控件自动化文档 (18)任务3-1录制宏 (18)任务3-2录制并应用宏 (19)任务3-3指定宏到按钮创建交互式表格 (20)任务3-4应用控件控制工作表数据 (21)单元4 保护和共享数据 (22)任务4-1 加密工作簿 (22)任务4-2保护工作表以限制输入 (22)任务4-3为工作簿设定属性 (23)任务4-4共享工作簿 (24)任务4-5将工作表中数据导出为XML文件 (25)任务4-6显示共享工作簿中的修订 (26)单元1 Excel公式与函数的高级应用任务1-1应用COUNTIF函数进行条件统计在工作表“图书销售统计”的单元格M2中，添加一个函数以对“仓库2”中的图书种类进行计数。