Excel 高效办公应用与技巧大全

Excel 是微软公司开发的Office 办公软件中的电子表格软件，它具有强大的数据统计与分析功能，被广泛地应用于日常工作中，尤其对市场销售、财务会计、人力资源、行政文秘等相关岗位来说，更是必不可少的得力助手。本书系统、全面地讲解了Excel 在日常办公中的应用技巧。在内容安排上，本书特点就是在指导读者做到“会用”的基础上，重在传教如何“用好”Excel 来进行高效办公。

《Excel 高效办公应用与技巧大全》共分为17 章，内容包括Excel 的基本操作与设置技巧，工作簿与工作表的管理技巧，表格数据的录入与编辑技巧，工作表的格式设置技巧，公式的应用技巧，函数（常用函数、财务函数、数学函数、日期与时间函数、文本函数、逻辑函数、统计函数、查找函数等）的应用技巧，数据和排序与筛选应用技巧，数据分析、预测与汇总技巧，使用图表分析数据的技巧，数据透视表与数据透视图的应用技巧，宏与VBA 的使用技巧，以及Excel 页面设置与打印技巧。

本书内容系统全面，案例丰富，可操作性强。全书结合微软Excel 常用版本（如Excel 2007、2010、2013、2016），以技巧罗列的形式进行编写，非常适合读者阅读与查询使用，是不可多得的职场办公必备案头工具书。

本书非常适合读者自学使用，尤其适合对Excel 软件使用技巧缺少经验的读者学习使用，也可以作为大、中专职业院校计算机相关专业的教学参考用书。

IT 新时代教育由一线办公专家和多位 MVP（微软全球*有价值专家）教师合作组成，他们具有丰富的Office 软件应用技巧和办公实战经验，对于他们的辛苦付出在此表示衷心的感谢！

在工作中，是否经常面对如下问题：

? 工作任务堆积如山，别人使用Excel 工作很高效、很专业，我怎么不行？

? 使用Excel 处理数据时，总是遇到这样那样的问题，百度搜索多遍，依然找不到需要的答案，怎么办？

? 想成为Excel 办公高手，要把数据处理与分析工作及时、高效地做好，却不懂一些Excel办公技巧，怎么办？

? 工作方法有讲究，提高效率有捷径。通过对本书的学习，了解一些Excel 办公技巧，可以让你在工作中节省很多时间，解除你工作中的烦恼，让你少走许多弯路！

花一本书的钱，买到的不仅仅是一本书，而是一套超值的综合学习套餐，内容包括：图书+同步学习素材+ 同步视频教程+ 办公模板+《电脑入门必备技能手册》+Office 快速入门视频教程+ 《Office 办公应用快捷键速查表》。多维度学习套餐，真正超值实用！

1 同步视频教程。配有与书同步的高质量、超清晰的多媒体视频教程，时长达12 小时，扫描书中二维码，即可使用手机同步学习。

2 同步学习素材。提供了书中所有案例的素材文件，方便读者跟着书中讲解同步练习操作。

3 赠送：1000 个Office 商务办公模板文件，包括Word 模板、Excel 模板、PPT 模板文件，拿来即用，不用再去花时间与精力搜集整理。

4 赠送：《电脑入门必备技能手册》，即使不懂计算机，也可以通过本手册的学习，掌握计算机入门技能，更好地学习Office 办公应用技能。

5 赠送：3 小时的Office 快速入门视频教程，即使一点基础都没有，也不用担心学不会，学完此视频就能快速入门。

6 赠送：《Office 办公应用快捷键速查表》，快速提高办公效率。

你知道吗？

在工作中，是否经常面对如下问题：

工作任务堆积如山，别人使用Excel 工作很高效、很专业，我怎么不行？

使用Excel 处理数据时，总是遇到这样那样的问题，百度搜索多遍，依然找不到需要的答案，怎么办？

想成为Excel 办公高手，要把数据处理与分析工作及时、高效地做好，却不懂一些Excel办公技巧，怎么办？

工作方法有讲究，提高效率有捷径。通过对本书的学习，了解一些Excel 办公技巧，可以让你在工作中节省很多时间，解除你工作中的烦恼，让你少走许多弯路！

本书内容

本书适合有一定Excel 基础的学员，目的在于帮助职场人士进一步提高活用、巧用Excel 的能力，高效解决工作中的制表、数据处理与分析等难题，真正实现早做完不加班！

