公式縮減的問題

vvcvc

常常有以下2種情況，都需同樣公式寫數次再運算
請問是否有方法可以縮減公式
1. SUM(C:C)-SUM(D:D)+SUM(E:E)
2. SUMPRODUCT(A:A="P")+SUMPRODUCT(A:A="S")

以上常常出現在公式中的一小段
ex. .....SUMPRODUCT(A:A="P")+.....SUMPRODUCT(A:A="S")   /點點的部份表示公式相同
為了這一小段的差異常常公式的長度由3行變5、6行
後續維護也很複雜
第2點本想用OR(SUMPRODUCT(A:A="P"),SUMPRODUCT(A:A="S"))
但使用OR之後就將陣列變成非陣列的1個結果

請問是否有什麼方法可以簡化公式

hcm19522

Q1 {=SUM(MMULT(IF(C1:E999<>"",C1:E999,),{1;-1;1}))
Q2 {=COUNT(0/(FIND(A:A,0&"ABC")-1))
Q2 以A欄=A . B .C為例  ;Q1用C:E 時間較久

ML089

回復 1# vvcvc

2.
=SUMPRODUCT(A:A="P")+....+SUMPRODUCT(A:A="S")

可以修改為

=SUMPRODUCT(COUNTIF(A:A,{"P",...,"S"}))

vvcvc

mmlut看了EXCEL說明也複製範例實做
還是搞不懂他在算什麼? 算出來的結果有什麼用意?
可以再幫忙說明一下excel計算過程嗎?

vvcvc

如果狀況是多重條件
=SUMPRODUCT((A:A="P")*(B:B="V"))+....+SUMPRODUCT((A:A="S")**(B:B="V"))

修改為
=SUMPRODUCT((COUNTIF(A:A,{"P",...,"S"}))*(B:B="V"))
則一直出現錯誤，無法輸入公式
可否幫忙看看公式那裡錯誤? 謝謝

如果用的是sumifs、countifs也可以如法炮製嗎?

ML089

回復 5# vvcvc

如果狀況是多重條件
=SUMPRODUCT((A:A="P")*(B:B="V"))+....+SUMPRODUCT((A:A="S")*(B:B="V"))

修改為
=SUMPRODUCT(COUNTIFS(A:A,{"P", ... , "S"}, B:B,"V"))

vvcvc

試了好久都顯示錯誤
另外設計簡單的表單試才成功
countifs也可以改成sumifs做加總

(要再來研究原本要改的公式到底那裡有問題)

vvcvc

找到原因是SUMIF、SUMIFS、COUNTIF、COUNTIFS公式的寫法不能裡面還有公式
如SUMIF(B:B,LEFT(A1,2),LEFT(C:C,2))
會因式子裡包含了LEFT(C:C,2)這個公式而錯誤

vvcvc

1. =SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="_")*(Sheet2!$P$2:$P$1488))+SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="T")*(Sheet2!$P$2:$P$1488))

複製代碼

使用以下簡化成功

1. =SUMPRODUCT(SUMIFS(Sheet2!$P$2:$P$1488,Sheet2!$A$2:$A$1488,E$3&"*",Sheet2!$T$2:$T$1488,DAY($B$2+1),Sheet2!$B$2:$B$1488,RIGHT(E$2,3),Sheet2!$S$2:$S$1488,{"*)_*","*)T*"}))

複製代碼

但下面式子Sheet2!$T$2:$T$1488<=DAY($B$2+1)該怎麼修改才能簡化?

1. =SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488<=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="_")*(Sheet2!$P$2:$P$1488))+SUMPRODUCT((LEFT(Sheet2!$A$2:$A$1488,2)=LEFT(E$3,2))*(Sheet2!$T$2:$T$1488<=DAY($B$2+1))*(Sheet2!$B$2:$B$1488=RIGHT(E$2,3))*(LEFT(RIGHT(Sheet2!$S$2:$S$1488,3),1)="T")*(Sheet2!$P$2:$P$1488))

複製代碼

ML089

=SUMPRODUCT(SUMIFS(
Sheet2!$P$2:$P$1488,
Sheet2!$A$2:$A$1488,E$3&"*",
Sheet2!$T$2:$T$1488,"<="&DAY($B$2+1),
Sheet2!$B$2:$B$1488,RIGHT(E$2,3),
Sheet2!$S$2:$S$1488,{"*_??","*T??"}))