Excel报表一劳永逸（数据+函数+表格）

第一部分 必备知识

第1章万丈高楼平地起 2

1.1 工作表与工作簿·3

1.2 单元格·3

1.3 区域4

1.4 活动单元格4

1.5 选项卡与功能区·5

1.6 名称5

1.6.1 名称的概念及作用5

1.6.2 名称的快速创建 6

1.6.3 根据所选内容批量创建名称 6

1.6.4 名称修改与删除 7

1.7 函数7

1.7.1 函数的概念与构成7

1.7.2 如何有效地学习函数9

1.8 单元格引用方式及其区别10

1.8.1 公式计算结果为何出错10

1.8.2 如何让行号列标填充时不变化10

1.8.3 相对引用、绝对引用与混合引用11

1.8.4 公式中4种引用方式的选用与快速变换11

1.9 快速选取单元格的方法·12

1.9.1 选取连续单元格12

1.9.2 选取不连续单元格·13

1.9.3 使用名称框选取连续及不连续单元格·13

1.9.4 使用定位选取特定单元格·13

1.9.5 使用查找选取特定单元格·15

1.10 ※当前区域16

1.11 ※R1C1引用样式17

1.12 ※关于名称的几点补充17

1.12.1 名称的作用·17

1.12.2 使用功能区命令定义名称18

1.12.3 名称的两种作用范围19

1.12.4 给名称起名字注意事项19

1.12.5 查看已定义的名称·20

1.12.6 名称管理器高级应用技巧20

1.12.7 名称相同时的处理规则21

1.12.8 名称定义中的相对引用与绝 对引用·22

1.12.9 将名称应用到公式中22 ...

第二部分采集数据

第2章设计Excel数据采集模板26

2.1搞懂Excel中的数据类型27

2.2揭开日期类型的真面目·27

2.3各种类型的数据如何识别28

2.4利用数据验证限定数据符合要求29

2.4.1限定输入数值型字段·30

2.4.2限定输入文本型字段·31

2.4.3限定日期与时间型字段31

2.4.4制作下拉框进行选择·32

2.4.5※限定不能重复输入·33

2.5为模板添加保护措施以防修改·33

2.5.1保护工作表·34

2.5.2为不同单元格区域设定不同的修改密码35

2.5.3为工作簿加把锁35

2.6※使用IS函数判断数据类型·36

第3章数据输入技巧37

3.1身份证号码的输入方法·38

3.2数值输入技巧·38

3.2.1输入前导038

3.2.2快速输入数值末尾的多个039

3.2.3自动插入小数点39

3.3分数的输入方法40

3.4日期与时间输入方法40

3.4.1当前日期输入41

3.4.2※日期的简化输入法·41

3.4.3※几十年后两位年份怎么办·42

3.4.4时间与当前时间输入·42

3.4.5日期与时间组合输入·43

3.4.6※使用下拉框输入日期与时间43

3.5在一个单元格内输入多行内容·44

3.6使用自动下拉框输入已有数据·44

3.7填充序列与自定义序列·45

3.7.1生成1，2，3，4，…自增序号·45

3.7.2生成1，3，5，7，…等差数列·46

3.7.3生成按天递增或仅工作日的日期序列·46

3.7.4使用填充序列对话框·47

3.7.5Excel中的内置文本序列·47

3.7.6添加自定义序列48

3.8一键批量输入多个单元格48

3.9工作组模式对多张工作表批量输入49

3.10更改回车键移动方向·50

第4章获取外部数据51

4.1导入文本文件·52

4.1.1按分隔符号导入52

4.1.2按固定宽度导入53

4.2导入网页数据·54

第三部分加工整合

第5章合理规范的表格更利于分析56

5.1Excel其实只有两种表·57

5.2画蛇添足的多行标题58

5.3多此一举的小计及汇总行59

5.4不要有合并单元格59

5.5该分成多列的没拆分61

5.5.1使用分列功能拆分为多列·61

