新函數(shù)分享第3篇。將多行多列數(shù)據(jù)轉(zhuǎn)成單列或者單行排列，以往操作或者公式復(fù)雜，現(xiàn)在用TOCOL或TOROW函數(shù)非常簡(jiǎn)單。與之類似，二維表轉(zhuǎn)一維表——逆透視表格——也變得很簡(jiǎn)單。

1.TOCOL

1)作用與語法

將多行多列數(shù)據(jù)排成一列。

=TOCOL(數(shù)組,[是否忽略空白與錯(cuò)誤值],[從數(shù)組中取數(shù)的方式])

第一參數(shù)，數(shù)組，可以是直接引用的，也可以是其他函數(shù)生成的數(shù)組。

第二參數(shù)，是否忽略空白與錯(cuò)誤值。

0（也是默認(rèn)值），表示不忽略，會(huì)保留所有數(shù)據(jù)；

1，表示忽略空白；

2，表示忽略錯(cuò)誤值；

3，表示忽略空白和錯(cuò)誤值。

第三參數(shù)，從數(shù)組中取值方式。

0（也是默認(rèn)值），表示逐行取值；

1，表示逐列取值。

2）基本用法

（1）不忽略任何值

數(shù)組中的錯(cuò)誤值被保留，空單元格用0取代。

按行取值

=TOCOL(A2:C6)

按列取值

=TOCOL(A2:C6,,1)

（2）忽略空白

=TOCOL(A2:C6,1)

（3）忽略錯(cuò)誤值和空單元格

=TOCOL(A2:C6,3)

2.TOROW

1)作用與語法

將多行多列數(shù)據(jù)排列成一行。

=TOROW(數(shù)組,[是否忽略空白與錯(cuò)誤值],[從數(shù)組中取數(shù)的方式])

參數(shù)含義與TOCOL一致。

2）基本用法

（1）不忽略任何值

空單元格用0取代，錯(cuò)誤值保留。

（2）忽略空單元格和錯(cuò)誤值

=TOROW(A23:B26,3)

3.典型應(yīng)用

1）將多個(gè)數(shù)組排成單列或單行

譬如將下方4個(gè)數(shù)組排成一列?？梢杂美ㄌ?hào)將需要排列的多個(gè)數(shù)組括起來，各數(shù)組之間用逗號(hào)分開（注：只有直接引用或者通過函數(shù)間接引用的數(shù)組可以如此，其他的常量數(shù)組、運(yùn)算生成的數(shù)組都不能）。剩下的就交給TOCOL。

=TOCOL((A2:B4,D2:F2,H2:H6,D5:E5),3)

說明：依次把第一參數(shù)中的每個(gè)數(shù)組都按行取值排成單列并忽略空單元格和錯(cuò)誤值。

如果要排成單行，把公式中的TOCOL換成TOROW即可。

2）提取多行多列數(shù)據(jù)中的唯一值

譬如從下方值班表中提取人員名單。

=UNIQUE(TOCOL(B23:E27,1))

3）逆透視——二維表轉(zhuǎn)一維表

譬如將下方的成績(jī)二維表轉(zhuǎn)成一維表，過往采用ALT+D——P“數(shù)據(jù)透視表和透視圖向?qū)А边M(jìn)行轉(zhuǎn)化，操作步驟多?，F(xiàn)在可以更簡(jiǎn)單完成。

=VSTACK({"姓名","科目","成績(jī)"},HSTACK(TOCOL(IF(B46:D50<>"",A46:A50)),TOCOL(IF(B46:D50<>"",B45:D45)),TOCOL(

B46:D50)))

說明：

①IF(B46:D50<>"",A46:A50)，得到3列5行的姓名數(shù)組“楊過,楊過,楊過；郭芙,郭芙,郭芙；孫少平,……”。這里看不懂的請(qǐng)看《IF函數(shù){1,0}結(jié)構(gòu)原理和用法》中的數(shù)組邏輯。

②TOCOL(①),得到按行取值的一列姓名。

③TOCOL(IF(B46:D50<>"",B45:D45))，得到1列15行按“語文；數(shù)學(xué)；英語”循環(huán)的科目列。

④TOCOL(B46:D50)，得到按行取值的一列成績(jī)。

⑤HSTACK(②, ③, ④)，將姓名列、科目列、成績(jī)列在水平方向上組合成一個(gè)3列15行的數(shù)組。

⑥VSTACK({"姓名","科目","成績(jī)"},⑤),將數(shù)組⑤添加上標(biāo)題。

新函數(shù)TOCOL和TOROW的用法就介紹這么多。逆透視二維表是它們的典型應(yīng)用。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

多列數(shù)據(jù)轉(zhuǎn)成兩列

用數(shù)據(jù)透視表將二維表轉(zhuǎn)一維表

UNIQUE去重復(fù)提取經(jīng)典案例

為什么完整的自定義格式代碼是四段

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。