[已解决]单元格中有多个数值,提取符合某个规定的数值

gggfhv2005

单元格中有多个数值,提取符合某个规定的数值！

最佳答案

月排行榜 / 总排行榜

tgydslr

2016-1-18 11:18

如果真要你的原汁原味附件的话，公式再加一层嵌套
=IFERROR(MIN(IF(ABS(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"：",":"),"/车间",""),"d",""),":","/"),"/",REPT(" ",99)),ROW($1:$4)*99-98,99))-SUMPRODUCT(TRIM(MID(SUBSTITUTE(B7,"-",REPT(" ",9)),{1,10},9))*{0.5,0.5}))<=SUMPRODUCT(TRIM(MID(SUBSTITUTE(B7,"-",REPT(" ",9)),{1,10},9))*{-0.5,0.5}),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"：",":"),"/车间",""),"d",""),":","/"),"/",REPT(" ",99)),ROW($1:$4)*99-98,99))*1)),"")

跳转到最佳答案楼层

gggfhv2005

在线，急等！

tgydslr

=IFERROR(SMALL(IF(ABS(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C3,"d",""),":","/"),"/",REPT(" ",99)),ROW(1:4)*99-98,99))-25)<=5,TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C3,"d",""),":","/"),"/",REPT(" ",99)),ROW(1:4)*99-98,99))*1),COLUMN(A1)),"")三键结束，右拉
如下图

tgydslr

就一个数据没代表性，公式这样写好像没什么意思
不过也可以借助分列试试，如果数据多有规律的话

gggfhv2005

tgydslr 发表于 2016-1-16 14:34
=IFERROR(SMALL(IF(ABS(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C3,"d",""),":","/"),"/",REPT(" ",99 ...

非常感觉！！！你的回答！！！

请问当20-30内的数值不在最前该如何写代码！

gggfhv2005

在线急等！！！

gggfhv2005

tgydslr 发表于 2016-1-16 14:34
=IFERROR(SMALL(IF(ABS(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C3,"d",""),":","/"),"/",REPT(" ",99 ...

老师，我上传了附件请帮我看一下，能否实现

tgydslr

gggfhv2005 发表于 2016-1-18 10:03
老师，我上传了附件请帮我看一下，能否实现

如图
d7=IFERROR(MIN(IF(ABS(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"/车间",""),"d",""),":","/"),"/",REPT(" ",99)),ROW($1:$4)*99-98,99))-SUMPRODUCT(TRIM(MID(SUBSTITUTE(B7,"-",REPT(" ",9)),{1,10},9))*{0.5,0.5}))<=SUMPRODUCT(TRIM(MID(SUBSTITUTE(B7,"-",REPT(" ",9)),{1,10},9))*{-0.5,0.5}),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"/车间",""),"d",""),":","/"),"/",REPT(" ",99)),ROW($1:$4)*99-98,99))*1)),"")
三键结束，然后下拉
例.rar (7.03 KB, 下载次数: 1)

2016-1-18 11:13 上传

点击文件名下载附件
说明：为了是你的公式短一点，对附件稍作处理：附件中的全角冒号：统统换成了半角冒号:

tgydslr

公式这么长，写起来没啥意思了
其实规范输入行为的话表格就很好处理了

tgydslr

如果真要你的原汁原味附件的话，公式再加一层嵌套
=IFERROR(MIN(IF(ABS(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"：",":"),"/车间",""),"d",""),":","/"),"/",REPT(" ",99)),ROW($1:$4)*99-98,99))-SUMPRODUCT(TRIM(MID(SUBSTITUTE(B7,"-",REPT(" ",9)),{1,10},9))*{0.5,0.5}))<=SUMPRODUCT(TRIM(MID(SUBSTITUTE(B7,"-",REPT(" ",9)),{1,10},9))*{-0.5,0.5}),TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C7,"：",":"),"/车间",""),"d",""),":","/"),"/",REPT(" ",99)),ROW($1:$4)*99-98,99))*1)),"")