目录

第1章.Excel工作表操作技能与技巧...............................01

1.1　选择工作表..................................................................................................02

1.1.1　选择一个工作表的常规方法........................................................................02

1.1.2　选择多个工作表的常规方法........................................................................02

1.1.3　使用“激活”对话框快速选择某个工作表..............................................02

1.1.4　使用名称框快速选择某个工作表...............................................................03

1.1.5　使用“定位”对话框快速选择某个工作表..............................................03

1.1.6　快速选择定义有某个名称的工作表...........................................................03

1.1.7　快速选择有某个特殊数据的工作表...........................................................04

1.1.8　快速切换工作表.............................................................................................04

1.2　隐藏和保护工作表.......................................................................................05

1.2.1　隐藏/显示工作表的普通方法....................................................................05

1.2.2　隐藏/显示工作表的特殊方法....................................................................06

1.2.3　防止所有工作表被删除、移动、复制和重命名.....................................07

1.2.4　保护工作表全部数据不被改动...................................................................08

1.3　工作表的其他操作.......................................................................................09

1.3.1　插入新工作表..................................................................................................09

1.3.2　移动工作表......................................................................................................10

1.3.3　复制工作表......................................................................................................10

1.3.4　将工作表复制为新工作簿............................................................................10

1.3.5　重命名工作表..................................................................................................11

1.3.6　删除工作表......................................................................................................11

1.3.7　设置工作表标签颜色.....................................................................................11

1.4　工作表界面显示设置....................................................................................12

1.4.1　显示/不显示网格线.....................................................................................12

1.4.2　设置网格线颜色.............................................................................................13

1.4.3　显示/不显示分页预览.................................................................................14

1.4.4　显示/不显示表格的零值............................................................................15

1.4.5　拆分窗格..........................................................................................................16

1.4.6　冻结窗格..........................................................................................................17

1.4.7　并排显示工作表.............................................................................................17

1.5　设置Excel选项............................................................................................19

1.5.1　设置新工作簿默认的工作表数...................................................................20

1.5.2　设置新工作簿默认的字体和字号...............................................................20

1.5.3　设置自动重算/手动重算............................................................................21

1.6　自定义快速访问工具栏和功能区.................................................................22

1.6.1　自定义快速访问工具栏................................................................................22

1.6.2　自定义功能区..................................................................................................23

第2章.Excel单元格操作技能与技巧...............................25

2.1　快速选择单元格...........................................................................................26

2.1.1　选择单元格的常规方法................................................................................26

2.1.2　使用快捷键快速选择较大的单元格区域..................................................26

2.1.3　使用名称框快速选择单元格区域...............................................................27

2.1.4　使用“定位”对话框快速选择单元格......................................................27

2.1.5　使用“定位条件”对话框快速选择特殊单元格.....................................28

2.1.6　快速选择有特定数据的单元格...................................................................29

2.1.7　快速选择有指定格式的单元格...................................................................30

2.1.8　快速选取所有的合并单元格........................................................................31

2.1.9　快速选择数据区域的第一个单元格和最后一个单元格........................31

2.1.10　同时选择几个工作表的相同单元格区域..................................................31

2.2　操作单元格实用技能与技巧.........................................................................32

2.2.1　复制单元格的常见操作................................................................................32

2.2.2　将单元格复制为图片.....................................................................................34

2.2.3　移动单元格......................................................................................................35

2.2.4　插入单元格......................................................................................................35

2.2.5　插入复制的单元格.........................................................................................35

2.2.6　一次插入多个空行.........................................................................................35

2.2.7　每隔几行插入几个空行................................................................................36

2.2.8　删除单元格......................................................................................................36

2.2.9　删除数据区域中所有的空行和空列...........................................................37

2.2.10　取消合并单元格...........................................................................................37

2.2.11　跨列居中显示数据.......................................................................................38

2.3　单元格清除操作技能与技巧.........................................................................39

2.3.1　清除单元格内容.............................................................................................39

2.3.2　清除单元格批注.............................................................................................39

2.3.3　清除单元格的普通格式................................................................................39

