如何在Excel工作表中插入动态时间和日期? -excel学习网-百度知道-英雄云拓展知识分享

该模板可以在任何月份或部门显示相同的报表。他从一个可以显示任何月份信息的Excel仪表板报告开始。

这是许多公司的典型要求。假定“月”是第一个设置，则第2个设置可以是“部门”，“部门”，“产品”，“地区”，“项目”等等。为了展现如何设置第2个设置，我将描写两个简单的报告。示例1说明了我在电子书“使用Excel进行仪表板报告”中描写的一种设置方法，并在IncSight仪表板模板中使用了该方法。示例2显示了具有两个设置的增强版本。让我们从一个简短报告的简单示例开始。 示例1：黄色区域标记此非常简短的报告的打印区域。但是，与大多数Excel报告不同，它是动态的。即，当您更改日期设置时，它会自动更新。D10单元格中的日期设置使用Excel的数据验证列表功能，可以轻松选择正确的报告日期。虽然不是绝对必要的，但我分配了以下范围名称，以使查看公式的工作变得更加容易：代码：= Sheet1！$ C $ 3：$ C $ 7描写：= Sheet1！$ D $ 3：$ D $ 7日期：= Sheet1！$ E $ 2：$ K $ 2数据：= Sheet1！$ E $ 3：$ K $ 7数据验证设置 中显示的单元格的公式为：D10：=日期单元格I9中的公式返回日期范围内指定日期的列索引号：I9：= MATCH（$ D $ 10，Dates，0）这些单元格返回在其左边输入的帐号的行索引号：G11：= MATCH（$ F11，Code，0）G12：= MATCH（$ F12，Code，0）这些单元格返回帐号的标题：H11：= INDEX（Desc，$ G11）H12：= INDEX（Desc，$ G12）最后，对此示例，这些公式将返回指定日期和帐户的值：I11：= INDEX（数据，$ G11，I $ 9）I12：= INDEX（数据，$ G12，I $ 9）实际的动态报告的这一简单框架应当使查看动态报告链接到Excel数据库时的工作方式变得容易。当您在上方的单元格D10当选择新日期时，单元格I9中的MATCH公式将返回该日期的新列索引号。然后，单元格I11和I12中的INDEX公式从数据库中返回新选择日期的值。现在，假定上一个示例中的公司成立了第2个部门。假定一个部门位于俄勒冈州的科瓦利斯，并使用代码CVD；另外一个在华盛顿州的史蒂文斯湖，代码为LSD。这是这两个部门的数据： 图2示例2：一样，黄色区域标记了报表的打印区域。但是与第一个示例不同，该示例同时允许日期和分区设置。该图说明了允许用户指定第2种设置以从Excel数据库中获得数据的几种最简单的方法。在这里，“代码”列使用公式；数据库中的所有其他列均包括值。“代码”列是数据库中的“帮助列”。Helper列使用公式返回其他列或报表所需的值。在此，该列返回的代码结合了部门代码和总帐科目代码。这是显示的单元格的公式：C4：= $ E4＆”-”＆$ D4（如果没法从该字体中清除该字符，则“＆”字符为“＆”号，该字符位于标准键盘的“ 7”键上。）针对需要将此公式向下复制到列中。单元格D13和D14使用Excel的数据验证功能返回显示的值。Div值来自F列的Divs验证列表。就像帮助程序列一样，报告区域的第一列中的公式结合了部门和客户设置：I14：= $ D $ 13＆”-“＆$ H14I15：= $ D $ 13＆”-“＆$ H15其余公式与第一个示例非常类似。这是列索引的公式：L12：= MATCH（$ D $ 14，Dates，0）这是行索引的公式：J14：= MATCH（$ I14，Code，0）J15：= MATCH（$ I15，Code，0）以下是描写的公式：K14：= INDEX（Desc，$ J14）K15：= INDEX（Desc，$ J15）最后，这是值的公式：L14：= INDEX（数据，$ J14，L $ 12）L15：= INDEX（数据，$ J15，L $ 12）当您在示例2的单元格D13中将Div设置更改成“ LSD”时，列I中的报表代码公式将显示该新信息。这将致使重新计算K列中的行公式，这将致使重新计算黄色报表中的Desc和value公式。与之前一样，当您在单元格D15当选择其他日期时，报告也会更新。 温馨提示：要运行此报告的季度，只需输入季度日期。要汇总该季度的数据，您有几种选择：1.您可使用边计算来返回当前和前两个月的数据，然后对其求和。2.可使用宽度值为3的OFFSET函数将参考返回至3个月的数据，然后对参考求和。3.您可使用INDEX（）：INDEX（）为4分之一中的第一个到最后一个单元格指定援用，然后对援用进行求和。方法2和方法3最强大。但是，如果您之前从未使用过它们，则可能很难理解。我将在以后的文章中详细介绍这些方法。正如我所说，这是非常简短的报告。但是有了框架的指点，您应当可以设置自己的动态Excel报表。