2.要怎麼用excel公式算出編號2的a+b+c
2.要怎麼用excel公式算出編號3的X+Y+Z平均值
更新1:
4.要怎麼用excel公式算出編號4、5的a+b+c
更新2:
打錯2,4題是a+c+e
求excel公式,用excel算加總?
回答 (2)
2017-03-30 2:25 pm
2017-03-30 9:09 am
1.要怎麼用excel公式算出編號1的a加總,
=SUMPRODUCT(($A$2:$A$100=1)*$B$2:$B$100)
2.要怎麼用excel公式算出編號2的a+c+e
=SUMPRODUCT(($A$2:$A$100=2)*(($B$2:$B$100)+($D$2:$D$100)+($F$2:$F$100)))
3.要怎麼用excel公式算出編號3的X+Y+Z平均值
=SUMPRODUCT(($A$2:$A$100=3)*($C$2:$C$100+$E$2:$E$100+$G$2:$G$100))/COUNTIF(A2:A100,3)
4.要怎麼用excel公式算出編號4、5的a+c+e
=SUMPRODUCT(($A$2:$A$100={4,5})*($B$2:$B$100+$D$2:$D$100+$F$2:$F$100))
=SUMPRODUCT(($A$2:$A$100=1)*$B$2:$B$100)
2.要怎麼用excel公式算出編號2的a+c+e
=SUMPRODUCT(($A$2:$A$100=2)*(($B$2:$B$100)+($D$2:$D$100)+($F$2:$F$100)))
3.要怎麼用excel公式算出編號3的X+Y+Z平均值
=SUMPRODUCT(($A$2:$A$100=3)*($C$2:$C$100+$E$2:$E$100+$G$2:$G$100))/COUNTIF(A2:A100,3)
4.要怎麼用excel公式算出編號4、5的a+c+e
=SUMPRODUCT(($A$2:$A$100={4,5})*($B$2:$B$100+$D$2:$D$100+$F$2:$F$100))
收錄日期: 2021-04-28 17:29:30
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20170329140905AAKDxxr