Excel高效办公应用与技巧大全 即用即查 实战精粹PDF电子书下载

第1章 Excel的基础操作技巧 2

1.1优化Excel的工作环境 2

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

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

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

004快速显示或隐藏功能区 4

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

006更改Enter键的功能 6

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

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

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

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

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

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

1.2 Excel窗口的设置技巧 8

013调整文档的显示比例 8

014如何分页预览工作表 9

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

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

017隐藏工作簿窗口 11

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

第2章 工作簿与工作表的管理技巧 14

2.1工作簿的基本操作 14

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

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

021将工作簿保存为低版本可以打开的文件 16

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

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

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

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

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

2.2工作表的管理 19

027快速切换工作表 19

028一次性插入多个工作表 20

029重命名工作表 20

030设置工作表标签颜色 21

031调整工作表的排列顺序 21

032复制工作表 22

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

034删除工作表 23

035隐藏与显示工作表 23

036全屏显示工作表内容 24

2.3保护工作簿与工作表 25

037为工作簿设置打开密码 25

038为工作簿设置修改密码 26

039防止工作簿结构被修改 26

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

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

042清除工作簿的个人信息 29

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

043快速插入多行或多列 30

044交叉插入行 31

045隔行插入空行 31

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

047精确设置行高与列宽 33

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

049隐藏与显示行或列 34

050快速删除所有空行 35

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

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

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

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

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

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

057为单元格添加批注 39

第3章 表格数据的录入技巧 42

3.1快速输入特殊数据 42

058输入身份证号码 42

059输入分数 42

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

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

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

063快速输入大写中文数字 45

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

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

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

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

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

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

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

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

071自定义填充序列 49

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

073如何自动填充日期值 51

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

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

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

3.3设置数据的有效性 55

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

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

079限制重复数据的输入 56

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

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

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

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

084圈释表格中无效的数据 59

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

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

087设置在具有数据有效性的单元格中输入非法值 61

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

089快速清除数据验证 62

第4章 表格数据的快速编辑技巧 65

4.1查找与替换数据 65

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

091查找和替换公式 65

092在查找时区分大小写 66

093使用通配符查找数据 66

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

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

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

4.2复制与粘贴数据 69

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

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

099将数据复制为关联图片 70

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

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

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

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

4.3链接数据 73

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

105创建指向文件的超链接 74

106创建指向网页的超链接 75

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

108更改超链接文本的外观 76

109删除单元格中的超链接 77

110阻止Excel自动创建超链接 77

第5章 工作表的格式设置技巧 79

5.1设置单元格格式 79

111设置小数位数 79

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

113将数字按小数点对齐 80

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

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

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

117缩小字体填充单元格 83

118控制单元格的文字方向 84

119快速修改日期格式 84

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

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

5.2美化工作表格式 86

122手动绘制表格边框 86

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

124将表格设置为三维效果 88

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

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

127制作斜线表头 90

128快速套用单元格样式 91

129自定义单元格样式 91

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

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

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

133自定义表格样式 94

5.3使用图形和图片 96

134插入屏幕截图 96

135裁剪图片以突出主体 97

136设置图片的艺术效果 97

137压缩图片减小文件大小 98

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

139插入艺术字，突出标题 99

140设置艺术字的格式 99

141插入文本框添加文字 100

142设置文本框的样式 101

143插入SmartArt图形 101

144更改SmartArt图形布局 102

145快速美化SmartArt图形 103

第6章 公式的应用技巧 105

6.1公式的引用 105

146复制公式 105

147单元格的相对引用 105

148单元格的绝对引用 106

149单元格的混合引用 106

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

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

152保护公式不被修改 108

153将公式隐藏起来 109

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

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

6.2公式中如何引用名称 110

156为单元格定义名称 110

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

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

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

6.3使用数组计算数据 114

160在多个单元格中使用数组公式进行计算 114

161在单个单元格中使用数组公式进行计算 114

162扩展数组公式 115

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

6.4公式审核与错误处理 116

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

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

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

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

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

169设置公式错误检查选项 120

170 ＃＃＃＃错误的处理办法 121

171 ＃NULL！错误的处理办法 121

172 ＃NAME？错误的处理办法 121

173 ＃NUM！错误的处理办法 121

174 ＃VALUE！错误的处理办法 122

175 ＃DIV/0！错误的处理办法 122

176 ＃REF！错误的处理办法 122

177 ＃N/A错误的处理办法 122

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

第7章 函数的基本应用技巧 125

7.1函数的调用 125

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

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

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

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

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

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

185使用嵌套函数计算数据 129

186自定义函数 130

7.2常用函数的应用 131

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

188使用AVERAGE函数计算平均值 131

189使用MAX函数计算最大值 132

190使用MIN计算最小值 132

191使用RANK函数计算排名 133

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

193使用PRODUCT函数计算乘积 134

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

第8章 财务函数的应用技巧 138

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

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

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

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

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

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

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

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

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

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

8.2本金和利息函数的应用 142

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

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

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

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

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

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

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

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

212使用NOMINAL函数 146

计算名义年利率 146

