本书共15章，包含251个疑难案例，每个疑难案例均给出一个或多个解决方案，并融入一个或多个知识点讲解，贴切实际，深入浅出；在解决问题的，引导读者学习数据透视表套用的原理和知识扩展。

本书适合各个层次的Excel用户，既可以作为初学者的入门教材，又可作为中、高级用户的参考手册。书中案例多数来源于各行各业中的实际案例，读者可以直接套用于实际工作中。阅读本书后，可以帮助读者快速提高数据处理能力和工作效率。

自 序 寻，是一种尺度。古时八尺为一寻，王安石诗云“飞来峰上千寻塔，闻说鸡鸣见日升”，用以形容飞来峰上塔之极高。 寻，是一种技巧。现今的网路为自学者提供了非常巨大的资源平台，但缺乏技巧的查询和求助，往往“众里寻他千百度”却难有“蓦然回首”的顿悟。而善用关键字搜寻，则常可使人体会到“灯火阑珊”之美景。 寻，是一种追求。网路上活跃着这幺一群人，他们曾经也是懵懂的菜鸟。

第1章 认识数据透视表 1

第1节 数据透视表技巧 1

疑难1 如何创建一个简单数据透视表 1

※ 插入数据透视表 ※ 2

※ 利用嚮导创建数据透视表 ※ 3

※ 数据透视表的发展历程 ※ 4

疑难2 如何用含有合併单元格的数据来创建数据透视表 4

※ 培养良好的数据录入习惯 ※ 6

疑难3 如何使用推荐的数据透视表 7

※ 使用推荐的数据透视表 ※ 8

疑难4 如何重命名数据透视表 8

※ 重命名数据透视表 ※ 9

疑难5 如何移动数据透视表位置 9

※ 移动数据透视表 ※ 10

疑难6 如何複製数据透视表 10

※ 複製数据透视表 ※ 11

疑难7 如何删除数据透视表 12

※ 删除数据透视表 ※ 12

疑难8 如何创建非共享快取的数据透视表 12

※ 创建非共享快取的数据透视表 ※ 13

※ 共享快取与非共享快取的区别 ※ 15

第2节 数据透视表结构 15

疑难9 如何汇总各销售经理各区域销售总额 16

※ 创建多条件的数据透视表 ※ 16

※ “数据透视表栏位”窗格说明 ※ 17

疑难10 如何显示/隐藏数据透视表栏位窗格 17

※ 显示/隐藏数据透视表栏位窗格 ※ 18

疑难11 如何隐藏数据透视表中行栏位的“+/-”按钮 19

※ 显示/隐藏“+/-”按钮 ※ 19

疑难12 如何在数据透视表栏位窗格中显示更多栏位 20

※ 改变数据透视表栏位窗格布局 ※ 21

疑难13 如何隐藏数据透视表的栏位标题和筛选按钮 22

※ 隐藏栏位标题和筛选按钮 ※ 22

疑难14 如何升序排列栏位列表中的栏位 24

疑难15 如何快速选取所有的标籤并标注为灰色底纹 25

疑难16 如何快速选定数据透视表的汇总行并添加灰色底纹 27

※ 快速选定数据透视表元素 ※ 28

※ 选定行标籤所在项父级的所有数据 ※ 29

疑难17 如何得到数据透视表中某汇总行的明细数据 29

※ 显示明细数据 ※ 30

第3节 练习与思考 31

第2章 布局和列印 32

第1节 拖动栏位 32

疑难18 如何在数据透视表中使用滑鼠拖放 32

※ 设定经典数据透视表布局 ※ 33

疑难19 如何将值栏位从行区域移到列区域 34

※ 拖动值栏位 ※ 34

疑难20 如何将行栏位中的项目拖动到指定行显示 36

※ 改变行栏位项目显示顺序 ※ 36

疑难21 如何快速拖动改变行栏位的排列顺序 37

※ 改变行栏位排列顺序 ※ 37

疑难22 如何删除数据透视表中的某个栏位 38

※ 移除栏位 ※ 39

第2节 调整布局 40

疑难23 如何隐藏数据透视表栏位的分类汇总 40

※ 隐藏所有栏位的分类汇总 ※ 40

※ 在底部/顶部显示分类汇总 ※ 41

※ 添加或隐藏某个栏位的分类汇总 ※ 41

疑难24 如何为一个栏位添加多种分类汇总方式 43

※ 一个栏位的多种分类汇总 ※ 43

疑难25 如何禁用数据透视表的总计行/列 44

※ 禁用总计行和总计列 ※ 45

※ 删除总计行或总计列 ※ 46

※ 总计行与列总计、总计列与行总计的关係 ※ 46

疑难26 如何在数据透视表顶部显示列总计数据 46

※ 在顶部显示列总计 ※ 47

疑难27 如何以表格形式显示数据透视表 48

※ 以表格形式显示报表 ※ 48

※ 报表布局的三种显示形式及其区别 ※ 50

疑难28 如何调整压缩形式显示下的缩进字元数 51

※ 设定行标籤字元缩进量 ※ 51

疑难29 如何重複显示行栏位的项目标籤 52

※ 重複所有项目标籤 ※ 52

