EVALUATE函数的妙用 evaluate函数

EVALUATE函数只有一个参数,就是文本公式,比如可以计算EVALUATE("1+1*2") = 3,也可以计算EVALUATE("{1,2,3}") = {1,2,3}(常量数组),让我们来探寻一下它的用法吧
1.普通用法---纯文本计算

EVALUATE函数的妙用 evaluate函数

文章插图
2.带其他文字的计算(比较规律的那种)
这里需要先了解一下N函数,N函数可以把"文本"都变成0,数值还是原来的数值,如下

EVALUATE函数的妙用 evaluate函数

文章插图
然后我们再来看SUBSTITUTE函数把以下数据替换后的结果
=SUBSTITUTE(SUBSTITUTE(A2,"[","*1^N("""),"]",""")")

EVALUATE函数的妙用 evaluate函数

文章插图

是不是就相当于2*1^0*3*1^0=6,此时套EVALUATE函数,结果如下
=EVALUATE(SUBSTITUTE(SUBSTITUTE(A2,"[","*1^N("""),"]",""")"))

EVALUATE函数的妙用 evaluate函数

文章插图
3.规律数字求和、最大值、最小值、平均值
我们知道{1,2,3,4}这种是常量数组,SUM({1,2,3,4})=10,那就可以对以下数据构造常量数组的形式

EVALUATE函数的妙用 evaluate函数

文章插图
然后套EVALUATE函数,SUM求和

EVALUATE函数的妙用 evaluate函数

文章插图
最大值MAX函数,最小值MIN函数,平均值AVERAGE
4.计算档案的件数
同样我们构造常量数组的形式
="{"""&SUBSTITUTE(A2,",",""";""")&"""}"
EVALUATE函数的妙用 evaluate函数

文章插图
套EVALUATE函数,变成常量数组
=EVALUATE("{"""&SUBSTITUTE(A2,",",""";""")&"""}")

EVALUATE函数的妙用 evaluate函数

文章插图
此时选中公式按F9查看,里面已经是数据的形式
EVALUATE函数的妙用 evaluate函数

文章插图
套SUBSTITUTE,把"-" 替换为 "-1-",因为45-84的个数计算,件数应该是(尾-首)+1
=SUBSTITUTE(EVALUATE("{"""&SUBSTITUTE(A2,",",""";""")&"""}"),"-","-1-")
EVALUATE函数的妙用 evaluate函数

文章插图
最终公式如下,数组公式,需要按CTRL+SHIFT+ENTER
=SUM(TEXT(EVALUATE(T(IF(,,SUBSTITUTE(EVALUATE("{"""&SUBSTITUTE(A2,",",""";""")&"""}"),"-","-1-")))),"1;0")*1)
EVALUATE函数的妙用 evaluate函数

文章插图
TEXT的作用是把负数显示成正数,正数就是单个的数字,强制显示为1,代表1件,然后SUM求和,因为TEXT后的都是文本数字,需要*1转数值才能求和
注:OFFICE用户EVALUATE函数属于宏表函数,需要定义名称使用,另存表格为xlsm格式文件
【EVALUATE函数的妙用 evaluate函数】