Excel高效办公应用技巧

作者简介

怎样成为职场高效能人士？怎样从职场办公中脱颖而出？怎样提高办公效率享受美好生活？看看这本随身查小书吧，一册在手，办公无忧。

《Excel高效办公应用技巧》全书共15章，包括Excel数据编辑、数据批量输入、数据的合并与拆分、自定义单元格的格式、按条件显示数据、图表、数据统计分析方法以及函数在各个工作领域的应用，第15章还列出了Excel使用中的常见问题集，为读者提供查阅参考。

本书适合办公人员、文秘人员作为随身速查手册。

书籍目录

第1章  Excel数据编辑技巧
1

1.1  常用编辑技巧
1

技巧1  输入可计算的分数
1

技巧2  当前日期与时间的快捷键输入法
1

技巧3  建立广告文档时设置特大号字体
1

技巧4  让输入的数据自动添加小数位
2

技巧5  输入带圈数字（包括10以上的）
3

技巧6  同一单元格中同时包含文字与数值（数值可计算）
5

技巧7  在表格中设置竖向显示的文字
6

技巧8  跨列居中显示表格的标题
6

技巧9  随单元格的列宽自动换行
7

技巧10  在任意位置上换行
8

技巧11  制作3栏表头
8

技巧12  让单元格不显示"零"值
10

技巧13  快速复制表格的格式（用格式刷）
11

技巧14  一次清除表格中所有单元格的格式设置
11

1.2  快速选定与查找替换
12

技巧15  逐步扩展选取选定区域
12

技巧16  在同行（同列）非空单元格之间跳转
12

技巧17  快速准确定位多个任意的单元格区域
12

技巧18  一次性选中所有空值单元格
13

技巧19  反向选择剩余的行
14

技巧20  选取多个工作表的相同区域
14

技巧21  查看公式引用的所有单元格
15

技巧22  一次性选中表格中相同格式的单元格
15

技巧23  快速定位合并的单元格
16

技巧24  数据替换的同时自动设置格式
17

技巧25  妙用通配符进行模糊查找并替换
19

1.3  选择性粘贴的妙用
19

技巧26  将公式计算结果转换为数值
19

技巧27  只粘贴格式
20

技巧28  粘贴数据匹配目标区域格式
21

技巧29  数据行列转置生成新表格
22

技巧30  复制数据表保持行高、列宽不变
22

技巧31  选择性粘贴实现数据计算
23

技巧32  计算时忽略空单元格
24

技巧33  让粘贴数据随原数据自动更新
25

技巧34  将表格快速转换为图片
25

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

第2章  通过批量输入提高工作效率
28

2.1  数据批量输入的技巧
28

技巧36  为什么用填充柄填充序号时不能递增
28

技巧37  在连续单元格中输入相同日期
29

技巧38  一次性输入1月份的工作日日期
29

技巧39  一次性输入1月份的星期日日期
30

技巧40  快速输入数字的阶乘数据
31

技巧41  块状数据的填充
32

技巧42  巧妙填充单元格的格式
34

技巧43  自定义填充序列
35

技巧44  一次性在不连续单元格中输入相同数据
37

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

技巧46  海量相同数据的快捷填充
38

技巧47  空白单元格横向填充前面数据纵向填充上面数据
39

技巧48  忽略非空单元格批量建立公式
41

2.2  行列的批量处理
42

技巧49  一次性插入多行、多列
42

技巧50  快速互换两列数据
43

技巧51  一次性隔行插入空行
44

技巧52  一次性隔行调整行高
45

技巧53  隔4行插入空行
47

技巧54  删除A列为空的所有行
48

技巧55  删除整行为空的所有行
50

2.3  导入使用外部数据
52

技巧56  导入Excel表格数据
52

技巧57  导入文本文件数据
54

技巧58  从Word文档中引用表格到Excel中
56

技巧59  让导入到Excel表格中的数据自动更新
56

技巧60  导入Internet网页数据到Excel中
57

技巧61  导入网页中的有用表格
58

