每个月我都需要制作一份大型电子表格以用于计费。
在其他列中,我有一个开始日期和另一个结束日期,格式为 dd-mmm。要在 3 月的电子表格中输入此日期,我输入(例如)12/3 以获得“12-Mar” - 年份不是必需的。3 月电子表格中的所有日期都将在 3 月(但我准备了下个月的电子表格)。
我想简化数据输入,因此我只需要在一个单元格中指定月份,然后在一个带有 dd 数字的列中输入,并有一个输出列显示日期为 dd-mmm。
有什么建议可以实现这一点吗?
答案1
您可以使用多种方式使用公式创建日期,但我更喜欢这种方式。假设在 A1 中输入开始日期 01/mm/yyyy - 始终为每月 1 日,例如:
=1/3/2021
如果您在美国,则可能是 mm/dd/yyyy。最好也包括年份,以便正确处理闰日。现在,在您的 dd 输入列(例如 C1 到 C1000)中,只需捕获 1 或 2 或 31 或月份的任何一天的数据。现在在开始日期中输入
=$A$1+C1-1
答案2
答案Mobus
是一个很好的例子,它跳出了问题的正常 Excel 方面:基本上,他希望在某处输入月份的第一天,也许是列中的第一个条目,然后,由于输入的月份的任何其他日期都是月份的日期,如果你将它添加到第一天并减去一天,这样你就不会重复计算第一天(输入的日期还包括第一天,因此它将被计入第一个条目和新条目中),然后你就得到了该条目日期的值。
非常好,在弄清楚 Excel 需要做什么之前,先使用代数来简化问题。
但是,它并不涵盖所有可能的条目。例如,您使用输入 3 月 12 日作为第一个条目的示例。因此它会失败。如果您可以重新调整数据输入,或者如果您在某处留出一个 ell 来输入 3 月 1 日、4 月 1 日等日期,然后将其用作添加条目并从中减去 1 的参考,则不会出现问题。这两种方法都有效,而且他的非常直接的方法有效。
但是如果您愿意像现在这样去做,不改变数据输入的性质(毕竟,3 月 1 日可能不是您本月需要输入的日期,因此可能会很尴尬......)并且不改变电子表格的形式以包括一种方法(单元格、命名范围等)将月份的第一天用于计算,那么还有另一种方法,一种方法是为此目的内置在 Excel 中。
这是使用函数DATEVALUE()
。它以包含日期信息的字符串为输入,并返回正确的 Excel 样式日期。这样它也会包含年份,但只需将输出列格式化为显示“dd-mmm”,即可获得所需的显示。
假设输入列是 A 列,其中的第一个单元格 A1 是输入的第一个日期。您可以按照问题中所述输入它(并根据需要设置其格式),但由于 Excel 将其视为日期,因此它将包含年份,无论您如何显示它。然后,它将成为您获取 Excel 在公式中使用的月份和年份的参考。您将从数据输入单元格中获取日期:
=DATEVALUE( A2 & "/" & MONTH($A$1) & "/" & YEAR($A$1) )
该公式通过获取输入的日期值、将查找/
附加到第一个单元格 A1 以获取月份值并将其附加到该单元格、然后附加另一个单元格/
并最终在 A1 中查找年份来构建字符串。然后显示该字符串,DATEVALUE()
将其转换为“正确”的日期。它会按照您设置的格式显示,以便您拥有它。
请注意,它从不查找日期值的第一个条目,只查找月份和年份,因此即使它是月份中的随机一天,例如 3 月 12 日,所有其他日期也会正确计算和显示。
一切按您当前的方式进行,只需在第一个输入单元格中输入 12/3,然后在所有其他单元格中输入每一天的数值的一位或两位数字。您和其他用户的工作习惯都无需改变。无需培训,无需抱怨,无需重新组织电子表格。