构建高效数据分析模板：职场必学的Excel函数与动态图表高级应用

图书标准信息

• 作者 韩小良 编
• 出版社 中国铁道出版社
• 出版时间 2014-08
• 版次 1
• ISBN 9787113183233
• 定价 49.00元
• 装帧 平装
• 开本 16开
• 纸张 胶版纸
• 页数 296页
• 字数 361千字
• 正文语种 简体中文

【内容简介】

Excel是一个强大的数据管理和分析工具，但是很多人对Excel的学习和应用，热衷于快捷键和小技巧的使用，并没有真正掌握数据分析的两个重要的工具：函数和动态图表。本书结合大量的企业实际案例，重点介绍函数和动态图表在数据管理和分析中的实用技能、解决问题的逻辑、思路和方法，以及实际应用模板，使读者快速提高Excel数据分析能力！本书介绍的大量案例，都是来自于作者的培训第一线，非常具有实用价值，大部分案例实际上可作为现成的模板，应用于实际工作中，可使工作效率迅速成倍提高。
本书适合各类企事业单位的管理者，也可作为大专院校经济专业的本科生、研究生和MBA学员的教材和学习参考书。

【作者简介】

韩小良，资深实战型Excel培训讲师和应用解决方案专家，对Excel及ExcelVBA在企业管理中的应用有着较深的研究和独到的认识，对Excel及VBA在管理中高级应用培训有着丰富的实战经验，已经为包括中国银行、中国民生银行、浦发银行、深圳联合证券、谷歌、上海医药集团、上海麦考林国际邮购、上海电信、南瑞集团、美国强生医疗器械、南京朗诗集团等数百家中外企业讲授Excel及ExcelVBA高效管理应用课程，著有30余部Excel应用方面的著作，开发了数套基于Excel的人力资源管理系统、工资管理系统、财务管理系统、进销存管理系统、合同管理系统、固定资产管理系统、客户管理系统、销售管理系统等，并应用于数十家企业，取得了明显的经济效益。

【目录】

