在日常工作中，經(jīng)常會(huì)碰到被審計(jì)單位提供的數(shù)據(jù)與要求上報(bào)的數(shù)據(jù)格式不一致的情況，這就需要進(jìn)行行列轉(zhuǎn)換。但對(duì)于具有姓名、身份證號(hào)等多個(gè)屬性的數(shù)據(jù)行列轉(zhuǎn)換，無(wú)論用哪種常規(guī)方法來實(shí)現(xiàn)，均難度較大且費(fèi)時(shí)費(fèi)力。筆者在實(shí)踐中運(yùn)用EXCEL2016的“函數(shù)+透視”功能快速完成數(shù)據(jù)標(biāo)準(zhǔn)化整理，本文以某次審計(jì)中遇到的村干部及家屬信息數(shù)據(jù)轉(zhuǎn)換為例，分享操作經(jīng)驗(yàn)。

一、數(shù)據(jù)整理需求分析

在該次審計(jì)中，雖然被審計(jì)單位提供的數(shù)據(jù)（見圖1）滿足標(biāo)準(zhǔn)表（見圖2）要求，但仍然存在著字段值不規(guī)范和表格格式不一致的問題。

圖1

圖2

1.標(biāo)準(zhǔn)表中要求填報(bào)的內(nèi)容為父親、母親、配偶、子女1、子女2……而現(xiàn)有數(shù)據(jù)中為長(zhǎng)子、次子、小女、女婿、兒媳等，需要剔除無(wú)用內(nèi)容，并規(guī)范保留字段內(nèi)容。

2.兩表都以村干部信息為關(guān)鍵字，但標(biāo)準(zhǔn)表中村干部家屬信息為橫向排列，而現(xiàn)有數(shù)據(jù)中是縱向排列，需要將行數(shù)據(jù)轉(zhuǎn)為列顯示。

二、相應(yīng)解決辦法

（一）表格內(nèi)容整理

1.清洗并規(guī)范數(shù)據(jù)。利用篩選功能，將表中與村干部關(guān)系中的數(shù)據(jù)按照父親、母親、配偶、子女四類進(jìn)行統(tǒng)一，剔除無(wú)用信息，如圖3所示。

圖3

2.對(duì)子女進(jìn)行編號(hào)。按照標(biāo)準(zhǔn)表要求，用COUNTIFS函數(shù)將同一位村干部的多名子女編號(hào)，分為子女1、子女2……

（1）如圖4所示，在J2單元格輸入公式:=IF(I2="子女",COUNTIFS($E$2:E2,E2,$I$2:I2,"子女"),"")，意思是當(dāng)前單元格為“子女”時(shí)，統(tǒng)計(jì)從E2單元格到當(dāng)前單元格為同一村干部身份證號(hào)出現(xiàn)的次數(shù)。

圖4

（2）利用函數(shù)將子女與順序號(hào)合并得到子女序號(hào)，如圖5所示。

圖5

（二）與村干部關(guān)系整理

1.如圖6所示，選擇“數(shù)據(jù)”-“獲取和轉(zhuǎn)換”-“從表格”，進(jìn)入POWER QUERY編輯器。

圖6

2.如圖7所示，選擇“家屬姓名”、“家屬身份證號(hào)”兩列進(jìn)行逆透視。

圖7

3.如圖8所示，選擇“屬性”列，利用“轉(zhuǎn)換”-“替換值”，將“家屬姓名”和“家屬身份證號(hào)”中的家屬兩字替換掉。

圖8

4.如圖9所示，選中“與村干部關(guān)系”和“屬性”列，利用“轉(zhuǎn)換”-“合并列”構(gòu)造出標(biāo)準(zhǔn)表所需字段。

圖9

5.如圖10所示，選中“已合并”和“值”列，執(zhí)行“轉(zhuǎn)換”-“透視列”，在彈出窗口中，選擇“值列”為“值”，將使用列“已合并”中的名稱創(chuàng)建新列，“聚合值函數(shù)”選擇“不要聚合”。

圖10

6.點(diǎn)擊“確定”并按標(biāo)準(zhǔn)表調(diào)整字段順序后就得到所需的標(biāo)準(zhǔn)表，如圖11所示。選擇“關(guān)閉并上載”，將結(jié)果保存到EXCEL中。

圖11