Excel 2007公式与函数完全剖析PDF电子书下载

第一部分 掌握Excel区域和公式第1章 充分利用区域 3

1.1 高级区域选择技巧 3

1.1.1 使用鼠标选择区域的技巧 4

1.1.2 使用键盘选择区域的技巧 4

1.1.3 处理三维区域 5

1.1.4 使用“转到”命令选择区域 6

1.1.5 使用“定位条件”对话框 6

1.2 在区域中输入数据 10

1.3 区域的填充 10

1.4 使用填充柄 11

1.4.1 使用“自动填充”创建文本和数字序列 11

1.4.2 创建自定义的自动填充序列 12

1.4.3 填充区域 13

1.5 创建序列 13

1.6 高级区域复制技巧 14

1.6.1 复制选定的单元格属性 14

1.6.2 以算术方式组合源区域和目标区域 16

1.6.3 转置 16

1.7 清除区域 17

1.8 对区域应用条件格式 17

1.8.1 创建突出显示单元格的规则 17

1.8.2 创建最高值／最低值规则 19

1.8.3 添加数据条 20

1.8.4 添加色阶 23

1.8.5 添加图标集 24

第2章 使用区域名 26

2.1 定义区域名 27

2.1.1 使用“名称”框 27

2.1.2 使用“新建名称”对话框 28

2.1.3 修改范围以定义工作表级区域名 29

2.1.4 使用工作表文本定义名称 29

2.1.5 命名常量 31

2.2 使用区域名 32

2.2.1 引用区域名 32

2.2.2 使用名称自动完成功能 34

2.2.3 使用区域名进行导航 34

2.2.4 将区域名列表粘贴到工作表中 34

2.2.5 打开名称管理器 35

2.2.6 筛选名称 35

2.2.7 编辑区域名的坐标 35

2.2.8 自动调整区域坐标 36

2.2.9 修改区域名 37

2.2.10 删除区域名 37

2.2.11 结合使用区域名和交集运算符 37

第3章 建立基本公式 39

3.1 掌握有关公式的基本知识 39

3.1.1 Excel 2007对公式的限制 40

3.1.2 输入和编辑公式 40

3.1.3 使用算术公式 41

3.1.4 使用比较公式 41

3.1.5 使用文本公式 42

3.1.6 使用引用公式 42

3.2 理解运算符优先级 43

3.2.1 优先级顺序 43

3.2.2 控制优先级顺序 43

3.3 控制工作表的计算 45

3.4 复制和移动公式 46

3.4.1 理解相对引用格式 47

3.4.2 理解绝对引用格式 48

3.4.3 复制公式而不调整相对引用 48

3.5 显示工作表中的公式 49

3.6 将公式转换为值 49

3.7 在公式中使用区域名 50

3.7.1 将名称粘贴到公式中 50

3.7.2 对公式应用名称 51

3.7.3 命名公式 53

3.8 在公式中使用链接 53

3.8.1 理解外部引用 54

3.8.2 更新链接 55

3.8.3 修改链接源 56

3.9 设置数值、日期和时间的格式 56

3.9.1 数字显示格式 56

3.9.2 日期和时间的显示格式 63

3.9.3 删除自定义格式 65

第4章 创建高级公式 66

4.1 使用数组 66

4.1.1 使用数组公式 67

4.1.2 使用数组常量 69

4.1.3 使用或返回数组的函数 70

4.2 使用迭代和循环引用 71

4.3 合并多个工作表的数据 72

4.3.1 根据位置合并 73

4.3.2 根据分类合并 76

4.4 对单元格应用数据有效性规则 77

4.5 在工作表中使用对话框控件 79

4.5.1 使用表单控件 79

4.5.2 在工作表中添加控件 80

4.5.3 将控件同单元格关联起来 80

4.5.4 理解工作表控件 81

第5章 排除公式中的错误 85

5.1 理解Excel错误值 86

5.1.1 #DIV/0! 86

5.1.2 #N/A 86

5.1.3 #NAME? 87

5.1.4 #NULL! 88

5.1.5 #NUM! 88

5.1.6 #REF! 88

5.1.7 #VALUE! 88

5.2 修复其他公式错误 89

5.2.1 缺少括号或括号不匹配 89

5.2.2 错误的公式结果 89

5.2.3 解决循环引用 90

5.3 使用IFERROR（）处理公式错误 91

5.4 使用公式错误检查器 92

5.4.1 选择处理的措施 92

5.4.2 设置错误检查选项 93

5.5 审核工作表 95

5.5.1 理解审核 95

5.5.2 追踪引用单元格 96

