如何筛选Mod列为Units行的重复项并设置条件格式

需求 #

处理游戏模组数据精简，需要实现两个条件格式规则：

• 所有重复的Description（O列）单元格标记为青色
• 仅Mod列为"Units"的行中，重复的Description单元格标记为绿色（绿色需优先于青色显示）

当前公式问题 #

现有公式错误地将非Units行的全局重复项标记为绿色：

• 绿色标记公式：=(($A2="Units") * (COUNTIF($O$2:$O$851, $O2)>1))
• 青色标记公式：=COUNTIF($O$2:$O$851, $O2)>1

问题根源：原绿色公式仅判断当前行是Units且全局重复，而非Units组内的重复，导致非Units行的全局重复项被错误触发绿色标记（当该行恰好是Units行的重复项时）。

数据示例 #

解决方案 #

方案1：修正条件格式公式（无需辅助列） #

绿色标记规则（修正后）

使用COUNTIFS同时限定Mod列和Description列，只统计Units组内的重复项：

=AND($A2="Units", COUNTIFS($A$2:$A$851, "Units", $O$2:$O$851, $O2)>1)

青色标记规则（保留原公式）

=COUNTIF($O$2:$O$851, $O2)>1

关键设置

在条件格式规则面板中，将绿色标记规则拖动到青色规则的上方，确保绿色优先级更高，覆盖青色。

方案2：使用辅助列（更易维护） #

添加辅助列（如P列），在P2输入以下公式并填充至P851：

=IF($A2="Units", COUNTIFS($A$2:$A$851, "Units", $O$2:$O$851, $O2)>1, FALSE)

然后设置条件格式：

• 绿色标记：=$P2=TRUE
• 青色标记：=COUNTIF($O$2:$O$851, $O2)>1
  同样需确保绿色规则优先级高于青色。

方案3：Apps Script自动化处理 #

如果需要批量自动标记，可使用以下脚本：

function markDuplicates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("A2:O851");
  const values = range.getValues();
  const unitsDescMap = new Map();
  
  // 统计Units组内每个描述的出现次数
  values.forEach(row => {
    const mod = row[0];
    const desc = row[14]; // O列对应数组索引14
    if (mod === "Units") {
      unitsDescMap.set(desc, (unitsDescMap.get(desc) || 0) + 1);
    }
  });
  
  // 遍历每行设置格式
  values.forEach((row, idx) => {
    const rowNumber = idx + 2;
    const mod = row[0];
    const desc = row[14];
    const cell = sheet.getRange(`O${rowNumber}`);
    
    // 清除原有格式
    cell.clearFormat();
    
    // 标记全局重复为青色
    const globalCount = values.filter(r => r[14] === desc).length;
    if (globalCount > 1) {
      cell.setBackground("#00FFFF"); // 青色
    }
    
    // 标记Units组内重复为绿色（覆盖青色）
    if (mod === "Units" && unitsDescMap.get(desc) > 1) {
      cell.setBackground("#00FF00"); // 绿色
    }
  });
}

使用步骤：

1. 打开你的表格，点击「扩展程序」→「Apps脚本」
2. 粘贴代码，保存项目，点击运行按钮授权后即可执行。

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