《Excel公式、函数与图表案例实战从入门到精通 》

Excel公式、函数与图表案例实战从入门到精通

视频自学版

恒盛杰资讯  编著

前  言

PREFACE

Excel是Microsoft Office办公软件套装中最重要的组件之一，它的主要功能可大致分为数据操作与处理、公式与函数、图表与图形、数据分析、宏与VBA五个方面，其中公式、函数与图表占据近一半。掌握这部分功能，是Excel新手到高手的必经之路。本书立足于帮助各行各业的办公人员使用Excel的公式、函数与图表功能解决实际问题而编写，为读者的Excel晋级之路扫清障碍。

◎内容结构

全书共17章，可分为3个部分。

★第1部分为第1～6章，主要讲解各类函数在公式中的应用，包括文本函数、日期和时间函数、查找和引用函数、数学和三角函数、统计函数、财务函数等。

★第2部分为第7～8章，主要讲解VBA函数和数组公式的应用。

★第3部分为第9～17章，主要讲解各类图表在实际工作中的应用，包括柱形图、折线图、饼图、条形图、面积图、散点图、雷达图、股价图、直方图、组合图表、高级图表等。

◎编写特色

★本书中每个知识点的讲解都依托相应的实例，每个操作步骤都以“一步一图”的方式呈现，清晰、直观、易懂，便于读者理解和掌握。

★书中以“提示”“重点函数介绍”等小栏目的形式穿插了大量延展知识或从实际工作中提炼和总结出的经验与诀窍，每章最后以“专栏”形式讲解较易被新手忽视、却在日常工作中有很大用处的Excel操作，帮助读者增长见识、开阔眼界。

★本书的云空间资料完整收录了书中全部实例的相关文件及操作视频。读者按照书中的讲解，结合实例文件和操作视频边看、边学、边练，能够更加轻松、高效地理解和掌握知识点。

◎读者对象

本书既适合具备一定Excel操作基础且打算深入学习公式、函数与图表功能的读者，也适合渴望以更智能化的方法提高效率的办公人员、学生等参考，还可作为大专院校或社会培训机构的教材。

由于编者水平有限，在编写本书的过程中难免有不足之处，恳请广大读者指正批评，除了扫描二维码关注订阅号获取资讯以外，也可加入QQ群227463225与我们交流。

编者

2019年2月

如何获取云空间资料

一　 扫描关注微信公众号

在手机微信的“发现”页面中点击“扫一扫”功能，如右一图所示，进入“二维码/条码”界面，将手机对准右二图中的二维码，扫描识别后进入“详细资料”页面，点击“关注”按钮，关注我们的微信公众号。

二　 获取资料-和密码

点击公众号主页面左下角的小键盘图标，进入输入状态，在输入框中输入本书书号的后6位数字“621928”，点击“发送”按钮，即可获取本书云空间资料的-和访问密码。

三　 打开资料下载页面

方法1：在计算机的网页浏览器地址栏中输入获取的-（输入时注意区分大小写），如右图所示，按Enter键即可打开资料下载页面。

方法2：在计算机的网页浏览器地址栏中输入“wx.qq.com”，按Enter键后打开微信网页版的登录界面。按照登录界面的操作提示，使用手机微信的“扫一扫”功能扫描登录界面中的二维码，然后在手机微信中点击“登录”按钮，浏览器中将自动登录微信网页版。在微信网页版中单击左上角的“阅读”按钮，如右图所示，然后在下方的消息列表中找到并单击刚才公众号发送的消息，在右侧便可看到-和相应密码。将-复制、粘贴到网页浏览器的地址栏中，按Enter键即可打开资料下载页面。

四　 输入密码并下载资料

在资料下载页面的“请输入提取密码”下方的文本框中输入步骤2中获取的访问密码（输入时注意区分大小写），再单击“提取文件”按钮。在新页面中单击打开资料文件夹，在要下载的文件名后单击“下载”按钮，即可将其下载到计算机中。如果页面中提示选择“高速下载”还是“普通下载”，请选择“普通下载”。下载的资料如为压缩包，可使用7-Zip、WinRAR等软件解压。

提示

读者在下载和使用云空间资料的过程中如果遇到自己解决不了的问题，请加入QQ群227463225，下载群文件中的详细说明，或找群管理员提供帮助。

目  录

CONTENTS

前言

如何获取云空间资料

第1章　文本函数在公式中的应用

1.1创建员工联系名单   10

1.1.1　输入员工姓名与地址数据      10

1.1.2　用分列功能编辑数据       12

1.1.3　输入身份证号并提取信息      15

