Excel商业应用宝典PDF电子书下载

Part 01 Excel操作环境的设置及工作簿的操作 24

Excel基本操作 24

001 Excel 2010的操作界面及组成元素 24

002 通过“帮助”了解Excel 2010的新增功能 25

003 了解Excel 2010的保存类型 26

004 了解新增的【文件】选项卡 27

005 检查与早期版本的兼容性 28

006 Excel 2010中查看Excel 2003的命令 29

007 向功能区添加组和命令 31

008 导入自定义功能区 34

009 隐藏以及重新显示功能区 36

010 向快速访问工具栏添加常用命令，以便快速执行相应命令 37

011 使用快捷键快速执行命令 38

012 初始化功能区 39

013 设置新建工作簿的默认值 40

环境设置 41

014 更改工作表屏幕颜色和用户界面 41

015 自定义列表中添加常用职位序列 42

016 将文档保存为早期版本格式的工作簿 44

017 通过自动保存功能创建自动恢复文件 45

工作簿管理 47

018 设定工作簿密码 47

019 锁定工作簿窗口或结构 48

020 创建使用MS模板的工作簿 49

021 将已设置安全性文件指定为受信任文档 50

022 将工作簿标记为最终状态 51

023 工作簿中输入信息 52

024 删除工作簿属性及个人信息 53

025 固定最近使用的文档和文件夹 55

026 将工作簿另存为PDF文件 56

工作表的基本操作 57

027 更改工作表名称和颜色 57

028 快速添加工作表 58

029 快速选择不在屏幕中显示的工作表 59

030 将特定工作表拖动到您所希望的位置 60

031 将特定工作表复制到其他工作簿 61

032 隐藏或取消隐藏工作表 62

033 以电子邮件形式发送特定工作表 64

034 为工作表添加背景图 66

035 保护工作表并使用被许可的编辑功能 67

036 消除文档中显示的虚线 69

Part 02 数据操作 72

输入数据 72

037 数据的分类 72

038 临时禁用自动更正功能 74

039 输入各类型数值数据 76

040 显示多种形式的数据 77

041 一个单元格中输入两行数据 78

042 纵向输入数据 79

043 将简体中文转换成繁体中文 80

044 工作表中插入符号 81

045 快速插入常用符号 82

046 以对象形式插入公式 83

047 输入日期和时间数据 85

048 使用快捷键输入日期和时间数据 87

049 输入经过翻译的数据 88

050 利用小数点位数插入数字 89

自动填充数据 90

051 使用填充柄自动填充数据 90

052 填充工作日数据 92

053 填充连续性数据 93

054 填充自定义序列数据 94

055 填充按特定间隔递增的数据 95

056 使用公式填充排列序号并进行排序 96

数据有效性 98

057 应用数据有效性 98

058 输入指定范围内的金额（一） 100

059 输入指定范围内的金额（二） 102

060 基于文本长度应用数据有效性 104

061 通过输入法模式自动切换到英文输入法 105

062 将数据输入限制为下拉列表中的值 106

063 基于已定义名称的单元格区域输入数据 107

064 禁止重复输入数据 109

065 设置需要满足多项条件的数据有效性 110

066 只允许输入不包含特定文字的数据 112

067 通过双重有效性条件创建分类 113

068 标示无效数据 116

069 按列的顺序输入数据 117

070 更改单个单元格的有效性条件，并把更改内容应用到具有相同设置的其他单元格 118

071 查找数据有效性的应用范围 119

072 清除数据有效性 120

转换数据 122

073 将文本格式下输入的数字数据转换成数值数据 122

074 将数据转换成日期格式数据 124

075 删除空白字符 125

076 通过替换功能删除连字符（-） 126

077 将分成两排输入的数据合并成一排 128

078 将公式结果复制为数据值 129

079 删除空单元格中包含的记录 130

080 一次性向所有空单元格输入0值 132

Part 03 编辑文档 134

页面视图 134

081 单元格中用公式代替计算结果 134

082 冻结工作表窗格 135

083 使用自定义视图功能保存和应用显示设置 136

084 页面布局视图和分页预览视图 138

