Excel办公技巧查询与应用宝典PDF电子书下载

第1章 操作工作簿 1

技巧1使用模板创建新工作簿 1

技巧2自定义工作簿默认的保存类型 3

技巧3自定义工作簿默认的保存位置 4

技巧4固定工作簿在最近使用的文档列表 5

技巧5查看工作簿的保存路径 6

技巧6实现自动启动常用工作簿 6

技巧7重新设置工作簿数据的默认字体、字号 7

技巧8自定义工作簿默认工作表数 8

技巧9清除打开的工作簿的记录 9

技巧10快速定位到文件夹 10

技巧11快速合并与拆分多个工作簿数据 11

技巧12加密工作簿 13

技巧13设置只允许用户进行部分操作 14

技巧14繁简转换不求人 16

技巧15 外语翻译助手 17

技巧16向“快速访问工具栏”中添加命令 18

第2章 操作工作表 21

2.1工作表的编辑 21

技巧1操作工作表时快速切换至任意工作表 21

技巧2批量重命名工作表名称 21

技巧3将工作表复制或移至另一工作簿中 24

技巧4改变工作表标签颜色 25

技巧5设置工作表图片背景 26

技巧6防止他人对工作表的增删 27

技巧7指定工作表中的可编辑区域 29

技巧8只保护工作表中的特定单元格或区域 30

技巧9隐藏工作表内的部分数据 31

技巧10彻底隐藏工作表 32

2.2单元格、行、列操作 33

技巧11准确定位多个任意的单元格区域 33

技巧12选取多个工作表的相同区域 34

技巧13次插入多行或多列 35

技巧14一次插入不连续的多行或多列 36

技巧15 隔行插入空行 37

技巧16快速删除所有空行 39

技巧17设置合适的行高或列宽 41

技巧18不连续行（列）的行高（列宽）一次性设置 42

技巧19拖动交换两列数据的位置 43

第3章 数据的输入及自动填充 45

3.1数据输入 45

技巧1让数据前面的0不省略 45

技巧2如何输入生僻字 46

技巧3记忆式输入提高输入效率 47

技巧4不连续单元格中相同数据一次输入 48

技巧5多个工作表中相同数据一次输入 49

技巧6插入专业型数学公式 50

技巧7让输入数据后自动添加小数点 53

技巧8快速输入指定格式日期 54

技巧9快速输入指定格式时间 55

技巧10输入大写人民币金额 57

技巧11文字与数值在同一单元格并存且能计算 58

技巧12部分重复数据快速输入有捷径 60

技巧13改变大金额数据的显示单位 61

技巧14按小数点对齐小数 62

3.2数据批量填充技巧 64

技巧15 连续单元格填充递增的数字 64

技巧16连续单元格快速填充相同日期 64

技巧17只填充工作日日期 65

技巧18妙用填充引用单元格的格式 66

技巧19快速填充区域内的空白单元格 67

技巧20自定义填充序列 68

技巧21让空白单元格自动填充上面的数据 70

技巧22超大数据块的一次性填充 71

3.3导入外部数据 72

技巧23导入其他Excel表格中数据 72

技巧24导入文本文件数据 73

技巧25导入Word文档中表格数据 76

技巧26导入网页中的有用表格 77

技巧27根据网页内容更新工作表数据 78

第4章 数据编辑与处理 81

4.1常用编辑技巧 81

技巧1数据随单元格的列宽自动换行 81

技巧2在任意位置上强制换行 82

技巧3设置文字上下标效果 83

技巧4妙用〈F4〉进行重复操作 84

技巧5“拆分窗口”滚屏查看数据 85

技巧6“冻结窗口”滚屏查看数据 86

技巧7文本格式数字转换为数字格式 87

技巧8将数字转换为日期 90

技巧9链接到其他资料文档 92

技巧10鼠标指向链接对象时显示提示文字 93

技巧11阻止Excel自动创建超链接 94

技巧12如何编辑有超链接的单元格 95

4.2数据合并拆分 96

技巧13合并单元格时保留所有文本内容 96

