標題: [發問] EXCEL簡化公式問題 [打印本頁]

---

作者: msmplay    時間: 2020-8-26 09:12     標題: EXCEL簡化公式問題

[attach]32468[/attach]
請問該如何簡化以下四個組合起來的公式，2個INDEX+MATCH、2個SUMPRODUCT，四個公式只有$B3跟$D3這兩個位置不同，其他抓的位置都一樣。主要抓$B3對應的數字跟$D3對應的金額相加總在一起。求解~~~


[attach]32469[/attach]

---

作者: 准提部林    時間: 2020-8-26 13:36

附檔用舊版office開不了~~
型號7,型號151__
是指[型號7 and 型號151]
[型號7 and 型號151]
[型號7 or 型號151]
還是[型號7 ~ 型號151],

---

作者: msmplay    時間: 2020-8-26 14:25

回復 2# 准提部林


   准大~~~是指 [型號7 and 型號151] 喔!謝謝~~~   然後我另外存了一個2003版本，你看能不能開喔~~


[attach]32475[/attach]

---

作者: 准提部林    時間: 2020-8-26 16:51

回復 3# msmplay


直接另存.XLS是沒用的, 因格式太多, 還是無法開啟,
我是OFFICE2000

---

作者: msmplay    時間: 2020-8-26 16:59

回復 4# 准提部林


   那如果我貼公式，然後截圖給准大看看咧~~如果真不行就放棄 噗
儲存格E3公式
=IFERROR(INDEX(TEST!$D$3:$BZ$1000,MATCH($B3,TEST!$B$3:$B$1000,),MATCH($E1,TEST!$C$1:$BZ$1,)),0)+IFERROR(INDEX(TEST!$D$3:$BZ$1000,MATCH($D3,TEST!$B$3:$B$1000,),MATCH($E1,TEST!$C$1:$BZ$1,)),0)+SUMPRODUCT((1-ISNA(MATCH(手工單!$C$2:$FN$2,$B3,)))*(手工單!$A$3:$A$1000=$E1)*(手工單!$B$3:$B$1000=$E2)*(手工單!$C$3:$FN$1000))+SUMPRODUCT((1-ISNA(MATCH(手工單!$C$2:$FN$2,$D3,)))*(手工單!$A$3:$A$1000=$E1)*(手工單!$B$3:$B$1000=$E2)*(手工單!$C$3:$FN$1000))


[attach]32476[/attach][attach]32477[/attach]

---

作者: 准提部林    時間: 2020-8-26 17:17

回復 5# msmplay
先試 TEST 的金額是否正確:
=SUMPRODUCT(SUMIF(TEST!B3:B1000,B3:D3,OFFSET(C3:C1000,,MATCH(E1,TEST!C1:BZ1,)))

---

作者: msmplay    時間: 2020-8-26 17:22

回復 6# 准提部林


   准大~~~你太強了，TEST的金額正確喔，但中間的公式TEST!C3:C1000少了TEST!，我加上去就正確了
=SUMPRODUCT(SUMIF(TEST!B3:B1000,B3:D3,OFFSET(TEST!C3:C1000,,MATCH(E1,TEST!C1:BZ1,))))

---

作者: 准提部林    時間: 2020-8-26 17:26

回復 7# msmplay

手工單等下班回家再看~~

---

作者: msmplay    時間: 2020-8-26 17:28

回復 8# 准提部林


   哈~~~~~辛苦你了ㄟ

---

作者: 准提部林    時間: 2020-8-26 19:26

本帖最後由 准提部林 於 2020-8-26 19:27 編輯

TEST表改成:
=SUMPRODUCT(SUMIF(TEST!B3:B1000,B3:D3,index(TEST!C3:BZ1000,,MATCH(E1,TEST!C1:BZ1,)+(E2="金額"))))
__OFFSET 改成 INDEX 較不干擾檔案

手工單:你原來公式是對的
=SUMPRODUCT((手工單!A3:A1000=E1)*(手工單!B3:B1000=E2)*(1-ISNA(MATCH(手工單!C2:$FN2,B3:D3,)))*手工單!C3:FN1000)
或
=SUMPRODUCT((手工單!A3:A1000=E1)*(手工單!B3:B1000=E2)*(COUNTIF(B3:D3,手工單!C2:$FN2)>0)*手工單!C3:FN1000)
__COUNTIF較慢, 用MATCH即可

__在E2可選擇[金額]或[數量]而變換計算????

---

作者: msmplay    時間: 2020-8-27 09:13

回復 10# 准提部林


   准大~再次感謝幫忙喔！公式無誤，正解耶~~~~~哈

---

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