我是 Excel 新手。这是我的电子表格的简化版本:
start date end date eday duration
-21 4
12 0
begindate = "03/03/2020"
我想创建公式来根据 eday 和 duration 列以及 begindate 中的值来计算开始日期和结束日期:
start date = begindate + eday
end date = start date + duration
因此,对于上述示例,如果我应用公式,我会期望得到以下结果:
start date end date eday duration
02/11/2020 02/11/2020 -21 4
03/15/2020 03/19/2020 12 0
begindate03/03/2020
将始终相同。
我能够使公式起作用,但是当我使用填充柄时,它无法正确复制公式。
请帮助新手解决这些问题:
存储开始日期“03/03/2020”的最佳位置在哪里 - 单独的工作表?硬编码吗?其他?
开始日期和结束日期的公式是什么?
如何将公式复制到列中的所有行中
非常感谢。
答案1
关于 Excel 中的日期和时间,我首先要记住的是,它们实际上只是数字,使用数字格式将其格式化为看起来像日期/时间。今天的日期(2019 年 9 月 7 日)是 43715,当前时间(凌晨 12:20)是 0.14302083...
有一些工作表函数可以为您获取神奇数字(基本上是自 1900 年 1 月 1 日以来的天数),例如DATE()
或。TODAY()
NOW()
为此,假设你的埃迪列是C
,你的数据从行开始2
,你可以输入你的开始日期公式在A2
和=DATE(2020,3,3)+$C2
你的结束日期公式为B2
。=$A2+$D2
这是最简单的方法。
请注意,我使用了绝对列引用($C
、$A
、$D
)来强制 Excel 始终在 、 和 列中查找C
,A
而D
不管我在哪里复制/粘贴或填充,并且我使用了相对行引用(2
),这样,如果我复制/粘贴或向下填充,行号将作为填充的一部分而更改(即在行 上3
,公式将显示=$A3+$D3
)。有时您想要这样,有时您不需要,因此您应该尝试使用$
和 来$
查看它的作用。
我怀疑你最初把你的开始日期单元格中的值,但在公式中使用了相对引用,并且当您向下填充时,您的相对引用开始日期改变。如果我是对的,那么你需要一个绝对的参考来开始日期单元格,例如,如果它位于列J
行中1
,则您的公式应该读取$J$1
,而不是J1
。
随着你越来越自信,你可以使用更复杂的功能来提高其弹性或可配置性。例如:
您可以使用姓名经理
BeginDate
通过创建一个名为 的新名称并将其值设置为 来存储固定的常量日期=DATE(2020,3,3)
。现在,您可以将 中的开始日期公式更新A2
为=BeginDate+$C2
,并且该固定值将在任何地方使用。您可以将四列范围格式化为带标题的表格,这将允许您使用这些标题名称而不是列引用。如果您将范围格式化为表格,则在第一个数据行中开始日期列(这是
A2
)你可以输入=BeginDate+[eday]
和在你的结束日期列(原为B2
)中,您可以输入=[start date]+[duration]
。由于这是一张表格,因此随着表格的增长,两个公式都会自动填充。现在应用一个简单的数字格式短日期你的开始日期和结束日期列。您可以停止表格显示计算日期,当您没有输入任何值时,
eday
或者duration
通过更改公式开始日期更复杂的是:=IF([eday]="", "", BeginDate+[eday])
...并改变公式结束日期到:
=IF(OR([start date]="",[duration]=""), "", [start date]+[duration])