如何在引用单元格时指定偏移行以实现表格数据结构化转换

公式实现（基于VLOOKUP+辅助计算） #

1. 新增辅助列计算累计跳过行数 #

在源表格添加辅助列（如D列），计算每个分类/子分类的前置占用行数：

• 分类级跳过行数：计算当前分类前所有分类的总行数（子分类标题+条目）

  =SUMIF($A$2:$A$100, "<>"&A2, $C$2:$C$100) + COUNTIF($A$2:$A$100, "<>"&A2)
  

  注：$A$2:$A$100为分类列范围，$C$2:$C$100为条目列范围，按需调整。
• 子分类级跳过行数：计算同分类下当前子分类前的总行数

  =SUMIFS($C$2:$C$100, $A$2:$A$100, A2, $B$2:$B$100, "<>"&B2) + COUNTIFS($A$2:$A$100, A2, $B$2:$B$100, "<>"&B2)
  

2. 用VLOOKUP/INDEX+MATCH定位目标值 #

• 提取分类标题：

  =VLOOKUP("B", $A$2:$D$100, 2, FALSE)
  

• 提取指定子分类的条目（跳过前置行）：

  =INDEX($C$2:$C$100, MATCH("B", $A$2:$A$100, 0)+D2+1)
  

  这里D2是分类B的跳过行数，+1是跳过分类标题行，直接定位到第一个条目。

更高效的非公式方案 #

1. Power Query（推荐） #

无需手动计算行数，通过可视化操作完成转换：

• 选中源数据 → 「数据」选项卡 → 「从表格/区域」导入Power Query
• 按「分类」「子分类」分组，将「条目」列合并为列表
• 选择「转换」→ 「透视列」，以「子分类」为列，「合并的条目」为值，展开列表即可得到目标格式
• 点击「关闭并上载」，直接生成转换后的表格

2. 动态数组函数（Excel 365/2021） #

用FILTER批量提取指定分类/子分类的条目，自动填充：

=FILTER($C$2:$C$100, ($A$2:$A$100="B")*($B$2:$B$100="2"))

该公式会一次性返回分类B下子分类2的所有条目，无需手动计算跳过行数。

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