1.2创建销售明细表       19

1.2.1　评定销售星级     19

1.2.2　提取销售金额     21

1.2.3　创建销售金额条形图       22

1.3创建预测与实际销量分析表 23

1.3.1　计算预测与实际销量差异率  23

1.3.2　制作直方图分析差异情况      24

专栏　FIND函数的使用      27

第2章　日期和时间函数在公式中的应用

2.1创建员工年龄与工龄统计表 29

2.1.1　输入原始数据信息    29

2.1.2　计算员工年龄     32

2.1.3　计算员工工龄     33

2.2创建员工考勤统计表      35

2.2.1　输入考勤统计表数据       35

2.2.2　统计员工迟到、早退及加班时间 36

2.2.3　设置与打印工作表    38

2.3创建周产量与工时统计分析表    41

2.3.1　根据日期生成星期    41

2.3.2　工时统计分析     43

2.3.3　产量统计分析     44

专栏　加密工作簿  45

第3章　查找与引用函数在公式中的应用

3.1创建员工资料查询表      46

3.1.1　输入员工资料表中的数据信息     46

3.1.2　制作员工资料查询表       48

3.2创建销售员奖金与等级评比表    52

3.2.1　员工奖金等级评定    52

3.2.2　按奖金高低排列员工姓名      54

3.3创建产品销售额查询表  55

3.3.1　设置查询下拉列表    55

3.3.2　查询产品相关信息    56

专栏　HLOOKUP函数的使用    58

第4章　数学和三角函数在公式中的应用

4.1创建快递寄件明细表      60

4.1.1　计算续重费用与寄件总运费  60

4.1.2　计算不同省份的寄件总重量  61

4.1.3　筛选满足条件的寄件信息      63

4.2创建成本计算表       65

4.2.1　计算成本合计     65

4.2.2　计算成本差异率 67

4.3创建日销售额统计明细表     68

4.3.1　创建原始数据表 68

4.3.2　制作销售额明细查询表   71

专栏　自定义筛选数据 72

第5章　统计函数在公式中的应用

5.1创建员工考核指标分析表     74

5.1.1　分析考核成绩中的最值   74

5.1.2　众数、中位数、平均值的计算     76

5.2创建企业生产产量预测表     79

5.2.1　线性预测产量分析法      79

5.2.2　指数预测产量分析法       81

5.2.3　数组公式预测产量分析法      81

5.3创建员工销售情况统计分析表    83

5.3.1　销售数据统计与分析       83

5.3.2　销售员工评定与排名       85

专栏　统计单元格个数 87

第6章　财务函数在公式中的应用

6.1创建贷款方案分析表      89

6.1.1　不同利率与还款期限下的月还款额计算   89

6.1.2　固定利率与还款期限下的年还款额计算   91

6.2创建固定资产折旧计算表     94

6.2.1　使用平均年限折旧法计算月折旧额   94

6.2.2　使用固定余额递减折旧法计算年折旧额 96

6.2.3　使用双倍余额递减法计算年折旧额    98

6.2.4　使用年限总和折旧法计算年折旧额    99

6.2.5　使用可变递减折旧法计算折旧额 100

6.3创建企业投资收益率表  102

6.3.1　根据收入计算内部收益率      102

6.3.2　根据现金流计算内部收益率  104

专栏　未来值计算  105

第7章　自定义VBA函数的应用

7.1制作活动倒计时计时牌  107

7.1.1　计算倒计时时间 107

7.1.2　实现自动计时功能    109

7.2自定义函数保护工作表  111

7.2.1　编辑工作安排表 111

7.2.2　实现自动打开与保护工作表功能 112

7.3自定义函数统计分析销售数据    115

7.3.1　实现工作表自动统计功能      115

7.3.2　制作自动统计操作按钮   119

专栏　自定义函数合并字符串   121

第8章　数组公式的应用

8.1创建销售成绩统计分析表     123

8.1.1　销售等级与排名统计       123

8.1.2　销售成绩分析     125

8.2创建工作备忘录       128

8.2.1　创建日期查询数据    128

8.2.2　自动生成日期     129

8.3创建商品订单查询表      131

8.3.1　制作商品订单查询表       131

8.3.2　返回订单明细     133

专栏　使用数组公式重新排序数据  135

第9章　柱形图的应用

9.1创建销售业绩对比图表  137

9.1.1　制作销售业绩对比图表   137

9.1.2　美化销售业绩对比图表   138

9.2创建企业收支情况分析图表 141

9.2.1　制作企业收支情况分析图表  141

9.2.2　移动企业收支情况分析图表  145

