Google Sheets三级树形结构计算与自动可视化需求

核心需求与计算逻辑 #

基于原始乘数（如36）和一组数值（如3、2、7），自动生成三级树形结构表格：

• Level 1：每个原始数值 × 乘数
• Level 2：每个Level 1值按原始数值的占比拆分，生成n²个值（n为原始数值的数量）
• Level 3：每个Level 2值按同样占比规则拆分，生成n³个值
• 最终以树形结构展示（支持Table B的层级列展示，或Table C的缩进换行展示）

方案实现（无重复数据/重复数据通用） #

以下公式均基于假设：乘数存于A1，原始数值存于A2:A（非空），结果从D1开始生成。

1. Table B 样式（层级分列展示） #

Level 1、Level 2、Level 3分别占一列，每个Level 1对应n²行，每个Level 2对应n行Level 3：

• Level 1列（D列）：

=ARRAYFORMULA(INDEX(A2:A*A1, CEILING(SEQUENCE(COUNTA(A2:A)^3)/COUNTA(A2:A)^2)))

• Level 2列（E列）：

=ARRAYFORMULA(INDEX(FLATTEN((A2:A*A1)*(TRANSPOSE(A2:A)/SUM(A2:A))), CEILING(SEQUENCE(COUNTA(A2:A)^3)/COUNTA(A2:A))))

• Level 3列（F列）：

=ARRAYFORMULA(FLATTEN(FLATTEN((A2:A*A1)*(TRANSPOSE(A2:A)/SUM(A2:A)))*TRANSPOSE(A2:A)/SUM(A2:A)))

2. Table C 样式（缩进换行树形展示） #

每行对应一个层级，Level 1顶格，Level 2缩进2空格，Level 3缩进4空格：

=ARRAYFORMULA(LET(
  n, COUNTA(A2:A),
  total_rows, n^3,
  seq, SEQUENCE(total_rows),
  --计算各层级索引--
  level1_idx, CEILING(seq/n^2),
  level2_idx, CEILING(seq/n) - (level1_idx-1)*n,
  --计算各层级数值--
  level1_val, INDEX(A2:A*A1, level1_idx),
  level2_val, INDEX(FLATTEN((A2:A*A1)*(TRANSPOSE(A2:A)/SUM(A2:A))), (level1_idx-1)*n + level2_idx),
  level3_val, INDEX(FLATTEN(FLATTEN((A2:A*A1)*(TRANSPOSE(A2:A)/SUM(A2:A)))*TRANSPOSE(A2:A)/SUM(A2:A)), seq),
  --判断当前行所属层级--
  is_level1, MOD(seq-1, n^2)=0,
  is_level2, MOD(seq-1, n)=0 AND NOT(is_level1),
  --生成带缩进的文本--
  IF(is_level1, level1_val, IF(is_level2, "  "&level2_val, "    "&level3_val))
))

重复数据问题解决 #

之前的reduce函数因依赖唯一值分组失效，上述方案通过位置索引定位（CEILING+SEQUENCE）实现分组，完全不依赖原始数值是否重复，仅根据n²、n的块规则分配层级值，适配所有2-12个数值的场景。

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