新函数分享第3篇。将多行多列数据转成单列或者单行排列，以往操作或者公式复杂，现在用TOCOL或TOROW函数非常简单。与之类似，二维表转一维表——逆透视表格——也变得很简单。

1.TOCOL

1)作用与语法

将多行多列数据排成一列。

=TOCOL(数组,[是否忽略空白与错误值],[从数组中取数的方式])

第一参数，数组，可以是直接引用的，也可以是其他函数生成的数组。

第二参数，是否忽略空白与错误值。

0（也是默认值），表示不忽略，会保留所有数据；

1，表示忽略空白；

2，表示忽略错误值；

3，表示忽略空白和错误值。

第三参数，从数组中取值方式。

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

1，表示逐列取值。

2）基本用法

（1）不忽略任何值

数组中的错误值被保留，空单元格用0取代。

按行取值

=TOCOL(A2:C6)

按列取值

=TOCOL(A2:C6,,1)

（2）忽略空白

=TOCOL(A2:C6,1)

（3）忽略错误值和空单元格

=TOCOL(A2:C6,3)

2.TOROW

1)作用与语法

将多行多列数据排列成一行。

=TOROW(数组,[是否忽略空白与错误值],[从数组中取数的方式])

参数含义与TOCOL一致。

2）基本用法

（1）不忽略任何值

空单元格用0取代，错误值保留。

（2）忽略空单元格和错误值

=TOROW(A23:B26,3)

3.典型应用

1）将多个数组排成单列或单行

譬如将下方4个数组排成一列。可以用括号将需要排列的多个数组括起来，各数组之间用逗号分开（注：只有直接引用或者通过函数间接引用的数组可以如此，其他的常量数组、运算生成的数组都不能）。剩下的就交给TOCOL。

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

说明：依次把第一参数中的每个数组都按行取值排成单列并忽略空单元格和错误值。

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

2）提取多行多列数据中的唯一值

譬如从下方值班表中提取人员名单。

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

3）逆透视——二维表转一维表

譬如将下方的成绩二维表转成一维表，过往采用ALT+D——P“数据透视表和透视图向导”进行转化，操作步骤多。现在可以更简单完成。

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

B46:D50)))

说明：

①IF(B46:D50<>"",A46:A50)，得到3列5行的姓名数组“杨过,杨过,杨过；郭芙,郭芙,郭芙；孙少平,……”。这里看不懂的请看《IF函数{1,0}结构原理和用法》中的数组逻辑。

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

③TOCOL(IF(B46:D50<>"",B45:D45))，得到1列15行按“语文；数学；英语”循环的科目列。

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

⑤HSTACK(②, ③, ④)，将姓名列、科目列、成绩列在水平方向上组合成一个3列15行的数组。

⑥VSTACK({"姓名","科目","成绩"},⑤),将数组⑤添加上标题。

新函数TOCOL和TOROW的用法就介绍这么多。逆透视二维表是它们的典型应用。

本文配套的练习课件请加入QQ群：902294808下载。

做Excel高手，快速提升工作效率，部落窝教育Excel精品好课任你选择！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

多列数据转成两列

用数据透视表将二维表转一维表

UNIQUE去重复提取经典案例

为什么完整的自定义格式代码是四段

版权申明：

本文作者小窝；部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。