|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
我的原创文章,请参考:
Excel中49个字符的公式也能做千年月历,你信吗?
黄成武
EXCEL中日期和时间函数确实复杂,但学好它就必须对它感兴趣,如果你想学习就从你最迫切的方面如手,只要你入门,学习EXCEL并不寂寞,你是否也想亲手做个月历呢?那就跟随我的步骤开始吧!
一、用普通公式,公式最短只需49个字符
在A1单元格输入任意日期,A2输入“星期日”,往右填充至G2单元格,然后在A3单元格输入公式:
=DAY(FLOOR($A$1-DAY($A$1),7)+ROW()*7-21+COLUMN())
将公式往右填充至G3,再往下填充至G3,接着选中A3:G8,用条件格式将不属于本月的日期隐藏:
“条件格式—公式”中输入:
=MONTH($A$1)<>MONTH(FLOOR($A$1-DAY($A$1),7)+ROW()*7-21+COLUMN())
在“格式—字体”中选择白色背景(当然也可以用单元格格式屏蔽上月和下月的日期)。
再对其进行进一步的格式化表格,大功就告成了。
如果你还要系统误差(1900年2月只有28天,系统误将它定为29天),也好办,只需要将上公式改为:
=DAY(FLOOR($A$1-DAY($A$1),7)+ROW()*7-21+COLUMN())-($A$1<61)
我们不再用条件格式了,而是将A3:G3的单元格格式设置为:[=0]g;[>7]g;0
将A7:G8的单元格格式设置为:[>20]0;;
二、多单元格数组公式,公式最短57个字符
在A1单元格输入任意日期,A2输入“星期日”,往右填充至G2单元格,然后选中A3:G8单元格,在编辑栏输入公式:
=DAY(FLOOR(A1-DAY(A1),7)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7})
按数组公式SHIFT+CTRL+ENTER三个组合键。
我们也不用条件格式了,而是将A3:G3的单元格格式设置为:[=]g;[>7]g;0
将A7:G8的单元格格式设置为:[>20]0;;
如果要考虑系统误差,则多单元格数组公式改为:
=--TEXT(FLOOR(A1-DAY(A1),7)-(A1<60)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7},"[=60]9;d")
并将A3:G3的单元格格式设置为:[=]g;[>7]g;0
A7:G8的单元格格式设置为:[>20]0;;
函数含义解析:
月历涉及到易失性函数ROW()和COLUMN() ,日期函数MONTH和DAY,舍入函数FLOOR,文本函数TEXT共6个函数。
ROW()是行号,显示出所在行的行号,如要显示第一行可以用ROW(1:1),ROW(A1),它们的值都等于1
COLUMN(),显示出所在列的列号,如要显示第一列的列号可以用COLUMN(A:A),COLUMN(A1),它们的值都等于1
MONTH是返回日期中的月份,如MONTH(“2010-11-5”)=11,MONTH("1-5")=1
DAY是返回日期中的天数,如DAY(“2010-11-5”)=5,DAY("1-5")=5
FLOOR是将数字(数值)向下按倍数舍入(向零的方向),按我的话来说,不足一倍的都丢掉零头。如FLOOR(3.141589,0.01)=3.14
=FLOOR(871589,98)=871514,FLOOR(-871589,98)=-871612,结果都是除数的整数倍,并且比被除数小。
TEXT函数则更加神奇和精妙,要熟练掌握可不是一件很容易的事,但它的条件参数的用法与单元格格式是相通的。上述公式中:
=--TEXT(FLOOR(A1-DAY(A1),7)-(A1<60)+{0;1;2;3;4;5}*7+{1,2,3,4,5,6,7},"[=60]9;d")
条件参数“[=60]9;d”的含义:如果数值等于60(也就是日期为1900-2-29时)就将数值改为9显示,不等于60就按正常的天数显示(D是日期,与函数DAY类似,只不过TEXT的D参数算出来的是文本,DAY算出来是数值。
[ 本帖最后由 wshcw 于 2011-3-3 22:43 编辑 ] |
|