跳到主要内容

Excel数据分析工具

本章概览​

Excel提供了强大的数据分析工具，能够帮助我们快速处理和分析大量数据。本章将介绍Excel内置的数据分析功能，包括排序筛选、分类汇总、合并计算、模拟运算表等核心工具。

学习目标

  • 掌握多条件排序和高级筛选技巧
  • 学会使用分类汇总和合并计算
  • 熟练运用模拟运算表进行敏感性分析
  • 理解数据验证和条件格式的应用
  • 掌握数据分列和快速填充技巧

18.1 数据排序​

18.1.1 简单排序​

单列排序

  1. 选中数据列中的任意单元格
  2. 开始 → 排序和筛选 → 升序/降序
  3. 或右键 → 排序 → 升序/降序

快捷操作

  • 数据 → AZ↓（升序）
  • 数据 → ZA↑（降序）

注意事项

  • Excel会自动扩展选择区域包含相邻数据
  • 确保数据无空行空列
  • 首行通常作为标题行

18.1.2 多条件排序​

操作步骤

  1. 选中数据区域（含标题）
  2. 数据 → 排序
  3. 添加排序条件
    • 主要关键字：部门（升序）
    • 次要关键字：工资（降序）
  4. 点击确定

示例场景

排序需求：先按部门分组，再按工资从高到低排列

原始数据：
姓名   部门   工资
张三   销售   8000
李四   技术   12000
王五   销售   10000
赵六   技术   9000

排序后：
姓名   部门   工资
李四   技术   12000
赵六   技术   9000
王五   销售   10000
张三   销售   8000

高级选项

  • 区分大小写：勾选后"A"和"a"视为不同字符
  • 方向：按行排序（横向排序）
  • 数据包含标题：首行不参与排序

18.1.3 自定义排序​

自定义序列

  1. 文件 → 选项 → 高级 → 编辑自定义列表
  2. 输入自定义序列，如： 
    总经理,副总经理,部门经理,主管,员工
    一月,二月,三月,...,十二月
  3. 确定保存

使用自定义序列排序

  1. 数据 → 排序
  2. 次序选择"自定义序列"
  3. 选择对应的序列
  4. 确定

应用场景

  • 职位等级排序
  • 月份按自然顺序排序
  • 地区按指定顺序排序

18.1.4 按颜色排序​

前提条件 单元格或字体设置了颜色

操作步骤

  1. 数据 → 排序
  2. 排序依据选择"单元格颜色"或"字体颜色"
  3. 选择要排在前面的颜色
  4. 添加多个颜色排序规则

应用场景

  • 按任务优先级（红、黄、绿）排序
  • 按审核状态（颜色标记）排序

18.2 数据筛选​

18.2.1 自动筛选​

启用筛选

  1. 选中数据区域任意单元格
  2. 数据 → 筛选
  3. 标题行出现下拉箭头

筛选操作

  • 文本筛选：包含、等于、开头是、结尾是
  • 数字筛选：大于、小于、介于、前10项
  • 日期筛选：今天、本周、本月、去年
  • 颜色筛选：按单元格颜色、字体颜色筛选

多条件筛选

  • 同时对多列设置筛选条件
  • 条件间是"与"关系（同时满足）

示例

筛选条件：
- 部门 = "销售"
- 工资 > 8000
- 入职日期 >= 2023-01-01

显示结果：销售部门，工资超过8000，2023年后入职的员工

18.2.2 高级筛选​

适用场景

  • 需要"或"条件筛选
  • 复杂多条件组合
  • 将筛选结果复制到其他位置

操作步骤

  1. 准备条件区域

    姓名   部门   工资

           销售   >8000
           技术   >10000

    （空行表示"或"关系，同行表示"且"关系）

  2. 数据 → 高级筛选

  3. 设置：

    • 列表区域：原始数据区域
    • 条件区域：上面的条件表
    • 复制到：目标位置（可选）

  4. 确定

条件编写规则

# 且条件（同行）
部门     工资
销售    >8000        # 销售部门且工资>8000

