Excel高效办公:数据处理与分析

目 录

第 1 章 数据的输入

1.1 Excel可以用来做什么 2

1.1.1 Excel到底能做什么 2

1. 制作表单 2

2. 完成复杂的运算 2

3. 建立图表 2

4. 数据管理 2

5. 决策指示 3

1.1.2 3种不同用途的表——数据表、统计报表、表单 3

1.2 基本数据的输入 4

1.2.1 文本型数据的输入 4

1.2.2 数值型数据的输入 5

1.2.3 货币型数据的输入 6

1.2.4 会计专用型数据的输入 7

1.2.5 日期型数据的输入 7

1.2.6 使用自定义快速输入编号 9

1.2.7 自定义数据的格式 9

1. 文本和空格 10

2. 小数点、空格、颜色和条件 11

3. 货币、百分比和科学计数 12

4. 百分比 13

5. 日期和时间 13

1.2.8 使用自定义格式隐藏零值 15

1.3 有规律数据的填充 16

1.3.1 填充数字序列 16

1.3.2 填充文本序列 18

1.4 相同数据的填充 18

1.5 利用数据验证，高效输入数据 19

1.5.1 通过下拉列表填充 20

1.5.2 限定文本长度 21



第 2 章 数据的格式化

2.1 不规范表格及数据的整理技巧 24

2.1.1 表格结构的规范与整理 24

2.1.2 快速删除空白行、空白列 26

1. 手动删除 26

2. 通过筛选删除 26

3. 使用函数删除 27

4. 使用定位功能删除 29

2.1.3 快速删除重复值、重复记录 30

1. 删除重复项 31

2. 高级筛选法 31

2.1.4 不规范日期的整理技巧 32

1. 查找替换 32

2. 分列功能 33

2.1.5 取消合并单元格的整理技巧 34

2.2 套用Excel表格格式 36

2.2.1 套用系统自带格式 36

2.2.2 自定义样式 37

2.3 套用单元格样式 39

2.3.1 套用系统自带样式 39

2.3.2 自定义样式 40

2.4 条件格式的设置 43

2.4.1 突出显示重点单元格 44

2.4.2 辅助识别数据大小 44

2.4.3 辅助用户查看数据走向 45



第 3 章 排序、筛选与分类汇总

3.1 排序 50

3.1.1 单个关键字的排序 50

3.1.2 多个关键字的排序 51

3.1.3 自定义排序 52

3.2 筛选 53

3.2.1 自动筛选 53

1. 单一条件的筛选 53

2. 快速筛选前N项 54

3. 按字体颜色、单元格颜色筛选 55

4. 多条件的筛选 56

3.2.2 高级筛选 57

1. 同时满足多个条件的筛选 57

2. 满足其中一个条件的筛选 58

3.3 分类汇总 59

3.3.1 简单分类汇总 59

3.3.2 高级分类汇总 61

3.3.3 嵌套分类汇总 61

1. 删除分类汇总 62

2. 分类汇总 62



第 4 章 公式与函数的应用

4.1 认识公式与函数 67

4.1.1 初识公式 67

1. 单元格引用 68

2. 运算符 69

4.1.2 初识函数 69

1. 函数的基本构成 70

2. 函数的种类 70

4.2 文本函数 70

4.2.1 LEFT函数——从左侧截取字符 70

4.2.2 RIGHT函数——从右侧截取字符 72

4.2.3 MID函数——截取中间字符 73

4.2.4 FIND函数——查找字符位置 74

4.2.5 LEN函数——计算字符串长度 76

4.2.6 TEXT函数——字符串“整容”器 78

4.3 数学与三角函数 80

4.3.1 SUM函数——轻松求和 80

4.3.2 SUMIF函数——单一条件求和 81

4.3.3 SUMIFS函数——多条件求和 82

4.3.4 SUMPRODUCT——乘积求和 83

1. 一个参数 83

2. 两个参数 84

3. 多个参数 84

4. 按条件求和 85

4.3.5 SUBTOTAL函数——轻松分类汇总 86

4.3.6 MOD函数——求余计算器 90

4.3.7 INT函数——整数切割器 92

4.4 逻辑函数 94

4.4.1 IF函数——专精条件选择 94

1. Excel 中的逻辑关系 94

2. 用于条件判断的IF函数 94

4.4.2 OR函数——只要有一个值为真，结果就为真 95

4.4.3 AND函数——只要有一个值为假，结果就为假 97

4.4.4 IFS函数（2019）——多条件判断很简单 100

4.5 统计函数 103

4.5.1 COUNTA函数——非空格计算器 103

4.5.2 COUNT函数——数字计算器 104

4.5.3 MAX函数——极大值搜索器 105

4.5.4 MIN函数——极小值搜索器 106

4.5.5 AVERAGE函数——算数平均值计算器 107

4.5.6 COUNTIF函数——单条件自定义计算器 108

4.5.7 COUNTIFS函数——多条件自定义计算器 109