2.3.4　清除单元格的自定义数字格式...................................................................40

2.3.5　清除单元格的条件格式................................................................................40

2.3.6　清除单元格的数据验证................................................................................40

2.3.7　清除单元格的普通公式................................................................................41

2.3.8　清除单元格的数组公式................................................................................42

第3章.设置单元格格式技能与技巧..................................43

3.1　设置常规的固定格式....................................................................................44

3.1.1　合理设置边框..................................................................................................44

3.1.2　合理设置对齐..................................................................................................45

3.1.3　合理设置数字格式.........................................................................................45

3.1.4　合理设置字体和字号.....................................................................................46

3.1.5　合理设置行高和列宽.....................................................................................46

3.1.6　合理设置合计数格式.....................................................................................47

3.2　套用表格格式和单元格样式.........................................................................47

3.2.1　套用表格格式..................................................................................................48

3.2.2　套用单元格样式.............................................................................................50

3.3　设置自定义数字格式....................................................................................51

3.3.1　了解自定义数字格式的代码结构...............................................................52

3.3.2　将差异数分别设置为不同格式...................................................................52

3.3.3　根据数字大小分别设置不同格式...............................................................53

3.3.4　缩小位数显示数字.........................................................................................54

3.3.5　日期格式的特殊设置.....................................................................................55

3.3.6　时间格式的特殊设置.....................................................................................56

3.4　设置条件格式...............................................................................................57

3.4.1　标识大于/小于指定值的单元格...............................................................58

3.4.2　标识含有特殊字符的单元格........................................................................58

3.4.3　标识重复数据单元格.....................................................................................59

3.4.4　标识最前/最后数据单元格........................................................................60

3.4.5　使用数据条定性标识数据大小...................................................................60

3.4.6　使用色阶标识数据分布................................................................................61

3.4.7　使用图标集标识数据.....................................................................................63

3.4.8　创建新建规则的条件格式............................................................................64

3.4.9　条件格式的典型应用：建立提醒模型.......................................................66

3.4.10　编辑、删除条件格式....................................................................................69

3.4.11　使用公式来设置条件格式的重要注意事项...........................................70

3.4.12　条件格式综合应用案例..............................................................................70

第4章.数据排序实用技能与技巧.......................................73

4.1　数据排序规则及注意事项............................................................................74

4.1.1　基本数据排序规则.........................................................................................74

4.1.2　月份名称的排序问题.....................................................................................75

4.1.3　编码类数据的排序问题................................................................................75

4.1.4　对某列排序的问题.........................................................................................76

4.1.5　纯文本和文本型数字混合排序的问题......................................................77

4.1.6　有合并单元格的排序问题............................................................................78

4.1.7　有计算公式的排序问题................................................................................78

4.2　数据排序的基本方法....................................................................................79

4.2.1　使用命令按钮快速排序................................................................................79

4.2.2　使用对话框进行排序.....................................................................................80

4.3　不同类型标题的数据排序............................................................................83

4.3.1　单行标题的数据排序.....................................................................................83

4.3.2　多行标题（有合并单元格）的数据排序..................................................83

4.4　多条件排序..................................................................................................84

4.4.1　多条件排序的基本方法................................................................................85

4.4.2　数值与格式组合的多条件排序...................................................................86

4.5　自定义序列排序...........................................................................................88

4.5.1　创建自定义序列.............................................................................................88

4.5.2　单独使用自定义序列进行数据排序...........................................................90

4.5.3　常规排序与自定义序列排序组合使用......................................................92

4.6　按照关键词匹配排序....................................................................................95

4.6.1　不区分关键词出场次序的关键词匹配排序..............................................95

4.6.2　区分关键词出场次序的关键词匹配排序..................................................96

4.6.3　对所有关键词匹配排序................................................................................97

4.7　编辑、删除排序条件....................................................................................98

4.7.1　编辑已有的排序条件.....................................................................................98

4.7.2　删除排序条件..................................................................................................99

第5章.数据筛选实用技能与技巧.....................................100

5.1　自动筛选及注意事项..................................................................................101

