Excel应用与技巧大全PDF格式文档图书下载

第一篇 基础篇 1

第1章 Excel简介 1

1.1 Excel的主要功能 2

1.1.1数据记录与整理 2

1.1.2数据计算 2

1.1.3数据分析 2

1.1.4图表制作 3

1.1.5信息的传递和共享 3

1.1.6自动化定制Excel的功能和用途 4

1.2初次使用Excel 4

1.2.1安装Excel 2010 4

1.2.2启动Excel 2010 6

1.2.3退出Excel 2010 7

1.3熟悉与设置Excel操作环境 7

1.3.1熟悉Excel 2010窗口环境 7

1.3.2功能区的显示与隐藏 8

1.3.3设置快速访问工具栏 8

1.3.4创建自己的选项卡和组 10

第2章 Excel基础操作 14

2.1工作簿的基本操作 15

2.1.1新建工作簿 15

2.1.2保存工作簿 16

2.1.3打开和关闭工作簿 18

2.2工作表的基本操作 20

2.2.1选择工作表 21

2.2.2插入与删除工作表 22

2.2.3重命名工作表 23

2.2.4改变工作表标签的颜色 24

2.2.5移动与复制工作表 25

2.2.6隐藏与显示工作表 26

2.3行和列的基本操作 27

2.3.1选择表格中的行和列 27

2.3.2插入与删除行和列 28

2.3.3调整行高和列宽 29

2.3.4隐藏与显示行和列 30

2.4单元格的基本操作 31

2.4.1选择单元格 31

2.4.2插入与删除单元格 32

2.4.3合并单元格 33

2.5工作窗口的视图控制 34

2.5.1工作簿的多视图显示 34

2.5.2拆分窗口 35

2.5.3冻结窗口 36

2.5.4重排窗口 37

第3章高效输入数据 39

3.1输入基本数据 40

3.1.1输入文本型数据 40

3.1.2输入数值型数据 41

3.2自动填充数据 43

3.2.1快速输入相同数据 44

3.2.2快速填充有规律的数据 45

3.2.3自定义输入填充序列 47

3.3使用数据有效性高效输入数据 48

3.3.1只允许输入整数 49

3.3.2使用序列制作下拉列表 50

3.3.3限制文本长度 51

3.3.4自定义数据有效性的条件 52

3.3.5设置输入信息 53

3.3.6设置出错警告 53

3.3.7圈释无效数据 54

3.4其他高效输入数据的方法 56

3.4.1使用“记录单”输入 56

3.4.2从右键的“从下拉列表中选择”输入 57

第4章整理工作表中的数据 58

4.1设置数据格式 59

4.1.1应用内置数据格式 59

4.1.2自定义数据格式 60

4.2调整数据格式 61

4.2.1设置千位分隔符 62

4.2.2增加或减少小数位数 62

4.2.3设置负数显示形式 63

4.3强大的粘贴功能 64

4.3.1多样化的粘贴选项 64

4.3.2神奇的选择性粘贴 66

4.4强大的定位功能 68

4.4.1定位常量、批注和空格 69

4.4.2定位行/列内容差异单元格 70

4.4.3定位从属和引用单元格 71

4.5查找与替换 72

4.5.1查找和替换单元格内容 72

4.5.2查找和替换单元格中的格式 74

第5章创建个性化工作表 76

5.1自定义工作表中的数据显示方式 77

5.1.1自动换行 77

5.1.2调整文本显示方向 78

5.2设置单元格中数据格式 79

5.2.1设置字体、字号、字形和颜色 79

5.2.2设置单元格对齐方式 82

5.3添加边框和底纹 83

5.3.1为单元格添加边框 83

5.3.2为单元格填充底纹 84

5.4单元格样式的应用 86

5.4.1套用预置单元格格式 86

5.4.2自定义单元格格式 87

5.4.3修改单元格样式 88

5.5套用表格样式 89

5.5.1应用表格内置样式 90

5.5.2新建表格样式 91

5.6使用主题快速更改表格外观 93

5.6.1更换主题 93

5.6.2自定义主题效果 94

5.7使用批注添加解说 94

第6章打印与输出Excel表格数据 97

6.1页面设置 98

