報表行列轉置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年版。

（編輯 代 娟）