標題: [發問] 請問如何分攤費用到特定品項? [打印本頁]

---

作者: gaishutsusuru    時間: 2023-8-17 21:27     標題: 請問如何分攤費用到特定品項?

本帖最後由 gaishutsusuru 於 2023-8-17 21:30 編輯

各位前輩好，

想請教前輩一個問題，細節如下：
[attach]36772[/attach]
說明：
(1) 欄位：A~D欄、K欄之後是資料。E欄塗黃色處是希望能做出來的公式。F~I欄(淺灰色)僅是為了說明而做的欄位，實際上不會有F~I欄。
(2) 文具用品的費用是由3個細項所組成：研發費、運費、雜費
(2-1) 文具用品的研發費：不需由A1、A3歸屬。因此金額$10,000依A2、A4的售出金額占比去攤給A2、A4。結果可參照F欄的金額。
(2-2) 文具用品的運費：不需由A2歸屬。因此金額$6,000依A1、A3、A4的售出金額占比去攤給A1、A3、A4。結果可參照G欄的金額。
(2-3) 文具用品的雜費：可歸屬A1~A4。因此金額$12,000依A1~A4的售出金額占比去攤給A1~A4。結果可參照H欄的金額。
(3) 教室設備的費用是由3個細項所組成：水電費、運費、雜費
(3-1) 教室設備的水電費：不需由B1、B2歸屬。因此金額$20,000依B3的售出金額占比去攤給B3。結果可參照I欄的金額。
(3-2) 教室設備的運費：不需由B3歸屬。因此金額$5,000依B1、B2的售出金額占比去攤給B1、B2。結果可參照G欄的金額。
(3-3) 教室設備的雜費：可歸屬B1~B3。因此金額$2,000依B1~B3的售出金額占比去攤給B1~B3。結果可參照H欄的金額。
(4)
(4-1) 文具用品的代號是A1~A4，也因此N欄之後的「不需歸屬的產品代號→」只要K欄的費用類別是文具用品，則對應的N欄之後填的一定會是A1~A4之中的代號。
(4-2) 教室設備的代號是B1~B3，也因此N欄之後的「不需歸屬的產品代號→」只要K欄的費用類別是教室設備，則對應的N欄之後填的一定會是B1~B3之中的代號。
(5) 實際上的資料會有更多的產品類別、產品代號、產品名稱 ，在此僅舉出簡化例子。
(6) 附上檔案如右：[attach]36773[/attach]

以上是這個例子的說明，整個的概念簡單一句話就是「把不需歸屬的產品代號排除掉，去由其他產品依售出金額占比分攤該筆費用」。希望前輩能抽空協助幫忙設計E欄的公式，謝謝前輩。

---

作者: hcm19522    時間: 2023-8-22 17:10

https://hcm19522.blogspot.com/2023/08/11695.html

---

作者: Andy2483    時間: 2023-9-23 14:17

回復 2# hcm19522


    謝謝前輩發表此主題與範例
後學藉此帖練習陣列與字典,學習方案如下,請前輩參考
[attach]36847[/attach]

執行前:
[attach]36848[/attach]

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


Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, Z, Q, R&, i&, j%, T$, 費用項%
If [E2] <> "" Then Range([E2], [E65536].End(3)).ClearContents: Exit Sub
Range([E2], [E65536].End(3)(2)).ClearContents
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([N1], [K65536].End(3))
Crr = Range([E1], [A65536].End(3)): R = UBound(Crr)
ReDim Arr(1 To R, 1 To 10)
For i = 2 To UBound(Brr)
   If Z(Brr(i, 2)) = "" Then 費用項 = 費用項 + 1: Z(Brr(i, 2)) = 0: Arr(1, 費用項) = Brr(i, 2)
   T = Brr(i, 1) & "|" & Brr(i, 2)
   Z(T) = Brr(i, 3)
   For Each Q In Split(Brr(i, 4), ",")
      If Q <> "" Then Z(T & "|" & Q) = Brr(i, 3)
   Next
Next
For i = 2 To R
   For j = 1 To 費用項
      T = Crr(i, 1) & "|" & Arr(1, j)
      Arr(i, j) = Z(T) - Z(T & "|" & Crr(i, 2))
      If Arr(i, j) <> 0 Then Z(T & "/t") = Z(T & "/t") + Crr(i, 4)
   Next
Next
For i = 2 To R
   For j = 1 To 費用項
      T = Crr(i, 1) & "|" & Arr(1, j)
      If Z(T & "/t") > 0 Then Crr(i - 1, 1) = Val(Crr(i - 1, 1)) + Arr(i, j) * (Crr(i, 4) / Z(T & "/t"))
   Next
Next
[E2].Resize(R - 1, 1) = Crr
Set Z = Nothing: Erase Arr, Brr, Crr
End Sub

---

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