Hi，我是偏愛(ài)函數(shù)公式，愛(ài)用 Excel 圖表管理倉(cāng)庫(kù)的大叔 Mr 趙~
在實(shí)際工作中，我們經(jīng)常需要對(duì)數(shù)據(jù)的結(jié)構(gòu)進(jìn)行轉(zhuǎn)換。
比如，為了更直觀、方便查看數(shù)據(jù)，需要將下圖左邊一維表轉(zhuǎn)換成如右圖的二維表：
又或者為了更好的統(tǒng)計(jì)分析數(shù)據(jù)，需要將左邊的二維表數(shù)據(jù)轉(zhuǎn)換成右邊的一維表格式：
下面就來(lái)詳細(xì)說(shuō)說(shuō)，怎么用函數(shù)公式實(shí)現(xiàn)這兩種數(shù)據(jù)結(jié)構(gòu)的相互轉(zhuǎn)換。
1、一維表轉(zhuǎn)化成二維表
? 提取年級(jí)變成標(biāo)題行
在 D2 輸入公式：
=TRANSPOSE(UNIQUE(A2:A34))
首先用 UNIQUE 函數(shù)提取 A 列不重復(fù)的年級(jí)變成一列，再用 TRANSPOSE 函數(shù)將得到的一列數(shù)據(jù)轉(zhuǎn)置成一行。
效果如下圖所示：
? 提取對(duì)應(yīng)的名單
在 D2 單元格輸入公式：
=FILTER($B2:$B34,$A2:$A34=D1)
用 FILTER 函數(shù)，以年級(jí)作為篩選條件，篩選出對(duì)應(yīng)的名單。再將公式向右拖動(dòng)填充，得到如下圖右表的效果：
2、二維表轉(zhuǎn)化成一維表
? 首先用 IF 函數(shù)判斷名單區(qū)域「A2:D15」是否為空；如果為空則返回錯(cuò)誤值 (#NAME?)，否則返回第一行「A1:D1」對(duì)應(yīng)的年級(jí)。
在 F1 單元格輸入公式：
=IF(A2:D15="",x,A1:D1)
結(jié)果返回一個(gè)多行 4 列的數(shù)組，效果如下圖「F1:I14」區(qū)域所示：
? 然后利用 TOCOL 函數(shù)將這組多行 4 列的數(shù)組轉(zhuǎn)化成一列。
=TOCOL( IF(A2:D15 = "", x, A1:D1), 2, 1 )
效果如下圖 F 列所示：
TOCOL 是 Office 365 版本新增的函數(shù)，非常實(shí)用和強(qiáng)大，它可以將多數(shù)組轉(zhuǎn)化為一列數(shù)據(jù)。
該函數(shù)的語(yǔ)法為：
=TOCOL(array, [ignore], [scan_by_column])
第一參數(shù)是需要轉(zhuǎn)化成列的數(shù)組，公式中 TOCOL 函數(shù)的第一個(gè)參數(shù) IF (A2:D15 = "", x, A1:D1) 是需要轉(zhuǎn)化的數(shù)組；
第二參數(shù)可以選擇是否忽略空白或錯(cuò)誤，公式中的第二個(gè)參數(shù)是 2，表示忽略區(qū)域中的錯(cuò)誤值；
第三參數(shù)表示掃描方式，可以設(shè)定是按行方向掃描數(shù)組還是按列方向掃描數(shù)組，默認(rèn)情況下按行掃描，如果要按列掃描，則值為 TRUE 或 1。
? 最后再用 TOCOL 函數(shù)將姓名區(qū)域「A2:D15」，也轉(zhuǎn)化成一列。
在 G1 單元格輸入公式：
=TOCOL(A2:D15,1,1)
公式中的第二參數(shù)是 1，表示忽略區(qū)域「A2:D15」中的空白，轉(zhuǎn)化成一列。效果如下圖 G 列所示：
3、最后小結(jié)
? 一維表轉(zhuǎn)化成二維表：首先用 UNIQUE 函數(shù)提取一列的不重復(fù)值，作為標(biāo)題行；然后用 FILTER 函數(shù)，以標(biāo)題作為篩選條件，提取對(duì)應(yīng)的內(nèi)容。
? 二維表格轉(zhuǎn)化成一維表：當(dāng) TOCOL 函數(shù)第二參數(shù)為 2 時(shí)，忽略錯(cuò)誤值，將標(biāo)題行轉(zhuǎn)化成一列，再利用 TOCOL 函數(shù)第二參數(shù)為 1 時(shí)，忽略空白，將對(duì)應(yīng)的區(qū)域轉(zhuǎn)化成一列。
好了，今天就說(shuō)到這里結(jié)束啦~
本文來(lái)自微信公眾號(hào)：秋葉 Excel （ID：excel100），作者：趙驕陽(yáng)
廣告聲明：文內(nèi)含有的對(duì)外跳轉(zhuǎn)鏈接（包括不限于超鏈接、二維碼、口令等形式），用于傳遞更多信息，節(jié)省甄選時(shí)間，結(jié)果僅供參考，IT之家所有文章均包含本聲明。