Hi,我是偏爱函数公式,爱用 Excel 图表管理仓库的大叔 Mr 赵~

今天向大家介绍一个相当炸裂,并且 Excel 和 WPS 都有的新函数 – TOCOL

它的作用是将区域的数据转换成一列

举个例子,用 TOCOL 函数提取下图左表红框内的姓名,并转换成一列。

公式:

=TOCOL(B3:E5,3,1)

Excel 函数 TOCOL 介绍-风君雪科技博客

TOCOL 函数有 3 个参数,语法格式为:

=TOCOL (数据区域,[忽略空白和错误],[指定行 / 列扫描])

解析一下 F2 单元格的公式:

第一个参数:B3:E5 表示待转换的区域;

第二参数:3 表示忽略第一个参数 B3:E5 中的错误值和空白;

Excel 函数 TOCOL 介绍-风君雪科技博客

第三个参数:1 表示将 B3:E5 按垂直的方向一列一列的扫描转换。

Excel 函数 TOCOL 介绍-风君雪科技博客

通过这个示例演示,相信大家已经对 TOCOL 函数有了基本的了解。

接下来我就和大家一起分享,它在各种实际场景中的运用。

1、从合并单元格中提取信息

如下图,从带有合并单元格的 B 列提取姓名:

=TOCOL(B3:B9,1)

用 TOCOL 函数转换 B3:B9 为 1 列;第二个参数为 1 表示忽略第一个参数 B3:B9 的空白部分。

Excel 函数 TOCOL 介绍-风君雪科技博客

2、删除错误值求和

如下图,用 SUM 函数对 B 列的数据求和。

但因为数据区域存在错误值,如果直接求和,将无法得出正确结果。

Excel 函数 TOCOL 介绍-风君雪科技博客

这时我们就可以在公式中嵌套一个 TOCOL 函数,先删除错误值,再求和。

=SUM(TOCOL(B3:B12,2))

Excel 函数 TOCOL 介绍-风君雪科技博客

3、条件筛选

如下图,筛选 B 列菜单中含 “鱼” 的菜名。

Excel 函数 TOCOL 介绍-风君雪科技博客

❶ 首先用 FIND 函数查找字符「鱼」在 B 列每个菜名中的位置,如果找到就会返回数值,否则就会返回错误值,结果返回一组由数字和错误值组成的数组:

Excel 函数 TOCOL 介绍-风君雪科技博客

❷ 再用 IF 函数判断这列数组,如果是数值就返回 B 列菜单中的菜名:

Excel 函数 TOCOL 介绍-风君雪科技博客

❸ 最后用 TOCOL 函数删除错误值,筛选出含 “鱼” 的菜名:

=TOCOL(
    IF(FIND("鱼", B3:B12), B3:B12),
            2)

Excel 函数 TOCOL 介绍-风君雪科技博客

怎么样?TOCOL 是个很有用的函数吧?

4、重复标签个数

如下图,【标签】按指定【次数】重复。

Excel 函数 TOCOL 介绍-风君雪科技博客

❶ 首先用 COLUMN 函数构造一组水平的序列值,再与 C 列的次数比较,结果生成一组多行多列的逻辑值。

Excel 函数 TOCOL 介绍-风君雪科技博客

❷ 如果逻辑值是 TRUE,就用 IF 函数返回错误值,否则返回 B 列的标签名称。

Excel 函数 TOCOL 介绍-风君雪科技博客

❸ 最后用 TOCOL 函数忽略错误值转成 1 列:

=TOCOL(IF(COLUMN(A:Z)>C3:C5,x,B3:B5),2)

Excel 函数 TOCOL 介绍-风君雪科技博客

5、数据组合

将下图中 B 列的年级和 D 列的班组合。

Excel 函数 TOCOL 介绍-风君雪科技博客

❶ 首先用 TOROW 函数将 D 列的班转换成一行。

Excel 函数 TOCOL 介绍-风君雪科技博客

❷ 然后用 B 列的年级与这一行数组连接,得到一个多行多列的数组。

Excel 函数 TOCOL 介绍-风君雪科技博客

❸ 最后用 TOCOL 函数将数组转换成一列。

=TOCOL(B3:B5&TOROW(D3:D7))

Excel 函数 TOCOL 介绍-风君雪科技博客

6、表格转换

如下图,将下图中的组别和姓名合并转换成一列:

Excel 函数 TOCOL 介绍-风君雪科技博客

❶ 首先用 TOCOL 函数将左表两列数据转换成一列:

Excel 函数 TOCOL 介绍-风君雪科技博客

❷ 然后用 UNIQUE 函数,提取这列的不重复值。

=UNIQUE(TOCOL(B2:C17))

Excel 函数 TOCOL 介绍-风君雪科技博客

通过以上案例的分享,足以说明 TOCOL 函数是处理数据的好帮手,

它既可以剔除数据中的空白和错误值,从而提取有效信息;又能与其它函数嵌套,对数据进行格式转换,组合等一系列的操作

下面给大家留下一道练手题:

如下图,将左边的二维表,转换成如右图的一维表格式。

Excel 函数 TOCOL 介绍-风君雪科技博客

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:赵骄阳