# 或条件（不同行）
部门     工资
销售
技术               # 销售部门或技术部门

# 复杂组合
部门     工资
销售    >8000       # 销售部且工资>8000
技术    >10000      # 或技术部且工资>10000

通配符

  • *：任意多个字符
  • ?：单个字符
  • ~：转义符
姓名
张*          # 姓张的所有人
???         # 三个字的姓名
~*公司      # 包含*号的文本

18.2.3 切片器​

适用对象 表格、数据透视表

创建切片器

  1. 选中表格中任意单元格
  2. 插入 → 切片器
  3. 选择要筛选的字段
  4. 确定

优势

  • 可视化筛选界面
  • 支持多选
  • 可以同时控制多个表格
  • 美观直观

操作

  • 单击：单选
  • Ctrl+单击：多选
  • 清除筛选器：右上角漏斗图标
  • 多列显示：切片器工具 → 列数

应用场景

  • 销售仪表盘：按地区、产品、时间筛选
  • 数据报告：动态切换查看维度

18.3 分类汇总​

18.3.1 自动分类汇总​

前提条件 数据必须先按分类字段排序

操作步骤

  1. 按部门列排序
  2. 数据 → 分类汇总
  3. 设置：
    • 分类字段：部门
    • 汇总方式：求和
    • 汇总项：工资
    • 勾选"每组数据分页"（可选）
  4. 确定

效果

姓名   部门   工资
张三   技术   8000
李四   技术   12000
技术汇总      20000   ← 自动插入汇总行

王五   销售   10000
赵六   销售   9000
销售汇总      19000

总计          39000

汇总方式

  • 求和、计数、平均值
  • 最大值、最小值
  • 乘积、标准偏差、方差

18.3.2 多级分类汇总​

场景：先按部门汇总，再按职位汇总

操作步骤

  1. 先按部门排序，再按职位排序
  2. 第一次汇总：
    • 分类字段：部门
    • 取消勾选"替换当前分类汇总"
  3. 第二次汇总：
    • 分类字段：职位
    • 取消勾选"替换当前分类汇总"
  4. 确定

结构大纲

  • 左侧出现分组符号：1、2、3
  • 1：显示总计
  • 2：显示各部门小计
  • 3：显示明细数据

18.3.3 移除分类汇总​

方法

  1. 数据 → 分类汇总
  2. 点击"全部删除"
  3. 确定

18.4 合并计算​

18.4.1 基本合并计算​

应用场景 将多个工作表的相同结构数据汇总

示例：合并三个月的销售数据

一月表：
产品   销售额
A      1000
B      2000

二月表：
产品   销售额
A      1500
B      2500

三月表：
产品   销售额
A      1200
B      2200

操作步骤

  1. 在新工作表中定位汇总位置
  2. 数据 → 合并计算
  3. 设置：
    • 函数：求和
    • 引用位置：选择一月!A1:B3，点击"添加"
    • 继续添加二月、三月的数据区域
    • 勾选"首行"、"最左列"（使用标签）
  4. 确定

结果

产品   销售额
A      3700   (1000+1500+1200)
B      6700   (2000+2500+2200)

18.4.2 创建数据链接​

勾选"创建指向源数据的链接"

  • 结果会实时更新
  • 创建分组大纲
  • 可以追溯源数据

应用

  • 季度汇总链接到各月数据
  • 年度汇总链接到各季度数据

18.4.3 不同结构数据合并​

场景：两个表格产品数量不同

表1：
产品   销售额
A      1000
B      2000
C      3000

表2：
产品   销售额
A      1500
D      2500

合并结果

产品   销售额
A      2500   (1000+1500)
B      2000
C      3000
D      2500

Excel自动识别标签并智能匹配。

18.5 模拟运算表​

18.5.1 单变量模拟运算表​

场景：贷款本金固定，查看不同利率下的月供

准备公式

B1: 本金 = 1000000
B2: 利率 = 5%
B3: 期限 = 30年
B4: 月供 = PMT(B2/12,B3*12,-B1)

