Excel数据处理与分析

第1章Excel快速入门

1.1Excel的安装.启动与退出 1

1.1.1安装Excel. 1

1.1.2启动Excel 2

1.1.3退出Excel 4

1.2工作表操作 4

1.2.1新建工作表 4

1.2.2工作表的页数设置 6

1.2.3工作表的选定 6

1.2.4重命名工作表 8

1.2.5工作表的插入与删除 8

1.2.6工作表的移动与复制 9

1.2.7工作表的隐藏 10

1.2.8保护工作表 11

1.3工作表的美化 12

1.3.1设置对齐格式 12

1.3.2字体格式设置 13

1.3.3边框格式设置 15

1.3.4网格线格式设置 16

1.3.5背景与底纹图案设置 16

1.3.6使用自动套用格式 17

1.4单元格和单元格区域操作 18

1.4.1单元格的选择 18

1.4.2清除和修改单元格内容 19

1.4.3定义单元格名称 20

1.4.4添加单元格批注 21

1.5Excel文件和模板操作 22

1.5.1创建新的Excel文件 22

1.5.2打开已存在的Excel文件 23

1.5.3保存和关闭文件 24

1.5.4文件的恢复 25

1.5.5使用默认模板 26

1.5.6创建自定义模板 27

1.6Excel文件的打印 27

1.6.1快速打印文件 27

1.6.2打印设置 27

1.6.3打印预览 29

本章小结 31

习题 32

第2章数据的格式化

2.1数据的输入 33

2.1.1数字.文本.日期和时间的输入 33

2.1.2输入批注 35

2.1.3快速输入技巧 36

2.2格式化工具 37

2.2.1格式工具 37

2.2.2Excel的自动数据格式 38

2.2.3系统提供的数据格式 38

2.3样式的定义与使用 40

2.3.1使用Excel内置样式 40

2.3.2自定义样式 40

2.4日期和时间的格式 41

2.4.1年.月.日格式 41

2.4.2时间格式 42

2.5颜色格式 43

2.6条件格式 43

2.7自定义格式的应用 45

本章小结 47

习题 47

第3章Excel公式与函数基础

3.1公式基础 49

3.1.1在公式中使用运算符 49

3.1.2公式中运算符的优先级 51

3.1.3在公式中使用函数 53

3.2公式和函数的输入 55

3.2.1手工输入公式 55

3.2.2在公式中半自动输入函数 57

3.3公式的编辑 58

3.3.1编辑公式 59

3.3.2公式的移动与复制 59

3.3.3公式的替代 62

3.3.4删除公式 62

3.4单元格引用 63

3.4.1相对.绝对和混合引用 63

3.4.2工作表外单元格的引用 64

3.5常见公式错误的更正 66

3.5.1公式中常见错误类型 66

3.5.2处理公式的循环引用 66

3.6公式审核 68

3.7Excel中函数的分类 70

3.7.1财务函数 70

3.7.2日期与时间函数 71

3.7.3数学与三角函数 71

3.7.4统计函数 72

3.7.5信息函数 72

3.7.6查找与引用函数 72

3.7.7逻辑函数 73

3.7.8数据库函数 73

3.7.9文本函数 74

3.7.10工程函数 75

3.8在Excel中自定义函数 75

本章小结 76

习题 76

第4章Excel图表基础

4.1图表概述 79

4.1.1Excel对图表的处理 80

4.1.2图表的组成 81

4.2创建图表 83

4.2.1快速创建图表 83

4.2.2使用图表向导创建图表 84

4.3图表的编辑 87

4.3.1图表的移动与调整大小 87

4.3.2改变图表类型 88

4.3.3设置图表格式 89

4.3.4图表的复制与删除 90

4.3.5图表中源数据的编辑 90

4.4图表的打印 93

4.5Excel中图表的类型 94

4.5.1柱形图 95

4.5.2条形图 95

4.5.3折线图 96

4.5.4饼图 96

4.5.5XY散点图 97

4.5.6面积图 97

4.5.7圆环图 98

