Excel技术与应用大全电子书

版 权

内 容 提 要

前 言

第1章 熟悉Excel的工作环境

1.1 Excel简介

1.1.1 Excel发展历程

1.1.2 Excel涵盖的功能

1.1.3 Excel的学习方法

1.2 启动和退出Excel程序

1.2.1 启动Excel程序

1.2.2 使用特殊方式启动Excel程序

1.2.3 退出Excel程序

1.3 Excel界面的组成结构

1.3.1 Excel界面的整体结构

1.3.2 功能区的组成

1.3.3 常规选项卡和上下文选项卡

1.3.4 选项卡中的命令类型

1.3.5 文件按钮

1.3.6 状态栏

1.3.7 对话框和窗格

1.4 自定义功能区和快速访问工具栏

1.4.1 显示和隐藏选项卡

1.4.2 修改内置的选项卡、组和命令

1.4.3 创建新的选项卡和组

1.4.4 自定义快速访问工具栏

1.4.5 恢复功能区和快速访问工具栏的默认设置

1.4.6 备份和恢复Excel界面配置信息

1.5 让Excel更易用的选项设置

1.5.1 启动Excel时隐藏开始屏幕

1.5.2 设置Excel界面配色

1.5.3 设置屏幕提示方式

1.5.4 启用或禁用实时预览

1.5.5 选择内容时隐藏浮动工具栏

第2章 工作簿和工作表的基本操作

2.1 理解工作簿和工作表

2.1.1 工作簿和工作表的关系

2.1.2 Excel支持的工作簿类型

2.2 工作簿的基本操作

2.2.1 新建工作簿

2.2.2 打开工作簿

2.2.3 保存和另存工作簿

2.2.4 关闭工作簿

2.2.5 恢复未保存的工作簿

2.2.6 兼容模式与格式转换

2.3 让工作簿更易用的设置

2.3.1 设置打开和保存工作簿的默认文件夹

2.3.2 设置保存工作簿的默认格式

2.3.3 设置自动恢复工作簿的保存时间间隔和位置

2.3.4 启用或禁用未保存工作簿的恢复功能

2.4 工作表的基本操作

2.4.1 添加工作表

2.4.2 激活和选择工作表

2.4.3 修改工作表的名称

2.4.4 设置工作表标签的颜色

2.4.5 移动和复制工作表

2.4.6 显示和隐藏工作表

2.4.7 删除工作表

2.5 控制Excel窗口的显示方式

2.5.1 设置窗口的显示比例

2.5.2 多窗口和拆分窗格

2.5.3 冻结窗格

2.5.4 保存窗口的显示设置

2.5.5 在不同视图下工作

2.6 创建和使用Excel模板

2.6.1 创建Excel默认的工作簿模板和工作表模板

2.6.2 创建自定义模板

第3章 行、列和单元格的基本操作

3.1 行、列和单元格的基本概念

3.1.1 行、列和单元格的定义与表示方法

3.1.2 单元格区域的定义和表示方法

3.1.3 活动单元格和选中的单元格

3.2 选择行、列和单元格的基本方法

3.2.1 选择单行或单列

3.2.2 选择连续或不连续的多行和多列

3.2.3 选择连续的单元格区域

3.2.4 选择不连续的单元格区域

3.2.5 选择距离较远的单元格或范围较大的单元格区域

3.2.6 选择多个工作表中的单元格区域

3.3 使用定位条件和查找功能快速选择特定的单元格

3.3.1 选择包含数据的单元格

3.3.2 选择包含特定内容的单元格

3.3.3 选择包含特定格式的单元格

3.3.4 选择包含相似内容的单元格

3.4 调整行、列和单元格的结构

3.4.1 插入行和列

3.4.2 设置行高和列宽

3.4.3 移动与复制行和列

3.4.4 隐藏与显示行和列

3.4.5 删除行和列

3.4.6 插入和删除单元格

3.4.7 合并和拆分单元格

第4章 输入和编辑数据

4.1 了解Excel中的数据类型

4.1.1 数值

4.1.2 文本

4.1.3 日期和时间

4.1.4 逻辑值

4.1.5 错误值

4.2 输入和编辑不同类型的数据

4.2.1 输入数据的基本方法

4.2.2 输入数值和文本

4.2.3 输入日期和时间

4.2.4 换行输入

4.2.5 转换数据类型

4.2.6 修改和删除数据

4.3 提高输入效率的技巧

