[56期函数]反查日期——农历日期转为公历日期

wshcw

反查日期(农历日期转为公历日期)虽然解决了，再拿出来大家练习.
根据现有的辅助资料,将F列的农历日期转化为公历日期,也就是优化I列的公式.
注意:请看新附件:
公式长度要求：
1、H列公式加I列公式总长度不超过300字的给予评1分。
2、如果，不用H列作辅助列,直接转换出结果,并且公式长度在400字以内给予加1分鼓励
3、精彩答案给予另加分.
补充说明:
要求
1、根据现有的辅助资料,不能再增加
2、公式必须通用，不定义名称;闰月，公历跨年度的要考虑。
3、答题E友，不要反复加贴，每人只占用一个楼层，在原贴编辑修改即可，以方便查阅和评分。

答题终止日期延期至：2010-1-30日
再补充:现在发现我的公式有缺陷,300字根本不能圆满完成竞赛题公式.因此,字符限制放宽,03版能正常显示，公式正确、通用就可以评技术分。
提示:
1、G列的日期有日期型和文本型，要综合考虑。
2、跨年，1年中没有腊月，如2009-12-25；1年中有1个腊月，如2002-12-12；1年中有2个腊月，如2003-12-28；
3、1年中有2个腊月但不跨年，如2003-12-2；
4、闰月，如2033-闰11-5，2009-闰5-2


[ 本帖最后由 wshcw 于 2010-2-1 12:02 编辑 ]

lzzgzby

A.B.E列为已知列
384
=INDEX(A:A,MATCH(--LEFT(TEXT(G8,"e"),4),YEAR(A$2:A$2491),)+MATCH(INDEX(E:E,TEXT(SUBSTITUTE(MID(SUBSTITUTE(TEXT(G8,"-m"),"-",REPT(" ",8)),9,8),"闰",),"[=12]!11;0")+1),OFFSET(B$1,MATCH(--LEFT(TEXT(G8,"e"),4),YEAR(A$2:A$2491),),,14),)+(SUBSTITUTE(MID(SUBSTITUTE(TEXT(G8,"-m"),"-",REPT(" ",8)),9,8),"闰",)="12")+IF(ISERR(-G8),,YEAR(G8)=2033)+COUNTIF(G8,"*闰*"))+ABS(-RIGHT(TEXT(G8,"d"),2))-1
数组公式
A.B列为已知列
397
=INDEX(A:A,MATCH(--LEFT(TEXT(G8,"e"),4),YEAR(A$2:A$2491),)+MATCH(MID("正二三四五六七八九十冬腊",TEXT(SUBSTITUTE(MID(SUBSTITUTE(TEXT(G8,"-m"),"-",REPT(" ",8)),9,8),"闰",),"[=12]!11;0"),1)&"月",OFFSET(B$1,MATCH(--LEFT(TEXT(G8,"e"),4),YEAR(A$2:A$2491),),,14),)+(SUBSTITUTE(MID(SUBSTITUTE(TEXT(G8,"-m"),"-",REPT(" ",8)),9,8),"闰",)="12")+IF(ISERR(-G8),,YEAR(G8)=2033)+COUNTIF(G8,"*闰*"))+ABS(-RIGHT(TEXT(G8,"d"),2))-1

[ 本帖最后由 lzzgzby 于 2010-1-23 00:23 编辑 ]

donghan

给个解法，由于有的年有两个腊月，字数不能达到楼主要求，贵在参与吧

[ 本帖最后由 donghan 于 2010-1-16 09:21 编辑 ]

7890

1               

lanyuu

做了两个思路的,希望能给大家一些启发,这次从编辑公式中学了不少text函数的用法.

[ 本帖最后由 lanyuu 于 2010-2-1 21:37 编辑 ]

函数菜鸟

=LOOKUP(LEFT(SUBSTITUTE(G8,"闰",),6)+99,IF(B$2:B$2491=IF(COUNTIF(G8,"*闰*"),"闰",)&INDEX(E:E,1+MONTH(TEXT(LEFT(SUBSTITUTE(G8,"闰",),7),"0;@!1"))),A$2:A$2491+ABS(RIGHT(TEXT(G8,"d"),2))-1))
数组公式
没有考虑G列为不存在的农历日期

================================================
2033-闰11-5转换出错,请你尽快更正公式,否则撤销评分。
                                         ===wshcw===
================================================
更正公式：
=LOOKUP(99-LOOKUP(1,-LEFT(SUBSTITUTE(G8,"闰",),{6,7})),IF(B$2:B$2491=IF(COUNTIF(G8,"*闰*"),"闰",)&INDEX(E:E,1+MONTH(TEXT(LEFT(SUBSTITUTE(G8,"闰",),7),"0;@!1"))),A$2:A$2491+ABS(RIGHT(TEXT(G8,"d"),2))-1))

[ 本帖最后由 函数菜鸟 于 2010-2-1 23:33 编辑 ]

子元女王

好像余地很大啊

I列数组公式(310字)，只用了A、B列
{
=INDEX(A:A,MATCH(LEFT(TEXT(G8,"e"),4)-(-(MID(TEXT(G8,"e-m-"),7+(MID(G8,6,1)="闰"),1)&0)=0)&IF(MID(G8,6,1)="闰","闰",MID("正二三四五六七八九十冬腊",SUBSTITUTE(MID(TEXT(G8,"e-m"),6,2),"-",),1)),IF(ROW($1:$2491)<14,1899,YEAR(LOOKUP(A$1:A$2491,IF(B$1:B$2491="冬月",A$1:A$2491,""))))&LEFT(B$1:B$2491),))+ABS(RIGHT(TEXT(G8,"d"),2))-1
}

2056、2094、2219行怎么回事？故意的？

完全不考虑运算效率问题，弄到272字，用了A、B、E列
{
=INDEX(A:A,MATCH(LEFT(TEXT(G8,"e"),4)-(-(MID(TEXT(G8,"e-m-"),7+(MID(G8,6,1)="闰"),1)&0)=0)&IF(MID(G8,6,1)="闰","闰",LEFT(INDEX(E:E,SUBSTITUTE(MID(TEXT(G8,"e-m"),6,2),"-",)+1))),1899+COUNTIF(INDIRECT("b1:b"&ROW($1:$2491)),"冬月")&LEFT(B$1:B$2491),))+ABS(RIGHT(TEXT(G8,"d"),2))-1
}


附件2

[ 本帖最后由 子元女王 于 2010-1-27 11:47 编辑 ]

wshcw

2056、2094、2219行是程序错误所至.

wshcw

评分情况:

E友	楼层	公式长度	运行情况	技术分	财富分
lzzgzby	2	384	速度快	3
donghan	3	476	速度快,但公式未优化	2
lanyuu	5	380	速度慢	2
函数菜鸟	6	183	速度慢	2
子元女王	7	272	速度可以接受	2	30

donghan

现在提高一下技术分真不容易呀，学习一下其他人的函数