6.1.1选择纸张方向 98

6.1.2选择纸张大小 99

6.1.3调整页边距 99

6.1.4添加页眉和页脚 100

6.1.5设置打印标题 103

6.2选择打印区城 104

6.3插入分页符强制分页 105

6.4打印预览及打印设置 105

6.5使用电子邮件发送数据 108

6.5.1作为附件发送数据 108

6.5.2作为PDF邮件发送数据 109

6.6轻松发布数据 111

6.6.1发布为PDF或XPS 111

6.6.2发布为HTML文件 112

第二篇 公式与函数篇 114

第7章利用公式与函数简单处理数据 114

7.1公式与函数的基本知识 115

7.1.1公式的组成 115

7.1.2公式中的运算符及优先级 115

7.1.3了解数组公式 116

7.1.4函数的结构 117

7.1.5函数的种类和参数的类型 117

7.2了解单元格的引用方式 118

7.2.1 A1引用样式和R1C1引用样式 118

7.2.2相对引用、绝对引用和混合引用 119

7.2.3对其他工作表和工作簿的引用 120

7.3使用名称简化公式 121

7.3.1根据选定的内容快速创建名称 121

7.3.2使用“新建名称”对话框定义名称 122

7.3.3创建多工作表名称 123

7.3.4在公式中应用名称 123

7.4快速输入函数 124

7.4.1使用“自动求和”按钮插入函数 124

7.4.2使用函数库插入已知类别的函数 125

7.4.3输入关键字搜索函数 126

7.4.4手工输入熟悉的函数 127

7.5数组公式的基本操作 128

7.5.1创建多单元格数组公式 129

7.5.2创建单个单元格数组公式 129

7.5.3扩展数组公式 130

7.6审核计算公式是否正确 131

7.6.1在单元格中显示公式 131

7.6.2使用“公式求值”查看分步计算结果 132

7.6.3使用“错误检查”检查公式中的错误 133

7.6.4有目的地启用循环引用 134

7.7函数与公式的限制 135

第8章文本函数 136

8.1求指定的字符或字符串 137

8.1.1 LEN（）函数：返回文本字符串中的字符数 137

8.1.2 FIND（）函数：求字符串的位置 138

8.1.3 LEFT（）函数：返回左侧字符 139

8.1.4 RIGHT（）函数：返回右侧字符 140

8.1.5 MID（）函数：返回中间字符 141

8.2文本的转换函数 142

8.2.1 UPPER（）函数：转换大写 142

8.2.2 LOWER（）函数：转换为小写 142

8.2.3 TEXT（）函数：转换数字格式为文本 143

8.2.4 PROPER（）函数：将首字母大写 144

8.3文本的操作函数 145

8.3.1 EXACT（）函数：判断字符串的异同 145

8.3.2 TRIM（）函数：删除空格 146

8.3.3 REPT（）函数：重复显示文本 147

第9章数学与三角函数 149

9.1计算函数 150

9.1.1 SUM（）与SUMIF（）函数：求和与根据条件求和 150

9.1.2 PRODUCT（）与SUMPRODUCT（）函数：求乘积与数组乘积之和 152

9.1.3 SQRT（）函数：计算正平方根 153

9.1.4 SUBTOTAL（）函数：计算数据库的分类汇总 153

9.1.5 MOD（）函数：计算两数相除的余数 155

9.1.6 ABS（）函数：求绝对值 156

9.2数值舍入相关函数 157

9.2.1 INT（）函数：取整 157

9.2.2 ROUND（）、ROUNDDOWN（）和ROUNDUP（）函数：按位舍入 158

9.2.3FLOOR（）函数：按条件向下舍入 159

9.2.4 CEILING（）函数：按条件向上舍入 160

9.3使用RAND（）函数产生随机数 161

第10章日期与时间函数 163

10.1返回特定的日期和时间的函数 164

10.1.1 TODAY（）函数：显示当前日期 164

10.1.2 NOW（）函数：显示当前时间 165

10.1.3 YEAR（）、MONTH（）和DAY（）函数：返回年份、月份和天数 166

10.1.4 HOUR（）、MINUTE（）和SECOND（）函数：返回时、分、秒 167