※ 重複活动栏位项目标籤 ※ 53

※ 重複所有项目标籤与重複活动栏位项目标籤的区别 ※ 53

疑难30 如何在数据透视表项目间插入空行 54

※ 在每个项目后插入空行 ※ 54

※ 在指定项目标籤后插入空行 ※ 54

※ 删除每个项目后的空行 ※ 56

疑难31 如何在组的顶部显示分类汇总 56

※ 在组的顶部显示分类汇总 ※ 56

疑难32 如何在数据透视表中使用合併单元格标誌 57

※ 合併居中显示项目标籤 ※ 57

疑难33 如何水平并排显示报表筛选区域的栏位 58

※ 水平并排显示报表筛选区域栏位 ※ 59

※ 设定每行/列报表筛选栏位的数量 ※ 59

疑难34 如何设定手动更新数据透视表的布局 60

※ 推迟布局更新 ※ 61

※ “推迟布局更新”的影响 ※ 61

疑难35 如何在受保护工作表中改变数据透视表布局 61

※ 在受保护工作表中允许使用数据透视表和数据透视图 ※ 62

第3节 列印 63

疑难36 如何设定重複列印标题行 63

※ 设定列印标题行 ※ 63

※ 设定列印顶端标题行 ※ 64

※ 数据透视表选项与页面设定在设定列印标题行时的区别 ※ 64

疑难37 如何按公司名称分页列印销售业绩汇总表 65

※ 行栏位分类项目分页列印 ※ 65

疑难38 如何按地区分页列印销售人员的业绩报表 66

※ 按页栏位数据项分页列印 ※ 67

第4节 练习与思考 68

第3章 设定数据透视表格式 69

第1节 套用样式 69

疑难39 如何为数据透视表套用样式 69

※ 自动套用数据透视表样式 ※ 70

※ 套用表格格式 ※ 70

※ 启用/关闭实时预览 ※ 71

※ 套用并清除格式与套用并保留格式的区别 ※ 71

疑难40 如何自动设定数据透视表的框线 72

※ 设定数据透视表框线 ※ 72

疑难41 如何设定镶边行的数据透视表样式 73

※ 镶边行与镶边列 ※ 74

疑难42 如何新建数据透视表样式 74

※ 新建数据透视表样式 ※ 74

※ 自定义数据透视表样式中格式的优先顺序 ※ 76

※ 行副标题与第一列的区别 ※ 76

※ 第一行条纹、第二行条纹与镶边行的关係 ※ 76

※ 清除数据透视表元素的格式设定 ※ 77

疑难43 如何修改数据透视表样式 78

※ 修改数据透视表样式 ※ 78

※ 删除自定义数据透视表样式 ※ 80

第2节 自定义格式 80

疑难44 如何隐藏数据透视表中的错误值 81

※ 设定错误值显示为空 ※ 81

疑难45 如何将数据透视表中的空单元格显示为“-” 82

※ 设定空单元格显示为“-” ※ 82

※ 取消空单元格自定义显示 ※ 84

※ 典型的自定义单元格格式 ※ 84

疑难46 如何去掉数据透视表中的“（空白）”数据 85

※ 查找替换去除“（空白）” ※ 85

※ 重命名去除“(空白)” ※ 86

※ 自动更正去除“（空白）” ※ 86

疑难47 如何使栏位标题不显示“求和项”等统计标识 87

※ 查找替换去除“求和项” ※ 88

※ 重命名去除“求和项” ※ 88

※ 计数项与求和项 ※ 88

疑难48 如何将指定项下所有数据标记为红色 89

※ 突出显示指定项所有数据 ※ 90

疑难49 如何将测量平均值保留三位小数 90

※ 四捨五入保留两位小数 ※ 91

※ 单元格格式与数字格式的区别 ※ 92

疑难50 如何将不达标数据显示为红色“不达标” 92

※ 标记不达标数据 ※ 93

※ 在单元格格式中可以设定哪些颜色 ※ 93

第3节 条件格式 93

疑难51 如何套用数据条直观显示数据 94

※ 通过“格式按钮”为数据透视表套用数据条 ※ 94

※ “格式选项”按钮的作用 ※ 95

※ 通过“条件格式”为数据透视表套用数据条 ※ 95

疑难52 如何突出显示最高及最低的整行数据 97

※ 突出显示最大值/最小值所在数据行 ※ 97

※ 以不同颜色突出显示最大值与最小值 ※ 98

疑难53 如何突出显示销量增长最多的销售员 99

※ 突出显示销量增长最多的销售员 ※ 99

疑难54 如何按班级交替填充背景色 100

※ 按班级交替填充背景色 ※ 101

※ 条纹尺寸与条件格式 ※ 101

疑难55 如何将百分成绩显示为等第成绩 102

※ 按等级级别显示成绩 ※ 102

※ 条件格式的优先权 ※ 104

※ 调整条件格式的优先权 ※ 104

第4节 练习与思考 104

第4章 数据透视表排序 105

第1节 自动排序 105

疑难56 如何设定数据透视表的行栏位降序排序 105

※ 按行栏位项目降序排序 ※ 106

※ 数据透视表中行栏位排序的几种方法 ※ 106