5.5.2文本截取函数LEFT/RIGHT/MID62

5.6试图用颜色区分不同类型63

5.6.1※根据颜色自动填充文本·63

5.7错误的数值格式64

5.7.1利用“转换为数字”命令·64

5.7.2使用选择性粘贴64

5.8非法或不完整的日期格式65

5.8.1修正“20070820”8位年月日格式·65

5.8.2使用DATE函数修正8位年月日格式66

5.8.3修正“2007.08.20”带小圆点格式·67

5.8.4※修正“2007.8.20”月日长度不固定的格式·67

5.8.5只有月日的日期处理方法·69

5.8.6“XXXX年XX月XX日”格式的处理方法·69

5.8.7将标准日期转换成其他格式·69

5.9数据库不要使用二维表·70

5.9.1多重合并数据透视表转换二维表70

5.9.2※使用INDEX/ROW/INT/MOD函数转换二维表·71

5.9.3※逆透视法转换二维表73

5.10其他数据及表格结构转换73

5.10.1将两列合并成一列·74

5.10.2将多列合并成一列·74

5.10.3一列里都添加上同样的文字·75

5.10.4删除重复数据保留唯一值75

5.10.5转置功能将表格横向纵向相互转换·76

5.10.6※更复杂的横向或纵向表格数据处理76

5.11神奇的快速填充77

5.12表格设计实例·78

5.12.1资产折旧表·78

5.12.2产品销量表·80

5.12.3客户维修记录表80

5.12.4木业公司工资表81

5.12.5小结：何时要用“数据库”表82

第四部分分析呈现

第6章使用函数统计报表数据84

6.1多条件记数函数COUNTIFS85

6.1.1COUNTIFS使用场景与语法解读·85

6.1.2COUNTIFS单条件计数·85

6.1.3COUNTIFS多条件计数·86

6.2多条件求和函数SUMIFS·86

6.3条件判断函数IF87

6.3.1IF使用场景与语法解读·87

6.3.2两种情况的判断88

6.3.3三种情况的判断88

6.4查值函数VLOOKUP·89

6.4.1VLOOKUP使用场景与语法解读89

6.4.2参数“查找区域”的潜规则·89

6.4.3第4个参数：精确和近似查找·90

6.4.4何时用精确，何时用近似·91

6.5函数综合应用实例92

6.6公式错误处理·94

6.6.1公式报错原因及处理对策·94

6.6.2屏蔽公式错误95

第7章强大的报表工具——数据透视表96

7.1创建透视表98

7.2三种数据类型在数据透视表中的作用99

7.3更改值汇总依据100

7.4设置数字格式101

7.5设置值显示方式101

7.6对行/列标签字段组合105

7.6.1对数值字段分区间汇总105

7.6.2对文本字段分组·106

7.6.3对日期字段按月/季度/年度汇总107

7.7调整字段顺序107

7.7.1在区域中调整字段顺序108

7.7.2在透视表中调整值字段顺序·108

7.7.3在透视表中调整标签字段顺序108

7.8插入计算字段109

7.9在透视表中排序110

7.9.1各种数据类型排序规则110

7.9.2按一列值字段排序111

7.9.3按多列值字段排序111

7.9.4对标签字段自定义排序112

7.10在透视表中筛选113

7.10.1按标签字段筛选114

7.10.2按值字段筛选115

7.10.3直接在透视表中筛选116

7.11更改报表布局117

7.12添加行/列小计与总计118

7.13更改数据源引用位置·119

7.14从数据透视表到数据透视图120

7.15数据透视表更多应用技巧120

7.15.1在现有工作表中创建透视表120

7.15.2移动透视表位置121

7.15.3透视表中双击查看数据明细121

7.15.4不让透视表显示错误值和调整列宽·121

7.15.5更改字段与区域排列方式·122

7.15.6延迟布局更新122

第8章表间黏合剂SQL123

8.1什么是SQL124

8.2在哪里使用SQL125

8.2.1使用现有连接125

