如何在Excel中自动将categorical分类数据编码为对应数值

方案一：固定映射编码（适用于预设好分类对应数值的场景） #

就像你示例里BMW对应0、Audi对应0.25这种提前定好映射规则的情况，用这个方案最稳妥：

• 先单独做一张映射表，比如放在Sheet2的A、B两列：A列填所有分类的文本值，B列填你预设好的对应数值，把所有可能出现的分类都列全
• 回到原数据表，假设你要编码的Make列在A列，编码结果要放在B列，在B2单元格输入公式：
  新版本Excel：=XLOOKUP(A2,Sheet2!A:A,Sheet2!B:B,"未找到匹配项")
  老版本无XLOOKUP：=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"未找到匹配项")
• 输入完按回车，选中B2单元格下拉填充整列即可，十万行以内的量级处理速度都很快。

方案二：自动分配编码（适用于不需要预设映射，只要唯一分类对应唯一数值的场景） #

如果你只是要把不同分类转成不同数值，不需要固定对应关系，甚至需要生成你示例里0到1区间的等差数值，可以不用做映射表，直接用公式实现：

• 还是以Make列在A列、编码放B列为例：
  要生成整数编码：B2输入=MATCH(A2,UNIQUE(A:A),0)-1
  要生成0-1区间的等差编码（和你示例格式一致）：B2输入=(MATCH(A2,UNIQUE(A:A),0)-1)/(COUNTA(UNIQUE(A:A))-1)
  老版本无UNIQUE函数的用户，用这个数组公式，输入完按Ctrl+Shift+Enter确认：=IF(COUNTIF($A$2:A2,A2)=1,SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2))-1,INDEX(B$1:B1,MATCH(A2,A$1:A1,0)))
• 直接下拉填充整列即可，后续新增分类也会自动分配新的编码值。

Make、Model、Transmission三列的编码逻辑完全一致，只需要把公式里的列引用替换成对应列即可。

注意：数据量超过10万行的话，建议填充完公式后，把整列编码结果复制粘贴为纯数值，避免后续编辑表格的时候反复计算卡顿。

内容的提问来源于stack exchange，提问作者Nv9zEs