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

样例图1.判断数字格式公式IF(OR(B2NUMBER,B2numeric), 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))))emptyCountROWS(rng),nonNumCountemptyCount)),OK,NG),对象外)本人excel报错不能用MATCH匹配最后一行需要如下IF(OR(B2NUMBER,B2numeric), 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))))emptyCountROWS(rng),nonNumCountemptyCount)),OK,NG),对象外)还会报错需要去掉空格与换行符如下IF(OR(B2NUMBER,B2numeric),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))))emptyCountROWS(rng),nonNumCountemptyCount)),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))