Excel高效办公应用大全：方法、技巧及实用案例PDF电子书下载

Chapter1 选择单元格 1

1.1利用鼠标选择单元格 1

1.选择连续的单元格区域 1

2.选择不连续的单元格区域 1

3.选择单行 1

4.选择连续的多行 2

5.选择不连续的多行 2

6.选择单列 2

7.选择连续的多列 2

8.选择不连续的多列 2

9选取工作表的所有单元格 2

10.选择多个工作表的相同单元格区域 2

1.2利用名称框选择单元格 3

1.选择连续的单元格区域 3

2.选择不连续的单元格区域 3

3.选择单行 3

4.选择连续的多行 3

5.选择不连续的多行 3

6.选择单列 3

7.选择连续的多列 4

8.选择不连续的多列 4

9.选择多个工作表的相同单元格区域 4

1.3利用定位对话框选择单元格 4

1.4利用定位条件对话框选择特殊的单元格 5

1.选择有计算公式的单元格区域 5

2.选择计算公式出错的单元格区域 6

3.选择为常量的单元格区域 6

4.选择为数字常量的单元格区域 7

5.选择为文本常量的单元格区域 7

6.选择为逻辑值常量的单元格区域 8

7选择为空值的单元格区域 8

8.选择当前的单元格区域 8

9.选择可见的单元格区域 9

10.选择包含数组公式的单元格区域 10

11.选择有批注的单元格区域 11

12.选择有条件格式的单元格区域 11

13.选择有数据有效性的单元格区域 12

14.选择最后一个单元格 13

15.选择除公式外的所有不为空的单元格 13

1.5利用快捷键定位单元格 13

1.定位到单元格区域的边缘行或列的单元格 14

2.定位到工作表的第一行（列）和最后一行（列） 14

3.快速定位到数据区域的第一个单元格和最后一个单元格 14

1.6利用定义的名称定位单元格区域 14

1.在定义名称对话框中查看到某名称代表的单元格区域 14

2.在名称框中定位到某名称代表的单元格区域 15

3.在名称框中定位到几个名称代表的单元格区域 15

4.在“定位”对话框中定位到几个名称代表的单元格区域 15

1.7利用查找和替换对话框选择单元格 16

1.选取有指定数据的单元格区域 16

2.选取有特定字符的单元格区域 17

3.选取指定单元格格式的单元格区域 17

4.选取指定字体格式的单元格区域 19

5.选取区分字母大小写的单元格区域 19

6.选取单元格匹配的单元格区域 20

7.选取当前工作簿中所有满足匹配条件的单元格区域 20

1.8快速查看选择的单元格区域共有多少行和多少列 20

1.快速查看选择了多少整行 20

2.快速查看选择了多少整列 21

3.快速查看选择的单元格区域共有多少个单元格 21

1.9只允许选择某些单元格区域 22

Chapter2 处理单元格 25

2.1插入行 25

1.每隔一行插入一空行 25

2.每隔一行插入两个空行 27

3.每隔一行插入多个空行 28

4.每隔两行插入一空行 28

5.每隔多行插入一空行 29

6.每隔多行插入多个空行 30

2.2插入列 31

2.3隐藏行或列 31

1通过隐藏命令隐藏行或列 31

2.通过将行高或列宽设置为零的方式隐藏行或列 31

3.通过快捷键隐藏行或列 31

4.取消隐藏行或列 32

5.通过限制滚动区域的方式对行或列进行特殊隐藏 32

2.4删除行、列和单元格 33

1.快速删除有特定数据的单元格 33

2.快速删除有特定数据单元格所在的行 33

3.快速删除有特定数据单元格所在的列 33

4.一次性删除多行或多列 34

5.删除各列数据都相同的行 34

6.删除数据区域内的所有空行 36

7.删除数据区域内的所有空列 37

2.5合并单元格和取消合并单元格 37

1.将数个单元格的数据填充到一个合并单元格 38

2.取消合并单元格并将数据复制到每个单元格中 38

chapter3 输入数据 41

3.1输入文本 41

1.快速输入特殊符号 442

2.输入数值型文本 42

3.输入文本序列 43

4.输入自定义的文本序列 44

5.使用自动更正工具快速输入汉字文本 46

6.快速输入复杂的序列号 47

7.快速输入中文车间号“一车间、二车间、三车间…” 49

8.快速输入中文小写数字文本序列“一、二、三、四…” 49

9.快速输入中文大写数字文本序列“壹、贰、叁、肆…” 50

10.限制输入文本的长度 50

3.2输入数字 51

1.快速输入大量含小数点的数字 51