8.2.2使用MicrosoftQuery126

8.3SQL访问的数据对象128

8.3.1系统表128

8.3.2命名区域（名称）128

8.4常用SQL查询语句129

8.4.1按条件查询·129

8.4.2查询其他工作簿数据130

8.4.3多表合并查询131

8.4.4多表关联查询131

8.5SQL小结133

第9章报表格式与查看·134

9.1复制并粘贴格式和列宽135

9.2运用单元格样式统一格式135

9.3套用表格格式及自定义表样式136

9.4快速更改数据透视表样式136

9.5设置工作表背景137

9.6为报表添加条件格式137

9.6.1不同数据突出显示为不同颜色138

9.6.2选取数据TOP10%或最大/最小N项139

9.6.3用数据条长短直观表示数据大小·139

9.6.4色阶与图标集140

9.6.5设置合同到期前30天变色提醒140

9.6.6条件格式优先级与清除142

9.7小而美的迷你图142

9.7.1什么是迷你图142

9.7.2创建迷你图·142

9.8窗口界面自定义143

9.8.1自定义快速访问工具栏143

9.8.2创建个性化选项卡144

9.8.3快速隐藏或显示功能区144

9.8.4不显示网格线、标题和编辑栏145

9.8.5不显示多余的行和列145

9.8.6隐藏工作表标签与滚动条·146

9.9报表查看技巧146

9.9.1冻结窗格固定住标题行146

9.9.2快速调整工作表显示比例·147

9.9.3窗口拆分与重排·147

9.9.4报表导航系统148

第五部分一劳永逸

第10章一劳永逸的报表制作秘诀150

10.1在函数报表里多维查看152

10.1.1按产品查看报表152

10.1.2日期函数EOMONTH153

10.1.3按年份及月份查看报表·153

10.2函数报表自动适应变化154

10.2.1自动包含新增的行记录·155

10.2.2生成精准的年份列表155

10.2.3动态区域引用函数OFFSET·156

10.2.4制作动态精准年份下拉框·157

10.3数组公式入门158

10.3.1数组公式的概念与输入方法158

10.3.2数组公式初体验159

10.3.3数组公式的计算过程159

10.3.4数组公式让VLOOKUP逆向和多列查找161

10.3.5数组公式求绩效总分161

10.3.6位置检索函数MATCH162

10.3.7※自动提取某列内容的不重复列表·163

10.3.8※制作动态精准产品下拉框165

10.3.9※生成动态精准销售员名单165

10.4表格应用技巧167

10.4.1表格最有用的几个功能·167

10.4.2如何创建表格并命名170

10.4.3使用表格公式自动适应变化170

10.4.4※表格中的行列操作172

10.4.5※调整表格大小173

10.4.6※让表格不自动扩展和填充公式·173

10.4.7※将表格转换为单元格区域174

10.5切片器对透视表报表多维查看174

10.6透视表报表如何适应变化175

10.6.1定义名称实现动态数据源·176

10.6.2表格为透视表提供动态数据源176

10.6.3刷新数据透视表176

10.6.4超强组合“表格 数据透视表 切片器”177

10.7报表自动化中的实用技巧178

10.7.1查找数据时如何适应列顺序的调整·178

10.7.2间接引用INDIRECT函数·179

10.7.3制作二级动态关联下拉框·180

10.7.4获取当前工作表与工作簿名181

10.7.5生成所有工作表列表182

第11章让图表动起来183

11.1动态图表原理及制作思路185

11.2利用数据验证 VLOOKUP函数制作动态图表185

11.3显示表单控件186

11.4利用下拉框控件 INDEX函数制作动态图表187

11.5图表自定义动态标题189

11.6使用名称制作动态图表189

11.7使用更多表单控件190

11.7.1复选框控件191

11.7.2数值调节按钮控件·191

11.7.3列表框控件192

11.7.4单选按钮控件192

11.7.5滚动条控件193

11.8按部门查看最近N个月销量趋势图193

11.9交互式数据透视图197