085 状态栏上显示其他函数 140

086 比较两个工作簿 141

087 全屏显示选定区域 142

选择单元格 144

088 使用鼠标和键盘选取单元格区域 144

089 使用键盘一次性选择某个单元格区域 146

090 通过名称框选择特定单元格或区域 147

091 使用快捷键选择当前区域和所有单元格 148

092 通过定位条件功能查找包含批注的单元格 149

093 通过定位条件功能查找包含公式的单元格 150

094 仅复制可见单元格 151

编辑数据和单元格 153

095 插入行和列 153

096 删除行和列 155

097 清除内容和格式，但保留单元格 156

098 移动数据库中的分类（字段） 158

099 将多个不相邻的单元格复制到同一个区域 159

100 详解“选择性粘贴”命令 160

101 复制包含链接和列宽的源数据 161

102 将复制的源数据粘贴为链接的图片 163

103 粘贴时转置行和列 165

104 查找带格式的单元格 167

105 一次性查找并更改格式相同的单元格 169

页面和打印设置 171

106 设置打印环境 171

107 打印居中对齐的文档 172

108 只打印特定区域 173

109 打印时将工作表缩减到一个页面中 175

110 打印网格线和标题 176

111 每页都打印行标题 177

112 在希望位置对工作表分页 178

113 打印指定页面和工作表 179

114 不打印单元格中显示的错误提示 180

115 只打印图表 181

116 打印文档时排除图表 182

117 打印工作表背景 183

118 利用标签打印向导打印标签 185

设置页眉和页脚 188

119 页面布局中设置页眉和页脚 188

120 页眉中插入徽标并调整尺寸 190

121 为偶数页和奇数页设置不同的页眉和页脚 192

122 插入页码和总页数 194

123 自定义起始页码 196

124 插入当前日期后更改显示格式 197

Part 04 设置格式 200

设置单元格格式（不包括数字格式） 200

125 更改Excel的默认字体和字号 200

126 通过单元格样式和主题美化标题 201

127 使用键盘加速键设置字体格式 202

128 通过键盘设置单元格格式 203

129 重复使用上一次使用过的格式 204

130 保存经常使用的单元格样式 205

131 引用其他工作簿的单元格样式 206

132 使用拼音字段功能，为数据添加注释 207

133 缩进文句并分散对齐文字 208

134 根据单元格宽度排列数据 209

135 合并单元格及垂直对齐文本 211

136 在单元格区域中居中对齐数据 212

137 设置和删除单元格边框 213

138 使用渐变效果为单元格填充颜色 215

按分类设置数字格式 216

139 设置千位分隔样式和货币符号格式 216

140 数字数据中应用小数位数 218

141 为数字数据设置百分比样式 219

142 设置日期和时间数据的格式 220

143 不使用公式计算日期和时间数据 221

144 使用中文大写显示数字 223

自定义数字格式 224

145 了解自定义数字格式 224

146 为数字数据添加文本格式的单位 226

147 按千、百万单位省略数字数据的位数 227

148 以001,002,003形式显示序号 229

149 使用“？”代码对齐序号 230

150 根据分数显示“最佳”、“优秀”和“及格” 231

151 隐藏不想显示的数据 232

152 了解日期和时间格式中使用的符号和类型 233

153 显示包含汉字和星期的日期 235

154 将日期数据显示为星期 236

155 显示总经过时间 237

156 使用“_”和“＊”符号添加空格和重复字符 239

157 根据位数设置电话号码格式 241

158 使用＊代码填充目录线 243

条件格式 244

159 查找并标记聘用日期相同的数据 244

160 查找并标记营业实绩优于平均的月份 245

161 通过项目选取规则指定条件格式 247

162 使用数据条表示数据大小 248

163 为负数值指定不同的颜色 249

164 省略数据值，只显示数据条 251

165 通过图标集注释营业利润 253

166 根据利润率更改图标的阈值 254

167 隐藏图标集中的部分图标 255

168 使用色阶格式显示销量变化 256

169 显示上年对比销量提升的商品 257

170 突出显示销售率排在前20％的商品 259