全书共分为17 章，内容包括Excel 的基本操作与设置技巧，工作簿与工作表的管理技巧，表格数据的录入与编辑技巧，工作表的格式设置技巧，公式的应用技巧，函数（常用函数、财务函数、数学函数、时间函数、逻辑函数、统计函数等）的应用技巧，数据的排序与筛选技巧，数据分析、预测与汇总，使用图表分析数据的技巧，数据透视表与数据透视图的应用技巧，宏与VBA 的应用技巧，以及Excel 页面设置与打印技巧。

本书内容系统全面，案例丰富，可操作性强。全书结合微软Excel 常用版本（如Excel2007、2010、2013、2016），以技巧罗列的形式进行编写，非常适合读者阅读与查询使用，是不可多得的职场办公必备案头工具书。

通过本书的学习，你将由“菜鸟”变为“高手”。以前，你只会简单地运用Excel 软件，现在，你可以：

√ 5 分钟制作专业报表，灵活录入、编辑各类数据，合理打印设置文件；

√ 使用公式函数解决实际问题，对于数学函数、财务函数及其他常用函数，都应用自如；

√ 使用Excel 排序与筛选功能分析简单问题，使用数据透视表、图表分析复杂问题。

本书特色

花一本书的钱，买到的不仅仅是一本书，而是一套超值的综合学习套餐，内容包括：图书+同步学习素材+ 同步视频教程+ 办公模板+《电脑入门必备技能手册》+Office 快速入门视频

教程+ 《Office 办公应用快捷键速查表》。多维度学习套餐，真正超值实用！

1 同步视频教程。配有与书同步的高质量、超清晰的多媒体视频教程，时长达12 小时，扫描书中二维码，即可使用手机同步学习。

2 同步学习素材。提供了书中所有案例的素材文件，方便读者跟着书中讲解同步练习操作。

3 赠送：1000 个Office 商务办公模板文件，包括Word 模板、Excel 模板、PPT 模板文件，拿来即用，不用再去花时间与精力搜集整理。

4 赠送：《电脑入门必备技能手册》，即使不懂计算机，也可以通过本手册的学习，掌握计算机入门技能，更好地学习Office 办公应用技能。

5 赠送：3 小时的Office 快速入门视频教程，即使一点基础都没有，也不用担心学不会，学完此视频就能快速入门。

6 赠送：《Office 办公应用快捷键速查表》，快速提高办公效率。

读者对象

有一点Excel 的基础知识，但无法高效应用Excel 的职场人士。

想快速拥有一门高效办公的核心技能，找到好工作的毕业生。

需要提高办公技能的行政、文秘人员。

需要精通Excel 的人力资源、销售和财会等人员。

本书由IT 新时代教育策划并组织编写。全书由一线办公专家和多位 MVP（ 微软全球*有价值专家）教师合作编写，他们具有丰富的Office 软件应用技巧和办公实战经验，对于他们的辛苦付出在此表示衷心的感谢！同时，由于计算机技术发展非常迅速，书中疏漏和不足之处在所难免，敬请广大读者及专家指正。

—— 第1章 ——

Excel 的基础操作技巧

1.1优化 Excel 的工作环境

001 在快速访问工具栏中添加常用工具按钮

002 将常用命令按钮添加到快速访问工具栏

003 在工具栏添加新的选项卡

004 快速显示或隐藏功能区

005 将功能区同步到其他计算机

006 更改Enter 键的功能

007 设置Excel 最近使用的文档个数

008 更改工作簿的默认保存路径

009 更改新建工作簿的默认字体与字号

010 更改Excel 的默认工作表数量

011 将常用工作簿固定在最近使用列表中

012 自定义状态栏上显示的内容

1.2Excel 窗口的设置技巧

013 调整文档的显示比例

014 如何分页预览工作表

015 对两个工作表进行并排查看

016 通过拆分窗格来查看表格数据

017 隐藏工作簿窗口

018 通过冻结功能让标题行和列在滚动时始终显示

—— 第2章 ——

工作簿与工作表的管理技巧

2.1工作簿的基本操作

019 使用模板快速创建工作簿

020 将Excel 工作簿保存为模板文件

021 将工作簿保存为低版本

可以打开的文件

022 将低版本文档转换为高版本文档

023 将工作簿保存为PDF 格式的文档

024 如何将工作簿标记为最终状态

025 在受保护视图中打开工作簿

026 启动Excel 时自动打开指定的工作簿

2.2工作表的管理

027 快速切换工作表

028 一次性插入多个工作表

029 重命名工作表

030 设置工作表标签颜色

031 调整工作表的排列顺序

032 复制工作表

033 将工作表移动到新工作簿中

034 删除工作表

035 隐藏与显示工作表