疑难57 如何按分类汇总值降序排序 107

※ 按分类汇总升序排序 ※ 107

疑难58 如何设定多个值栏位降序排序 108

※ 多关键字排序 ※ 109

※ 多关键字排序的先后顺序 ※ 110

※ “每次更新报表时自动排序”複选框的作用 ※ 110

疑难59 如何按多个行栏位排序 110

※ 多个行栏位按值栏位排序 ※ 111

第2节 自定义排序 112

疑难60 如何手动排序行栏位的项 112

※ 手动排序栏位项目 ※ 113

疑难61 如何按笔划升序排序行栏位项目 114

※ 按笔划升序排序 ※ 114

※ 笔划排序规则 ※ 114

疑难62 如何对数据透视表中的局部数据进行排序 115

※ 局部排序 ※ 115

疑难63 如何按行降序排列不合格分类数量 116

※ 按行从左到右排序 ※ 116

疑难64 如何按照学历高低排序 117

※ 添加自定义序列排序 ※ 118

※ 启用排序时使用自定义列表 ※ 119

第3节 练习与思考 119

第5章 数据透视表筛选 120

第1节 自动筛选 120

疑难65 如何筛选成绩大于或等于60的学生名单 120

※ 值筛选及格名单 ※ 121

※ “值筛选”的筛选条件 ※ 121

※ 在数据透视表栏位列表任务窗格中设定值筛选 ※ 122

※ 清除“值筛选”的几种方法 ※ 122

※ 为什幺数据透视表有些新的筛选功能不可用 ※ 123

疑难66 如何获得高于平均值的销量数据列表 124

※ 自动筛选高于平均值数据 ※ 124

※ “值筛选”与数值区域执行自动筛选的区别 ※ 125

疑难67 如何筛选不同产地单价最高的2种商品 125

※ 筛选前N个最大值 ※ 125

※ 筛选的排位规则 ※ 126

※ 项、百分比、求和 ※ 126

疑难68 如何筛选包含“桃”字的商品信息 127

※ 通过标籤筛选包含指定字元的数据 ※ 127

※ 通过搜寻筛选器筛选包含指定字元的数据 ※ 128

※ 标籤筛选与搜寻筛选器筛选的区别 ※ 129

※ 筛选条件中的通配符 ※ 129

疑难69 如何筛选本周的销量数据 129

※ 通过日期筛选本周数据 ※ 130

疑难70 如何筛选销售额大于10 000的指定商品信息 131

※ 同一栏位的多重筛选 ※ 131

※ 每个栏位允许多个筛选 ※ 133

※ 搜寻筛选器中的4种筛选方式 ※ 133

疑难71 如何为同一栏位设定多个筛选条件 133

※ 同一栏位的叠加筛选 ※ 133

※ 同一栏位执行多次筛选 ※ 134

疑难72 如何对行列栏位执行筛选 134

※ 多栏位并列条件筛选 ※ 135

疑难73 如何按省份分页显示销售数据 135

※ 显示报表筛选页 ※ 136

疑难74 如何在筛选状态下包含数据源中的新增信息 137

※ 在手动筛选中包含新项目 ※ 137

疑难75 如何在栏位筛选状态下显示无数据的项目 138

※ 显示无数据项目 ※ 139

疑难76 如何隐藏栏位下拉列表中多余的项目 140

※ 隐藏栏位下拉列表中多余项目 ※ 140

第2节 切片器筛选 141

疑难77 如何通过切片器筛选分公司的销售报表 141

※ 插入切片器 ※ 141

※ 删除切片器 ※ 142

※ 隐藏切片器 ※ 143

※ 调整切片器大小 ※ 143

疑难78 如何设定切片器的字型格式 144

※ 修改切片器样式 ※ 145

※ 如何新建切片器样式 ※ 147

※ 切片器元素中各部分含义 ※ 147

疑难79 如何隐藏切片器的标题 149

※ 不显示切片器页眉 ※ 149

疑难80 如何通过切片器筛选多项 150

※ 切片器筛选多个项目 ※ 150

※ 切片器与筛选器的优缺点 ※ 150

※ 在切片器中选择项目的方法 ※ 151

疑难81 如何通过切片器筛选两个数据透视表 151

※ 通过切片器设定报表连线 ※ 152

※ 通过数据透视表设定筛选器连线 ※ 153

疑难82 如何用切片器实现多维度分析 153

※ 多个切片器联动筛选 ※ 154

疑难83 如何在被保护的工作表中使用切片器 154

※ 在受保护的工作表中使用切片器 ※ 155

疑难84 如何在切片器中隐藏数据源中删除的项目 155

※ 切片器不保留数据源删除的项目 ※ 156

※ 隐藏没有数据的项 ※ 156

疑难85 如何使交叉筛选的切片器不显示无效选项 157

※ 隐藏切片器的无效选项 ※ 159

疑难86 为什幺删除切片器后还保留其筛选结果 159

※ 删除切片器清除数据透视表筛选 ※ 160

疑难87 如何通过日程表筛选指定年份的数据 161

※ 插入日程表 ※ 161

※ 日程表与自动分组功能区别 ※ 162

※ 格式化日程表 ※ 163

第3节 练习与思考 163

