记录一个好用的excel判断数字格式的公式

样例图

1.判断数字格式公式

=IF(OR(B2="NUMBER",B2="numeric"), IF(LET(rng,B7:INDEX(B:B,MATCH("zzz",B:B)), trimmed,TRIM(rng), isNum,ISNUMBER(VALUE(trimmed)), isEmpty,trimmed="", nonNumCount,SUM(--NOT(isNum)), emptyCount,SUM(--isEmpty), AND(SUM(--(isNum*(NOT(isEmpty))))+emptyCount=ROWS(rng),nonNumCount=emptyCount)),"OK","NG"),"对象外")

本人excel报错，不能用MATCH匹配最后一行，需要如下：

=IF(OR(B2="NUMBER",B2="numeric"), IF(LET(rng,B7:B11, trimmed,TRIM(rng), isNum,ISNUMBER(VALUE(trimmed)), isEmpty,trimmed="", nonNumCount,SUM(--NOT(isNum)), emptyCount,SUM(--isEmpty), AND(SUM(--(isNum*(NOT(isEmpty))))+emptyCount=ROWS(rng),nonNumCount=emptyCount)),"OK","NG"),"对象外")

还会报错，需要去掉空格与换行符，如下：

=IF(OR(B2="NUMBER",B2="numeric"),IF(LET(rng,B7:B11,trimmed,TRIM(rng),isNum,ISNUMBER(VALUE(trimmed)),isEmpty,trimmed="",nonNumCount,SUM(--NOT(isNum)),emptyCount,SUM(--isEmpty),AND(SUM(--(isNum*(NOT(isEmpty))))+emptyCount=ROWS(rng),nonNumCount=emptyCount)),"OK","NG"),"对象外")

2.判断是否为空公式

=IF(B4<>"是","对象外", IF(COUNTBLANK(B7:INDEX(B:B,MATCH("zzz",B:B)))>0,"NG","OK"))

如果报错，去掉MATCH公式，根据自己数据行数写，如下：

=IF(B4<>"是","对象外", IF(COUNTBLANK(B7:B11)>0,"NG","OK"))

如果还是报错，去掉空格和换行，如下：

=IF(B4<>"是","对象外",IF(COUNTBLANK(B7:B11)>0,"NG","OK"))