Board logo

標題: [發問] 依項目名稱統計進銷總額之計算...? [打印本頁]
作者: cypd    時間: 2023-6-26 00:15     標題: 依項目名稱統計進銷總額之計算...?

依項目名稱統計進銷總額之計算...?
如下圖所示:
請問 Q3(進貨總額)及 R3(銷貨總額)
公式該如何 ??  (贈品數量不列入銷貨計算)

[attach]36643[/attach]

[attach]36644[/attach]
作者: 星空乂羽翼    時間: 2023-6-26 09:49

回復 1# cypd

依前輩需求及後學個人認為此表需求增加以下設定
1.增設中間品項及進銷貨價格表單
2.右側商品內容直接帶入中間表單名稱
3.右側表單皆使用SUMIF函數來設定，除了庫存數量是依進貨數減去銷貨的
4.左側表單名稱增加下拉式選單，來源是中間表單的名稱
5.左側表單增加格式化條件設定，若該列內容為進貨則顯示紅字，該列內容為銷貨則顯示紫字

[attach]36646[/attach]
作者: 星空乂羽翼    時間: 2023-6-26 10:01

回復 2# 星空乂羽翼

有關SUMIF函數說明如下圖
若有問題，再請前輩提出，感謝！

   [attach]36647[/attach]
作者: cypd    時間: 2023-6-26 12:36

回復 3# 星空乂羽翼

非常感謝 星空乂羽翼 熱心回覆
新增之 K L 欄進銷貨單價有些問題
同一產品之單價因數量或日期關係
其單價數據並非相同......
所以導致  R  S 兩欄進銷總額數據不正確...公式該如何修正所述  ??
作者: cypd    時間: 2023-6-26 12:43

回復 2# 星空乂羽翼

[attach]36648[/attach]

感謝 星空乂羽翼 熱心回覆
新增之 K L 欄進銷貨單價有些問題
同一產品之單價因數量或日期關係
其單價數據並非相同......
作者: 星空乂羽翼    時間: 2023-6-26 13:14

回復 5# cypd

所以整個作業模式應該是
左表示系統帶出或是人工key in的
然後需要轉成右表統計囉??
作者: cypd    時間: 2023-6-26 14:01

回復 6# 星空乂羽翼

是的  ^^
作者: 星空乂羽翼    時間: 2023-6-26 15:08

回復 5# cypd

後學延續SUMIF函數應用，提供一個笨方式
方式說明如下
增加兩列輔助欄，用來拆分進貨總額及銷貨總額
拆分後再依SUMIF函數來進行加總

    [attach]36649[/attach]

p.s若不可增加輔助欄，後學再想想別的方式。
作者: cypd    時間: 2023-6-26 23:50

回復 8# 星空乂羽翼

非常感謝 星空乂羽翼 熱心@回覆
依據所建議增加兩欄輔助(進貨總額及銷貨總額)
拆分後再依SUMIF函數陣列公式來進行加總  ;P :victory:
所提問題已獲得所需正確數據…感恩  ^^
作者: 星空乂羽翼    時間: 2023-6-27 11:23

回復 9# cypd

沒有輔助欄的函數如下
基本上是比對後抓資料
然後相乘
在相加

函數公式如下
  =SUM(IFERROR(((IF(MATCH($C$3:$C$32,$J$3:$J$10,0)=(MATCH($J3,$J$3:$J$10,0)),$D$3:$D$32,"")*(IF(MATCH($C$3:$C$32,$J$3:$J$10,0)=(MATCH($J3,$J$3:$J$10,0)),$F$3:$F$32,"")))),""))


[attach]36658[/attach]

後學依自己經驗，列出上述公式
一定有更好的公式或是可以簡化的寫法
再請各位大大指導，感謝。
作者: cypd    時間: 2023-6-27 20:19

回復 10# 星空乂羽翼

  非常感謝 星空乂羽翼 熱心@回覆
非常神奇的涵數公式
比對符合要件之後相乘相加...
希望筆數若多不影響運算...感恩解答  ^^
作者: Andy2483    時間: 2023-6-28 08:33

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

執行結果:
[attach]36659[/attach]


Sub TEST()
Dim Brr, V4&, V5&, Z, i&, T$, A&, Af&, B$, Bn&, D&, Df, Ra As Range
Set Z = CreateObject("Scripting.Dictionary")
Set Ra = Range([工作表1!H3], [工作表1!A65536].End(3)): Brr = Ra
For i = 1 To UBound(Brr)
   T = Brr(i, 3): V4 = Val(Brr(i, 4)): V5 = Val(Brr(i, 5))
   If V4 > 0 Then
      A = Z(T & "|進"): A = A + V4: Z(T & "|進") = A
      Af = Z(T & "|進額"): Af = Af + V4 * Val(Brr(i, 6))
      Z(T & "|進額") = Af
   End If
   Bn = Val(Brr(i, 8))
   If Bn > 0 Then
      B = Z(T & "|贈敘")
      Z(T & "|贈數") = Z(T & "|贈數") + Bn
      If B = "" Then
         B = Brr(i, 1) & "項_" & Brr(i, 2) & "_贈_" & Bn
         Else
         B = B & " ★" & Brr(i, 1) & "項_" & Brr(i, 2) & "_贈_" & Bn
      End If
      Z(T & "|贈敘") = B: B = ""
   End If
   If V5 > 0 Then
      D = Z(T & "|銷"): D = D + V5: Z(T & "|銷") = D
      Df = Z(T & "|銷額"): Df = Df + V5 * Val(Brr(i, 6))
      Z(T & "|銷額") = Df
   End If
