第1部分 基础知识 11
第1章学公式、函数与图表前你应该知道的 11
1.1学习公式必须掌握的Excel知识 11
1.1.1工作簿的基本操作 11
1.1.2工作表的简单操作 15
1.1.3单元格和区域的调整 17
1.1.4数据格式调整手段 20
1.2公式、函数、图表数据来源 25
1.2.1输入基础数据 25
1.2.2输入序列数据 27
1.2.3限制数据类型及范围 29
1.3源数据的选择、整理与汇总 30
1.3.1源数据的选择——筛选 30
1.3.2源数据的有序化排列——排序 34
1.3.3源数据的汇总——分类汇总 37
1.3.4源数据的整理——数据透视表 38
第2章 使用公式、函数前必知的基础知识及常见错误类型 43
2.1了解公式的类型和可用的运算符 43
2.1.1公式的组成 43
2.1.2可用运算符及优先级 43
2.2创建与编辑公式 45
2.2.1创建公式 46
2.2.2复制公式 47
2.3函数的结构和种类 48
2.3.1函数的结构 48
2.3.2函数的种类 48
2.4输入函数的方法 49
2.4.1直接输入函数 49
2.4.2通过“插入函数”对话框输入 50
2.5公式错误时可能遇到的问题及解决方案 51
2.5.1 Excel中的8种错误值 51
2.5.2检测错误的解决方案 51
第3章 公式使用中必知的数据引用方式及技巧 55
3.1公式中的单元格引用 55
3.1.1相对引用 55
3.1.2绝对引用 56
3.1.3混合引用 56
3.2在公式中使用名称 57
3.2.1定义名称的几种方法 57
3.2.2调整名称对应的区域范围 60
3.2.3将名称粘贴到公式中 61
3.2.4对现有区域引用应用名称 62
3.2.5定义公式名称 62
3.3关于数组公式 63
3.3.1输入数组公式 63
3.3.2编辑数组公式 64
3.4公式使用中的一些技巧 65
3.4.1将公式转换为数值 65
3.4.2在工作表中显示公式而非计算结果 66
3.4.3在编辑栏中隐藏公式 66
3.4.4复制公式但不使用相对引用 67
3.4.5在表中使用公式的方法 68
第4章你不可不知的基础函数 69
4.1数学函数 69
4.1.1 SUM、 SUMIF函数 69
4.1.2 PRODUCT、 SUMPRODUCT函数 70
4.1.3 ROUND、TRUNC函数 71
4.2逻辑函数 72
4.2.1IF、IFERROR函数 72
4.2.2TRUE、 FALSE函数 74
4.2.3 AND、 OR、 NOT函数 74
4.3日期和时间函数 75
4.3.1 NOW、TODAY函数 76
4.3.2YEAR、 MONTH、DAY函数 76
4.4文本函数 77
4.4.1 FIND、 FINDB函数 77
4.4.2 LOWER、 UPPER函数 78
第5章专用函数解析 80
5.1统计函数 80
5.1.1计数函数 80
5.1.2极值函数 81
5.2财务函数 82
5.2.1投资计算函数 82
5.2.2折旧计算函数 83
5.2.3偿还率计算函数 84
5.3工程函数 84
5.3.1进制转换函数 84
5.3.2数据比较函数 85
5.4查找与引用函数 86
5.4.1指定查找函数 86
5.4.2目录查找函数 87
5.4.3数据引用函数 88
第6章解读Excel图表的类型与元素 90
6.1 Excel图表类型 90
6.1.1标准图表 90
6.1.2单元格图表——迷你图 94
6.2 Excel图表组成元素 95
第7章一份简单图表的制作 96
7.1准备绘图数据 96
7.1.1准备之一：导入数据 96
7.1.2准备之二：确认数据排列方式 98
7.2创建图表 100
7.2.1创建标准图表 100
7.2.2创建单元格图表——迷你图 101
7.3标准图表的基础编辑 104
7.3.1添加图表元素 104
7.3.2更改图表源数据 105
7.3.3更改图表类型 106
7.3.4应用图表布局和样式 107
7.3.5设置图表元素格式 108
7.4迷你图的编辑 111
7.4.1更改迷你图的类型 112
7.4.2更改标记颜色 112
第8章设计一份专业的商务图表 113
8.1专业图表设计要点 113
8.1.1清晰易读——图表布局 113
8.1.2视觉舒适——图表配色 115
8.1.3结构完整——图表细节 116
8.2实现专业图表布局的方法 116
8.2.1专业的图表标题 116
8.2.2商务图表中图例巧安排 119
8.3好的配色是成功的一半 123
8.3.1图表配色原理与技巧 123
8.3.2图表的经典配色 125
8.4细节是好图表成功的关键 127
8.4.1不容忽视的图表细节 127
8.4.2图表细节的修饰 127
第2部分 行业应用 131
第9章函数在员工信息管理中的应用 131
9.1提取员工基本信息 131
9.1.1使用LEFT函数提取员工出生地区 131
9.1.2使用MID函数提取员工出生日期 133
9.1.3使用RIGHT函数提取员工性别 135
9.2计算员工工龄、合同到期日 135
9.2.1使用DAYS360和TODAY函数计算员工工龄天数 136
9.2.2使用DATE函数计算合同到期日期 136
9.2.3使用MONTH、 DAY函数自动提醒员工生日 137
9.3查询员工档案信息 138
9.3.1使用VLOOKUP函数引用员工姓名等信息 138
9.3.2使用INDEX、 MATCH函数查找员工所在部门 140
9.3.3使用OFFSET函数偏移引用员工工资账户 142
第10章函数在员工薪酬核算中的应用 144
10.1统计员工出勤情况 144
10.1.1使用条件格式突出显示迟到、早退、缺勤记录 144
10.1.2使用NETWORKDAYS函数统计应出勤天数 146
10.1.3使用COUNTA函数统计实际出勤天数 148
10.1.4使用COUNTIF函数统计迟到和早退次数 148
10.1.5使用COUNTBLANK函数统计缺勤天数 149
10.2计算员工业绩提成及加班费 150
10.2.1使用IF和LOOKUP函数计算员工业绩提成 150
10.2.2使用HOUR和MINUTE函数计算加班时间 151
10.2.3使用WEEKDAY、 SUMPRODUCT等函数计算加班工资 152
10.3员工工资的核算与汇总 153
10.3.1使用MAXA函数计算个人所得税和实发工资 154
10.3.2使用DSUM函数汇总各部门工资 155
10.3.3使用MOD、 ROW、 COLUMN函数生成工资条 157
第11章函数在企业财务管理中的应用 158
11.1预算企业投资收益情况 158
11.1.1使用FV、 FVSCHEDULE函数计算投资的未来值 158
11.1.2使用PV、 NPV、 XNPV函数计算现值和净现值 159
11.1.3使用IRR函数计算现金流的内部收益率 160
11.2企业贷款与偿还相关计算 162
11.2.1使用PMT函数计算每期偿还额 162
11.2.2使用PPMT和IPMT函数计算还款本金和利息 164
11.2.3使用CUMPRINC、 CUMIPMT函数计算阶段本金和利息 166
11.3企业固定资产折旧计算 167
11.3.1使用SLN函数计算固定资产折旧额 167
11.3.2使用DB函数计算固定资产折旧额 169
11.3.3使用DDB函数计算固定资产折旧额 169
11.3.4使用SYD函数计算固定资产折旧额 170
11.3.5使用VDB函数计算固定资产折旧额 171
第12章函数在生产管理中的应用 172
12.1生产库存管理中的相关计算 172
12.1.1使用AVERAGE等函数计算安全库存量 172
12.1.2使用条件格式设置安全库存提醒 174
12.1.3使用TODAY、 VLOOKUP函数进行库龄分析 176
12.2生产排程中的相关计算问题 178
12.2.1使用PERCENTRANK.INC函数对库存商品进行ABC分类 178
12.2.2使用LARGE和SMALL函数计算物料用量 180
12.2.3使用数组公式完成单阶BOM物料需求计算 181
12.3生产订单交期管理 182
12.3.1使用IF、 NOW函数计算完工剩余天数 183
12.3.2使用条件格式分析生产订单状况 183
第13章 函数在市场销售中的应用 186
13.1销售额的统计与分析 186
13.1.1使用SUMIF函数按地区计算销量和销售额 186
13.1.2使用ROUND等函数四舍五入销售额 187
13.1.3使用RANK函数计算销售额排名 188
13.1.4使用SUBTOTAL函数按多种方式分类汇总销售额 188
13.2销售业绩提成核算 190
13.2.1使用CHOOSE函数划分销售额等级 191
13.2.2使用VLOOKUP函数计算提成金额 191
13.2.3使用SUMIFS函数进行多条件求和 192
13.3预测下半年产品销量 193
13.3.1使用TREND函数预测下半年销量 193
13.3.2使用LINEST函数进行线性预测 194
13.3.3使用SLOPE和INTERCEPT函数进行线性预测 195
13.3.4使用FORECAST函数进行线性预测 196
13.3.5使用GROWTH函数进行指数预测 197
13.3.6使用LOGEST和POWER函数进行指数预测 197
第14章图表在员工信息管理中的应用 199
14.1使用饼图解读员工的年龄结构 199
14.1.1创建饼图 199
14.1.2应用图表布局 200
14.1.3应用图表样式 201
14.2使用柱形图比较学历结构 201
14.2.1复制图表 201
14.2.2更改图表源数据 203
14.2.3更改图表类型 204
14.3使用面积图表现员工男女构成比 204
14.3.1创建百分比堆积面积图 205
14.3.2设置图表标题格式 205
14.3.3设置数据系列格式 206
第15章图表在财务管理中的应用 209
15.1使用复合饼图分析资金结构 209
15.1.1创建复合饼图 209
15.1.2控制第二绘图区数据点个数 210
15.1.3更改图表布局 211
15.1.4调整图例位置 211
15.2使用折线图分析日常费用情况 212
15.2.1创建日常费用折线图 212
15.2.2设置数据点标记类型及大小 213
15.2.3隐藏网格线 214
15.2.4使用趋势线辅助分析数据 214
15.3建立阶梯图表现账户资金变化情况 216
15.3.1创建散点图 216
15.3.2添加X、Y误差线 217
15.3.3隐藏散点图标记 219
第16章图表在市场调查中的应用 221
16.1不同年龄段消费者使用手机品牌分析 221
16.1.1创建百分比堆积柱形图分析不同年龄段手机用户 221
16.1.2设置图表数据系列格式 222
16.1.3设置绘图区和图表区格式 223
16.1.4使用单元格内容作为图表标题 224
16.2使用条形图比较电子商务市场份额 225
16.2.1创建默认风格的条形图 225
16.2.2妙用数据标签将分类标签显示在条形上方 226
16.2.3设置坐标轴格式 228
16.2.4完善和美化条形图 229
16.3使用分离型饼图分析休闲方式调查数据 230
16.3.1创建饼图 230
16.3.2设置格式并美化饼图 231
16.3.3旋转和分离扇区 232
第17章图表在生产管理中的应用 233
17.1使用半饼图展示生产进度 233
17.1.1创建辅助数据绘制饼图 233
17.1.2通过设置数据系列格式完成半圆形进程图 234
17.2创建柏拉图分析产品不良率 235
17.2.1创建不良产品统计表 236
17.2.2创建不良项目不良率柱形图 237
17.2.3在柱形图中添加折线数据系列 238
17.2.4完善和美化条形图 241
17.3使用瀑布图解析生产成本构成 242
17.3.1创建瀑布图 243
17.3.2美化瀑布图 243
第18章图表在营销管理中的应用 245
18.1使用迷你图分析销量走势 245
18.1.1创建单个迷你图 245
18.1.2编辑迷你图 246
18.1.3在迷你图中显示标记 247
18.1.4美化迷你图 248
18.2创建不等宽柱形图分析各超市费用结构 249
18.2.1使用堆积面积图模拟不等宽柱形图 249
18.2.2使用辅助系列为图表添加数据标签 251
18.2.3完善和美化图表 256
第3部分 综合应用 259
第19章动态图表的制作 259
19.1使用函数和名称创建动态曲线图 259
19.1.1定义动态的名称区域 259
19.1.2使用动态名称创建动态图表 260
19.2创建下拉菜单式动态图表 262
19.2.1使用INDEX函数生成的数据区域创建饼图 263
19.2.2使用图片填充饼图数据点 264
19.2.3添加控件实现菜单式动态图表效果 265
19.3创建带滚动条的动态图表 267
19.3.1使用公式计算数据并创建名称 267
19.3.2使用名称创建带最值的折线图 268
19.3.3在图表中添加滚动条实现动态效果 272