10.1.5 EOMONTH（）函数：求月末日期 168

10.1.6 EDATE（）函数：求特定日期 168

10.2返回日期和时间序列号的函数 169

10.2.1 DATE（）函数：返回日期序列号 169

10.2.2 TIME（）函数：返回时间序列号 170

10.2.3 DATEVALUE（）函数：返回文本日期的序列号 171

10.2.4 TIMEVALUE（）函数：返回文本时间序列号 172

10.3时间段相关函数 172

10.3.1 DAYS360（）函数：求日期差 173

10.3.2 DATEDIF（）函数：求两日期间的年数、月数和天数 173

10.3.3 YEARFRAC（）函数：求天数比 175

10.4星期相关函数 176

10.4.1 WEEKDAY（）函数：求星期几 176

10.4.2 WEEKNUM（）函数：返回所在周 178

10.5工作日相关函数 178

10.5.1 WORKDAY（）函数：求工作日日期 178

10.5.2 NETWORKDAYS（）函数：求工作日天数 179

第11章查找与引用函数 181

11.1查找函数 182

11.1.1VLOOKUP（）函数：竖直查找指定的值 182

11.1.2 HLOOKUP（）函数：水平查找指定的值 183

11.1.3 INDEX（）函数：返回值或引用 184

11.1.4 CHOOSE（）函数：选择列表中的某个数值 185

11.1.5 MATCH（）函数：求数据的位置 186

11.2引用函数 187

11.2.1 ADDRESS（）函数：求引用的地址 187

11.2.2 OFFSET（）函数：求相应值 188

11.2.3INDIRECT（）函数：显示引用 189

11.2.4 ROW（）和ROWS（）函数：显示引用的行号和区域的行数 190

11.2.5 COLUMN（）和COLUMNS（）函数：显示引用的列号和区域的列数 191

第12章统计函数 192

12.1计算平均值 193

12.1.1 AVERAGE（）函数：计算数值的平均值 193

12.1.2 AVERAGEIF（）函数：求满足条件单元格的平均值 194

12.1.3 MEDIAN（）函数：获取数组的中值 195

12.1.4 MODE（）函数：计算分布的众数 196

12.2对单元格进行计数 197

12.2.1 COUNT（）函数：统计单元格个数 197

12.2.2 COUNTBLANK（）函数：统计空单元格个数 198

12.2.3 COUNTIF（）函数：按条件统计 199

12.3计算极值与排位 200

12.3.1 LARGE（）和SMALL（）函数：获取第K个最大值和最小值 200

12.3.2 MAX（）和MIN（）函数：获取最大值和最小值 201

12.3.3 RANK（）函数：获取数值的排位信息 202

12.3.4 FREQUENCY（）函数：计算数据的频率分布 203

第13章财务函数 205

13.1常见的贷款和投资函数 206

13.1.1 PMT（）函数：计算每期付款额 206

13.1.2 RATE（）函数：计算年金的利率 207

13.1.3 PV（）函数：计算投资的现值 208

13.1.4 NPER（）函数：计算投资的期数 209

13.1.5 FV（）函数：计算投资的未来值 209

13.2财务专业函数 210

13.2.1 PPMT（）函数：计算偿还本金的数额 210

13.2.2 PMT（）函数：计算偿还利息的数额 211

13.2.3CUMIPMT（）函数和CUMPRINC（）函数：计算累计偿还的利息和本金 212

13.2.4 EFFECT（）和 NOMINAL（）函数：计算有效年利率和名义年利率 213

13.3折旧函数 214

13.3.1 SLN（）函数：计算线性折旧值 214

13.3.2 DB（）函数：计算固定余额递减折旧值 215

13.3.3DDB（）函数：计算双倍余额递减折旧值 216

13.3.4 VDB（）函数：计算余额递减法折旧值 217

13.4投资分析函数 218

13.4.1 NPV（）函数：计算投资的净现值 218

13.4.2 IRR（）函数：计算定期现金流的内部收益率 219

13.4.3MIRR（）函数：计算修正内部收益率 220

13.4.4 XNPV（）函数：计算现金流计划的净现值 221

13.4.5 XIRR（）函数：计算内部收益率 221

13.5债券投资函数 222