5.1.1　建立自动筛选...............................................................................................101

5.1.2　多行标题的自动筛选..................................................................................102

5.1.3　数据区域有空行时的自动筛选................................................................102

5.1.4　为什么数据区域右侧没数据的列也出现了筛选标记.........................103

5.1.5　当工作表有大量数据和计算公式时筛选数据......................................104

5.1.6　取消自动筛选...............................................................................................104

5.2　三类数据的筛选操作..................................................................................104

5.2.1　日期数据筛选...............................................................................................104

5.2.2　文本数据筛选...............................................................................................106

5.2.3　数字筛选.......................................................................................................107

5.3　按颜色筛选................................................................................................108

5.3.1　按单元格颜色筛选......................................................................................108

5.3.2　按字体颜色筛选..........................................................................................109

5.3.3　单元格颜色和字体颜色都存在情况下的筛选......................................109

5.4　自定义筛选................................................................................................110

5.4.1　自定义筛选的运用......................................................................................110

5.4.2　在同一列中做多个项目筛选.....................................................................111

5.4.3　在不同列中做多次筛选.............................................................................111

5.4.4　清除某个字段的筛选..................................................................................112

5.5　高级筛选....................................................................................................112

5.5.1　高级筛选：设计筛选条件区域.................................................................112

5.5.2　高级筛选：基本操作方法..........................................................................114

5.5.3　高级筛选：将筛选结果显示到其他位置................................................116

5.5.4　清除高级筛选...............................................................................................116

5.6　筛选的其他操作.........................................................................................117

5.6.1　复制筛选结果...............................................................................................117

5.6.2　清除所有筛选...............................................................................................117

5.6.3　编辑筛选出来的数据和公式.....................................................................118

第6章.数据拆分列实用技能与技巧................................120

6.1　使用Excel快速填充工具拆分列................................................................121

6.1.1　明显规律情况下的快速填充.....................................................................121

6.1.2　存在特殊数据情况下的快速填充............................................................122

6.2　使用Excel分列工具拆分列.......................................................................123

6.2.1　根据单个分隔符进行分列.........................................................................124

6.2.2　根据多个分隔符进行分列.........................................................................126

6.2.3　根据固定宽度进行分列.............................................................................128

6.3　使用Excel函数拆分列...............................................................................130

6.3.1　使用文本函数进行分列的基本方法........................................................130

6.3.2　拆分全角字符和半角字符.........................................................................131

6.4　使用Power Query工具拆分列.................................................................133

6.4.1　将一列拆分成多列保存（拆分列工具）.................................................133

6.4.2　将一列拆分成多列保存（自定义列工具）.............................................137

6.4.3　将一列拆分成多行保存.............................................................................139

第7章.分类汇总与分级显示实用技能与技巧...............143

7.1　创建分类汇总.............................................................................................144

7.1.1　创建单级分类汇总......................................................................................144

7.1.2　创建多级分类汇总......................................................................................146

7.1.3　删除分类汇总...............................................................................................147

7.2　创建分级显示.............................................................................................148

7.2.1　自动建立分级显示......................................................................................148

7.2.2　手动建立分级显示......................................................................................149

7.3　分类汇总和分级显示的其他操作...............................................................151

7.3.1　复制分类汇总和分级显示数据................................................................151

7.3.2　取消分类汇总和分级显示.........................................................................152

第8章.合并计算工具实用技能与技巧............................153

8.1　合并计算工具的基本使用方法...................................................................154

8.1.1　仅获取合计数的合并计算.........................................................................154

8.1.2　与每个表格链接的合并计算.....................................................................155

8.2　复杂情况下的合并计算..............................................................................159

8.2.1　复杂结构表格合并......................................................................................159

8.2.2　跨工作簿的合并计算..................................................................................160

第9章.表格数据整理加工技能与技巧............................163

9.1　数据填充....................................................................................................164

9.1.1　将空单元格填充零......................................................................................164

9.1.2　将空单元格填充为上一行数据................................................................166

9.1.3　将空单元格填充为下一行数据................................................................167

9.1.4　将空单元格填充为左一列数据................................................................168