4.5.8雷达图 98

4.5.9曲面图 98

4.5.10气泡图 99

4.5.11股价图 99

4.5.12圆柱图.圆锥图和棱锥图 99

本章小结 100

习题 100

第5章数据的获取与建立数据清单

5.1Excel数据库操作 101

5.1.1Excel数据库概述 101

5.1.2建立与数据源的连接 102

5.1.3获取数据源的数据 103

5.1.4设置外部数据区域的属性 104

5.1.5更新外部数据区域中的数据 105

5.2Excel与外部数据库 106

5.3使用MsQuery与外部数据库

5.3进行交互 107

5.3.1MsQuery与ODBC程序简介 108

5.3.2使用MsQuery检索外部

5.3.2数据库文件中的数据 108

5.3.3使用SQL语句进行高级查询 110

5.4建立数据清单 110

5.5数据的自动填充 111

5.5.1简单数据的自动填充 111

5.5.2间隔值相同数据的自动填充 112

5.5.3公式的自动填充 113

5.6数据的分列 113

本章小结 115

习题 116

第6章数据的编辑与查询

6.1窗口的冻结与拆分 117

6.2数据的增删改 118

6.2.1插入与删除数据 118

6.2.2数据的移动与复制 119

6.3查找与替换数据 120

6.4使用选择性粘贴 120

6.4.1数据转置 121

6.4.2粘贴数值 121

6.4.3其他选择性粘贴选项 122

6.5数据的跨工作表操作 122

6.5.1工作表间数据的调用 122

6.5.2工作簿间数据的调用 123

6.6多工作表的合并计算 124

6.6.1按位置合并计算 124

6.6.2按分类合并计算 124

6.7数据的排序与筛选 125

6.7.1排序 125

6.7.2筛选 126

6.8数据的查询 126

6.8.1使用高级筛选查询数据 127

6.8.2使用查询函数查询数据 127

6.9将数据清单发布到网页上 128

本章小结 129

习题.. 129

第7章使用数据分析工具处理数据

7.1安装分析工具库 131

7.2数据审核与跟踪 132

7.2.1跟踪引用单元格 132

7.2.2跟踪从属单元格 133

7.2.3数据有效性 134

7.2.4标注无效数据 135

7.3模拟运算表 135

7.3.1单变量模拟运算表 136

7.3.2双变量模拟运算表 138

7.4单变量求解 139

7.5方案分析 140

7.5.1建立方案 140

7.5.2方案的增删改 141

7.5.3提交方案分析报告 142

7.6规划求解 142

7.6.1加载宏??规划求解 142

7.6.2建立规划求解模型 143

7.6.3利用“规划求解”宏解决

7.6.3问题 144

7.6.4变化求解模型 146

7.6.5规划求解报告说明 146

7.7使用分析工具库分析数据 147

7.7.1方差分析工具 147

7.7.2相关系数工具 149

7.7.3协方差工具 150

7.7.4描述统计工具 150

7.7.5指数平滑工具 152

7.7.6F-检验工具 152

7.7.7直方图工具 154

7.7.8移动平均工具 154

7.7.9回归分析工具 155

7.7.10抽样分析工具 156

本章小结 157

习题 157

第8章分类汇总与透视分析

8.1分类汇总 159

8.1.1创建简单分类汇总 159

8.1.2多级分类汇总 161

8.1.3取消分类汇总 163

8.2数据透视表 163

8.2.1一个数据透视表的例子 163

8.2.2选择合适的数据透视表

5.3.2数据 165

8.2.3创建数据透视表 165

8.2.4数据透视表的编辑 167

8.3数据透视图 169

8.3.1创建数据透视图 169

8.3.2数据透视图的编辑 171

8.3.3数据透视图与静态图表间的

8.3.3区别和转换 172

8.4使用数据透视表分析数据 173

8.4.1使用数据透视表的总计和

8.3.3分类汇总功能 173

8.4.2创建计算字段和计算项 174

本章小结 174

习题 175

第9章抽样与问卷调查数据处理