13.5.1 YIELD（）函数：计算有价证券的收益率 222

13.5.2 PRICE（）函数：计算定期付息的有价证券价格 223

13.5.3 RECEIVED（）函数：计算有价证券回收的数额 224

13.5.4 INTRATE（）函数：计算有价证券的利率 225

第三篇 数据分析篇 226

第14章用排序、筛选与分类汇总整理数据 226

14.1数据的排序 227

14.1.1简单排序 227

14.1.2按多个关键字进行排序 228

14.1.3按单元格颜色排序 230

14.1.4自定义排序 231

14.2数据的筛选 233

14.2.1自动筛选 233

14.2.2高级筛选 235

14.3对数据进行分类汇总 237

14.3.1创建简单的分类汇总 237

14.3.2嵌套分类汇总 239

第15章 巧用条件格式洞察数据变化趋势 241

15.1快速分析数据之突出显示单元格 242

15.1.1突出显示一定数值范围的单元格 242

15.1.2突出显示包含某文本的单元格 243

15.1.3突出显示发生日期在某个时段内的单元格 244

15.1.4突出显示重复值或唯一值 245

15.2快速分析数据之项目选取规则 246

15.2.1选取值最大或最小的N项 247

15.2.2选取高于或低于平均值的项 248

15.3使用数据条创建单元格内条形图 248

15.3.1套用数据条样式 249

15.3.2自定义数据条的最大/最小值 249

15.3.3设置数据条外观 251

15.4使用色阶以色彩分析数据 252

15.4.1套用色阶样式 252

15.4.2调整色阶刻度和颜色 253

15.5快速分析数据之图标集 254

15.5.1选择图标集样式 254

15.5.2修改图标集的阈值 255

15.6使用公式确定要设置格式的单元格 256

第16章 使用Excel进行模拟分析和规划求解 259

16.1使用模拟运算表 260

16.1.1单变量模拟运算表 260

16.1.2双变量模拟运算表 261

16.2单变量求解 262

16.3Excel的最优方案分析 263

16.3.1创建并显示方案 263

16.3.2生成方案报告 265

16.4使用规划求解处理数据 266

16.4.1加载规划求解 266

16.4.2建立规划求解模型 266

16.4.3设置约束条件 267

16.4.4规划求解结果报告 269

第17章使用数据工具分析数据 270

17.1智能的数据分列 271

17.2分级显示 273

17.2.1建立分级显示 273

17.2.2清除分级显示 275

17.3合并计算功能 276

17.3.1按位置合并计算 276

17.3.2按分类合并计算 277

17.4统计分析 279

17.4.1描述统计 279

17.4.2假设检验 281

17.5方差分析 283

17.5.1单因素方差分析 283

17.5.2双因素方差分析 284

17.6预测分析 285

17.6.1时间序列预测 285

17.6.2回归分析预测 287

第8章用数据透视表对数据进行立体分析 289

18.1创建数据透视表 290

18.1.1从工作簿中的数据区域创建 290

18.1.2导入外部数据库创建 291

18.2设置数据透视表的字段布局 292

18.2.1向数据透视表添加字段 293

18.2.2报表区域间移动字段 294

18.3编辑数据透视表 295

18.3.1更改数据透视表的数据源 295

18.3.2展开和折叠数据透视表中的明细数据 297

18.4轻松更改数据透视表的布局 298

18.4.1启用经典数据透视表布局 299

18.4.2改变数据透视表的整体布局 300

18.5设置数据透视表的格式 302

18.5.1应用数据透视表样式 302

18.5.2改变数据透视表中所有单元格的数字格式 303

18.6轻松打破数据透视表的常规排序 304

18.6.1改变字段的排列顺序 304

18.6.2按值排序 305

18.7动态洞察数据的切片器 306

18.7.1为数据透视表插入切片器 307

18.7.2为切片器应用样式 307

18.7.3筛选多个字段项 308

18.8计算数据透视表中的数据 309

18.8.1更改数值字段的汇总方式 309

18.8.2轻松更改数据透视表中数据的显示方式 310

18.8.3在数据透视表中使用计算字段 311

18.9使用数据透视图分析数据 312

18.9.1创建数据透视图 313