2.快速输入分数 52

3.利用有效性对数字进行限制输入 53

4.利用有效性输入规定格式的数字 55

5.限制只能输入比上行数字大的数字 55

6.在键盘数字区快速输入数字计算表达式并计算出结果 56

7.在数字前自动填充0 57

8.在数字后自动填充0 57

9.在数字后自动添加固定的数字 58

10.输入带小数的数字后自动将小数转换为上标 58

11.快速输入带千分号（％。）的数字 59

3.3输入日期 60

1.快速输入当前日期 60

2.输入日期的简单方法 60

3使用函数输入当前日期 61

4.限制只能输入某个日期区间内的日期 61

5.判断输入的日期数据是否为合法日期 61

6.输入日期应注意的问题 62

3.4输入时间 62

1.快速输入当前时间 62

2.输入时间的简单方法 63

3使用函数输入当前时间 63

4.如何输入没有小时只有分钟和秒的时间 63

5.限制只能输入某个时间区间内的时间 63

6.输入时间应注意的问题 64

3.5利用有效性控制数据输入 64

1.输入序列数据 64

2利用有效性对数据输入范围进行多重限制 65

3.利用有效性禁止输入重复的数据 67

4.当特定单元格非空时才可输入数据 67

5.禁止在输入数据时出现空行 68

6不让几列同时输入数据 69

7.只能输入其他工作表规定的数据 70

3.6利用查找和引用函数输入数据 71

1.根据产品编码自动输入产品单价 71

2.将其他单元格的数据依次向下填充 71

3.7一些常用的输入数据小窍门 72

1.快速输入上一个单元格的数据 72

2快速输入左边一个单元格的数据 72

3.在不同的单元格输入相同的数据 72

4.快速在合并单元格中填充数据序列 72

3.8输入数据的高级应用案例 73

1输入数据的同时进行排序 73

2.输入数据的同时输入当前日期 74

3.单元格的颜色在输入数据后变成另一种颜色 74

4.只能在没有公式的单元格输入数据 76

5.控制已经输入数据的单元格数据不被改动 77

6.禁止删除单元格而允许在单元格内输入数据 78

7.通过记录单窗体输入数据 78

8.在单元格时输入数据后立即显示为＊＊＊＊ 79

9.输入带井号（＃）上标的序列号1#、 2#、 3＃等 80

10.输入带1个数字上标的单位文本（如m2、 m3、 mm2等） 81

3.9如何为工作表对象指定事件 81

1.如何为工作表对象指定事件 82

2.工作表对象的常用事件 83

Chapter4 编辑数据 85

4.1编辑修改数据的基本方法 85

4.2查找和替换数据 85

4.3复制移动数据 87

1.复制移动数据的基本方法 87

2复制移动数据的快捷方法 87

3.复制可见单元格数据 87

4.4对单元格区域数据进行相同的运算修改 88

1.对连续的单元格区域数据进行相同的运算修改 88

2.对不连续的单元格区域数据进行相同的运算修改 89

3.注意事项 89

4.5清除单元格数据 90

1.仅删除单元格的数值和公式 90

2.选择性地清除单元格数据 90

4.6改变数据区域的位置 90

1.将行列数据进行对调 90

2.将两个单元格区域数据互换位置 91

4.7使用记录单编辑数据 92

1.使用记录单修改数据 92

2.使用记录单删除数据 92

3.使用记录单查找数据 93

4.8使用朗读工具核对数据 93

4.9圈释无效数据 94

4.10找出重复输入的数据 95

1.从单列数据中找出重复的数据并用颜色标示出来 95

2.从单列有重复数据中整理出没有重复的数据清单 96

3.从两列数据中找出重复的数据并用颜色标示出来 97

4.从两列有重复数据中整理出没有重复的数据清单 98

5.从两个有重复数据的工作表中整理出没有重复的数据清单 101

Chapter5 设置数据的显示格式 103

5.1条件格式 103

1.条件格式的使用方法 103

2.根据数值大小设置不同字体颜色或单元格颜色 104

3.找出每行或每列的最大值和最小值并用不同的颜色标示 106

4.隐藏指定单元格区域的0值 108

5.巧妙实现表格自动隔行着色 108

6.根据某列是否有数据设置边框 110

7.应收账款到期提醒 111

8.应收账款提前10天提醒 111

9.醒目显示逾期的应收账款 112

10.将两列中不同的数据用不同的颜色表示出来 112

11.将相同数据的行规整到一起并用两种颜色标示 113

12.利用多条件醒目显示不合格数据 114

13.利用多条件醒目显示完成率 115