第6章 数据透视表项目分组 164

第1节 自动分组 164

疑难88 如何按年龄分组统计员工人数 164

※ 等距步长自动组合数值型数据项 ※ 165

疑难89 如何按小时分组汇总行车数量 165

※ 按小时自动分组 ※ 166

疑难90 如何按周分组汇总产量 166

※ 按周自动分组 ※ 167

※ 改变周起始日 ※ 168

疑难91 如何按“旬”分组汇总数据 168

※ 按旬自动分组 ※ 168

※ 不等距步长自动分组 ※ 169

疑难92 如何按年、季度、月份分组汇总销售额 169

※ 按年、季、月自动分组 ※ 169

疑难93 为什幺提示“选定区域不能分组” 170

※ 选定区域不能分组的原因 ※ 173

第2节 手动组合 174

疑难94 如何製作账龄分析表 174

※ 不等距步长手动组合数值型数据项 ※ 174

※ 组选择与组栏位的区别 ※ 175

疑难95 如何按半年汇总产量数据 175

※ 按半年分组汇总 ※ 176

疑难96 如何按学年汇总校运动会报名人数 177

※ 手动组合文本型数据项 ※ 177

疑难97 如何按行政区域汇总销量 178

※ 按关键字分组汇总 ※ 178

疑难98 如何查看美洲入境游人数的分布 180

※ 全局取消组合与局部取消组合 ※ 181

第3节 练习与思考 181

第7章 更新数据源 182

第1节 刷新 182

疑难99 如何手动刷新数据透视表 182

※ 手动刷新数据透视表 ※ 183

疑难100 如何在打开工作簿时自动刷新数据透视表 184

※ 打开档案自动刷新数据 ※ 184

疑难101 如何在更新数据透视表时保持原格式和列宽不变 185

※ 刷新时保留原有格式和列宽 ※ 185

疑难102 如何批量刷新数据透视表 186

※ 批量刷新数据透视表 ※ 186

※ “数据透视表工具”的“全部刷新”与“数据”选项卡的“全部刷新”

有何区别※ 187

疑难103 如何定时刷新数据透视表 187

※ 定时刷新数据透视表 ※ 188

疑难104 为什幺刷新数据透视表时数据会丢失 189

疑难105 如何清除栏位下拉列表中已删除数据源的项目 190

※ 不保留从数据源中删除的项目 ※ 191

疑难106 如何更改现有数据透视表的数据源 192

※ 更改数据透视表数据源 ※ 192

第2节 动态数据源 193

疑难107 如何利用定义名称创建动态数据透视表 193

※ 定义名称法创建动态数据透视表 ※ 193

※OFFSET函式语法※ 195

※ COUNTA函式语法 ※ 196

疑难108 如何利用表功能创建动态数据透视表 196

※ 利用表功能创建动态数据透视表 ※ 196

疑难109 如何通过编辑OLE DB查询创建动态数据透视表 198

※ 编辑OLE DB查询创建动态数据透视表※ 198

第8章 数据透视表的计算 201

第1节 值汇总方式 201

疑难110 如何按账龄汇总客户的实际应收款 201

※ 条件求和汇总 ※ 201

※ 数据透视表对栏位类型的自动识别 ※ 204

※ 数据透视表的计算原理 ※ 204

疑难111 如何按性别和学历统计各部门人数 204

※ 条件计数 ※ 205

※ 值栏位的汇总方式 ※ 206

疑难112 如何统计各班级平均分与最高分 206

※ 条件极值均值 ※ 207

※ 对同一栏位使用多种汇总方式 ※ 208

※ 改变值栏位汇总方式的5种方法 ※ 208

疑难113 为什幺数据透视表的汇总方式默认为计数 208

※ 更改默认汇总方式 ※ 209

第2节 值显示方式 211

疑难114 如何统计各营业部电器的销售额占比 211

※ 总计的百分比 ※ 211

※ 15种值显示方式及其功能 ※ 212

疑难115 如何统计全球手机品牌市场占有率 213

※ 列汇总的百分比 ※ 213

疑难116 如何统计各班学生男女比例 214

※ 行汇总的百分比 ※ 214

疑难117 如何计算定基动态比率 215

※ 百分比 ※ 216

疑难118 如何统计销售额占父行汇总的百分比 217

※ 父行汇总的百分比 ※ 217

※ 父行汇总百分比与列汇总百分比的异同 ※ 218

疑难119 如何统计各车型的销量占分类汇总的百分比 218

※ 父级汇总的百分比 ※ 219

疑难120 如何统计服装销量与上个月的差异 220

※ 差异 ※ 220

疑难121 如何统计商品房成交均价环比增长率 222

※ 差异百分比 ※ 222

疑难122 如何按月份累计发货金额 223

※ 按某一栏位汇总 ※ 224

疑难123 如何统计等第成绩人数累计百分比 225

※ 按某一栏位汇总的百分比 ※ 225

疑难124 如何按地区统计平板电视的销量排名 226

※ 升序排列 ※ 227

疑难125 如何按报考职位统计考生的成绩排名 228

※降序排列※ 228

疑难126 如何统计各商品在不同区域的相对重要性 229

※指数※ 230