18.9.2使用字段按钮在数据透视图中筛选 314

第四篇 图表图形篇 315

第19章使用图形和图片增加工作表效果 315

19.1插入与编辑图片和剪贴画 316

19.1.1插入图片 316

19.1.2插入剪贴画 317

19.1.3调整图片的亮度和对比度 319

19.1.4更改图片的颜色 320

19.1.5为图片添加艺术效果 321

19.1.6去除图片背景 321

19.1.7为图片应用边框样式 323

19.1.8裁剪图片 324

19.2插入与编辑艺术字 326

19.2.1插入艺术字 326

19.2.2设置艺术字样式 327

19.2.3设置艺术字形状 328

19.3绘制与编辑形状 329

19.3.1插入形状 329

19.3.2编辑形状 331

19.3.3设置形状格式 332

19.3.4对齐和分布 334

19.4添加SmartArt图形 335

19.4.1插入SmartArt图形 335

19.4.2添加形状 337

19.4.3更改SmartArt图形的颜色和样式 339

19.4.4自定义SmartArt图形的颜色和样式 340

第20章简洁适用的迷你图 343

20.1迷你图的特点 344

20.2创建迷你图 344

20.2.1创建单个迷你图 344

20.2.2创建迷你图组 345

20.3改变迷你图类型 347

20.3.1改变单个迷你图类型 347

20.3.2改变一组迷你图类型 348

20.4突出显示数据点 348

20.5迷你图样式和颜色设置 349

20.5.1迷你图样式设置 350

20.5.2迷你图颜色设置 351

20.5.3标记颜色设置 352

20.6设置迷你图的坐标轴 353

20.6.1设置迷你图纵坐标 353

20.6.2设置迷你图横坐标 355

第21章使用图表直观图解数据 357

21.1认识图表 358

21.1.1常用图表类型 358

21.1.2图表的组成部分 359

21.2创建图表 359

21.2.1根据数据创建图表 360

21.2.2将图表另存为模板 361

21.2.3根据图表模板创建 362

21.3编辑图表数据 363

21.3.1更改图表的数据区域 363

21.3.2更改图表类型 364

21.3.3更改图表布局 365

21.3.4调整图表大小与位置 365

21.4自定义设置图表元素分析数据 366

21.4.1设置图表标题 366

21.4.2选择图例位置 367

21.4.3设置坐标轴 368

21.4.4设置数据标签 369

21.4.5添加模拟运算表 370

21.4.6设置网格线 371

21.5设置图表格式 372

21.5.1应用图表内置样式 372

21.5.2手动设置各图表元素的格式 373

21.6预测与分析图表数据 374

21.6.1为图表添加趋势线 374

21.6.2为图表添加误差线 375

21.6.3为图表添加涨/跌柱线 377

21.7高级图表的制作 377

21.7.1断层图 377

21.7.2瀑布图 379

第五篇 协同篇 381

第22章 链接与共享Excel数据 381

22.1创建超链接 382

22.1.1创建超链接的方法 382

22.1.2编辑超链接 383

22.1.3激活超链接 385

22.2共享工作簿 386

22.2.1启用共享工作簿 386

22.2.2停止共享工作簿 387

22.3多人协同审阅工作簿 389

22.3.1启用修订 389

22.3.2与同事共同编辑工作簿 390

22.4远程协同办公 392

22.4.1将数据保存到Web页 392

22.4.2登录Windows Live ID账户 393

22.4.3在线查看保存到Web页的工作簿 394

22.4.4添加共享人使同事也能查看工作簿 395

22.4.5多用户协同编辑数据 396

第23章 为数据设置安全屏障 398

23.1 借助IRM进行信息安全控制 399

23.1.1部署RMS服务器 399

23.1.2 Microsoft IRM服务 399

23.1.3配置客户端以使用IRM 399

23.1.4使用IRM保护工作簿 401

23.2保护工作表 402

23.2.1设置工作表的可用编辑方式 403

23.2.2凭密码或权限编辑工作表的不同区域 404

23.3保护工作簿 406

23.3.1保护工作簿结构和窗口 406

23.3.2加密工作簿 407

23.4添加数字签名 408

23.4.1添加隐形数字签名 408

