Excel2013教程(高级)
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
图表外观更美观、更专业、布局和样式更多 易于使用的数据透视表、数据透视图 快速连接外部数据源
1.1 Excel的功能与界面
1.1.2 Excel的用户界面
整个界面由功能区和工作表区组成； 功能区有：Office按钮、选项卡、组、快速访问工具栏、标题栏、状态栏。
Office按钮：相当于早期的“文件”菜单； 选项卡：面向 任务，包括功能控件。“开始”有日常操作功能，“页面布局”与打印有关
内容提要
1 数据处理与分析基础 P4 2 利用函数进行数据分析 P29 3 利用图表进行数据分析 P42 4 利用透视表（图）进行数据分析 P72 5 构建动态数据分析报表 P96 6 宏与VBA在数据分析中的应用 P114 7 Excel 的数据分析工具简介 P146
1、数据处理与分析基础
学习目标 1 认识Excel的功能与界面 2 学会利用数据条件格式进行数据处理 3 学会利用排序、筛选、分类汇总功能进行数据分
析
1、数据处理与分析基础
1.1 Excel的功能与界面 1.2 数据的输入、编辑与运算 （案例：销售产品基本信息表、销售记录汇总表） 1.3 利用数据条件格式进行数据处理 1.4 利用排序、筛选、分类汇总功能进行数据分析
1.1 Excel的功能与界面
1.1.1 Excel新增的主要功能
（1）取消了菜单方式，采用了面向结果的用户界面，易于找到。 （2）更强大的数据管理能力和安全性。（如更多的行和列1048576×16384，
1600万种颜色） （3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方
便地构造动态数据报表。
（4）其他方面：
提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入
1.1 Excel的功能与界面
1.1.1 Excel新增的主要功能
改进的筛选和排序功能（可按日期和颜色排序 ）
知识点： 条件格式有以下几种： （1）突出显示单元格规则：对选定区域内满足条件的 单元格突出显示，默认的规则是用某种色彩填充单元 格背景。条件包括：大于、小于、等于、文本包含等
。 （2）项目选取规则：对选定区域内小于或大于某个阈
值的单元格实施条件格式。条件包括：值最大的10项 、高于平均值等。
（3）数据条。以色彩条形图直观地表示单元格数据。 数据条长度代表数值的大小 。
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
思考： 公式可分为几大类？
可分三大类： 普通公式：如=A1+B1 , =sum(a1:b5) 数组公式：如 {=average(B1:B10-A1:A10)} （使用数组公式可减少存储空间，提高工作效率，详见“ 综合实例”工作薄中相关内容） 命名公式：如：
Data名称表示a1:a10 =sum(data)
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
知识点： 跨表引用。 根据“产品编号”自动返回产品基本信息表“系列”等字段内容（vlookup函数）； 如E3中公式： =VLOOKUP($D2,产品基本信息表!$B$3:$F$38,2,FALSE) 公式计算字段“系列”、“产品名称”、“销售单价”、“成本单价”
任务1：在“产品营销数据处理与分析实例”工作薄建立形如下面的工作表，名称为“产品基本信息” 企业所有商品基本信息列表
知识点： 表格内容了解、新建工作薄、新建或改名工作表； 格式化工作表。包括对齐方式、设置单元格格式（下划线、列宽、边框、填充
颜色）
Hale Waihona Puke Baidu
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
任务2： 建立表框架（见实例）； 区分数据来源（原始的、引用的、计算的）； 输入公式（从信息表引用的、本表计算的）； 数据复制（客户名称、发货日期、订单号、数量）。
思考： 什么是公式？其价值何在？
公式是由“=”号或“+”号开头，由常数、函数、单元格引用 以及运算符组成的式子。其价值不仅是计算，重要的是 构建计算模型。
1.3 利用数据条件格式进行数据处理
Excel2013高级教程 数据统计与处理分析
Excel是Office软件中的核心成员，是最优秀的电 子表格软件之一，具有强大的数据处理和数据分 析能力，是个人及办公事务中进行表格处理和数 据分析的理想工具之一。
如何利用Excel的函数、图表、高级分析工具、 VBA程序等功能进行数据分析是本次学习的重点。
予设置。利用此功能，可将单元格中的满足条件的数据进行特殊标记，以便直观 地查看。 方法：
选中数据区—开始—样式—条件格式 任务3：对“销售记录汇总”表进行 如下操作：
（1）把“销售单价”以图标集形式显示 （2）把“销售额”以数据条形式显示
（3）把 “毛利”低于平均值的数据设置为黄色。
1.3 利用数据条件格式进行数据处理
·······（有书也称主菜单） 组：每个组都与特定任务相关；（有书也称工具栏） 快速访问工具栏：独立显示，默认有“保存”、“撤销”和“恢复“三按钮，可以自定义；
标题栏：显示工作薄名称。 状态栏：宏录制按钮、查看方式、缩放工具。
1.2 数据的输入、编辑与运算
目标：建立某企业产品销售数据统计报表（两个工作表） 1.2.1建立产品基本信息表
要找的值
查找区域
第2列
精确比较
相关知识：Vlookup函数的使用
格式： Vlookup（查找的值，查找区域，返回的列号，选项）
功能： 在表格或数组的首列查找值 ，并返回表格或数组中其它列的值 。
选项： FALSE：精确匹配，若找不到返回#N/A TRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。 （Excel中的函数帮助可能有误，请在编辑栏输入公式时查看选项含义）
VLOOKUP的稍高级应用见综合实 例中相关练习
1.2 数据的输入、编辑与运算
本任务中相关公式说明：
销售额=数量 * 销售单价 总成本 =数量 * 成本单价 毛利= 销售额-总成本 销售数据可从“销售原始”工作表复制而得。
1.3 利用数据条件格式进行数据处理
概述： 条件格式功能，指的是当单元格中的数据满足某种条件时就设置某种格式，否则不
1.1 Excel的功能与界面
1.1.2 Excel的用户界面
整个界面由功能区和工作表区组成； 功能区有：Office按钮、选项卡、组、快速访问工具栏、标题栏、状态栏。
Office按钮：相当于早期的“文件”菜单； 选项卡：面向 任务，包括功能控件。“开始”有日常操作功能，“页面布局”与打印有关
内容提要
1 数据处理与分析基础 P4 2 利用函数进行数据分析 P29 3 利用图表进行数据分析 P42 4 利用透视表（图）进行数据分析 P72 5 构建动态数据分析报表 P96 6 宏与VBA在数据分析中的应用 P114 7 Excel 的数据分析工具简介 P146
1、数据处理与分析基础
学习目标 1 认识Excel的功能与界面 2 学会利用数据条件格式进行数据处理 3 学会利用排序、筛选、分类汇总功能进行数据分
析
1、数据处理与分析基础
1.1 Excel的功能与界面 1.2 数据的输入、编辑与运算 （案例：销售产品基本信息表、销售记录汇总表） 1.3 利用数据条件格式进行数据处理 1.4 利用排序、筛选、分类汇总功能进行数据分析
1.1 Excel的功能与界面
1.1.1 Excel新增的主要功能
（1）取消了菜单方式，采用了面向结果的用户界面，易于找到。 （2）更强大的数据管理能力和安全性。（如更多的行和列1048576×16384，
1600万种颜色） （3）更强大的表功能，提供了全新的数据引用方式，称为结构化引用，可以方
便地构造动态数据报表。
（4）其他方面：
提供了大量预定义主题和样式 丰富的条件格式 自动调整编辑栏 函数记忆式输入
1.1 Excel的功能与界面
1.1.1 Excel新增的主要功能
改进的筛选和排序功能（可按日期和颜色排序 ）
知识点： 条件格式有以下几种： （1）突出显示单元格规则：对选定区域内满足条件的 单元格突出显示，默认的规则是用某种色彩填充单元 格背景。条件包括：大于、小于、等于、文本包含等
。 （2）项目选取规则：对选定区域内小于或大于某个阈
值的单元格实施条件格式。条件包括：值最大的10项 、高于平均值等。
（3）数据条。以色彩条形图直观地表示单元格数据。 数据条长度代表数值的大小 。
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
思考： 公式可分为几大类？
可分三大类： 普通公式：如=A1+B1 , =sum(a1:b5) 数组公式：如 {=average(B1:B10-A1:A10)} （使用数组公式可减少存储空间，提高工作效率，详见“ 综合实例”工作薄中相关内容） 命名公式：如：
Data名称表示a1:a10 =sum(data)
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
知识点： 跨表引用。 根据“产品编号”自动返回产品基本信息表“系列”等字段内容（vlookup函数）； 如E3中公式： =VLOOKUP($D2,产品基本信息表!$B$3:$F$38,2,FALSE) 公式计算字段“系列”、“产品名称”、“销售单价”、“成本单价”
任务1：在“产品营销数据处理与分析实例”工作薄建立形如下面的工作表，名称为“产品基本信息” 企业所有商品基本信息列表
知识点： 表格内容了解、新建工作薄、新建或改名工作表； 格式化工作表。包括对齐方式、设置单元格格式（下划线、列宽、边框、填充
颜色）
Hale Waihona Puke Baidu
1.2 数据的输入、编辑与运算
1.2.2 建立“销售记录汇总”表
任务2： 建立表框架（见实例）； 区分数据来源（原始的、引用的、计算的）； 输入公式（从信息表引用的、本表计算的）； 数据复制（客户名称、发货日期、订单号、数量）。
思考： 什么是公式？其价值何在？
公式是由“=”号或“+”号开头，由常数、函数、单元格引用 以及运算符组成的式子。其价值不仅是计算，重要的是 构建计算模型。
1.3 利用数据条件格式进行数据处理
Excel2013高级教程 数据统计与处理分析
Excel是Office软件中的核心成员，是最优秀的电 子表格软件之一，具有强大的数据处理和数据分 析能力，是个人及办公事务中进行表格处理和数 据分析的理想工具之一。
如何利用Excel的函数、图表、高级分析工具、 VBA程序等功能进行数据分析是本次学习的重点。
予设置。利用此功能，可将单元格中的满足条件的数据进行特殊标记，以便直观 地查看。 方法：
选中数据区—开始—样式—条件格式 任务3：对“销售记录汇总”表进行 如下操作：
（1）把“销售单价”以图标集形式显示 （2）把“销售额”以数据条形式显示
（3）把 “毛利”低于平均值的数据设置为黄色。
1.3 利用数据条件格式进行数据处理
·······（有书也称主菜单） 组：每个组都与特定任务相关；（有书也称工具栏） 快速访问工具栏：独立显示，默认有“保存”、“撤销”和“恢复“三按钮，可以自定义；
标题栏：显示工作薄名称。 状态栏：宏录制按钮、查看方式、缩放工具。
1.2 数据的输入、编辑与运算
目标：建立某企业产品销售数据统计报表（两个工作表） 1.2.1建立产品基本信息表
要找的值
查找区域
第2列
精确比较
相关知识：Vlookup函数的使用
格式： Vlookup（查找的值，查找区域，返回的列号，选项）
功能： 在表格或数组的首列查找值 ，并返回表格或数组中其它列的值 。
选项： FALSE：精确匹配，若找不到返回#N/A TRUE或省略：近似匹配，若找不到返回一个小于要找参数的最大值。 （Excel中的函数帮助可能有误，请在编辑栏输入公式时查看选项含义）
VLOOKUP的稍高级应用见综合实 例中相关练习
1.2 数据的输入、编辑与运算
本任务中相关公式说明：
销售额=数量 * 销售单价 总成本 =数量 * 成本单价 毛利= 销售额-总成本 销售数据可从“销售原始”工作表复制而得。
1.3 利用数据条件格式进行数据处理
概述： 条件格式功能，指的是当单元格中的数据满足某种条件时就设置某种格式，否则不