如何用公式将多列内容合并为一列
送人玫瑰,手有余香,请将文章分享给更多朋友
动手操作是熟练掌握EXCEL的最快捷途径!
经常有朋友会私信我,问如何用公式将多列内容合并为一列内容。例如下面的例子。
相对来讲,使用公式会比较复杂,要用到多维引用的技术。下面,我们就这个例子向大家介绍如何使用公式来完成任务。
首先,我们复制标题“单据类型”到单元格G1,如下图。
在单元格G1中输入公式“=IFERROR(INDIRECT(TEXT(MOD(SMALL(IF(($G$1=$A$1:$E$1)*($A$2:$E$16<>""),ROW($A$2:$E$16)/1%+COLUMN($A:$E)*10001),ROW(A1)),10^4),"r0c00"),),"")”,三键回车并向下拖曳即可。
思路:
($G$1=$A$1:$E$1)*($A$2:$E$16<>"")部分,是这个题目的两个条件,标题名要等于“单据类型”,同时单元格不为空
在满足上述条件下,IF函数返回ROW($A$2:$E$16)/1%+COLUMN($A:$E)*10001这样的结果。它表示将数据区域的行号扩大100倍,数据区域的列号扩大10001倍,同时两者相加,结果是一个5位数,从右侧第1、2位数表示列号,3、4位表示行号
SMALL(IF(($G$1=$A$1:$E$1)*($A$2:$E$16<>""),ROW($A$2:$E$16)/1%+COLUMN($A:$E)*10001),ROW(A1))部分,利用SMALL函数依次提取最小的值。由于是将列号扩大10001倍,因此数据提取的方向是纵向的
MOD(SMALL(IF(($G$1=$A$1:$E$1)*($A$2:$E$16<>""),ROW($A$2:$E$16)/1%+COLUMN($A:$E)*10001),ROW(A1)),10^4)部分,将提取到的数值对10^4求余,得到的结果就是上面提到的,从右侧的4位数字,其实也就是行号和列号。这里也可以使用RIGHT函数来提取
接下来利用TEXT函数来将数据格式转化为“r0c00”的格式
利用INDIRECT函数提取到真正的数据。这里,如果数据格式是“r0c00”,那么INDIRECT函数必须要写成INDIRECT((),"")的形式
最后进行容错处理
有关将多列内容合并为一列的更多的内容,请参看帖子如何用公式将多列合并为一列
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”