第3节 计算栏位与计算项 231

疑难127 如何为成绩表添加总分计算栏位 231

※插入计算栏位※ 231

※ 二维表的总计列 ※ 232

疑难128如何修改数据透视表中的计算栏位公式 232

※ 修改计算栏位公式 ※ 232

※ 删除计算栏位 ※ 234

疑难129 如何按月统计各时段用电量占比 234

※ 二维表的行4总计占比 ※ 234

疑难130 如何判断应收金额与已收金额是否平账 235

※ 数据透视表中使用公式返回#VALUE!错误值的原因 ※ 237

※ 计算栏位公式可以使用哪些函式 ※ 237

疑难131 如何计算各商品的加权平均单价 238

※ 计算加权平均单价 ※ 238

疑难132 如何计算蔬菜销售的毛利 239

※ 再透视法 ※ 239

※ 计算栏位的局限性 ※ 241

疑难133 如何计算商品售罄率 242

※ 插入计算项 ※ 242

※ 计算栏位与计算项的区别 ※ 244

※ 修改计算项 ※ 244

疑难134 如何统计销售额的年增长率 244

※ 隐藏计算项的无效数据行 ※ 246

※ 计算项的局限性 ※ 246

疑难135 如何统计货运每日延误率及总延误率 246

※ 计算项总计不正确问题 ※ 249

※ 计算项的求解次序 ※ 249

第4节 数据透视表函式 249

疑难136 如何根据多列条件查询物品单价 249

※ 动态查询数据透视表数据 ※ 250

※ GETPIVOTDATA函式的3种语法结构 ※ 250

※ 禁用自动创建GETPIVOTDATA 公式 ※ 251

※ GETPIVOTDATA函式特点 ※ 252

疑难137 如何获取自定义分类汇总数据 252

※ 查询自定义分类汇总数据 ※ 252

疑难138 如何根据现有数据透视表重置报表格式 253

※ 量身定做数据报表 ※ 253

第5节 练习与思考 254

第9章 多重合併区域数据透视表 255

第1节 多重合併计算 255

疑难139 如何创建单个自定义页栏位的数据透视表 255

※ 单页栏位多表合併数据 ※ 256

※ 使用“多重合併计算数据区域”创建数据透视表的工作原理 ※ 257

疑难140 如何创建多个自定义页栏位的数据透视表 258

※ 多页栏位多表合併数据 ※ 259

疑难141 如何比较两张表格相同商品的差异 260

※ 多数据合併比较差异 ※ 260

疑难142 如何合併不同工作簿的新增客户报表数据 262

※ 多工作簿合併数据 ※ 262

疑难143 如何按店铺合併不同的指标数据 264

※ 多表合併不同指标数据 ※ 264

疑难144 如何统计不同员工调研结果 265

※ 多表合併不同指标数据 ※ 265

※ 使用“多重合併计算数据区域”创建不具有页栏位的数据透视表 ※ 266

疑难145 如何按行总计百分比显示二维表监测数据占比 267

※ 解决二维表行汇总百分比无法显示的问题 ※ 267

疑难146 如何将二维錶转换为一维数据列表 269

※ 二维錶转换为一维表 ※ 269

疑难147 如何创建动态多重合併计算数据区域数据透视表 270

※ 基于“表”创建的数据透视表 ※ 271

※ 基于定义名称创建动态数据透视表 ※ 272

疑难148 如何创建多个行栏位的多重合併区域数据透视表 272

第2节 练习与思考 274

第10章 获取外部数据创建数据透视表 275

第1节 使用多维数据集创建数据透视表 275

疑难149 如何使用OLAP多维数据集创建数据透视表 275

※ 使用OLAP多维数据集创建数据透视表 ※ 276

※ 多维数据集模型 ※ 277

※ OLAP与非OLAP源数据之间的功能差异 ※ 278

※ 维度的展开与摺叠 ※ 279

※ 向上/向下钻取 ※ 280

※ 快速浏览 ※ 281

疑难150 如何创建脱机多维数据集 281

※ 创建脱机多维数据集 ※ 282

疑难151 如何利用脱机多维数据集创建数据透视表 284

※ 利用脱机多维数据集创建数据透视表 ※ 284

疑难152 如何利用多维数据集函式创建报表 286

※ 将数据透视錶转换为公式 ※ 286

※ 利用多维数据集函式创建自定义报表 ※ 287

※ 多维数据集函式语法 ※ 288

※ 多维数据集函式列表 ※ 289

第2节 使用其他外部数据创建数据透视表 289

疑难153 如何从Access资料库导入数据创建数据透视表 289

※ 导入Access数据表创建数据透视表 ※ 290

疑难154 如何利用文本档案创建数据透视表 291

※ 利用文本档案创建动态数据透视表 ※ 292

※ schema.ini档案详解 ※ 295

疑难155 如何连线SQL Server资料库创建数据透视表 296

※ 连线SQL Server资料库创建数据透视表 ※ 296

疑难156 如何断开数据透视表的外部数据源连线 298

※ 断开数据透视表与外部数据源的连线 ※ 298

第11章 数据透视表与SQL 300

第1节 SQL语法基础 300

疑难157 如何查询导入外部数据 300

