《Excel公式、函数与图表应用大全》文库

封面图

目录

• 前言
• 第一篇基础篇
• 第1章Excel 基础知识的准备002
• 1.1启动与退出Excel003
• 1.1.1启动Excel003
• 1.1.2退出Excel003
• 1.2工作簿的基本操作004
• 1.2.1创建工作簿004
• 1.2.2保存工作簿005
• 1.2.3打开工作簿006
• 1.2.4关闭工作簿007
• 1.2.5加密工作簿008
• 1.3工作表的简单操作008
• 1.3.1认识工作表标签008
• 1.3.2选择工作表009
• 1.3.3插入工作表009
• 1.3.4重命名工作表010
• 1.3.5删除工作表011
• 1.3.6移动或复制工作表011
• 1.3.7隐藏与显示工作表013
• 1.3.8保护工作表014
• 1.4单元格和区域的操作014
• 1.4.1选择单元格014
• 1.4.2插入与删除单元格016
• 1.4.3合并单元格017
• 1.4.4调整行高和列宽017
• 1.5调整工作簿视图、比例和窗口快速查看内容018
• 1.5.1认识工作簿视图019
• 1.5.2调整显示比例022
• 1.5.3调整显示窗口023
• 回顾拓展026
• 自定义快速访问工具栏026
• 快速打开最近使用的工作簿027
• 以只读方式打开工作簿027
• 趁热打铁：创建档案保存年限表028
• 常用表格030
• 第2章公式、函数、图表的数据来源032
• 2.1输入数据033
• 2.1.1认识数据的类型033
• 2.1.2巧妙利用文本型数据输入长数字033
• 2.1.3输入特殊数据034
• 2.2高效输入数据035
• 2.2.1利用设置单元格格式输入035
• 2.2.2利用数据有效性输入036
• 2.2.3使用记录单输入037
• 2.3快速编辑数据039
• 2.3.1填充与复制数据039
• 2.3.2使用查找与替换功能编辑数据041
• 2.3.3快速定位数据042
• 2.4导入外部数据043
• 2.4.1导入Access中的数据043
• 2.4.2导入网站中的数据044
• 2.4.3导入文本文档中的数据044
• 2.4.4导入Word中的数据045
• 回顾拓展047
• 快速输入相同特征的数据047
• 创建下拉列表快速输入数据047
• 快速删除重复项048
• 自动输入小数点049
• 趁热打铁：创建员工通讯录049
• 常用表格052
• 第3章源数据的整理与汇总054
• 3.1数据的排序055
• 3.1.1简单排序055
• 3.1.2多关键字排序055
• 3.1.3自定义排序序列056
• 3.2数据的筛选057
• 3.2.1简单筛选058
• 3.2.2关键字筛选058
• 3.2.3高级筛选059
• 3.3数据的分类汇总060
• 3.3.1单字段分类汇总060
• 3.3.2嵌套分类汇总061
• 3.3.3创建组实现分类汇总061
• 3.4使用合并计算快速汇总数据063
• 3.4.1按位置合并063
• 3.4.2按字段合并064
• 回顾拓展066
• 按字母大小写排序066
• 使用通配符模糊筛选066
• 分页打印分类汇总结果067
• 趁热打铁：整理公司竣工工程一览表067
• 常用表格071
• 第4章公式、函数应用基础072
• 4.1公式与函数073
• 4.1.1公式的组成073
• 4.1.2什么是数组公式073
• 4.1.3函数的结构074
• 4.1.4函数的种类075
• 4.2公式的运算符075
• 4.2.1运算符的类型075
• 4.2.2运算符的优先级076
• 4.3单元格的引用077
• 4.3.1A1引用和R1C1引用077
• 4.3.2相对引用078
• 4.3.3绝对引用078
• 4.3.4混合引用079
• 4.3.5三维引用079
• 4.4公式的输入与编辑080
• 4.4.1输入公式080
• 4.4.2移动和复制公式081
• 4.4.3使用数组公式082
• 4.5函数的输入与编辑083
• 4.5.1三种输入函数的方法083
• 4.5.2嵌套使用函数085
• 4.6利用名称输入简单公式和函数086
• 4.6.1什么是名称086
• 4.6.2创建名称的三种方法086
• 4.6.3管理名称088
• 4.6.4在公式和函数中应用名称089
• 4.7公式与函数的审核090
• 4.7.1认识错误类型090
• 4.7.2追踪引用或从属单元格091
• 4.7.3监视窗口091
• 4.7.4显示或隐藏公式092
• 4.7.5查看公式求值步骤093
• 4.7.6公式错误检查094
• 回顾拓展096
• 公式记忆式键入096
• 使用括号改变公式计算顺序096
• 引用不同工作簿中的数据097
• 趁热打铁：在销售业绩表中创建名称并计算业绩097
• 常用表格101
• 第5章认识常用函数103
• 5.1文本函数104
• 5.1.1使用FIND函数求字符串位置104
• 5.1.2使用LEFT、MID、RIGHT函数
• 返回指定字符105
• 5.1.3使用TEXT函数格式化数据106
• 5.2逻辑函数106
• 5.2.1使用AND函数取交集106
• 5.2.2使用OR函数取并集107
• 5.2.3使用IF函数根据条件返回不同值108
• 5.3查找与引用函数109
• 5.3.1使用LOOKUP函数查找数据109
• 5.3.2使用HLOOKUP与VLOOKUP函数水平和垂直查找数据110
• 5.3.3使用INDEX函数得到指定内容111
• 5.4日期和时间函数113
• 5.4.1使用DATE函数返回日期序列号114
• 5.4.2使用DAYS360函数求日期差114
• 5.4.3使用WEEKDAY函数求星期几115
• 5.5数学与三角函数116
• 5.5.1使用CEILING函数按条件向上舍入116
• 5.5.2使用INT函数向下取整117
• 5.6统计函数118
• 5.6.1使用COUNTIF函数按条件统计118
• 5.6.2使用FREQUENCY计算数据频率分布119
• 5.6.3使用RANK函数获取排位信息120
• 5.7财务函数120
• 5.7.1使用PMT函数求贷款的每期偿还额121
• 5.7.2使用FV函数计算投资的未来值121
• 5.7.3使用SYD函数计算折旧值122
• 5.8数据库函数123
• 5.8.1使用DCOUNT函数统计单元格个数123
• 5.8.2使用DAVERAGE函数计算数据的平均值123
• 常用表格124
• 第6章图表应用基础126
• 6.1认识图表127
• 6.1.1常用的图表类型127
• 6.1.2主要的图表组成元素127
• 6.1.3Excel基础图表的两种样式128
• 6.2创建图表129
• 6.2.1准备源数据129
• 6.2.2创建基础图表130
• 6.3图表的基本操作130
• 6.3.1更改图表类型131
• 6.3.2更改图表中的数据131
• 6.3.3更改图表布局132
• 6.3.4更改图表样式133
• 6.4图表的编辑和修饰134
• 6.4.1准确选择图表的项目134
• 6.4.2删除图表的网格线134
• 6.4.3添加数据标签135
• 6.4.4修改绘图区背景颜色135
• 6.4.5设置数据系列格式136
• 6.4.6修改图例位置137
• 回顾拓展138
• 创建并应用图表模板138
• 在图表中显示隐藏的数据和空单元格138
• 创建静态图表139
• 趁热打铁：创建季度销售数据对比图139
• 常用图表141
• 第7章认识高级图表143
• 7.1直观反映数据增减的瀑布图144
• 7.1.1什么是瀑布图144
• 7.1.2简单瀑布图145
• 7.1.3负值瀑布图146
• 7.1.4通过涨跌柱线创建瀑布图149
• 7.2处理数据点相差大的断层图150
• 7.2.1什么是断层图150
• 7.2.2使用单个柱形图创建断层图151
• 7.2.3使用模拟Y轴法创建152
• 7.3把握问题重点的柏拉图156
• 7.3.1什么是柏拉图156
• 7.3.2创建从0开始的柏拉图156
• 7.3.3创建不从0开始的柏拉图158
• 7.4用于数据统计的直方图160
• 7.4.1什么是直方图160
• 7.4.2使用数据分析工具创建直方图160
• 7.4.3使用柱形图创建直方图161
• 7.5用于项目管理的甘特图165
• 7.5.1什么是甘特图165
• 7.5.2创建甘特图166
• 常用图表168
• 第二篇高级篇
• 第8章在Excel其他功能中使用函数172
• 8.1在条件格式中使用函数173
• 8.1.1添加条件格式173
• 8.1.2使用公式确定要设置格式的单元格175
• 8.2在数据有效性中使用函数180
• 8.2.1设置数据有效性180
• 8.2.2自定义数据有效性条件183
• 回顾拓展187
• 突出显示文本包含的单元格187
• 用数据条显示单元格内数据大小188
• 圈释无效数据188
• 趁热打铁：使用二级列表快速创建产品信息表189
• 常用表格191
• 第9章使用迷你图创建单元格内的微型图表193
• 9.1认识迷你图194
• 9.1.1什么是迷你图194
• 9.1.2迷你图的类型194
• 9.2创建迷你图195
• 9.2.1创建单个迷你图195
• 9.2.2创建迷你图组196
• 9.3编辑迷你图196
• 9.3.1更改迷你图数据197
• 9.3.2更改迷你图类型197
• 9.4美化迷你图198
• 9.4.1更改迷你图样式198
• 9.4.2显示标记199
• 9.4.3更改迷你图颜色200
• 回顾拓展200
• 隐藏和空单元格的设置技巧200
• 组合单个迷你图201
• 设置迷你图坐标201
• 趁热打铁：创建年度销售数据趋势迷你图202
• 常用图表204
• 第10章巧用动态图表让数据更有表现力206
• 10.1认识动态图表207
• 10.1.1什么是动态图表207
• 10.1.2创建动态图表的方法207
• 10.1.3常用于创建动态图表的函数208
• 10.2使用数据有效性和函数创建动态图表210
• 10.2.1使用数据有效性和函数创建辅助区域210
• 10.2.2在图表中使用辅助区域211
• 10.3通过定义名称创建动态图表212
• 10.3.1在定义名称时使用函数212
• 10.3.2在图表中使用名称213
• 10.4使用窗体控件创建动态图表214
• 10.4.1认识窗体控件214
• 10.4.2在图表中使用窗体控件215
• 10.5制作动态标题217
• 回顾拓展218
• 使用“视图管理器”创建动态图表218
• 利用“数据透视图”创建动态图表218
• 创建随鼠标变化的动态图表218
• 使用“自动筛选”巧妙创建动态图表219
• 趁热打铁：使用动态图表展示各产品单价的变化趋势219
• 常用图表221
• 第11章将图形、图片应用到图表中223
• 11.1在图表中插入形状224
• 11.1.1认识形状224
• 11.1.2绘制形状224
• 11.1.3格式化形状225
• 11.1.4在形状中添加文字227
• 11.2为图表添加艺术字228
• 11.2.1创建艺术字228
• 11.2.2自定义艺术字效果230
• 11.3在图表中使用图片231
• 11.3.1使用图片美化数据系列231
• 11.3.2将图片设置为绘图区或图表区背景232
• 11.3.3在图表中插入图片美化图表233
• 11.3.4编辑插入的图片234
• 回顾拓展234
• 连续绘制多个相同形状234
• 在图表添加超链接235
• 在图表中添加文本框输入文字235
• 在图表中插入剪贴画236
• 趁热打铁：使用形状、艺术字、图片美化业绩对比图236
• 常用图表239
• 第三篇应用篇
• 第12章员工档案管理系统242
• 问题提出243
• 12.1实例概述243
• 12.1.1分析实例应用环境243
• 12.1.2初探实例制作流程243
• 解决方案243
• 12.2从身份证号码中提取员工的基本信息243
• 12.2.1快速输入身份证号码244
• 12.2.2判断身份证号码是否输入正确244
• 12.2.3使用LEFT函数提取员工出生地区245
• 12.2.4使用MID函数提取员工出生日期246
• 12.2.5使用RIGHT函数提取员工性别247
• 12.3计算员工工龄、合同到期日247
• 12.3.1计算员工年龄248
• 12.3.2使用DATEDIF函数计算员工工龄248
• 12.3.3使用DATE函数计算合同到期日期249
• 12.3.4使用数组公式自动提醒员工生日251
• 12.4创建信息查询工作表250
• 12.4.1使用数据有效性设置姓名下拉列表251
• 12.4.2使用IF、VLOOKUP函数引用员工性别等信息252
• 12.4.3使用INDEX、MATCH函数查找员工籍贯等信息253
• 12.4.4使用OFFSET函数偏移引用员工工资账户255
• 常用表格256
• 第13章员工薪酬福利管理系统258
• 问题提出259
• 13.1实例概述259
• 13.1.1分析实例应用环境259
• 13.1.2初探实例制作流程259
• 解决方案259
• 13.2自动获取员工基本工资、岗位工资259
• 13.2.1引用员工基本工资数据260
• 13.2.2引用岗位工资数据260
• 13.3获取员工相关的应发和扣发金额261
• 13.3.1统计员工的补贴费用261
• 13.3.2计算员工加班费262
• 13.3.3计算员工缺勤扣款264
• 13.3.4统计员工奖金金额265
• 13.4计算社会保险个人应扣除费用266
• 13.4.1计算养老保险缴费266
• 13.4.2计算医疗保险缴费267
• 13.4.3计算失业保险缴费268
• 13.4.4计算公积金缴费269
• 13.5计算应扣个人所得税269
• 13.5.1计算应发工资269
• 13.5.2确定个人所得税税率270
• 13.5.3计算个人所得税271
• 13.6计算实发工资金额271
• 13.6.1计算实发工资271
• 13.6.2按照部门汇总工资额272
• 13.7创建发放工资条273
• 13.7.1制作工资条273
• 13.7.2打印工资条274
• 常用表格275
• 第14章业绩奖金评定系统277
• 问题提出278
• 14.1实例概述278
• 14.1.1分析实例应用环境278
• 14.1.2初探实例制作流程278
• 解决方案278
• 14.2创建奖金评定标准表278
• 14.3统计每位销售员当月总销售额279
• 14.4创建奖金评定表280
• 14.4.1判断业绩奖金提成率280
• 14.4.2计算基本业绩奖金281
• 14.4.3计算本月累计销售额282
• 14.4.4计算累计销售额奖金282
• 14.4.5计算员工总奖金283
• 14.5评选最佳销售奖283
• 14.6评估员工业绩284
• 14.6.1创建员工销售业绩图284
• 14.6.2添加趋势线285
• 常用表格和图表286
• 第15章新进人员管理系统288
• 问题提出289
• 15.1实例概述289
• 15.1.1分析实例应用环境289
• 15.1.2初探实例制作流程289
• 解决方案289
• 15.2创建新进人员登记表289
• 15.3创建培训成绩管理系统291
• 15.3.1计算各员工总成绩292
• 15.3.2计算平均成绩292
• 15.3.3计算成绩排名293
• 15.3.4使用条件格式快速筛选出不及格的员工294
• 15.4分析新进员工情况295
• 15.4.1培训合格率分析295
• 15.4.2试用期合格率分析296
• 15.4.3新员工离职分析296
• 常用表格298
• 第16章客户管理系统300
• 问题提出301
• 16.1实例概述301
• 16.1.1分析实例应用环境301
• 16.1.2初探实例制作流程301
• 解决方案301
• 16.2客户基本资料表301
• 16.2.1使用记录单快速输入信息302
• 16.2.2判断客户信息是否详细303
• 16.2.3使用批注巧妙完善信息304
• 16.2.4查看更多客户联系方式305
• 16.3客户订单管理306
• 16.3.1创建订单管理表格306
• 16.3.2根据订单数据评估客户等级307
• 16.3.３创建客户等级饼图308
• 16.4客户来往账款管理309
• 16.4.1客户来往款明细表分析309
• 16.4.2欠款分析310
• 16.4.3创建催款单313
• 16.5创建系统导航页315
• 常用表格和图表316
• 第17章库存管理系统318
• 问题提出319
• 17.1实例概述319
• 17.1.1分析实例应用环境319
• 17.1.2初探实例制作流程319
• 解决方案319
• 17.2创建产品基本信息表319
• 17.3产品入库表320
• 17.3.1创建产品入库表320
• 17.3.2录入产品入库信息322
• 17.4产品出库表323
• 17.5产品库存信息汇总表324
• 17.5.1获取本期入库、本期出库和本期结余324
• 17.5.2设置自动提醒存货过少327
• 常用表格和图表328
• 第18章固定资产管理系统330
• 问题提出331
• 18.1实例概述331
• 18.1.1分析实例应用环境331
• 18.1.2初探实例制作流程331
• 解决方案331
• 18.2创建分类折旧表331
• 18.3创建固定资产台账表333
• 18.3.1使用SUMPRODUCT函数编制“编号”333
• 18.3.2计算残值333
• 18.3.3计算年折旧额和月折旧额334
• 18.4创建月折旧表335
• 18.4.1计算已使用月份335
• 18.4.2计算本月折旧额336
• 18.4.3计算本月累计折旧336
• 18.4.4计算本月净值337
• 18.4.5使用条件格式制作到期提示337
• 18.5创建折旧法比较分析图339
• 常用表格和图表341
• 第19章人事分析报表343
• 问题提出344
• 19.1实例概述344
• 19.1.1分析实例应用环境344
• 19.1.2初探实例制作流程344
• 解决方案344
• 19.2人事变动分析344
• 19.2.1创建人事流动月报表345
• 19.2.2分析员工人数变动情况345
• 19.2.3分析员工离职情况347
• 19.3学历分析349
• 19.3.1统计各学历层次的员工人数349
• 19.3.2创建学历分析图350
• 19.4年龄分析352
• 19.4.1统计各年龄段人数352
• 19.4.2创建年龄分析图353
• 19.5性别分析354
• 19.5.1统计各部门男、女员工人数354
• 19.5.2创建男女员工人数对比图356
• 常用表格和图表359
• 第20章产品成本分析报表361
• 问题提出362
• 20.1实例概述362
• 20.1.1分析实例应用环境362
• 20.1.2初探实例制作流程362
• 解决方案362
• 20.2各产品生产成本分析表362
• 20.2.1快速创建各产品成本分析表363
• 20.2.2费用结构对比图365
• 20.2.3产量和成本变动图366
• 20.2.4费用变动趋势图367
• 20.3主要产品单位成本分析表368
• 20.3.1创建主要产品成本分析表368
• 20.3.2单位成本对比图371
• 20.3.3单位成本项目增减图372
• 20.3.4单位成本结构增减图373
• 20.3.5各成本比重对比图373
• 20.4总生产成本分析表375
• 20.4.1计算总生产成本分析表中各项分析数据375
• 20.4.2总生产成本分析图表377
• 常用表格和图表378
• 第21章销售分析报表380
• 问题提出381
• 21.1实例概述381
• 21.1.1分析实例应用环境381
• 21.1.2初探实例制作流程381
• 解决方案381
• 21.2销售利润分析381
• 21.2.1创建销售数据表382
• 21.2.2创建单位成本表382
• 21.2.3创建增减变化数据表384
• 21.2.4添加销售利润变化图386
• 21.3销售预测分析388
• 21.3.1根据历史数据创建折线图388
• 21.3.2添加趋势线线性预测388
• 21.3.3添加TREND函数预测销售量389
• 21.3.4使用FORECAST函数预测销售量391
• 常用表格和图表391
• 第22章财务会计报表393
• 问题提出394
• 22.1实例概述394
• 22.1.1分析实例应用环境394
• 22.1.2初探实例制作流程394
• 解决方案394
• 22.2资产负债表394
• 22.2.1创建资产负债表基本表格395
• 22.2.2计算资产合计和负债合计等总计数397
• 22.2.3设置数据对齐方式399
• 22.3利润表400
• 22.3.1创建利润表基本表格400
• 22.3.2计算营业利润、利润总额和净利润401
• 22.4现金流量表401
• 22.4.1创建现金流量表基本表格402
• 22.4.2计算小计403
• 22.4.3计算现金及现金等价物净增加额404
• 22.5所有者权益变动表404
• 常用表格405
• 第23章财务报表分析407
• 问题提出408
• 23.1实例概述408
• 23.1.1分析实例应用环境408
• 23.1.2初探实例制作流程408
• 解决方案408
• 23.2资产负债表分析408
• 23.2.1企业自身资产状况及资产变化说明409
• 23.2.2企业自身负债及所有者权益状况及说明415
• 23.2.3企业的财务类别状况在行业中的偏离416
• 23.3利润及利润分配表分析418
• 23.3.1分析收入结构比例418
• 23.3.2分析成本费用结构比例419
• 23.3.3利润增长情况分析420
• 23.3.4创建收入增减图421
• 23.3.5费用增减图421
• 23.4现金流量表分析422
• 23.4.1现金流量结构分析422
• 23.4.2现金流动性分析424
• 23.4.3获取现金能力分析425
• 23.4.4现金流动图426
• 常用表格和图表428
• 第四篇Excel VBA篇
• 第24章使用Excel VBA开发程序前必须掌握的知识432
• 24.1掌握Excel VBA的基本语法知识433
• 24.1.1标识符433
• 24.1.2常量、变量与运算符433
• 24.1.3VBA流程控制语句437
• 24.1.4VBA过程443
• 24.1.5VBA的对象及其属性、方法和事件445
• 24.2掌握Excel VBA的基本操作446
• 24.2.1启动Visual Basic编辑器446
• 24.2.2各视图窗口的结构及用途449
• 24.2.3Excel VBA帮助文件的搜寻451
• 24.2.4模块程序的管理操作451
• 24.2.5程序的调试操作453
• 表格常用VBA功能455
• 第25章使用VBA开发数据自动化计算和分析功能457
• 25.1使用VBA开发数据自动计算拓展功能458
• 25.1.1根据身份证号码判断公民性别458
• 25.1.2自动将日期转换为中文星期数459
• 25.1.3轻松计算孕妇的预产期和孕周数460
• 25.1.4轻松计算缺勤扣款金额461
• 25.1.5一键获取贷款的月还款额、月还款利息和本金461
• 25.2使用VBA开发数据自动分析功能462
• 25.2.1一键创建各店营业额排行榜463
• 25.2.2一键删除重复登记的数据464
• 25.2.3一键创建迷你折线图展示数据趋势464
• 25.2.4根据所选数据快速创建特定格式的图表465
• 25.2.5批量更改图表类型466
• 25.2.6以涨跌柱线分析产品同期销量的涨跌情况467
• 表格常用VBA功能468
• 第五篇技巧篇
• 第26章公式、函数与图表应用技巧472
• 26.1源数据的准备技巧473
• 26.1.1快速输入当前日期和时间473
• 26.1.2快速输入大写金额473
• 26.1.3在不连续的单元格中输入相同内容474
• 26.1.4快速在多个工作表中输入相同内容475
• 26.1.5使用记忆功能快速键入重复数据475
• 26.2公式与函数应用技巧476
• 26.2.1使用【F9】键查看分步计算结果476
• 26.2.2保护工作表中的公式477
• 26.2.3利用名称查询478
• 26.2.4使用运算符巧妙替代逻辑函数480
• 26.2.5查找目标字符出现N次的位置481
• 26.2.6金额数字分列481
• 26.2.7自动获取圆周率482
• 26.2.8巧用IF函数使单元格不显示0现金值483
• 26.2.9对多个单元格数据执行同样的运算483
• 26.3图表应用技巧484
• 26.3.1将图表旋转一个角度484
• 26.3.2快速统一图表的字体485
• 26.3.3突出显示特殊的坐标轴数字486
• 26.3.4创建多层次的分类标签487
• 26.3.5处理差异悬殊的数据487
• 26.3.6在柱形图中使用图片488
• 26.3.7使用标注强调图表重点488
• 26.3.8使用单元格快速排列多个图表489
• 26.3.9在图表中画直线490
• 26.3.10在图表中显示趋势线的公式492