作者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/cn.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