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

報(bào)表行列轉(zhuǎn)置Excel應(yīng)用淺析

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

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

一、報(bào)表行列轉(zhuǎn)置應(yīng)用例析

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

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

圖1

圖2

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

二、報(bào)表行列轉(zhuǎn)置方法的Excel應(yīng)用

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

圖3

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

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

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

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

圖4

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

圖5

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

［1］耿萍、楊虹：《Excel在財(cái)務(wù)管理中的應(yīng)用技術(shù)》，中國(guó)鐵道出版社2002年版。

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

（編輯 代 娟）