036 全屏显示工作表内容

2.3保护工作簿与工作表

037 为工作簿设置打开密码

038 为工作簿设置修改密码

039 防止工作簿结构被修改

040 保护工作表不被他人修改

041凭密码编辑工作表的不同区域

042 清除工作簿的个人信息

2.4行、列和单元格的编辑操作

043 快速插入多行或多列

044 交叉插入行

045 隔行插入空行

046 在插入或删除行时保持编号连续

047 精确设置行高与列宽

048 设置最适合的行高与列宽

049 隐藏与显示行或列

050 快速删除所有空行

051 巧用双击定位到列表的最后一行

052 使用名称框定位活动单元格

053 选中所有数据类型相同的单元格

054 隐藏重要单元格中的内容

055 将计算结果为0 的数据隐藏

056 合并两列数据并自动删除重复值

057 为单元格添加批注

—— 第3章 ——

表格数据的录入技巧

3.1快速输入特殊数据

058 输入身份证号码

059 输入分数

060 输入以0 开头的数字编号

061 巧妙输入位数较多的员工编号

062 快速输入部分重复的内容

063 快速输入大写中文数字

064 对手机号码进行分段显示

065 利用记忆功能快速输入数据

066 快速输入系统日期和系统时间

067 快速在多个单元格中输入相同数据

068 在多个工作表中同时输入相同数据

3.2数据的快速填充和导入技巧

069 利用填充功能快速输入相同数据

070 使用填充功能快速输入序列数据

071 自定义填充序列

072 快速填充所有空白单元格

073 如何自动填充日期值

074 从Access 文件导入数据到工作表

075 从网页导入数据到工作表

076 从文本文件导入数据到工作表

3.3设置数据的有效性

077 只允许在单元格中输入数字

078 为数据输入设置下拉选择列表

079 限制重复数据的输入

080 设置单元格文本的输入长度

081 设置单元格数值输入范围

082 限定单元格输入小数不超过2 位

083 设置使单元格只能输入汉字

084 圈释表格中无效的数据

085 设置输入数据前的提示信息

086 设置数据输入错误的警告信息

087 设置在具有数据有效性的

单元格中输入非法值

088 只复制单元格中的数据验证设置

089 快速清除数据验证

—— 第4章 ——

表格数据的快速编辑技巧

4.1查找与替换数据

090快速修改多处同一错误的内容

091查找和替换公式

092在查找时区分大小写

093使用通配符查找数据

094设置查找范围提高搜索效率

095为查找到的数据设置指定格式

096清除某种特定格式的单元格内容

4.2复制与粘贴数据

097让粘贴数据随源数据自动更新

098将单元格区域复制为图片

099将数据复制为关联图片

100在粘贴数据时对数据进行目标运算

101将表格行或列数据进行转置

102快速删除表格区域中的重复数据

103使用分列功能分列显示数据

4.3链接数据

104设置工作表之间的超链接

105创建指向文件的超链接

106创建指向网页的超链接

107 选择超链接后不激活该链接

108更改超链接文本的外观

109删除单元格中的超链接

110阻止Excel 自动创建超链接

—— 第5章 ——

工作表的格式设置技巧

5.1设置单元格格式

111设置小数位数

112快速增加/ 减少小数位数

113将数字按小数点对齐

114快速为数据添加文本单位

115对不同范围的数值设置不同颜色

116对单元格中的数据进行强制换行

117缩小字体填充单元格

118控制单元格的文字方向

119快速修改日期格式

120使输入的负数自动以红色显示

121使用格式刷快速复制单元格格式

5.2美化工作表格式

122手动绘制表格边框

123设置个性化设置单元格背景

124将表格设置为三维效果

125设置具有立体感的单元格

126将图片设置为工作表背景

127制作斜线表头

128快速套用单元格样式

129自定义单元格样式

130批量修改应用了同一样式的单元格样式

131将单元格样式应用到其他工作簿

132使用表格样式快速美化表格

133自定义表格样式

5.3使用图形和图片

134插入屏幕截图

135裁剪图片以突出主体

136设置图片的艺术效果

137压缩图片减小文件大小

138在单元格中固定图片大小及位置

139插入艺术字，突出标题

140设置艺术字的格式

141插入文本框添加文字

142设置文本框的样式

143插入SmartArt 图形

144更改 SmartArt 图形布局

145快速美化SmartArt 图形

—— 第6章 ——

公式的应用技巧

6.1公式的引用

146复制公式

147单元格的相对引用

148单元格的绝对引用

149单元格的混合引用

