是否可使用公式自動做一簡單核消明細表???

p6703

book3.rar (2.02 KB)

下載次數: 26

2011-3-9 09:17

資料會有訂單,出貨明細，是否可使用公式自動套取其未結數據於d欄位（附件中），謝謝...!!!

ANGELA

d2=C2-SUMPRODUCT((出貨明細!$A$2:$A$10=A2)*(出貨明細!$B$2:$B$10=B2)*(出貨明細!$C$2:$C$10))

p6703

感謝ANGELA的回覆，但小弟忽略提到一點，就是同訂單號及料號有可能會有重覆的（就是同一訂單有同時該料號下二筆以上==>因應二訂單的需求日不同)，以上公式即無法套取出正確數值

ANGELA

回復 3# p6703


    請依樣劃葫蘆.還是用SUMPRODUCT取代c2

p6703

小弟不材，未能明白ANGELA的意思，是否可明確告知，謝謝...!!!

ANGELA

=SUMPRODUCT((A2:A12=A2)*(B2:B12=B2)*C2:C12)-SUMPRODUCT((出貨明細!$A$2:$A$10=A2)*(出貨明細!$B$2:$B$10=B2)*(出貨明細!$C$2:$C$10))
不是不直接告訢你答案,是要你試著去了解公式的作用原理,否則下次遇到類似問題,你還得提問.
=SUMPRODUCT(($A$2:$A$12=A2)*($B$2:$B$12=B2)*$C$2:$C$12)-SUMPRODUCT((出貨明細!A$2:A$10=A2)*(出貨明細!B$2:B$10=B2)*(出貨明細!C$2:C$10))
把簵圍改成絕對參照,就可往下copy

p6703

感謝ANGELA解說，所以就是捉取二次的數量相減，即可得出需要數據

Andy2483

謝謝論壇,謝謝各位前輩
後學藉此帖練習陣列與字典,學習方案如下,請各位前輩指教

資料表:(出貨明細)

下載 (36.58 KB)

2023-5-16 09:59

結果表執行前:

下載 (38.36 KB)

2023-5-16 09:59

執行結果:

下載 (47.62 KB)

2023-5-16 10:00

Option Explicit
Sub TEST()
Dim Brr, Crr, Y, i&, T$, Sh(2)
'↑宣告變數
Set Y = CreateObject("Scripting.Dictionary")
'↑令Y變數是 字典
Set Sh(1) = Sheets("未結訂單"): Set Sh(2) = Sheets("出貨明細")
'↑令Sh這一維陣列1~2索引號值各以工作表帶入
Brr = Sh(1).[A1].CurrentRegion: Crr = Sh(2).[A1].CurrentRegion
'↑令Brr,Crr變數各是二維陣列,各以儲存格值帶入陣列中
For i = 2 To UBound(Crr)
   T = Crr(i, 1) & "|" & Crr(i, 2): Y(T) = Y(T) + Crr(i, 3)
Next
'↑設迴圈組合(訂單單號|料號)新字串當key,item是數量累加,納入Y字典裡
For i = 2 To UBound(Brr)
   T = Brr(i, 1) & "|" & Brr(i, 2): Brr(i - 1, 1) = Brr(i, 3) - Y(T)
Next
'↑設迴圈將(訂單單號|料號)組合成的新字串查Y字典得到item值,
'當被減數做扣除,寫入Brr陣列的第1欄
Sh(1).[D2].Resize(UBound(Brr) - 1, 1) = Brr
'↑將Brr陣列值寫入"未結訂單"表結果欄中
Application.Goto Sh(1).[D2].Resize(UBound(Brr) - 1, 1)
'↑令儲存格游標跳到 "未結訂單"表結果欄中
Set Y = Nothing: Erase Brr, Crr, Sh
'↑令釋放變數
End Sub