EXCEL公式如何提取数据

Question

B列已知数据，用公式如何提取到A列的效果，B列没有数据时，A列不显示或为“0”

Answer 1

工作中遇到不同文段内容提取规律不一样，所以要根据内容寻找规律。

首先，把本问题的句段分拆三部分，如下图：

第二，拆分字段一中，要提取“针织”或“梭织”，用到经典函数LOOKUP(,-FIND（）) ,

函数公式为：=LOOKUP(,-FIND({"针织","梭织"},B2),{"针织","梭织"})

第三，拆分字段二中，提取"女装","女成人","男成人","男装","女童","男童"且改为"女式","女式","男式","男式","女童","男童"

函数公式为=LOOKUP(,-FIND({"女装","女成人","男成人","男装","女童","男童"},B2),{"女式","女式","男式","男式","女童","男童"})

第四，拆分字段三中，要提取"女装"/"女成人"/"男成人"/"男装"/"女童"/"男童"后面所有内容，

函数公式是=RIGHT(B2,LEN(B2)-FIND(LOOKUP(,-FIND({"女装","女成人","男成人","男装","女童","男童"},B2),{"女装","女成人","男成人","男装","女童","男童"}),B2)-LEN(LOOKUP(,-FIND({"女装","女成人","男成人","男装","女童","男童"},B2),{"女装","女成人","男成人","男装","女童","男童"}))+1)

最后，最终提取的结果就是用连接符& 和“|”连接起来就可以。

最终函数公式是

=LOOKUP(,-FIND({"针织","梭织"},B2),{"针织","梭织"})&"|"&LOOKUP(,-FIND({"女装","女成人","男成人","男装","女童","男童"},B2),{"女式","女式","男式","男式","女童","男童"})&"|"&RIGHT(B2,LEN(B2)-FIND(LOOKUP(,-FIND({"女装","女成人","男成人","男装","女童","男童"},B2),{"女装","女成人","男成人","男装","女童","男童"}),B2)-LEN(LOOKUP(,-FIND({"女装","女成人","男成人","男装","女童","男童"},B2),{"女装","女成人","男成人","男装","女童","男童"}))+1)

【说明：】公式内容很多，采用这种运算公式的作用避免句段内容有变，如“棉制针织女装工装长裤”变为“网红棉制针织女装工装长裤”，公式结果也不会变；再例如除了“针织”和“梭织”，之后增加“机织”类别，只要把公式中LOOKUP(,-FIND({"针织","梭织"},B2),{"针织","梭织"})改为LOOKUP(,-FIND({"针织","梭织","机织"},B2),{"针织","梭织","机织"})就可以, 其它也是容易改动。

Answer 2

=IF(B1="","",MID(SUBSTITUTE(B1,"成人","式"),3,2)&"|"&MID(SUBSTITUTE(B1,"成人","式"),7,7)&"|"&SUBSTITUTE(MID(SUBSTITUTE(B1,"成人","式"),5,2),"装","式"))

效果图如下：

追问

你好，还有一点问题，如果这样呢？

追答

=SUBSTITUTE(IF(B1="","",MID(SUBSTITUTE(B1,"成人","大"),3,2)&"|"&MID(SUBSTITUTE(B1,"成人","大"),7,7)&"|"&SUBSTITUTE(MID(SUBSTITUTE(B1,"成人","大"),5,2),"装","式")),"大","成人")

追问

没有“式”了，就是原文提取的

追答

=SUBSTITUTE(IF(B1="","",MID(SUBSTITUTE(B1,"成人","大"),3,2)&"|"&MID(SUBSTITUTE(B1,"成人","大"),7,7)&"|"&MID(SUBSTITUTE(B1,"成人","大"),5,2)),"大","成人")

本回答被提问者采纳

Answer 3

=IF(ISERROR(FIND("针织",B2)),IF(ISERROR(FIND("梭织",B2)),"","梭织"),"针织")&"|"&IF(ISERROR(FIND("工装长裤",B2)),IF(ISERROR(FIND("工人中裤",B2)),IF(ISERROR(FIND("长裤",B2)),IF(ISERROR(FIND("中裤",B2)),IF(ISERROR(FIND("上衣",B2)),IF(ISERROR(FIND("背心",B2)),IF(ISERROR(FIND("长裙",B2)),IF(ISERROR(FIND("裙",B2)),"","裙"),"长裙"),"背心"),"上衣"),"中裤"),"长裤"),"工人中裤"),"工装长裤")&"|"&IF(ISERROR(FIND("女装",B2)),IF(ISERROR(FIND("女成人",B2)),IF(ISERROR(FIND("女童",B2)),"","女童"),"女装"),"女装")

右面布匹种类、样式和品种不用管，在A2单元格把上面的公式粘过去下拉就可以了，但这不是完美解决方案，这一方案只适合你上面提到的这几种分类（就是右面我列出来的），每个项目增加分类的话要修改公式。

完美解决需要用到宏或者数据库ACCESS

追问

实现不了，公式有误

追答

=IF(ISERROR(FIND("针织",B1)),IF(ISERROR(FIND("梭织",B1)),"","梭织"),"针织")&"|"&IF(ISERROR(FIND("工装长裤",B1)),IF(ISERROR(FIND("工人中裤",B1)),IF(ISERROR(FIND("长裤",B1)),IF(ISERROR(FIND("中裤",B1)),IF(ISERROR(FIND("上衣",B1)),IF(ISERROR(FIND("背心",B1)),IF(ISERROR(FIND("长裙",B1)),IF(ISERROR(FIND("裙",B1)),"","裙"),"长裙"),"背心"),"上衣"),"中裤"),"长裤"),"工人中裤"),"工装长裤")&"|"&IF(ISERROR(FIND("女装",B1)),IF(ISERROR(FIND("女成人",B1)),IF(ISERROR(FIND("女童",B1)),"","女童"),"女装"),"女装")
A1单元格粘贴这个，你那个表没有表头

追问

还是不行



追答

你的excel版本是2003吧？03版及以前的版本if函数嵌套不能超过7个

追问

是2003版

Answer 4

A1输入：=IF(ISERR(FIND("针",B1)),"梭织","针织")&"︳"&IF(ISERR(FIND("女装",B1)),"童装","女式")&"︳"&SUBSTITUTE(RIGHT(B1,2),"装",)，下拉填充。

追问

已知数里有女成人的，公式好像不行

追答

修改公式为：=IF(ISERR(FIND("针",B1)),"梭织","针织")&"︳"&IF(ISERR(FIND("童装",B1)),"女式","童装")&"︳"&SUBSTITUTE(RIGHT(B1,2),"装",)

鉴于你的题目中添加了性别和服装类型，公式改为：

=IF(ISERR(FIND("针",B1)),"梭织","针织")&"︳"&IF(ISERR(FIND("女",B1)),IF(ISERR(FIND("男童",B1)),"男装","男童"),IF(ISERR(FIND("女童",B1)),"女式","女童"))&"︳"&IF(ISERR(FIND("工",B1)),SUBSTITUTE(RIGHT(B1,2),"装",),RIGHT(B1,LEN(B1)-FIND("工",B1)+1))

Answer 5

以|为分隔符，将A列分为3列
在D1中输入或复制粘贴下列公式
=A1&C1&B1
下拉填充本回答被网友采纳