※ 打开OLE DB视窗编写SQL语句 ※ 301

※ 什幺是SELECT子句 ※ 302

※ 如何查询单列或多列记录 ※ 302

疑难158 如何在SQL里面使用单元格区域 303

※ 选定指定单元格区域的数据作为查询数据源表 ※ 304

疑难159 如何使用列别名把英文栏位转换为中文栏位 305

※ 如何使用列别名 ※ 307

疑难160 如何查询上网流量超过60M的记录 308

※ WHERE条件查询子句 ※ 309

※ 带有括弧的栏位名表示方式 ※ 309

※ 大于、小于等运算 ※ 310

疑难161 如何查询等级在B至E之间的记录 310

※ 模糊匹配模式中字元区间的表示 ※ 312

疑难162 如何查询所有姓“林”的员工的记录 312

※ 运用LIKE进行模糊匹配 ※ 314

※ 模糊匹配中不确定长度的字元表示 ※ 314

※ 模糊匹配模式中长度为1的字元表示 ※ 314

疑难163 如何计算学生总成绩并按总成绩降序排列 314

※ 栏位的四则运算 ※ 316

※ 利用ORDERBY子句对记录进行排序 ※ 316

※ SQL查询机制步骤详解 ※ 316

疑难164 如何查询满足申请租赁型经适房条件的人员 316

※ 满足多个条件的查询 ※ 318

※ 满足多个条件之一的查询 ※ 318

疑难165 如何分离籍贯栏位中的城市和区县 319

※ 拆分字元串 ※ 320

※ INSTR函式的使用 ※ 320

※ LEFT函式的使用 ※ 320

※ RIGHT函式的使用 ※ 321

疑难166 如何通过多条件分组统计数据 321

※ AVG函式在SQL语句中的使用 ※ 322

※ SUM函式在SQL语句中的使用 ※ 323

※ GROUP BY子句的使用 ※ 323

第2节 利用OLE DB结合SQL创建数据透视表 323

疑难167 如何通过OLE DB来创建动态的数据透视表 324

※ 使用SQL语句创建动态的数据透视表 ※ 324

疑难168 如何汇总同一个工作簿中多个工作表的数据 326

※ 同一工作簿中多工作表的数据汇总 ※ 326

※ UNION ALL的使用 ※ 327

※ UNION ALL与UNION的区别 ※ 328

疑难169 如何汇总同一个工作簿中不同区域的数据 328

※ 同一个工作簿内不同工作表中不同区域的数据汇总 ※ 330

疑难170 如何汇总不同工作簿中不同工作表的数据 330

※ 不同工作簿中不同工作表的数据汇总 ※ 332

疑难171 如何汇总列数不等的多个工作表数据 333

※ 汇总列数不等的多个工作表数据 ※ 334

※ 0与Null的区别 ※ 335

疑难172 如何通过关联不同工作表的商品代码汇总销售金额 335

※ 工作表名称自定义别名 ※ 336

※ 多对一连线两个工作表 ※ 337

疑难173 如何连线两个以上工作表来创建数据透视表 337

※ 关联两个以上的工作表创建数据透视表 ※ 338

疑难174 如何创建进销存报表的数据透视表 339

※ 创建进销存的动态数据透视表 ※ 340

疑难175 如何用数据透视表创建日曆 341

※ 获取日期的年、月、日、星期 ※ 342

※ DatePart函式套用 ※ 343

疑难176 如何将包含空格的借方和贷方金额相减求余额 343

※ IsNull函式的使用 ※ 344

※ IIF函式的使用 ※ 345

疑难177 如何修改OLE DB查询路径 345

第3节 结合Microsoft Query创建数据透视表 347

疑难178 如何利用Microsoft Query汇总单个数据表 347

※ 利用Microsoft Query汇总单个数据表 ※ 348

※ 如何解决数据源无可见表的问题 ※ 351

疑难179 如何利用Microsoft Query统计不重複数据个数 352

※ 利用Microsoft Query统计不重複值个数 ※ 353

※ SELECT DISTINCT语句筛选不重複记录 ※ 356

疑难180 如何利用Microsoft Query汇总多个数据表 356

※ 利用Microsoft Query汇总多个工作表 ※ 357

疑难181 如何利用Microsoft Query关联汇总多个工作表 361

※ 左连线原理说明 ※ 365

※ 右连线原理说明 ※ 365

※ 内连线原理说明 ※ 366

疑难182 如何修改“Microsoft Query”查询的连线路径 367

※ 修改“Microsoft Query”查询的连线路径 ※ 367

第4节 练习与思考 369

第12章 数据透视表与数据模型 370

第1节 使用内置的数据模型创建数据透视表 370

疑难183 如何使用数据模型创建销售额汇总报表 370

※ 使用数据模型创建数据透视表 ※ 371

※ 数据模型及其特点 ※ 371

疑难184 如何统计谘询师接诊顾客的不重複数量 372

※ 非重複计数 ※ 372

疑难185 如何让分类汇总值不随筛选而改变 373

※ 汇总值包含筛选项 ※ 375

疑难186 如何在左侧显示分类汇总 376

※ 创建集 ※ 376

疑难187 如何汇总比较计画与实际销售额 378

