報表行列轉置Excel應用淺析

張家口職業(yè)技術學院 邢偉平 趙 林

報表行列轉置Excel應用淺析

張家口職業(yè)技術學院 邢偉平 趙 林

由于管理目的不同，不同企業(yè)之間使用的內(nèi)部報表格式也不同，甚至同一企業(yè)內(nèi)不同部門在使用同一數(shù)據(jù)時也會應用不同的報表格式。由于使用的管理軟件對報表格式做了設定，因此輸出的報表不適應用于其他用途，這些都給報表數(shù)據(jù)管理帶來不便，也不利于數(shù)據(jù)的再加工和再利用，本文擬利用Excel來實現(xiàn)報表行列轉置。

一、報表行列轉置應用例析

報表行列轉置法是在不改變報表數(shù)據(jù)的前提下改變報表行列的位置，從而使報表按使用者的需求進行排列的方法。該方法是將源報表的行作為新報表列，源報表的列作為新報表的行，從而得到目的報表的過程。由于企業(yè)使用的內(nèi)部報表格式?jīng)]有具體的規(guī)定，因此在實務中，報表標題有的以行優(yōu)先，有的以列優(yōu)先，有的行、列標題混用。這樣，同一張報表由于格式不同，內(nèi)容排列也不同，對同一張報表的加工、利用方式也就不同，從而使報表的使用效率降低。以兩個實例予以說明：

［例1］某企業(yè)行政部統(tǒng)計的本部門1月至4月Excel管理費用如圖1所示，該部門制表時從本部門用表及排版的方便性出發(fā)，將管理費用明細項目按行優(yōu)先的順序排列，然而報表提交會計部時，卻與會計賬簿管理費用明細賬的排列順序不一致，不方便相互核對，為此需要將該表的管理費用明細項目轉置為以列優(yōu)先的順序（見圖2）。

圖1

圖2

［例2］某注冊會計師審計時利用被審計單位財務軟件導出功能取得的Excel會計科目余額表如圖3，該表每個會計科目信息均占四行，每行描述一部分會計科目信息，在查找、統(tǒng)計或分析時，都不方便，為此需將該表轉置為每個會計科目信息只占一行的報表。

二、報表行列轉置方法的Excel應用

（1）簡單表的行列轉置。簡單表是指在一個數(shù)據(jù)表中一條記錄只占一行的表。簡單表由于結構簡單，因此可以通過函數(shù)法或復制——選擇性粘貼法實現(xiàn)行列轉置。

圖3

一是函數(shù)法。函數(shù)法是利用Excel函數(shù)TRANSPOSE（Array）實現(xiàn)報表行列轉置的方法。TRANSPOSE（Array）函數(shù)的作用是返回指定區(qū)域的轉置。函數(shù)TRANSPOSE（Array）必須在某個區(qū)域中以數(shù)組公式的形式輸入，該區(qū)域的行數(shù)和列數(shù)分別與Array的列數(shù)和行數(shù)相同。Array為需要進行轉置的數(shù)組或工作表中的單元區(qū)域。以圖1為例，若要轉置為圖2所示，首先根據(jù)源數(shù)據(jù)表區(qū)域A1：E17的列數(shù)（5列）和行數(shù)（17行）選中放置目的表的區(qū)域G1：W5（共5行、17列），在公式編輯欄輸入“=TRANSPOSE（A1：E17）”并按組合鍵“Ctrl+Shift+Enter”，以數(shù)組形式完成輸入，即完成了報表的行列轉置。

二是復制——選擇性粘貼法。復制——選擇性粘貼法是利用“復制”功能拷貝源數(shù)據(jù)表，利用“編輯”菜單的“選擇性粘貼”項得到目的數(shù)據(jù)表的方法，該方法比函數(shù)法便捷。以圖1為例，若要得到圖2所示，首先選中源數(shù)據(jù)表區(qū)域A1：E17并按組合鍵“Ctrl+C”復制，然后選中要放入目的表的單元，打開“編輯”菜單中的“選擇性粘貼”項，選中“轉置”復選項，點擊“確定”按鈕即可。

