如何基于三列创建可重置且重复值复用的自动编号公式
2026-6-25
三级编码生成解决方案
针对你需要的品牌、型号、颜色三级自动编码需求，以下是适配自定义输入的公式方案，假设数据从第2行开始（A列=品牌，B列=型号，C列=颜色）：
1. 品牌编码（D列）
给每个首次出现的品牌分配递增编号，重复品牌复用同一编号：
=MATCH(A2, UNIQUE($A$2:A2), 0)
- 逻辑：
UNIQUE($A$2:A2)提取当前行及以上的唯一品牌列表，MATCH定位当前品牌在列表中的位置，自动实现新品牌递增、旧品牌复用。
2. 型号编码（E列）
同品牌下型号编号重置，同一品牌内相同型号复用编号：
=MATCH(B2, UNIQUE(FILTER($B$2:B2, $A$2:A2=A2)), 0)
- 逻辑：
FILTER($B$2:B2, $A$2:A2=A2)筛选出当前品牌下的所有型号（到当前行），UNIQUE去重后，MATCH定位当前型号的位置，实现品牌切换时编号重置。
3. 颜色编码（F列）
同型号下颜色编号重置，同一型号内相同颜色复用编号：
=MATCH(C2, UNIQUE(FILTER($C$2:C2, ($A$2:A2=A2)*($B$2:B2=B2))), 0)
- 逻辑：
FILTER筛选出当前品牌+型号下的所有颜色（到当前行），UNIQUE去重后MATCH定位，实现型号切换时编号重置。
4. 组合编码（G列）
将三级编码拼接为统一格式（如1-1-1）：
=D2&"-"&E2&"-"&F2
示例结果对应
结合你提供的表格，生成的编码如下：
| Name | Model | Colour | 品牌编码 | 型号编码 | 颜色编码 | 组合编码 |
|---|---|---|---|---|---|---|
| Honda | Accord | Black | 1 | 1 | 1 | 1-1-1 |
| Honda | Civic | Red | 1 | 2 | 1 | 1-2-1 |
| Toyota | Rav4 | Silver | 2 | 1 | 1 | 2-1-1 |
| Honda | Accord | Blue | 1 | 1 | 2 | 1-1-2 |
| Ford | F-150 | Onyx | 3 | 1 | 1 | 3-1-1 |
| Ford | F-150 | White | 3 | 1 | 2 | 3-1-2 |
| Chevrolet | Silverado | Moonlight | 4 | 1 | 1 | 4-1-1 |
| Ford | F-150 | Steel | 3 | 1 | 3 | 3-1-3 |
| Chevrolet | Silverado | Pearl | 4 | 1 | 2 | 4-1-2 |
| Audi | Q4 | Midnight | 5 | 1 | 1 | 5-1-1 |
| Audi | Q4 | Chrome | 5 | 1 | 2 | 5-1-2 |
| Audi | Q8 | Night | 5 | 2 | 1 | 5-2-1 |
| Audi | Q4 | Gunmetal | 5 | 1 | 3 | 5-1-3 |
注意事项
- 公式需下拉填充至所有数据行，确保引用范围动态扩展；
- 支持自定义输入：无论用户输入新的品牌/型号/颜色，公式都会自动识别并分配对应编号，无需依赖下拉列表；
- 若使用旧版Excel（无
UNIQUE/FILTER函数），可替换为数组公式：- 品牌编码：
=SUM(IF(UNIQUE($A$2:A2)=A2,1,0)*ROW(UNIQUE($A$2:A2)))/ROW(UNIQUE($A$2:A2))（需按Ctrl+Shift+Enter确认） - 型号编码：
=SUM(IF(UNIQUE(FILTER($B$2:B2,$A$2:A2=A2))=B2,1,0)*ROW(UNIQUE(FILTER($B$2:B2,$A$2:A2=A2))))/ROW(UNIQUE(FILTER($B$2:B2,$A$2:A2=A2)))（需按Ctrl+Shift+Enter确认）
- 品牌编码：
内容的提问来源于stack exchange，提问作者Jo Loh
相关产品推荐
相关产品推荐
