麻辣家族討論版版 - Powered by Discuz! Board

標題: 公式縮減的問題 [打印本頁]

作者: vvcvc 時間: 2017-5-3 14:03 標題: 公式縮減的問題

常常有以下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 時間: 2017-5-8 16:24

本帖最後由 hcm19522 於 2017-5-8 16:27 編輯

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 時間: 2017-5-8 20:15

回復 1# vvcvc

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

可以修改為

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

作者: vvcvc 時間: 2017-5-9 09:42

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

作者: vvcvc 時間: 2017-5-9 10:08

本帖最後由 vvcvc 於 2017-5-9 10:15 編輯

如果狀況是多重條件
=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 時間: 2017-5-9 13:37

回復 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 時間: 2017-5-9 17:00

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

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

作者: vvcvc 時間: 2017-5-11 14:34

本帖最後由 vvcvc 於 2017-5-11 14:39 編輯

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

作者: vvcvc 時間: 2017-5-11 14:53

本帖最後由 vvcvc 於 2017-5-11 14:57 編輯

=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))

複製代碼

使用以下簡化成功

=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)該怎麼修改才能簡化?

=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 時間: 2017-5-11 21:38

=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??"}))

作者: vvcvc 時間: 2017-5-12 10:53

以為加上""會變文字一直沒嘗試><
謝謝ML089的協助

作者: vvcvc 時間: 2017-5-22 09:11

本帖最後由 vvcvc 於 2017-5-22 09:26 編輯

又想到如果狀況再變成如下
=SUMPRODUCT((A:A="P")*(B:B="V"))+SUMPRODUCT((A:A="S")*(B:B="V"))+SUMPRODUCT((A:A="P")*(B:B="U"))+SUMPRODUCT((A:A="S")*(B:B="U"))

理論上是2*2個結果加總
但修改為下式時只有2個結果
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"}, B:B,{"V","U"}))
這樣子有變法寫成1個式子嗎?

作者: ML089 時間: 2017-5-22 23:00

回復 12# vvcvc

=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,{"V";"U"}))

作者: vvcvc 時間: 2017-5-23 10:29

我要用以下的結果才會對欸
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"V"))+SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"U"))

=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,{"V";"U"}))
的結果跟下式結果一樣
=SUMPRODUCT(COUNTIFS(A:A,{"P","S"},B:B,"V"))

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)