4.3.1 一次性在多个单元格中输入数据

4.3.2 使用填充功能快速输入一系列数据

4.3.3 使用记忆式键入和从下拉列表中选择功能

4.3.4 自动输入小数点

4.4 使用数据验证功能限制数据的输入

4.4.1 了解数据验证

4.4.2 限制输入的数值和日期范围

4.4.3 为单元格提供包含指定选项的下拉列表

4.4.4 禁止输入重复内容

4.4.5 检查并圈释无效数据

4.4.6 管理数据验证

4.5 移动和复制数据

4.5.1 移动和复制数据的基本方法

4.5.2 使用不同的粘贴方式

4.5.3 使用Office剪贴板

4.5.4 将数据同时复制到多个工作表

4.6 导入外部数据

4.6.1 导入文本文件中的数据

4.6.2 导入Access数据库中的数据

4.6.3 使用Microsoft Query导入数据

第5章 设置数据格式

5.1 设置数据在单元格中的显示方式

5.1.1 单元格格式设置工具

5.1.2 设置字体格式

5.1.3 设置数字格式

5.1.4 自定义数字格式

5.1.5 设置数据的对齐方式

5.1.6 为数据添加边框和填充色

5.1.7 复制和清除格式

5.2 使用样式快速设置多种格式

5.2.1 使用内置样式

5.2.2 创建自定义样式

5.2.3 在其他工作簿中使用创建的单元格样式

5.3 自动为满足条件的数据设置格式

5.3.1 使用Excel内置的条件格式

5.3.2 创建自定义条件格式

5.3.3 突出显示最大值

5.3.4 突出显示重复值

5.3.5 突出显示到期数据

5.3.6 管理条件格式

第6章 打印和导出数据

6.1 设置工作表的页面格式

6.1.1 设置纸张的大小和方向

6.1.2 设置页边距

6.1.3 设置页眉和页脚

6.1.4 设置分页

6.1.5 页面布局视图和分页预览视图

6.2 打印数据

6.2.1 打印预览

6.2.2 缩放打印

6.2.3 打印行列标题

6.2.4 打印指定的数据区域和对象

6.2.5 设置其他打印选项

6.3 导出数据

第7章 公式和函数基础

7.1 公式的基本概念

7.1.1 公式的组成

7.1.2 运算符及其优先级

7.1.3 普通公式和数组公式

7.1.4 单个单元格公式和多个单元格公式

7.1.5 Excel在公式和函数方面的限制

7.2 输入和编辑公式

7.2.1 输入和修改公式

7.2.2 移动和复制公式

7.2.3 更改公式的计算方式

7.2.4 删除公式

7.2.5 A1引用样式与R1C1引用样式

7.3 在公式中使用函数

7.3.1 为什么使用函数

7.3.2 函数的类型

7.3.3 函数的参数

7.3.4 函数的易失性

7.3.5 在公式中输入函数

7.4 在公式中使用名称

7.4.1 名称的级别

7.4.2 创建名称的基本方法

7.4.3 为单元格区域创建名称

7.4.4 为公式创建名称

7.4.5 在公式中使用名称

7.4.6 在名称中使用相对引用

7.4.7 管理名称

7.5 使用数组公式

7.5.1 数组的类型

7.5.2 数组的运算方式

7.5.3 输入和编辑数组公式

7.6 在公式中引用其他工作表或工作簿中的数据

7.6.1 在公式中引用其他工作表中的数据

7.6.2 在公式中引用其他工作簿中的数据

7.6.3 在公式中引用多个工作表中的相同区域

7.7 处理公式中的错误

7.7.1 公式返回的7种错误值

7.7.2 使用公式错误检查器

7.7.3 追踪单元格之间的关系

7.7.4 监视单元格中的内容

7.7.5 使用公式进行分步求值

7.7.6 显示公式本身而非计算结果

7.7.7 循环引用

7.8 公式使用技巧

7.8.1 查看公式的中间结果

7.8.2 复制公式时使用绝对引用

7.8.3 将公式转换为值

第8章 逻辑判断和信息获取

8.1 使用逻辑函数进行条件判断

8.1.1 AND、OR和NOT函数

8.1.2 IF函数

8.1.3 IFERROR函数

8.2 使用信息函数获取信息

8.2.1 INFO函数

8.2.2 CELL函数

8.2.3 IS类函数

第9章 数学计算

9.1 常用数学函数

9.1.1 RANDBETWEEN函数

9.1.2 MOD函数

