[原创]将中文日期文本逆转为数字日期值(新解)

wshcw

将中文日期文本逆转为数字日期值(新解)

将中文日期文本逆转为数字日期值,最近突然来了灵感,用"偷梁换柱"法进行逆转换,与《精粹》公式相比，在速度不减的前提下，公式长度减少40%.看附件

wcdDdzay.rar (4.74 KB, 下载次数: 346)

2008-4-3 10:41 上传

点击文件名下载附件
[原创]将中文日期文本逆转为数字日期值(新解)

另参考我的主题:

将中文日期文本逆转为数字日期值(已结题)

[此贴子已经被gouweicao78于2008-10-15 10:03:50编辑过]

wupefei

学习了

gouweicao78

QUOTE:

以下是引用wshcw在2008-4-2 10:25:19的发言：

将中文日期文本逆转为数字日期值(新解)

将中文日期文本逆转为数字日期值,最近突然来了灵感,用"偷梁换柱"法进行逆转换,与《精粹》公式相比，在速度不减的前提下，公式长度减少40%.看附件

另参考我的主题:

将中文日期文本逆转为数字日期值(已结题)

要说“最快”，《精粹》中定义名称的方法是目前最快的，测试5000条公式速度0.109秒。

黄兄第1条公式16.688秒，下面我给个解法，0.984秒：

=--TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A$1,"月","  "),"日",),{1,2,3,4,6,8},{1,1,1,1,2,5})),TEXT(ROW($1:$32)-1,"[dbnum1]d"),)-1)*10^{7,6,5,4,2,0}),"#-00-00")

共165字，如果要考虑“元月”，再增加20字符。速度1.016秒。也取个自己熟悉的诨号“庖丁解牛”，将字符分解开来，年份一个字一个字得查找，月份、日期分开查找，速度赢在ROW($1900:$2100)、ROW($1:$366)这两个数组大户上。而且，适用日期到9999-12-31。

[此贴子已经被作者于2008-7-22 2:27:29编辑过]

syz105729913

学习学习

TEXT的第二参数真是很难吃透的一个参数

gouweicao78

QUOTE:

以下是引用syz105729913在2008-7-22 7:48:11的发言：

学习学习

TEXT的第二参数真是很难吃透的一个参数

这个题解法重点不是TEXT的参数，而是针对“速度”问题、怎样减少运算量方面做文章。2007版的数组公式能够引用整列，但如果速度也是按整列计算而没有优化的话，这个功能就成了“鸡肋”。

wshcw

QUOTE:

以下是引用gouweicao78在2008-7-22 2:05:29的发言：

要说“最快”，《精粹》中定义名称的方法是目前最快的，测试5000条公式速度0.109秒。

黄兄第1条公式16.688秒，下面我给个解法，0.984秒：

=--TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A$1,"月","  "),"日",),{1,2,3,4,6,8},{1,1,1,1,2,5})),TEXT(ROW($1:$32)-1,"[dbnum1]d"),)-1)*10^{7,6,5,4,2,0}),"#-00-00")

共165字，如果要考虑“元月”，再增加20字符。速度1.016秒。也取个自己熟悉的诨号“庖丁解牛”，将字符分解开来，年份一个字一个字得查找，月份、日期分开查找，速度赢在ROW($1900:$2100)、ROW($1:$366)这两个数组大户上。而且，适用日期到9999-12-31。

把老师的公式再脱去一层"外衣"(少用一次SUBSTITUTE函数),给公式再减减肥(共152字):

=--TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(A3,"月","日 "),{1,2,3,4,6,9},{1,1,1,1,2,4})&"*"),TEXT(ROW($1:$32)-1,"[dbnum1]d日"),)-1)*10^{7,6,5,4,2,0}),"#-00-00")

目前,除了我原来那个短但巨慢的公式以及原精粹的解法除外,现将目前又新增的解法归纳如下:
130字符 偷梁换柱法: =--(1899+MATCH(LEFT(A1,4),TEXT(ROW($1900:$2100),"[dbnum1]0"),)&TEXT(MATCH(MID(A1,6,8),TEXT(ROW($1:$366),"[dbnum1]m月d日"),),"-m-d"))
150字符  =--(1899+MATCH(LEFT(B1,4),TEXT(ROW($1900:$2100),"[dbnum1]0"),)&TEXT(MATCH(SUBSTITUTE(MID(B1,6,8),"元","一"),TEXT(ROW($1:$366),"[dbnum1]m月d日"),),"-m-d"))
164字符 替换法: =--TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"月","  "),"日",),{1,2,3,4,6,9},{1,1,1,1,2,4})),TEXT(ROW($1:$32)-1,"[dbnum1]d"),)-1)*10^{7,6,5,4,2,0}),"#-00-00")
148字符 替换法加偷梁换柱法: =SUM((MATCH(MID(A1,{1,2,3,4},1),TEXT(ROW($1:$10)-1,"[dbnum1]"),)-1)*10^{3,2,1,0})&TEXT(MATCH(MID(A1,6,8),TEXT(ROW($1:$366),"[dbnum1]m月d日"),),"-m-d")
217字符 两步替换法: =--(SUM((MATCH(MID(A3,{1,2,3,4},1),TEXT(ROW($1:$10)-1,"[dbnum1]"),)-1)*10^{3,2,1,0})&""&TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(MID(A3,6,8),"月","日 "),{1,4},{3,5})),TEXT(ROW($1:$32)-1,"[dbnum1]d日"),)-1)*10^{2,0}),"-#-00"))
152字符 简化替换法: =--TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(A3,"月","日 "),{1,2,3,4,6,9},{1,1,1,1,2,4})&"*"),TEXT(ROW($1:$32)-1,"[dbnum1]d日"),)-1)*10^{7,6,5,4,2,0}),"#-00-00")
172字符  =--TEXT(SUM((MATCH(TRIM(MID(SUBSTITUTE(SUBSTITUTE(B1,"月","日 "),"元","一"),{1,2,3,4,6,9},{1,1,1,1,2,4})&"*"),TEXT(ROW($1:$32)-1,"[dbnum1]d日"),)-1)*10^{7,6,5,4,2,0}),"#-00-00")

dlQk7xe6.rar (8.54 KB, 下载次数: 106)

2008-10-15 19:55 上传

点击文件名下载附件

[此贴子已经被作者于2008-10-15 19:56:01编辑过]

gouweicao78

佩服黄兄的钻劲儿！

利用MATCH支持通配符精确查找的原理，省掉一层替换。

附件中164字、218字、152字、172字的四个解法都存在“三十一日”问题，需将Row($1:$31)改为Row($1:$32)。

[此贴子已经被作者于2008-10-15 9:21:11编辑过]

me1156202564

学习学习！

yakedidi

学习了 都是高手 呵呵

stcxj

请教两个问题：
1、中文日期文本——输入中文日期，如：“ErLingLingJIuNianErYueErShiSiRi”，显示“二〇〇九年二月二十四日”，而不是“二oo九年二月二十四日”，答案是#N/A。
2、中文日期——以日期格式输入，如：设置单元格格式——数字——日期：二〇〇九年二月二十四日——编辑栏输入“2009-2-24”——单元格显示“二〇〇九年二月二十四日”，答案是#N/A。
关键是第一个问题，显示往往是二〇〇九年二月二十四日”（两个0是“大圆圈”），如何处理？