※ 创建一对多的表关係 ※ 378

※ 关係 ※ 381

※ 列和键 ※ 382

※ 表之间的多个关係 ※ 382

※ 表关係的必要条件 ※ 382

※ 表关係的局限性 ※ 382

疑难188 如何导入多个表创建数据透视表 383

※ 导入多个表 ※ 383

※ 管理关係 ※ 385

疑难189 如何连线Windows Azure Marketplace源创建数据透视表 385

※ 连线Windows Azure Marketplace源创建数据透视表 ※ 386

第2节 使用PowerPivot创建数据模型 389

疑难190 如何载入PowerPivot For Excel 2013 390

※ 载入PowerPivot For Excel 2013 ※ 390

※ 什幺是PowerPivot For Excel 2013 ※ 391

※ PowerPivot与Excel的区别 ※ 391

疑难191 如何使用连结表向PowerPivot添加数据 392

※ 创建连结表数据模型 ※ 392

※ 连结表的特点 ※ 393

疑难192 如何複製并贴上数据到PowerPivot 393

※ 複製贴上数据创建PowerPivot表 ※ 394

※ 贴上数据的限制 ※ 395

※ PowerPivot表与Excel工作表之间的差异 ※ 395

疑难193 如何将Access资料库的表导入PowerPivot 396

※ 使用“表导入嚮导”导入外部数据 ※ 396

疑难194 如何导入满足指定条件的数据 398

※ 在导入过程添加筛选器 ※ 398

疑难195 如何在PowerPivot中的关係图视图创建关係 401

※ 在PowerPivot中的关係图视图创建关係 ※ 401

疑难196 如何在PowerPivot中创建层次结构 402

※ 添加计算列 ※ 403

※ 创建层次结构 ※ 404

※ 计算列 ※ 405

※ 什幺是DAX表达式 ※ 406

※ DAX公式输入小技巧 ※ 406

第3节 在PowerPivot中使用数据透视表 407

疑难197 如何在PowerPivot创建数据透视表汇总销售额 407

※ 在PowerPivot创建数据透视表 ※ 407

※ PowerPivot数据透视表选项 ※ 408

疑难198 如何根据多列条件汇总比较多表数据 409

※ 添加计算列建立表关係 ※ 409

疑难199 如何计算加权毛利率 412

※ 计算加权毛利率 ※ 413

※ PowerPivot计算栏位 ※ 415

※ 编辑/删除计算栏位 ※ 416

※ 计算列与计算栏位的区别 ※ 417

疑难200 如何按部门及应发工资分段统计员工人数 417

※ 条件计数 ※ 417

疑难201 如何解决父级汇总的百分比不正确问题 419

※ 引用分类汇总值 ※ 419

疑难202 如何计算加权平均单价 421

※ 计算加权平均单价 ※ 421

疑难203 如何在数据透视表的值区域显示文本 423

※ 值区域显示文本 ※ 423

疑难204 如何筛选各商品的最近且最低单价 425

※ 筛选最近且最低单价 ※ 426

※ 逻辑运算符 ※ 428

疑难205 如何统计销售额的同比和环比增长率 428

※ 同比和环比增长率 ※ 428

※ 时间智慧型函式 ※ 431

疑难206 记账统计收支余额表 431

※ 年、月、日收支余额表 ※ 432

※ 期末余额 ※ 434

※ 期初余额 ※ 435

疑难207 如何使用KPI分析销售数据 435

※ 创建KPI ※ 435

※ PowerPivot 中的关键绩效指标 (KPI) ※ 438

第4节 练习与思考 439

第13章 数据透视表与VBA 440

第1节 VBA语法基础 440

疑难208 如何显示“开发工具”选项卡 440

※ 显示开发工具选项卡 ※ 441

疑难209 为什幺无法运行档案中的代码 441

※ 启用宏 ※ 442

※ 设定为受信任文档 ※ 443

※ 在出现安全警告时启用一次宏 ※ 443

※ 宏的4个安全等级 ※ 444

疑难210 如何执行过程 444

※ 运行宏 ※ 444

※ 运行代码的多种方式 ※ 445

疑难211 如何在立即视窗中获得代码的运算结果 447

※ 查看变数运行结果 ※ 447

※ VBA中几种视窗的作用 ※ 448

疑难212 如何统计多工作表中的报考人数 449

※ For…Next循环语句 ※ 449

※ For Each…Next循环语句 ※ 451

※ For…Next与For Each…Next区别 ※ 451

※ Do…Loop循环语句 ※ 452

疑难213 如何获得创建数据透视表的代码 453

※ 录製宏 ※ 453

※ 数据透视表常用代码解析 ※ 455

※ AddDateField与AddFields方法 ※ 456

※ 简化代码的方法 ※ 456

※ 数据透视表对象模型 ※ 457

第2节 使用VBA创建数据透视表 458

疑难214 如何基于本地数据源创建数据透视表 458

※ PivotTableWizard方法 ※ 459

疑难215 如何使用VBA创建动态数据透视表 460

※ 行列自动扩展的动态数据透视表 ※ 460

疑难216 如何创建默认汇总方式为求和的数据透视表 461

※ 默认汇总方式为求和的数据透视表 ※ 462

