报表行列转置Excel应用浅析

张家口职业技术学院 邢伟平 赵 林

报表行列转置Excel应用浅析

张家口职业技术学院 邢伟平 赵 林

由于管理目的不同，不同企业之间使用的内部报表格式也不同，甚至同一企业内不同部门在使用同一数据时也会应用不同的报表格式。由于使用的管理软件对报表格式做了设定，因此输出的报表不适应用于其他用途，这些都给报表数据管理带来不便，也不利于数据的再加工和再利用，本文拟利用Excel来实现报表行列转置。

一、报表行列转置应用例析

报表行列转置法是在不改变报表数据的前提下改变报表行列的位置，从而使报表按使用者的需求进行排列的方法。该方法是将源报表的行作为新报表列，源报表的列作为新报表的行，从而得到目的报表的过程。由于企业使用的内部报表格式没有具体的规定，因此在实务中，报表标题有的以行优先，有的以列优先，有的行、列标题混用。这样，同一张报表由于格式不同，内容排列也不同，对同一张报表的加工、利用方式也就不同，从而使报表的使用效率降低。以两个实例予以说明：

［例1］某企业行政部统计的本部门1月至4月Excel管理费用如图1所示，该部门制表时从本部门用表及排版的方便性出发，将管理费用明细项目按行优先的顺序排列，然而报表提交会计部时，却与会计账簿管理费用明细账的排列顺序不一致，不方便相互核对，为此需要将该表的管理费用明细项目转置为以列优先的顺序（见图2）。

图1

图2

［例2］某注册会计师审计时利用被审计单位财务软件导出功能取得的Excel会计科目余额表如图3，该表每个会计科目信息均占四行，每行描述一部分会计科目信息，在查找、统计或分析时，都不方便，为此需将该表转置为每个会计科目信息只占一行的报表。

二、报表行列转置方法的Excel应用

（1）简单表的行列转置。简单表是指在一个数据表中一条记录只占一行的表。简单表由于结构简单，因此可以通过函数法或复制——选择性粘贴法实现行列转置。

图3

一是函数法。函数法是利用Excel函数TRANSPOSE（Array）实现报表行列转置的方法。TRANSPOSE（Array）函数的作用是返回指定区域的转置。函数TRANSPOSE（Array）必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与Array的列数和行数相同。Array为需要进行转置的数组或工作表中的单元区域。以图1为例，若要转置为图2所示，首先根据源数据表区域A1：E17的列数（5列）和行数（17行）选中放置目的表的区域G1：W5（共5行、17列），在公式编辑栏输入“=TRANSPOSE（A1：E17）”并按组合鍵“Ctrl+Shift+Enter”，以数组形式完成输入，即完成了报表的行列转置。

二是复制——选择性粘贴法。复制——选择性粘贴法是利用“复制”功能拷贝源数据表，利用“编辑”菜单的“选择性粘贴”项得到目的数据表的方法，该方法比函数法便捷。以图1为例，若要得到图2所示，首先选中源数据表区域A1：E17并按组合鍵“Ctrl+C”复制，然后选中要放入目的表的单元，打开“编辑”菜单中的“选择性粘贴”项，选中“转置”复选项，点击“确定”按钮即可。

（2）复杂表的行列转置。复杂表是指在一个数据表中一条记录占两行或两行以上的表。利用Excel进行复杂表的行列转置时，有两种方法，分别是手工筛选法和参数筛选法。

一是手工筛选法。手工筛选法的思路是把转置前是字段值，转置后做为字段名称的数据做为筛选条件，对源数据表依次筛选出需要的结果，并将每次筛选出的结果分别通过复制、粘贴功能拷贝到目的区域，从而得到转置后的目的数据表。以图3为例的手工筛选法操作过程是：通过数据筛选功能中的“自动筛选”，分别以源数据表中“数据项”字段的字段值——“年初余额”、“本期发生额”、“累计发生额”和“期末余额”做为筛选条件，进行四次筛选（见图4），并将四次筛选的结果依次通过复制、粘贴功能拷贝到目的区域的方法。

图4

二是参数筛选法。参数筛选法的思路是在目的数据表先利用公式得到对源数据表的第一条记录值的引用，生成目的数据表的第一条记录，再利用向下填充功能得到一个引用数据表。若源数据表有N条记录，每条记录占Q行，则引用数据表有（N×Q）行，其中从第一行开始，依次间隔（Q－1）行的共N行数据为有效数据，其余共N×（Q－1）行为无效数据，为了取得有效数据同时剔除无效数据，需要一个包含Q个数据的序列，且该序列循环N次产生N×Q个值依次对应引用数据表的每个行，即将包含N个循环的序列Q做为引用数据表的行标识，最后利用“自动筛选”功能对与引用数据表第一条记录的行标识相同的记录进行一次筛选，就能得到最终的目的数据表。以图3为例的参数筛选法操作过程是：首先，在目的工作表将表头制作完成；其次，在目的工作表最左侧插入一个辅助列，并以“a”、“b”、“c”、“d”做为序列，利用填充柄向下填充，产生辅助列的值，该列的值依次为“a”、“b”、“c”、“d”、“a”、“b”、“c”、“d”、“a”、“b”、“c”、“d”、……；再次，在目的数据表第一条记录所占的区域B2：K2分别输入对源数据表第一条记录对应值的引用“=源表!A2”、“=源表!B2”、“=源表!D2”、“=源表!E2”、“=源表!D3”、“=源表!E3”、“=源表!D4”、“=源表!E4”、“=源表!D5”、“=源表!E5”（见图5）；最后，选中B2：K2区域，利用填充柄向下填充；第五，选择“数据”——“筛选”——“自动筛选”菜单，以“辅助列”的值“a”做为筛选条件，在“辅助列”所在的下拉列表中选择“a”，即得到转置后的数据表。

图5

报表行列转置是对报表内容的重新排列，这种排列没有改变报表的内容，只是变换了报表的排列顺序，因此不会增加或减少数据容量，但为使用者提供了可以按需求和习惯排列和使用报表的方法。另外，报表行列转置的四种方法：函数法、复制——选择性粘贴法、手工筛选法和参数筛选法，也可由使用者根据需求和习惯自行选用。

［1］耿萍、杨虹：《Excel在财务管理中的应用技术》，中国铁道出版社2002年版。

［2］宇传华、颜杰：《Excel与数据分析》，电子工业出版社2002年版。

（编辑 代 娟）