技巧62  根据网页内容更新工作表数据
60

第3章  数据的合并与拆分
61

3.1  单元格的合并
61

技巧63  合并单元格时保留所有数据
61

技巧64  复制合并的单元格且让数据连续显示
62

技巧65  取消单元格合并后一次性填充空白单元格
64

技巧66  解决合并单元格的不能筛选问题
65

技巧67  解决合并单元格不能排序问题
67

技巧68  合并单元格（不同大小）中公式的复制
70

3.2  数据的合并与拆分
71

技巧69  合并两列数据并自动删除重复值
71

技巧70  合并多个单元格的数据且自动分行显示
73

技巧71  一个单元格中的数据拆分为多列
73

技巧72  一个单元格中的数据拆分为多行
75

技巧73  合并两个表格的数据
76

第4章  自定义单元格格式输入特殊数据
79

4.1  特殊数据的输入
79

技巧74  0开头的编号如何填写
79

技巧75  快速输入大量负数
79

技巧76  在编辑会计报表时如何输入大写人民币值
80

技巧77  让输入的身份证号码不要显示为科学计数方式
81

技巧78  设置当数据有部分重复时只输入不重复部分即可自动生成
82

技巧79  设置数据以万元为显示单位
83

技巧80  不改变数字实际大小，数字显示扩大100倍
85

技巧81  根据单元格内容显示不同的字体颜色
86

技巧82  根据数值的正负自动添加不同的前后缀
86

技巧83  约定数据宽度不足时用零补齐
88

技巧84  输入的所有数字均以*显示
89

技巧85  输入1显示"√"，输入0显示"×"
89

技巧86  隐藏单元格中所有或部分数据
90

4.2  数据格式的相互转换
91

技巧87  将数字转换为日期
91

技巧88  文本格式数字转换为数字格式
92

技巧89  将完整的计算公式显示在单元格中
93

技巧90  自定义格式的数据转换为实际数据
94

第5章  按条件自动设置特殊格式及限制数据输入
96

5.1  条件格式设置
96

技巧91  当学生成绩小于60分时显示红色
96

技巧92  为成绩表中前3名成绩设置特殊格式
98

技巧93  标识出只值班一次的员工
99

技巧94  将本月的销售数据突出显示出来
100

技巧95  根据数据大小显示数据条
101

技巧96  通过单元格的颜色判断商品库存量
101

技巧97  工作表自动隔行填色
103

技巧98  自动标识出周末日期
104

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

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

技巧101  将成绩高于平均值的标注为"优"
107

技巧102  突出显示"缺考"或未填写数据的单元格
109

5.2  限制输入
110

技巧103  只允许在单元格中输入介于某个区间的小数
110

技巧104  避免输入重复的产品编码
111

技巧105  提示出货数量大于进货数量
112

技巧106  限制输入数据的长度
113

技巧107  避免输入文本值
114

技巧108  设置D列中只能输入女性员工姓名
115

技巧109  禁止录入错误格式的产品规格
115

技巧110  设置单元格输入必须包含指定内容
116

技巧111  复制数据有效性
117

技巧112  取消数据有效性设置
118

第6章  数据统计与分析实用技巧
119

6.1  数据筛选实用技巧
119

技巧113  筛选单笔销售金额大于5000元的记录
119

技巧114  筛选单笔销售金额大于5000元或小于100元的记录并排序
120

技巧115  筛选学生成绩表中分数大于平均分的学生
121

技巧116  筛选成绩表中成绩排名前5位的学生
121

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

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

技巧119  筛选出不重复记录
124

技巧120  筛选出指定时间区域的记录
125

技巧121  模糊筛选获取同一类型的数据
127

技巧122  根据名称筛选出男装的销售记录
128

技巧123  取消数据筛选
128

6.2   分类汇总实用技巧
129

技巧124  创建多种统计的分类汇总
129

技巧125  创建多级分类汇总
131

技巧126  只显示分类汇总的结果
133

