ERP(SAP)中Allocation的迭代展开

最新推荐文章于 2023-12-11 21:15:15 发布

weixin_34100227

最新推荐文章于 2023-12-11 21:15:15 发布

阅读量376

点赞数

文章标签： python

原文链接：https://my.oschina.net/tedzheng/blog/735078

版权

2019独角兽企业重金招聘Python工程师标准>>>

一般SAP分配表下载下来的数据是以下结构,这里我手工添加了Dir_BU字段不判断是否是底层分配表Y->True,X->False

FROM	TO	VALUE	Dir_BU
6100010100	6100019311	100	X
6100010200	6100019312	100	X
6100010300	6100019320	100	X
6100010400	6100019370	100	X
6100724320	320	100	Y
6100724370	333	3.66	Y
6100724370	370	75.41	Y
6100724370	380	20.93	Y
6100726311	311	82.34	Y
6100726311	312	8.08	Y
6100726311	370	9.19	Y
6100726311	380	0.39	Y
6100740311	311	100	Y
6100811000	311	27.53	Y
6100811000	312	22.76	Y
6100811000	320	32.54	Y
6100811000	333	0.29	Y
6100811000	360	6.88	Y
6100811000	370	5.33	Y
6100811000	380	4.38	Y
6100811000	848	0.29	Y
6100823000	6100901000	100	X
6100825000	6100010100	5	X
6100825000	6100010200	15	X
6100825000	6100010300	17	X
6100825000	6100811000	4	X
6100825000	6100851000	1	X
6100825000	6100886000	2	X
6100825000	6100886200	1	X
6100825000	6100887000	10	X
6100825000	6100891000	5	X
6100825000	6100901000	4	X
6100825000	6100912400	5	X
6100825000	6100921000	3	X
6100825000	6100941300	7	X
6100825000	6100941800	4	X
6100825000	6100950100	12	X
6100825000	6100962000	5	X
6100851000	311	27.53	Y
6100851000	312	22.76	Y
6100851000	320	32.54	Y
6100851000	333	0.29	Y
6100851000	360	6.88	Y
6100851000	370	5.33	Y
6100851000	380	4.38	Y
6100851000	848	0.29	Y
6100876000	311	27.53	Y
6100876000	312	22.76	Y
6100876000	320	32.54	Y
6100876000	333	0.29	Y
6100876000	360	6.88	Y
6100876000	370	5.33	Y
6100876000	380	4.38	Y
6100876000	848	0.29	Y
6100886000	311	28.86	Y
6100886000	312	33.66	Y
6100886000	320	24.6	Y
6100886000	360	0.86	Y
6100886000	370	7.07	Y
6100886000	380	4.45	Y
6100886000	848	0.5	Y
6100886200	6100010100	32	X
6100886200	6100010200	38	X
6100886200	6100010300	30	X
6100887000	311	28.86	Y
6100887000	312	33.66	Y
6100887000	320	24.6	Y
6100887000	360	0.86	Y
6100887000	370	7.07	Y
6100887000	380	4.45	Y
6100887000	848	0.5	Y
6100891000	311	32.86	Y
6100891000	312	26.02	Y
6100891000	320	28.77	Y
6100891000	360	8.8	Y
6100891000	370	2.36	Y
6100891000	380	1.07	Y
6100891000	848	0.12	Y
6100901000	6100010100	20	X
6100901000	6100010200	30	X
6100901000	6100010300	30	X
6100901000	6100010400	20	X
6100901500	6100811000	12	X
6100901500	6100851000	7	X
6100901500	6100886200	5	X
6100901500	6100887000	19	X
6100901500	6100891000	24	X
6100901500	6100901000	19	X
6100901500	6100921000	14	X
6100912400	6100010100	10	X
6100912400	6100010200	35	X
6100912400	6100010300	40	X
6100912400	6100010400	15	X
6100921000	6100010100	26	X
6100921000	6100010200	25	X
6100921000	6100010300	25	X
6100921000	6100010400	24	X
6100941300	6100010100	25	X
6100941300	6100010200	25	X
6100941300	6100010300	20	X
6100941300	6100010400	30	X
6100941800	6100010100	20	X
6100941800	6100010200	20	X
6100941800	6100010300	10	X
6100941800	6100010400	50	X
6100950100	6100010100	20	X
6100950100	6100010200	30	X
6100950100	6100010300	30	X
6100950100	6100010400	20	X
6100962000	6100010100	15	X
6100962000	6100010200	65	X
6100962000	6100010300	5	X
6100962000	6100010400	15	X
6100019311	311	100	Y
6100019312	312	100	Y
6100019320	320	100	Y
6100019370	370	100	Y

我们想要得到的结果是以下结式，方便观察每个BU分摊的情况

