您的位置: 首页 > 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...
相关知识:

下面内容对您也许有用

      话题:EXCEL分列公式
      问:例:在EXCEL表格的A列中,A1:125.5*87.5*66.5;A2:85.5*45*12;A3:33.5*25;
      答:(1)将要分列的字符值放在A列;(2)B列转换“*”公式为=SUBSTITUTE(A1,"*","-");(3)C列为分列公式的第1组值;D列为分列公式的第2组值;E列为分列公式的第3 求EXCEL显示指定分组字符分列 excel 2007 如何 用公式实现数据
      话题:EXCEL2010分列功能怎样取一列中的中间内容
      问:例:A 1 BAIDU PTY LTD 151 XXX ROAD BEIJING CHINA 2 TENGXUN PTY LTD 75 XXX
      答:在b1单元格输入=TRIM(MID(SUBSTITUTE(A1,"",REPT("",100)),300,300))下拉 excel2010 的“数据”-“分列” 各位高手,excel2010数据分列,如
      话题:excel2007有这样的分列功能吗?(不是:数据-分列)知道
      问:我有一列数据比如:1,2,3,4,5,6,7,8,9,10,11,12 我想将这列数据按照我需要的单位
      答:明明“数据-分列”就可以达到目的了嘛。固定宽度,然后就随你怎么定义都行。 请问excel的数据-分列excel excel2007表格分列问题,求高手~
      话题:excel分列求和的公式怎么写啊?知道
      答:M7输入公式,按ctrl+shift+enter键结束就行了。SUMPRODUCT((MOD(COLUMN(Z7:DN7),3)=2)*(Z7:DN70)) excel分列求和的公式怎么写啊? excel求和公式
      话题:excel分列求和的公式怎么写啊?知道
      答:利用SUMIF函数 SUMIF(Z6:DR6,J3,Z7)右拉 excel分列求和的公式怎么写啊? excel求和公式
      话题:excel 2007 如何 用公式实现数据的分列?知道
      问:A列 第一行是 ACD,BFE,WER,第二行是 NGS,UY,第三行是 WERQ,ASC,WZ,如何用公式
      答:B1公式=TRIM(MID(SUBSTITUTE($A1,",",REPT("",100)),COLUMN(A1)*100-99,100))向右填充 Excel 2007 数据分列的问题! EXCEL分列公式
      话题:怎么用EXCEL公式 完成下表功能
      答:VLOOKUP(A1,$A$1:$L$1,ROW(A2),0)提问者评价 谢谢 excel制作表格的方法有哪些 请帮我把下面一个EXCEL表设一下
      话题:excel如何利用公式自动分列
      答:A2单元格输入公式=MID(OFFSET($A$1,((COLUMN(A1)-1)/2)*2-MOD(COLUMN(A1)-1,2)),MOD(COLUMN(A1)-1,2)+1,1)右拉复制公式 当某单元格不足二位数时,在分列过程中 EXCEL分列公式 excel 分列公式
      话题:求EXCEL显示指定分组字符分列公式
      问:例:在EXCEL的A列中,A1单元格中的内容为:“50.5*43.5*18.5”;A2单元格中的
      答:LEFT(A1,FIND("*",A1,1)-1)=MID(A1,FIND("*",A1,1)+1,FIND("*",A1,FIND("*",A1,1)+1)-FIND("*",A1,1)-1)=TRIM(RIGHT(SUBSTITUTE(A1,"*",REPT("",LEN(A1))),LEN EXCEL分列公式 EXCEL公式$符号在哪里
      话题:EXCEL 公式中怎么截取这样的字
      问:1。背景:我有许多这样的数据源:如 TVP-12325;1203-53223*TVP-HJPL-12325;
      答:假设原数在A列,产生的数据在B列,则在B1输入公式=MID(A1,1,FIND(;A1))。向下拖拉填充公式就是,产生的B1结果是:TVP-12325;B1结果是:TVP-HJPL-12325;
最新评论