5.5.3 追踪从属单元格 96

5.5.4 追踪错误单元格 97

5.5.5 移去追踪箭头 97

5.5.6 公式求值 97

5.5.7 监视单元格的值 98

第二部分 发挥函数的威力 101

第6章 理解函数 101

6.1 Excel函数简介 102

6.2 函数的结构 102

6.3 在公式中输入函数 104

6.4 使用“插入函数”功能 105

6.5 加载分析工具库 107

第7章 使用文本函数 108

7.1 Excel文本函数 108

7.2 使用字符和编码 109

7.2.1 CHAR（）函数 110

7.2.2 CODE（）函数 112

7.3 转换文本 113

7.3.1 LOWER（）函数 113

7.3.2 UPPER（）函数 113

7.3.3 PROPER（）函数 113

7.4 设置文本的格式 113

7.4.1 DOLLAR（）函数 114

7.4.2 FIXED（）函数 114

7.4.3 TEXT（）函数 115

7.4.4 显示工作簿最后一次更新的时间 115

7.5 操作文本 116

7.6 从字符串中删除不想要的字符 116

7.6.1 TRIM（）函数 116

7.6.2 CLEAN（）函数 117

7.6.3 PEPT（）函数：重复字符 117

7.6.4 填充单元格 117

7.6.5 创建文本图表 118

7.7 提取子字符串 119

7.7.1 LEFT（）函数 119

7.7.2 RIGHT（）函数 119

7.7.3 MID（）函数 120

7.7.4 将文本转换为句首字母大写 120

7.7.5 一个日期转换公式 121

7.8 搜索子字符串 122

7.8.1 FIND（）函数和SEARCH（）函数 122

7.8.2 提取名和姓 122

7.8.3 提取名、姓和中间缩写 123

7.8.4 确定列字母 124

7.9 替换子字符串 125

7.9.1 REPLACE（）函数 125

7.9.2 SUBSTITUTE（）函数 125

7.9.3 从字符串中删除一种字符 126

7.9.4 从字符串中删除两种字符 126

7.9.5 删除换行符 127

第8章 使用逻辑和信息函数 128

8.1 使用逻辑函数添加智能 128

8.1.1 使用IF（）函数 129

8.1.2 执行多重逻辑检测 131

8.1.3 结合使用逻辑函数和数组 136

8.2 创建应收账款超期工作表 140

8.2.1 计算更精确的到期日期 141

8.2.2 超期票据的分类 142

8.3 使用信息函数获取数据 143

8.3.1 CELL（）函数 143

8.3.2 ERROR.TYPE（）函数 145

8.3.3 INFO（）函数 147

8.3.4 IS函数 148

第9章 使用查找函数 150

9.1 理解查找表 151

9.2 CHOOSE（）函数 151

9.2.1 确定是星期几 152

9.2.2 确定财年中的月份 152

9.2.3 计算加权的调查问卷结果 153

9.2.4 结合使用CHOOSE（）函数与工作表选项按钮 154

9.3 在表中查找值 154

9.3.1 VLOOKUP（）函数 155

9.3.2 HLOOKUP（）函数 155

9.3.3 使用范围查找确定顾客的折扣率 156

9.3.4 使用范围查找确定税率 157

9.3.5 查找精确匹配 158

9.3.6 高级查找操作 159

第10章 使用日期和时间函数 164

10.1 Excel如何处理日期和时间 164

10.1.1 输入日期和时间 165

10.1.2 Excel和两位数年份 166

10.2 使用Excel日期函数 166

10.2.1 返回日期 167

10.2.2 返回日期的组成部分 169

10.2.3 计算两个日期的差 176

10.3 使用Excel时间函数 180

10.3.1 返回时间 180

10.3.2 返回时间的组成部分 181

10.3.3 计算两个时间之差 183

10.4 建立员工时间表 183

10.4.1 在时间表中输入数据 184

10.4.2 计算每天的工作时间 184

10.4.3 计算每周的工作时间 186

10.4.4 计算周薪 186

第11章 使用数学函数 187

11.1 理解Excel舍入函数 189

11.1.1 ROUND（）函数 190

11.1.2 MROUND（） 190

11.1.3 ROUNDDOWN（）和ROUNDUP（）函数 191

11.1.4 CEILING（）和FLOOR（）函数 191

11.1.5 确定日期所处的财务季度 192

11.1.6 计算复活节的日期 192

11.1.7 EVEN（）和ODD（）函数 193

11.1.8 INT（）和TRUNC（）函数 193

11.1.9 使用舍入防止计算误差 193

