☰

將範圍組的最大總和寫入單一儲存格

tag-icon • tag-icon microsoft-excel worksheet-function
將範圍組的最大總和寫入單一儲存格

考慮以下人為的範例，其中一系列儲存格表示多年來某項內容的每月計數以及一個前導摘要列，例如：

.|A    |B      |C    |D    |E    |F    |G    |H    |I    |J    |K    |...
-+-----+-------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
1|     |Most/3M|Jan19|Feb19|Mar19|Apr19|May19|Jun19|Jul19|Aug19|Sep19|...
2|Foo  |     14|    1|    3|    7|    3|    4|    2|    1|    3|    1|...
3|Bar  |     13|    3|    4|    1|    2|    2|    2|    9|    1|    1|...
4|Baz  |     18|    2|    3|    8|    7|    3|    2|    3|    7|    1|...

中的摘要列B:B代表任何三個月期間的最高計數，這是計算出來的天真地使用：

=MAX(SUM($C2:$E2),SUM($D2:$F2),SUM($E2:$G2),
     SUM($F2:$H2),SUM($G2:$I2),SUM($H2:$J2),
     SUM($I2:$K2))

....然後填寫，但這非常令人惱火，必須維護 - 因為值被添加到最右邊的部分，例如，L:L，M:M，公式必須用SUM($J2:$L2)和SUM($K2:$M2)等進行更新。

一種替代方案是：

={LARGE(($C2:$I2 + $D2:$J2 + $E2:$K2), 1)}

……這不是相當太糟糕了，因為更改:$I2為:$J2、:$J2to:$K2和:$K2to:$L2對於三個月的窗口來說是合理的，並且本身可以通過可怕地使用 來導出INDIRECT，如下所示：

={LARGE(INDIRECT("R[0]C3:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000)),FALSE)+
        INDIRECT("R[0]C4:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+1,FALSE)+
        INDIRECT("R[0]C5:R[0]C"&COUNTA(OFFSET($C2,0,0,1,2000))+2,FALSE), 1)}

然而，隨著視窗大小的增加（即必須添加越來越多的+子句）和/或如果視窗大小是可配置的，例如基於B1（實際上只是 value 3，具有自訂數字）的值，這會變得很痛苦的格式"Most/"0"M"）

我確實想到了可能：

={MAX(SUM(OFFSET($C2:$I2,0,0,1,3)))}
={LARGE(SUM(OFFSET($C2:$I2,0,0,1,3)), 1)}

....我只需要更新給定的範圍和群組大小OFFSET，目的是最終動態導出它，例如使用另一個可怕的INDIRECT，例如：

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&
                           (COUNTA(OFFSET($C2,0,0,1,2000))+COLUMN($C:$C)-B$1),
                           FALSE),
                  0,0,1,B$1)),
       1)

使用時評估公式跨過上述的怪物普通的形式，我發現它最終確實解決為：

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+3-B$1),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(12-3),FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C9",FALSE),0,0,1,3)),1)
=LARGE(SUM(OFFSET($C$2:$I$2,0,0,1,3)),1)
=LARGE(SUM($C$2:$E$2),1)
=LARGE(11,1)
=11

……所以看起來給定的範圍OFFSET沒有擴展為數組，而是直接使用該範圍的左上角單元格OFFSET。

在大批形式（即CTRLENTER），它最終會解析為：

=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&(9+{3}-B$1),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT("R[0]C3:R[0]C"&({12}-3),FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET(INDIRECT({"R[0]C3:R[0]C9"},FALSE),0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,B$1)),1)
=LARGE(SUM(OFFSET({#VALUE!},0,0,1,3)),1)
=LARGE(SUM({#VALUE!}),1)
=LARGE(1,1)
=1

……所以看起來數組擴展發生得太早（即結果COLUMNS($C:$C)），稍後會破壞INDIRECT呼叫。

（兩者LARGE都會MAX產生相同的值——我使用它是LARGE因為它的文檔表明它需要一個數組，而MAX需要一個由單個值組成的無界參數列表。）

考慮到不使用 VBA 或更改工作表佈局的偏好，是否有：

  1. 有什麼技巧力量在正確的位置進行數組擴展，以便SUM(OFFSET)發出一個可由LARGE?使用的數組。
  2. 當資料新增至表格右側時，是否有任何替代方法需要對公式進行零至最小修正？

（PS。我使用的是 Excel 2019，而不是 O365/Insiders，所以我還沒有動態數組/SEQUENCE東西，這看起來可能會變得相關。依賴於此的答案可能對後代有用，但不會'不能解決我的迫切需要...）

答案1

我想建議以下方法來獲取一年中最高的任何 3 個季度的總和，包括輔助數據和 SUM Large 3 Quarter 的值。

在此輸入影像描述

怎麼運作的：

  • 將相關月份的日期放入Row 1並套用儲存格格式mmm-yy。
  • 在 Rnage 中插入季度的名稱T9:T12。

  • 單元格中的公式U9，填寫。

    =IF(ROW(A1)>=5,"",(SUMPRODUCT((ROUNDUP(MONTH(T$1:AE$1)/3,0)=ROW(A1))*(T$2:AE$2))))

注意

  • ROUNDUP(MONTH(T$1:AE$1)/3,0)=Row(A1)返回1，0s是1月份所屬的時間Q1何時0不等其他季度，例如2, 3& 4。
  • SUMPRODUCT，毫不費力地處理所有這些範圍的數據。
  • =ROW(A1))返回 1，代表Q1等等 down 2, 3 & 4。
  • =IF(ROW(A1)>=5,""Blank當公式無法取得 4 之後的季度值時回傳。

取得每個季度的資料後，現在在儲存格中輸入公式U14以求和 3 個最大值。

{=SUM(LARGE(U$9:U$12,{1,2,3}))}

注意

  • 最好使用數組（CSE）形式的公式，因此需要結束Ctrl+Shift+Enter，否則它也可以用作非數組公式。

根據需要調整公式中的儲存格引用。

相關內容