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

1.TOCOL

1)作用與語法

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

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

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

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

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

1，表示忽略空白；

2，表示忽略錯誤值；

3，表示忽略空白和錯誤值。

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

0（也是默認值），表示逐行取值；

1，表示逐列取值。

2）基本用法

（1）不忽略任何值

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

按行取值

=TOCOL(A2:C6)

按列取值

=TOCOL(A2:C6,,1)

（2）忽略空白

=TOCOL(A2:C6,1)

（3）忽略錯誤值和空單元格

=TOCOL(A2:C6,3)

2.TOROW

1)作用與語法

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

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

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

2）基本用法

（1）不忽略任何值

空單元格用0取代，錯誤值保留。

（2）忽略空單元格和錯誤值

=TOROW(A23:B26,3)

3.典型應用

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

譬如將下方4個數(shù)組排成一列?？梢杂美ㄌ枌⑿枰帕械亩鄠€數(shù)組括起來，各數(shù)組之間用逗號分開（注：只有直接引用或者通過函數(shù)間接引用的數(shù)組可以如此，其他的常量數(shù)組、運算生成的數(shù)組都不能）。剩下的就交給TOCOL。

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

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

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

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

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

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

3）逆透視——二維表轉一維表

譬如將下方的成績二維表轉成一維表，過往采用ALT+D——P“數(shù)據透視表和透視圖向導”進行轉化，操作步驟多。現(xiàn)在可以更簡單完成。

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

B46:D50)))

說明：

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

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

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

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

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

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

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

本文配套的練習課件請加入QQ群：902294808下載。

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

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

多列數(shù)據轉成兩列

用數(shù)據透視表將二維表轉一維表

UNIQUE去重復提取經典案例

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

版權申明：

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