CO-Indirect cost allocation

本文介绍了一个详细的间接费用分配流程,包括创建成本要素、活动类型、输入活动价格等六个步骤,帮助理解和实施SAP系统的间接费用分配。

Step-1: Create a allocation cost element
Path: Accounting>>Controlling>>Cost Element Accounting>>Master Data>>Cost Element>>Individual Processing>> Create Secondary
T code: KA06
    Enter your controlling area in the pop up screen for ‘Set Controlling Area’
    Cost element <enter cost element unique number>
    Valid from <first day of the current year>
    Click on Master Data button
    Name < Enter name of the cost element>
    Description <enter description>
    Cost element category <43>
    <Save>

 

STEP-2: Create activity type
Path: Accounting>>Controlling>> Cost Center Accounting>> Master Data>> Activity Type>> Individual Processing>> T code:KL01 - Create
    T code: KL01   
    Activity Type <enter unique activity ID>
    Valid from <first day of the current year>
    Click on Master Data button
    Name <enter name of the activity>
    Description <enter description of the activity>
    Activity Unit <select HR>
    Cost center categories < *>
    Activity type category <3>
    Allocation cost element <enter cost element, created in step-1>
    Price indicator <3>
    <Save>

Step-3: Enter activity price
Path: Accounting>>Controlling>> Cost Center Accounting>> Planning>> Activity Output/Prices>>Change
T code: KP26
Version <0>
From period <1> to period <12>
Fiscal Year <2009.>
Cost center <enter the sender cost center ID>
Activity type< enter the sender cost centre activity type, defined in step-2>
Select the radio button for Form Based option
Click on <Period screen>
Enter activity price for all periods in the presented screen in the column for fixed price.

Step-4: Enter Sender activities
Path:    Accounting>>Controlling>> Cost Center Accounting>> Actual Postings>>Sender Activities>> Enter
T code:KB51N
    Sender cost centre<Sender cost centre>
    Sender activity type<sender’s activity created in step-2>
    Total Quantity<total activity quantity of the sender>
    <Save>

Step-5: Create SKF and enter SKF for the current period for at least 2 receiver cost centers.
   T code: KK01 and KB31N

 

Step-6: Create cycle and segment
 Path:    Accounting>>Controlling>> Cost Center Accounting>> Period-End Closing>> Current Settings>> Define Indirect Activity Allocation
    Cycle<unique ID for cycle>
    Start date <first day of your fiscal year>
    Hit <Enter>
    Text <description>
    Click on <Attach Segment>
    Segment name<unique name>
    Description<description>
    Rule <Posted quantities>
    Shares in % <100>
    Confirm defaulted radio button for <Actual value>
    Receiver Tracing Factor<Variable Portion>
    Variable portion type<select Actual Statistical Figures>
    Click on Sender/Receivers Tab
    Sender:
    Cost center <enter sender cost center>
    Activity type<select activity type created in step-2>
    Receiver:
    Cost centre: From—To <enter receiver cost centres or Group
    Click on Receiver Tracing Factor Tab
    Variable portion type <Actual Statistical Figures>
    Selection criteria:
   Statistical key figure <select SKF created in step-5>
   <Save>

 

Step-7: Execute indirect activity cycle and segment:
Path: Accounting>>Controlling>> Cost Center Accounting>> Period-End Closing>> Single function>> Allocations>>Indirect Activity Allocation        
T code: KSC5
   Period From –To <current period>
    Fiscal year<current fiscal year>
    Flag for Test Run
    <Execute>
    Review the results and if results correct, remove the test run flag and again execute in production mode.
    Confirm postings in sender and receivers cost centers line item report.
T code KSB1

 

Source:
http://www.erpgenie.com/fico/1440-indirect-cost-allocation

