Excel码头缓冲库存分布可视化：非宏公式实现方法问询

一、基础数据结构设计 #

先整理扫描数据到一张「扫描记录表」（比如Sheet1），列结构如下：

• A列：缓冲位名称（如Mx1、Mx2）
• B列：托盘序号（1~10，对应每个缓冲位的10个托盘位置）
• C列：预约追踪码（扫描获取的12345ABC、12345XYZ等）
• D列：颜色标识（辅助列，用于后续颜色区分）

二、可视化区域数据同步公式 #

假设可视化视图在Sheet2，每行对应一个缓冲位：

• F列：缓冲位名称（与Sheet1的A列一致）
• G~P列：对应该缓冲位的10个托盘位置（G=1、H=2…P=10）

同步追踪码到可视化区域 #

在Sheet2的G2单元格输入以下公式，然后横向拖拽到P2，再纵向拖拽到所有缓冲位行：

=IFERROR(INDEX(Sheet1!$C:$C, MATCH($F2&G$1, Sheet1!$A:$A&Sheet1!$B:$B, 0)), "")

• 说明：$F2锁定当前缓冲位，G$1锁定托盘序号；通过MATCH匹配缓冲位+托盘序号的组合，INDEX提取对应的追踪码，空值则返回空白。
• 兼容旧版Excel：若使用Excel 2019及更早版本，需按Ctrl+Shift+Enter作为数组公式输入。
• 简化版（Excel 365/2021）：用XLOOKUP替代更直观：

  =XLOOKUP($F2&G$1, Sheet1!$A:$A&Sheet1!$B:$B, Sheet1!$C:$C, "")
  

三、追踪码颜色区分（无宏条件格式） #

方法1：自动分配唯一颜色（适合大量追踪码） #

1. 在Sheet1的D列（颜色标识）输入公式：

   =MATCH(C2, UNIQUE(Sheet1!$C:$C), 0)
   

   该公式会给每个首次出现的追踪码分配唯一数字（第一个码=1，第二个=2…），重复码会得到相同数字。
2. 在Sheet2的第一行（可隐藏）新增对应公式，比如G1输入：

   =IFERROR(INDEX(Sheet1!$D:$D, MATCH($F2&G$1, Sheet1!$A:$A&Sheet1!$B:$B, 0)), "")
   

   拖拽覆盖所有可视化托盘单元格，同步颜色标识数字。
3. 选中Sheet2中所有可视化托盘单元格（G2:Pxx），点击「开始」→「条件格式」→「色阶」，选择一个多色渐变（如绿-蓝-红）。Excel会根据颜色标识数字自动分配不同颜色，相同追踪码的单元格颜色一致。

方法2：手动指定颜色（适合少量追踪码） #

若追踪码数量不多，可逐个设置规则：

1. 选中可视化区域→「条件格式」→「新建规则」→「只为包含以下内容的单元格设置格式」
2. 选择「特定文本」→「包含」，输入目标追踪码（如12345ABC）
3. 点击「格式」→「填充」，选择对应颜色，重复此步骤为每个追踪码分配唯一颜色。

四、关键注意事项 #

• 扫描记录表中，每个「缓冲位+托盘序号」的组合必须唯一，避免重复扫描导致数据覆盖。
• 若旧版Excel无法使用UNIQUE函数，可将Sheet1的D列公式替换为=MATCH(C2, Sheet1!$C$2:C2, 0)，同样能让相同追踪码得到一致的数字标识。

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