小编推荐

管理报表对于一个组织的运营和决策过程非常重要，随着法定报告和管理报告的融合，合并报表项目实施过程中经常遇到的“中国式复杂报表”，主要特点是表头乱、格式复杂、承载的信息数据来源多样，通常很难在系统中定义。报表编制完成后，用户需要查询并导出所有报表进行存档，通过web网页中导出的EXCEL每次都需要调整打印格式和样式。

怎么解决上述问题，既能满足集团法定报表格式要求，又能满足每家公司复杂的管理报表表样需求？本期文章或能给大家一些借鉴的思路。

撰稿人：金蝶-韩立国

1 业务背景

项目情况

某大型投资集团业态分为能源、房地产、金融、文旅、矿山等。集团共用一套会计科目，按不同业态进行了授权。合并报表上线后通过集成方式获取科目余额表和各板块业务数据，报表编报完成后用户需要将报表数据分为纸质版和电子版PDF进行存档。

集团合并报表由集团统建，集团下发统一的报表表样，但因集团投资的业务比较广，不同的行业和公司对报表的样式要求也不尽相同，下级单位的报表个性化需求不能全部满足，如果全部满足则集团报表维护的工作量非常大。

同时，因为合并报表系统目前还不支持分级授权，无法将维护报表的角色下发到二级或三级单位。

集团统一样式报表和下级单位的报表样式如下所示。

集团统建标准样式报报表：

利润表表样

  图1 利润表表样示例

现金流量表表样

图2 现金流量表表样示例

单位内部管理报表样式：

内部管理利润表样式

图3 内部管理利润表-按部门

内部分行业管理利润表样式

图4 分行业管理利润表表样示例

内部经营情况报表样式

图5 经营情况报表样式示例

客户痛点分析

前文提到了个性化报表表样，除了报表表样复杂，还涉及打印格式、报表美观可读性，以及临时性取数需求等。

• 提取数据效率低、操作复杂

用户查询和提取数据需要登录网页系统，导出EXCEL后再编辑，操作步骤多且复杂，因为星瀚合并报表系统为多维系统，每次打开报表需要选择正确的维度，经常选错。客户期望有一种更简单的数据获取方式，不需要在系统和EXCEL之间来回切换，减少用户复制粘贴环节，避免过程中产生不必要的错误。

• 管理报表格式复杂，系统很难定义

各家单位的报表样式和集团统建的报表样式不一致，大多情况下财务人员不能根据自己的需求去定义报表样式，即使能定义操作太复杂，不能像财务人员习惯的EXCEL电子表格方式去定义。

• 报表存档及打印样式设置繁琐，消耗了财务人员的精力

打印要单独设置打印区域，存档报表要登录系统导出打印，客户希望能一次性获取所有报表，每张报表的格式无需再次设置，可直接批量存档或打印。

客户诉求与期望

• 在电子表格中定义的报表，可以直接提取星瀚系统中的数据

在office的电子表格中定义个性化的报表表样，每个单元格的公式可以单独设置取数公式，公式定义可以快速拖拽生成，数据来源可以是总账、预算、合并报表和财务报表模块的数据。

• 可复制性强，定义简单灵活

定义报表门槛不要过高，经过简单的学习或参考模板即可快速上手。可以一键获取所有数据，并可针对数据的变化进行简单的预警设置和图表分析设置。

2 解决方案

整体思路

“EPM客户端”是一个搭载在Office办公软件上的功能插件，兼容微软Office和国产的WPS等主流软件，在EPM客户端中可以进行合并报表的一站式操作，如公式取数、报表编制、调整抵销、报表计算、智能合并等。同时结合表格函数的强大功能，可获取多维库和星瀚总账等数据。

图6 EPM客户端业务蓝图

刷数模板：在电子表格中设置参数切换的“报表封面”页签和“报表表样”页签，“报表表样”页签中每个单元格通过函数获取多维数据和业务模块数据，最终通过宏按钮生成数值版。

图7 模板定义及取数步骤

关键步骤及效果展示

第一步：定义报表封面

