[發問] 請問如何分攤費用到特定品項?

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

各位前輩好，

想請教前輩一個問題，細節如下：

說明：
(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) 附上檔案如右：

新增 Microsoft Excel Worksheet.zip (7.86 KB)

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

樓主熱帖

hcm19522

大學生 Rank: 4

帖子: 1363
主題: 4
精華: 0
積分: 1373
點名: 0
作業系統: WIN
軟體版本: 2007
閱讀權限: 50
性別: 男
註冊時間: 2015-9-11
最後登錄: 2024-4-26

熱烈參與勳章

2^#

發表於 2023-8-22 17:10 | 只看該作者

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

google"EXCEL迷" blog 或google網址:https://hcm19522.blogspot.com/

TOP

Andy2483

　暱稱: 厚臉皮的學生

大學生 Rank: 4

帖子: 1425
主題: 40
精華: 0
積分: 1449
點名: 0
作業系統: Windows 7
軟體版本: Excel 2010 & 2016
閱讀權限: 50
性別: 男
來自: 台灣
註冊時間: 2020-7-15
最後登錄: 2024-4-16

3^#

發表於 2023-9-23 14:17 | 只看該作者

回復 2# hcm19522

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

新增_20230923.zip (18.15 KB)

執行前:

執行結果:

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

用行動裝置瀏覽論壇學習很方便,謝謝論壇經營團隊
請大家一起上論壇來交流

TOP