14.删除条件格式 116

5.2自定义数字显示格式 116

1.只显示带两位小数的百位以上数字 117

2.只显示千位数字 117

3.只显示万位数字 118

4.只显示十万位数字 118

5.只显示百万位数字 118

6.将负数显示为正数 119

7.将正数显示为负数 120

8.为输入数字自动添加说明文本 120

9.隐藏单元格的数据 121

11.将单元格的数字显示为其他字符 121

12.设置电话号码的显示格式 121

13.使用Excel自带的自定义数字格式 122

14.将输入数字的小数表示为上标 122

15.将数字居中显示并对齐小数点 123

16.将分数居中显示并对齐分数线 123

5.3自定义条件式数字格式 124

1.自定义条件式数字格式的语法结构 124

2.根据数据的种类以不同的颜色表示 124

3.根据数值的大小将数字表示为不同颜色 124

4.利用自定义格式控制数字的输入和显示 125

5联合使用自定义数字格式和条件格式实现数据的多种颜色显示 126

5.4自定义日期格式 126

1.将日期显示为中文的年月日 126

2.将日期显示为中文的星期 127

3.将日期显示为中文的年月日和星期 128

4.将日期显示为英文的年月日 128

5.将日期显示为英文的星期 129

6.将日期显示为英文的年月日和星期 129

7.将日期显示为纯数字格式 130

5.5自定义时间格式 130

1.将时间显示为24小时制的标准格式 130

2.将时间显示为12小时制的格式 131

3.将时间显示为中文格式 131

4.将时间显示为计时格式 132

5.显示超过24小时的累计时间 132

Chapter 6公式、函画数和名称的基本使用技巧 135

6.1公式概述 135

1.公式的构成 135

2公式的运算符及其优先级 135

3.公式的输入及修改 136

4.单元格的引用 136

5.公式的复制与移动 137

6.使用数组公式 137

7.公式的显示与隐藏 137

8.保护公式 138

9.公式中星号（＊）的妙用 140

10.公式中减号（-）的妙用 140

11.公式中加号（＋）的妙用 140

12.如何在公式中显示中间计算结果 140

13.快速复制公式小窍门 141

6.2函数概述 141

1.函数的种类 142

2.函数的基本语法 142

3.函数的调用 142

4.嵌套函数的使用 143

5.使用分析工具库的函数 144

6.3使用名称 145

1.定义名称的规则 145

2.定义名称的基本方法 145

3.为可变的单元格区域定义名称 147

4.如何快速查看定义名称所代表的单元格区域 151

5.获取当前工作簿的所有名称及其引用位置清单 152

6.名称应用案例：进行计算 152

7.名称应用案例：查询数据 153

8.名称应用案例：绘制动态图表 154

6.4宏表函数及其应用 155

1.什么是宏表函数 155

2.常用的宏表函数 156

3.将没有等号的表达式进行计算 156

4.将公式的计算结果显示为公式表达式 157

5.根据单元格颜色求和 158

6.根据单元格字体颜色求和 158

7.根据单元格字形求和 159

Chapter7 数值和日期时间计算 161

7.1数字计算 161

1.单元格有长度不为零的空字符串时的计算 161

2.对数值型文本进行计算 162

3.如何处理Excel的计算误差 163

4.如何处理明细加总与总和不符的问题 164

5.解决单项百分比数字加总不等于100％的问题 165

6.尽量避免数据的重复循环计算 166

7.2日期计算 166

1.Excel对日期的处理方式 166

2.今天是星期几（设计考勤表） 166

3.今天是今年的第几周 168

4.确定3个月前的月底日期 169

5.3个月前的15号距今天有多少天 170

6.确定3个月后的月底日期 171

7.3个月后的15号距今天有多少天 171

8.今天所在的月份有多少天 172

9.确定今天所在的月份月底日期 172

10.确定两个日期之间的天数 173

11.确定两个日期之间的工作日天数 173

12.确定两个日期之间的双休日天数 173

13.确定两个日期之间的星期六天数 174

14.确定两个日期之间的星期日天数 174

15.确定两个日期之间的星期一、星期二、…天数是多少 174

16.将3个单元格的数字合并为可以计算的日期序列 175

17.计算若干年后的具体日期 176

18.计算若干年前的具体日期 176

19.计算工龄（不满半年按半年计…满半年不满1年按1年计） 176

20.计算两个日期之间有多少年、多少月和多少日 177

21.将总月数转换为年月 178

22.获取日期中的年、月和日数字 178

23.员工生日的提前提醒 178

7.3时间计算 180

1.Excel对时间的处理方式 180