9.1.5　将空单元格填充为右一列数据................................................................169

9.1.6　将某行数据往下复制填充N行...............................................................170

9.1.7　将某列数据往右复制填充N列...............................................................170

9.2　数据去重....................................................................................................170

9.2.1　从某列中获取不重复数据列表................................................................171

9.2.2　删除表单中的重复数据.............................................................................171

9.3　数字格式转换.............................................................................................172

9.3.1　将文本型数字转换为数值型数字............................................................172

9.3.2　将数值型数字转换为文本型数字............................................................173

9.4　特殊字符处理.............................................................................................175

9.4.1　清除字符前后的空格..................................................................................175

9.4.2　清除字符中的星号......................................................................................175

9.4.3　清除字符中的特殊字符.............................................................................176

第10章.数据验证实用技能与技巧...................................178

10.1　数据验证的设置方法与注意事项.............................................................179

10.1.1　数据验证的设置方法...............................................................................179

10.1.2　复制粘贴会破坏设置的数据验证..........................................................181

10.1.3　清除数据验证............................................................................................181

10.2　设置数据验证来控制输入日期.................................................................182

10.2.1　只允许输入指定时间段的日期..............................................................182

10.2.2　不允许输入指定时间段的日期..............................................................182

10.2.3　只允许输入指定日期之前或之后的日期.............................................183

10.2.4　只允许输入当天日期...............................................................................184

10.3　设置数据验证来控制输入数字.................................................................184

10.3.1　只允许输入整数........................................................................................185

10.3.2　只允许输入小数........................................................................................185

10.4　设置数据验证来控制输入文本.................................................................186

10.4.1　只能输入固定长度的文本.......................................................................186

10.4.2　只能输入中文名称，并且不允许有空格.............................................187

10.4.3　只能输入英文名称....................................................................................188

10.5　使用序列规则在单元格设置下拉列表......................................................188

10.5.1　序列项目固定情况下的下拉列表..........................................................189

10.5.2　序列项目不固定情况下的下拉列表.....................................................190

10.6　设置自定义规则的数据验证....................................................................190

10.6.1　只能输入18位不重复身份证号码.......................................................191

10.6.2　只能输入位数不定的文本型数字编码.................................................192

第11章.Excel常用函数公式与实际应用........................194

11.1　公式基本知识和操作技能与技巧.............................................................195

11.1.1　公式运算规则及运算符...........................................................................195

11.1.2　单元格引用.................................................................................................195

11.1.3　在公式中输入固定值...............................................................................196

11.1.4　复制公式的基本方法...............................................................................197

11.1.5　复制公式的快捷方法...............................................................................197

11.1.6　移动公式的基本方法...............................................................................198

11.1.7　移动公式的快捷方法...............................................................................198

11.1.8　复制公式本身字符串或公式的一部分字符串....................................198

11.1.9　用F9键查看公式的计算结果................................................................198

11.1.10　将公式转换为值......................................................................................199

11.1.11　显示公式计算结果和显示公式表达式...............................................199

11.1.12　将公式分行输入，以便使公式更加容易理解和查看.....................199

11.1.13　在公式表达式中插入空格，编辑起来更加方便.............................200

11.1.14　保护公式不被修改或查看....................................................................200

11.1.15　公式和函数中的字母不区分大小写...................................................202

11.1.16　公式和函数中的标点符号都必须是半角字符..................................202

11.2　函数基本知识和操作技能与技巧.............................................................203

11.2.1　函数的基本语法........................................................................................203

11.2.2　函数参数的类型........................................................................................204

11.2.3　养成输入函数的好习惯...........................................................................205

11.2.4　在单元格快速输入函数的技巧..............................................................205

11.3　常用逻辑判断函数及其应用....................................................................206

11.3.1　IF函数基本应用........................................................................................206

11.3.2　IF函数嵌套应用........................................................................................207

11.3.3　IFS函数基本应用.....................................................................................210

11.3.4　IFERROR函数基本应用...........................................................................210

11.4　常用分类汇总函数及其应用....................................................................211