Next
Set Ra = Range([工作表1!S2], [工作表1!M65536].End(3)): Brr = Ra
For i = 2 To UBound(Brr)
   T = Brr(i, 1)
   Brr(i, 2) = Z(T & "|進")
   Brr(i, 3) = Z(T & "|銷") + Z(T & "|贈數")
   Brr(i, 4) = Brr(i, 2) - Brr(i, 3)
   Brr(i, 5) = Z(T & "|進額")
   Brr(i, 6) = Z(T & "|銷額")
   Brr(i, 7) = Z(T & "|贈敘")
Next
Ra = Brr: [S2] = "備註"
Set Z = Nothing: Erase Brr: Set Ra = Nothing
End Sub
作者: hcm19522    時間: 2023-6-28 11:31

https://www.blogger.com/blog/pos ... /787676332473588008
作者: cypd    時間: 2023-6-28 12:15

回復 13# hcm19522

不好意思  hcm19522

無法登入呢  ?
作者: cypd    時間: 2023-6-28 13:50

回復 12# Andy2483

非常感謝前輩  Andy2483  熱心解答
關於此問有以下補充
※是否能增加盈虧金額一欄 ( T 欄)

另外 U 欄備註贈品數之註記
同一商品因不同銷貨日期所致
若筆數增多...U備註欄內容文字會隨之拖長問題  ?

[attach]36660[/attach]
作者: Andy2483    時間: 2023-6-28 14:44

回復 15# cypd


    謝謝前輩回復
後學學習方案如下,請前輩參考

執行結果:
[attach]36661[/attach]


Sub TEST()
Dim Brr, V4&, V5&, Z, i&, T$, A&, Af&, B$, Bn&, D&, Df, Ra As Range
Set Z = CreateObject("Scripting.Dictionary")
Set Ra = Range([工作表1!H3], [工作表1!A65536].End(3)): Brr = Ra
For i = 1 To UBound(Brr)
   T = Brr(i, 3): V4 = Val(Brr(i, 4)): V5 = Val(Brr(i, 5))
   If V4 > 0 Then
      A = Z(T & "|進"): A = A + V4: Z(T & "|進") = A
      Af = Z(T & "|進額"): Af = Af + V4 * Val(Brr(i, 6))
      Z(T & "|進額") = Af
   End If
   Bn = Val(Brr(i, 8))
   If Bn > 0 Then
      B = Z(T & "|贈敘")
      Z(T & "|贈數") = Z(T & "|贈數") + Bn
      If B = "" Then
         B = Brr(i, 1) & "項_" & Brr(i, 2) & "_贈_" & Bn
         Else
         B = B & " ★" & Brr(i, 1) & "項_" & Brr(i, 2) & "_贈_" & Bn
      End If
      Z(T & "|贈敘") = B: B = ""
   End If
   If V5 > 0 Then
      D = Z(T & "|銷"): D = D + V5: Z(T & "|銷") = D
      Df = Z(T & "|銷額"): Df = Df + V5 * Val(Brr(i, 6))
      Z(T & "|銷額") = Df
   End If
Next
Set Ra = Range([工作表1!V2], [工作表1!N65536].End(3)): Brr = Ra
For i = 2 To UBound(Brr)
   T = Brr(i, 1)
   Brr(i, 2) = Z(T & "|進")
   Brr(i, 3) = Z(T & "|銷") + Z(T & "|贈數")
   Brr(i, 4) = Brr(i, 2) - Brr(i, 3)
   Brr(i, 5) = Z(T & "|進額")
   Brr(i, 6) = Z(T & "|銷額")
   Brr(i, 7) = Brr(i, 6) - Brr(i, 5)
   Brr(i, 8) = "共贈出: " & Z(T & "|贈數")
   Brr(i, 9) = Z(T & "|贈敘")
Next
Ra = Brr: [U2] = "備註"
Set Z = Nothing: Erase Brr: Set Ra = Nothing
End Sub
作者: cypd    時間: 2023-6-28 21:15

回復 16# Andy2483

非常感謝前輩  Andy2483  熱心解答
若是不經執行巨集之動作
而是工作表內儲存格D(進貨)E(銷貨)F(單價)H(贈品)等相關欄位輸入數據或異動
觸發事件就會執行VBA…該程式如何修正  ??
Private Sub Worksheet_Change(ByVal Target As Range)
作者: Andy2483    時間: 2023-6-29 08:01

回復 17# cypd


    謝謝前輩再回復