4.5.8 RANK.EQ函数——排序好帮手 111

4.6 查找与引用函数 112

4.6.1 VLOOKUP函数——纵向查找利器 112

4.6.2 HLOOKUP函数——横向查找利器 115

4.6.3 MATCH函数——确定数据位置 117

4.6.4 LOOKUP函数——查找神器 119

1. LOOKUP函数进行纵向查找 120

2. LOOKUP函数进行横向查找 122

3. LOOKUP函数进行条件判断 122

4. LOOKUP函数进行逆向查询 124

4.7 日期和时间函数 127

4.7.1 YEAR、MONTH、DAY函数——年、月、日函数 127

4.7.2 DATE函数——生成日期 129

4.7.3 DATEDIF函数——计算两日期之差 132

4.7.4 EDATE函数——计算相差几个月的日期 133

4.7.5 EMONTH函数——计算相差几个月的月末日期 134

4.7.6 TODAY函数——计算当前日期 134

4.8 财务函数 136

4.8.1 SLN函数——平均折旧 136

4.8.2 DDB函数——指定期间折旧 137

4.8.3 SYD函数——年数总计折旧 139

4.8.4 PMT函数——等额还款 140

4.8.5 PPMT函数——等额还款本金 141

4.8.6 IPMT函数——等额还款利息 142

4.9 定义名称 144

4.9.1 认识Excel中的名称 144

4.9.2 定义名称 145

1. 为数据区域定义名称 145

2. 为数据常量定义名称 146

3. 为公式定义名称 147

4.9.3 编辑和删除名称 148

1. 编辑名称 149

2. 删除名称 149

4.9.4 在公式中使用名称 150

4.10 数组与数组公式 151

4.10.1 什么是数组 151

4.10.2 什么是数组公式 152

4.10.3 数组公式的应用 152

4.11 函数输入、编辑中的技巧 153

4.11.1 根据需求搜索函数 153

4.11.2 函数参数不用背的秘密 154

4.11.3 函数较长记不住怎么办 154

4.11.4 事半功倍——快速输入函数 155

4.11.5 通过单击选择的方式快速引用其他工作表数据 155



第 5 章 可视化分析工具——图表

5.1 图表的类型及应用原则 158

5.1.1 柱形图及其适用场合 158

5.1.2 折线图及其适用场合 159

5.1.3 饼图及其适用场合 159

5.1.4 条形图及其适用场合 160

5.1.5 面积图及其适用场合 160

5.1.6 XY散点图及其适用场合 161

5.1.7 股价图及其适用场合 161

5.1.8 雷达图及其适用场合 162

5.1.9 树状图及其适用场合 162

5.1.10 旭日图及其适用场合 162

5.1.11 直方图及其适用场合 163

5.1.12 瀑布图及其适用场合 163

5.1.13 漏斗图及其适用场合 164

5.2 图表的创建 164

5.2.1 利用固定数据区域创建图表 164

1. 连续区域的所有数据 165

2. 选定的部分数据 165

5.2.2 利用固定常量创建图表 166

5.2.3 利用定义名称创建图表 168

5.3 图表的编辑 170

5.3.1 更改图表类型 171

1. 更改整个图表的图表类型 171

2. 更改某个数据系列的图表类型 172

5.3.2 编辑数据系列 173

1. 减少数据系列 173

2. 增加数据系列 173

5.3.3 编辑图表标题 175

5.3.4 编辑图例 176

5.3.5 添加坐标轴标题 177

5.3.6 添加数据标签 178

5.3.7 添加趋势线 179

5.4 格式化图表 180

5.4.1 保证图表的完整性 180

5.4.2 格式化图表区 181

5.4.3 格式化绘图区 183

5.4.4 格式化图表标题、坐标轴标题、图例 183

5.4.5 格式化坐标轴 186

5.4.6 格式化数据系列 187

5.4.7 格式化数据标签 189

5.5 制作高级图表 190

5.5.1 用箭头替代数据条 190

5.5.2 制作金字塔分布图 191

5.5.3 将精美小图应用于图表 195

5.5.4 双轴复合图表 198

5.6 制作动态图表 200

5.6.1 利用查找函数创建动态图表 200

5.6.2 利用INDIRECT函数创建动态图表 203

1. INDIRECT函数基本解析 203

2. ADDRESS函数基本解析 204

3. ROW函数基本解析 204

4. CELL函数基本解析 204

5. 创建动态图表 207



第 6 章 数据透视表与数据透视图

6.1 创建数据透视表 210

6.1.1 认识数据和数据透视表 210

6.1.2 字段与值 210

6.1.3 创建初始数据透视表 211

6.2 布局数据透视表 213

6.2.1 数据透视表字段窗格 213

6.2.2 数据透视表的布局 214

1. 数据结构的布局 214

2. 显示方式的布局 214

6.3 格式化数据透视表 215

6.4 数据透视表的排序与筛选 216

6.4.1 数据透视表的排序 216

