【作　　者】
任 杰

【作者单位】
（湖北黄石市众邦城市住房投资有限公司 湖北黄石 435000）

【摘　　要】

      【摘要】Excel软件对于财务专业人员是一个简便易学、高效实用的好帮手。本文通过Excel软件进行关联方交易对账操作，为人们提供了一种方便快捷的对账方法。
【关键词】Excel   关联方交易   对账

集团企业内部关联方交易对账涉及的子企业多，相关子企业要定期逐笔进行会计凭证核对，费时、费力，容易出错，很大程度上制约了集团公司合并财务报表的准确性和及时性。下面笔者根据多年的工作实践，谈一谈如何利用Excel表格对关联方交易进行快速对账。
一、需求分析
在各关联方的内部往来方面，按月份显示各科目交叉核对结果，其中：“关联方销售收入”与“关联方购入成本”、“收到销售收入现金”与“支付购买商品现金”、“从关联方收到的现金”与“支付给关联方的现金”、“应收账款”与“应付账款”、“其他应收款”与“其他应付款”科目进行核对。选择月份对应的核对科目，显示各关联方名称、科目、金额及核对差异数，最终核对结果汇总见表1：
二、总体设计
新建空白Excel工作簿，将自动建立的Sheet1、Sheet2和 Sheet3工作表以及按SHIFT+F11新增的Sheet4、Sheet5和 Sheet6等多个工作表，重命名为汇总核对、核对结果、核对过程和得力文教、得力电子等多家关联单位。
1. 定制各关联单位工作表。在关联单位工作表A1单元格中输入报表编号，如：C-05；A2单元格中输入报表名称：关联方交易披露表；A3单元格中输入编制单位，如：得力文教。
在第四行从A1单元格开始顺序输入记录字段名：交易名称/单位名称、合计、集团管理部等多家关联单位名称。在第一列从A5单元格开始顺序输入记录字段名：本月关联方销售收入、本月关联方购入成本等，具体见表2。
2. 定制核对结果工作表基本框架。在核对结果工作表第一行中输入名称，如：控制表；选择A3：A6单元格，合并单元格，输入参数设置名称；在B3：B6单元格中顺序输入记录字段名：“月份选择：”、“核对项目1：”、“核对项目2：”、“核对项数：”。在第二列选中C3单元格，在功能区中点击数据，选择数据有效性，在设置有效性条件下选择序列，再在来源中输入1 ~ 12月，每个数据以分号分开；选中C6单元格，在功能区中点击数据，选择数据有效性，在设置有效性条件下选择序列，再在来源中输入1、2两个数字，每个数据以分号分开；选中C4、C5、D4、D5单元格，分别在功能区中点击数据，选择数据有效性，在设置有效性条件下选择序列，再在来源中输入“本月关联方销售收入、本月关联方购入成本、本月收到销售收入”等名称，每个数据以分号分开（见表3）。在核对结果工作表第九行选择A9:H6单元格，合并单元格，输入名称，如：得力集团关联方数据核对结果；选择第十一行在A11：H11单元格中顺序输入记录字段名：序号、关联方1、项目1、金额、关联方2、项目2、金额2、差额，并设计表格边框（见表4）。