在EXCEL中定义“报表封面”页签，“报表封面”页签中定义可选择组织、财年、期间、过程的下拉框，用户切换组织、财年期间来获取不同组织的数据。

图8 报表封面定义

第二步：报表编码表定义

在EXCEL中定义“编码表”页签，编码表中按报表项目转换为科目和变动类型的编码，填到指定位置。可以理解为，提前定义好报表公式取数的维度组合放到此页签中，报表定义的取数页签可以直接通过公式解析到此单元格，方便取数公式快捷定义，因为大多情况下维度组合都是一致的，无需每个单元格都重新定义。

图9 编码表定义

图10 编码表定义

指定的单元格中录入如下公式内容，示例如下:

="S@"&$B$1&",FY@"&报表封面!$C$6&",P@"&报表封面!$C$7&",CT@YTD,C@DC,E@"&报表封面!$C$4&",BP@"&报表封面!$C$8

此处公式获取了报表封面页签中C4单元格组织编码、C6和C7单元格的财年期间和过程编码，和指定了变动类型和币别，通过EXCEL公式解析后变为“S@MRpt,FY@FY2022,P@M_M12,CT@EndingBalance,C@DC,E@LC0000,BP@ERpt”

第三步：报表取数页签的函数设置

通过EPM客户端的Kd.GetV 函数，可将多维库中的数据提取出来。

图11 公式向导

EPM客户端构建公式的方式定义GetV公式，GetV公式可以获取星瀚合并报表、全面预算中的数据。构建函数后在公式编辑栏自动生成V公式的取数维度组合：=Kd.GetV("demo|CM|N2023","E@LC0000,A@BS01,S@MRpt,FY@FY2020,P@M_M12,BP@ERpt,C@DC,AT@ATTotal,CT@EndingBalance,IC@ICNone,C1@C1None,C2@C2None,C3@C3None,C4@C4None,C5@C5None",)

维度组合可以通过EXCEL表内公式解析的方式进行设置设置，如下：

图12 函数定义示例

通过构建函数的方式示例：

Kd.GetV("demo|CM|N2023","E@LC0000,A@BS01,S@MRpt,FY@FY2022,P@M_M12,BP@ERpt,C@DC,AT@ATTotal,CT@EndingBalance,IC@ICNone,C1@LocalGAAP,C2@C2None,C3@C3None,C4@C4None,C5@C5None",)

“Kd.GetV”函数可将公式定义成如下格式：

ROUND(Kd.GetV("demo|CM|N2023",""&编码表!$B$2&",A@"&编码表!A18&",AT@ATTotal,IC@InternalCompany,C1@LocalGAAP,C2@C2None,C3@C3None,C4@C4None,C5@C5None",0)/编码表!$G$2,2)

通过公式我们可以看到，组织、财年、期间和过程的维度组合经过""&编码表!$B$2&"进行了替换，GetV公式可以自行解析“编码”表页面中的B2单元格。本文示例中，“编码表”的B2单元格定义了维度组合S@MRpt,FY@FY2022,P@M_M12,CT@EndingBalance,C@DC,E@LC0000,BP@ERpt，通过公式EXCEL公式解析后每个单元格无需用户去构建函数，而是通过公式编辑栏的方式实现。

EPM客户端同时支持ACCT（总账科目余额表）、ACCTCF（总账现金流量表）等公式、汇率公式及股权关系公式。

通过上述公式可以将合并报表、全面预算、总账的数据直接获取到定义的EXCEL页签中，无需在系统中定义报表模板。通过ACCT公式取数无需将总账的数据进行多维存储，直接展现到报表表样中。

EPM函数支持在定义公式过程中使用IF、ROUND等常用函数，定义更快捷、便于财务人员操作。熟练后只需要定义好一个单元格的公式，其他单元格通过EXCEL的拖拉快速进行公式填充，只要财务人员理解公式构成，就可以自己定义报表，无需星瀚合并报表的管理员和运维人员定义。

第四步：勾稽检查

通过在模板中定义检查公式，可以检查模板中的报表之间的勾稽关系，用户可以通过此页签快速链接到对应的单元格和检查报表的逻辑正确性。同时可以验证不通过无法生成数值版文档。

