![查找 A 列中具有特定值的上一行](https://linux22.com/image/1452870/%E6%9F%A5%E6%89%BE%20A%20%E5%88%97%E4%B8%AD%E5%85%B7%E6%9C%89%E7%89%B9%E5%AE%9A%E5%80%BC%E7%9A%84%E4%B8%8A%E4%B8%80%E8%A1%8C.png)
鉴于上述示例表具有语义“标题”、“子标题”和“小计”行,我试图确定一个公式来定位相对于当前单元格的上一个子标题行。例如,如果在 中输入公式F5
,它将定位行2
,如果在 中输入公式F17
,它将定位行13
。
根据 或 列中的值,行被有条件地格式化为标题、子标题或小计,即H
子标题行是 的行。现在,我想将这个概念扩展到我的公式中。S
T
$A:$A
n
$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"
LARGE
FALSE
{=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,这没问题。
和公式的结果图获取两块数据。