本帖最后由 wangjguo44 于 2014-10-29 17:56 编辑
crixalishu 发表于 2014-10-28 23:37
这位长老道行很高啊,不过小弟的话的核心问题其实是 - 有几个人看懂了?
公式没有相应附件,不知数据如何,的确犹如天书,很难看懂,但是有了数据,只要有点函数基础,就不难看懂了:应该说公式的想法还是比较巧妙的,把原来的用文本表示的数值,去掉一定的位数,加上“KMBT”等量纲(小于100,000留原数,大于等于100,000、小于100,000,000的,去掉后3位尾数加K,大于等于100,000,000、小于100,000,000,000的,去掉后6位尾数加M,……) 但是公式还是可以商榷和简化的:
1、原式=CONCATENATE(TEXT(LEFT(SUBSTITUTE(INDIRECT("RC[-1]",0),",",""),LEN(SUBSTITUTE(INDIRECT("RC[-1]",0),",",""))-((FLOOR(LEN(SUBSTITUTE(INDIRECT("RC[-1]",0),",",""))/3,1)*3)-3)),"#,##0"),IFERROR(MID("KMBTqQsSoNdUDZ",((FLOOR(LEN(SUBSTITUTE(INDIRECT("RC[-1]",0),",",""))/3,1)*3)-3)/3,1),""))
中粉色部分括弧就是多余的,完全可以去掉(去掉后,那个粉色的“-”,改成“+”便可。)
2、沿用原公式思路,把FLOOR函数用INT函数,公式可以简化点:
- =CONCATENATE(TEXT(LEFT(SUBSTITUTE(INDIRECT("RC[-1]",),",",""),LEN(SUBSTITUTE(INDIRECT("RC[-1]",),",",""))-INT((LEN(SUBSTITUTE(INDIRECT("RC[-1]",),",",""))-3)/3)*3),"#,##0"),IFERROR(MID("KMBTqQsSoNdUDZ",INT((LEN(SUBSTITUTE(INDIRECT("RC[-1]",),",",""))-3)/3),1),""))
复制代码
3、用位数取整,公式可以简化:- =CONCATENATE(TEXT(INT(SUBSTITUTE(INDIRECT("RC[-1]",),",","")/(10^(MAX(0,INT((LEN(SUBSTITUTE(INDIRECT("RC[-1]",),",",""))-3)/3)*3)))),"#,##0"),IFERROR(MID("KMBTqQsSoNdUDZ",INT((LEN(SUBSTITUTE(INDIRECT("RC[-1]",),",",""))-3)/3),1),""))
复制代码
4、不要用SUBSTITUTE()函数,直接把文本型数值用“--”、“1*”转换成数值性,还可以简化
- =CONCATENATE(TEXT(--INDIRECT("RC[-1]",)/10^MAX(0,3*(INT(LEN(--INDIRECT("RC[-1]",))/3)-1)),"#,##0"),IFERROR(MID("KMBTqQsSoNdUDZ",INT(LEN(--INDIRECT("RC[-1]",))/3)-1,1),""))
复制代码 不多考虑了,应该还可以简化,比如INDIRECT("RC[-1]",0)能否可以直接用A1引用样式的相对引用呢,- =TEXT(1*M3/10^MAX(0,3*(INT(LEN(1*M3)/3)-1)),"#,##0")&IFERROR(MID("KMBTqQsSoNdUDZ",INT(LEN(1*M3)/3)-1,1),"")
复制代码 岂不更短些?
(以上解析仅以附件N列为例,其余列没做分析)
|