技巧127  取消分类汇总
134

6.3  数据透视表的创建及编辑技巧
134

技巧128  用表格中某一列数据建立数据透视表
134

技巧129  使用外部数据源建立数据透视表
135

技巧130  在原数据透视表上更改数据源
137

技巧131  更改数据透视表默认的汇总方式
137

技巧132  统计各品牌商品销售金额占总销售金额的比
138

技巧133  显示字段的明细数据
139

技巧134  将数据透视表转换为普通表格
139

技巧135  删除数据透视表
140

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

技巧137  让数据透视表中的空白单元格显示为0
141

6.4  在数据透视表中排序筛选技巧
142

技巧138  双标签时数据字段的排序
142

技巧139  在数据透视表中设置按行排序
143

技巧140  实现同时满足两个条件的自动筛选（筛选出原表数据）
143

技巧141  添加切片器快速实现数据筛选
144

技巧142  将费用支出金额按月汇总
146

技巧143  统计成绩表中指定分数区间人数
147

6.5  自定义计算公式
148

技巧144  自定义公式求解各销售员奖金
148

技巧145  自定义公式求解各类别商品利润率
150

技巧146  自定义公式根据销售额确定次年底薪
151

技巧147  自定义公式比较1、2月支出金额差额
152

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

技巧149  删除建立的计算字段
154

第7章  图表使用中的实用技巧
155

7.1  图表新建技巧
155

技巧150  选择不连续数据源建立图表
155

技巧151  不新建图表重新更改图表的数据源
156

技巧152  快速用小图表直观比较一行或一列数据
157

技巧153  按实际需要更改系列的名称
158

技巧154  快速向图表中添加新数据
159

技巧155  在一张图表中使用两种不同的图表类型
160

技巧156  让图表绘制出被隐藏的数据
162

技巧157  以0值替代数据源中的空值
162

7.2  图表编辑技巧
163

技巧158  图表标题与表格标题同步更新
163

技巧159  设置竖排标题效果
164

技巧160  让水平轴从右向左显示
165

技巧161  设置刻度最大、最小值且显示单位
166

技巧162  根据图表需求更改刻度线标签的位置
167

技巧163  将垂直轴显示在图表正中位置
168

技巧164  使用对数刻度绘制图表
169

技巧165  一张图表中同时显示毛利与毛利率（毛利率绘制于次

坐标轴上）
170

技巧166  在图表中显示最大与最小数据
172

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

技巧168  重新调整数据系列的显示次序
174

技巧169  将饼图中特定扇面分离出来以突出显示
175

技巧170  更改复合饼图第二绘图区包含值以得到正确的图表
176

技巧171  固定图表的大小与位置让其不改变
178

技巧172  快速取消图表的所有格式
178

技巧173  将建立的图表转换为静态图片
178

技巧174  隐藏工作表中的图表
179

技巧175  保护图表不被随意更改
179

技巧176  只允许对指定图表进行编辑
180

技巧177  实现在Word中编辑Excel图表
181

技巧178  让复制到Word中的图表跟随Excel的数据源自动更新
182

技巧179  在PowerPoint演示文稿中使用Excel图表
182

第8章  用函数辅助日常行政办公
184

技巧180  根据消费卡类别与消费额派发赠品
184

技巧181  屏蔽中奖手机号码的后几位数
185

技巧182  输入身份证号码填写框
186

技巧183  嵌套使用SUBSTITUTE函数返回公司名称简称
186

技巧184  建立倒计时牌
188

技巧185  计算停车时间
188

技巧186  计算来访签到时间的区间
189

技巧187  根据活动开始日期计算各月活动天数
190

技巧188  计算国庆节到元旦节之间的工作日
191

技巧189  快速得知2013年中各节日在第几周
191

技巧190  显示高于或低于标准时间的值
192

技巧191  根据给定时间界定整点范围
193

技巧192  比较两个采购部门的采购价格
194

技巧193  根据商品的名称与颜色进行一次性调价
195

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

技巧195  计算商品运送车次
198

技巧196  统计出故障的机器台数
199

技巧197  从商品全称中提取产地信息
200

技巧198  从产品编号中提取类别
200

技巧199  提取产品的规格数据
201

技巧200  从规格数据中提取部分数据
202

技巧201  提取产品的货号
203

技巧202  从房号数据中提取单元号
204

技巧203  问卷调查时实现自动统计答案
204

技巧204  让合并的日期显示正确格式
206

技巧205  以1个百分点为单位计算奖金或扣款
207

技巧206  计算物品的快递费用
209

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

技巧208  计算上网费用
211

技巧209  返回被投诉次数最多的客服的编号
212

技巧210  根据业务处理量判断员工业务水平
212

技巧211  统计业务水平为"好"的人数
214

技巧212  根据销售额用"★"评定等级
214

技巧213  根据3项业务的完成率计算综合完成率
215

技巧214  根据产品不合格率决定产品处理办法
217

技巧215  计算8次测试中的达标次数
218

第9章  用函数辅助销售、库存管理工作
220

技巧216  根据销售数量与单价计算总销售额
220

技巧217  比较今年销售额与去年销售额
220

技巧218  自动填写销售报表中的月份
221

技巧219  根据不同返利比计算返利金额
222

技巧220  计算本月下旬的销售额总计
223

技巧221  按经办人计算销售金额
224

技巧222  只计算某两个产品的合计金额
225

技巧223  按奇数月与偶数月统计销量
225

技巧224  解决条件为文本计算结果为0的问题
227

技巧225  用通配符对某一类数据求和
227

技巧226  计算销售金额前3名合计值
228

技巧227  统计总销售额时满足指定类别、指定时间
229

技巧228  多条件统计某一类数据总和
230

技巧229  统计非工作日销售金额
231

技巧230  计算平均销售数量时取整数
231

技巧231  排除新店计算平均利润
232

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

技巧233  比较两种产品的销售利润的稳定性
234

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

技巧235  计算单日销售金额并返回最大值
235

技巧236  返回上半月单笔最高销售金额
236

技巧237  返回最低利润额
237

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

技巧239  对不连续单元格排名次
239

技巧240  统计全年各月利润值中的中位数
239

技巧241  根据不同的返利率计算各笔订单的返利金额
240

技巧242  在销售表中自动返回产品单价
241

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

技巧244  查询总金额最高的对应的店铺
244

技巧245  实现隔列求总销售金额
245

技巧246  提取季度合计值计算全年销售额
246

技巧247  动态查询各分店各月销售利润
247

技巧248  库存量过小时提示补货
248

技巧249  计算月平均出库数量
250

技巧250  根据产品编号查询库存数量
251

技巧251  指定时间段的进出库合计统计
252

第10章  用函数辅助人事工作
254

技巧252  根据年龄与学历对应聘人员二次筛选
254

技巧253  计算出员工年龄
255

技巧254  计算出员工工龄
256

技巧255  计算员工在职天数
257

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

技巧257  从身份证号码中提取出生年份
259

技巧258  从身份证号码中提取性别
260

技巧259  配合多函数从身份证号码中提取完整的出生年月日
261

技巧260  统计指定日期区间中指定性别的人数
263

技巧261  返回企业女性员工的最大年龄
264

技巧262  计算指定车间、指定性别员工的人数
265

技巧263  计算每位员工的加班时长
266

技巧264  返回值班日期对应的星期数
267

技巧265  判断值班日期是平时加班还是双休日加班
268

技巧266  在考勤表中根据当前月份自动建立日期序列
269

技巧267  在考勤表中根据各日期自动返回对应的星期数
271

技巧268  根据休假天数自动显示出休假结束日期
272

技巧269  计算年假占全年工作日的百分比
273

技巧270  计算各地点工作时间占全年天数百分比
274

第11章  用函数辅助教育统筹工作
275

技巧271  分性别判断成绩是否合格
275

技巧272  统计各班级平均分
276