9.1.3 ROUND函数

9.1.4 INT和TRUNC函数

9.2 数学函数的实际应用

9.2.1 生成1～50的随机偶数

9.2.2 使用MOD函数判断是否是闰年

9.2.3 汇总奇数月和偶数月的销量

第10章 处理文本

10.1 常用文本函数

10.1.1 LEN和LENB函数

10.1.2 LEFT、RIGHT和MID函数

10.1.3 FIND和SEARCH函数

10.1.4 REPLACE和SUBSTITUTE函数

10.1.5 LOWER和UPPER函数

10.1.6 CHAR和CODE函数

10.1.7 TEXT函数

10.2 空单元格、空文本和空格之间的区别

10.3 文本函数的实际应用

10.3.1 计算文本中包含的数字和汉字的个数

10.3.2 将文本转换为句首字母大写其他字母小写的形式

10.3.3 提取公司名称

10.3.4 格式化公司名称

10.3.5 从身份证号码中提取出生日期

10.3.6 从身份证号码中提取性别信息

10.3.7 统计指定字符在文本中出现的次数

10.3.8 将普通数字转换为电话号码格式

10.3.9 评定员工考核成绩

10.3.10 将顺序错乱的字母按升序排列

第11章 日期和时间计算

11.1 常用日期和时间函数

11.1.1 TODAY和NOW函数

11.1.2 DATE和TIME函数

11.1.3 YEAR、MONTH和DAY函数

11.1.4 HOUR、MINUTE和SECOND函数

11.1.5 DATEVALUE和TIMEVALUE函数

11.1.6 DAYS函数

11.1.7 DATEDIF函数

11.1.8 WEEKDAY函数

11.1.9 WEEKNUM函数

11.1.10 EDATE和EOMONTH函数

11.1.11 WORKDAY.INTL函数

11.1.12 NETWORKDAYS.INTL 函数

11.2 日期和时间函数的实际应用

11.2.1 提取日期和时间

11.2.2 计算本月的总天数

11.2.3 判断闰年

11.2.4 计算母亲节和父亲节的日期

11.2.5 统计工资结算日期

11.2.6 计算员工工龄

11.2.7 计算还款日期

11.2.8 计算加班费用

11.2.9 安排会议时间

11.2.10 计算用餐时长

第12章 数据求和与统计

12.1 常用求和与统计函数

12.1.1 SUM函数

12.1.2 SUMIF和SUMIFS函数

12.1.3 SUMPRODUCT函数

12.1.4 AVERAGE、AVERAGEIF和AVERAGEIFS函数

12.1.5 COUNT和COUNTA函数

12.1.6 COUNTIF和COUNTIFS函数

12.1.7 MAX和MIN函数

12.1.8 LARGE和SMALL函数

12.1.9 RANK.EQ函数

12.1.10 FREQUENCY函数

12.1.11 SUBTOTAL函数

12.2 求和与统计函数的实际应用

12.2.1 累计求和

12.2.2 计算某部门员工的年薪总和

12.2.3 计算前两名和后两名员工的销售额总和

12.2.4 汇总指定销售额范围内的销售总额

12.2.5 计算商品打折后的总价格

12.2.6 统计不重复员工的人数

12.2.7 统计迟到人数

12.2.8 计算单日最高销量

12.2.9 计算销量前3名的销量总和

12.2.10 在筛选状态下生成连续编号

第13章 查找和引用数据

13.1 常用查找和引用函数

13.1.1 ROW和COLUMN函数

13.1.2 VLOOKUP函数

13.1.3 LOOKUP函数

13.1.4 MATCH函数

13.1.5 INDEX函数

13.1.6 INDIRECT函数

13.1.7 OFFSET函数

13.2 查找和引用函数的实际应用

13.2.1 快速输入月份

13.2.2 汇总多个列中的销量

13.2.3 从多列数据中查找员工信息

13.2.4 逆向查找

13.2.5 提取商品最后一次进货日期

13.2.6 汇总最近5天的销量

13.2.7 统计销量小于600的员工人数

13.2.8 提取文本中的数字

13.2.9 提取不重复的员工姓名

13.2.10 创建可自动扩展的二级下拉列表

第14章 财务金融计算

14.1 财务基础知识

14.1.1 货币的时间价值

14.1.2 现金的流入和流出

14.1.3 单利和复利

14.2 借贷和投资计算

14.2.1 FV函数

14.2.2 PV函数