171 只对每日变更的特价商品指定格式 261

172 每十行设置颜色区分数据 263

173 显示满足多项条件的记录 264

174 仅对星期一售出的数据设置格式 265

175 输入数据的时候显示边框 266

176 重复数据的第二个数据开始设置格式 268

177 在数据透视表中使用条件格式比较数据 269

178 快速删除条件格式 270

Part 05 创建公式 272

公式计算选项 272

179 查看公式求值步骤 272

180 在单元格中检查和编辑公式 273

181 同时更改和计算多项数据 274

182 隐藏重要的公式 275

公式的基本操作 277

183 创建公式的方法和运算符简介 277

184 在公式中通过多种途径引用值 278

185 更改链接文档的位置 281

186 快速打开引用文档 283

187 不再显示更新链接消息栏 284

188 公式中查看引用单元格和从属单元格 285

189 检查公式中的错误 286

190 错误检查和追踪错误 288

191 更正公式中的循环引用 290

192 计算以文本格式输入的公式 291

使用名称 293

193 定义和确认名称 293

194 使用数据区域的标签定义名称 294

195 修改和删除定义的名称 296

196 选用定义的名称 298

197 使用相对引用定义名称 300

198 使用名称简化重复的公式 301

199 筛选并删除有错误的名称 302

创建表格 303

200 Excel 2010程序的表格简介和计算构造 303

201 创建表格并设置表格样式 305

202 自定义更改表格样式 306

203 自动向表格添加数据 308

206 表格中设置各个项目的汇总 309

205 仅汇总特定区域的数据 310

206 更改表名称和设计 311

207 在表格中以结构化引用形式运算公式 312

208 在表格中使用常规引用形式运算公式 314

209 在表格中自动填充公式结果 315

210 将表格转换成普通数据区域 316

Part 06 应用函数 318

自动求和 318

211 了解函数使用基础 318

212 通过求和命令快速计算数据 320

213 指定计算区域后一次性求和 322

214 计算最大值和最小值 323

215 COUNT,COUNTA,COUNTBLANK函数简介 325

统计／汇总函数 326

216 ～IF函数和～IFS函数简介 326

217 计算平均完成率-AVERAGEIFS函数 327

218 对销量超过平均的商品计算销售额-SUMIFS函数 329

219 按分类计算小计-SUBTOTAL函数 331

220 仅对筛选的数据计算小计-SUBTOTAL函数 334

221 使用混合引用复制函数公式 336

文本／数据编辑函数 338

222 提取电子邮件中的ID-FIND,LEFT函数 338

223 使用分列功能提取ID 339

224 隐藏身份证号码的后几位数①-REPLACE函数 341

225 将日期更改为星期-TEXT函数 342

226 从订单编号中提取商品名称-LEN,RIGHT,SEARCH函数 343

227 将单元格数据连接成一个句子-CONCATENATE,TEXT函数 344

228 根据身份证号查出性别-CHOOSE,MID函数 345

逻辑／判断函数 346

229 按销售额和地区计算奖金-IF,OR函数 346

230 返回对平均值的评价-IF,VLOOKUP函数 347

231 通过多个条件和引用表进行评价-IF,VLOOKUP函数 349

232 IF函数和IS～函数简介 350

233 自动为数据添加序列号-IF,ROW函数 352

234 根据进出库明细计算税前金额-IF,ISBLANK函数 353

235 填充序号时忽略空单元格-COUNTA,IF函数 354

236 按5000元单位计算售价-IF,RIGHT,ROUND系列函数 355

数学／计算函数 357

237 截去指定位数以下的值-TRUNC函数 357

238 计算累计销售额-SUM函数 358

239 ROUND系列函数简介 359

240 七舍八入计算售价-ROUND函数 360

241 基于库存计算采购量CEILING.PRECISE函数 361

日期／时间函数 363

242 返回当前日期和时间-NOW,TODAY函数 363

243 基于身份证号码返回出生年月日-DATE,MID函数 364

244 基于出生年月日返回周岁-DATEDIF,TODAY函数 365

245 计算在职时间-CONCATENATE,DATEDIF,TODAY函数 366