11.1.10 设置价格的小数部分 194

11.2 舍入收费时间 194

11.3 数值求和 195

11.3.1 SUM（）函数 195

11.3.2 计算累计总和 195

11.3.3 仅对区域中的正数或负数求和 196

11.4 MOD（）函数 196

11.4.1 一个更好的计算时间差的公式 197

11.4.2 对每隔n行中的单元格求和 197

11.4.3 判断是否为闰年 198

11.4.4 生成分类账阴影 198

11.5 生成随机数 199

11.5.1 RAND（）函数 199

11.5.2 RANDBETWEEN（）函数 202

第12章 使用统计函数 203

12.1 理解描述统计学 204

12.2 使用COUNT（）函数计算项数 205

12.3 计算平均值 206

12.3.1 AVERAGE（）函数 206

12.3.2 MEDIAN（）函数 206

12.3.3 MODE（）函数 207

12.3.4 计算加权平均 207

12.4 计算极值 208

12.4.1 MAX（）和MIN（）函数 208

12.4.2 LARGE（）和SMALL（）函数 209

12.4.3 对排位靠前的k个值执行计算 210

12.4.4 对排位靠后的k个值执行计算 210

12.5 计算偏差指标 210

12.5.1 计算分布范围 210

12.5.2 使用VAR（）函数计算方差 211

12.5.3 使用STDEVP（）和STDEV（）函数计算标准偏差 211

12.6 处理频数分布 212

12.6.1 FREQUENCY（）函数 213

12.6.2 理解正态分布和NORMDIST（）函数 214

12.6.3 曲线形状Ⅰ：SKEW（）函数 215

12.6.4 曲线形状Ⅱ：KURT（）函数 216

12.7 使用分析工具库中的统计工具 217

12.7.1 使用“描述统计”工具 219

12.7.2 确定数据的相关性 220

12.7.3 使用“直方图” 222

12.7.4 使用“随机数发生器”工具 223

12.7.5 使用“排位与百分比排位”工具 225

第三部分 建立商业模型 229

第13章 使用表分析数据 229

13.1 将区域转换为表 230

13.2 基本的表操作 231

13.3 表排序 233

13.3.1 以自然顺序对表排序 234

13.3.2 根据字段的一部分排序 235

13.3.3 排序时不考虑冠词 236

13.4 筛选表数据 237

13.4.1 使用筛选列表筛选表 237

13.4.2 用复杂条件筛选表 240

13.4.3 输入计算条件 242

13.4.4 将筛选结果复制到其他区域中 243

13.5 在公式中引用表 244

13.5.1 使用表说明符 245

13.5.2 输入表公式 246

13.6 Excel表函数 247

13.6.1 表函数简介 247

13.6.2 不需要条件区域的函数 248

13.6.3 接受多个条件的表函数 250

13.6.4 要求条件区域的表函数 252

13.7 在缺陷产品数据库中应用统计表函数 255

第14章 使用数据透视表分析数据 257

14.1 数据透视表是什么 257

14.1.1 数据透视表的工作原理 258

14.1.2 一些数据透视表术语 260

14.2 创建数据透视表 260

14.2.1 从表或区域创建数据透视表 260

14.2.2 从外部数据库创建透视表 263

14.2.3 使用和定制数据透视表 263

14.3 使用数据透视表分类汇总 264

14.3.1 隐藏数据透视表中的总计 265

14.3.2 隐藏数据透视表中的分类汇总 265

14.3.3 自定义分类汇总计算 265

14.4 修改计算数据字段汇总的方法 265

14.4.1 使用差异汇总计算 266

14.4.2 使用百分比汇总计算 268

14.4.3 使用累计汇总 270

14.4.4 使用指数汇总计算 271

14.5 创建自定义的数据透视表计算方式 273

14.5.1 创建计算字段 274

14.5.2 创建计算项 275

14.6 使用计算项编制预算 276

14.7 在工作表公式中使用数据透视表结果 278

第15章 使用Excel商业建模工具 280

15.1 使用假设分析 280

15.1.1 建立单输入数据表 281

15.1.2 在输入数据表中添加公式 283

15.1.3 建立双输入数据表 284

15.1.4 编辑数据表 285

15.2 使用单变量求解 285

15.2.1 单变量求解的工作原理 285

15.2.2 运行“单变量求解” 286

15.2.3 优化产品利润率 288

15.2.4 “单变量求解”得到的是近似值 289

15.2.5 盈亏平衡分析 290

15.2.6 代数方程求解 291

15.3 使用方案 292

15.3.1 理解方案 292

