Excel 365下构建销售数据一对多关系实现Store Brand与其他品牌对比

前置准备 #

首先确认两张基础表结构，建议均转换为Excel超级表（选中数据区域按Ctrl+T勾选「表包含标题」后确认），方便公式自动适配数据更新：

• 销售原始表（表名设为销售表），包含4个字段：
  • 日期：标准日期格式
  • 类型：标注对应产品是*Store Brand（自有品牌）*还是其他品牌
  • 产品编号：产品唯一编码
  • 销售额：数值格式的销售金额
• 品牌映射表（表名设为映射表），包含2个字段：
  • Store Brand产品编号：自有品牌的产品编码
  • 对应其他品牌产品编号：和该自有产品可比的竞品编码

步骤1：生成全量对比维度组合 #

我们需要生成「所有年月 + 所有可比产品对」的全量组合，确保每个时段的所有对比关系都不会遗漏：

1. 先提取销售数据中所有唯一年月，在空白单元格（假设为A2）输入动态数组公式：

=SORT(UNIQUE(DATE(YEAR(销售表[日期]),MONTH(销售表[日期]),1)))

公式会自动溢出所有出现过的年月（统一为每月1日的日期格式，方便后续匹配），在A1单元格输入表头年月
2. 生成全量年月+可比产品对的组合，在B1单元格输入表头Store Brand产品编号，C1输入对应其他品牌产品编号，在B2单元格输入公式：

=LET(
年月列,A2#,
映射列,映射表[#全部],
年月行数,ROWS(年月列),
映射行数,ROWS(映射列),
全量年月,INDEX(年月列,SEQUENCE(年月行数*映射行数,,1,1/映射行数)),
全量映射,INDEX(映射列,SEQUENCE(年月行数*映射行数,,1,1/年月行数),{1,2}),
HSTACK(全量年月,全量映射)
)

公式会自动溢出所有对比维度组合，覆盖所有需要的对比场景

步骤2：计算对应销售额 #

选中前三列数据区域按Ctrl+T转成超级表后，新增两列计算销售额：

• Store Brand销售额（表头为Store Brand销售额），首行公式：

=SUMIFS(销售表[销售额],销售表[类型],"Store Brand",销售表[产品编号],[@Store Brand产品编号],YEAR(销售表[日期]),YEAR([@年月]),MONTH(销售表[日期]),MONTH([@年月]))

无对应销售时自动返回0，符合要求

• 其他品牌销售额（表头为其他品牌销售额），首行公式：

=SUMIFS(销售表[销售额],销售表[类型],"<>Store Brand",销售表[产品编号],[@对应其他品牌产品编号],YEAR(销售表[日期]),YEAR([@年月]),MONTH(销售表[日期]),MONTH([@年月]))

超级表会自动把公式填充到所有行，无需手动下拉

步骤3：拆分年月为单独字段（可选） #

如果需要单独的年份、月份字段，新增两列：

• 年份列公式：=YEAR([@年月])
• 月份列公式：=MONTH([@年月])
  完成后可按需隐藏年月列

注意事项 #

• 若销售表中自有品牌的类型标识不是Store Brand文本，请自行修改公式中的判断条件为实际使用的标识值
• 若需要包含无任何销售记录的空白年月，手动补充到A列的年月序列中即可，后续公式会自动适配
• 映射表新增/删除对比关系、销售表新增数据后，选中超级表右键刷新即可自动更新结果

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