150引用同一工作簿中其他工作表的单元格

151引用其他工作簿中的单元格

152保护公式不被修改

153将公式隐藏起来

154使用“&”合并单元格内容

155为何公式下拉复制后计算结果都一样

6.2公式中如何引用名称

156为单元格定义名称

157将自定义名称应用于公式

158使用单元格名称对数据进行计算

159使用名称管理器管理名称

6.3使用数组计算数据

160在多个单元格中使用数组公式

进行计算

161在单个单元格中使用数组公式

进行计算

162扩展数组公式

163对数组中N 个最大值进行求和

6.4公式审核与错误处理

164追踪引用单元格与追踪从属单元格

165对公式中的错误进行追踪操作

166使用公式求值功能查看公式分步计算结果

167用错误检查功能检查公式

168使用【监视窗口】来监视公式及其结果

169设置公式错误检查选项

170#### 错误的处理办法

171#NULL! 错误的处理办法

172#NAME? 错误的处理办法

173#NUM! 错误的处理办法

174#VALUE! 错误的处理办法

175#DIV/0! 错误的处理办法

176#REF! 错误的处理办法

177#N/A 错误的处理办法

178通过【Excel 帮助】获取错误解决办法

—— 第7章 ——

函数的基本应用技巧

7.1函数的调用

179在单元格中直接输入函数

180通过提示功能快速输入函数

181通过【函数库】输入函数

182使用【自动求和】按钮输入函数

183通过【插入函数】对话框调用函数

184不知道需要使用什么函数时应如何查询

185使用嵌套函数计算数据

186自定义函数

7.2常用函数的应用

187使用SUM 函数进行求和运算

188使用AVERAGE 函数计算平均值

189使用MAX 函数计算最大值

190使用MIN 计算最小值

191使用RANK 函数计算排名

192使用COUNT 函数计算参数中包含的个数

193使用PRODUCT 函数计算乘积

194使用IF 函数执行条件检测

—— 第8章 ——

财务函数的应用技巧

8.1投资预算与收益函数的应用

195使用FV 函数计算投资的未来值

196使用PV 函数计算投资的现值

197使用NPV 函数计算投资净现值

198使用NPER 函数计算投资的期数

199使用XNPV 函数计算现金流的净现值

200使用IRR 函数计算一系列现金流的内部收益率

201使用XIRR 函数计算现金流计划的内部收益率

202使用MIRR 函数计算正、负现金流在不同利率下支付的内部收益率

203使用FVSCHEDULE 函数计算某投资在利率变化下的未来值

8.2本金和利息函数的应用

204使用CUMIPMT 函数计算两个付款期之间累计支付的利息

205使用CUMPRINC 函数计算两个付款期之间累计支付的本金

206使用PMT 函数计算月还款额

207使用PPMT 函数计算贷款在给定期间内偿还的本金

208使用IPMT 函数计算贷款在给定期间内支付的利息

209使用ISPMT 函数计算特定投资期内支付的利息

210使用RATE 函数计算年金的各期利率

211使用EFFECT 函数计算有效的年利率

212使用NOMINAL 函数计算名义年利率

8.3证券函数的应用

213使用COUPDAYS 函数计算成交日所在的付息期的天数

214使用COUPDAYBS 函数计算当前付息期内截止到成交日的天数

215使用COUPNUM 函数计算成交日和到期日之间的应付利息次数

216使用COUPDAYSNC 函数计算从成交日到下一个付息日之间的天数

217使用COUPPCD 函数计算成交日之前的上一付息日

218使用COUPNCD 函数计算成交日之后的下一个付息日

219使用ACCRINT 函数计算定期支付利息的有价证券的应计利息

220使用ACCRINTM 函数计算在到期日支付利息的有价证券的应计利息

8.4 折旧函数的应用

221使用DB 函数计算给定时间内的折旧值

222使用SLN 函数计算线性折旧值

223使用SYD 函数按年限计算资产折旧值

224使用VDB 函数计算任何时间段的折旧值

225使用DDB 函数按双倍余额递减法计算折旧值

226使用AMORLINC 函数计算每个结算期间的折旧值

227使用AMORDEGRC 函数计算每个结算期间的折旧值

—— 第9章 ——

文本函数、逻辑函数、时间函数的应用技巧

9.1文本函数与逻辑函数的应用

228使用MID 函数从文本指定位置起提取指定个数的字符

229使用RIGHT 函数从文本右侧起提取指定个数的字符

230使用LEFT 函数从文本左侧起提取指定个数的字符

231使用LEFTB 函数从文本左侧起提取指定字节数字符

