Excel/Google Sheets基于Balance表动态生成多供应商表格咨询

需求明确 #

1. 数据源：Balance工作表，包含Name（供应商）、Item（物料）、Quantity（数量）、**Unit（计量单位）**四列
2. 目标效果：在Vendor Balance工作表为每个供应商生成独立表格，表格以Item为行标题、Unit为列标题，单元格值为对应Quantity总和
3. 核心要求：
   • 随Balance表的记录增删改自动更新数据
   • 新增供应商时自动生成对应表格
   • 表格按3个横向排列、换行继续的布局放置
4. 兼容要求：同时支持Excel和Google Sheets，面向新手友好

可行性确认 #

完全可以实现，利用两款工具都支持的动态数组函数（如UNIQUE、FILTER、SUMIFS、WRAPCOLS等）即可完成所有动态需求，无需复杂VBA或脚本。

分步实现（兼容双平台） #

1. 提取并排版供应商列表 #

在Vendor Balance工作表的任意空白单元格（例如A1）输入以下公式，自动提取Balance表的唯一供应商并按3列横向排列：

=WRAPCOLS(UNIQUE(Balance!A:A), 3)

• 效果：新增供应商时，该列表会自动扩展并保持3列布局
• 注意：确保Balance表的Name列无空值，否则会提取空行

2. 为单个供应商生成动态表格 #

以Vendor Balance表A1单元格的供应商（如Test 1）为例，在A2单元格输入以下公式，自动生成该供应商的余额表格：

=VSTACK(
  HSTACK("物料", TRANSPOSE(UNIQUE(FILTER(Balance!D:D, Balance!A:A=A1)))),
  HSTACK(
    UNIQUE(FILTER(Balance!B:B, Balance!A:A=A1)),
    BYROW(UNIQUE(FILTER(Balance!B:B, Balance!A:A=A1)), 
      LAMBDA(item, 
        BYCOL(UNIQUE(FILTER(Balance!D:D, Balance!A:A=A1)), 
          LAMBDA(unit, SUMIFS(Balance!C:C, Balance!A:A=A1, Balance!B:B=item, Balance!D:D=unit))
        )
      )
    )
  )
)

• 公式说明：
  • UNIQUE(FILTER(...))：提取该供应商的唯一Item和Unit
  • SUMIFS：计算对应Item+Unit组合的Quantity总和
  • VSTACK/HSTACK：组合表头和数据，生成完整表格
• 复制公式：将A2的公式复制到其他供应商单元格的下方（如D2、G2、A8等），修改公式中的供应商引用（把A1改成对应单元格，如D1、G1）即可生成对应供应商的表格

3. 自动更新设置 #

• Excel：动态数组函数默认自动更新，若未触发可按F9手动刷新（需使用Excel 365/2021及以上版本）
• Google Sheets：数据变化时会实时自动更新，无需手动操作

4. 美化布局（可选） #

• 给每个表格添加标题：在供应商单元格（如A1）输入=A1&" 供应商余额表"，作为表格标题
• 设置边框：选中生成的表格区域，添加边框区分行列
• 调整列宽：自动调整列宽以适配内容

注意事项 #

1. 数据源规范：Balance表的表头必须在第一行，数据从第二行开始，避免空行或合并单元格
2. 版本要求：Excel需为365/2021及以上版本（支持动态数组和LAMBDA函数），Google Sheets无版本限制
3. 重复数据处理：若Balance表存在同一供应商的同一Item+Unit多条记录，SUMIFS会自动求和，符合需求

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