如何编写Excel公式自动判定ID的已占用状态

规则说明 #

• ID命名规则：同一主ID对应的衍生ID采用「主ID_递增数字」的后缀格式命名
• 状态判定规则：
  • 单条记录自身Item列数值非0时，对应Engaged列标记为Yes
  • 所有带下划线后缀的同主ID衍生记录中，只要任意一条Item列存在非零值，该主ID下全部衍生记录的Engaged列统一标记为Yes
• 实现目标：通过Excel公式自动计算填充Engaged列，消除人工判定误差

可用公式 #

以下公式默认ID列为A列、Item列为B列，数据从第2行开始，Engaged列从C2单元格开始填充，输入公式后下拉覆盖全列即可生效。

全版本通用公式（适配所有Excel版本） #

=IF(SUMPRODUCT(--(LEFT($A$2:$A$999,FIND("_",$A$2:$A$999&"_")-1)=LEFT(A2,FIND("_",A2&"_")-1))*--($B$2:$B$999<>0))>0,"Yes","No")

公式中的$A$999、$B$999为表格数据最大行引用，可根据实际数据行数修改，比如数据共2000行就将数值改为2000即可。

高版本简化公式（适配Excel 365/2021及以上支持动态数组的版本） #

=IF(SUM(--(TEXTBEFORE($A$2:$A$999,"_",,1)=TEXTBEFORE(A2,"_",,1))*--($B$2:$B$999<>0))>0,"Yes","No")

该公式用原生TEXTBEFORE函数直接提取下划线前的主ID，计算效率更高，逻辑和通用公式完全一致。

逻辑说明 #

• 第一步自动提取当前行的主ID：无论ID本身是否带_数字后缀，都能准确截取到下划线前的主ID内容，无后缀的ID会直接取自身完整值作为主ID
• 第二步遍历全表所有行，匹配和当前行主ID一致的所有记录，统计这些记录中Item列非0的条目总数
• 只要统计值大于0，就说明同主ID下存在Item非0的记录，当前行Engaged列返回Yes，否则返回No

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