创建模拟运算表

  1. 布局：

         A          B
    6            =B4
    7   4.0%
    8   4.5%
    9   5.0%
    10  5.5%

  2. 选中A6:B10

  3. 数据 → 模拟分析 → 模拟运算表

  4. 输入引用列的单元格：B2（利率）

  5. 确定

结果

        A          B
   6            月供
   7   4.0%    4774.15
   8   4.5%    5066.64
   9   5.0%    5368.22
   10  5.5%    5677.88

18.5.2 双变量模拟运算表​

场景：同时改变利率和期限，查看月供变化

布局

       B        C        D        E
6    =B4      20年     25年     30年
7    4.0%
8    4.5%
9    5.0%
10   5.5%

操作

  1. 选中B6:E10
  2. 数据 → 模拟运算表
  3. 输入引用行的单元格：B3（期限）
  4. 输入引用列的单元格：B2（利率）
  5. 确定

结果

       B        C          D          E
6              20年       25年       30年
7    4.0%    6059.68    5278.35    4774.15
8    4.5%    6326.67    5558.55    5066.64
9    5.0%    6599.56    5845.89    5368.22
10   5.5%    6878.24    7139.29    5677.88

应用场景

  • 投资回报分析
  • 定价敏感性分析
  • 财务预测

18.6 数据验证​

18.6.1 基本数据验证​

设置数值范围

  1. 选中单元格区域
  2. 数据 → 数据验证
  3. 设置：
    • 验证条件：整数
    • 数据：介于
    • 最小值：0
    • 最大值：100
  4. 确定

其他验证类型

  • 整数/小数：限制数值范围
  • 日期/时间：限制日期范围
  • 文本长度：限制字符数
  • 序列：创建下拉列表
  • 自定义：使用公式验证

18.6.2 下拉列表​

方法一：直接输入

  1. 数据验证 → 序列
  2. 来源输入：技术,销售,财务,人事
  3. 确定

方法二：引用单元格

  1. 在工作表中准备部门列表（如G1:G4）
  2. 数据验证 → 序列
  3. 来源：=$G$1:$G$4
  4. 确定

方法三：使用名称

  1. 定义名称"部门列表" = G1:G4
  2. 数据验证 → 序列
  3. 来源：=部门列表
  4. 确定

优势

  • 防止输入错误
  • 统一数据格式
  • 提高录入效率

18.6.3 自定义验证公式​

场景1：禁止输入重复值

自定义公式：=COUNTIF($A$1:$A$100,A1)<=1

场景2：结束日期必须晚于开始日期

# 在结束日期列设置验证
自定义公式：=B2>A2

场景3：只允许输入工作日

自定义公式：=WEEKDAY(A1,2)<=5

场景4：身份证号码验证

自定义公式：=AND(LEN(A1)=18,ISNUMBER(--MID(A1,1,17)))

18.6.4 输入提示和错误警告​

输入提示

  1. 数据验证 → 输入信息选项卡
  2. 标题：请选择部门
  3. 输入信息：请从下拉列表中选择您所在的部门
  4. 选中单元格时显示提示

错误警告

  1. 数据验证 → 出错警告选项卡
  2. 样式：
    • 停止：禁止输入无效数据
    • 警告：警告但允许继续
    • 信息：仅提示信息
  3. 标题和错误信息自定义

18.7 条件格式​

18.7.1 突出显示单元格规则​

大于/小于

  1. 选中数据区域
  2. 开始 → 条件格式 → 突出显示单元格规则 → 大于
  3. 输入值：60
  4. 选择格式：浅红填充色深红色文本
  5. 确定

其他规则

  • 介于：值在某范围内
  • 等于：值等于指定值
  • 文本包含：包含特定文本
  • 发生日期：昨天、今天、明天、上周
  • 重复值：高亮显示重复或唯一值

18.7.2 项目选取规则​

前10项

  1. 条件格式 → 项目选取规则 → 前10项
  2. 设置数量或百分比
  3. 选择格式

其他选取

  • 前10%项
  • 后10项
  • 后10%项
  • 高于平均值
  • 低于平均值

