你看是不是这种结果:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/e4dde71190ef76c60c281dad9016fdfaae5167ba?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
函数这么写的:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/94cad1c8a786c917a4f1b4bfc43d70cf3ac75742?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
=IF(AND(B2>=INT(LEFT(A2,FIND("~",A2)-1)),B2<=INT(RIGHT(A2,LEN(A2)-FIND("~",A2))))=FALSE,0,B2)
使用函数:
find("字符",值) 获取~符号位置
int(字符) 将字符型数字转换成真正的数字
left,right,len我就不介绍
and(表达式1,表达式2....)=true 并列条件
if(表达式,表达式成立的值,不成立的值) 条件取值
函数较多,另外一种方法就是使用VBA自定义一个函数,我们先新建一个模块
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/9213b07eca8065388a1344959adda144ac3482bf?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
插入函数
'///condition//条件字符串
'///data//存放数据的单元格
Public Function get_value(condition As Range, data As Range)
Dim min, max '//定义最小值、最大值、数组
min = Int(Split(Trim(condition.Formula), "~")(0))
max = Int(Split(Trim(condition.Formula), "~")(1)) '//存储切割着两个值转换格式
Dim da
da = Round(Trim(data.Formula), 3) '//获取data里面的值,格式化,取保留三位小数
If da >= min And da <= max Then '//如果da在min和max之间就取自己的值,如果不是就是0
get_value = da
Else
get_value = 0
End If
End Function
我们再回去看看函数是否生效
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/91529822720e0cf38d04a27d0746f21fbf09aa41?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/024f78f0f736afc377a2d7e3be19ebc4b645128d?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
为啥不行了呢?
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/6a600c338744ebf844f9b1b9d4f9d72a6159a7a8?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
原因是VBA取值着range.Formula这玩意是单元格字表值,你选择了公式,他就是公式自己写法,不会换算成值的,修改一下:
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/fd039245d688d43f65a84474701ed21b0ef43b37?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/9a504fc2d56285357fee07a79def76c6a7ef632e?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
以上是用EXCEL自身函数和VBA自定义函数,仅供参考!