COST_CENTER	311	312	320	333	355	360	370	380	848
6100010100	100.00	-	-	-	-	-	-	-	-
6100010200	-	100.00	-	-	-	-	-	-	-
6100010300	-	-	100.00	-	-	-	-	-	-
6100010400	-	-	-	-	-	-	100.00	-	-
6100724320	-	-	100.00	-	-	-	-	-	-
6100724370	-	-	-	3.66	-	-	75.41	20.93	-
6100726311	82.34	8.08	-	-	-	-	9.19	0.39	-
6100740311	100.00	-	-	-	-	-	-	-	-
6100811000	27.53	22.76	32.54	0.29	-	6.88	5.33	4.38	0.29
6100823000	20.00	30.00	30.00	-	-	-	20.00	-	-
6100825000	19.58	34.96	32.92	0.01	-	0.89	10.75	0.81	0.08
6100851000	27.53	22.76	32.54	0.29	-	6.88	5.33	4.38	0.29
6100876000	27.53	22.76	32.54	0.29	-	6.88	5.33	4.38	0.29
6100886000	28.86	33.66	24.60	-	-	0.86	7.07	4.45	0.50
6100886200	32.00	38.00	30.00	-	-	-	-	-	-
6100887000	28.86	33.66	24.60	-	-	0.86	7.07	4.45	0.50
6100891000	32.86	26.02	28.77	-	-	8.80	2.36	1.07	0.12
6100901000	20.00	30.00	30.00	-	-	-	20.00	-	-
6100901500	27.64	28.06	28.46	0.06	-	3.58	10.08	1.93	0.18
6100912400	10.00	35.00	40.00	-	-	-	15.00	-	-
6100921000	26.00	25.00	25.00	-	-	-	24.00	-	-
6100941300	25.00	25.00	20.00	-	-	-	30.00	-	-
6100941800	20.00	20.00	10.00	-	-	-	50.00	-	-
6100950100	20.00	30.00	30.00	-	-	-	20.00	-	-
6100962000	15.00	65.00	5.00	-	-	-	15.00	-	-
6100019311	100.00	-	-	-	-	-	-	-	-
6100019312	-	100.00	-	-	-	-	-	-	-
6100019320	-	-	100.00	-	-	-	-	-	-
6100019370	-	-	-	-	-	-	100.00	-	-

以下是程序代码，

Option Explicit

Dim arrDB                                'Original Data
Public Dic As Object
'主程序
Sub Get_Mapping()
Dim t
Dim BU_List
Dim CC_List
Dim Output
Dim i%, j%
Dim CC$, BU$
'------------------------------------------------------------
t = Timer
arrDB = Sheets("ALLOCATION").UsedRange
CC_List = Unique_CC(arrDB)                '获取CC列表,begin with 0
BU_List = Get_BU_List                     '获取BU列表,begin with 1
ReDim Output(1 To UBound(CC_List) + 2, 1 To UBound(BU_List) + 1)
'------------------------------------------------------------
'标题栏读入
Output(1, 1) = "COST_CENTER"
For i = 0 To UBound(CC_List)
    Output(i + 2, 1) = CC_List(i)       '列
Next i
For j = 1 To UBound(BU_List)
    Output(1, j + 1) = BU_List(j)       '行
Next j
'------------------------------------------------------------
For i = 2 To UBound(Output)
    CC = Output(i, 1)                     'COST CENTER
    For j = 2 To UBound(Output, 2)
        BU = Output(1, j)                 'BU
        Output(i, j) = Get_Value(arrDB, CC, BU) '根据CC+BU获取相应值
    Next j
Next i
'============================================================
With Sheets("Mapping")
    .[A1].CurrentRegion.ClearContents
    .[A1].Resize(UBound(Output), UBound(Output, 2)) = Output
End With
'=============================================================
Erase arrDB
Erase Output
MsgBox (Timer - t)
End Sub

'获取BU=>VALUE
Function Get_Value(arr, ByVal CC, ByVal BU) As Double
Dim Val As Double
If Dic(CC) = "Y" Then
   Val = Get_BU_Value(arr, CC, BU)
ElseIf Dic(CC) = "X" Then
   Val = Get_CC_Value(arr, CC, BU)
Else
    Val = 0
End If
Get_Value = Val             '返回值
End Function

'直接根据CC获取BU值
Function Get_BU_Value(arr, ByVal CC, ByVal BU) As Double
Dim n%
Dim Val As Double
Val = 0                         '初始化
For n = 2 To UBound(arr)
    If arr(n, 1) = CC And arr(n, 2) = BU Then
       Val = arr(n, 3)          '获取值
       Exit For
    End If
Next n
Get_BU_Value = Val              '返回值
End Function

'获取CC的权重值
Function Get_CC_Value(arr, ByVal CC, ByVal BU) As Double
Dim n%
Dim Val As Double
Dim CC_From$, CC_To$
Dim Weight As Double
Val = 0                         '初始化
For n = 2 To UBound(arr)
    CC_From = arr(n, 1)
    CC_To = arr(n, 2)
    Weight = arr(n, 3)          '权重
    '因为不同的CC可能会因为CC_TO的不同而产生不同层级迭代，这里不设出口
    '每次都进行完全循环来确保TO的目标BU没有缺失
    If CC_From = CC Then        '目标CC
        If Dic(CC_To) = "Y" Then        'CC_To
            Get_CC_Value = Get_CC_Value + Weight * Get_BU_Value(arr, CC_To, BU) / 100
        Else
            Get_CC_Value = Get_CC_Value + Weight * Get_CC_Value(arr, CC_To, BU) / 100
        End If
    End If
Next n
End Function


'建立字典，储存每个COST CENTER的内存地址
Function Unique_CC(arr)
Dim i%
Dim SK$
Dim m%
Set Dic = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(arr)
    SK = arr(i, 1)                  'From
    If Not Dic.Exists(SK) Then
        Dic(SK) = arr(i, 4)         '判断是直接获取VALUE
    End If
Next i
Unique_CC = Dic.keys               '返回值
End Function

'建立BU_Arry列表
Function Get_BU_List()
Dim arr(1 To 9) As String
    arr(1) = "311"
    arr(2) = "312"
    arr(3) = "320"
    arr(4) = "333"
    arr(5) = "355"
    arr(6) = "360"
    arr(7) = "370"
    arr(8) = "380"
    arr(9) = "848"
Get_BU_List = arr                   '返回值
End Function

最后可以用googleVis或D3包在R中创建一个sankey来观察数据的流向