23.4.2添加Microsoft Office签名行 411

23.4.3添加图章签名行 413

23.5标记为最终状态 414

23.6借助“文档检查”保护私有信息 415

23.7自动保存与自动恢复 417

23.7.1自动保存 417

23.7.2自动恢复 418

第24章 与其他程序协作处理数据 419

24.1 Excel与Word的数据交换 420

24.1.1使用屏幕剪辑把Excel表格插入到Word中 420

24.1.2将Excel表格复制到Word中 421

24.1.3把Word数据粘贴到 Excel工作表中 422

24.1.4将已有的Word文件插入到Excel中 423

24.1.5将已有Excel文件作为对象插入到Word中 424

24.2 Excel与PowerPoint的数据交换 426

24.2.1将Excel图表复制粘贴至PPT中 426

24.2.2将Excel图表作为对象插入至PPT 427

24.2.3将幻灯片插入到Excel中 428

24.3获取外部数据 429

24.3.1导入自Access数据 430

24.3.2导入并同步来自网站的数据 431

24.3.3导入自文本数据 432

24.4使用OutLook发送Excel工作簿 434

第六篇 编程篇 436

第25章在Excel中用宏提高办公效率 436

25.1认识宏与宏病毒 437

25.2录制与编辑宏 437

25.2.1录制宏 437

25.2.2查看与编辑宏代码 438

25.2.3删除宏 440

25.3执行宏 440

25.3.1执行宏 441

25.3.2单步执行宏 441

第26章 初识VBA 443

26.1认识Excel VBA 444

26.2 Excel 2010中VBA的工作环境 444

26.2.1“开发工具”选项卡的显隐 445

26.2.2宏安全性设置 445

26.2 3启用工作簿中的宏 446

26.2.4受信任位置 447

26.2.5使用加载宏快速加载可用宏 449

26.3 VBE开发环境界面介绍 450

26.3.1工程资源管理器窗口 450

26.3.2属性窗口 451

26.3.3代码窗口 452

26.3.4立即窗口 453

26.4对象模块的基本操作 454

26.4.1对象模块的插入 455

26.4.2对象模块的导入与导出 455

26.5程序的调试 456

26.5.1逐语句调试 457

26.5.2添加断点分段调试代码 458

第27章VBA程序设计的语言基础 460

27.1 VBA语法基础 461

27.1.1 VBA字符集和标识符 461

27.1.2 VBA常量 463

27.1.3VBA变量 464

27.1.4 VBA运算符 468

27.2 VBA的代码结构 472

27.2.1顺序结构 472

27.2.2选择结构 473

27.2.3循环结构 476

27.3 VBA的过程 479

27.3.1 Sub过程 479

27.3.2 Function过程 480

第28章VBA中常用对象的应用管理 482

28.1认识Excel VBA中的对象、方法、属性和事件 483

28.1.1 Excel VBA的对象 483

28.1.2对象的属性 483

28.1.3对象的方法 485

28.1.4对象的事件 486

28.2用对象、方法、属性和事件操作工作簿、工作表、单元格和图表 487

28.2.1工作簿操作 487

28.2.2工作表操作 490

28.2.3单元格操作 492

28.2.4数据图表操作 494

第29章用控件在工作表实现交互式操作 496

29.1认识控件 497

29.1.1表单控件 497

29.1.2 ActiveX控件 497

29.2控件的添加 498

29.3控件的属性设置 500

29.3.1表单控件属性设置 500

29.3.2 ActiveX控件属性设置 505

29.4为控件添加相应的事件代码 506

29.4.1为表单控件指定宏 507

29.4.2为AcitveX控件添加事件代码 508

第30章用对话框在VBA中实现人机交互操作 510

30.1调用对话框实现交互式操作 511

30.1.1调用输入信息对话框实时接收用户输入信息 511

30.1.2调用输出信息对话框提醒用户进行选择性操作 513

30.1.3调用Excel文件对话框实现交互式操作 515

30.2使用用户窗体自定义对话框 517

30.2.1窗体模块的添加与属性设置 517

30.2.2在窗体中使用控件 518

30.2.3为用户窗体添加事件代码 522

查看更多关于Excel应用与技巧大全的内容