（2）復雜表的行列轉置。復雜表是指在一個數(shù)據(jù)表中一條記錄占兩行或兩行以上的表。利用Excel進行復雜表的行列轉置時，有兩種方法，分別是手工篩選法和參數(shù)篩選法。

一是手工篩選法。手工篩選法的思路是把轉置前是字段值，轉置后做為字段名稱的數(shù)據(jù)做為篩選條件，對源數(shù)據(jù)表依次篩選出需要的結果，并將每次篩選出的結果分別通過復制、粘貼功能拷貝到目的區(qū)域，從而得到轉置后的目的數(shù)據(jù)表。以圖3為例的手工篩選法操作過程是：通過數(shù)據(jù)篩選功能中的“自動篩選”，分別以源數(shù)據(jù)表中“數(shù)據(jù)項”字段的字段值——“年初余額”、“本期發(fā)生額”、“累計發(fā)生額”和“期末余額”做為篩選條件，進行四次篩選（見圖4），并將四次篩選的結果依次通過復制、粘貼功能拷貝到目的區(qū)域的方法。

圖4

二是參數(shù)篩選法。參數(shù)篩選法的思路是在目的數(shù)據(jù)表先利用公式得到對源數(shù)據(jù)表的第一條記錄值的引用，生成目的數(shù)據(jù)表的第一條記錄，再利用向下填充功能得到一個引用數(shù)據(jù)表。若源數(shù)據(jù)表有N條記錄，每條記錄占Q行，則引用數(shù)據(jù)表有（N×Q）行，其中從第一行開始，依次間隔（Q－1）行的共N行數(shù)據(jù)為有效數(shù)據(jù)，其余共N×（Q－1）行為無效數(shù)據(jù)，為了取得有效數(shù)據(jù)同時剔除無效數(shù)據(jù)，需要一個包含Q個數(shù)據(jù)的序列，且該序列循環(huán)N次產(chǎn)生N×Q個值依次對應引用數(shù)據(jù)表的每個行，即將包含N個循環(huán)的序列Q做為引用數(shù)據(jù)表的行標識，最后利用“自動篩選”功能對與引用數(shù)據(jù)表第一條記錄的行標識相同的記錄進行一次篩選，就能得到最終的目的數(shù)據(jù)表。以圖3為例的參數(shù)篩選法操作過程是：首先，在目的工作表將表頭制作完成；其次，在目的工作表最左側插入一個輔助列，并以“a”、“b”、“c”、“d”做為序列，利用填充柄向下填充，產(chǎn)生輔助列的值，該列的值依次為“a”、“b”、“c”、“d”、“a”、“b”、“c”、“d”、“a”、“b”、“c”、“d”、……；再次，在目的數(shù)據(jù)表第一條記錄所占的區(qū)域B2：K2分別輸入對源數(shù)據(jù)表第一條記錄對應值的引用“=源表!A2”、“=源表!B2”、“=源表!D2”、“=源表!E2”、“=源表!D3”、“=源表!E3”、“=源表!D4”、“=源表!E4”、“=源表!D5”、“=源表!E5”（見圖5）；最后，選中B2：K2區(qū)域，利用填充柄向下填充；第五，選擇“數(shù)據(jù)”——“篩選”——“自動篩選”菜單，以“輔助列”的值“a”做為篩選條件，在“輔助列”所在的下拉列表中選擇“a”，即得到轉置后的數(shù)據(jù)表。

圖5

報表行列轉置是對報表內(nèi)容的重新排列，這種排列沒有改變報表的內(nèi)容，只是變換了報表的排列順序，因此不會增加或減少數(shù)據(jù)容量，但為使用者提供了可以按需求和習慣排列和使用報表的方法。另外，報表行列轉置的四種方法：函數(shù)法、復制——選擇性粘貼法、手工篩選法和參數(shù)篩選法，也可由使用者根據(jù)需求和習慣自行選用。

［1］耿萍、楊虹：《Excel在財務管理中的應用技術》，中國鐵道出版社2002年版。

［2］宇傳華、顏杰：《Excel與數(shù)據(jù)分析》，電子工業(yè)出版社2002年版。

（編輯 代 娟）