8.3证券函数的应用 147

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

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

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

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

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

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

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

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

8.4折旧函数的应用 150

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

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

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

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

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

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

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

第9章 文本函数、逻辑函数、时间函数的应用技巧 157

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

244录用条件合格的员工 166

9.2日期与时间函数的应用 166

245使用YEAR函数返回年份 167

246使用MONTH函数返回月份 167

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

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

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

250使用HOUR函数返回小时数 169

251使用MINUTE函数返回分钟数 170

252使用SECOND函数返回秒数 170

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

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

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

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

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

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

259将时间值换算为秒数 174

260计算员工年龄和工龄 174

261计算还款时间 175

262计算还款倒计时 176

第10章 数学函数的应用技巧 178

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

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

264使用SUMIFS函数对一组给定条件指定的单元格求和 178

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

266使用RANDBETWEEN函数返回两个指定数之间的一个随机数 179

267使用POWER函数计算数据 180

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

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

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

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

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

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

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

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

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

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

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

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

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

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

282随机抽取员工姓名 188

10.2舍入与取整函数的应用 189

283使用ROUND函数对数据进行四舍五入 189

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

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

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

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

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

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

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

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

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

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

294使用ROUNDDOWN函数向绝对值减小的方向舍入数字 195

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

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

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

第11章 统计与查找函数的应用技巧 200

11.1统计函数应用技巧 200

298使用AVERAGE函数计算平均值 200

299使用AVERAGEIF函数计算指定条件的平均值 200

300使用AVERAGEIFS函数计算多条件平均值 201

301使用TRIMMEAN函数返回一组数据的修剪平均值 202

302使用COUNTA函数统计非空单元格 202

303使用COUNTIF函数进行条件统计 203

304使用COUNTBLANK函数统计空白单元格 203

305使用COUNTIFS函数进行多条件统计 204

306使用FREQUENCY函数分段统计员工培训成绩 204

307使用MODE.SNGL函数返回在数据集内出现次数最多的值 205

308使用MODE.MULT函数返回阵列中出现频率最多的垂直数组 205

309使用LARGE函数返回第k个最大值 205

310使用SMALL函数返回第k个最小值 206

11.2查找函数的应用 206

311使用CHOOSE函数基于索引号返回参数列表中的数值 206

312使用LOOKUP函数以向量形式仅在单行单列中查找 207

313使用VLOOKUP函数在区域或数组的列中查找数据 208

314使用VLOOKUP函数制作工资条 208

315使用HLOOKUP函数在区域或数组的行中查找数据 210

316使用INDEX函数在引用或数组中查找 210

317使用MATCH函数在引用或数组中查找值 211

第12章 数据的排序与筛选应用技巧 214

12.1如何对数据快速排序 214

318按一个关键字快速排序表格数据 214

319使用多个关键字排序表格数据 214

320让表格中的文本按字母顺序排序 215

321按笔画数进行排序 215

322按行进行排序 216

323按单元格背景颜色进行排序 217

324通过自定义序列排序数据 218

325对表格数据进行随机排序 218

326对合并单元格相邻的数据区域进行排序 219

327利用排序法制作工资条 219

328分类汇总后按照汇总值进行排序 220

12.2数据筛选技巧 221

329使用一个条件筛选 221

330使用多个条件筛选 222

331筛选销售成绩靠前的数据 223

332快速按目标单元格的值或特征进行筛选 224

333利用筛选功能快速删除空白行 224

334在自动筛选时让日期不按年月日分组 225

335对日期按星期进行筛选 226

336按文本条件进行筛选 227

337在文本筛选中使用通配符进行模糊筛选 228

338使用搜索功能进行筛选 228

339按单元格颜色进行筛选 229

340以单元格颜色为条件进行求和计算 229

341对双行标题的工作表进行筛选 230

342对筛选结果进行排序整理 230

343使用多个条件进行高级筛选 231

344将筛选结果复制到其他工作表中 232

345高级筛选不重复的记录 233

第13章 数据分析、预测与汇总技巧 235

13.1使用条件格式分析数据 235

346突出显示符合特定条件的单元格 235

347突出显示高于或低于平均值的数据 235

348突出显示排名前几位的数据 236

349突出显示重复数据 237

350突出显示部门总经理名册记录 238

351用不同颜色显示不同范围的值 239

352复制条件格式产生的颜色 239

353使用数据条表示不同级别人员的工资 240

354让数据条不显示单元格数值 240

355用图标把考试成绩等级形象地表示出来 241

356调整条件格式的优先级 242

357如果为真则停止 242

358只在不合格的单元格上显示图标集 243

359利用条件格式突出显示双休日 244

360快速将奇数行和偶数行用两种颜色来区分 245

361标记特定年龄段的人员 247

13.2数据合并计算与预测分析 247

362对单张工作表的数据进行合并计算 247

363对多张工作表的数据进行合并计算 248

364进行单变量求解 249

365使用方案管理器 250

366使用单变量模拟运算表分析数据 251

367使用双变量模拟运算表分析数据 252

368使用模拟运算表制作九九乘法表 254