技巧14取消单元格合并的数据到未合并的区域中 97

技巧15 如何批量合并相同内容的单元格 98

技巧16将一个单元格中的数据拆分到多列 100

4.3数据复制 104

技巧17粘贴数据时匹配目标区域格式 104

技巧18数据行列转置生成新表格 105

技巧19复制数据表保持原行高列宽 105

技巧20多次复制、粘贴相同的文件和图片 106

技巧21选择性粘贴实现简易数据计算 108

技巧22选择性粘贴功能计算时忽略空单元格 110

技巧23让粘贴数据随原数据自动更新 111

技巧24将表格快速转换为图片 112

技巧25将公式计算结果转换为实际值 113

技巧26一次性复制公式到多个工作表中 114

4.4查找和替换数据 115

技巧27查找数据并突出显示出来 115

技巧28一次性找到所有空值单元格 117

技巧29一次性找到所有设置了公式的单元格 118

技巧30妙用通配符进行模糊查找 119

技巧31替换的同时自动设置格式 120

技巧32一次性取消Excel表中的换行符 122

技巧33批量删除特定的字符 123

技巧34只在指定区域中进行替换 123

第5章 表格美化与打印 125

5.1表格美化 125

技巧1跨列居中显示表格的标题 125

技巧2标题应用会计用双下划线 126

技巧3设置表格标题倾斜显示 128

技巧4套用表格样式 129

技巧5自定义表格样式 130

技巧6设置默认表格样式 134

技巧7自定义单元格样式 135

技巧8单元格样式的修改 137

技巧9将单元格样式应用到其他工作簿中 137

技巧10强大的格式刷 139

5.2表格页面设置与打印 140

技巧11手动直观调整页边距 140

技巧12让表格显示在纸张的正中间 141

技巧13在任意需要的位置上分页 143

技巧14删除所有手动分页符 144

技巧15 在页眉页脚中使用图片 145

技巧16设置页脚起始页码为所需的页数 147

技巧17把页面设置复制到其他工作表中 148

技巧18打印指定的页 151

技巧19打印工作中某一特定区域 151

技巧20打印整个工作簿 153

技巧21一次性打印多张工作表 154

技巧22将数据缩印在一页纸内打印 154

技巧23打印超宽工作表 156

技巧24将表格列标题打印到每一页中 157

技巧25将行、列号一起打印出来 159

技巧26在打印时不打印单元格中的颜色和底纹效果 160

技巧27将工作表上的网格线打印出来 161

技巧28将表格中添加的批注信息也打印出来 162

技巧29巧妙打印工作表背景 163

第6章 条件格式 165

6.1条件格式设置 165

技巧1当数值大于或小于指定数值时特殊显示 165

技巧2当数值介于指定值之间时特殊显示 166

技巧3当排名前N名时显示特殊格式 168

技巧4让值班安排表中只值班一次的特殊显示 169

技巧5当文本包含某个值时突出显示 171

技巧6将日期是本月的记录特殊显示 172

技巧7用数据条显示出不同级别人员的工资 173

技巧8复制使用条件格式 175

技巧9清除建立的条件格式规则 177

技巧10屏蔽公式返回的错误值 178

6.2在条件格式中使用公式 180

技巧11工作表自动隔行填色 180

技巧12当两个单元格采购价格不同时就突出显示出来 182

技巧13为不包含某一特定字符的单元格设置格式 183

技巧14标记特定年龄段的人员 185

技巧15 标识出表格中非数字单元格 186

技巧16特殊显示双休日 188

技巧17只在不及格成绩的单元格上显示图标集 189

技巧18突出显示部门总经理 192

技巧19将销售业绩高于平均值的标注为“优” 194

第7章 数据有效性设置 197

7.1数据有效性设置 197

技巧1创建下拉列表提高输入效率 197

技巧2选中单元格时给出输入提示 199

技巧3限定输入指定范围内的日期 200

技巧4复制单元格的数据有效性设置 201

技巧5在数据有效性序列中引用其他工作表的单元格区域 202

技巧6在多个工作表中同时设置相同的有效性验证 205

技巧7为什么数据有效性不显示下拉箭头 207

技巧8为什么单元格禁止输入任何内容但数据有效性中看不到设置 207

技巧9圈释无效数据 209

7.2设置数据有效性时使用公式 210

技巧10限制输入空格 210

技巧11禁止输入重复值 211

技巧12设置数据有效性使单元格只能输入汉字 212

技巧13限定输入小数不超过两位 214

技巧14禁止录入错误格式的产品规格 215

技巧15 限定单元格输入必须包含指定内容 217

技巧16把不符合要求的数据找出来 218

技巧17提示出货数量大于进货数量 220

技巧18使用有效性保护公式单元格不被修改 221

技巧19创建二级联动下拉列表 223

技巧20创建多级联动下拉列表 225

技巧21带提示功能的下拉列表实现多列快速录入 230

第8章 表格数据的排序、筛选 235

8.1排序的技巧 235

技巧1按姓名的汉字笔画排序 235

技巧2设置按行排序 236

技巧3有相同记录时按第二个关键字排序 237

技巧4自定义排序规则 239

技巧5分类汇总后如何按汇总值排序 241

技巧6在受保护的工作表中实现排序 242

技巧7如何将多行内容随机重新排序 243

技巧8巧妙恢复排序前的数据 244

技巧9对字母和数字组成的混合文本进行有规律的排序 244

8.2数据筛选实用技巧 246

技巧10筛选出大于指定数值的记录 246

技巧11筛选出介于指定数值间的记录 247

技巧12或条件筛选 248

技巧13筛选大于平均值的记录 249

技巧14筛选数据表中排名前N位的记录 250

技巧15 筛选出各门科目成绩都大于等于90分的记录 252

技巧16筛选只要一门科目成绩大于90分的记录 253

技巧17筛选出不重复记录 254

技巧18筛选出指定时间区域的记录 255

技巧19模糊筛选获取同一类型的数据 257

技巧20如何对双行标题列表进行筛选 257

技巧21自动筛选奇数行或偶数行的数据 259

技巧22如何使用筛选列表中的搜索功能 261

技巧23在受保护的工作表中使用筛选 261

技巧24解决自动筛选日期时不按年月日分组的问题 263

技巧25将高级筛选结果输出到其他位置 264

技巧26使用公式作为筛选条件 265

技巧27一次性取消所有工作表的自动筛选 267

第9章 表格数据的分析 269

9.1数据的合并计算 269

技巧1快速删除表格中的重复记录 269

技巧2按位置合并计算多表数据 270

技巧3按类别合并计算多表数据 272

技巧4巧用合并计算检测两列数据差异 273

技巧5让合并计算结果自动更新 275

9.2数据分类汇总 276

技巧6创建多级分类汇总 276

技巧7如何复制分类汇总结果 278

技巧8只显示分类汇总的结果 279

技巧9取消分类汇总 281

9.3模拟分析 281

技巧10巧用单变量模拟运算表快速计算员工业绩奖金 281

技巧11使用单变量求解产品的成本定价 283

技巧12双变量模拟运算表求解不同利率与贷款金额下的月还款金额 285

技巧13建立不同销量的销售方案 287

技巧14规划求解确定最优运输方案 290

技巧15 使用“规划求解”求解出所有产品利润最大化生产方案 296

第10章 用数据透视表统计分析数据 301

10.1数据透视表创建与编辑 301

技巧1用表格中部分数据建立数据透视表 301

技巧2在当前工作表中显示数据透视表 302

技巧3重新更改数据透视的数据源 303

技巧4设置默认的数据透视表样式 304

技巧5打开文件时自动刷新数据透视表 306

技巧6在每个分级之间添加空白行 307

技巧7将数据透视表转换为普通表格 309

技巧8删除数据透视表 309

技巧9根据数据透视表创建数据透视图 310

10.2报表数据的处理与分析 311

技巧10更改统计结果的计算方式 311

技巧11更改值的显示方式（如显示为占总和的百分比） 312

技巧12显示字段的明细数据 313

