我們在平時處理數據的過程中，經常會遇到如下圖1的數據形式，類似的數據格式在下一步的各種數據分析過程中是很不方便的，為此我們常常需要將數據轉換為圖2的形式。

圖1

圖2

那麼，我們如何能夠快速的將數據轉換為圖2要求的格式呢？下面介紹兩種方法。

一、公式法

使用IF、SMALL、RIGHT、TEXT、ROW、COLUMN、INDIRECT函數的配合，可以實現本功能。公式如下：

部門：=INDIRECT(TEXT(RIGHT(SMALL(IF($A$2:$E$16<>"",ROW($2:$16)*10+COLUMN($A:$E)*10001,99999),ROW(A1)),1),"r1c0"),)&""

姓名：=INDIRECT(TEXT(RIGHT(SMALL(IF($A$2:$E$16<>"",ROW($2:$16)*10+COLUMN($A:$E)*10001,99999),ROW(A1)),3),"r0c0"),)&""

結果為：

該公式看起來非常複雜，下面以姓名為例對該公式做一下解釋：

$A$2:$E$16<>""：根據A2：E16是否為空，生成一個邏輯數組，該數組為15行5列。{TRUE,TRUE,TRUE,TRUE,TRUE; TRUE,TRUE,TRUE,TRUE,TRUE;……}，表格示意如下：

ROW($2:$16)*10：行數組成的數組,該數組為15行1列。{20;30;40……160}；

COLUMN($A:$E)*10001：列數組成的數組，該數組為1行5列。{10001,20002,3003,40004,50005}；

ROW($2:$16)*10+COLUMN($A:$E)*10001：以上兩個數組相加，根據數組運算法則，生成15行5列的數組。{10021,20022,30023,40024,50025;10031……}。表格示意如下：

IF():根據前面的邏輯數組，如果為空的全部填充為99999。{10021,20022,30023,40024,50025;10031……}，表格示意如下：

SMALL(……,ROW(A1))：依次從小到大取出上述數組中的數據。

RIGHT(……,3):取出上述數據的後3位，上述的數據中最後一位表示單元格的列號，倒數第二位表示行號。

TEXT(……,"r0c0"):將上一步取出來的數字格式化為RC格式的單元格引用。

INDIRECT（……,）：將文本變為單元格的引用，第二個參數省略代表引用為RC模式。

二、POWER QUERY工具

Excel官方發佈了POWER QUERY工具，可以更加方便的對數據進行處理，在Excel 2016中已經自帶了本工具，其他版本的Excel可以在微軟官方網站下載安裝包。

下面就演示使用該工具實現上述的功能，在工具面板中選擇"從表/範圍"，彈出編輯PQ的編輯界面。

在【轉換】選項卡下選擇【逆透視列】，就可以得到需要的結果啦，然後將結果上載回工作表。

怎麼樣，PQ工具的實現是不是很簡單呢？同時，該工具還提供了其他非常多的使用功能呢，大家可以多瞭解一下，同時再後期我也會持續給大家分享相關功能，希望大家一如既往的關注喲。

閱讀更多 Excel函數公式技巧 的文章

關鍵字: Excel 技術 中是