本帖最后由 魔魔 于 2012-5-21 22:17 编辑
- =SUBSTITUTE(TEXT(SUM((TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"减","加-"),"加",REPT(" ",99)),COLUMN(A:F)*99-98,99))=SUBSTITUTE(TEXT(ROW($1:$1999)-1000,"[dbnum1]"),"一十","十"))*(ROW($1:$1999)-1000)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码 以下是精简版:- =SUBSTITUTE(TEXT(SUM((TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"减","加-"),"加",REPT(" ",99)),COLUMN(A:F)*99-98,99))=SUBSTITUTE(TEXT(ROW(A:A)-4^5,"[dbnum1]"),"一十","十"))*(ROW(A:A)-4^5)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码
为保证速度重新精简了,长度为218,公式如下:
- =SUBSTITUTE(TEXT(SUM((TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"减","加-"),"加",REPT(" ",99)),COLUMN(A:F)*99-98,99))=SUBSTITUTE(TEXT(ROW(1:3400)-7^4,"[dbnum1]"),"一十","十"))*(ROW(1:3400)-7^4)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码 上面的公式1:3400没有使用绝对引用,拖动到1403时将不存在-999的数据,开始出错.O(∩_∩)O~ |