咨询按指定规则为20000家门店缺货数据表格设置颜色编码的最优方法

针对20000家门店的缺货数据标记需求，最直接高效的方式是使用Excel或Google Sheets的条件格式+自定义公式，无需额外编程，且支持数据更新后自动同步格式。以下是分步操作指南：

前提：数据结构准备 #

确保表格包含以下核心列（可根据实际调整列名）：

• 列A：门店ID
• 列B：分区（值为1-10）
• 列C：2020-2023年总缺货量（需预先计算各门店四年缺货量之和）
• 列D：2023年单独缺货量

步骤1：设置橙色标记（分区内2023年缺货量最高门店） #

橙色标记优先级高于红色，需先配置：

1. 选中需要格式化的所有行（如A2:D20001，表头在第1行）
2. 打开「条件格式」→「新建规则」（Excel）或「格式」→「条件格式」→「添加规则」（Google Sheets）
3. 选择「使用公式确定要设置格式的单元格」
4. 输入自定义公式：

   =SUMPRODUCT(($B:$B=$B2)*($D:$D>$D2)) +1 =1
   

   公式说明：计算当前门店在同分区内2023年缺货量的排名，排名为1时触发格式
5. 点击「格式」→ 填充色选择橙色，确认保存规则

步骤2：设置红色标记（分区内2020-2023总缺货量前200门店） #

1. 再次新建条件格式规则，同样选择「使用公式确定要设置格式的单元格」
2. 输入自定义公式：

   =SUMPRODUCT(($B:$B=$B2)*($C:$C>$C2)) +1 <=200
   

   公式说明：计算当前门店在同分区内四年总缺货量的排名，排名≤200时触发格式
3. 点击「格式」→ 填充色选择红色，确认保存规则

步骤3：调整规则优先级 #

在条件格式规则管理器中，将橙色规则拖动到红色规则上方，确保同时满足两个条件的门店显示橙色而非红色。

注意事项 #

• 处理并列排名：如果多个门店缺货量相同，上述公式会将所有并列的门店纳入标记范围（比如并列第200的门店都会变红）。若需严格限制仅前200个唯一门店，可添加辅助列用RANK.EQ+COUNTIF生成唯一排名后再设置格式。
• 性能优化：对于20000行数据，SUMPRODUCT公式运行稳定；若出现卡顿，可将列范围缩小为实际数据区域（如$B$2:$B$20001而非$B:$B）。

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