18.7.3 数据条​

应用

  1. 选中数据区域
  2. 条件格式 → 数据条 → 选择样式

效果 单元格内显示彩色横条，长度与数值成正比

自定义

  • 管理规则 → 编辑规则
  • 设置最小值、最大值
  • 选择条形颜色
  • 仅显示数据条（隐藏数值）

18.7.4 色阶​

三色色阶

  • 最小值：红色
  • 中间值：黄色
  • 最大值：绿色

应用 热力图、成绩分布、销售业绩可视化

18.7.5 图标集​

类型

  • 箭头（3-5种）
  • 形状（3-5种）
  • 指示器（3-4种）
  • 等级（3-5种）

应用场景

销售完成率：
>100%  ↑ 绿色箭头
80-100% → 黄色箭头
<80%   ↓ 红色箭头

自定义图标集

  1. 管理规则 → 编辑规则
  2. 设置每个图标的值范围和类型
  3. 可以使用不同图标集混搭

18.7.6 自定义公式​

场景1：隔行填色

选中数据区域
公式：=MOD(ROW(),2)=0
格式：浅蓝色填充

场景2：高亮整行

# 当工资>10000时，整行变红
选中A2:E100
公式：=$D2>10000
格式：浅红色填充

场景3：截止日期提醒

# 距离截止日期<7天，标黄色；已过期，标红色
公式1：=AND($C2-TODAY()<=7,$C2-TODAY()>0)  # 黄色
公式2：=$C2<TODAY()                        # 红色

场景4：周末高亮

公式：=WEEKDAY(A2,2)>5
格式：灰色填充

18.8 数据分列​

18.8.1 分隔符分列​

场景：将"张三,男,28"拆分为三列

操作步骤

  1. 选中包含数据的列
  2. 数据 → 分列
  3. 选择"分隔符号"
  4. 下一步
  5. 勾选"逗号"（或其他分隔符）
  6. 预览确认
  7. 下一步
  8. 选择目标位置
  9. 完成

常见分隔符

  • Tab制表符
  • 分号、逗号
  • 空格
  • 自定义（如|、-）

18.8.2 固定宽度分列​

场景：身份证号拆分为出生年月日和性别

操作步骤

  1. 数据 → 分列
  2. 选择"固定宽度"
  3. 在预览区域点击设置分割线
  4. 双击删除不需要的分割线
  5. 设置列数据格式
  6. 完成

示例

身份证：110101199001011234
拆分：
第1-6位：110101（地区码）
第7-14位：19900101（出生日期）
第15-17位：123（顺序码）
第18位：4（校验码）

18.8.3 分列高级技巧​

提取日期

  • 列数据格式选择"日期"
  • 选择日期格式：YMD、MDY、DMY

跳过某些列

  • 列数据格式选择"不导入此列（跳过）"

保留原始数据

  • 目标位置选择新的列

18.9 快速填充​

18.9.1 快速填充基础​

功能 Excel自动识别输入模式，智能填充数据

操作方法

  1. 在第一行输入示例结果
  2. 在第二行开始输入
  3. Excel弹出预览建议
  4. 按Enter接受，或Ctrl+E强制触发

快捷键 Ctrl + E

18.9.2 应用场景​

场景1：提取姓氏

A列        B列（手动输入一个示例后按Ctrl+E）
张三       张
李四       李
王五       王

场景2：合并姓名

A列    B列    C列（快速填充）
张     三     张三
李     四     李四

场景3：提取手机号中段

A列              B列
13812345678      123
13987654321      876

场景4：格式转换

A列              B列
2023-01-01      2023年1月1日
2023-02-15      2023年2月15日

场景5：提取邮箱用户名

A列                  B列
zhang@company.com   zhang
li@company.com      li

18.9.3 快速填充限制​

无法处理的情况

  • 规律过于复杂
  • 示例数量不足（至少2个）
  • 数据格式不一致

解决方法

  • 提供更多示例
  • 简化提取规则
  • 使用公式替代（如MID、LEFT、RIGHT函数）

18.10 实战案例​