技巧13在数据透视表中设置按行排序 314

技巧14禁止他人通过双击单元格查看明细数据 316

技巧15 如何清除字段下拉列表中已删除数据源的项目 317

技巧16添加切片器快速实现数据筛选 318

技巧17按工龄分组统计各工龄段的人数 320

技巧18按周分组汇总产量 321

技巧19按年、季度、月份分组汇总销售额 322

技巧20按小时分组统计生产量 324

10.3自定义计算公式 325

技巧21自定义公式求解各销售部门奖金 325

技巧22自定义公式计算学生总分 326

技巧23自定义公式判断应付金额与已付金额是否平账 328

技巧24自定义公式计算商品的加权平均单价 330

技巧25查看当前数据透视表中自定义的公式 332

技巧26删除建立的计算字段 333

第11章 用图表比较分析数据 335

11.1图表新建技巧 335

技巧1选择不连续数据源建立图表 335

技巧2重新更改图表的数据源 336

技巧3用迷你图展示一组数据 337

技巧4给迷你图添加文本 339

技巧5更改迷你图的类型 339

技巧6向图表中追加新数据 340

技巧7更改图表中系列的名称 341

技巧8混合使用不同的图表类型 343

技巧9隐藏的数据也可以作为图表数据源 344

11.2图表编辑技巧 346

技巧10准确选中图表中要操作对象 346

技巧11图表标题与表格标题相链接 346

技巧12按需要重设刻度值的数字类型 347

技巧13更改刻度的最小值 347

技巧14将垂直轴显示在图表正中位置 350

技巧15 让相差悬殊的数据清晰显示（对数刻度） 351

技巧16次坐标轴在哪种情况下需要启用 352

技巧17设置数据系列分离（重叠）显示 355

技巧18重新调整数据系列的显示次序 356

技巧19拖动分离出饼图中最大扇面 356

技巧20套用图表样式快速美化 358

技巧21在图表中使用图形图片修饰 358

技巧22在图表中添加移动平均线 361

技巧23在图表中添加趋势线 362

技巧24实现缩放图表时横纵比不变 363

技巧25快速取消图表的所有格式 364

技巧26制作静态图表 364

技巧27将自己建立的图表保存为模板 365

技巧28复制到Word中使用 368

技巧29将Excel图表复制到幻灯片中使用 370

技巧30只允许对指定图表进行编辑 371

技巧31保护图表不被随意更改 372

第12章 公式编辑与数据源引用必备技巧 373

12.1公式的输入与编辑 373

技巧1快速获取函数提示信息 373

技巧2使用函数向导 373

技巧3查看长公式中某一步的计算结果 375

技巧4直观查看单元格引用情况 375

技巧5将公式转换为数值 377

技巧6快速复制公式的方法 377

技巧7大范围复制公式 378

技巧8检查公式的错误 380

技巧9查看公式每步计算结果 381

技巧10隐藏公式实现保护 382

12.2公式中数据源的引用 385

技巧11公式中引用相对引用数据源 385

技巧12公式中引用绝对引用数据源 386

技巧13引用其他工作表的单元格 388

技巧14引用其他工作簿中的数据源 390

技巧15引用多工作表的同一单元格计算 392

技巧16 〈F4〉键快速更改数据源的引用方式 393

技巧17利用名称框来定义名称 394

技巧18使用“定义名称”功能来定义名称 396

技巧19根据所选内容批量创建名称 396

技巧20引用定义的名称创建公式 398

技巧21定义公式为名称 401

技巧22创建动态引用名称 402

技巧23计算动态数据平均数 405

技巧24让定义的名称只应用于当前工作表 406

技巧25查看当前工作簿中定义的所有名称 407

技巧26修改定义的名称 408

技巧27删除不再使用的名称 408

第13章 文本与逻辑函数 411

13.1本章应用函数功能查询 411

函数1 MID函数——返回特定数目的字符 411

函数2 LEFT函数——返回从文本左侧开始指定个数的字符 411

函数3 RIGHT函数——返回最后一个或多个字符 412