第12章终极核武器VBA开发198

12.1宏与VBA开发200

12.1.1什么是“宏”200

12.1.2宏不是Excel特有200

12.1.3VBA开发环境201

12.1.4如何运行宏202

12.1.5为工程设置密码保护203

12.2VBA基础语法203

12.2.1数据类型·204

12.2.2运算符·204

12.2.3变量与常量204

12.2.4自定义函数并在工作表中调用205

12.2.5为自定义函数定义参数·205

12.3VBA流程控制206

12.3.1分支语句·206

12.3.2循环语句·207

12.3.3错误处理语句208

12.4ExcelVBA常用对象209

12.4.1对象、属性和方法·209

12.4.2Application对象210

12.4.3Workbooks集合对象211

12.4.4Workbook对象·211

12.4.5Worksheets集合对象·212

12.4.6Worksheet对象·212

12.4.7Range对象213

12.5使用“录制宏”快速学习214

12.6VBA开发实例215

12.6.1刷新数据透视表215

12.6.2隐藏分组框控件的边框线·215

12.6.3去除所有工作表中的公式·216

12.6.4批量创建工作表216

12.6.5合并多表数据并生成图表·217

12.6.6桌牌批量打印工具·217

12.6.7工资表自动生成系统218

12.7VBA小结218

后记·221 2100433B

Excel 是职场办公人员的必备工具，然而大多数用户只会进行较基础的操作，在制作报表时常常采用手工操作等低效的方法，加班加点、费时费力。究其原因，一是不理解 Excel 的规则和理念，二是缺乏相应的技巧。 本书开篇即阐明制作报表的正确步骤。接下来按此步骤介绍大量的技巧：在采集数据阶段，如何设计规范的数据表格；在加工整合阶段，如何进行数据表格格式转换及查漏补缺；在分析呈现阶段，如何应用常用的统计函数及数据透视表，以及使用 SQL 技巧。在本书的最后，系统总结了“一劳永逸”报表的制作理念和具体方法。

《Excel 2007函数、图表与数据分析(第2版)》主要介绍目前最流行的电子表格制作和数据处理软件Excel 2007在函数、图表与数据分析方面的功能与应用，主要内容包括Excel 2007快速入门、认识公式与函数、文本与逻辑函数、数学与三角函数、日期与时间函数、信息和工程函数、引用和查询函数、统计函数、财务函数、将数据形象化——图表、数据透视图和数据透视表、轻轻松松管理数据、数据分析很简单等，最后通过两个实例综合练习Excel 2007的各种操作，提高读者的综合应用能力。《Excel 2007函数、图表与数据分析(第2版)》内容深入浅出，以“小魔女”从对Excel 2007一窍不通到能熟练应用Excel的函数、图表与数据分析功能完成各种工作簿的计算为线索贯穿始终，引导初学者学习。《Excel 2007函数、图表与数据分析(第2版)》选择了工作和生活中大量的应用实例，以帮助读者掌握Excel 2007，并在每章后面附有大量丰富生动的练习题，以检验读者对本章知识点的掌握程度，达到巩固所学知识的目的。《Excel 2007函数、图表与数据分析(第2版)》定位于Excel的初级用户，适用于在校学生、有一定基础的Excel用户、工作中涉及大量计算的财务统计人员、教师、各种电脑培训班学员及不同年龄阶段喜欢Excel的电脑爱好者。

本书共分为13章，内容包括Excel 2010的基本操作、录入与编辑数据、工作表与工作簿的操作、表格格式的设置与套用、插入与编辑图形对象、公式的应用、函数的应用、创建与编辑图表、数据透视表与透视见图、排序筛选与分类汇总、数据分析工具的应用、页面设置与打印、与外部数据的协作等。

本书系统全面地介绍了Excel 2007应用的相关知识，内容包括Excel的基本操作、表格数据的录入与编辑、表格对象的编辑与格式设置、公式与函数的使用等，并以实例的形式介绍了Excel的典型应用。