Excel数据分析辞典（双色）

目　　录
第1章　定义Excel数据分析环境1
分类检索． 1
用户环境．．．．． 1
建立数据表格． 1
安全保护数据． 1
名词术语． 2
Microsoft账户．．． 2
工作表标签． 2
功能区/选项卡/组．．．．． 2
合并单元格/合并后居中/跨越合并． 2
001 用户操作环境设置．． 3
难易度 ★　　适用版本：Excel 2003/2007/2010/2013
实例1-1：Microsoft用户账户设置．．．． 3
实例1-2：定制Office背景和Office主题 5
实例1-3：设置默认工作表数与标准字体．．．． 6
实例1-4：自由控制功能区显示 8
实例1-5：自定义选项卡和组．． 11
实例1-6：自定义界面显示元素．．．． 13
002 Excel数据表格的建立．15
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例1-7：创建员工通讯录工作表 15
实例1-8：创建周末加班申请表．．．． 17
003 数据的安全保护 ．19
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例1-9：设置密码保护员工工资工作簿．． 19
实例1-10：将部门工作分配工作簿标记为最终状态进行保护． 21
实例1-11：保护面试最终结果工作表限制人员编辑．．． 23
实例1-12：设置只允许填写考核成绩表中的成绩． 24
第2章　数据的输入．27
分类检索．．．．． 27
各类数据的输入．．．．． 27
目录．indd 5 2014-3-27 17：24：26
VI Excel数据分析辞典
规律与非规律数据的输入．． 27
有效数据的输入．．．．． 28
不同的输入方式．．．．． 28
名词术语．．．．． 28
数据类型．．． 28
文本型数字．．． 29
科学计数．．． 29
004 数字和文本的输入 29
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例2-1：输入以001开头的学员编号．． 30
实例2-2：轻松输入身份证号．． 30
实例2-3：登记职员住房面积时自动插入小数点 32
实例2-4：横向登记员工当月工资明细 33
005 日期和时间的输入 34
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例2-5：登记销售流水账 34
006 分数的输入
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例2-6：会议出席情况统计．． 35
007 金额的输入
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例2-7：快速填制费用报销单中的大写金额 36
008 特殊字符的输入 ．37
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例2-8：填写员工考勤记录．． 37
实例2-9：制作转账凭证 38
009 相同数据的输入 ．39
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例2-10：不同职位岗位工资的快速填制 40
实例2-11：快速制作多份记账凭证表单．．．． 40
010 规律数据的输入 ．41
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例2-12：复制填充连续的调薪时间．． 42
实例2-13：序列填充连续的产品编号．． 43
实例2-14：自动为日常工作日程表填充工作日． 44
实例2-15：快速输入几个一组的固定商品名称． 45
目录．indd 6 2014-3-27 17：24：26
目录 VII
01
实例2-16：员工姓与名的快速分列输入．．．． 46
011 随机数据的输入 ．47
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例2-17：使用RAND()函数输入随机座位编号 47
012 有效数据的输入 ．48
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例2-18：输入不超过10分的有效评分．．．． 48
实例2-19：输入不在当前日期范围的值班日期． 49
实例2-20：输入不重复的员工工号．． 50
实例2-21：有效输入不小于当前时间的签到．． 51
实例2-22：输入不超过总预算额的单项订购金额． 52
实例2-23：最高采购量输入提醒．． 53
013 列表选择输入 ．．．．．54
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例2-24：从右键下拉列表中选择年龄段 54
实例2-25：使用有效性列表选择输入所属部门． 55
实例2-26：快速选择员工出生省份和对应的城市． 56
014 选择性粘贴输入 ．58
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例2-27：选择性粘贴将各分公司营业收入金额由元转换为万元．．．． 59
015 记录单输入
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例2-28：使用记录单登记产品入库情况 60
第3章　格式化数据让其更易分析．．．．．62
分类检索．．．．． 62
数据的对齐与排列． 62
动动鼠标让数据显示更明确．．．． 62
让数据按个人意愿显示．． 62
按条件突出显示数据．．． 63
名词术语．．．．． 63
货币．．．．． 63
千位分隔符．．． 63
占位符． 63
条件格式．．． 64
数据条． 64
目录．indd 7 2014-3-27 17：24：26
VIII Excel数据分析辞典
色阶．．．．． 64
图标集． 64
公式．．．．． 64
016 数据的对齐．．．．64
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例3-1：对齐产品销售单价中小数点 65
017 数据的排列
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-2：让日记账中的月/日分列显示．．．．． 66
实例3-3：换行显示计量单位．． 68
实例3-4：实现销售统计表的行列转换 68
实例3-5：比较产销记录表中两个间隔较远的字段．．．．． 69
018 调整小数位数
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例3-6：快速增加员工离职率小数位 70
实例3-7：保证销售回款率计算结果的精确度 71
019 数据的货币显示
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例3-8：为金额添加人民币符号 72
实例3-9：为工资表中的工资数额应用会计专用格式． 74
020 显示千位分隔符．．75
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-10：给应收账款添加千位分隔符以便查看． 75
021 用文本占位符自动添加文本．．76
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-11：在产品编号中自动添加固定型号．． 76
022 数字占位符的格式占位．．．78
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-12：将客户编号用“0”补足为4位 78
实例3-13：用“#”统一金额格式 79
023 字符的重复显示．．80
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-14：用“*”重复填充隐私信息 80
024 按条件显示格式．．81
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-15：用盈利/亏损替代具体的金额数字． 82
目录．indd 8 2014-3-27 17：24：26
目录 IX
01
025 灵活显示日期时间．82
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例3-16：客户来电日期时间的灵活设置 83
026 将满足一定范围的数据以某种格式显示．．84
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例3-17：突出显示能承受的贷款每月固定偿还额．．． 85
实例3-18：标识本周新进人员 86
实例3-19：将兼职人员区别显示．． 87
实例3-20：突出显示重复的电话号码．． 88
实例3-21：将积分排名前10%的员工以特定格式显示．．．．． 89
实例3-22：找出工资表中高于平均收入的员工． 90
027 将数值大小以数据条长短反映．．91
难易度 ★★★　　适用版本： Excel 2007/2010/2013
实例3-23：使用实心数据条分析产品销售状况． 92
实例3-24：用负值条形图表现收入盈亏．．．． 92
实例3-25：隐藏工资条形图的工资数额．．．． 94
028 将数值高低以色阶过渡呈现．．95
难易度 ★★　　适用版本：Excel 2007/2010/2013
实例3-26：用双色刻度分析员工培训成绩 95
实例3-27：用三色刻度分析产品需求量．．．． 96
029 用图标区分不同范围的数据．．97
难易度 ★★★　　适用版本：Excel 2007/2010/2013
实例3-28：以箭头形状明示职工业绩等级 97
实例3-29：用象限图标识项目完成进度．．．． 98
030 自定义为满足公式条件的数据设置格式．．99
难易度 ★★★★　　适用版本：Excel 2007/2010/2013
实例3-30：合同到期的提前提醒．． 99
实例3-31：最低库存预警 101
第4章　数据的查找与更新．．103
分类检索．．． 103
“查找”对话框查找． 103
名称与记录单查找．．．．． 103
定位查找． 103
名词术语．．． 104
查找/替换/转到/定位．． 104
目录．indd 9 2014-3-27 17：24：27
X Excel数据分析辞典
常量．．． 104
关键字．．．．． 104
匹配查找． 105
空值．．． 105
在多个单元格中输入数据 105
031 关键字查找
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例4-1：查找并快速更新订购商品的单位．． 106
032 按格式查找
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例4-2：查找最新进货的生鲜类食品．．．． 108
033 模糊查找
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例4-3：查找护理头部的洗护商品．． 110
034 匹配查找
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例4-4：查找已到使用期限的固定资产 110
035 名称查找
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例4-5：快速查找并更新折扣率．． 111
036 记录单查找
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例4-6：使用记录单浏览修改固定资产表．． 112
实例4-7：查找数量在2台以上的记录 114
037 差异查找
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例4-8：使用差异查找辅助对账．． 115
038 查找数字
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例4-9：查找零售价格表中的单价并统一应用货币格式 116
039 查找公式结果
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例4-10：查看近期商品出库金额 118
040 查找错误值
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例4-11：查找排名表中出现的公式错误．．．． 119
目录．indd 10 2014-3-27 17：24：27
目录 XI
01
041 查找空值
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例4-12：在现有库存的空单元格中快速输入“缺货”字样．．．． 120
第5章　排序、筛选和分类汇总123
分类检索．．． 123
数据的排序． 123
数据的筛选． 123
数据的分类汇总．．． 124
名词术语．．． 124
关键字/字段 124
排序/筛选/分类汇总．．． 125
分类汇总/分级显示．．．． 125
合并计算． 125
042 单一条件排序．．．．125
难易度 ★　　适用版本：Excel 2003/2007/2010/2013
实例5-1：将员工健康检查评分由高到低排列． 126
043 多条件排序
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-2：按入职时间与当前岗位薪资排序员工信息表 126
044 按汉语拼音排序128
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-3：按考场人员姓名的汉语拼音排序编制考场名单 128
045 按笔划排序
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-4：按考场人员姓名的笔划编制考场名单． 130
046 自定义排序．．132
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-5：使用自定义排序制作部门工作计划． 132
047 不规则编号排序133
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-6：按办公用品编号排序整理办公用品表． 134
048 按颜色排序．．136
难易度 ★★★　　适用版本：Excel 2007/2010/2013
实例5-7：通过颜色排序区分待办事项是否紧急． 136
目录．indd 11 2014-3-27 17：24：27
XII Excel数据分析辞典
049 按图标排序．．138
难易度 ★★★　　适用版本：Excel 2007/2010/2013
实例5-8：按图标排序查询日常费用开支程度． 138
050 按行排序140
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-9：按行排序员工奖金评估表．． 140
051 对合并单元格数据排序．142
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-10：将小组当月销售额由高到低排序 142
052 自动筛选144
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-11：自动筛选统计公司男员工人数．．．． 144
053 包含合并单元格的自动筛选145
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-12：筛选查看某日所有工作内容．． 145
054 搜索筛选149
难易度 ★★★　　适用版本：Excel 2007/2010/2013
实例5-13：筛选当月全勤人员．．．． 149
实例5-14：筛选某月转正的所有人员记录．．．． 149
055 两个条件内的自定义筛选．．．150
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-15：查看年龄在20到25之间的员工信息 151
056 “且”规则的高级筛选．152
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-16：按一段时间的消费次数、消费金额划分客户等级．．．． 152
057“或”规则的高级筛选．．．．154
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-17：对迟到5次以上、缺席2天或业务额低于1万的员工
予以劝退．． 154
058 混合规则的高级筛选．．．．．155
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-18：查询具有法律专业本科学历或有事业单位工作经验的
人员．．．． 156
059 计算条件的高级筛选．．．．．157
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-19：筛选工龄超过单位平均工龄的人员记录． 157
目录．indd 12 2014-3-27 17：24：27
目录 XIII
01
060 不重复记录的高级筛选．159
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-20：筛选本月有订单的货品名 159
061 简单分类汇总
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-21：汇总统计不同职位的平均工资．．．． 161
062 选择多个汇总项的分类汇总162
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-22：统计不同部门的基本工资和工资总额．．．．． 162
063 多重分类汇总
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-23：统计不同部门的工资总额和平均值 164
064 嵌套分类汇总．．．．166
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-24：汇总统计各部门中不同职位的工资状况． 166
065 手动建立分级显示169
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-25：以上、中、下旬时间查询当月销售记录． 169
066 自动生成分级显示171
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例5-26：自动建立分级显示划分客户所在区域．．．．． 171
067 按位置合并计算172
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-27：按位置合并计算一季度各商品订单额．．．．． 173
068 按分类合并计算175
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例5-28：按分类合并计算一季度各商品订单额．．．．． 175
第6章　交互式分析的数据透视表．．．179
分类检索．．． 179
数据透视表的创建与刷新 179
数据透视表的整理与查看 179
数据透视表的排序与筛选 179
数据透视表的组合与计算 180
数据透视表的多表分析 180
目录．indd 13 2014-3-27 17：24：27
XIV Excel数据分析辞典
名词术语．．． 180
数据透视表． 180
源数据．．．．． 180
字段．．． 181
数据项．．．．． 181
汇总函数． 181
行字段/列字段/值字段．． 181
筛选器．．．．． 181
切片器．．．．． 181
日程表．．．．． 181
069 数据透视表的建立182
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例6-1：建立员工档案数据透视表．． 182
070 快速统计重复项183
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例6-2：快速统计公司人员情况．． 184
071 调整字段排列多维度分析．．．185
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例6-3：多角度分析公司人员构成．． 185
072 自动刷新数据透视表．．．．．187
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例6-4：自动刷新员工档案数据透视表 187
073 以不同汇总方式统计．．．．．188
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例6-5：以不同方式统计物品库存情况 189
074 以不同显示方式分析．．．．．190
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例6-6：统计不同物品库存比例．． 190
075 查看数据透视表明细数据．．．192
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例6-7：查看某商品不同规格型号的库存明细． 192
076 更改透视表数据源194
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例6-8：对滞销品清理库存后重新更新透视表． 194
077 在数据透视表中排序．．．．．195
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
目录．indd 14 2014-3-27 17：24：27
目录 XV
01
实例6-9：对现有库存距安全库存的情况进行排序．．． 196
078 报表筛选实现分页浏览．197
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例6-10：按缺货物品的紧急状态进行筛选 197
079 使用筛选按钮筛选199
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例6-11：对订购额在一定金额以上的正价商品免运费．． 199
080 使用切片器直观筛选数据．．．200
难易度 ★★★　　适用版本：Excel 2010/2013
实例6-12：按商品名、订购日期查询负责商品的销售员．． 200
081 使用日程表实现时段筛选．．．203
难易度 ★★★　　适用版本：Excel 2013
实例6-13：使用日程表查看商品订购情况．．．． 203
082 组合透视表中的日期项．204
难易度 ★★★★　　适用版本：Excel 2007/2010/2013
实例6-14：按月份查看商品的订购情况．． 204
083 在透视表中添加计算字段．．．206
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例6-15：按月计算各商品的订购额 206
084 数据透视表的多表分析
难易度 ★★★　　适用版本：Excel 2013
实例6-16：建立双透视表分析公司产品的订购情况． 208
第7章　统计分析、假设检验与数据预测．．210
分类检索．．． 210
统计分析． 210
假设检验． 210
数据预测． 211
名词术语．．． 211
方案．．． 211
规划求解． 211
描述统计． 212
直方图．．．．． 212
排位．．． 212
相关性．．．．． 212
目录．indd 15 2014-3-27 17：24：27
XVI Excel数据分析辞典
抽样．．． 212
回归分析． 212
单变量求解． 213
模拟运算表． 213
方差．．． 213
t/Ｚ/Ｆ检验．． 213
移动平均法． 213
指数平滑法． 213
085 最优方案分析
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-1：公司销售的最优方案分析．． 214
086 规划求解最优解216
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-2：求解利润最大化生产方案．． 216
实例7-3：合理安排员工值班 218
087 描述统计数据．．．．220
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-4：描述统计分析职员工资分布情况．． 220
088 直方图分段统计发生频率．．．221
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-5：直方图分析公司人员流动情况 222
089 排位
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-6：根据全年业绩排位发放年终奖 223
090 抽样分析
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-7：抽样分析公司工资水平．． 225
实例7-8：抽样调查产品的市场满意度．．．． 227
实例7-9：未收款项的抽样调查分析．． 229
091 相关性与回归分析231
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-10：卖场面积与营业额的相关性分析 231
实例7-11：公司产品生产与销售成本的回归分析．．．．． 232
092 单变量求解
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-12：使用单变量求解实现目标存款额的最低月储蓄额．．．． 234
目录．indd 16 2014-3-27 17：24：27
目录 XVII
01
093 单变量模拟运算表235
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-13：建立销售员提成的单变量模拟运算表．．．．． 236
094 双变量模拟运算表237
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-14：建立公司固定资产折旧的双变量模拟运算表．． 237
095 单因素方差分析239
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-15：不同行业收入状况的单因素方差分析．．．．． 239
096 双因素方差分析241
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-16：工人和机器设备对产量影响的双因素方差分析．． 241
实例7-17：产品品牌和销售地区对销量影响的双因素方差分析 242
097 t检验．．243
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-18：产品标准性的t检验．．． 244
实例7-19： t检验分析两种血压测定仪检测值是否有明显差别．． 245
098 Z检验．246
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-20：Z检验法分析产品生产情况是否符合设计标准 246
099 F检验．248
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-21：部门工作效率的F检验分析．．．． 248
100 时间序列的趋势外推预测．．．249
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-22：使用趋势线预测明年销售额．． 250
101 时间序列的移动平均预测．．．252
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-23：利用移动平均法预测来年工资增长率．．．．． 252
实例7-24：利用二次移动平均法预测的未来两年办公费用．． 253
102 时间序列的指数平滑预测．．．255
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例7-25：利用指数平滑法预测公司明年利润额．．．．． 256
实例7-26：公司产品销量的二次指数平滑预测 258
目录．indd 17 2014-3-27 17：24：27
XVIII Excel数据分析辞典
第8章　数据计算．．．260
分类检索．．． 260
公式的输入、编辑与复制 260
单元格引用． 260
名称的应用． 260
数组常量和数组公式． 261
错误分析与检查．．． 261
名词术语．．． 261
公式．．． 261
运算符及运算符优先级 261
A1与R1C1引用样式．．． 262
相对引用、绝对引用、混合引用以及三维引用．． 262
名称．．． 263
数组、数组常量以及数组公式．．．． 263
迭代/循环引用 263
103 输入与编辑公式264
难易度 ★　　适用版本：Excel 2003/2007/2010/2013
实例8-1：输入编辑公式计算产品销售额 264
104 复制公式266
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例8-2：使用“填充柄”复制公式计算销售额． 267
实例8-3：使用“粘贴”功能复制公式计算销售额．．． 268
105 自动求和、求平均值计算．．．269
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例8-4：使用自动求和计算材料采购金额．． 269
实例8-5：使用“自动求和”统计成绩．．．． 270
106 相对引用计算．．．．272
难易度 ★　　适用版本：Excel 2003/2007/2010/2013
实例8-6：使用相对引用根据单价和销量计算销售额．．．．．272
107 绝对引用计算．．．．274
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例8-7：使用绝对引用计算不同商品相同折扣下的销售额 274
108 混合引用计算．．．．275
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例8-8：使用混合引用计算同一商品不同折扣不同销售下的销
售额．．．． 276
目录．indd 18 2014-3-27 17：24：27
目录 XIX
01
109 三维引用计算．．．．277
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例8-9：在工资表中使用三维引用计算工龄工资．．． 277
110 使用名称计算．．．．279
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例8-10：定义名称计算固定资产计提折旧额 279
实例8-11：根据所选内容创建名称计算工资总额．．．．． 282
111 运用数组常量计算284
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例8-12：使用数组常量计算员工缴纳个人所得税． 284
112 运用数组公式计算287
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例8-13：运用数组公式计算销售商品总额 287
实例8-14：运用数组公式统计满足条件的员工姓名． 288
实例8-15：运用数组公式制作日期 289
113 启用迭代计算．．．．290
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例8-16：使用迭代计算生成不变的随机数 291
实例8-17：使用迭代计算获取输入时间．． 294
114 公式审核296
难易度 ★　　适用版本： Excel 2003/2007/2010/2013
实例8-18：审核固定资产计提折旧额公式的正确性． 296
实例8-19：修改显示错误值的公式 297
第9章　数据处理函数．．300
分类检索．．． 300
数据的逻辑判断与处理函数．． 300
日期数据的处理函数． 300
文本数据的处理函数． 300
财务数据处理函数．．．．． 301
数据查找函数． 301
数据集中趋势统计函数 301
数据离散趋势分析统计函数．． 302
数据概率统计函数．．．．． 302
数据汇总计算函数．．．．． 302
目录．indd 19 2014-3-27 17：24：27
XX Excel数据分析辞典
名词术语．．． 302
集中趋势． 302
离散趋势． 303
概率．．． 303
115 数据的逻辑判断与处理．303
难易度 ★★★　　适用版本：2003/2007/2010/2013
实例9-1：按年均消费额划分客户等级．．．． 304
实例9-2：判断累积积分是否可以领取季度奖． 304
实例9-3：判断培训成绩是否合格．． 305
116 处理日期数据．．．．306
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例9-4：计算工作年限．． 306
实例9-5：计算报表当前填制时间．． 307
实例9-6：计算转正日期．． 308
实例9-7：计算项目执行天数 309
117 处理文本数据．．．．310
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-8：从身份证号中获取出生信息．．．． 310
实例9-9：将金额按设置格式显示．． 311
实例9-10：从产品编号中获取产品类别．． 312
118 处理财务数据．．．．313
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-11：计算固定资产线性折旧费 313
实例9-12：使用固定余额递减法计算折旧值 315
实例9-13：计算公司贷款每期偿还额 316
实例9-14：计算投资的未来值．．．． 317
实例9-15：计算某项目投资的现值和净现值 317
实例9-16：计算一系列现金流的内部收益率 318
119 查找数据319
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-17：查找某积分对应的奖金金额．． 320
实例9-18：根据员工编号显示员工个人信息 320
实例9-19：计算哪一个月完成了目标 322
实例9-20：查找游戏点数可兑换的礼物．． 323
实例9-21：从电话簿中提取姓名 324
实例9-22：将满足一定条件的员工调配到集团工作． 325
目录．indd 20 2014-3-27 17：24：27
目录 XXI
01
120 集中趋势统计．．．．325
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-23：算术与几何平均值计算 326
实例9-24：计算众数． 327
实例9-25：计算中位数 328
121 离散趋势统计．．．．328
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-26：计算方差． 329
实例9-27：计算标准差 329
实例9-28：计算四分位数 330
实例9-29：计算偏态值 332
实例9-30：计算峰度值 333
122 概率统计334
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-31：使用二项分布检查产品合格率．．．． 334
实例9-32：使用泊松分布制定公司生产目标 335
123 数据汇总计算．．．．337
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例9-33：统计客户当月投诉次数 337
实例9-34：统计某部门业务量总额 338
实例9-35：统计女职员评为中级职称的人数 338
实例9-36：返回不同年龄段的员工人数．． 339
第10章　单元格图表和数据图．．341
分类检索．．． 341
迷你图．．．．． 341
常用标准图表的使用． 341
高级图表． 342
组合图．．．．． 342
动态图表． 342
名词术语．．． 343
迷你图/迷你图组．． 343
标准图表． 343
温度计图． 344
甘特图．．．．． 344
瀑布图．．．．． 344
目录．indd 21 2014-3-27 17：24：28
XXII Excel数据分析辞典
漏斗图．．．．． 344
阶梯图．．．．． 344
柏拉图．．．．． 344
124 使用柱形迷你图比较数值大小344
难易度 ★★　　适用版本：Excel 2010/2013
实例10-1：使用柱形迷你图分析每月收入状况 345
实例10-2：创建间隔时间不等的柱形迷你图分析网站访问情况 346
125 使用折线迷你图分析数据变动趋势．．348
难易度 ★★　　适用版本：Excel 2010/2013
实例10-3：创建折线迷你图分析一周股价变动趋势． 348
126 使用盈亏迷你图反映金额正负变化．．350
难易度 ★★　　适用版本：Excel 2010/2013
实例10-4：创建盈亏迷你图分析信用卡是否欠款．．．．． 350
127 使用REPT函数自定义创建单元格迷你图．352
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例10-5：用REPT函数创建商品信用等级图． 352
实例10-6：用REPT函数制作销量条形图 353
128 使用柱形图纵向比较数据．．．355
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例10-7：创建簇状柱形图比较产品销量．．．． 355
实例10-8：创建堆积柱形图比较各分公司营业额．．．．． 356
实例10-9：创建百分比堆积柱形图查看近年新进职员学历倾向 357
129 使用条形图横向比较分类项358
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例10-10：创建三维簇状条形图比较各项社团活动参与人数．． 358
实例10-11：创建水平圆柱图比较项目计划与实际完成情况 359
130 使用折线图表现数据发展趋势362
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例10-12：创建平滑折线图分析年度营业利润增长变动趋势．． 362
实例10-13：创建带数据标记的堆积折线图分析不同等级客户近
年消费趋势．． 364
实例10-14：创建三维折线图分析两大产品线营业额成长趋势．． 365
131 使用饼图或圆环图分析数据比例关系．．．．367
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-15：创建饼图分析员工出勤状况 367
实例10-16：创建分离型饼图进行员工离职原因分析．．．．． 369
目录．indd 22 2014-3-27 17：24：28
目录 XXIII
01
实例10-17：创建复合饼图分析公司年度福利经费开支． 371
实例10-18：创建双层复合饼图分析不同年龄段客户的消费能力．．．． 372
实例10-19：创建圆环图分析各子公司的销售额． 375
实例10-20：创建圆环图分析各岗位工资构成． 376
132 使用面积图进行数据量与趋势变化分析377
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-21：创建三维面积图分析公司账款回收状况．．．．． 378
实例10-22：创建堆积面积图分析公司近年来客户的构成 379
133 使用散点图和气泡图进行相关性分析．．．．380
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-23：创建散点图分析商品售价与销量的相关性． 380
实例10-24：创建带平滑线的散点图进行竞争产品销量比较 382
实例10-25：创建气泡图分析各品牌销售状况及市场地位 383
实例10-26：创建气泡图分析因广告投入不同所呈现的销售额的
变化．．．． 384
134 使用雷达图进行倾向/平衡分析测试386
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-27：创建雷达图分析员工体能状况．． 386
实例10-28：创建雷达图对比A、B产品营养成分 387
135 使用温度计图表分析项目完成比例．．388
难易度 ★★★★　　适用版本： Excel 2003/2007/2010/2013
实例10-29：创建温度计图表分析任务完成进度． 389
136 使用甘特图表现项目执行进度392
难易度 ★★★　　适用版本： Excel 2003/2007/2010/2013
实例10-30：使用悬浮条形图查看项目阶段．． 392
137 使用瀑布图反应数据增减变动395
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-31：通过堆积柱形图创建瀑布图表现库存商品变动情况．．．． 395
实例10-32：通过涨跌柱线创建瀑布图分析现金流．．． 396
实例10-33：创建带负值的瀑布图反映员工实发工资情况 398
138 使用漏斗图表现数据阶梯型递减402
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-34：创建漏斗图表现客户等级．．．． 402
139 使用阶梯图分析数据阶梯变动趋势．．405
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-35：创建阶梯图表现本年费用递增状况． 405
目录．indd 23 2014-3-27 17：24：28
XXIV Excel数据分析辞典
140 使用柏拉图找到对数据起关键作用的因素409
难易度 ★★★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-36：创建柏拉图分析产品不良率 409
141 使用不等宽柱形图表现值的范围和分布414
难易度 ★★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-37：创建不等宽柱形图反映销量与市场占有率． 414
142 组合图的使用．．．．416
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-38：创建线-柱图比较两事业部销售额差异． 417
实例10-39：创建双轴线-柱图分析商品销售与折扣率的关系．．．． 418
实例10-40：创建双轴线-柱图进行男女比例与消费状况相关分析．．．．． 419
实例10-41：创建双轴线-面积图进行男女比例与薪资状况的相
关分析 421
143 解决值差较大的断层图的制作423
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-42：用模拟Y轴法制作断层图．．．．． 423
144 迷你图与数据图表的组合使用430
难易度 ★★★　　适用版本：Excel 2010/2013
实例10-43：在条形图中建立迷你图分析成本变化．．． 430
145 创建动态图交互式分析数据432
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例10-44：使用辅助区域建立动态图分析不同月份各产品订单额．433
实例10-45：定义名称建立随记录项增加的销量流水动态图 435
实例10-46：创建下拉菜单式图表分析近年各部门人数变动 437
第11章　源数据链接、嵌入与输出．440
分类检索．．． 440
建立链接． 440
嵌入或被嵌入． 440
获取数据． 440
共享与输出． 441
名词术语．．． 441
屏幕提示． 441
嵌入与链接． 441
目录．indd 24 2014-3-27 17：24：28
目录 XXV
01
146 使用超链接链接外部数据源442
难易度 ★　　适用版本：Excel 2003/2007/2010/2013
实例11-1：制作公司员工通讯簿 442
147 通过复制粘贴建立链接．444
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-2：制作一式三联对账单 444
148 通过公式建立链接447
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-3：销售汇总的跨工作簿计算 447
实例11-4：引用计算保险扣款．．．． 449
149 在工作表中嵌入其他文件对象450
难易度 ★　　适用版本：Excel 2003/2007/2010/2013
实例11-5：在日常费用统计表中嵌入费用类别说明文件．． 450
150 将工作表或图表嵌入到其他程序452
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-6：将Excel销售统计图嵌入到PPT中 452
151 获取数据库数据454
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-7：将Access中员工记录导入到员工档案表中．．． 454
152 获取外部文本文件数据．456
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-8：将文本文件中本周工作计划导入到Excel日程安排表中．．．．． 456
153 获取网站数据．．．．458
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例11-9：从网站获取Excel统计数据．．．．． 459
154 传递工作表记录到Access数据库中．．．．460
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-10：将员工通讯簿导入到Access数据库中．．．． 460
155 将数据表存为文本文件．463
难易度 ★★　　适用版本：Excel 2003/2007/2010/2013
实例11-11：将人事通报表存为文本文件 463
156 将Excel数据表保存到云
难易度 ★　　适用版本：Excel 2013
实例11-12：将职务调动申请表保存到云 465
目录．indd 25 2014-3-27 17：24：28
XXVI Excel数据分析辞典
157 邀请他人协同编辑数据．468
难易度 ★★　　适用版本：Excel 2013
实例11-13：邀请他人协同进行采购物品登记． 468
158 数据表的打印输出471
难易度 ★★★　　适用版本：Excel 2003/2007/2010/2013
实例11-14：会议议程安排表的打印输出 471