9.1抽样 177

9.1.1简单抽样 177

9.1.2系统抽样 180

9.2调查问卷的设计 185

9.2.1调查问卷的设计步骤 185

9.2.2使用工作表设计调查问卷 187

9.3问卷数据的验证与检查 188

9.3.1事前数据的有效性检查 188

9.3.2事后数据的有效性检查 189

9.4问卷数据的分析 190

9.4.1问卷数据的描述性分析 190

9.4.2问卷数据统计分析 192

9.4.3问卷数据的回归分析 193

本章小结 195

习题 195

第10章经济数据处理

10.1投资决策数据处理 197

10.1.1投资静态指标评价 197

10.1.2投资动态指标评价 198

10.1.3投资决策敏感性分析 204

10.2资产更新数据处理 204

10.2.1折旧综合计算 205

10.2.2设备更新决策 206

10.3证券投资组合问题 208

10.4经营数据处理 210

10.4.1经营数据处理常用方法 210

10.4.2制定经营利润规划 211

10.4.3经营利润敏感性分析 213

10.4.4经营利润分析 215

10.5市场占有率预测 216

10.6期望利润预测 218

10.7投入产出预测 219

本章小结 219

习题 219

第11章用于商务决策的数据处理问题

11.1商务决策模型的创建 221

11.1.1有限方案的决策模型 221

11.1.2目标变量最优化决策模型 223

11.1.3无限方案的决策模型 225

11.1.4包含随机变量的决策模型 227

11.2商务决策模型基础分析方法 228

11.2.1假设分析 229

11.2.2单参数盈亏平衡分析 229

11.2.3双参数盈亏平衡分析 230

11.2.4单参数敏感性分析 231

11.2.5双参数敏感性分析 232

11.3随机模拟技术 233

11.3.1随机模拟技术概述 233

11.3.2特定观测值的生成方法 234

11.3.3生成样本分布数据并制作

11.3.3直方图 235

11.3.4检验特定观测值 236

11.3.5随机模拟技术的应用 237

11.4商务决策问题实例 238

11.4.1与财务管理相关的商务

11.3.3决策问题 238

11.4.2与生产和营销有关的商务

11.3.3决策问题 239

11.4.3其他商务决策问题举例 241

本章小结 243

习题 243

第12章财务管理相关决策数据处理

12.1筹资决策数据处理 245

12.1.1Excel中的筹资决策函数 245

12.1.2长期贷款筹资决策分析 248

12.1.3租赁筹资决策分析 249

12.1.4不同筹资方案的比较

12.1.4决策分析 250

12.2财务分析与预测 251

12.2.1移动平均法分析与预测 252

12.2.2回归法分析与预测 255

12.2.3指数平滑法分析与预测 257

12.3账务处理 258

12.3.1原始凭证 259

12.3.2记账凭证 260

12.3.3日记账 260

12.3.4分类账 261

12.3.5建立试算平衡表 262

12.4创建会计报表 263

12.4.1资产负债表 263

12.4.2建立损益表 264

12.4.3建立现金流量表 265

12.5会计报表的分析 267

12.5.1获利能力分析 267

12.5.2短长期偿债能力分析 268

12.5.3资产管理效果分析 269

12.5.4现金流量分析 269

本章小结 270

习题 270

第13章生产决策数据处理

13.1生产函数的确定 271

13.1.1建立生产函数工作表 271

13.1.2绘制生产函数曲线图 272

13.1.3生产函数分析 273

13.2总产量与边际产量计算 274

13.2.1建立计算工作表 274

13.2.2绘制边际产量折线图 275

13.2.3边际产量折线图的应用 275

13.3单位成本与收益分析 276

13.3.1建立计算工作表 276

13.3.2绘制曲线图 277

13.3.3单位成本与收益决策 278

13.4质量管理 279

13.4.1质量评价 279

13.4.2使用工作表函数进行

11.3.3质量控制 280

13.5工序能力分析 283

13.6生产的最优安排 286

本章小结 289