2.计算时间的规则 180

3.计算加班时间 181

4.跨月情况下的加班时间计算 183

5.不满半小时的按半小时计、满半小时的按1小时计 185

6.不满1分钟的按1分钟计 185

7.不满10秒的按10秒计 186

8.获取时间的小时、分钟和秒数字 186

Chapter8 数据汇总计算 187

8.1单条件求和 187

1.每隔一行求和 187

2.每隔两行求和 188

3.每隔多行求和 189

4.每隔一列求和 190

5.每隔两列求和 191

6.每隔多列求和 191

7.对含有错误值的单元格区域进行求和 192

8.对含有文本型数字的单元格进行求和 192

9.对含有特殊字符的单元格区域进行求和 193

10.对单元格区域内指定的数字进行求和 195

11.对单元格区域内所有的正数或负数进行求和 195

12.动态汇总当日数据和以前累计数据 196

13.按照月份累加求和 197

14.动态向下或向右累加计算 197

15.不经过排序对数据区域的前N个最大或最小数据进行求和 198

16.快速计算1～100的数字之和 199

17.对可见的数据列单元格求和 199

18.对隐藏的数据列单元格进行求和 200

19.对可见的数据行单元格求和 201

20.对隐藏的数据行单元格进行求和 202

8.2多条件求和 202

1.对指定时间区间内的数据进行求和 203

2.不同单位的不同商品型号的销售额累加 204

3.按年月进行汇总 207

4.统计一个月内周一到周五的加班小时数 209

5.按周次和星期几对不同商品进行分类汇总 210

6.按周对不同商品进行分类汇总 214

7.按时间进行多条件加总计算 215

8.存在合并单元格情况下的多条件加总计算 216

9.利用数据库函数进行多条件加总计算 217

8.3求平均值、最大值和最小值 219

1.每隔一行求平均值、最大值和最小值 219

2.每隔两行求平均值、最大值和最小值 220

3.每隔多行求平均值、最大值和最小值 220

4.对含有错误值的单元格区域求平均值、最大值和最小值 221

5.对含有零值的单元格区域求平均值 221

6.除去一个最大值和一个最小值后对单元格区域求平均值 222

7.单条件求平均值、最大值和最小值 222

8.多条件求平均值、最大值和最小值 224

9.利用数据库函数进行多条件求平均值、最大值和最小值 224

10.对可见的数据列单元格求平均值 225

11.对隐藏的数据列单元格求平均值 225

12.对可见的数据行单元格求平均值 226

13.对隐藏的数据行单元格求平均值 226

14.跑步测验7次，取最好5次成绩的平均值 227

15.计算加权平均值 228

16.计算移动平均值 228

8.4计数问题 230

1.统计单元格区域内符合条件的单元格数目 230

2.统计单元格区域内非空单元格数目 230

3.统计单元格区域内空单元格数目 231

4.统计单元格区域内错误值的个数 231

5.统计单元格区域内文本单元格数目 232

6.统计单元格区域内数字单元格数目 232

7.比较两个大小相同区域的数据是否——对应 233

8.多条件计数：公司员工构成分析 234

9.多条件计数：统计超过某个最大工时的员工人数统计 236

8.5当前工作簿多个工作表数据的汇总问题 236

1.多个有关联的工作表数据的汇总计算 236

2.多个工作表数据区域完全相同的汇总计算 244

3.多个工作表数据区域不尽相同的汇总计算 247

4.复杂的存在合并单元格并且跨表情况下的多条件加总计算 251

8.6多个工作簿数据的汇总问题 257

Chapter9 数据的排序与筛选 261

9.1数据排序 261

1.排序规则 261

2.按照汉字姓氏笔画排序 261

3.按行排序 262

4.区分大小写排序 262

5.对多个关键字进行排序 263

6.自定义排序 263

7.先排序再恢复原始状态 264

8.提取唯一值并排序 264

9.输入姓名后按拼音自动排序 267

10.按照行基于字符串内特征字符进行排序 268

11.根据单元格颜色进行排序 268

12.利用排序函数RANK、LARGE、SMALL进行排序 269

13.利用排位与百分比排位工具进行排名 270

9.2数据筛选 272

1.自动筛选 272

2.筛选前10个最大值或最小值 273

3.对数据进行比较的自定义筛选 274

4.对包含特定文本的记录进行筛选 275

5.高级筛选 276

6.根据计算结果进行筛选 277

7.取消筛选 278

8.保存筛选结果 278

9.3使用列表统计分析数据 280

1.创建列表 280

2.利用列表分析数据 282

3.删除列表 282

Chapter10 数据查询 283