9.3创建不同产品销售比例图表 146

9.3.1　制作不同产品销售比例图表  146

9.3.2　创建销售数据分析图表   148

专栏　将二维图表转换为三维图表  150

第10章　折线图的应用

10.1创建销售收益率比较分析图      151

10.1.1　创建销售收益率折线图 151

10.1.2　美化销售收益率折线图 152

10.2创建销售目标实现情况图   155

10.2.1　创建销售情况折线图     155

10.2.2　添加形状展示销售趋势 158

10.3创建企业盈余和赤字分析图      161

10.3.1　创建企业盈余和赤字分析折线图      161

10.3.2　添加形状和文本框对图表进行注释  163

专栏　预测未来销售金额    166

第11章　饼图的应用

11.1创建企业市场份额分析图表      168

11.1.1　创建企业市场份额饼图 168

11.1.2　添加竖排文字标题  172

11.2创建产品成本比例分析图表      173

11.2.1　创建产品成本比例分析圆环图   173

11.2.2　美化图表    175

11.3创建固定资产结构分析图表      176

11.3.1　创建固定资产结构复合饼图       176

11.3.2　移动并完善固定资产结构分析图表  178

专栏　分离饼图中的单个数据系列  180

第12章　条形图的应用

12.1创建不同地区品牌占有率比较图表 181

12.1.1　创建不同地区品牌占有率比较条形图     181

12.1.2　创建超链接切换工作表 185

12.2创建折扣与销售量关系分析图表     187

12.2.1　创建折扣与销售量关系分析条形图  187

12.2.2　进一步优化图表      189

12.3创建企业盈亏状况分析图表      193

12.3.1　创建企业盈亏状况分析条形图   193

12.3.2　完善企业盈亏状况分析图表       196

专栏　使用形状将条形图中的柱体变为箭头       198

第13章　面积图的应用

13.1创建总销售额趋势图表       199

13.1.1　创建总销售额趋势分析面积图   199

13.1.2　移动并完善面积图  202

13.2创建产品逐步淘汰分析图表      203

13.2.1　创建产品市场占有率百分比堆积面积图 203

13.2.2　打印并保护产品逐步淘汰分析图表  206

13.3创建企业与整体市场的关系分析图表    208

13.3.1　创建企业产品与整体市场关系面积图     208

13.3.2　设置图表对象完善图表效果       211

专栏　将工作表发布为网页       213

第14章　散点图的应用

14.1创建企业月度业务量图表   215

14.1.1　创建企业月度业务量散点图       215

14.1.2　设置图表的格式      216

14.2创建收货天数和客户满意度相关性分析图表      218

14.2.1　使用数据完成散点图的制作       218

14.2.2　添加趋势线分析相关性 220

14.3创建企业投资周转率分析图表  222

14.3.1　创建投资周转率分析散点图       222

14.3.2　美化投资周转率分析图表    225

专栏　制作气泡图  226

第15章　其他常用图表的应用

15.1创建各品牌汽车性能比较图表  229

15.1.1　创建汽车性能比较雷达图    229

15.1.2　使用文本框添加图表标题    232

15.2创建股票行情分析图表       234

15.2.1　创建不同类型的股价图 234

15.2.2　组合多个股价图      239

15.3创建销售量频率分布情况图表  239

15.3.1　创建销售量分布情况的直方图   240

15.3.2　更改直方图类型展现比例趋势   242

专栏　创建旭日图按层次分析数据的占比    242

第16章　组合图表的应用

16.1创建含有平均值参考线的组合图表 244

16.1.1　添加制作组合图表的辅助数据   244

16.1.2　创建组合图表展现销售额情况   245

16.2创建不同产品销售额与销售数量关系图表   248

16.2.1　创建不同产品销售额与销售数量组合图表    248

16.2.2　美化不同产品销售额与销售数量组合图表    250

16.3创建销售额与销售点数量关系图表 251

16.3.1　创建销售额与销售点数量面积图      251

16.3.2　更改“销售点”数据系列的图表类型   254

专栏　创建数据透视图 256

第17章　高级图表的应用

17.1创建按年份查询产品地区占有率图表    257

17.1.1　创建图表的主体部分     257

17.1.2　添加控件制作动态图表 259

17.2创建项目进度情况图表       261

17.2.1　添加辅助序列   262

17.2.2　制作直观显示项目进度的甘特图      263

17.3创建实际与预测月销售额查询分析图表       266

17.3.1　创建销售额分析图表     266

17.3.2　实现图表动态效果  269

专栏　设置控件格式     271

数据结构 数据可视化

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。