246 计算项目开始日期的星期数-CHOOSE,WEEKDAY函数 367

247 计算项目结束日期-WORKDAY函数 368

248 计算某个日期为所属月份的第几周-MONTH,WEEKNUM函数 369

249 计算特定作业期间内休息日数-DATEDIF,NETWORKDAYS函数 370

250 检查项目进度-IF,NETWORKDAYS,TEXT函数 371

251 计算星期一、星期二休息时的工作天数-NETWORKDAYS,INTL函数 372

252 计算N年、N月、N日以后的日期-DATE,DAY,MONTH,YEAR函数 373

253 计算贷款的首期利息交付日和贷款到期E-EDATE,EOMONTH函数 374

254 判断闰年与否-DAY,DATE,IF函数 375

255 根据工作时间计算工作津贴-DAY,HOUR,IF,TIME函数 376

统计／趋势分析函数 379

256 比较兼容性函数类别和新增函数 379

257 计算包括未应试情况在内的平均成绩-AVERAGEA函数 380

258 计算销售排行①-RANK.EQ函数 381

259 计算销售排行②-RANK.AVG函数 382

260 按商品分类计算排行-RANK.EQ函数 383

261 按成绩返回百分位排名和等级-PERCENTRANK.INC,VLOOKUP函数 384

262 返回众数-MODE.SNGL函数 386

263 返回指定排名值-LARGE,SMALL函数 387

264 基于几何平均值返回平均增长率-GEOMEAN函数 388

265 按分数段返回分数的出现频率-FREQUENCY函数 390

266 返回线性趋势的预测值-FORECAST函数 392

267 根据线性回归预测法分析销售趋势-TREND函数 393

268 根据指数回归拟合曲线分析销售趋势-GROWTH函数 395

269 计算R2决定系数和最适合的预测值-GROWTH,RSQ函数 396

查找与引用函数 398

270 查找行和列的位置-COLUMN,ROW函数 398

271 根据编码返回产地-CHOOSE函数 400

272 使用部分字符锁定商品所在位置-MATCH函数 401

273 引用商品列表中的单价计算销售额-VLOOKUP函数 402

274 使用姓名对数据分组-HLOOKUP函数 403

275 根据职位和工资等级计算基本工资-INDEX,MATCH函数 404

276 在明细表中按职位和类型计算津贴额-COLUMN,INDEXMATCH函数 405

277 根据工号编制培训证明-LOOKUP函数 406

278 通过定义名称设置双重有效性-INDIRECT函数 409

279 通过单价表返回每种商品的单价-INDEX,INDIRECT,MATCH,OFFSET函数 413

280 创建动态引用区域-COUNTA,OFFSET函数 415

281 只显示最近五个项目-COUNTA,OFFSET函数 417

282 根据多个工作表中的分散数据创建商品目录-INDIRECT,ADDRESS函数 418

财务函数 419

283 计算投资达到目标金额的期限-NPER函数 419

284 计算某项投资5年后的到期总金额-FV函数 421

285 计算长期储蓄的月存款额-PMT函数 422

286 等额偿还贷款本息-IPMT,PPMT函数 423

287 基于定额法计算固定资产的折旧值-SLN函数 425

288 基于定率法计算固定资产的折旧值-DB函数 426

数据库／信息函数 427

289 数据库函数的参数类型 427

290 找出销售额最高的营业员-DGET,MAX函数 428

291 基于地区代理店和数量返回总销售额-DSUM函数 429

292 搜索系统信息和单元格信息-CELL,INFO函数 430

293 将文本值转化成0值-N函数 432

294 根据身份证号码的偶数、奇数判断性别-IF,ISEVEN,MID函数 433

函数技巧 435

295 轻松应用复杂的嵌套函数的技巧 435

296 为函数公式添加注释-IF,ISEVEN,MID,N函数 438

297 忽略错误并计算函数公式-AGGREGATE函数 439

298 将包含错误的工作簿修饰得更整洁美观-IFERROR函数 441

299 引用多个条件计算销售数量-INDEX函数 443

300 创建简单重复的函数公式 444

