企业培训-Excel20XX培训高阶 精品

单元格的引用
相对引用：A1 在行中复制时，行号不动，列标在动，在 列中复制时，列标不动，行号在动. 绝对引用：$A$1 f4不能动的要加$ 混合引用：$A1 , A$1,适合向两个方向上复制.分别进行讨 论，只讨论默认在动的，不讨论默认不动的。 – 动的不加$，不动的要加$ – 向下复制只看行号 – 向右复制只看列标
多条件分类汇总与透视表布局的关系
报表筛选：作条件的字段（要筛选数据的字段） 行或列标签：分类的字段 – 合并同类项 – 分组：数值，日期 数值：汇总（计算）的字段
透视表进行数据组合
透视表中字段的组合：右击要给合的字段\Group
– 日期型，数值型，文本型 ：
• 自动组合
– 被组合的字段中每个值均为固定类型 – 被组合的字段中不能有空单元格 – 将要组合的字段放到行中。
查找匹配类函数
根据内容找内容
=Vlookup(lookup_value,table_array,col_index_num,range_lookup)
lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。 table_array：数据源，命名数据源。 col_index_num：返回被找信息所在的列数。 range_lookup:是否精确匹配：
条件格式
开始\条件格式 可以设置64个条件，有6种做法 – 突出显示单元格规则（三种数据类型都有效） – 项目选取规则（只对数字有效） – 数据条（颜色长短表示数据大小） – 图标集（用不同的符号表示数据大小） – 色阶（用颜色渐变过度表示数据大小） – 使用公式自定义条件格式
新增的的条件格式
突出显示单元格规则（三 种数据类型都有效） 项目选取规则（只对数字 有效）
透视表进行数据百分比分析
透视表中百分比的显示：光标定位在Data中要百分比显 的字段中，右击/Value Field Settings • % of total • 如果透视表中行中的一个字段占另一个行中字段百
分比时，这两个字段要分别放到行中及列中。
– % of row – % of column – Running total in
• true:-1相似匹配。
– 被找依据在被找表中要升序排放 – 返回小于或等于查找值的最接近值 – 常用于数值范围的查找
• false:0 精确匹配。
Vlookup找不到数据原因
被找的数据不在被找表格的首列中 有多余的空格 – Trim():去除多余空格 有非打印符号 – Clean():去除非打印符号 数据类型不匹配：数据\分列\下一步\下一步(data\text to column\next\next) – 文本text – 数值general – 日期date
= COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2…) 最多可以有127个
Countifs的用法
判断重复总次数 – Countifs($a$1:$a$800,A1)：只能判断15位以内数 – Countifs($a$1:$a$800,a1&”*”):判断任意多个数 判断第几次重复 – Countifs($a$1:a1,a1):只能判断15位以内数 – Countifs($a$1:A1,a1&”*”)判断任意多个数
条件判断函数
条件判断类
=if(logical_test,Value_if_true,Value_if_false) = round(number,num_digits)
– Rounddown() – Roundup()
多条件判断类函数
=SumifS (sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)最多可 以有127个
拿多个表格进行数据汇总 制作交互式图表 …
原始数据要求
行为记录，列为字段 每个字段必须有一个字段名 表格中不能有合并单元格 表格中不能有空列，但可以有空单元格 每个字段保持数据类型一致：数据\分列 – 文本：字符运算 – 数值：算术运算，逻辑运算 – 日期：算术运算，逻辑运算 将区域转化为表格再制作透视表：插入\表格
• 手动组合
– 行中只保留要组合的字段，其他字段先放到页中 – 选择要组合的内容，右击组合 – 组合完后将其他页字段中的字段放到该放的位置
对分类汇总后的数据进行字段计算数据项计算
向透视表中添加新的计算字段。 – 光标定位在透视表中，PivotTable
tools\Options\Formulas\Calculated field
对分类汇总后的数据进行字段计算数据项计算
透视表计算项： – 用过组合的透视表字段不能进行计算项 – 光标定位在透视表中要进行计算项的字段中。
PivotTable tools\Options\Formulas\Calculated field
行标签字段添加汇总
更改行中字段的汇总方式： – 双击行中除最后一个字段以外的字段名\Subtotals &
140
120
100
10
80
25
15 25
60
20
15
115
100
5
10
40
20
0
StaHale Waihona Puke Baidut
a
b
c
d
e
f
g
h
End
使用Excel 2010分析数据
透视表功能
多条件分类汇总 对分类汇总后的数据进行筛选 （xlsx) 对分类汇总后的数据进行字段 计算数据项计算 对分类汇总后的数据进行百分 比分析 – 同比 – 环比 – MTD –…
红色的数据代表不良业绩！ 不同的颜色区域 更能表现出用户 所需的关键数据
Excel 2010最新提供的图标格式
使用户更容易阅读和理解销售数据
Icon sets:更多的图标示显敏感数据
还有许多更容易 表现数据的图标
Excel 函数运用的基础:单元格引用
Excel 2010公式与函数简介
在 Excel 2010 中，一个公式最多可以包含 64 层嵌套，但是在 早期版本的 Excel 中，最大嵌套层数仅为 7 在 Excel 2010 中，公式最多可以包含 255 个参数，但是在早 期版本的 Excel 中，公式中最大参数数目限制仅为 30。 Excel 2010 提供的下列函数在早期版本的 Excel 中不可用： AVERAGEIF ,AVERAGEIFS,SUMIFS,COUNTIFS,IFERROR
透视表进行数据分析
透视表的更新： – 数值的修改：修改原始表格，回到透视表中右击Refresh – 数据的插入：在原表格数据的中间插入，回到透视表中右击
refresh data – 数据的append:
0
1月
2月
3月
Sum of Order Amount - USA 77
27
25
22
13
8
Count of OrderID - UK
35
47
40
13
12
19
4月
5月
6月
7月
8月
9月
Count of OrderID - USA
49 17
10月
37 19
11月
100
56
80
60
13
40
20
0 12月
甘特图的制作技巧
filters。 – 如果双击内容，展开及折叠。 – 如果要展开或折叠所有项：右击字段名
Expand/collapse,选择Expand 或Collapse
透视表对汇总后数据的筛选（＊.xlsx)
对行或列标签中字段的筛选 – 同平同表格 对数值的筛选 – 被筛选的字段：行 – 被筛选的依据：值 – 作条件的字段：报表筛选或行的被筛选的字段的前方 – 被查询的字段：行的被筛选的字段的后方
多维数据表格分析数据
明细数据的产生：双击data中的值： 多维（四维）数据透视表的制作： – 每张原始表格都要有交叉表的结构 – 要划分出表格的维数。 – 使用透视表向导\Multiple consolidation ranges
透视图布局规律
多行多列数据产生的图表 – X轴：行 – 图例：列 – Y轴：数值 – 图的下拉列表：报表筛选 单列数据产生的图表（饼形图） – 图例：行 – 扇区大小：数值 – 图的下拉列表：报表筛选
双轴线柱图的制作
1. 双轴线柱图适合展示的数据 2. 选择数据制作平面柱形图 3. 如何选择图表中的数据小的系列 4. 设置数据系列的坐标轴 5. 设置数据系列的图表类型 6. 设置并显示出线形系列的数据标签
千
Sum of Order Amount - UK
150
76
66
100
54
24
27
23
50
阶梯图的制作应用
1. 选择数据制作平面堆积图 2. 设置数据系列的重叠比例 3. 给图表系列显示数据 4. 格式化阶梯图
百万
6
5
4
1.261601
3
1.270047
2
1.010962
1 1.279692
0 2001年
0.308829 0.886629 1.205658
1.485247 2002年
第四季 第三季 第二季 第一季
创建数值查询类公式
=ISERROR(value):判断某单元格中的值是否有错误
=TRUE: 有错误 =false: 无错误
＝IFERROR(value,value_if_error) =IFerror(VLOOKUP(A2,data,1,0),”有错误”) =trim():去除多余的空格。 =clean():清除非打印符号 =VLOOKUP(trim(A2),data,1,0)
1. 甘特图适合展示的数据数据 2. 选择数据制作平面条形图 3. 选择适合的系列更改其坐标轴 4. 设置两个坐标轴显示的数据一致 5. 设置第二坐标轴上的数据系列的分类间距
0
20
40
60
80
100
C
Plan B
Act
A
0
10
20
30
40
50
60
70
80
90
100
瀑布图的制作技巧
1. 瀑布图适合展示的数据 2. 制作所需的表格布局 3. 制作三维堆积图 4. 修改数据及格式以显示桥形连接
根据内容找位置
=Match(lookup_value,table_array,type) – lookup_value:查找依据 – table_array:一般要绝对引用,只能为一行或一列数据 – Type:
• 1:返回小于或等于lookup_value的最接近的值，且Table_array中 一定要升序排放
• 0:精确匹配： Table_array可以没有顺序 • -1:返回大于或等于lookup_value的最接近的值，且Table_array
中一定要降序排放
其他查找函数
Index(Array,Row_num,Column_num):根据位置找内容 Iferror(value, value_if_error) Date(year,month,day) Year() Month()
如何让我的销 售数据更加直
观
Excel 2010最新提供的数据条格式
让我们换一种方式看销售报表
Color Bar：更直观找出 数据大小
想知道谁销售的 最好，很容易看
出来
Excel 2010最新提供的颜色条格式
使销售报表中的关键数据更加突出！
Color scale更直观的发现 数据大小的分布区域
表中哪些数据是亏损的？
Excel2010 平面图表操作
专业图表轻松做！
静态图表
图表名词 – 系列
• 系列名称：图例 • 系列大小：Y轴 – 类别 • 类别标签：X轴 方法：（由上到下 由左到右） – 选择相邻数据做图 – F11：在新的工作表中创建图表 – 选择不相邻数据做图
向图表中添加数据
只能添加系列不能添加类别 添加系列： – 选择系列数据,ctrl+c – 选择数图,ctrl+v 修改系列的位置 – 在图表中选择要修改的系列 – 修改公式中的最后一个参数