函数4 FIXED函数——将数字按指定的小数位数进行取整 412

函数5 TEXT函数——将数值转换为按指定数字格式表示的文本 412

函数6 EXACT函数——比较两个字符串是否完全相同 413

函数7 REPT函数——按给定次数重复显示文本 413

函数8 SEARCH函数——返回第一个文本字符串的起始位置的编号 413

函数9 REPALCE函数——替换文本字符串中的部分文本 414

函数10 SUBSTITUTE函数——在文本字符串中用new_ text替代 old text 414

函数11CONCATENATE函数——将两个值合并到另一个单元格中 415

函数12NOT函数——对参数值求反 415

函数13OR函数——任何参数逻辑值为真时返回真 415

函数14 AND函数——检验一组数据是否都满足条件 416

函数15 IF函数——根据指定条件判断其“真”（TRUE）、“假”（FALSE） 416

13.2文本函数范例 417

技巧1提取出产品编码 417

技巧2按两位小数位数取整 418

技巧3将文本型日期转换为正确的日期格式显示 418

技巧4屏蔽手机号码的后几位数字 419

技巧5如何处理文本中多余的空格 420

技巧6在班级前面统一加上“高三”字样 420

技巧7检验原始分数与查卷结果是否一致 421

技巧8输入多个相同的符号 422

技巧9从最左侧提取出长地址中的省市名称 422

技巧10合并编码的时候显示前面的0 423

技巧11合并几个单元格数据日期显示为序列号怎么办 424

技巧12在项目编码中提取出合同号 425

技巧13将公司名称格式化 426

技巧14统计出实际参加人数 427

技巧15 输入的身份证号码长度是否正确 428

技巧16判断员工的综合评定成绩 429

技巧17筛选出符合条件的应聘人员 430

技巧18判断是否录取面试人员 431

技巧19判断是否为员工发放奖金 432

技巧20根据业绩计算应发总奖金 433

技巧21根据职工性别和职务判断退休年龄 433

第14章 数学函数 435

14.1本章应用函数功能查询 435

函数1 SUM函数——将指定的所有参数数字相加 435

函数2 SUMIF函数——对符合指定条件的值求和 435

函数3 SUMIFS函数——对满足多个条件的单元格求和 436

函数4 SUMPRODUCT函数——将数组对应元素相乘并返回乘积之和 436

函数5 ABS函数——返回数字的绝对值 436

函数6 ROUNDUP函数——向上舍入数字 437

函数7 ROUNDDOWN函数——向下舍入数字 437

函数8 MOD函数——返回两数相除的余数 437

函数9 INT函数——数字向下舍入到最接近的整数 438

函数10 CEILING函数——将向上舍入为最接近的倍数 438

函数11 ROUND函数——四舍五入为指定的位数 438

函数12 RAND函数——返回随机实数 438

14.2数学函数范例 439

技巧1根据销量和单价统计出总销售额 439

技巧2根据每月预算费用计算总预算费用 439

技巧3统计同时满足两个条件的总和 440

技巧4统计出各部门的工资总额 441

技巧5统计某个时段的销售总金额 443

技巧6按多个条件统计 444

技巧7统计某一日期区域的销售金额 445

技巧8统计总销售额 446

技巧9计算商品折后的总金额 447

技巧10统计出指定部门获取奖金的人数（去除空值） 447

技巧11统计出指定班级分数大于指定值的人数 449

技巧12统计指定专柜各类别产品的销售金额合计值 450

技巧13使用SUMPRODUCT函数统计非工作日销售金额 451

技巧14求绝对值 452

技巧15 计算物品的快递费用 452

技巧16将金额按小数位数向下舍入计算 453

技巧17统计出缺勤的人数 454

技巧18只统计某两个产品的合计金额 455

技巧19使用通配符对某一类数据求和 456

技巧20统计加班时长 457

技巧21计算平均销售量时取整 458

技巧22以6秒（不足6秒按6秒计算）为单位计算通话费用 459

技巧23随机获取编号 460

第15章 统计函数 461

15.1本章应用函数功能查询 461