301 在Excel程序中发送电子邮件-HYPERLINK函数 446

数组公式 448

302 数组公式中设置常量 448

303 对满足多个条件的值求和-RIGHT,SUM函数 450

304 按月份计算满足多个条件的销售额-MONTH,SUM函数 451

305 按季度求和-COLUMN,IF,MOD,SUM函数 452

306 计算销量前十位商品的总销量-INDIRECT,LARGE,ROW,SUM函数 453

307 计算各分组的最高销售额-MAX,N函数 454

308 计算首次交易日-IF,MIN函数 455

Part 07 用图表形式显示数据 458

创建图表 458

309 图表的组成元素和创建方法 458

310 创建并简单修饰图表 460

311 图表布局中显示数值轴和网格线 462

312 设置图表标题和数据系列格式 463

313 取消图表中的图例，显示图表标签 464

314 复制图表的格式 465

编辑图表 467

315 在图表中只显示特定年份 467

316 在图表中编辑分类轴标签 469

317 设置图表的次坐标轴并更改图表类型 471

318 修饰折线图的数据标记 473

319 在图表中更改数值轴的最大值和最小值 475

320 在图表中更改类别标签的顺序（逆序显示） 477

321 数据区域标题和图表标题联动 478

322 在图表中重叠显示数据系列 479

323 基于主要数据和其他数据创建复合饼图 480

324 更改图表中负数值数据系列的颜色 482

325 更改图表中分类轴标签的位置 483

326 锁定图表的大小和位置 484

327 更改默认图表 486

328 保存和使用专属于自己的图表样式 487

329 在图表中设置系列线 489

分析图表 490

330 工作表中添加线性趋势线并指定周期 490

331 图表中设置R平方值后，查看回归分析类型 492

332 针对值间差距过大的数据创建图表 494

333 创建可对比数据的条形图 497

334 为创建图表而定义名称 499

335 图表中设置动态数据区域 501

336 利用圆环图显示小计 503

337 创建气泡图 505

迷你图 508

338 使用迷你图显示数据的趋势 508

339 在迷你图中显示高点和低点 509

340 删除部分单元格中的迷你图 510

341 更改迷你图的类型 511

342 通过盈亏迷你图显示目标达成情况 512

343 显示不规则日期的趋势 514

Part 08 创建数据库 516

排序 516

344 通过功能区升序排列文本 516

345 在表格中降序排列数字数据 517

346 使用多个条件排序 518

347 以行为条件排序 520

348 按颜色对数据进行排序 522

349 按自定义序列对数据进行排序 524

350 数据区域中重新对字段位置进行排序 526

自动筛选 528

351 按所选单元格的值筛选数据 528

352 按关键词筛选数据 529

353 基于自定义自动筛选功能筛选数据 530

354 按单元格颜色筛选数据 531

355 基于两个字段的条件筛选数据 532

356 基于低于平均值的金额筛选数据 534

357 筛选并排序销售额在最大15％以内的数据 535

358 通过函数按分组筛选数据 537

359 以包含合并单元格字段为条件筛选数据 539

360 自动筛选中取消对日期的分组 541

361 仅对筛选结果进行更改操作 543

高级筛选 545

362 设置高级筛选中使用的条件 545

363 根据条件筛选结果后复制到其他工作表 546

364 在两个以上字段中使用OR条件筛选数据 548

365 只筛选出期望字段中的数据 550

366 筛选特定日期（星期数）里订购的商品 552

367 筛选本月过生日的国外区营业员 554

368 筛选文本准确匹配的数据 556

369 仅筛选偶数行的数据 558

370 通过高级筛选功能筛选唯一项 560

371 通过删除重复项，筛选唯一项 561

372 筛选新列表后添加到总列表 562

分类汇总 564

373 按地区分类汇总 564

374 使用嵌套分类汇总统计各季度销售额 566

375 复制汇总值创建统计表 569

376 将汇总更改为小计后设置条件格式 571

377 删除分类汇总 573

数据透视表 574

378 数据透视表中选择要分析的数据 574

379 创建数据透视表 575

