鉴于上述示例表具有语义“标题”、“子标题”和“小计”行，我试图确定一个公式来定位相对于当前单元格的上一个子标题行。例如，如果在 中输入公式F5，它将定位行2，如果在 中输入公式F17，它将定位行13。
根据 或 列中的值，行被有条件地格式化为标题、子标题或小计，即H子标题行是 的行。现在，我想将这个概念扩展到我的公式中。ST$A:$An$An = "S"
标题行后面总是会跟着一个副标题（因此我不必担心标题和副标题的顺序混乱）。
我尝试了以下方法：
=MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)这总是返回行
2，因为MATCH返回第一的匹配，在集合中，我无法限制OFFSET高度（即递归，因为前一个子标题位置未知）；{=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}这也返回
2，因为即使在数组上下文中（即使用++ Ctrl），仍然只返回第一个结果；AltEnterMATCH=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)这将返回
0，因为IF这里不期望一个数组，所以扩展为一个不匹配的OFFSET($A5, 0, 0, -ROW($A5), 1)单个值，并将其视为数字；0"S"LARGEFALSE{=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}这将返回
#VALUE，因为数组扩展发生得太早，从而留下-ROW($A5)数组-{5}，它不是有效的数字height参数OFFSET（我希望IF(OFFSET(...)="S",...)位是一个数组，而不是-ROW($A5)位，但 Excel 无法区分）。
我目前的目标是 Excel 2010。早期版本不适用（尽管向前兼容性是一个优势）。我试图避免使用 VBA，因为对于我来说，分发 *.xlsm 文件比分发 *.xlsx 文件更困难（此外，我已经知道如何使用 VBA 来做到这一点）。
还有什么我可以尝试的吗？
答案1
最简单的方法是作弊并使用混合绝对/相对公式。这是一个数组公式（使用 CTRL+SHIFT+ENTER 输入），输入到单元格中，B4但它可以位于第 4 行的任何位置。它将返回标记为 的行号S。
=MAX(IF($A$1:A4="S",ROW($A$1:A4)))
向下复制时，引用的第二部分B4 and A4将会增加。这可确保获得匹配度最大的行多于当前行。F4输入/选择相关范围后，您可以使用 更快地输入这些公式。这将循环显示所有选项中的美元符号。
范围图片
用于替换你的公式
在阅读了问题后（并基于@SteveTaylor 的编辑），您似乎需要这样做来更新公式。您可以使用从上面返回的行以及INDEX获取要求和的数据范围。我看到 2 个可以替换的公式：
- 对每个标记行的数据进行总计计算。在这种情况下，可以动态引用上面的小计行。
- 小计行的总计计算。在这种情况下，可以动态引用上面要求和的值。
对于单行数据，您可以使用公式，F3以数组公式开始。请注意，我已切换到使用SUMPRODUCT，这使得转到 2 列以上变得容易得多。
=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)
对于总行公式，您可以F11再次使用数组公式：
=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))
如果您希望一个公式可以控制所有公式！那么您可以IF根据列中的值将它们组合成一个嵌套公式A。这是所说的数组公式，F2可以从中开始向下复制。
=IF(
A2="S",
SUM(D2:E2),
IF(A2="T",
SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))),
C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))
此公式不区分空白行和“数据”行。它目前对间隔行返回 0，这没问题。
和公式的结果图获取两块数据。
