加總平行四邊型範圍內的數字

有沒有什麼簡便的方法，來加總平行四邊型範圍內的數字呢？

上圖為改編自真實案例的概念示意圖。
我試了四種方法，覺得沒有一種是足夠優雅的。全數列在下面供大家參考。
也歡迎提供更漂亮的方法。

以下每一個方法都會附上一格公式範例。細節則請參考完整範例檔。

方法一

使用SUM函數，並且手動慢慢把要加總的範圍填入函數的引數中。
這種方法給人帶來一種勤勞的美感。
而且如果要框選的範圍不大，這樣做確實可以比寫複雜的公式要快得多。

=SUM('raw data'!B2:B13,'raw data'!C2:C12,'raw data'!D2:D11,'raw data'!E2:E10,'raw data'!F2:F9,'raw data'!G2:G8,'raw data'!H2:H7,'raw data'!I2:I6,'raw data'!J2:J5,'raw data'!K2:K4,'raw data'!L2:L3,'raw data'!M2)

方法二

類似中學數學的線性規劃，用二元一次方程式的概念把我們要加總的區域表達出來。然後用SUMIFS函數，針對符合條件的儲存格做加總。
因平行四邊型有四個邊，每個邊都可以用一條方程式來表達。每一條方程式就是一個條件，所以每個SUMFIS的公式裡都會用到四個條件。
此方法不需要任何計算輔助區，也不受限於欄/列的名稱格式，純粹以幾何概念對平行四邊型的範圍做加總。

(提醒：此為陣列公式，需按Ctrl + Shift + Enter以完成輸入)

{=SUM(range*
((ROW(range)+COLUMN(range))>IFERROR((RIGHT(A$10,4)-2012),-1)*12+15)*
((ROW(range)+COLUMN(range))<=(RIGHT(B$10,4)-2012)*12+15)*
(COLUMN(range)>IFERROR((LEFT($A10,4)-2012),-1)*12+13)*
(COLUMN(range)<=(LEFT($A11,4)-2012)*12+13))}

方法三

另闢一區與原始資料範圍1:1的計算輔助區，在輔助區做判斷，在原始資料區做加總。

=SUMIFS(range,
ref,">="&LEFT($A19,4),
ref,"<"&LEFT($A19,4)+1,
ref,">="&LEFT($A19,4)&"."&RIGHT(B$18,4),
ref,"<"&LEFT($A19,4)&"."&RIGHT(B$18,4)+1)

輔助區公式示例：

=LEFT(B$1,4)+LEFT(B$1,4)/10000+((RIGHT($A2,2)-1)/12/10000)+((RIGHT(B$1,2)-1)/12/10000)

方法四

另闢一區輔助區，在輔助區對原始資料的各欄列做平移，把平行四邊型改換成同面積的矩型再做加總。

=SUM(OFFSET(ref_2!$B$2,
(RIGHT(B$26,4)-2012)*12,
(LEFT($A27,4)-2012)*12,12,12))

輔助區公式示例：

=IF(ISNUMBER(OFFSET('raw data'!B2,-(COLUMNS($B:B)-1),0)),OFFSET('raw data'!B2,-(COLUMNS($B:B)-1),0),0)

範例下載

完整範例檔


Select a repo