[算表] 利用sumproduct和offset計算加權平均

[ZOROCOOL (DH)]

軟體: Excel

版本: 2016

參考: https://goo.gl/iN6iS2

在上面這個試算表內
希望可以將H4回傳C4~C10和D4~D10的加權平均結果
( C4*D4+C5*D5+C6*D6...+C10*D10 )/( C4+C5+...+C10)

然後拉下來，H5可以出現C11~C17與D11~D17的加權平均

我使用了sumproduct+offset，公式如下(前半部加總的部分):

=SUMPRODUCT(OFFSET(C$4,(ROW()-4)*7,0,7),OFFSET(D$4,(row()-4)*7,0,7))

但很奇怪的是，這個公式在Google Sheets上可以用，在Excel裡不行
如下圖:

http://imgur.com/XPX6ABk  (Excel)
http://imgur.com/ap6t0Ih  (Sheets)

我感覺好像是因為在excel裡有height的offset並不是回傳一個array?

想請問要在excel上使這個實現的話有甚麼替代方法呢?

感謝各位看完我的問題，希望有清楚表達到!

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 174.63.83.39
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1483743584.A.E7F.html
※ 編輯: ZOROCOOL (174.63.83.39), 01/07/2017 07:08:27
→ azteckcc: 把sumproduct用的到2個offset作成兩個名稱試試
→ azteckcc: 另一個解法，不必作成名稱，offset裡 (ROW()-4)*7 改成
→ azteckcc: SUM((ROW()-4)*7), 也就是加個sum()
感謝az大，我使用第二個方法有成功了!

不過還是好奇為什麼多個sum()包住(row()-4)*7公式就會成功呢?

我注意到原本公式，H4一步步解析的結果是
=SUMPRODUCT({29074},{2})

而用了你的方法後，就得到了想要的array
=SUMPRODUCT({29074;30504;27651;29859;26665;347;310},{2;2;2;2;1.9;1.1;1})

想了很久還是想不透差異在哪裡@@
不知道能否進一步說明! 感激感激
※ 編輯: ZOROCOOL (50.136.53.122), 01/07/2017 13:33:03
→ azteckcc: offset參數有用到row()，產出的動態range無法變成陣列
→ azteckcc: 所以要加個sum包起來或是改用rows()，改成
→ azteckcc: (ROWS($1:1)-1)*7 或 (ROWS($1:4)-4)*7 也可以
→ ZOROCOOL: 還是有點模糊，不過大概了解了，再次感謝!