14.2.3 PMT函数

14.2.4 RATE函数

14.2.5 NPER函数

14.3 计算本金和利息

14.3.1 PPMT和IPMT函数

14.3.2 CUMPRINC和CUMIPMT函数

第15章 排序、筛选和分类汇总

15.1 了解数据列表

15.2 排序数据

15.2.1 单条件排序

15.2.2 多条件排序

15.2.3 自定义排序

15.3 筛选数据

15.3.1 进入和退出筛选模式

15.3.2 筛选数据

15.3.3 高级筛选

15.4 分类汇总数据

15.4.1 汇总一类数据

15.4.2 汇总多类数据

15.4.3 删除分类汇总

15.5 合并计算

15.5.1 按位置汇总多个表中的数据

15.5.2 按类别汇总多个表中的数据

第16章 使用数据透视表多角度透视分析

16.1 数据透视表简介

16.1.1 什么是数据透视表

16.1.2 数据透视表的整体结构

16.1.3 数据透视表的常用术语

16.2 创建数据透视表

16.2.1 数据透视表的数据源类型

16.2.2 使用位于独立区域的数据源创建数据透视表

16.2.3 使用位于多个区域的数据源创建数据透视表

16.2.4 使用外部数据源创建数据透视表

16.2.5 创建动态的数据透视表

16.2.6 刷新数据透视表

16.3 设置数据透视表的布局和数据显示方式

16.3.1 布局字段

16.3.2 重命名字段

16.3.3 更改数据透视表的整体布局

16.3.4 为数据分组

16.3.5 设置总计的显示方式

16.4 使用切片器筛选数据

16.4.1 创建切片器

16.4.2 清除切片器的筛选状态

16.4.3 在多个数据透视表中共享切片器

16.4.4 删除切片器

16.5 计算数据透视表中的数据

16.5.1 设置数据的汇总方式和计算方式

16.5.2 创建计算字段

16.5.3 创建计算项

16.6 创建数据透视图

第17章 使用高级分析工具

17.1 模拟分析

17.1.1 单变量模拟分析

17.1.2 双变量模拟分析

17.1.3 在多组条件下进行模拟分析

17.2 单变量求解

17.3 规划求解

17.3.1 加载规划求解

17.3.2 使用规划求解分析数据

17.4 分析工具库

17.4.1 加载分析工具库

17.4.2 分析工具库中包含的分析工具

17.4.3 使用分析工具库分析数据

17.5 预测工作表

第18章 使用图表和图形展示数据

18.1 创建和编辑图表

18.1.1 Excel中的图表类型

18.1.2 图表的组成

18.1.3 嵌入式图表和图表工作表

18.1.4 创建图表

18.1.5 移动和复制图表

18.1.6 更改图表类型

18.1.7 设置图表的整体布局和配色

18.1.8 设置图表元素的格式

18.1.9 编辑数据系列

18.1.10 删除图表

18.1.11 图表制作实例

18.2 使用迷你图

18.2.1 创建迷你图

18.2.2 迷你图组合

18.2.3 更改迷你图类型

18.2.4 编辑迷你图

18.2.5 设置迷你图格式

18.2.6 删除迷你图

18.3 使用图形对象增强显示效果

18.3.1 使用图片

18.3.2 使用形状、文本框和艺术字

18.3.3 使用SmartArt

第19章 使用Power BI分析数据

19.1 在Excel中安装Power加载项

19.2 使用Power Query导入和刷新数据

19.2.1 导入不同来源的数据

19.2.2 重命名和删除查询

19.2.3 将导入的数据加载到Excel工作表中

19.2.4 刷新数据

19.2.5 更改数据源

19.3 在Power Query编辑器中整理数据

19.3.1 打开Power Query编辑器

19.3.2 将第一行数据设置为列标题

19.3.3 更改字段的数据类型

19.3.4 转换文本格式

19.3.5 提取字符

19.3.6 提取日期元素

19.3.7 拆分列

19.3.8 将二维表转换为一维表

19.3.9 将结构相同的多个表中的数据合并到一起

19.3.10 基于同一个字段合并两个表中的相关数据

19.4 使用Power Pivot为数据建模

19.4.1 将数据添加到Power Pivot中

19.4.2 创建关系

19.4.3 创建计算列

19.4.4 创建度量值

19.5 使用Power View展示数据

第20章 数据安全、协作和共享

20.1 保护工作簿和工作表

20.1.1 受保护的视图