11.4.1　COUNTA函数统计不为空的单元格个数............................................211

11.4.2　COUNTIF函数统计满足一个条件的单元格个数..............................212

11.4.3　COUNTIFS函数统计满足多个条件的单元格个数............................213

11.4.4　SUMIF函数对满足一个条件的单元格求和........................................213

11.4.5　SUMIFS函数对满足多个条件的单元格求和......................................215

11.5　常用查找引用函数及其应用....................................................................216

11.5.1　VLOOKUP函数从列结构表中查找数据..............................................216

11.5.2　HLOOKUP函数从行结构表中查找数据..............................................220

11.5.3　XLOOKUP函数灵活查找数据................................................................221

11.5.4　MATCH函数从一列或一行中定位........................................................223

11.5.5　INDEX函数根据行坐标和列坐标查找数据.........................................224

11.6　常用日期处理函数及其应用....................................................................226

11.6.1　日期数据特征与注意事项.......................................................................226

11.6.2　TODAY函数获取当天日期......................................................................228

11.6.3　EDATE函数计算到期日..........................................................................229

11.6.4　EOMONTH函数计算月底日期..............................................................230

11.6.5　DATEDIF函数计算两个日期的期限.....................................................231

11.6.6　WEEKDAY函数判断星期几....................................................................232

11.6.7　DATE函数将年月日三个数字组合为日期..........................................233

11.7　常用文本处理函数及其应用....................................................................235

11.7.1　LEN函数获取文本长度...........................................................................235

11.7.2　LEFT函数从文本字符串左侧截取字符...............................................235

11.7.3　RIGHT函数从文本字符串右侧截取字符.............................................236

11.7.4　MID函数从文本字符串指定位置截取字符.........................................237

11.7.5　SUBSTITUTE函数替换指定字符..........................................................237

11.7.6　CONCAT函数连接字符串.......................................................................238

11.7.7　TEXTJOIN函数连接字符串.....................................................................240

11.7.8　TEXT函数将数值转换为指定格式的文本字符串..............................240

第12章.Excel数据透视分析技能与技巧........................243

12.1　制作数据透视表的基本方法....................................................................244

12.1.1　以一个一维表格制作数据透视表..........................................................244

12.1.2　以多个一维表格制作数据透视表..........................................................246

12.1.3　以一个二维表格制作数据透视表..........................................................250

12.1.4　以多个二维表格制作数据透视表..........................................................252

12.2　数据透视表的格式化和美化....................................................................256

12.2.1　设置报表布局............................................................................................257

12.2.2　重复项目标签（填充项目空单元格）...................................................258

12.2.3　显示/不显示分类汇总...........................................................................259

12.2.4　显示/不显示行总计和列总计..............................................................259

12.2.5　合并居中标签单元格...............................................................................260

12.2.6　设置报表样式............................................................................................261

12.2.7　修改字段名称............................................................................................261

12.2.8　调整字段项目的次序...............................................................................262

12.2.9　设置数字格式............................................................................................262

12.3　利用数据透视表分析数据........................................................................263

12.3.1　排名分析.....................................................................................................263

12.3.2　筛选排名前（后）N的商品或客户......................................................264

12.3.3　设置值字段汇总依据...............................................................................266

12.3.4　设置值字段显示方式...............................................................................268

12.3.5　对字段的项目进行分组：组合日期......................................................269

12.3.6　对字段的项目进行分组：组合数字......................................................270

12.3.7　对字段的项目进行分组：组合文本......................................................271

12.3.8　插入计算字段，生成新的分析指标.....................................................273

12.3.9　插入计算项，生成新的分析项目..........................................................275

12.3.10　插入切片器，快速灵活筛选数据.......................................................277

12.3.11　快速制作某个项目的明细表................................................................280

12.3.12　快速制作所有项目的明细表................................................................280

12.4　使用数据透视图可视化数据透视表.........................................................282

12.4.1　创建数据透视图的基本方法..................................................................282

12.4.2　不显示数据透视图上的字段按钮..........................................................283

12.4.3　数据透视图的基本格式化.......................................................................284