案例1：销售数据多维度分析​

数据

日期        地区   产品   销售额
2024-01-01  北京   A      1000
2024-01-01  北京   B      2000
2024-01-02  上海   A      1500
...

分析需求

  1. 按地区汇总销售额
  2. 按产品汇总销售额
  3. 查看北京地区销售额>1500的记录
  4. 按月份统计销售趋势

操作

  1. 分类汇总：按地区汇总
  2. 切片器：添加地区和产品切片器
  3. 高级筛选：地区=北京 且 销售额>1500
  4. 添加辅助列：=TEXT(A2,"yyyy-mm") 提取年月
  5. 按年月分类汇总

案例2：贷款计算器​

模拟运算表应用

本金：500000
利率：4.5%
期限：20年

双变量模拟：
- 行：利率（3.5%, 4.0%, 4.5%, 5.0%, 5.5%）
- 列：期限（10年, 15年, 20年, 25年, 30年）
- 结果：月供金额矩阵

案例3：考勤异常提醒​

条件格式应用

规则1：迟到（上班时间>9:00）→ 黄色
规则2：早退（下班时间<18:00）→ 橙色
规则3：缺勤（空白单元格）→ 红色
规则4：加班（下班时间>20:00）→ 绿色

案例4：动态数据验证​

场景：选择省份后，城市下拉列表自动更新

步骤

  1. 准备省份城市对照表

    广东：广州,深圳,珠海
    北京：东城,西城,朝阳

  2. 定义名称（重要！）

    • 名称：广东，引用：=Sheet2!$B$2:$B$4
    • 名称：北京，引用：=Sheet2!$C$2:$C$4

  3. 省份列数据验证：序列 = 广东,北京

  4. 城市列数据验证：

    • 序列 = =INDIRECT($A2)
    • A2为省份所在单元格

原理 INDIRECT函数将省份文本转换为名称引用

18.11 最佳实践​

18.11.1 数据整理建议​

保持数据规范

  • 首行作为标题，不要合并单元格
  • 不要有空行空列
  • 每列数据类型一致
  • 日期使用标准格式

使用表格功能

  • 插入 → 表格（Ctrl+T）
  • 自动扩展，结构化引用
  • 便于筛选、排序、汇总

18.11.2 性能优化​

避免过度使用条件格式

  • 大数据量时影响性能
  • 限定应用范围
  • 使用简单规则

分类汇总替代方案

  • 小数据量：分类汇总
  • 大数据量：数据透视表
  • 动态分析：Power Query

18.11.3 数据安全​

数据验证保护数据完整性

  • 防止错误输入
  • 统一数据格式
  • 设置合理的验证规则

保护工作表

  • 设置数据验证后锁定单元格
  • 保护工作表但允许筛选排序

本章小结​

核心工具回顾

  1. 排序筛选：多条件排序、高级筛选、切片器
  2. 分类汇总：自动汇总、多级汇总、大纲结构
  3. 合并计算：多表汇总、数据链接
  4. 模拟运算：单/双变量敏感性分析
  5. 数据验证：下拉列表、自定义公式验证
  6. 条件格式：数据条、色阶、图标集、自定义规则
  7. 数据分列：分隔符分列、固定宽度分列
  8. 快速填充：智能识别模式填充

学习建议

  • 熟练掌握快捷键提升效率
  • 结合实际场景练习
  • 先理解原理再灵活运用
  • 关注数据规范性

下一步学习

  • 第20章：Excel常用函数详解
  • 第22章：Excel数据透视表精通
  • 第23章：Excel高级函数应用

思考练习​

  1. 创建一个员工信息表，使用数据验证实现部门、职位的下拉选择
  2. 用条件格式设计一个项目进度跟踪表，自动高亮逾期任务
  3. 制作贷款计算器，使用模拟运算表分析不同利率和期限组合
  4. 用分类汇总和切片器分析产品销售数据
  5. 练习快速填充功能，从身份证号提取出生日期和性别

练习数据集 建议使用真实场景数据：考勤记录、销售数据、学生成绩等。