15.3.2 为使用方案建立工作表 293

15.3.3 添加方案 293

15.3.4 显示方案 295

15.3.5 编辑方案 295

15.3.6 合并方案 296

15.3.7 生成摘要报表 296

15.3.8 删除方案 298

第16章 使用回归分析跟踪趋势及进行预测 299

16.1 选择回归分析方法 300

16.2 对线性数据进行简单回归分析 300

16.2.1 使用最佳拟合线分析趋势 300

16.2.2 预测 307

16.3 季节性销量模型的趋势分析和预测 311

16.3.1 预测工作簿简介 312

16.3.2 计算常规趋势 313

16.3.3 计算预测趋势 314

16.3.4 计算消除季节因素后的趋势 314

16.3.5 计算月度季节指数 315

16.3.6 计算消除季节因素的月度销量 316

16.3.7 计算消除季节因素后的趋势 317

16.3.8 计算考虑季节因素后的趋势 317

16.3.9 计算考虑了季节因素的预测值 317

16.3.10 处理季度数据 317

16.4 对非线性数据进行简单回归分析 318

16.4.1 处理指数趋势 318

16.4.2 处理对数趋势 323

16.4.3 处理幂趋势 325

16.4.4 使用多项式回归分析 327

16.5 使用多变量回归分析 330

第17章 使用“规划求解”求解复杂问题 333

17.1 “规划求解”的背景知识 333

17.1.1 “规划求解”的优点 334

17.1.2 何时使用“规划求解” 334

17.2 加载“规划求解” 335

17.3 使用“规划求解” 335

17.4 添加约束 337

17.5 将解存储为方案 339

17.6 设置其他规划求解选项 339

17.6.1 控制“规划求解” 340

17.6.2 选择“规划求解”使用的方法 341

17.6.3 使用“规划求解”模型 341

17.7 理解“规划求解”消息 342

17.8 求解运输问题 343

17.9 显示规划求解报告 345

17.9.1 运算结果报告 345

17.9.2 敏感性报告 347

17.9.3 极限值报告 348

第四部分 建立财务公式 351

第18章 建立贷款公式 351

18.1 理解货币的时间价值 351

18.2 计算还贷额 352

18.2.1 还贷分析 353

18.2.2 处理气球式贷款 353

18.2.3 计算利息成本（第1部分） 354

18.2.4 计算本金和利息 355

18.2.5 计算利息成本（第2部分） 355

18.2.6 计算累积本金和利息 356

18.3 建立分期还贷计划表 357

18.3.1 建立固定利率分期还贷计划表 357

18.3.2 建立动态的分期还贷计划表 358

18.4 计算贷款期限 360

18.5 计算要求的贷款利率 361

18.6 计算贷款金额 362

18.7 抵押贷款 363

18.7.1 建立浮动利率抵押贷款的分期还贷计划表 363

18.7.2 考虑提前归还抵押贷款本金 365

第19章 建立投资公式 367

19.1 利率 367

19.1.1 理解复利 367

19.1.2 名义利率与实际利率 368

19.1.3 名义利率和实际利率之间的转换 368

19.2 计算未来值 369

19.2.1 整存整取的未来值 370

19.2.2 零存整取的未来值 370

19.2.3 初始存款配合追加存款的未来值 371

19.3 实现投资目标 371

19.3.1 计算要求的利率 371

19.3.2 计算需要的周期数 372

19.3.3 计算每个周期的存款额 373

19.3.4 计算所需的初始存款额 374

19.3.5 计算浮动利率条件下的未来值 374

19.4 建立投资计划表 375

第20章 建立贴现公式 378

20.1 计算现值 378

20.1.1 剔除通货膨胀的影响 379

20.1.2 使用PV（）函数计算现值 379

20.1.3 进行收益性投资还是购买出租性房产 380

20.1.4 购买还是租用 381

20.2 现金流贴现 382

20.2.1 计算净现值 383

20.2.2 使用NPV（）函数计算净现值 384

20.2.3 现金流不固定的净现值 385

20.2.4 非周期性现金流的净现值 385

20.3 计算回收期 386

20.3.1 不考虑贴现的回收期 386

20.3.2 不考虑贴现的精确回收点 387

20.3.3 考虑贴现的回收期 388

20.4 计算内部收益率 389

20.4.1 使用IRR（）函数 389

20.4.2 计算非周期性现金流的内部收益率 390

20.4.3 计算修正内部收益率 390

20.5 图书出版 391

20.5.1 单件常量 391

20.5.2 运营成本和销售额 392

20.5.3 现金流 393

20.5.4 现金流分析 393