图13 审核页签定义示例

第五步：宏定义

开发VB宏开发代码，可将刷出的数据套表生成数值版。定义宏代码需安装宏VB开发功能，目前微软的EXCEL和WPS的电子表格均支持。

图14 宏代码开发示例

图15 宏代码开发按钮定义

第六步：通过模板刷取数据生成数值版

点击EPM客户端中的“刷新所有表页”按钮，套表自动更新系统最新数据，点击报表封面的“生成文档”按钮，调用宏代码自动断开公式链接，生成数值版套表数据。

图16 数据刷新及宏功能按钮调用

生成数值版可以继承模板中定义的打印区域等信息，无需每次打印进行设置，并可以通过宏代码自定义开发实现批量打印和存档功能。

第七步：效果展示

通过EMP客户端的函数定义整套管理报表，普通的报表人员了解公式用法后，可按自己的需求去定义。

图17 定义报表样式展示

用户按自己的报表需求在模板定义报表样式，无需每次从系统中导出后自己重新加工。

在EXCEL电子表格中可以通过vlookup、Index及match等公式定义星瀚合并报表中无法定义的样式。

图18“生成文档”按钮功能效果展示

“生成文档”按钮调用自定义开发的宏，可以将报表模板中需要保存为“数值版”的页签去掉GetV公式，保存为数值格式。同时原来定义的表内公式如：=B63+B24继续保留。

产品版本

金蝶云星瀚合并报表V6.0以上、EPM客户端V2.20及以上版本

3 方案的推广价值

行业的普适程度

财务人员最熟悉office电子表格的操作，通过在EXCEL中定义套表，切换组织、财年、期间获取系统数据的方式很容易让财务人员接受。财务人员熟悉函数用法后，可以自己按管理需求定义报表，减轻了运维和实施的工作量，报表人员也可以根据自己的实际使用习惯和需求定义报表。此方案非常具有代表性，可以作为管理类报表实施方案进行推广。

对客户的价值

• 提取数据效率更高

财务人员无需通过web登录合并报表系统，只需要打开报表模板套表，选择链接输入用户名和密码，即可一次性获取整套报表数据。在套表中可查看勾稽状态，EXCEL链接到对应报表的单元格更直观高效。

• 按业务需求快速定义管理报表表样

大型企业集团通常为多业态的投资公司，不同行业的管理报表样式不能做到完全统一。通过EPM客户端函数可以获取总账、财务报表、合并报表、及全面预算的数据，实现同一张表展示不同系统数据的场景，报表样式更加灵活。

• 报表套表存档及打印，定义久其导出样式

通过VB宏代码实现批量刷新套表，并对套表进行另存，在生成套表的基础上批量打印，同时使用EPM客户端函数可以按久其导出格式定义套表，无需在系统中定义。

4 注意事项

• 附件中提供刷数模板格式为“xlsm“类型，仅启用宏的的工作簿；

•  使用”刷新所有表页”功能，需要使用EPM客户端2.2.0及以上版本。

  
  

5 相关资料

生成数值版的宏代码如下，供参考：

Private Sub cb_crtDom_Click()

   Dim fname As String '文档保存路径

   pth = ThisWorkbook.Path & "\"  '当前路径

   '封面参数

   lEntity = Sheets("报表封面").[C4].Value '实体

   PYear = Sheets("报表封面").[F6].Value '年

   Period = Sheets("报表封面").[F7].Value '月

   RptType = Sheets("报表封面").[C9].Value '报表类型 月报 季报

   Statement = Sheets("报表封面").[C10].Value 'custom4 报表口径

   PerData = Sheets("报表封面").[C12].Value '数据期间

If f_crtHFMCHK() = False Then

       MsgBox "勾稽审核不通过，请检查"

Exit Sub

End If

   '取到文档保存路径

   fname = Application.GetSaveAsFilename(InitialFileName:=lEntity & "_" & PYear & "年" & Period & "月_" & RptType & "_" & Statement & ".xlsx", FileFilter:="Excel 启用宏的工作簿 (*.xlsx),*.xlsx")

