作者ZOROCOOL (DH)
看板Office
標題[算表] 利用sumproduct和offset計算加權平均
時間Sat Jan 7 06:59:41 2017
軟體: 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://webptt.com/m.aspx?n=bbs/Office/M.1483743584.A.E7F.html
※ 編輯: ZOROCOOL (174.63.83.39), 01/07/2017 07:08:27
1F：→ azteckcc: 把sumproduct用的到2個offset作成兩個名稱試試 01/07 09:56
2F：→ azteckcc: 另一個解法，不必作成名稱，offset裡 (ROW()-4)*7 改成 01/07 10:27
3F：→ azteckcc: SUM((ROW()-4)*7), 也就是加個sum() 01/07 10:28
感謝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
4F：→ azteckcc: offset參數有用到row()，產出的動態range無法變成陣列 01/07 14:03
5F：→ azteckcc: 所以要加個sum包起來或是改用rows()，改成 01/07 14:05
6F：→ azteckcc: (ROWS($1:1)-1)*7 或 (ROWS($1:4)-4)*7 也可以 01/07 14:07
7F：→ ZOROCOOL: 還是有點模糊，不過大概了解了，再次感謝! 01/08 02:39