3. 设计核对过程表及相关函数表达式。
首先，对子公司进行编号，以便按序号取值。选择第一列，从A1单元格依次输入序号1、2、3……选取第三列，从B1单元格依次输入单位名称：集团管理部、得力文教等。
选择第46行，从A46单元格依次在单元格中输入：标记、序号1、公司1、项目1金额、序号2、公司2、项目2金额、差异等字段。
选择C44、C45单元格，依次输入项目1、项目2两个字段；在D44单元格中输入函数关系式：“=核对结果!C4”，选取核对结果参数选项项目1。其他项目依次参照输入。
编制序列号1函数关系式：在B47单元格中输入序列号：1；在B48单元格中输入函数关系式：“=IF（COUNTIF（B47：$B$47，B47）=$G$1，B47+1，B47）”，运用条件函数关系式COUNTIF计算在B47：$B$47区域中存在B47的单元格个数，再用IF函数关系式组合进行列示。在E47单元格中输入函数关系式：“=COUNTIF（$B$47：B47，B47）”。
编制公司1函数关系式：在C47单元格中输入函数关系式：“=VLOOKUP（B47，$A$2：$B$41，2，0）”，运用函数关系式VLOOKUP选取B47在$A$2：$B$41区域中第二列中对应的公司名称。在F47单元格中输入函数关系式：“=VLOOKUP（E47，$A$2：$B$41，2，0）”。
编制项目1金额函数关系式：在D47单元格中输入函数关系式：“=IF（核对结果！$C$6=1，HLOOKUP（F47，INDIRECT（C47&"！4：400"），$G$3，0），HLOOKUP（F47，INDIRECT（C47&"！4：400"），$G$3，0）-HLOOKUP（F47，INDIRECT（C47&"！4：400"），$G$4，0））”，运用函数关系式INDIRECT选取公司在对应关联公司表格中所在区域返回文本字符中所指定的引用，运用函数关系式HLOOKUP在关联公司所在区域中的列序号，进一步选取对应项目的值，再用IF函数关系式组合进行列示。在G47单元格中输入函数关系式：“=IF（核对结！$C$6=1，HLOOKUP（C47，INDIRECT（F47&"！4：400"），$H$3，0），HLOOKUP（C47，INDIRECT（F47&"！4：400"），$H$3，0）-HLOOKUP（C47，INDIRECT（F47&"！4：400"），$H$4，0））”。编制差异函数关系式：在H47单元格中输入函数关系式：“=IF(COUNTIF（I47：J47，C47）+COUNTIF（I47：J47，F47）=1，0，ROUND（D47-G47，2））”，运用函数关系式COUNTIF计算I47：J47中对应子公司数目；运用函数关系式ROUND对运算结果保留二位小数；再用IF函数关系式组合进行列示。
4. 设计关联方数据核对结果相关函数表达式。在A12单元格中输入函数关系式：“=IF（ROW（A1）>核对过程!$A$45，""，ROW（A1））”，对序号进行编号；在B12单元格中输入函数关系式：“=IF（A12=""，""，VLOOKUP（A12，核对过程!A46：$C$980，3，0））”，选取符合条件的对应关联方；在E12单元格中输入函数关系式：“=IF（A12=""，""，VLOOKUP（A12，核对过程!$A$46：$F$980，6，0））”，选取符合条件的对应关联方；在C12单元格中输入函数关系式：“=IF（A12=""，""，$C$4）”，选取符合条件的对应项目；在F12单元格中输入函数关系式：“=IF（A12=""，""，$D$4）”，选取符合条件的对应项目。
在D12单元格中输入函数关系式：“=IF（A12=""，""，VLOOKUP（A12，核对过程！$A$46：$D$980，4，0））”，选取符合条件的金额；在G12单元格中输入函数关系式：“=IF（A12=""，""，VLOOKUP（A12，核对过程！$A$46：$H$980，7，0））”，选取符合条件的金额；在H12单元格中输入函数关系式：“=IF（A12=""，""，D12-G12）”，计算关联方对应项目差异额。
5. 设计汇总核对表相关函数表达式。在汇总核对表中选取B5单元格，输入函数关系式：“=HLOOKUP（$B$3，$C$4：$N$14，2，0）-HLOOKUP（$B$3，$C$4：$N$14，3，0）”，选取对应项目差异额；选取C5单元格，输入函数关系式：“=SUM（得力文教：得力办公！B5）”，对各关联单元对应科目进行汇总。然后分别选中B5、C5单元格，拖动B5、C5单元格右下角的小“十”字图标向下。
三、核对明细
打开核对结果表，在月份选择中选择对应的月份，如1月份，再选择核对项目1对应的C4、D4两个往来项目，再选择核对项数1，核对结果可显示在关联方数据核对结果表中。如果应收/应付、其他应收/其他应付核对，选择对应项目为2。
通过上述操作，当我们要进行关联方往来账目核对时，只要将各关联单位交易数据填入表格，打开“汇总核对表”，就可以找到差异项目，再在核对结果表中选择差异项目，就可以找到关联企业及对应项目差异所在。这样，核对工作简单、高效，不需要人工分别进行核对了。○