☰

按日期将列拆分为多列

tag-icon • tag-icon microsoft-excel vba
按日期将列拆分为多列

Excel 中有一列包含 3,652 个值。如何按日期拆分此列，以便从指定日期开始有十列，每列代表一年？

来自评论：
我正在处理大约 20 年的每日气候数据。这会产生一个包含 2 列的长时间序列，第一列包含日期，第二列包含值。我必须计算年度和每月平均值，为此我必须使用复制和粘贴功能按年或按月排列，这需要花费大量时间和精力。但我读过有关宏和数组函数的文章，但不知道如何编写它们。

答案1

根据你问题中的额外信息，我会添加 3 个额外列，并制作一个数据透视表

我假设日期在 A 列，数据在 B 列。
例如 

日期 降雨量
1994 年 1 月 1 日 0.04
1994 年 1 月 2 日 0.02
...
额外的列将使表格看起来如下：

日期 降雨量 月份 年份 YYYY-MM
1994 年 1 月 1 日 0.04 =MONTH(A2) =YEAR(A2) =TEXT(A2,"YYYY-MM")
1994 年 1 月 2 日 0.02 =MONTH(A3) =YEAR(A3) =TEXT(A3,"YYYY-MM")
...

然后，我可以制作一个数据透视图，其中包含月/年比较（一个轴上表示月份，另一个轴上表示年份），还可以通过沿轴放置相应的列来计算年度和每月的总数

答案2

您可以使用工作表上的数组公式来执行此操作。

继续并手动创建列标题（标记为年份，例如2013，因为这将在公式中使用）。

在第一个标题下方的第一行中，输入以下公式作为数组公式（将公式粘贴到公式栏中并按Ctrl+ Shift+ Enter），其中A2:A3653是日期的大列，B1是第一年列的标题：

=IFERROR(TEXT(SMALL(IF(YEAR($A$2:$A$3653)=B$1,$A$2:$A$3653,""),ROW()-1),"m/d/yyyy"),"")

在此示例中，公式输入在第 2 行。如果您的输出列从不同的行开始，则需要调整ROW() - 1公式的这一部分，使得ROW() - x第一个日期的 等于 1（例如，如果您的标题在第 3 行，而第一行日期在第 4 行，则您需要将公式的这一部分更改为ROW()-3。

此公式可以填充所有 10 列。最后填充到您需要的行数（可能为 400-500 行，具体取决于日期的分布）。数组公式需要进行大量计算，因此计算可能需要几秒钟。

拆分数据后，您可能需要复制所有这些单元格并将值粘贴回同一范围。这将删除公式并提高工作表的性能。

答案3

修改根据 OP 提供的进一步信息

据我了解，您希望将一列值转换为表格，每列代表从指定日期开始的一年的值。在制定答案时，我做了三个假设来处理您的问题提出但未解决的可能性：

  1. 原始列中的值已经与特定日期相关联，即第一个值与特定日期相关联，例如，2013 年 1 月 1 日，第二个值与 1 月 2 日相关联，等等。

  2. 这些值可能是数值数据，也可能是连续的日期。

  3. 指定日期可以是一年中的任意日期。

以下公式将产生您想要的输出。它应输入到输出表的初始单元格中，复制共十列十年的值，并向下复制每年的 366 行值，包括闰年的额外一天的一行。（工作表的完整设置如下图所示。）

=IFERROR
        (
         INDEX
              (
               Data1,
               IF
                 (
                  AND
                     (
                      DATE(YEAR(E$2)+1,MONTH(E$2),DAY(E$2))-E$2=365,
                      ROW(E3)-ROWS(E$2)-1=366
                     ),
                  "-",
                  E$2-$C$2+COUNTA(E$2:E2)
                 ),
               1
              ),
         "-"
        )

这个表达式本质上是一个INDEX公式，用于处理当年份不是闰年时在输出表的第 366 行中显示的内容。Data1是数据值的命名范围；该IF语句返回行索引，非闰年除外，此时它用破折号 (-) 替换输出表第 366 行的计算行索引。

输出表从单元格 E2 开始。如果您的表格在工作表中的位置不同，则需要调整公式中的锚定引用 E$2、$E$2 和 $C$2，以反映表格标题行中第一个单元格的起始位置以及第一个值的日期位置。

下图显示了值（在本例中，只是一组以 2013 年 1 月 1 日开头的日期）、所需的另外两个输入，以及开始日期为 2013 年 1 月 4 日的结果输出表。

图片中间部分显示的是输出表的底部，其中正确显示了 2016 年闰年的一个额外值。

底部显示 3652 个数据值以破折号填充，用于指示由于开始日期在年初之后，第十年不再有值。

使用日期值的数据、输入单元格和输出表

下图显示了使用日期以外的（随机）值作为输入的同一张工作表。输出公式中唯一的变化是使用命名值范围Data2而不是Data1。

使用数值的数据、输入单元格和输出表

相关内容