整理Excel數據

02-05

1、行列互換

需要將數據的行和列對調過來時，可以用 TRANSPOSE 轉置函數。

萬能配方：

? TRANSPOSE 是數組函數，必須先選中區域，輸入公式後按【Ctrl+Enter】完成輸入才能生效。

? 案例第 10 行中因為源數據沒有更多數據，產生了錯誤值，這些錯誤值不能單獨刪除，因為數組區域是一個整體，必須同時修改。

其實，普通的行列互換轉置，用選擇性粘貼更簡單，還可以保留源格式。So，函數公式雖好，可不要貪多哦 ~

2、一行轉多行

想要把一行數據轉換成多行數據怎麼辦？比如，轉換成固定 3 列的多行數據：

萬能配方：

這是一個萬能的函數公式，你不需要懂太多，只要會改參數就能套用。

=INDEX($A$1:$G$1,1,(ROW(A1)-1)*3+COLUMN(A1))

? 把紅色的數據區域換成你的數據區域

? 把 3 換成一行希望填寫的數據個數，比如一行 4 個，就換成 4。

3、一列轉多列

相對應的，如果想要把一列數據按固定的行數轉成多列怎麼辦呢？還是一樣的配方，稍微改造一下公式結構，就能能套用：

萬能配方：

這同樣是一個萬能的公式結構，只需要改變兩個參數，就能套用

=INDEX($A$1:$A$7,ROW(A1)+(COLUMN(A1)-1)*3,1)

? 把紅色的數據區域換成你的數據區域

? 把 3 換成一列希望填寫的數據個數，比如一列 2 個，就換成 2。

4、多行轉一行

一行轉多行可以，那反過來多行轉一行行不行？當然可以，看效果：

萬能配方：

這裡用的公式看起來更複雜了，但是要做的事情反而更簡單了，只需要將下方公式中 3 個一樣的數據區域，統一換成你要轉換的數據源，就可以直接套用。

=INDEX($A$1:$B$3,INT((COLUMN(A1)-1)/COLUMNS($A$1:$B$3))+1,MOD(COLUMN(A1)-1,COLUMNS($A$1:$B$3))+1)

5、多列轉一列

你應該已經猜到了，多列轉一列，也很簡單：

萬能配方：

照貓畫虎似的，將下方公式中的標紅數據區域統一換掉，就能直接套用

=INDEX($A$1:$B$3,MOD(ROW(A1)-1,ROWS($A$1:$B$3))+1,INT((ROW(A1)-1)/ROWS($A$1:$B$3))+1)

6、交叉轉換

下面這個表格中的數據就比較棘手了，所有的人員信息都分成了兩列，一列是人員信息類別的名稱，一列是具體的信息。怎麼把他們轉成一個人的信息為一行的數據記錄呢？

這是一個函數公式的高級應用：萬能配方：

案例效果中涉及 3 個函數公式，都是非常經典的套路。

? 利用 COUNTIF 函數自動分組

=COUNTIF(數據區域,條件)

案例公式 COUNTIF($B$2:B2,$B$2) 中第一個參數 $B$2:B2 表示鎖定起點為 B2 ，但終點跟隨公式自動變化的動態區域；第二個參數則是計數條件。公式含義為，計算 B 列第 2 行到當前公式所在行中，值和 B2 相等的單元格數量。結果就是每一行信息屬於第幾個人的。

? 利用LOOKUP 函數實現多條件查找匹配

=LOOKUP(1,0/((條件1)*(條件2)*(條件N)),匹配區域)

案例中的公式為 Lookup(1,0/(($D$2:$D$14=$F2)*($B$2:$B$14=G$1)),$C$2:$C$14)。用到了兩個條件，D 列中的數據等於公式所在行的組名,B 列中的數據等於公式所在列的標題。返回同時滿足 2 個條件時，C 列中的數據，也就是每一個人的具體信息。

? 利用 IFERROR 屏蔽錯誤值

=IFERROR(原公式,出錯時返回另一個結果)

當原公式結果出錯時，讓整個公式結果等於橫杠符號。實現暗度陳倉的效果。