20.1.2 删除工作簿中的隐私信息

20.1.3 将工作簿标记为最终状态

20.1.4 禁止更改工作簿的结构

20.1.5 使用密码打开和编辑工作簿

20.1.6 禁止编辑指定的单元格

20.1.7 禁止在编辑栏中显示公式

20.2 数据协作

20.2.1 添加批注

20.2.2 将工作簿共享给其他用户

20.3 与其他程序共享数据

20.3.1 在其他程序中链接Excel数据

20.3.2 编辑和更新链接对象

20.3.3 在其他程序中嵌入Excel数据

20.3.4 在Excel中链接和嵌入其他程序中的数据

20.3.5 使用“选择性粘贴”在程序之间共享数据

第21章 宏和VBA基础

21.1 VBA简介

21.1.1 为什么使用VBA

21.1.2 支持VBA的文件格式

21.1.3 允许或禁止运行VBA代码

21.2 录制和使用宏

21.3 VBA编程基础

21.3.1 编写VBA代码的界面环境

21.3.2 VBA程序的基本结构

21.3.3 变量、常量和数据类型

21.3.4 变量的作用域和生存期

21.3.5 Sub过程和Function过程

21.3.6 分支结构

21.3.7 循环结构

21.3.8 数组

21.3.9 理解对象模型

21.3.10 创建和销毁对象

21.3.11 处理集合中的对象

21.3.12 使用对象的属性和方法

第22章 编程处理工作簿、工作表和单元格

22.1 编程处理工作簿

22.1.1 引用工作簿

22.1.2 新建工作簿

22.1.3 打开工作簿

22.1.4 保存和另存工作簿

22.1.5 关闭工作簿

22.1.6 获取工作簿的路径和名称

22.2 编程处理工作表

22.2.1 Worksheets集合与Sheets集合

22.2.2 引用工作表

22.2.3 新建工作表

22.2.4 重命名工作表

22.2.5 移动工作表

22.2.6 复制工作表

22.2.7 删除工作表

22.3 编程处理单元格和区域

22.3.1 引用单元格

22.3.2 引用连续或不连续的单元格区域

22.3.3 引用行

22.3.4 引用列

22.3.5 通过偏移引用新的单元格或单元格区域

22.3.6 扩大或缩小引用区域的范围

22.3.7 引用独立的数据区域

22.3.8 引用工作表中的已使用区域

22.3.9 在单元格区域中读写数据

第23章 使用事件编程

23.1 理解事件

23.1.1 事件的类型及优先级

23.1.2 事件代码的存储位置和输入方法

23.1.3 在事件中使用参数

23.1.4 开启和关闭事件的触发机制

23.2 使用工作簿事件

23.2.1 工作簿事件一览

23.2.2 Open事件

23.2.3 BeforeClose事件

23.2.4 BeforePrint事件

23.2.5 SheetActivate事件

23.2.6 NewSheet事件

23.2.7 SheetChange事件

23.2.8 SheetSelectionChange事件

23.3 使用工作表事件

23.3.1 工作表事件一览

23.3.2 Change事件

23.3.3 SelectionChange事件

第24章 使用窗体和控件

24.1 创建和设置用户窗体

24.1.1 了解用户窗体

24.1.2 创建用户窗体

24.1.3 设置用户窗体的属性

24.1.4 显示和关闭用户窗体

24.1.5 模式和无模式用户窗体

24.1.6 禁用用户窗体中的关闭按钮

24.2 在用户窗体中使用控件

24.2.1 控件类型

24.2.2 在用户窗体中添加控件

24.2.3 设置控件的属性

24.2.4 调整控件的大小

24.2.5 调整控件的位置

24.2.6 设置控件的Tab键顺序

24.3 为用户窗体和控件编写事件代码

24.3.1 引用用户窗体中的控件

24.3.2 用户窗体中的控件集合

24.3.3 用户窗体事件

24.3.4 控件事件

24.3.5 使用用户窗体和控件制作颜色选择器

第25章 创建自定义函数和加载项

25.1 创建和使用自定义函数

25.1.1 理解Function过程中的参数

25.1.2 创建自定义函数

25.1.3 自定义函数实例

25.2 创建和管理加载项

25.2.1 了解加载项

25.2.2 创建加载项

25.2.3 安装和卸载加载项

25.2.4 修改加载项

25.2.5 删除加载项

附录1 Excel规范和限制

附录2 Excel函数速查表

附录3 Excel快捷键