If fname = "False" Then

       MsgBox "没有选择文件,退出执行"

Exit Sub

End If

   '路径选择正确

If fname <> "False" Then

Application.EnableEvents = False

Application.ScreenUpdating = False

Application.Calculation = xlManual

On Error GoTo ErrHandler

       '另存为文档

ActiveWorkbook.SaveAs Filename:=fname, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

       '移除不在工作目录表中的sheet

Dim contentSheet As Worksheet

Dim contentRange As Range

Dim contentArea As String

Dim sheetNames() As Variant

Dim isDel As Boolean

contentArea = "B3:C13"

       Set contentSheet = Sheets("工作表目录")

sheetNames = contentSheet.Range(contentArea).Value

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets

isDel = True

For i = LBound(sheetNames, 1) To UBound(sheetNames, 1)

If ws.Name = sheetNames(i, 1) Then

                   '替换sheet名称

ws.Name = sheetNames(i, 2)

isDel = False

Exit For

End If

Next i

If isDel = True And ws.Visible <> XlSheetVisibility.xlSheetVeryHidden Then

ws.Delete

End If

Next ws

Application.DisplayAlerts = True

       '替换worksheet中的epm自定义公

f_replaceFormulaToValue

       '保存生成文档，并关闭

       ActiveWorkbook.Worksheets("报表封面").cb_crtDom.Visible = False '文档封面隐藏 生成文档按钮

ActiveWorkbook.Save

Application.EnableEvents = True

Application.ScreenUpdating = True

Application.Calculation = XlCalculation.xlCalculationAutomatic

End If

ErrHandler:

Application.DisplayAlerts = True

Application.EnableEvents = True

Application.ScreenUpdating = True

Application.Calculation = XlCalculation.xlCalculationAutomatic

End Sub

'审核函数

Function f_crtHFMCHK()

Dim chk As String

   chk = Sheets("审核验证表").[A3].Value

   If Abs(Int(chk)) = 0 Then '通过审核

f_crtHFMCHK = True

Else

f_crtHFMCHK = False

End If

End Function

Sub f_replaceFormulaToValue()

Dim ws As Worksheet

Dim frngs As Range

Dim rng As Range

Dim filterFormulasArr(2) As String

Dim isInFormula As Boolean

filterFormulasArr(0) = "V"

filterFormulasArr(1) = "Kd.GetV"

filterFormulasArr(2) = "Kd.ACCT"

For Each ws In ActiveWorkbook.Worksheets

If ws.Visible = XlSheetVisibility.xlSheetVisible Then

On Error Resume Next

Set frngs = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)

If Not frngs Is Nothing Then

For Each rng In frngs.Cells

If rng.HasFormula = True Then

isInFormula = f_isItemInArr(filterFormulasArr, rng.Formula)

If isInFormula = True Then

rng.Value = rng.Value

End If

End If

Next rng

Else

Err.Clear

End If

On Error GoTo 0

End If

Next ws

End Sub

Function f_isItemInArr(filterArr() As String, item As String) As Boolean

Dim cur As String

Dim found As Boolean

Dim i As Long

found = False

For i = LBound(filterArr) To UBound(filterArr)

If InStr(item, filterArr(i) & "(") > 0 Then

found = True

Exit For

End If

Next i

f_isItemInArr = found

End Function

报表查询模板参考附件1：报表查询模板EPM客户端V1.0.xlsm

说明：因社区不支持上传Excel启用宏的工作簿，有需要的同事可联系作者获取。

附：案例评论有奖活动

我们将每月挑选优质案例评论发放精美奖品，以下形式的评论中奖概率更高哟：

1、分享案例给您带来的启发：这个案例为您当前、进行中或即将开展的项目带来了哪些启发？若您成功将案例中的策略或方法应用到实际项目中，可在评论区分享您的具体应用情况，我们将为您准备额外的奖励。

2、提出案例优化的建议：您认为案例在哪些方面还有改进的空间？您有哪些更好的替代方案或建议？

期待您的案例留言，您的反馈是我们前进的动力~