369以最低成本购买固定数量的礼品 255

370使用方案管理器分析贷款方式 257

13.3数据汇总与分析技巧 259

371创建分类汇总 259

372更改分类汇总 260

373将汇总项显示在数据上方 260

374对表格数据进行嵌套分类汇总 261

375对表格数据进行多字段分类汇总 262

376复制分类汇总结果 263

377分页存放汇总结果 264

378删除分类汇总 264

379自动建立组分级显示 265

380隐藏/显示明细数据 265

381将表格中的数据转换为列表 266

382对列表中的数据进行汇总 267

383更改汇总行的计算函数 268

第14章 使用图表分析数据的技巧 270

14.1创建正确图表 270

384根据数据创建图表 270

385更改已创建图表的类型 270

386在一个图表使用多个图表类型 271

387在图表中增加数据系列 272

388精确选择图表中的元素 273

389更改图表的数据源 273

390分离饼形图扇区 274

391将隐藏的数据显示到图表中 274

392快速显示和隐藏图表元素 275

393更改图表元素的显示位置 276

394设置图表标题 276

395设置饼图的标签值类型 276

396在饼状图中让接近0％的数据隐藏起来 277

397设置纵坐标的刻度值 278

398将图表移动到其他工作表 279

399隐藏图表 279

400切换图表的行列显示方式 280

401将图表转换为图片 280

402设置图表背景 281

403让鼠标悬停时不显示数据点的值 281

404将图表保存为PDF文件 282

405制作可以选择的动态数据图表 283

406设置条件变色的数据标签 285

14.2添加辅助线分析数据 285

407突出显示折线图表中的最大值和最小值 285

408在图表中添加趋势线 287

409更改趋势线类型 287

410给图表添加误差线 288

411更改误差线类型 288

412为图表添加折线 289

413在图表中添加涨/跌柱线 289

414改变涨柱和跌柱的位置 290

415在图表中筛选数据 290

14.3迷你图的创建与编辑技巧 291

416创建迷你图 291

417一次性创建多个迷你图 292

418更改迷你图的数据源 293

419更改迷你图类型 293

420突出显示迷你图中的重要数据节点 294

421对迷你图设置标记颜色 294

第15章 数据透视表与数据透视图的应用技巧 297

15.1数据透视表的应用技巧 297

422快速创建数据透视表 297

423创建带内容、格式的数据透视表 298

424重命名数据透视表 299

425更改数据透视表的数据源 299

426添加或删除数据透视表字段 299

427查看数据透视表中的明细数据 300

428更改数据透视表字段位置 300

429在数据透视表中筛选数据 301

430更改数据透视表的汇总方式 302

431利用多个数据源创建数据透视表 303

432更新数据透视表中的数据 305

433对数据透视表中的数据进行排序 306

434在数据透视表中显示各数占总和的百分比 306

435让数据透视表中的空白单元格显示为0 307

436隐藏数据透视表中的计算错误 307

437将二维表格转换为数据列表 308

438显示报表筛选页 309

439在每个项目之间添加空白行 310

440插入切片器 310

441使用切片器筛选数据 311

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

15.2数据透视图的应用技巧 313

443创建数据透视图 313

444利用现有透视表创建透视图 314

445更改数据透视图的图表类型 315

446将数据标签显示出来 315

447在数据透视图中筛选数据 316

448在数据透视图中隐藏字段按钮 316

449将数据透视图转为静态图表 317

第16章 宏与VBA的应用技巧 319

16.1宏的应用技巧 319

450录制宏 319

451插入控件按钮并指定宏 321

452为宏指定图片 322

453为宏指定快捷键 323

454保存录制宏的工作簿 324

455设置宏的安全性 324

456将宏模块复制到另一工作簿 325

457删除工作簿中不需要的宏 326

16.2 VBA的应用技巧 327

458快速打开VBA窗口 327

459在Excel中添加视频控件 327

460在Excel中制作条形码 328

461插入用户窗体 330

462为窗体更改背景 332

463使用VBA创建数据透视表 332

464对数据透视表进行字段布局 333

465合并单元格时保留所有数据 335

466通过设置Visible属性隐藏工作表 336

第17章 Excel页面设置与打印技巧 339

17.1工作表的页面设置 339

467编辑页眉和页脚信息 339

468在页眉页脚中添加文件路径 340

469为奇偶页设置不同的页眉、页脚 341

470插入分页符对表格进行分页 342

471设置打印页边距 342

472把页面设置复制到其他工作表 343

17.2正确打印工作表 344

473使打印的纸张中出现行号和列标 344

474实现缩放打印 344

475一次性打印多个工作表 345

476居中打印表格数据 345

477避免打印工作表中的错误值 345

478不打印单元格的底纹颜色 346

479打印工作表中的网格线 346

480重复打印标题行 346

481打印员工的工资条 347

482只打印工作表中的图表 347

483将工作表中的公式打印出来 348

484只打印工作表中的部分数据 348

485打印指定的页数范围 349

486打印批注 349

487强制在某个单元格处开始分页打印 350