☰

在 Excel 中将列中的时间序列转换为行中的时间序列

tag-icon • tag-icon microsoft-excel
在 Excel 中将列中的时间序列转换为行中的时间序列

我正在准备擅长电子表格中，我将行中的时间序列数据移动到列中，因为每个类别（在本例中为国家/地区）的数据点数量不同。如果我描述得不够完美，请提前致歉。

我将通过一个例子来解释这一点。这是我目前拥有的模型：

Country         Year     Data
Afghanistan     2000     10  
Afghanistan     2004     12
Afghanistan     2006     15
Afghanistan     2010     30 
Albania         2001     40
Albania         2002     50
Albania         2005     55
Albania         2006     70
Albania         2008     60
Albania         2009     60
Algeria         2000     23
Algeria         2005     30
Algeria         2006     40
Algeria         2007     41
Algeria         2008     39
Algeria         2009     35
Algeria         2010     40  
...

数据集很大并且运行时间较长。

我希望数据集看起来像这样：

Country       2000    2001    2002    2003    2004    2005    2006   2007     2008      2009    2010  
Afghanistan:  10                              12              15                                30  
Albania:              40       50                     55      70              60        60  
Algeria:      23                                      30      40     41       39        35      40 
...

我可以很容易地生成我想要的结构：

Country      2000    2001     2002     2003    2004   2005    2006   2007     2008     2009    2010  
Afghanistan  
Albania    
Algeria      
...

但我无法弄清楚如何填充表格。我认为我需要执行 hlookup 或 vlookup，从列移动到行，并且它需要处理多个匹配条件 - “国家”+“年份”（我尝试连接，但产生了太多数据点：Afghanistan2000，Afghanistan2001，等等）。

而且数据透视表似乎不起作用，因为它会对数据求和，并且复制“if(and(”语句不会起作用，因为它的列到行。

我在网上找遍了所有地方，但还是没能找到解决此问题的工具。尽管如此，我确信有一个简单的解决方案，但我似乎无法找到它。

如果有人能提供帮助，我将不胜感激，因为我获得了很多这种格式的时间序列数据。

答案1

你可以这样做无需任何公式通过使用构建查询获取并转换假设您使用的是 Windows 上最新版本的 Office。

以下是针对您提供的数据执行此操作的方法：

  1. 首先，将原始数据放在表格中（选择范围内的任意单元格 > ALT+N -> T），并确保年份列按从小到大排序
  2. 接下来，从表中进行查询（ALT+O -> PT）
  3. 在查询编辑器中，转到Transform选项卡并选择Pivot Column
  4. 对于Values Column选择Data
  5. 在Home选项卡上选择Close and Load

这将输出下表：

关于这一点最好的部分是，当原始源发生变化时，您只需重新运行查询，ALT+A -> R -> A它就会填充新数据。

答案2

如果数据已排序，那么您可以使用INDEX:INDEX并MATCH像 B2 那样（Sheet1 用于列表，Sheet2 用于假设公式）：
首先设置要查看的范围MATCH

MATCH(Sheet2!$A2,Sheet1!$A:$A,0)
MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1

这样你就得到了国家的第一行和最后一行。现在设置要查看的范围。

INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1)

输出看起来是一样的$C:$C。
为了找到正确的行，我们使用

MATCH(Sheet2!B$1,[Search range formula],0)

将其代入完整公式中

INDEX([output range],[right row])

如果没有找到匹配项，则会出现错误，因此请在其周围放置一个错误部分

IFERROR([full formula],"")

总的来说它看起来应该是这样的

=IFERROR(INDEX(INDEX(Sheet1!$C:$C,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$C:$C,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1),MATCH(Sheet2!B$1,INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A3,Sheet1!$A:$A,0)-1),0)),"")

可以根据需要自动向左和向下填充。只需在两个列表的最后一行后添加一行，如“zzz”，否则最后一个国家将不会输出任何内容。

在此处输入图片描述

通过电话完成，可能包含错误。

答案3

进入

=SUMPRODUCT((TRIM($A$2:$A$18)=TRIM($E3))*(TRIM($B$2:$B$18)=TRIM(F$2))*$C$2:$C$18)

并F3复制/拖动它以填充整个表格（F3:P5在示例中）：

在此处输入图片描述

0当没有数据时，此公式会放置，因此IF()如果您需要空单元格，则可以轻松使用函数。

答案4

感谢大家的建议，不过我最终还是使用数据透视表解决了这个问题。我所要做的就是创建一个数据透视表。将列设置为“年份”，将行设置为“国家”，将值设置为“数据”。

然后我只需将数据的“字段值设置”设置为“最大”，表格就会被填充。最后花了大约 30 秒...

您可以从数据透视表字段对话框中值框中“数据”字段的下拉菜单中访问“字段值设置”。

再次感谢您的回答，我一定会尝试匹配、索引公式。

相关内容