鑑於上面的範例表具有語義「標題」、「子標題」和「小計」行，我試圖確定一個公式來定位相對於當前單元格的前一個子標題行。例如，如果在 中輸入公式F5，它將找到 row 2，如果輸入到 中F17，它將找到 row 13。
H根據值或列的存在情況，將行有條件地格式化為標題、子標題或小計S，即子標題行是其中 的行。現在，我想將這個概念擴展到我的公式中。T$A:$An$An = "S"
標題行後面總是跟著一個子標題（因此我不必擔心標題和子標題亂序）。
我已經嘗試過以下方法：
=MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)這總是返回 row
2，因為MATCH返回第一的匹配，在集合中，並且我無法限制OFFSET高度（即遞歸地，因為之前的子標題位置未知）；{=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}這也會返回
2，因為即使在數組上下文中（即使用Ctrl+ Alt+ Enter），MATCH仍然只返回第一個結果；=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)array-{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取得要求和的資料範圍。我看到兩個可以替換的公式：
- 每個標記資料行的總計算。在這種情況下，可以動態引用上面的小計行。
- 小計行的總計計算。在這種情況下，可以動態引用上面求和的值。
對於單行數據，您可以使用公式，從F3數組公式開始。請注意，我改用了 using 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根據 column 中的值將它們組合成嵌套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，這很好。
結果和公式圖片兩個資料塊。