6.4.2 数据透视表的筛选 217

6.5 生成数据透视图 219

6.6 数据透视表（图）的高级应用 220

6.6.1 使用切片器筛选 220

1. 插入并使用切片器 220

2. 设置切片器样式 222

3. 一个切片器控制多个数据透视表（图） 224

4. 删除切片器 225

6.6.2 使用日程表筛选 225

6.7 数据透视表的计算 227

6.7.1 添加计算字段 228

6.7.2 添加计算项 229



第 7 章 数据分析工具的使用

7.1 安装分析工具库 233

7.2 模拟运算表 233

7.2.1 单变量模拟运算表 234

7.2.2 双变量模拟运算表 235

7.3 单变量求解 236

7.4 规划求解 237

7.4.1 线性规划概述 237

7.4.2 加载规划求解 238

7.4.3 实例：建立规划求解模型——人员*优配置方案 238

7.5 分析工具库 242

7.5.1 方差分析 242

7.5.2 相关系数 245

7.5.3 协方差 247

7.5.4 指数平滑 248

7.5.5 移动平均 251



第 8 章 生产决策数据的处理

8.1 生产成本分析 254

8.1.1 知识点 254

1. 公式的应用 254

2. 盈亏平衡点 254

3. IF函数的应用 254

4. 单变量求解的应用 254

8.1.2 成本分析的流程 254

8.1.3 实例：设备生产能力优化决策分析 255

8.1.4 实例：量本利分析 257

1. 降低单位变动成本 257

2. 降低固定成本 258

8.2 产品*优组合决策分析 259

8.2.1 知识点：规划求解 259

8.2.2 决策分析的步骤 259

8.2.3 实例：利润*大化 259

8.2.4 实例：成本*小化 263



第 9 章 销售数据的处理

9.1 公司销售发展趋势分析 269

9.1.1 知识点 269

1. 趋势分析 269

2. 趋势分析的影响因素 269

3. 同比和环比 270

4. 发展速度 270

5. 平均发展速度 270

6. POWER函数 271

7. 增长速度 271

9.1.2 实例：计算销售额的同比增速 271

9.1.3 实例：计算销售额的环比发展速度 272

9.1.4 实例：计算销售额的发展速度 274

9.1.5 实例：计算销售额的平均发展速度 276

9.1.6 实例：计算销售额的增长速度 276

9.1.7 实例：制作分析图表 277

1. 同比分析图表 277

2. 环比分析图表 280

3. 销售走势图表 282

9.2 公司销售相关分析 286

9.2.1 知识点 286

1. 什么是相关分析 286

2. 相关分析的分类 287

3. 相关表和相关图 288

4. 相关系数 290

5. CORREL函数 290

9.2.2 实例：计算直通车与销售额的相关程度 291

1. CORREL函数法 291

2. 数据分析工具库 292

9.2.3 实例 直通车与销售额相关性分析——散点图 293

9.3 公司结构分析 296

9.3.1 知识点 296

1. 什么是结构分析 296

2. 结构相对指标 297

3. 饼图 297

9.3.2 实例：公司份额结构分析 297

9.3.3 实例：分析各类商品的贡献率 301

9.4 门店的客流量分析 303

9.4.1 知识点 303

1. 描述分析 303

2. 删除重复值 303

3. 数据透视表 303

4. 折线图 303

9.4.2 实例：确定客流高峰期的时间段 303

1. 删除重复订单编号 304

2. 汇总各时间段的订单量 305

3. 折线图——清晰展示客流量 307



第 10 章 人力资源数据的处理

10.1 在职人员结构分析 310

10.1.1 知识点 310

1. 自动筛选 310

2. COUNTIFS函数 310

3. 数据透视表 310

10.1.2 实例：统计在职人员结构信息 310

1. 筛选法 311

2. 函数法 315

3. 数据透视表法 320

10.1.3 实例：分析在职人员结构 326

1. 简单图表 326

2. 多级联动图表 328

10.2 分析人员流动情况 331

10.2.1 知识点 331

1. SUMPRODUCT函数 331

2. YEAR函数 332

3. MONTH函数 332

10.2.2 实例：统计分析各部门的人员流动情况 332

1. 计算入职人数 332

2. 计算离职人数 333

3. 分析人员流动 335



第 11 章 财务数据的处理

11.1 财务比率分析 339

11.1.1 知识点 339

1. 变现能力比率 339

2. 资产管理比率 339

3. 负债比率 341

4. 盈利能力比率 342

11.1.2 实例：计算变现能力比率 343

11.1.3 实例：计算资产管理比率指标 343

11.1.4 实例 计算负债比率指标 346

11.1.5 实例：计算盈利能力比率指标 348

11.2 财务比较分析 349

11.3 财务趋势分析 354

11.4 杜邦分析 355

11.4.1 知识点 355

1. 资产净利率 355

2. 权益乘数 356

3. 权益净利率 356

11.4.2 实例：计算各财务比率 356