技巧273  在成绩表中忽略0值求平均分
277

技巧274  统计缺考人数
278

技巧275  统计参与某项课程的总人数
278

技巧276  学员的姓名以逗号间隔显示在同一个单元格中，如何

统计总人数
279

技巧277  统计出指定性别测试合格的人数
280

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

技巧279  提取部分数据作为关键字以统计学生平均成绩
282

技巧280  计算平均分时将"缺考"的也计算在内
283

技巧281  统计学生成绩及格率
284

技巧282  统计其中一科得满分的人数
285

技巧283  统计成绩小于60分的人数（忽略0值）
286

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

技巧285  是三好学生且参加数学竞赛的人数
287

技巧286  统计连续3次考试都进入前10名的人数
288

技巧287  忽略0值求最低分数
289

技巧288  分别统计各班级第一名成绩
290

技巧289  计算成绩表中前5名的平均分
291

技巧290  统计成绩表中后5名的平均分
292

技巧291  统计成绩表中后5名的平均分（忽略0值）
292

技巧292  分奇偶行统计成绩
293

技巧293  为学生考试成绩排名次
294

技巧294  快速查询任意科目的成绩序列
295

技巧295  将多张单科成绩表合并为一张汇总表
297

技巧296  返回成绩最高的学生的姓名
299

技巧297  返回200米跑中用时最短的次数编号
299

技巧298  按姓名查询学生的各科目成绩
300

技巧299  实现对各科目平均成绩查询
302

技巧300  实现查询各科目成绩中的最高分
304

第12章  用函数辅助日常财务工作
306

技巧301  让计算得到金额显示为"余款：15,850.00"形式
306

技巧302  判断应收账款是否到期
306

技巧303  计算总借款天数
307

技巧304  计算借款至今日的时长
308

技巧305  计算还款剩余天数
309

技巧306  利用DAYS360函数判断借款是否逾期
310

技巧307  统计大于12个月的账款
311

技巧308  根据员工工龄自动追加工龄工资
312

技巧309  按不同性质统计应收款
313

技巧310  分单位统计账龄
315

技巧311  统计各部门工资总额
318

技巧312  在工资统计表中得出多种汇总方式的结果
319

技巧313  计件工资中的奖金计算
320

技巧314  计算一车间女职工平均工资
322

技巧315  统计工资大于3000元的人数
323

技巧316  根据多条件计算员工年终奖
323

技巧317  计算总工资时去除某一个（或多个）部门
324

技巧318  计算指定车间、指定性别员工的平均工资（双条件）
325

技巧319  统计出指定性别工资大于2500元的人数
326

技巧320  返回指定车间、指定性别员工的最高工资
327

技巧321  计算个人所得税
328

技巧322  利用IF函数进行金额校验
331

技巧323  票据中将总金额分列填写
331

技巧324  计算固定资产已使用时间
334

技巧325  用直线法计算出固定资产的每年折旧额
335

技巧326  用年数总和法计算出固定资产的每年折旧额
336

技巧327  用固定余额递减法计算出固定资产的每年折旧额
337

技巧328  用双倍余额递减法计算出固定资产的每年折旧额
337

技巧329  计算贷款的每年偿还额
338

技巧330  计算贷款每年偿还金额中有多少是利息
339

技巧331  计算贷款每年偿还金额中有多少是本金
340

技巧332  在等额本金还款方式下计算某贷款的利息
341

技巧333  计算分期存款的未来值
342

技巧334  判断购买某项保险是否合算
343

技巧335  计算出贷款的清还年数
343

技巧336  计算出某项投资的投资期数
344

第13章  Excel工作环境个性化设置技巧
345

技巧337  更改工作表网格线的默认颜色
345

技巧338  取消网格线
345

技巧339  重新设置工作表默认字体、字号
346

技巧340  更改新建工作簿时默认包含的工作表数量
347

技巧341  为工作表设置背景效果
347

技巧342  隐藏工作表标签
348

技巧343  自定义快速访问工具栏
349

