如何自动化调整单元格引用 实现表格横向数据转纵向地址块

你可以通过两种方案实现无需手动改行号的批量排版： #

方案1：单数组公式一键生成（推荐） #

直接在新表的首个空白单元格（如A1）输入以下数组公式，即可自动读取所有联系人数据并按你需要的纵向块状格式输出，后续原始表新增联系人也会自动同步更新：

=ARRAYFORMULA(
  LET(
    // 过滤获取NameDirectory页从第4行开始的所有有效联系人数据
    raw_data, FILTER(NameDirectory!B4:I, NameDirectory!B4:B<>""),
    // 计算总联系人数量
    contact_count, ROWS(raw_data),
    // 每个联系人占5行（可根据实际需求调整），生成行序列
    seq, SEQUENCE(contact_count*5),
    // 计算当前行对应第几个联系人、属于联系人的第几行
    contact_idx, QUOTIENT(seq-1,5)+1,
    line_idx, MOD(seq-1,5)+1,
    // 按行返回对应内容
    SWITCH(line_idx,
      1, INDEX(raw_data,contact_idx,1)&" "&INDEX(raw_data,contact_idx,2),
      2, INDEX(raw_data,contact_idx,3),
      3, INDEX(raw_data,contact_idx,4),
      4, INDEX(raw_data,contact_idx,5),
      5, INDEX(raw_data,contact_idx,6)&" "&INDEX(raw_data,contact_idx,7)&" "&INDEX(raw_data,contact_idx,8),
      ""
    )
  )
)

你可以根据自己的实际字段数量、每个联系人占用的行数，调整公式里的行数参数和SWITCH分支的内容即可。 #

方案2：可拖拽填充的普通公式 #

如果不习惯使用数组公式，可以按以下步骤操作：

1. 假设你第一个联系人的3行内容从A1开始填写，分别输入三个公式：
   • A1（姓名行）：=INDIRECT("NameDirectory!B"&QUOTIENT(ROW()-1,3)+4)&" "&INDIRECT("NameDirectory!C"&QUOTIENT(ROW()-1,3)+4)
   • A2（联系方式行）：=INDIRECT("NameDirectory!D"&QUOTIENT(ROW()-1,3)+4)
   • A3（地址行）：=INDIRECT("NameDirectory!E"&QUOTIENT(ROW()-1,3)+4)&" "&INDIRECT("NameDirectory!F"&QUOTIENT(ROW()-1,3)+4)
2. 选中A1到A3三个单元格，将鼠标移动到选中区域右下角的填充柄上，按住左键向下拖拽即可，所有行的引用行号会自动按每3行+1的规则递增，无需手动修改。

原理说明：QUOTIENT(ROW()-1,3)的作用是每3行返回的数值自动加1，刚好匹配你每个联系人占3行、逐联系人行号+1的需求。如果你每个联系人占用5行，把公式里分母的3改成5即可。

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