無懼轉(zhuǎn)換輕松完成Excel表格的轉(zhuǎn)換

平淡

通過函數(shù)快速完成轉(zhuǎn)換

從圖1中可以看出，左半部分每個員工的數(shù)據(jù)結(jié)構(gòu)是一致的，所以我們只要在右側(cè)的單元格中通過函數(shù)對原始數(shù)據(jù)進(jìn)行引用就能完成轉(zhuǎn)換。步驟如下：

1將F列設(shè)為輔助列，定位到F2單元格并輸入公式“=OFFSET（A$2，INT（（ROW（A1）-1） /4），MOD（ROW（A1）-1， 4））”，下拉填充到F49單元格（即直到最后一直出現(xiàn)“0，0，0，0”數(shù)據(jù)為止）。這樣就會從F2單元格開始以A 2單元格中的數(shù)據(jù)作為第一個填充數(shù)據(jù)，按照從左到右再向下的順序?qū)⒈砀褡蟀氩糠种械膬?nèi)容依次引用到F列中（圖2）。

公式解釋：

這里先將A $ 2（對行的絕對引用）作為OFFSET函數(shù)引用的基準(zhǔn)，接著使用INT函數(shù)向下取整的數(shù)值作為OF FS E T函數(shù)向下偏移的行數(shù)，比如在F3單元格中取整部分的公式為“INT（（ROW（A 2）-1）/4）”，計算結(jié)果是“0”，即向下偏移0行（取第二行的值）。再使用MOD函數(shù)求得的余數(shù)作為OFFSET函數(shù)向右偏移的列數(shù)，同樣在F3單元格中求余數(shù)的結(jié)果為M O D（ROW（A1）-1，4）=1，這樣會向右偏移1列，即最終數(shù)值為“張三”。

2定位到H2單元格并輸入公式“=INDEX（$F$2：$F$49，IF（MOD（ROW（$F2）*12，12）=0，COLUMN（A$1）+ROW（$A1）*12-12））”，向下和向右填充到H5和O5單元格，這樣F列中的數(shù)據(jù)就會轉(zhuǎn)換成每個員工信息為一行的格式（圖3）。

公式解釋：

這里先將$F$2：$F$49（對數(shù)據(jù)區(qū)域的絕對引用）作為INDEX函數(shù)引用的區(qū)域，接著使用IF函數(shù)引用的數(shù)據(jù)作為引用的單元格，填充公式后即可實現(xiàn)從H列到O列依次引用F列中的數(shù)據(jù)。

接下來隱藏F列。通過這樣的轉(zhuǎn)換，我們再對員工信息進(jìn)行排序和篩選就方便多了。

使用Word協(xié)助完成轉(zhuǎn)換

很多時候我們需要轉(zhuǎn)換的表格數(shù)據(jù)結(jié)構(gòu)并不一致，比如下圖的左側(cè)是某班組的原始加班數(shù)據(jù)，現(xiàn)在需要轉(zhuǎn)換成右側(cè)的按照“帶班班長”順序進(jìn)行排列的格式（圖4）。由于每個班組的加班人數(shù)不同，使用上述的方法無法完成轉(zhuǎn)換。

這時我們可以先為每個帶班班長添加標(biāo)記，然后在Word中通過對這個標(biāo)記進(jìn)行分段排列的方法來完成轉(zhuǎn)換。步驟如下：

1先在Excel中插入一個A列作為標(biāo)記列，定位到A2單元格并輸入公式“=IF（C2="帶班班長"，"a"，"b"）”（表示使用IF函數(shù)對C列中的內(nèi)容進(jìn)行判斷，如果單元格值為“帶班班長”就標(biāo)記為“a”，否則標(biāo)記為“b”），下拉填充到A19單元格。

2在F2單元格中輸入公式“= OFFSET（A$2，INT（（ROW（B1）-1） /4），MOD（ROW（B1）-1，4））”，下拉填充到F73單元格;在H2單元格中輸入公式“=INDEX（$F$2：$F$73，IF（MOD（ROW（$F2）*12，12）=0，COLUMN（A$1）+ROW（$A1）*12-12））”，向右填充到CA2單元格。這樣原始數(shù)據(jù)就會全部填充到H2：CA2區(qū)域中了（圖5）。

3復(fù)制H2：CA 2區(qū)域，將數(shù)據(jù)以文本的方式粘貼到Word 2019的新建文檔中。接著在Word中按下“Ctrl+H”快捷鍵打開“查找和替換”窗口，在“查找內(nèi)容”處輸入“a”、“替換為”處輸入“^p”，然后點(diǎn)擊“全部替換”按鈕（圖6）。

4再次打開“查找和替換”窗口，在“查找內(nèi)容”處輸入“b ”（字母后有一個制表符，可在記事本的任意兩個字符之間按下Tab鍵，然后復(fù)制出現(xiàn)的空白字符并粘貼到此）、“替換為”處留空，點(diǎn)擊“全部替換”按鈕。完成替換后每個段落中的內(nèi)容就會全部以“xx 帶班班長”開頭，每個班組為一個獨(dú)立的段落進(jìn)行顯示了（圖7）。

5復(fù)制整理后的所有數(shù)據(jù)，再將其粘貼到Excel文檔中，即可實現(xiàn)如圖所示的效果（圖8）。