Excel 基础操作和函数
1189 字约 4 分钟
2026-05-20
本页定位
这一章是 Excel 数据分析的基本功。目标不是“会点表格操作”，而是能稳定把原始数据整理成结构化数据，并用基础函数完成日常业务分析。
1. 数据表规范
适合分析的数据表应该满足：
- 第一行是字段名，不使用合并单元格。
- 每一行是一条记录，每一列是一个变量。
- 同一列保持同一数据类型，例如日期列不要混入文本。
- 字段命名清晰，例如
订单ID、用户ID、订单日期、实付金额。 - 原始数据、清洗数据、计算过程、展示报表分开存放。
常见工作表结构：
| 工作表 | 用途 |
|---|---|
Raw | 原始数据，只导入，不手工修改 |
Clean | 清洗后的明细数据 |
Dim | 维度表，如商品、门店、用户、日期 |
Calc | 计算区域、透视表、辅助表 |
Dashboard | 最终展示和交付 |
2. 基础操作
排序和筛选
排序用于看排名和趋势，筛选用于快速定位问题数据。
- 按销售额降序，看 Top 商品或 Top 门店。
- 按日期升序，检查是否存在断档。
- 按空白筛选，定位缺失值。
- 按文本包含筛选，检查异常标签。
删除重复值
删除重复前先确认唯一键。订单表通常用 订单ID 去重，用户表通常用 用户ID 去重。如果按所有列去重，可能会漏掉业务上的重复记录。
数据验证
数据验证用于减少录入错误：
- 下拉列表限制输入范围。
- 日期范围限制开始和结束时间。
- 数值范围限制折扣率、价格、数量。
- 自定义公式限制手机号、邮箱、编码格式。
条件格式
条件格式适合做异常提示：
- 销售额低于目标值标红。
- 毛利率低于阈值高亮。
- 重复订单号高亮。
- 到期时间小于 7 天提醒。
3. 常用函数
查找匹配
XLOOKUP 是现代 Excel 中优先使用的查找函数。
=XLOOKUP(A2, 商品表[商品ID], 商品表[商品名称], "未找到")常见场景：
- 用商品 ID 匹配商品名称。
- 用用户 ID 匹配用户等级。
- 用门店 ID 匹配区域。
- 用订单 ID 回填支付状态。
旧版本 Excel 可以使用 VLOOKUP：
=VLOOKUP(A2, 商品表!A:D, 2, FALSE)更灵活的组合是 INDEX + MATCH：
=INDEX(商品表[商品名称], MATCH(A2, 商品表[商品ID], 0))条件统计
SUMIFS、COUNTIFS、AVERAGEIFS 是业务分析中最常用的三类函数。
=SUMIFS(订单表[实付金额], 订单表[月份], "2026-05", 订单表[渠道], "自然流量")=COUNTIFS(订单表[月份], "2026-05", 订单表[是否新客], "是")=AVERAGEIFS(订单表[实付金额], 订单表[城市], "上海")关键点是先明确统计口径：
- 时间窗口是什么？
- 指标按订单、用户还是商品统计？
- 是否要去重？
- 是否排除退款、测试单、异常单？
文本处理
常用文本函数：
| 函数 | 用途 |
|---|---|
LEFT | 从左侧截取字符 |
RIGHT | 从右侧截取字符 |
MID | 从指定位置截取 |
LEN | 统计文本长度 |
TRIM | 删除多余空格 |
SUBSTITUTE | 替换文本 |
TEXTSPLIT | 拆分文本 |
TEXTBEFORE | 取分隔符前内容 |
TEXTAFTER | 取分隔符后内容 |
示例：从编码 CN-SH-001 中提取城市：
=TEXTBEFORE(TEXTAFTER(A2, "-"), "-")日期处理
常用日期函数：
=YEAR(A2)
=MONTH(A2)
=EOMONTH(A2, 0)
=TEXT(A2, "yyyy-mm")典型用途：
- 把订单日期转成月份。
- 计算月末日期。
- 判断是否本周、本月、本季度。
- 计算用户注册到下单的间隔天数。
4. 数据透视表
数据透视表是 Excel 分析效率最高的功能之一。它适合回答：
- 每个月销售额趋势如何？
- 各渠道转化率谁更好？
- 哪些商品贡献了主要收入？
- 不同城市、门店、用户层级表现如何？
使用数据透视表时注意：
- 明细表必须是一行一条记录。
- 金额字段不要存成文本。
- 日期字段要是真日期，不是文本日期。
- 先确认聚合方式，是求和、计数、平均还是去重计数。
5. 基础分析指标
常见经营指标：
| 指标 | 公式 |
|---|---|
| 销售额 | SUM(实付金额) |
| 订单数 | COUNT / DISTINCTCOUNT(订单ID) |
| 客单价 | 销售额 / 订单数 |
| 转化率 | 下单人数 / 访问人数 |
| 复购率 | 复购用户数 / 成交用户数 |
| 毛利率 | 毛利 / 销售额 |
Excel 的基础能力不是孤立函数，而是“数据结构 + 口径 + 函数 + 透视表”的组合。