Excel按每第5列堆叠数据并排除空行的公式异常问题

Hey Nick, 看起来你用OFFSET公式堆叠每5列数据时，遇到了每组底部内容被截断的问题对吧？我来帮你拆解下问题所在，再给你适配不同Excel版本的解决方案。

为什么你的OFFSET公式会截断数据 #

你当前的公式：

=OFFSET(Sheet1!A$2,MOD(ROW()-2,COUNTA(Sheet1!A:A)-1),ROUNDDOWN((ROW()-2)/(COUNTA(Sheet1!A:A)-1),0)*5)

它的核心逻辑是用A列的非空行数作为所有列的统一行数标准，但问题是：

• 如果其他列的非空行数比A列多，那么超出A列行数的那些行就会被直接跳过，导致每组底部数据丢失；
• 如果其他列的非空行数比A列少，公式会读取到空行，甚至出现错误值。

从你提供的数据来看，不同列的有效行数明显不一致（比如第6行开始部分列就没有数据了），这就是截断问题的根源。

解决方案分两种情况： #

情况1：使用Excel 365/2021（支持动态数组）

这个版本有现成的VSTACK和FILTER函数，能完美解决你的需求，公式简单直观，还自动排除空行：
假设你要堆叠的是A列、F列、K列、P列...每间隔5列的列，公式如下（可以根据实际列范围调整）：

=VSTACK(FILTER(Sheet1!A:A,Sheet1!A:A<>""),FILTER(Sheet1!F:F,Sheet1!F:F<>""),FILTER(Sheet1!K:K,Sheet1!K:K<>""),FILTER(Sheet1!P:P,Sheet1!P:P<>""))

• FILTER负责提取单列的所有非空行；
• VSTACK把这些提取出来的列数据垂直堆叠在一起，完全不会出现截断问题。

情况2：使用旧版Excel（无动态数组）

我们可以用INDEX+AGGREGATE组合来模拟，这个组合能忽略错误值，自动跳过空行。在新工作表的A2单元格输入以下公式，然后下拉填充直到出现空值：

=IFERROR(INDEX(Sheet1!$A:$S,
    AGGREGATE(15,6,ROW(Sheet1!$2:$1000)/(INDEX(Sheet1!$2:$1000,,(INT((ROW(A1)-1)/100)+1)*5-4)<>""),
    MOD(ROW(A1)-1,100)+1),
    (INT((ROW(A1)-1)/100)+1)*5-4),"")

这里的100是预设的单列最大非空行数（你可以根据实际数据调整成更大的数，比如500），(INT((ROW(A1)-1)/100)+1)*5-4是用来定位每5列的起始列（比如1,6,11...）。这个公式会逐个提取每列的非空行，直到所有列的有效数据都被堆叠完成。

针对你提供的测试数据的适配 #

看你给出的数据是从F列开始的三组4列数据（Home、Cats、Dogs、Mice），如果你的需求是把每组的4列数据整体堆叠（比如F-I列的所有非空行，然后J-M列，然后N-Q列），那调整公式即可：

• 动态数组版：

  =VSTACK(FILTER(Sheet1!F:I,Sheet1!F:F<>""),FILTER(Sheet1!J:M,Sheet1!J:J<>""),FILTER(Sheet1!N:Q,Sheet1!N:N<>""))
  

• 旧版Excel版：同样用INDEX+AGGREGATE，调整列偏移的计算逻辑即可。

备注：内容来源于stack exchange，提问作者Nick P