380 更改数据透视表样式并设置选项 577

381 更改行标签和列标签的项和顺序 578

382 将汇总值更改为平均值 580

383 按年或按季度分组日期数据 582

384 删除数据透视表中的分类汇总 583

385 使用多重合并功能创建数据透视表 584

386 更改数据透视表的字段名称和项名称 587

387 导出已经过分析的数据列表 589

388 数据透视表中只显示必要的项目 590

389 报表筛选中创建多项报表 592

390 在单独工作表中显示报表筛选的各项目 593

391 更改布局并在项目间插入空行 594

392 字段中显示无数据的项目 595

393 空单元格中重复显示标签 597

394 引用数据透视表中的值返回汇总数据-GETPIVOTDATA 598

395 插入计算字段添加“税额”项 601

396 插入计算项来计算增长率 603

397 报表中插入累计值 605

398 按排名显示销售实绩 606

399 父行汇总百分比形式显示数据 608

400 按姓名对项目分组 609

401 通过数据透视表选项修饰数据透视表报表 610

402 更新数据透视表的源数据 612

403 创建数据透视图 613

404 隐藏数据透视表中的筛选按钮 615

405 选择并删除和移动数据透视表 616

切片器 618

406 插入切片器 618

407 设置切片器样式 620

408 连接其他数据透视表共享切片器 622

409 删除切片器 624

Part 09 数据工具和连接分析外部数据源 626

PowerPivot 626

410 安装PowerPivot加载项 626

411 使用PowerPivot分析Excel数据 628

412 链接Excel数据创建数据透视表和透视图 632

413 通过PowerPivot分析Access数据库 635

414 创建数据表之间的关系以对数据进行分析 639

合并及连接外部数据源 642

415 使用三维引用合并多个工作表的数值 642

416 使用合并计算功能合并数据 644

417 基于多个列合并计算数据 646

418 在Excel中导入文本数据 650

419 在Excel中导入网站数据 652

420 在Excel表格中导入Access DB表 654

421 删除工作簿的数据连接 656

模拟分析 658

422 模拟分析简介 658

423 使用单变量求解查找减少率 660

426 方案管理器中添加方案 662

425 方案管理器中创建摘要报告 664

426 使用两个变量创建奖金查阅表（模拟运算表） 666

427 使用三个变量创建奖金查阅表（模拟运算表） 668

428 功能区中添加规划求解和分析工具 670

429 使用规划求解求得最合理的商品配送条件 671

430 在规划求解中保存方案 675

数据分析 681

431 统计分析简介 681

432 使用描述统计分析工具计算人口统计值 682

433 通过设置接收区域创建直方图 684

434 计算各家经销商的销售分部-排位与百分比排位 688

635 使用移动平均工具预测销售趋势-时间序列分析 691

436 使用指数平滑工具预测销售趋势-时间序列分析 694

437 使用方差分析工具分析商品分类和订单量的关系 696

438 使用回归分析工具分析广告费用和销售额的关系 698

Part 10 使用控件工具和宏 702

控件工具和宏 702

439 在功能区中显示【开发工具】选项卡 702

440 使用列表框创建证明书标题列表 703

441 使用组合框创建综合证明书 705

442 使用复选框创建调查问卷格式 707

443 使用数值调节钮调整数据值 711

444 录制宏 715

445 连接表单控件和宏 718

446 创建宏按钮控件 720

创建宏 722

447 运行宏的各种方法 722

448 使用VBA编辑器编辑宏 723

449 删除宏 725

450 用复制的宏创建个人宏工作簿 727

451 导入宏文件 732

452 在“立即窗口”中执行VBA命令 735

453 处理宏错误 736

实用宏 739

454 通过VBA设置条件格式 739

455 通过宏把工作表拆分成符合条件的多个工作表后复制数据 742

456 创建从字符串提取数字的自定义函数 746

457 在所有工作簿中使用自定义函数 748

458 计算显示为字符串的公式 752

459 使用宏提取新增数据 754

460 使用宏合并相同的分类项 759

461 功能区中添加宏按钮 761

462 使用宏程序管理单元格中输入的批注 764