以下觸發學習方案請前輩參考


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xR As Range
With Target
   Set xR = Intersect([A:H], ActiveSheet.UsedRange)
   If Not Intersect(.Cells, xR) Is Nothing Then Call TEST
End With
Set xR = Nothing
End Sub
作者: 星空乂羽翼    時間: 2023-6-29 10:56

建議M欄資料可用函數去抓出C欄名稱的資料
並刪除重複資料。

若版本為2021以上的版本
建議可以用UNIQUE函數

於M3儲存格輸入以下公式
=UNIQUE(C3:C32)
作者: cypd    時間: 2023-6-29 23:18

回復 18# Andy2483

非常感謝前輩  Andy2483  熱心提供解答
觸發學習方案相關公式經實測已正確執行順暢…感恩  ^^
作者: cypd    時間: 2023-6-29 23:22

回復 19# 星空乂羽翼

非常感謝前輩  星空乂羽翼  熱心提供解答
建議可使用 Office 2021 excel  UNIQUE函數
後學之Office 2013 版喔…感恩  ^^
作者: 星空乂羽翼    時間: 2023-6-30 08:39

回復 21# cypd

前輩Office是 2013的要做到資料貼上後就自動刪除重複資料比較難@@
好像無法單純用一個函數解決
後學已知的方式都滿複雜的
且有的只有一次性，沒辦法做到連續自動處理
可能要看其他前輩們有無更好的經驗分享了
作者: 准提部林    時間: 2023-6-30 14:46

回復 17# cypd


利用CHANGE去處理資料的匯算統計...這是初學VBA者的"惡習",
每KEY個儲存格就跑一輪資料表, 這有效益嗎? 何況若資料幾千上萬筆!!!
再說使用者若用粘貼一整區或刪除, CHANG就崩潰了!
而且, 再也用不了"復原"這功能!!!
作者: 准提部林    時間: 2023-6-30 14:52

回復 15# cypd


"盈虧金額"...不能這樣算吧!!!
銷售金額-成本金額[不是進貨總額] = 毛利
至于"成本金額"的算法又是另一回事!!!
作者: Andy2483    時間: 2023-6-30 15:11

回復 23# 准提部林


    請教前輩以下突發奇想的方式,可以有復原功能,但沒看過這樣的方法,請前輩指導有何利弊?
謝謝前輩

執行結果:
[attach]36666[/attach]


[attach]36667[/attach]
作者: 准提部林    時間: 2023-6-30 15:30

回復 25# Andy2483

沒用過這個方法//
FUNCTION應只能傳遞, 無法輸出~~
作者: Andy2483    時間: 2023-6-30 15:34

回復 26# 准提部林


    謝謝前輩,後學再試別的案例,這Evaluate 很奇妙
作者: cypd    時間: 2023-7-2 01:12

回復 25# Andy2483

感謝前輩  Andy2483  再度熱心解答
經實測 FUNCTION 及 Evaluate

只限當下輸入時之儲存格可復原一次之狀況...

[attach]36670[/attach]
作者: cypd    時間: 2023-7-2 01:31

回復 28# cypd

感謝前輩  Andy2483  再度熱心解答
經實測 FUNCTION 及 Evaluate

只限當下輸入時之儲存格可復原一次之狀況...
若是A2之儲存格進行保護隱藏之後...
只要儲存格有輸入數據會出現 #VALUE!

[attach]36671[/attach]
作者: Andy2483    時間: 2023-7-4 08:19

回復 29# cypd


    謝謝前輩幫忙測試回復
請前輩使用一般公式的方法
經過測試這方法不適合工作表有保護的情境,或有條件的保護工作表但目標格要設定可編輯
這方法是後學學不會複雜公式偷懶所想出來的天馬行空方案,待後學有其他心得再分享給大家
作者: cypd    時間: 2023-7-4 12:04

回復 30# Andy2483

感謝前輩  Andy2483  再度熱心回覆
不好意思
因為 G 欄進銷總額以下有簡單的 進  銷  單價  試算的公式
避免誤刪...所以該 G 欄會有保護工作表的情況呢
作者: Andy2483    時間: 2023-7-5 08:11

回復 31# cypd


    謝謝前輩回復
如果前輩想用後學的方法,後學建議以下方法
1.將$A:$F,$H:$W設為 允許使用者編輯範圍
2.再設定保護工作表
作者: cypd    時間: 2023-7-5 23:43

回復 32# Andy2483

感謝前輩  Andy2483  再度熱心回覆
依建議參考以下方法
1.將$A:$F,$H:$W設為 允許使用者編輯範圍
2.再設定保護工作表
作者: hcm19522    時間: 2023-8-7 11:24

https://hcm19522.blogspot.com/2023/08/11626.html
作者: cypd    時間: 2023-8-11 05:23

回復 34# hcm19522

非常感謝  hcm 19522 前備回覆
提供  sumif+sumifs...
及sumproduct相關函數

前備在提示文字關於
銷貨總額  不含贈品(R4=14*23=322...芝麻)

若是以黑豆為例  R3=80*??(因為兩個單價不同...若是三個以上呢？)



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