习题 289

第14章销售与营销数据处理

14.1收入的确认 291

14.1.1概述 291

14.1.2获利过程分析 292

14.1.3生产完成时确认收入 292

14.1.4销售点法确认收入 293

14.1.5现金法确认收入 294

14.1.6不同行业收入的确认 296

14.1.7收入确认分析 296

14.2贡献和毛利分析 297

14.2.1贡献和毛利计算 297

14.2.2单位贡献法 298

14.2.3创建营业利润表 299

14.2.4计算保本点 299

14.2.5贡献毛利分析 301

14.2.6销售品种结构的确定 301

14.2.7部门毛利分析 302

14.3销售数据统计分析 303

14.3.1销售收入.成本.费用.

11.3.3税金年度分析表 303

14.3.2销售收入.成本汇总表的

11.3.3构建与分析 306

14.3.3销售收入.成本.费用.

11.3.3税金年度对比表 307

14.3.4产品成本分析表 308

14.4销售预测 311

本章小结 312

习题 312

第15章实验数据处理

15.1列表法处理实验数据 315

15.2图示法处理实验数据 316

15.2.1建立合适的坐标系 317

15.2.2坐标分度的确定 318

15.3选择经验公式分析实验数据关系 321

15.3.1经验公式的选择 321

15.3.2常见经验公式的直线化方法 323

15.4经验公式系数求解 323

15.4.1幂函数系数求解 323

15.4.2指数和对数函数系数求解 325

15.5实验数据拟合 329

15.5.1数据拟合概述 329

15.5.2线性回归 330

15.5.3非线性回归 333

15.5.4逐步回归 334

本章小结 334

习题 334

第16章工程数据处理

16.1Excel工程函数应用 337

16.1.1积分函数在工程计算中的

16.1.1应用 337

16.1.2复数运算函数在工程计算中

11.3.3的应用 339

16.2工程造价管理 339

16.2.1编制定额单位估价表 340

16.2.2工程量清单报价 341

16.3建筑工程数据处理 344

16.3.1引例 344

16.3.2用Excel预测建筑工程工量 347

16.3.3用Excel解决建筑工程中的

11.3.3最优化问题 349

16.4公路工程数据处理 351

16.4.1数据输入与修正 352

16.4.2数据描述 353

16.4.3数据分析 353

16.4.4计算及输出结果 355

本章小结 356

习题 356

第17章在Excel中使用XML数据

17.1XML概述 359

17.2XML的基础知识 360

17.2.1数据的结构表示 360

17.2.2对XML文档的简单分析 360

17.2.3编辑和浏览XML文件 362

17.3Excel2003对XML的支持 363

17.3.1使用映射导入XML数据 363

17.3.2导入XML数据到列表 365

17.3.3XML数据的导出 365

本章小结 367

习题 367

第18章图表的高级应用

18.1图表的自定义 369

18.2在图表中操作数据系列 371

18.2.1数据系列的增删改 372

18.2.2丢失数据的处理 373

18.2.3添加误差线 374

18.2.4添加趋势线 375

18.2.5使用次坐标轴 377

18.2.6创建组合图表 378

18.3高级图表制作实例 379

18.3.1制作图片图表 379

18.3.2制作温度计图表 381

18.3.3制作比较柱状图表 383

18.3.4制作甘特图 384

18.3.5制作自动更新的图表 386

18.3.6绘制函数图像 387

本章小结 389

习题 389

第19章Excel中的宏与VBA

19.1宏简介 391

19.2宏的创建 391

19.2.1录制宏 392

19.2.2运行宏 394

19.2.3查看宏代码 395

19.2.4分析宏代码 397

19.2.5修改并测试宏代码 398

19.3VBA应用举例 400

19.3.1新建VBA工程项目 400

19.3.2设计应用程序界面 400

19.3.3创建储存窗体数据的

19.3.3工作表 403

19.3.4为窗体控件添加事件代码 403

19.3.5保存窗体数据到工作表 408

本章小结 409

习题... 409

附录习题答案