我从事一个项目,需要重新格式化大量实验室结果数据集。现在,每个实验室测试都与研究参与者的 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
您必须记住的是,无论您是在对平面文件数据库进行排序还是查询,您都需要有一个唯一的数据键才能进行排序或查找。诀窍是通过连接两个或三个数据点,如果尚未提供唯一键,您通常可以创建一个唯一键。这就是魔力所在,其他一切都只是您如何格式化结果。