您的位置: 首页 > EXCEL技巧 > Excel函数 >

Excel中类似分列功能的公式

时间:2013-12-13 整理:docExcel.net

在Excel中会经常用到分列功能,可以将同一单元格内的数据分割到多列中,非常方便。实际上,用公式也能实现类似分列的功能,如下面的一些公式。

一、固定宽度分列

在分列时的“文本分列向导”对话框中有两个选项,分别是“固定宽度”和“分隔符号”。假如A1单元格中包含文本“甲乙丙丁戊己”,分列的宽度相同。

分列的宽度为1个字符时,在B1单元格中输入公式:

=MID($A$1,COLUMN(A1),1)

分列的宽度为2个字符时,在B2单元格中输入公式:

=MID($A$1,COLUMN(A1)*2-1,2)

分列的宽度为3个字符时,在B3单元格中输入公式:

=MID($A$1,COLUMN(A1)*3-2,3)

然后分别向右填充公式,即可得到按不同字符宽度分列后的数据。

下面的多单元格数组公式将A1单元格中的数据按1-3个字符宽度“分列”到一行多列的内存数组中:

=MID(A1,COLUMN(INDIRECT("1:"&LEN(A1))),1)

=MID(A1,COLUMN(INDIRECT("1:"&LEN(A1)))*2-1,2)

=MID(A1,COLUMN(INDIRECT("1:"&LEN(A1)))*3-2,3)

选择一个一行多列的区域,如B5:G5,输入上述公式,然后按Ctrl+Shift+Enter,即可得到分列后的数据。

二、按分隔符号分列

例如A1单元格中包含用顿号分割的文本“足球、篮球、沙滩排球、羽毛球、网球、乒乓球”,用下面的公式将顿号分隔的数据分列到一行中:

在某个单元格,如A4单元格输入公式:

=TRIM(MID(SUBSTITUTE($A$1,"、",REPT(" ",LEN($A$1))),(COLUMN(A1)-1)*LEN($A$1)+1,LEN($A$1)))

然后拖动填充柄向右填充公式即可。

下面的多单元格数组公式将A1单元格中的文本“分列”到一个一行多列的区域:

=TRIM(MID(SUBSTITUTE(A1,"、",REPT(" ",LEN(A1))),LEN(A1)*(COLUMN(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,"、",""))+1))-1)+1,LEN(A1)))

选择一个一行多列的区域,如B6:F6,输入上述公式,然后按Ctrl+Shift+Enter,即可得到分列后的数据。

下面的数组公式将A1单元格中的数据“分列”到一个一行多列的内存数组,公式输入完毕后按Ctrl+Shift+Enter结束:

=TRIM(MID(SUBSTITUTE(A1,"、",REPT(" ",LEN(A1))),LEN(A1)*(TRANSPOSE(ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,"、",""))+1))-1))+1,LEN(A1)))

公式返回“{"足球","篮球","沙滩排球","羽毛球","网球","乒乓球"}”。

又如A1单元格为用“/”分隔的数值“33.5/25.7/3.1/25”,要得到这些数值的和:

=SUM(--TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)*(TRANSPOSE(ROW(INDIRECT("A1:A"&LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))+1))-1))+1,LEN(A1))))

在EXCEL中如何分列 问题:在EXCEL中如何分列
回答:...考回答:这个只能先选中这一列,以“;”为分隔符进行分列,分开之后再一列一列添加标题。 哦,看错了,你这个已经不是分列问题了。 这个用公式或函数也能做出来,但是很麻烦、很费时间,还不如手动分拣来得痛快。
Excel2003入门动画教程66:数据的分列 问题:Excel2003入门动画教程66:数据的分列
回答:...如何将他们分开保存到两列中呢?我们可以用Excel中数据分列来解决,具体操作方法就让Excel办公网小编为大家带来分享! 动画演示: ①选中准备分列的数据,单击“数据”—“分列”,启动“文本分列向...
Excel排序和总结 问题:Excel排序和总结
回答:...的笔划来排序。按笔划排序的法则是:按姓字的划数几多分列,同划数内的姓字按起笔顺序分列(横、竖、撇、捺、折),划数和笔形都沟通的字,按字形布局分列,先阁下、再上下,最后整体字。假如姓字沟通,则依次看名第二...
一个Excel2013单元格内有多个数据,如何计算汇总 问题:一个Excel2013单元格内有多个数据,如何计算汇总
回答:...和进行汇总计算了。对于这种情况,我们需要先将单元格分列,这样进行求和就简单多了。 案例教程 ①打开我准备好了的数据表格,先单击D列,然后点击菜单栏--数据--分列。 ②弹出文本分列向导,将文件类型选为分隔符...
类似VLOOKUP函数能区分大小写查询的公式 问题:类似VLOOKUP函数能区分大小写查询的公式
回答:...在下图中要B列中查找“108-d”所对应的数值。 如果用公式: =VLOOKUP( 108-d ,A2:B5,2,0) 将返回数值“10”,而不是“108-d”所对应的“210”。 如果要区分大小写进行类似的查询,可以用下面的公式: =LOOKUP(1,FIND(A2:A8, 108-d ),B2:B8) ...
类似VLOOKUP查询第N个相同值的公式 问题:类似VLOOKUP查询第N个相同值的公式
回答:...要返回第N个相同值所对应的其他列数据,可以用下面的公式,例如数据在A2:A25区域,共有24条记录,其中与销售人员“王冬”对应的有多条记录,要查找第二个“王冬”所对应的销售额。 在某个单元格中输入数据公式: =INDE...
相关推荐: