Excel:将多行合并为

Excel:将多行合并为

我从事一个项目,需要重新格式化大量实验室结果数据集。现在,每个实验室测试都与研究参与者的 ID 相关联 - 每个测试占一行。我需要重新格式化数据,以便每个参与者在最多 26 个测试中每个测试只有一行和一列。

现在:

ID....TEST....VALUE  
ID001 TEST1 25     
ID001 TEST2 30  
ID001 TEST3 54  
ID002 TEST1 23   
ID002 TEST2 54  
ID002 TEST3 67  
etc          

目标:

ID  TEST1 TEST2 TEST  
ID001 25 30 54  
ID002 23 54 67  
etc  

这在 Excel 2010 中可行吗?如能提供任何帮助,我将不胜感激。

答案1

这是人们使用数据透视表进行的典型工作:

  • 选择您的数据并插入数据透视表
  • 添加 ID 作为行标签
  • 添加测试作为列标题
  • 将数字添加到数据透视表的主体中,可以使用总和或平均值,因为您在原点中只有一行外部项,因此询问天数两者都是相同的

答案2

我会非常小心地使用数据透视表。我曾遇到过几次非常糟糕的结果,它们没有如实地返回正确的值,总数不正确,还有许多其他问题。

这是我对如何处理此问题的建议,并且适用于任何版本的 Excel。

您首先需要的是 唯一键,以便您可以在数据集中查找值。在 ID 前插入一列,然后将公式添加=B2&C2到单元格 中A2。然后只需将其复制到整个列中,您就会得到一个类似于 的标识符ID001TEST1

接下来,您要命名数据范围。最简单的方法是突出显示从A1到的整个数据集Dx,单击 A 列上方的小框并输入Data。这将命名您的范围。您的数据现在将如下所示:

              ID    TEST    VALUE
ID001TEST1  ID001   TEST1   25
ID001TEST2  ID001   TEST2   30
ID001TEST3  ID001   TEST3   54
ID002TEST1  ID002   TEST1   23
ID002TEST2  ID002   TEST2   54
ID002TEST3  ID002   TEST3   67

现在创建一个新工作表并在列标题和参与者 ID 中输入以下内容

    A      B        C       D
1  ID      TEST1    TEST2   TEST3
2  ID001    
3  ID002    

一旦你有了它,你将创建以下公式并粘贴到B2

=VLOOKUP($A2&B$1,Data,4,0)

基本上,第一个参数通过连接 A 列中的参与者 ID 和第 1 行中的列标题来创建唯一键。美元符号将第二部分的列固定为 A,将行固定为 1,这样当您复制并粘贴公式时,它将生成您要查找的唯一键。现在,您可以将该公式粘贴到表格主体中,它应该会正确地为您提供每个值。

第二个参数是您之前设置的数据集的名称。使用该名称的好处是,当您复制和粘贴它时不会扰乱单元格引用。

第三个参数 4 代表值行,函数将从这里提取与查找键匹配的数据。

最后一个参数 0 告诉查找函数如果没有找到该键的值则返回 #N/A。

最终结果将如下所示:

ID     TEST1    TEST2   TEST3
ID001   25       30      54
ID002   23       54      67

您必须记住的是,无论您是在对平面文件数据库进行排序还是查询,您都需要有一个唯一的数据键才能进行排序或查找。诀窍是通过连接两个或三个数据点,如果尚未提供唯一键,您通常可以创建一个唯一键。这就是魔力所在,其他一切都只是您如何格式化结果。

相关内容