疑难217 如何快速汇总多个工作表 464

※ 多工作表合併计算 ※ 464

※ 二维数组创建多重合併计算区域 ※ 466

疑难218 如何创建多行栏位的多重合併计算数据透视表 466

※ 多行栏位多表合併计算 ※ 467

第3节 使用VBA运算元据透视表 470

疑难219 如何选中数据透视表的元素 470

※ 套用PivotSelect方法选取数据透视表中元素 ※ 471

疑难220 如何隐藏数据透视表中指定的栏位 473

※ 隐藏栏位 ※ 474

疑难221 如何用代码刷新数据透视表 475

※ 自动刷新数据透视表 ※ 476

疑难222 如何刷新受保护工作表中的数据透视表 478

※ 刷新受保护的数据透视表 ※ 478

疑难223 如何批量更改数据透视表的汇总方式 479

※ 批量更改汇总方式 ※ 479

疑难224 如何在数据透视表中插入切片器 480

※ 插入切片器 ※ 480

※ 获取切片器中选中的项目 ※ 482

疑难225 如何设定数据透视表不共享快取 483

※ 设定多个数据透视表不共享快取 ※ 484

※ 设定多个数据透视表共享数据快取 ※ 485

※ 新建数据透视表设定非共享快取 ※ 485

疑难226 如何更改数据透视表数值区域的数字 486

※ 启用值区域编辑功能 ※ 486

疑难227 如何在数据透视表中插入日程表 487

※ 插入日程表 ※ 487

第4节 练习与思考 488

第14章 数据透视图 489

第1节 创建数据透视图 489

疑难228 如何创建数据透视表和透视图 489

※ 创建数据透视表与透视图 ※ 490

※ 数据透视表与数据透视图的关係 ※ 491

疑难229 如何根据数据透视表创建透视图 493

※ 为现有数据透视表创建透视图 ※ 494

※ 数据透视图无法创建的图表类型 ※ 497

※ 创建数据透视图的方法比较 ※ 497

疑难230 如何使用PowerPivot创建数据透视图 498

疑难231 如何通过VBA创建数据透视图 500

※ VBA创建数据透视图 ※ 501

※ 数据透视图中数值栏位的名称列表 ※ 503

疑难232 如何改变数据透视图的图表类型 504

※ 更改数据透视图的图表类型 ※ 505

疑难233 如何更改数据透视图的显示位置 506

※ 移动数据透视图 ※ 506

※ 一次性调整数据透视图的字号 ※ 508

第2节 布局和样式 508

疑难234 如何隐藏数据透视图中的栏位按钮 508

※ 隐藏所有栏位按钮 ※ 509

※ 隐藏指定栏位按钮 ※ 509

疑难235 如何切换数据透视图中数据行/列显示 510

※ 切换数据行/列显示 ※ 511

疑难236 如何调整报表筛选栏位至透视图的图例栏位 513

※ 调整栏位布局 ※ 513

疑难237 如何使数据透视图示题与页栏位同步 515

※ 图表标题与页栏位同步 ※ 516

疑难238 如何为数据点添加数据标注 517

※ 设定数据标注 ※ 517

※ 强化的数据标籤功能 ※ 518

※ 更改数据标籤形状※ 518

第3节 典型套用案例 519

疑难239 如何通过Power View 创建互动式图表 519

※ 通过Power View创建互动式图表 ※ 519

※ 第一次使用时可能需要的解决办法 ※ 523

※ 在互动图表中设定排序 ※ 524

※ 互动式图表解释 ※ 524

疑难240 如何通过切片器显示各月销售额 524

※ 通过切片器按月份显示销售额 ※ 524

疑难241 如何製作半圆形饼图 525

※ 数据透视图中显示总计 ※ 526

疑难242 如何创建排列图 527

※ 排列图的原理及套用 ※ 529

※ 如何保存为模板 ※ 530

疑难243 如何分车型比较不同厂家的销量 531

※ 分车型比较不同厂家的销量 ※ 532

※ Power View筛选器的用法 ※ 534

※ 水平比较不同车型的销量数据 ※ 535

※ 调整萤幕中序列图显示数量 ※ 535

第4节 练习与思考 536

第15章 综合套用实践 537

第1节 汇总同期利润对比表 537

疑难244 如何準备数据源 538

※ 利用名称定义动态数据源 ※ 539

疑难245 如何创建同期利润对比报表 540

※ 多重合併计算利润表数据 ※ 540

疑难246 如何调整同期利润对比报表的项目顺序 543

※ 手动排序同期利润对比报表的项目 ※ 543

疑难247 如何计算利润率 545

※ 插入利润率计算项 ※ 545

第2节 费用差异分析报表 547

疑难248 如何按照年月汇总各项费用 548

※ 按年月汇总各项费用 ※ 548

疑难249 如何组合费用科目 550

※ 手工组合费用科目 ※ 551

疑难250 如何进行差异分析计算 552

※ 插入差异和增长比计算项 ※ 553

※ 利用公式计算总计 ※ 554

第3节 收发存汇总报表 555

疑难251 如何利用SQL创建收发存报表 556

※ 利用SQL创建收发存报表 ※ 556