函数1 AVERAGE函数——计算算术平均值 461

函数2 AVERAGEA函数——返回参数的平均值 461

函数3 AVERAGEIF函数——返回满足条件的平均值 461

函数4 AVERAGEIFS函数——返回满足多重条件的平均值 462

函数5 HARMEAN函数——返回数据集合的调和平均值 462

函数6 TRIMMEAN函数——求数据集的平均值 463

函数7COUNT函数——统计函数数字的单元格个数 463

函数8 COUNTIF函数——计算满足条件的单元格个数 463

函数9 COUNTIFS函数——计算满足多重条件的单元格的个数 464

函数10 COUNTBLANK函数——计算区域中空白单元格的数目 464

函数11MIN函数——返回数据集中的最小值 464

函数12MINA函数——返回列表中的最小值 464

函数13MAX函数——返回数据集中的最大数值 465

函数14MAXA函数——返回列表中的最大值 465

函数15LARGE函数——返回某一数据集中的某个最大值 465

函数16GROWTH函数——对给定的数据预测指数增长值 465

15.2统计函数范例 466

技巧1计算商品的平均价格 466

技巧2忽略单元格中的0值求出平均值 467

技巧3在含有文本值的区域中计算平均销售额 468

技巧4计算每个月的平均收入金额 469

技巧5统计各班级平均分 469

技巧6排除老店计算出平均利润 471

技巧7计算一车间女职工平均工资 472

技巧8求介于某一区间内的平均值 472

技巧9统计指定店面所有男装品牌的平均利润 473

技巧10统计所有女装品牌的平均利润 474

技巧11统计出成绩表中缺考的人数 475

技巧12统计出缺考人数合计 476

技巧13统计出工资大于指定值的记录条数 477

技巧14统计出成绩大于平均分数的学生人数 477

技巧15 使用通配符统计出某一类数据的记录条数 478

技巧16统计各店面男装的销售记录条数（双条件） 479

技巧17计算出五天的平均产量 480

技巧18根据评委打分计算选手的得分 481

技巧19返回女职工的最大年龄 482

技巧20返回上半个月单笔最高销售金额 483

技巧21返回成绩表中的最高分（包含文本时） 484

技巧22返回最低利润额 484

技巧23返回成绩表中的最低分（包含文本时） 485

技巧24返回排名前3位的销售金额 486

技巧25计算销售金额前3名合计值 487

技巧26统计数据表中前5名的平均值 488

技巧27分别统计各班级第一名成绩 489

技巧28预测出指数增长值 490

第16章 查找函数 491

16.1本章应用函数功能查询 491

函数1 LOOKUP函数——向量形 491

函数2 LOOKUP函数——数组形 492

函数3 HLOOKUP函数——返回表格或数组中指定行的所在列中的值 492

函数4 ROW函数——返回引用的行号 493

函数5 VLOOKUP函数——返回该区域相同行上任何单元格中的值 494

函数6 INDIRECT函数——返回由文本字符串指定的引用 495

函数7 INDEX函数——返回数据清单或数组中的元素值 495

函数8 MATCH函数——返回数据清单或数组中的元素的位置 496

函数9 OFFSET函数——通过给定偏移量得到新的引用 497

16.2查找函数范例 497

技巧1查找指定员工的信息 497

技巧2输入编号查询指定员工的全部信息 498

技巧3根据产品编号查询库存数量 500

技巧4根据不同的返利率计算订单的返利金额 501

技巧5快速查询任意月份每位销售员的业绩 502

技巧6根据多条件计算员工年终奖 503

技巧7在销售表中自动返回产品单价 504

技巧8统计出销售记录条数 506

技巧9按指定的范围计算平均值 506

技巧10查找指定月份指定专柜的销售金额 507

技巧11返回成绩最高的学生的姓名 509

技巧12查询总金额最高的对应的店铺（反向查询） 510

技巧13使用ROW函数控制处份序列的显示行数 511

技巧14按奇数月与偶数月统计销量 512

技巧15 实现数据的动态查询 514

技巧16对每日出库量累计求和 516