Excel精英培训网

 找回密码
 注册
查看: 4817|回复: 9

中文小写之延伸题

[复制链接]
发表于 2012-5-20 13:54 | 显示全部楼层 |阅读模式
本帖最后由 蝶·舞 于 2012-5-27 10:30 编辑

原贴http://www.excelpx.com/thread-243267-1-1.html
在原条件情况下,现增加条件如下:
1、允许存在0的加减运算
2、不只是单纯的两个数加减,可能就一个数,也许是多个数加减(但具体几个数不定,暂先限制6个以内吧)
3、字符控制在250个以内(含等号)
4、开贴时间5月27日

快照2.jpg

中文小写数字加减延伸版.zip (2.21 KB, 下载次数: 135)

评分

参与人数 2 +20 收起 理由
周义坤 + 10 很给力!
飞雨飘 + 10 赞一个!

查看全部评分

excel精英培训的微信平台,每天都会发送excel学习教程和资料。扫一扫明天就可以收到新教程
发表于 2012-5-20 15:13 | 显示全部楼层
本帖最后由 周义坤 于 2012-5-26 17:09 编辑

真晕,07以上函数,
  1. =SUBSTITUTE(SUBSTITUTE(TEXT(SUM((SUBSTITUTE(IF(MOD(ROW($1:2000),2),"加","减")&NUMBERSTRING(INT((ROW($1:2000)/2-0.5)),1),"一十","十")=TRIM(MID(SUBSTITUTE(SUBSTITUTE("加"&A2,"加",REPT(" ",90)&"加"),"减",REPT(" ",90)&"减"),{1,2,3,4,5,6,7}*90-89,90)))*-1^ROW($2:2001)*INT((ROW($1:2000)/2-0.5))),"[dbnum1]"),"-","负"),"一十","十")
复制代码
回复

使用道具 举报

发表于 2012-5-20 19:48 | 显示全部楼层
=SUBSTITUTE(TEXT(SUM(ISNUMBER(FIND({"加","减","加","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","减","加"},"加"&A2&"减"))*{1,-1,1,-1}*ROW($1:999)),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")

点评

当A2,A3单元格返回结果是错误的  发表于 2012-5-20 20:01
回复

使用道具 举报

发表于 2012-5-20 22:25 | 显示全部楼层
本帖最后由 魔魔 于 2012-5-21 22:17 编辑
  1. =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/通用格式"),"一十","十")
复制代码
以下是精简版:
  1. =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,公式如下:
  1. =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~

点评

精简版的运算速度太慢了  发表于 2012-5-21 08:15

评分

参与人数 2 +13 收起 理由
wjc2090742 + 3 爱你!
周义坤 + 10 原来魔魔老师这么厉害啊!

查看全部评分

回复

使用道具 举报

发表于 2012-5-21 09:58 | 显示全部楼层
写了个超长的,谁给精简一下!
  1. =SUBSTITUTE(TEXT(SUM((LEN("加"&SUBSTITUTE(SUBSTITUTE(A2,"加","加加"),"减","减减")&"减")-LEN(SUBSTITUTE("加"&SUBSTITUTE(SUBSTITUTE(A2,"加","加加"),"减","减减")&"减",{"加","加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:1000)-1,1),"一十","十")&{"加","减","加","减"},"")))/LEN({"加","加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:1000)-1,1),"一十","十")&{"加","减","加","减"})*(ROW($1:1000)-1)*{1,1,-1,-1}),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码
回复

使用道具 举报

发表于 2012-5-21 10:12 | 显示全部楼层
这个不用row($1:1000)-1用row($1:999)就行!391字符!
  1. =SUBSTITUTE(TEXT(SUM((LEN("加"&SUBSTITUTE(SUBSTITUTE(A2,"加","加加"),"减","减减")&"减")-LEN(SUBSTITUTE("加"&SUBSTITUTE(SUBSTITUTE(A2,"加","加加"),"减","减减")&"减",{"加","加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","加","减"},"")))/LEN({"加","加","减","减"}&SUBSTITUTE(NUMBERSTRING(ROW($1:999),1),"一十","十")&{"加","减","加","减"})*ROW($1:999)*{1,1,-1,-1}),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码
回复

使用道具 举报

发表于 2012-5-21 10:31 | 显示全部楼层
本帖最后由 飞雨飘 于 2012-5-21 10:32 编辑
  1. =SUBSTITUTE(TEXT(SUM((ROW($1:1998)-1000)*(SUBSTITUTE(TEXT(ROW($1:1998)-1000,"[dbnum1]"),"一十","十")=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"加",REPT(" ",50)),"减",REPT(" ",50)&"-"),{1,2,3,4,5,6}*50-49,50)))),"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码

评分

参与人数 1 +10 收起 理由
周义坤 + 10 学习飘老师的!

查看全部评分

回复

使用道具 举报

发表于 2012-5-25 06:41 | 显示全部楼层
本帖最后由 CheryBTL 于 2012-5-25 11:09 编辑
  1. =SUBSTITUTE(TEXT(SUM(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2&"加○加○加○加○加○","减","加负"),"加",REPT(" ",50)),ROW($1:$6)*50-49,50)),SUBSTITUTE(TEXT(ROW($1:1999)-1000,"[DBNum1];负[DBNum1]G/通用格式"),"一十","十"),))-6000,"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码

蝶·舞兄,开贴前我看不到你的回复呀。其中的TEXT可以简化:
  1. =SUBSTITUTE(TEXT(SUM(MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2&"加○加○加○加○加○","减","加-"),"加",REPT(" ",50)),ROW($1:$6)*50-49,50)),SUBSTITUTE(TEXT(ROW($1:1999)-1000,"[DBNum1]"),"一十","十"),))-6000,"[DBNum1];负[DBNum1]G/通用格式"),"一十","十")
复制代码



回复

使用道具 举报

 楼主| 发表于 2012-5-25 10:26 | 显示全部楼层
CheryBTL 发表于 2012-5-25 06:41

跟我思路一样,但如果将“减”替换成“加-”的话,那么text的第二个参数就可以直接用"[dbnum1]"了
回复

使用道具 举报

发表于 2012-5-27 21:51 | 显示全部楼层
match的解法是有漏洞的,存在"减○"时,比如单元格内容是“一减○”,1楼和8楼的match公式出错。

回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

小黑屋|手机版|Archiver|Excel精英培训 ( 豫ICP备11015029号 )

GMT+8, 2024-3-28 16:51 , Processed in 0.599194 second(s), 16 queries , Gzip On, Yac On.

Powered by Discuz! X3.4

Copyright © 2001-2020, Tencent Cloud.

快速回复 返回顶部 返回列表