10.1查找重复数据 283

1.查找某数据在某列数据中第N次出现的位置 283

2.查找某数据在某行数据中第N次出现的位置 284

3.查找重复次数最多的数据及其重复次数 285

4.查找某列数据中不重复数据并生成一个新数据清单 287

5.查找某行数据中不重复数据并生成一个新数据清单 288

10.2精确查找数据 288

1.查找某数据在数据列中的位置及单元格地址 288

2.查找某数据在数据行中的位置及单元格地址 289

3.查找数据区域中最大值或最小值的位置及单元格地址 289

4.利用INDEX函数进行精确查找 290

5.利用HLOOKUP函数进行精确查找 291

6.利用VLOOKUP函数进行精确查找 293

7.利用OFFSET函数进行精确查找 295

8.利用名称进行精确查找 297

9.区分字母大小写的精确查找 298

10.查找数据行中最后的非空单元格数据 299

11.查找数据列中最后的非空单元格数据 301

12.查找出现次数最多的前10个项目 302

13.根据数据中的某几个字符查找数据 304

14.多条件下的精确查找 305

10.3近似查找数据 306

1.查找最接近于查找值的最大数据 306

2.查找最接近于平均值的数据 307

10.4从多个区域中查找数据 308

1.从当前工作表的多个区域中查找数据：简单情况 308

2.从当前工作表的多个区域中查找数据：复杂情况 309

3.从多个工作表中查找数据 311

5.查询汇总多个工作表中某行数据 312

6.查询汇总多个工作表中某列数据 313

7.查询汇总多个工作表中某些单元格区域的数据 314

8.快速将多个工作表中某些单元格区域数据进行汇总 315

10.5利用数据透视表进行查询 316

1.查询重复数据 316

2.查询不重复数据并生成新数据清单 317

3.查询满足多列条件的重复数据 317

4.从单个数据区域查询满足条件的数据 319

5.从多个数据区域查询满足条件的数据 321

6.从其他工作簿中查询数据 326

10.6利用导入数据工具查询数据 330

1.查询某个工作表中满足条件的数据 331

2.快速查询并核对多个工作表中的数据 334

3.查询仅存于某个工作表中的数据 336

4.查询两个工作表中都存在的数据 339

5.查询其他工作簿中满足条件的数据 340

10.7数据查询综合应用案例：制作工资条 343

1.无大标题行情况下的工资条制作 344

2.有大标题行情况下的工资条制作 345

Chapter11 利用数据透视表进行据处理和报表分析 347

11.1制作数据透视表的基本方法 347

1.以当前工作簿的单个数据区域制作数据透视表 347

2.以当前工作簿的多个数据区域制作数据透视表 351

3.以其他工作簿的数据制作数据透视表 357

4.以数据库数据制作数据透视表 362

11.2设置数据透视表 367

1.修改字段名 367

2.设置字段的汇总方式 367

3.设置字段的数字格式 368

4.取消数据透视表的行总计和列总计 368

5.将外部字段合并居中显示 368

6.将分类汇总行显示在组的顶部 369

7.取消字段的分类汇总 370

11.3利用数据透视表进行统计分析 370

1.通过重新布局数据透视表进行统计分析 370

2.通过设置字段的显示方式进行统计分析 371

3.通过组合字段进行统计分析 372

11.4自定义计算字段和计算项 374

1.添加自定义计算字段 375

2.添加自定义计算项 376

3.删除自定义计算字段和自定义计算项 378

Chapter12 处理文本据 379

12.1获取文本的有关信息 379

1.判断单元格数据是否是文本 379

2.获取文本的字符个数 380

3.获取文本的字节个数 380

12.2将数字型文本数据转换为数值 380

1.利用智能标记 380

2.重新设置格式 380

3.选择性粘贴 381

4利用VALUE函数 381

5.利用查找替换工具 382

12.3查找特定字符在文本中最先出现的位置 383

1.查找特定字符在文本中的位置（区分大小写） 383

2.查找特定字符在文本中的位置（不区分大小写） 383

12.4获取文本的部分字符 383

1.从文本字符串的左边取指定长度的字符 384

2.从文本字符串的右边取指定长度的字符 384

3.从文本字符串的指定位置取指定长度的字符 385

4.从某个特定字符位置取指定长度的字符 385

12.5合并文本 387

1.将不同单元格的数据合并为一行文本 387

2.将不同单元格的数据合并为多行文本 387

12.6清除文本中的特殊字符 388

1.清除文本前后的空格 388

2.清除文本前后打印不出的特殊字符 388

Appendix Excel用键盘快捷键 389