<think>根据问题描述,当channel=13时,生成的公式没有包含文件路径,这会导致公式在外部文件未打开时无法正确引用。修正的关键是在INDIRECT函数中正确包含文件路径。 ### 问题分析 1. 公式中缺少文件路径:当前生成的公式只包含文件名和工作表,没有包含文件所在的完整路径。 2. 路径格式要求:在INDIRECT函数中引用外部文件时,需要完整的文件路径,并且路径要用单引号括起来,同时路径中的特殊字符(如空格)需要处理。 ### 修正方案 在构建INDIRECT函数时,将文件路径(`currentFolder`)与文件名(`externalFileName`)拼接,并正确转义。 ### 修改后的代码(仅针对channel=13的部分) ```vba If channel = 13 Then Dim colW As String: colW = channelArray(1) ' "W" Dim colZ As String: colZ = channelArray(4) ' "Z" ' 构建带路径的文件引用字符串 Dim fullPathW As String, fullPathZ As String fullPathW = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colW & "86:" & colW & "145" fullPathZ = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colZ & "86:" & colZ & "145" ' 注意:由于路径中包含反斜杠,需要将路径中的反斜杠替换为双反斜杠(或者使用单引号避免转义问题) ' 在VBA字符串中,两个连续的双引号表示一个双引号字符,而路径中的反斜杠不需要额外转义,因为INDIRECT函数接收的是字符串。 ' 但是,如果路径中包含空格或其他特殊字符,需要用单引号将整个路径、文件名和工作表部分括起来。 ' 由于fullPathW和fullPathZ已经用单引号括起,所以直接使用。 baseFormula = "=SUMPRODUCT(ABS(INDIRECT(""" & fullPathW & """) - INDIRECT(""" & fullPathZ & """)))/60" Cells(i, j).FormulaArray = baseFormula Else ' 普通公式同样需要加上路径 Dim fullPath As String fullPath = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & formulaRange baseFormula = "=SUMPRODUCT(ABS(INDIRECT(""" & fullPath & """)))/60" Cells(i, j).Formula = baseFormula End If ``` ### 完整修改后的过程 下面是修改后的完整过程,重点修改了公式生成部分,确保所有外部引用都包含完整路径。 ```vba Sub ProcessMultipleTables() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual ' 常量定义 Const nameStr1 As String = "2g5gduty0" Const nameStr2 As String = "mcs0" Const nameStr3 As String = ".xlsx" Const cell_column_add As Long = 5 ' E列 ' 动态获取当前工作簿所在文件夹路径 Dim currentFolder As String currentFolder = ThisWorkbook.Path If Right(currentFolder, 1) <> "\" Then currentFolder = currentFolder & "\" ' 定义channel值到列字母的映射 (修正索引偏移) Dim channelArray As Variant channelArray = Array("", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "", "AJ", "AK") ' 定义表格起始行数组 Dim tableStartRows As Variant tableStartRows = Array(38) ' 根据截图从38行开始 ' 变量声明 Dim mcs As Variant, channel As Variant, duty As Long Dim i As Long, j As Long, tableIndex As Long Dim mcsCell As Range, channelCell As Range Dim startRow As Long, endRow As Long Dim externalFileName As String, columnLetter As String Dim formulaRange As String ' 处理每个表格 For tableIndex = LBound(tableStartRows) To UBound(tableStartRows) startRow = tableStartRows(tableIndex) ' 自动检测表格结束行(直到遇到空行) endRow = startRow Do While Cells(endRow + 1, cell_column_add - 3).MergeCells Or _ Not IsEmpty(Cells(endRow + 1, cell_column_add - 3).Value) endRow = endRow + 1 If endRow > 1000 Then Exit Do Loop ' 处理当前表格的每一行 For i = startRow To endRow ' === 读取mcs值 (B列) === Set mcsCell = Cells(i, cell_column_add - 3) ' B列 If mcsCell.MergeCells Then mcs = mcsCell.MergeArea.Cells(1, 1).Value Else mcs = mcsCell.Value End If If IsError(mcs) Or IsEmpty(mcs) Or mcs = "" Or Not IsNumeric(mcs) Then mcs = 0 ' === 读取channel值 (C列) === Set channelCell = Cells(i, cell_column_add - 2) ' C列 If channelCell.MergeCells Then channel = channelCell.MergeArea.Cells(1, 1).Value Else channel = channelCell.Value End If If IsError(channel) Or IsEmpty(channel) Or channel = "" Or Not IsNumeric(channel) Then channel = 0 ' === 根据channel值获取列字母 === If channel >= LBound(channelArray) And channel <= UBound(channelArray) Then columnLetter = channelArray(channel) Else columnLetter = "G" ' 默认值 End If If columnLetter = "" Then columnLetter = "G" formulaRange = "$" & columnLetter & "$86:$" & columnLetter & "$145" ' === 处理duty值 === duty = 0 For j = cell_column_add To 25 Step 2 ' 从E列开始 Dim dutyStr As String dutyStr = Format(duty, "00") externalFileName = nameStr1 & dutyStr & nameStr2 & mcs & nameStr3 ' === 关键修正:为所有外部引用添加完整路径 === If channel = 13 Then Dim colW As String: colW = channelArray(1) ' "W" Dim colZ As String: colZ = channelArray(4) ' "Z" ' 构建带完整路径的引用字符串 Dim fullPathW As String, fullPathZ As String fullPathW = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colW & "86:" & colW & "145" fullPathZ = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colZ & "86:" & colZ & "145" ' 注意:在INDIRECT函数中,整个路径和引用需要用双引号括起来,并在VBA中表示为两个双引号 baseFormula = "=SUMPRODUCT(ABS(INDIRECT(""" & fullPathW & """) - INDIRECT(""" & fullPathZ & """)))/60" ' 使用FormulaArray输入数组公式 Cells(i, j).FormulaArray = baseFormula Else ' 构建普通公式,同样添加完整路径 Dim fullPath As String fullPath = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & formulaRange baseFormula = "=SUMPRODUCT(ABS(INDIRECT(""" & fullPath & """)))/60" Cells(i, j).Formula = baseFormula End If duty = duty + 10 Next j Next i Next tableIndex Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ``` ### 修正说明 1. **添加完整路径**:在构建`fullPathW`、`fullPathZ`和`fullPath`时,将`currentFolder`(当前文件夹路径)与文件名、工作表名和单元格引用拼接,并用单引号括起来(因为路径中可能有空格)。 2. **转义双引号**:在`INDIRECT`函数中,整个引用字符串需要用双引号括起来,所以在VBA中我们使用三个双引号:`INDIRECT(""" & fullPath & """)`,这样生成的公式中会变成`INDIRECT("'C:\folder\[file.xlsx]sheet'!A1")`。 3. **注意路径分隔符**:Windows路径使用反斜杠,在字符串中不需要额外转义(因为VBA字符串中的反斜杠就是字面值)。 ### 注意事项 - 确保外部文件存在,否则公式会返回错误。 - 如果路径中包含特殊字符(如方括号、单引号等),可能需要进一步处理,但通常用单引号将整个路径和引用括起来可以解决大部分问题。 ### 相关问题 §§相关问题§§: 1. 在Excel VBA中,如何避免INDIRECT函数引用外部文件时出现#REF错误? 2. 如何优化VBA代码以提高处理大量外部文件引用的性能? 3. 在动态生成公式时,如何处理文件名或路径中包含特殊字符(如空格、单引号)的情况? 4. 使用VBA操作外部工作簿时,如何避免内存泄漏?</think>### 问题分析与解决方案 您发现当`channel=13`时生成的公式缺少文件路径,导致公式无法正确引用外部工作簿。这主要是因为代码中构建公式时未包含当前文件夹路径。以下是完整的修正方案: ```vba Sub ProcessMultipleTables() Application.ScreenUpdating = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual ' 常量定义 Const nameStr1 As String = "2g5gduty0" Const nameStr2 As String = "mcs0" Const nameStr3 As String = ".xlsx" Const cell_column_add As Long = 5 ' E列 ' 动态获取当前工作簿所在文件夹路径 Dim currentFolder As String currentFolder = ThisWorkbook.Path If Right(currentFolder, 1) <> "\" Then currentFolder = currentFolder & "\" ' 定义channel值到列字母的映射 Dim channelArray As Variant channelArray = Array("", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "", "AJ", "AK") ' 定义表格起始行数组 Dim tableStartRows As Variant tableStartRows = Array(38) ' 根据截图从38行开始 ' 变量声明 Dim mcs As Variant, channel As Variant, duty As Long Dim i As Long, j As Long, tableIndex As Long Dim mcsCell As Range, channelCell As Range Dim startRow As Long, endRow As Long Dim externalFileName As String, columnLetter As String Dim formulaRange As String ' 处理每个表格 For tableIndex = LBound(tableStartRows) To UBound(tableStartRows) startRow = tableStartRows(tableIndex) ' 自动检测表格结束行 endRow = startRow Do While Cells(endRow + 1, cell_column_add - 3).MergeCells Or _ Not IsEmpty(Cells(endRow + 1, cell_column_add - 3).Value) endRow = endRow + 1 If endRow > 1000 Then Exit Do Loop ' 处理当前表格的每一行 For i = startRow To endRow ' === 读取mcs值 (B列) === Set mcsCell = Cells(i, cell_column_add - 3) ' B列 If mcsCell.MergeCells Then mcs = mcsCell.MergeArea.Cells(1, 1).Value Else mcs = mcsCell.Value End If If IsError(mcs) Or IsEmpty(mcs) Or mcs = "" Or Not IsNumeric(mcs) Then mcs = 0 ' === 读取channel值 (C列) === Set channelCell = Cells(i, cell_column_add - 2) ' C列 If channelCell.MergeCells Then channel = channelCell.MergeArea.Cells(1, 1).Value Else channel = channelCell.Value End If If IsError(channel) Or IsEmpty(channel) Or channel = "" Or Not IsNumeric(channel) Then channel = 0 ' === 根据channel值获取列字母 === If channel >= LBound(channelArray) And channel <= UBound(channelArray) Then columnLetter = channelArray(channel) Else columnLetter = "G" ' 默认值 End If If columnLetter = "" Then columnLetter = "G" formulaRange = "$" & columnLetter & "$86:$" & columnLetter & "$145" ' === 处理duty值 === duty = 0 For j = cell_column_add To 25 Step 2 ' 从E列开始 Dim dutyStr As String dutyStr = Format(duty, "00") externalFileName = nameStr1 & dutyStr & nameStr2 & mcs & nameStr3 ' === 关键修正:添加完整文件路径 === If channel = 13 Then Dim colW As String: colW = channelArray(1) ' "W" Dim colZ As String: colZ = channelArray(4) ' "Z" ' 构建带完整路径的引用 Dim fullPathW As String Dim fullPathZ As String fullPathW = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colW & "$86:$" & colW & "$145" fullPathZ = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colZ & "$86:$" & colZ & "$145" ' 构建完整公式 baseFormula = "=SUMPRODUCT(ABS(INDIRECT(""" & fullPathW & """) - " & _ "INDIRECT(""" & fullPathZ & """)))/60" ' 写入公式到单元格 Cells(i, j).FormulaArray = baseFormula Else ' 普通公式也添加完整路径 Dim fullPath As String fullPath = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & formulaRange baseFormula = "=SUMPRODUCT(ABS(INDIRECT(""" & fullPath & """)))/60" Cells(i, j).Formula = baseFormula End If duty = duty + 10 Next j Next i Next tableIndex Application.Calculation = xlCalculationAutomatic Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub ``` ### 关键修正点详解 1. **添加完整文件路径**: ```vba fullPathW = "'" & currentFolder & "[" & externalFileName & "]工作表1'!" & colW & "$86:$" & colW & "$145" ``` - 将`currentFolder`(当前工作簿路径)添加到文件引用中 - 生成格式如:`'C:\YourFolder\[2g5gduty000mcs00.xlsx]工作表1'!W$86:W$145` 2. **路径格式处理**: - 使用单引号`'`包裹完整路径,处理路径中的空格 - 保持`[文件名]工作表名!单元格`的标准Excel引用格式 - 添加绝对引用`$`符号确保公式复制时引用不变 3. **公式构建优化**: ```vba "=SUMPRODUCT(ABS(INDIRECT(""" & fullPathW & """) - " & _ "INDIRECT(""" & fullPathZ & """)))/60" ``` - 使用`SUMPRODUCT`替代`AVERAGE`避免隐式交集问题 - 保持公式数学逻辑不变:计算两列差的绝对值的平均值 4. **特殊字符处理**: - 路径中的反斜杠`\`不需要额外转义 - 文件名中的数字和字母无需特殊处理 - 工作表名含中文字符时保持原样 ### 公式输出示例 修正后当`channel=13`时生成的公式将包含完整路径: ```excel =SUMPRODUCT(ABS( INDIRECT('C:\YourDataFolder\[2g5gduty000mcs00.xlsx]工作表1'!W$86:W$145") - INDIRECT('C:\YourDataFolder\[2g5gduty000mcs00.xlsx]工作表1'!Z$86:Z$145") ))/60 ``` ### 验证方法 1. 在Excel中按`Alt+F11`打开VBA编辑器 2. 插入新模块并粘贴修正后的代码 3. 运行宏后检查E列及后续列公式: - 确认公式包含完整文件路径 - 验证`channel=13`的公式格式正确 - 测试文件存在时公式能否正确计算 ### 相关问题
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值