技巧344  定制适合自己的工具栏
351

技巧345  添加"开发工具"选项卡
353

技巧346  锁定常用文档
354

技巧347  清除打开的文件记录
355

技巧348  批量修改多个文档作者
356

技巧349  将工作簿保存为Excel 97-2003完全兼容的文档
357

技巧350  将Excel报表发布成网页
358

第14章  表格安全与打印技巧
360

14.1  工作表安全设置技巧
360

技巧351  加密工作簿
360

技巧352  隐藏含有重要数据的工作表
361

技巧353  设置只允许用户进行部分操作
362

技巧354  指定工作表中的可编辑区域
362

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

技巧356  设置可编辑区域的权限
364

技巧357  隐藏公式以实现保护
365

技巧358  阻止文档中的外部内容
367

技巧359  宏安全性设置
367

技巧360  禁用文档中的ActiveX控件
368

14.2  工作表页面设置技巧
369

技巧361  手动直观调整页边距
369

技巧362  让表格显示在纸张的正中间
371

技巧363  调整分页的位置
372

技巧364  在任意需要的位置上分页
373

技巧365  设置页脚起始页码为所需的页数
375

14.3  工作表打印设置技巧
375

技巧366  打印指定的页
375

技巧367  打印工作表中某一特定区域
376

技巧368  添加多个打印区域
377

技巧369  一次性打印多张工作表
378

技巧370  一次性打印多个工作簿
378

技巧371  将数据缩印在一页纸内打印
379

技巧372  打印超宽工作表
380

技巧373  将表格标题打印到每一页中
380

技巧374  将行、列号一起打印出来
382

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

技巧376  将工作表上的网格线打印出来
383

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

技巧378  巧妙打印工作表背景
384

第15章  Excel日常办公操作常见问题集
386

技巧379  鼠标指向任意功能按钮时都看不到屏幕提示了
386

技巧380  如何删除最近的文件列表
386

技巧381  向下滑动鼠标查看文档时可以让表格标题与列标识

始终显示吗
387

技巧382  同时打开了多个Excel文档可以一次性将它们都关闭吗
388

技巧383  工作簿中的垂直滚动条和水平滚动条不见了该如何处理
388

技巧384  打开工作表发现没有行号、列标了
389

技巧385  为什么滑动鼠标中键，工作表会随之进行缩放
389

技巧386  想复制公式却找不到填充柄了
390

技巧387  对数字应用了"文本"后，数字数据并没有变成文本数据
391

技巧388  为什么在计算结果的单元格中始终显示公式
391

技巧389  解决公式中计算结果无法自动更新的问题
392

技巧390  怎样查找星号*（非通配符）
392

技巧391  如何定位和取消循环引用
393

技巧392  添加了"自动筛选"，日期不能分组筛选了
393

技巧393  只想复制筛选结果但粘贴后是所有未筛选前的数据
394

技巧394  添加到用户自定义图表类型中的文本框和图片等没有

包括在内
395

技巧395  条形图垂直轴标签从大到小显示，有办法调整吗
395

技巧396  更改了图表数据源，为何图表中数据标签不能同步更改
396

技巧397  解决Excel图表插入到Word中只显示黑色边框问题
397

技巧398  完成合并计算后，原表数据更新了结果不能同步更新
398

技巧399  在数据透视表中添加计算项时，为什么"计算项"为灰色

不可用
398

技巧400  可以使用图片来填充批注框吗
399

技巧401  批注框的形状可以更改吗
400

技巧402  解决更新数据源后数据透视表不能相应更新的问题
400

技巧403  解决刷新数据透视表后所有格式都消失的问题
401

技巧404  公式返回"#DIV/0!"错误值
402

技巧405  公式返回"#N>A"错误值
402

技巧406  公式返回"#NAME?"错误值
403

技巧407  公式返回"#NUM!"错误值
405

技巧408  公式返回"#VALUE!"错误值
406

技巧409  公式返回"#REF!"错误值
407

 Excel高效办公应用技巧下载