232使用RIGHTB 函数从文本右侧起提取指定字节数字符

233使用EXACT 函数比较两个字符串是否相同

234使用CONCATENATE 函数将多个字符串合并到一处

235使用RMB 函数将数字转换为带人民币符号￥的文本

236使用VALUE 函数将文本格式的数字转换为普通数字

237从身份证号码中提取出生日期和性别

238快速从文本右侧提取指定数量的字符

239只显示身份号码后四位数

240使用TRUE 函数与FALSE 函数返回逻辑值

241使用AND 函数判断指定的多个条件是否同时成立

242使用OR 函数判断多个条件中是否至少有一个条件成立

243使用NOT 函数对逻辑值求反

244录用条件合格的员工

9.2日期与时间函数的应用

245使用YEAR 函数返回年份

246使用MONTH 函数返回月份

247使用DAY 函数返回某天数值

248使用WEEKDAY 函数返回一周中的第几天的数值

249使用EDATE 函数返回指定日期

250使用HOUR 函数返回小时数

251使用MINUTE 函数返回分钟数

252使用SECOND 函数返回秒数

253使用NETWORKDAYS 函数返回两个日期间的全部工作日数

254使用WORKDAY 函数返回若干工作日之前或之后的日期

255使用TODAY 函数显示当前日期

256使用NOW 函数显示当前日期和时间

257计算两个日期之间的年份数

258计算两个日期之间的月份数

259将时间值换算为秒数

260计算员工年龄和工龄

261计算还款时间

262计算还款倒计时

—— 第10章 ——

数学函数的应用技巧

10.1常规数学计算函数应用技巧

263使用SUMIF 函数进行条件求和

264使用SUMIFS 函数对一组给定条件

指定的单元格求和

265使用RAND 函数制作随机抽取表

266使用RANDBETWEEN 函数

返回两个指定数之间的一个随机数

267使用POWER 函数计算数据

268使用SIGN 函数获取数值的符号

269使用ABS 函数计算数值的绝对值

270统计员工考核成绩波动情况

271使用SUMPRODUCT 函数计算对应的数组元素的乘积和

272使用AGGREGATE 函数返回列表或数据库中的聚合数据

273统计考核成绩排在第1 位的员工姓名

274使用SUBTOTAL 函数返回列表或数据库中的分类汇总

275使用SQRT 函数返回正平方根

276使用ROMAN 函数将阿拉伯数字转换成文本式罗马数字

277使用罗马数字输入当前员工编号

278使用GCD 函数计算最大公约数

279使用LCM 函数计算最小公倍数

280使用FACT 函数返回数字的阶乘

281使用COMBIN 函数返回给定数目对象的组合数

282随机抽取员工姓名

10.2舍入与取整函数的应用

283使用ROUND 函数对数据

进行四舍五入

284使用QUOTIENT 函数计算除法的整数部分

285使用MOD 函数计算除法的余数

286使用INT 函数将数字向下舍入到最接近的整数

287根据工资数额统计各种面额需要的张数

288使用MROUND 函数返回一个舍入到所需倍数的数字

289使用TRUNC 函数返回数字的整数部分

290使用ODD 函数将数字向上舍入为最接近的奇数

291使用EVEN 函数将数字向上舍入为最接近的偶数

292使用CEILING 函数按条件向上舍入

293使用ROUNDUP 函数向绝对值增大的方向舍入数字

294使用ROUNDDOWN 函数

向绝对值减小的方向舍入数字

295使用FLOOR 函数向下舍入数字

296使用FLOOR.MATH 函数将数据向下取舍求值

297使用CEILING.MATH 函数将数据向上舍取求值

—— 第11章 ——

统计与查找函数的应用技巧

11.1统计函数应用技巧

11.2查找函数的应用

—— 第12章 ——

数据的排序与筛选应用技巧

12.1 如何对数据快速排序

122数据筛选技巧

—— 第13章 ——

数据分析、预测与汇总技巧

131使用条件格式分析数据

132数据合并计算与预测分析

133数据汇总与分析技巧

—— 第14章 ——

使用图表分析数据的技巧

141创建正确图表

142添加辅助线分析数据

143迷你图的创建与编辑技巧

—— 第15章 ——

数据透视表与数据透视图的应用技巧

151数据透视表的应用技巧

152数据透视图的应用技巧

—— 第16章 ——

宏与VBA 的应用技巧

161宏的应用技巧

162VBA 的应用技巧

—— 第17章 ——

Excel 页面设置与打印技巧

171工作表的页面设置

172正确打印工作表