用EXCEL计算两个日期之间的每一天.docVIP

用EXCEL计算两个日期之间的每一天 这个月的公式采用了一项不可思议的技术，是我从 MrE 信息栏里学来的。输入起始日期和结束日期之后，你就可以用这项技术去研究这个范围内的每一天。假如，如图 1所示，这两个日期跨越了三十年，这个表能估算出这期间内的 11,000 天并算出与你所选取的标准相匹配的天数。 比如说你想计算这 30 年中一个月的第一天刚好落在周末的次数，你可以用=DAY(A2)=1来检验某一天是否是一个月的第一天，也可以用=WEEKDAY(A2,2)5 去验证某一天是否落在 星 期 六 或 星 期 天 。 将 那 些 结 合 起 来 形 成 单 一 的 一 天 ， 你 可 以 用=AND(DAY(A2))=1,WEEK-DAY(A2,2)5)，其中的秘密是怎样重复那个能确定 A2 和 B2 间的任一天的公式，而不需要输入 11,044 个式子。 日期以数字形式保存随便选择一个单元格并输入=A2，这个公式的结果会显示 Excel 在后台是怎样存储日期的。29,688 这一结果表示 1981 年 4 月 12 日，代表 1900 年 1 月 1 日的后 29,688 天；通常人们看到结果是序号而不是日期都会非常地沮丧，因此 Excel 显示日期的方式正合你的胃口。 从两个单元格中产生全部日期 假如你用=A2”:””B2”来把储存在 A2 和 B2 单元格中的日期合并，Excel 会产生一个像 29688:40732 这种形式的文本。这个文本很不直观，但却是 Excel 中的有效表达方式。正如 =COUNTBLANK(A:C) 能 够 数 出 所 有 在 列 A:C 范 围 内 的 空 白 单 元 格 个 数 那 样 ，=COUNTBLANK(1:10)能够数出在第 1 行到第 10 行范围内的空白单元格个数。鉴于29688:40732 是一个有效的 Excel 表达方式，你可以用这个文本作为 INDIRECT 函数的一个参数。 INDIRECT 函数只能识别一些符合 Excel 有效表达方式的文本，因此 INDIRECT 只会采用符合 Excel 有效表达方式的数据，例如，=INDIRECT(29688:40732)会涉及到所有在第 29,688 行和第 40,732 行之间的行，行里面是什么数字并不重要，毕竟他们有可能只是空的单元格。把 INDIRECT 函数的结果传递给 ROW 函数后，你会得到一个包含从起始日期到结束日期之间 11,044 天的阵列！这格单元格内的函数是=ROW(INDIRECT(A2”:”B2))。把这个函数放在任意一个日期函数中，如=WEEKDAY(ROW(INDIRECT(A2”:”B2)),2)5，就会检验列阵中的每一天是否落在周末里，而=DAY( ROW(INDIRECT(A2”:”B2)))则会检验列 阵中的每一天是否落在月份的第一天上。图 2 列示了这些函数的变化过程。把真/假转换为 0/1距离 Excel 赢得它与 Lotus 1-2-3 的电子表格战争已经有 15 年了，但我仍然记得 Lotus1-2-3 做得比 Excel 好的一小方面。其中的一个方面就是处理由之前公式产生的真/假值的巨型阵列，Lotus 1-2-3 能非常轻易地把假值赋为零（0）而真值赋为一（1）。不幸的是，Excel需要一些离奇的技巧才能把真/假转换为 1 和 0。 一个常用的技巧是使用两个减号，第一个减号把真值转换为-1，第二个减号把-1 转换为1。这个方法适用于检验单一标准的情况，如计算周末天数的时候。 第二个技巧牵涉到把每个公式乘进一个 SUM PRODUCT 函数里。这个方法适用于检验 两个标准的情况，如周末刚好是月份的第一天。把所有的真值转换为 1 之后，就可以用 SUM 函数把它们全部加起来了。图 3 所示是计算周末的天数、月份的第一天的天数和两者兼有的最终公式。输入公式之后，结果会自动设定为日期格式，我们可以转换回数字格式来看真实的数值。 虽然每个公式都会产生 11,044 个初始值，但你不需要像以往处理阵列那样使用Ctrl+Shift+Enter 键，因为 WEEKDAY、DAY 和其他日期函数在编写的时候都被设定成能够处理阵列的程序。 这项技术运用了很多技巧，任何人想不使用公式求值工具而直接复制图 3 中公式都是不可能的。但按照图 2 所示那样把步骤分解，你就能在需要对一个日期范围内的每一天进行计算的时候使用这项技术。 1 CMA培训黄金品牌 CMA培训黄金品牌