Excel带偏移量的动态范围（跳过行）构建求助

嘿，我来帮你搞定这个动态求和的需求！结合你提到的跳过空白/标题行、响应新增条目、包含附加税费的要求，我给你分两种场景来提供解决方案，适配不同版本的Excel：

核心思路回顾 #

我们需要：

• 从H111向下移动5行（也就是H116）作为数据起始点
• 自动跳过中间的空白行或着色标题行
• 对所有资产的累计摊销值求和，新增条目时公式自动更新
• 支持附加税费的统计

方案一：兼容旧版Excel（无动态数组功能） #

如果你的Excel版本是2019及更早，用这个公式：

=SUMPRODUCT(
    (H116:INDEX(H:H, MATCH("*", H:H, -1)) <> "") *  // 筛选H列非空行
    (NOT(ISNUMBER(SEARCH({"标题","合计"}, H116:INDEX(H:H, MATCH("*", H:H, -1))))) *  // 排除标题/合计行
    I116:INDEX(I:I, MATCH("*", H:H, -1))  // 对应累计摊销的数值范围
)

公式解释：

1. MATCH("*", H:H, -1)：找到H列最后一个非空单元格的行号，不管中间有多少空白行，确保新增条目会被自动包含
2. H116:INDEX(H:H, ...)：构建从H116到最后一个非空行的动态范围
3. 第一个条件(H列范围<>"")：只保留有内容的行
4. 第二个条件NOT(ISNUMBER(SEARCH(...)))：排除包含“标题”“合计”这类关键词的行（你可以根据实际标题文本修改括号里的关键词）
5. 最后乘以对应的I列累计摊销值，SUMPRODUCT会自动求和所有符合条件的数值

方案二：Excel 365/2021（用动态数组函数更简洁） #

如果你用的是支持动态数组的Excel版本，FILTER函数会让公式更易读且灵活：

=SUM(
    FILTER(
        I116:I1048576,
        (H116:H1048576 <> "") *  // 非空行
        (NOT(ISNUMBER(SEARCH({"标题","合计"}, H116:H1048576))))  // 排除标题行
    )
)

公式优势：

• 自动扩展范围：新增资产条目时，FILTER会自动识别并包含新行，不需要手动调整公式
• 逻辑清晰：直接用筛选条件明确哪些行需要被统计

附加税费的处理 #

如果需要把附加税费也计入总和，分两种情况调整：

情况1：税费行和资产行在同一范围（H列有“附加税费”标识）

修改FILTER的条件，把税费行包含进来：

=SUM(
    FILTER(
        I116:I1048576,
        (H116:H1048576 <> "") *
        (OR(
            NOT(ISNUMBER(SEARCH({"标题","合计"}, H116:H1048576))),
            ISNUMBER(SEARCH("附加税费", H116:H1048576))
        ))
    )
)

情况2：税费行单独存在（比如固定在某个区域但需要动态识别）

用XLOOKUP单独定位税费金额，加到累计摊销总和里：

=SUM(FILTER(I116:I1048576,(H116:H1048576<>"")*(NOT(ISNUMBER(SEARCH({"标题","合计"},H116:H1048576)))))) + XLOOKUP("附加税费", H:H, I:I, 0)

这里XLOOKUP("附加税费", H:H, I:I, 0)会自动找到H列中“附加税费”对应的I列金额，如果找不到就返回0，避免出错。

注意事项 #

• 请根据实际情况修改公式中的列（比如如果累计摊销在J列，就把I改成J）
• 关键词（比如“标题”“附加税费”）请替换成你表格中实际的文本
• 如果资产行的H列是数值而非文本，把MATCH("*", H:H, -1)改成MATCH(9.99999999999999E+307, H:H, -1)，用来定位最后一个数值型非空行

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