Part01函数和公式
第1章为什么要进行数据分析
1.1一个实际咨询案例3
1.2企业要做好四个数据流的管理和分析7
1.3正确认识Excel工具8
1.4怎样正确高效使用Excel8
1．树立正确的Excel理念8
2．设计标准化、规范化的Excel表格9
3．掌握Excel实用的数据管理和分析的思路、方法和技能9
4．利用掌握的Excel数据分析技能制作各种适合企业具体情况的自动化数据分析模板9
第2章标准化、规范化Excel表格数据
2.1设计标准化、规范化的Excel基础表格12
1．结构的科学性13
2．数据的易读性14
3．汇总的方便性14
4．分析的灵活性16
5．外观的美观性17
案例2-1设计标准化表格的方法与技巧18
1．表格结构设计18
2．工号的自动连续填充19
3．规范姓名的输入，不允许在姓名文字中输入空格19
4．规范快速输入部门名称19
5．快速输入学历名称20
6．快速输入婚姻状况20
7．输入不重复的18位身份证号码20
8．自动输入性别21
9．自动输入出生日期21
10．自动计算年龄21
11．规范输入入职时间21
12．自动计算本公司工龄22
13．保证员工基本信息的完整性22
14．规范输入离职时间22
15．规范输入离职原因22
16．创建表格，自动复制有效性和公式，并自动美化表格23
2.2不规范表格数据的标准化、规范化25
案例2-2修改非法日期，使之成为真正日期26
案例2-3修改文本型数字，使之能够使用函数计算27
案例2-4删除数据中的特殊字符，让数据还原本来面目28
案例2-5填充数据，保证数据的完整性29
案例2-6数据分列，让不同类型数据各居其位31
案例2-7分列数字和汉字——规律明显33
案例2-8分列数字和汉字——规律不明显34
案例2-9数据重新布局，将多次刷卡数据分成列34
案例2-10将道路及其门牌号分列35
总结40
第3章必须掌握的函数和公式基本技能
3.1梳理表格数据的逻辑关系42
1．搞清楚自己要做什么42
案例3-1管理费用的趋势分析和结构分析42
2．分析表格数据之间的逻辑关系43
案例3-2销售收入达成分析43
案例3-3计算物料最新采购价格和加权平均采购价格44
3．学会画逻辑流程图45
案例3-4根据工龄工资标准计算工龄工资45
案例3-5根据不同城市、不同工龄计算补贴标准46
4．设计好分析报告的结构和标题47
案例3-6重新设计报告标题以简化计算公式47
案例3-7设计二级分层表格结构48
3.2学会创建嵌套函数公式49
1．分解综合法49
案例3-8根据商品名称查找对应编码50
3
2．函数对话框——名称框法51
案例3-9应用单流程嵌套IF公式计算工龄工资51
3.3学会使用条件表达式解决复杂问题58
1．什么是条件表达式58
2．了解逻辑运算符59
3．简单的条件表达式59
4．复杂的条件表达式59
5．很多问题使用条件表达式来解决更方便60
3.4学点数组公式的知识61
1．数组的概念61
2．数组公式的概念62
3．输入数组公式63
4．数组公式应用案例剖析63
5．常见数组公式应用举例64
案例3-10计算数据区域内N个最大数或最小数之和64
案例3-11获取数据列中最后一个非空单元格数据64
3.5适当使用名称65
1．定义名称的规则65
2．定义名称的三种方法66
3．编辑、修改和删除名称68
3.6不可忽略的绝对引用和相对引用知识68
1．单元格引用方式68
2．单元格引用方式对公式的影响69
3．引用其他工作表单元格70
4．单元格引用的两种表示法A1和R1C1有什么不同70
3.7公式和函数的其他应当掌握的小技巧72
1．复制公式本身73
2．复制公式的一部分73
3．查看公式中的某些表达式计算结果73
4．将公式分行输入，以便使公式更加容易理解和查看73
第4章逻辑判断函数及其实际应用
4.1只使用IF函数的逻辑判断75
1．IF函数的基本语法75
案例4-1计算含有错误值的单元格区域76
2．嵌套IF函数76
3．使用条件表达式来代替嵌套IF函数77
4.2逻辑函数、信息函数联合使用的复杂逻辑判断77
1．使用AND函数组合与条件77
案例4-2将逾期数据按时间不同标识出来77
2．使用OR函数组合或条件78
案例4-3计算公司年休假天数78
3．使用IFERROR或者ISERROR函数处理公式的错误值79
4．使用ISNUMBER函数处理特殊的问题80
4.3实际应用案例解析80
案例4-4统计员工的出勤情况（固定时间）80
案例4-5统计员工的出勤情况（阶段时间）82
案例4-6统计18:00~20:00以及20:00以后的刷卡人数84
案例4-7处理重复刷卡数据87
案例4-8按成本中心分析工资的四分位值88
案例4-9根据税后工资反算税前工资91
案例4-10制作个人所得税计算表91
案例4-11调整信用期92
案例4-12判断应收账款账龄区间92
4.4逻辑函数和信息函数的其他应用93
1．自定义数据有效性93
案例4-13在员工信息表中输入身份证号码94
2．复杂的条件格式94
案例4-14自动跟踪不同的业绩变动率94
第5章日期函数及其实际应用
5.1常用日期函数的基本使用方法97
1．TODAY函数：动态日期计算98
2．EDATE函数：计算几个月之前或之后的日期98
3．EOMONTH函数：计算几个月之前或之后的月底日期98
4．DATEDIF函数：计算两个日期之间的期限99
5．DATE函数：把代表年、月、日的三个数字组合成一个日期99
6．YEAR、MONTH和DAY函数：把日期拆分成年、月、日三个数字100
7．WEEKDAY函数：判断日期为星期几100
8．WEEKNUM函数：判断日期为当年的第几周101
9．YEARFRAC函数：计算小数点年数101
5.2常用日期函数的实际应用案例101
案例5-1指定月数或年数后的具体日期101
案例5-2指定天数、月数或年数后的月底日期102
案例5-3根据出生日期计算年龄103
案例5-4根据入职日期计算工龄104
案例5-5设计动态的日程表和考勤表104
案例5-6计算应付款截止日，遇双休日顺延108
案例5-7设计动态的固定资产折旧表108
1．计算折旧开始日期109
2．计算固定资产到期日109
3．计算已计提折旧月数109
4．计算剩余折旧月数109
5．计算当月折旧额110
6．计算本年度累计折旧额110
7．计算累计折旧额110
8．其他计算公式和输入项目110
9．折旧表的使用110
案例5-8编制销售周报表111
5.3日期函数的其他应用案例112
案例5-9确定某日期所在年已经过去的天数和剩余天数112
案例5-10获取某个月最后一天的日期以及该月有多少天113
案例5-11确定某个日期在该年的第几季度113
第6章文本函数及其实际应用
6.1获取文本的部分数据115
案例6-1从文本字符串左边往右取指定长度的字符116
案例6-2从文本字符串右边往左取指定长度的字符116
案例6-3从文本字符串中间指定位置往右取指定长度的字符117
案例6-4从文本字符串右边数指定位置往左取指定长度的字符117
案例6-5从某个特定字符位置取指定长度的字符118
案例6-6将前部分是数字、后部分是汉字的字符串分成两列120
案例6-7将前部分是汉字、后部分是数字的字符串分成两列121
案例6-8将数字、汉字混合的字符串中的数字取出121
案例6-9将前部分是字母、后部分是数字的字符串分成两列123
案例6-10将前部分是数字、后部分是字母的字符串分成两列123
案例6-11将金额的各个数字依次填入到连续的单元格124
案例6-12从身份证号码获取出生日期和性别125
6.2把数字和日期转换为指定格式的文本126
1．TEXT函数的基本用法126
2．将一个单元格的日期和时间分成两列127
3．将日期转换成中文月份或者英文月份名称127
案例6-13根据日期流水账制作月份（英文标题）汇总表127
6.3获取当前文件的有关信息127
案例6-14获取文件路径、文件名和工作表名等信息127
第7章分类汇总函数及其实际应用
7.1常见的数据分类汇总问题概述130
1．两种常见的分类汇总方式：计数与求和130
2．两种条件下的分类汇总：单条件与多条件131
3．两种匹配模式下的分类汇总：精确匹配与模糊匹配131
4．五个分类汇总函数的基本使用语法131
7.2单条件计数与单条件求和132
1．精确匹配下的单条件计数132
案例7-1COUNTIF函数进行单条件计数：简单应用132
案例7-2使用COUNTIF函数进行自定义有效性设置：
不允许输入重复数据133
案例7-3使用COUNTIF函数核对两个表格的数据134
2．模糊匹配下的单条件计数136
案例7-4根据明细表统计每种材料的购买次数136
3．精确匹配下的单条件求和136
案例7-5计算每个供应商的采购次数和采购总金额136
4．模糊匹配下的单条件求和137
案例7-6计算每个部门和每个费用的总金额137
7.3多条件计数与多条件求和138
1．精确匹配下的多条件计数138
案例7-7按部门统计总人数、男女人数、已婚未婚人数、
各个学历的人数138
2．模糊匹配下的多条件计数139
3．精确匹配下的多条件求和140
案例7-8按地区分别汇总自营店和加盟店的销售总额和销售成本140
4．模糊匹配下的多条件求和141
案例7-9按客户、按产品大类进行汇总141
7.4SUMPRODUCT函数的综合应用141
1．SUMPRODUCT函数的基本用法142
案例7-10计算所有产品的销售总额、折扣额、销售净额142
2．SUMPRODUCT函数的变形用法143
案例7-11通过销售表计算每个客户的销售总额143
3．SUMPRODUCT函数用来进行条件计数144
案例7-12制作能够查看任意指定年份员工流动率变化的模板144
4．SUMPRODUCT函数用来进行多条件求和145
案例7-13计算每个产品类别、每个月的总金额145
案例7-14根据管理费用科目余额表汇总每个费用项目
每个月的总金额146
案例7-15从销售及回款清单汇总每个客户每个账龄的应收
账款总额147
5．SUMPRODUCT函数的其他应用148
案例7-16从采购数据计算物料的加权平均采购价格和算术
平均采购价格148
案例7-17按照客户编码和存货名称核对从两套软件中导出
的数据是否一致149
6．SUMPRODUCT函数的优点和缺点150
7.5条件计数函数与条件求和函数的一个特殊问题150
第8章查找引用函数及其实际应用
8.1查找引用问题概述153
1．单条件查找和多条件查找154
2．精确匹配数据查找和模糊匹配数据查找155
3．精确定位单元格查找和模糊定位单元格查找155
4．单一数据查找和重复数据查找156
5．单区域查找和多区域查找156
6．固定区域查找和滚动区域查找157
7．常用查找引用函数的使用语法158
8.2VLOOKUP函数各种实际应用161
1．精确匹配下的单条件查询161
案例8-1查询每个人的实发工资161
2．模糊匹配下的单条件查询161
案例8-2查找指定城市价格161
3．多个区域的数据查找161
案例8-3根据编码从“低值品”和“原材料”中查询对应的数据161
4．从不同列取数的数据查找162
案例8-4随意指定一个工资项目就能把每个员工的工资项目取出来162
案例8-5查询指定员工的所有工资项目
（工资项目横向排列，顺序与工资表一致）163
案例8-6查询指定员工的所有工资项目（工资项目垂直排列，顺序
与工资表一致）163
5．模糊定位单元格查找163
案例8-7在应收账款清单中通过逾期天数判断账龄164
案例8-8统计各个税率下的人数、税前工资总额、个税总额164
8.3MATCH函数和INDEX函数的各种实际应用166
1．核对两个表格166
案例8-9使用MATCH函数核对两个表格数据166
2．精确定位指定数据的单元格地址167
案例8-10使用MATCH函数和ADDRESS函数查找指定
数据的单元格地址167
3．模糊定位指定数据所在单元格168
案例8-11利用查找函数进行数学插值计算168
4．定位最大值/最小值的单元格地址168
案例8-12从一个数据区域中查找最大值所属的地区和产品168
5．多条件精确查找169
案例8-13根据数据表计算每个员工的小时津贴170
案例8-14从流水账整理成分类汇总表171
6．模糊查找171
案例8-15分列汉字、字母和数字171
案例8-16自动根据工作令号把真正的成本对象编码查询出来172
7．财务中的典型应用：成本费用分析173
案例8-17从管理费用数据中按照部门和费用项目分类汇总174
案例8-18制作动态成本分析报告，查看任意产品的成本构成174
8．人力资源中的典型应用：制作员工简历表175
案例8-19设计动态查询表，查询指定员工的基本信息175
8.4INDIRECT函数的各种实际应用177
1．滚动循环查找技术：制作明细表177
案例8-20制作对账单表格，把指定客户的所有销售明细查找出来177
2．滚动汇总技术：快速汇总大量工作表181
案例8-21各月利润表数据的动态滚动汇总181
案例8-22制作动态员工工资单查询表182
案例8-23制作每个部门各个月的预算执行情况跟踪分析表183
案例8-24制作查看指定部门、指定费用各个月的跟踪分析表183
案例8-25按部门汇总每个月的应发工资合计数184
案例8-26按部门、按费用汇总跟踪分析每个月的预算执行情况185
案例8-27指定部门、指定费用的各个月汇总分析186
3．工作表间的累计值自动计算187
案例8-28自动计算每天的累计数187
4．利用INDIRECT函数与ROW函数构建常量数组188
8.5OFFSET函数的各种实际应用188
案例8-29获取动态数据区域，制作动态数据源的数据透视表188
案例8-30获取动态数据区域，作为数据有效性序列来源189
案例8-31计算截至指定月份的累计值190
案例8-32从任意指定项目列中进行分类汇总191
案例8-33复杂的考勤汇总192
8.6其他查找函数的实际应用194
案例8-34LOOKUP函数实际应用194
案例8-35CHOOSE函数实际应用195
案例8-36TRANSPOSE函数实际应用196
案例8-37使用HYPERLINK函数自动建立超链接196
案例8-38使用HYPERLINK函数设计产品图片查看系统197
8.7查询数据的几个应注意的问题197
1．查不到数据出现错误怎么办197
2．为什么会查不到数据198
3．如何区别对待零值与空值198
Part02动态图表
第9章制作动态图表的基本原理和方法
9.1两类动态图表解析201
案例9-1自动调整数据源的动态图表201
案例9-2控件控制的动态图表202
案例9-3两类动态图表的结合202
案例9-4在单元格利用数据有效性来控制图表的显示203
9.2制作动态图表五大基本方法及其原理203
1．筛选法204
案例9-5利用Excel的筛选功能实现订单动态监控204
2．动态名称法205
3．辅助区域法206
4．数据透视图法208
案例9-6制作数据透视图208
5．导入数据法208
案例9-7制作动态图表209
6．必须牢记的动态图表制作的六大步骤213
9.3让图表能动的元素都动起来213
案例9-8制作动态的图表标题214
案例9-9制作动态的坐标轴标题214
案例9-10制作动态的数据标签215
9.4制作动态图表需要注意的几个问题216
1．如何保护绘图数据的安全性216
2．使用表单控件制作动态图表需要注意的一个问题216
第10章利用表单控件制作各种动态图表
10.1插入控件的基本方法和注意事项218
1．插入表单控件的基本方法218
2．在功能区显示开发工具218
10.2选项按钮使用方法及应用案例220
案例10-1可控费用与不可控费用分析220
案例10-2两年销售收入、销售成本、销售利润分析222
10.3复选框使用方法及应用案例223
案例10-3三年资产价格同比分析224
案例10-4成本趋势分析225
10.4组合框使用方法及应用案例226
案例10-5使用一个组合框227
案例10-6使用多个彼此独立的组合框229
案例10-7使用多个彼此关联的组合框232
案例10-8分析指定时间段内的数据235
10.5滚动条使用方法及应用案例237
案例10-9利润敏感性分析模型238
案例10-10动态高亮显示数据点240
案例10-11动画效果的计划完成对比分析图243
10.6数值调节钮使用方法及应用案例244
案例10-12动态显示最新几个数据245
案例10-13动态显示指定日期之前或之后的几个数据246
10.7列表框使用方法及应用案例249
案例10-14使用列表框控制图表显示250
Part03自动化数据分析模板
第11章管理费用滚动跟踪分析模板
11.1设计滚动汇总表253
11.2制作动态趋势分析图254
11.3制作动态结构分析图255
1．指定部门下的费用结构分析255
2．指定费用下的部门占比分析257
第12章产品成本滚动跟踪分析模板
12.1分析模板效果260
12.2产品成本变动趋势跟踪分析261
12.3产品成本结构跟踪分析262
第13章预算执行情况滚动跟踪分析模板
13.1基础数据265
13.2模板效果266
13.3模板制作266
1．建立滚动汇总表266
2．绘制动态分析图267
第14章薪酬滚动汇总分析模板
14.1基础薪酬表270
14.2制作员工的全年工资单270
14.3制作部门工资清单271
14.4制作部门工资项目分析表272
14.5制作全部工资项目分析表273
14.6制作工资四分位值分析表274
1．分析指定部门的各个月的工资四分位值276
2．分析指定月份的各个部门的工资四分位值279