如何根据单元格值自动递增产品编号（Product Ref）

通用逻辑 #

核心是按Description列的内容分组，统计同类内容的出现次数/新内容的顺序，拼接固定前缀和格式化后的序号，即可实现自动递增的产品编号。 #

1. 场景1：同Description按出现顺序递增编号 #

适用需求：相同Description的产品每出现一次，序号加1，比如第一个键盘是PROD-001，第二个键盘是PROD-002。

Excel/Google Sheets通用公式 #

假设表头在第1行，A列为Product Ref，B列为Description，数据从第2行开始，在A2单元格输入以下公式后下拉填充即可：

="PROD-"&TEXT(COUNTIF(B$2:B2,B2),"000")

公式说明

• "PROD-" 为产品编号固定前缀，可根据业务需求修改
• COUNTIF(B$2:B2,B2) 统计从数据首行到当前行，和当前行Description一致的内容数量，实现同类内容序号自增
• TEXT(..., "000") 将序号格式化为3位补零格式，需要更长序号可以增加0的数量，比如"0000"就是4位编号

2. 场景2：同Description复用相同编号，仅新Description递增编号 #

适用需求：相同Description的产品共用同一个Product Ref，只有出现全新的Description时才生成新的编号，比如所有键盘都用PROD-001，所有鼠标都用PROD-002。

Excel公式（365/2021及以上版本） #

在A2单元格输入以下公式后下拉填充：

="PROD-"&TEXT(IF(COUNTIF(B$1:B1,B2)=0,MAX(IFERROR(--RIGHT(A$1:A1,3),0))+1,VLOOKUP(B2,A$1:B1,2,0)),"000")

旧版本Excel输入公式后需要按Ctrl+Shift+Enter触发数组计算生效。

Google Sheets公式 #

=IF(B2="",,"PROD-"&TEXT(IF(COUNTIF(B$1:B1,B2)=0,MAX(IFERROR(--RIGHT(A$1:A1,3),0))+1,VLOOKUP(B2,A$1:B1,2,0)),"000"))

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