在Excel中转置数据并与唯一ID匹配

Question

第一个Excel文件包含班级中每个学生的项目分析数据：

Student ID    Item #    Response
101           1         A
101           2         B
101           3         D
101           4         B

当然，这只是一个包含500个唯一学生ID的文件的摘录。

在第二个Excel文件中，我有每个学生的期末成绩：

Student ID    Grade    
101           95         
102           87        
103           65         
104           72

同样，第二个Excel文件有500个唯一的学生ID。我需要创建一个基本上类似于第二个Excel文件的文件，其中每一行代表一个唯一的学生ID，每一列代表每个测试题和最终成绩。我怎样才能做到这一点，而不是求助于复制和转置粘贴？

Answer 1

在最终成绩文件中，为每个项目#添加列。假设第一个项目#在单元格C2中，项目分析数据在工作表Sheet1中，将以下公式放入成绩文件的单元格C2中，使用Ctrl+Shift+Enter (它是一个数组公式)进行验证，然后上下复制。

=INDEX(Sheet1!$C$1:$C$5000,MATCH($A2&"|"&C$1,Sheet1!$A$1:$A$5000&"|"&Sheet1!$B$1:$B$5000,0))

Answer 2

答案是两个步骤。首先，你必须将字母的等级改为数字(对于Excel来说，第二步不能求平均值)

​

我举了一个等级的例子，但你可以按你的意愿改变它。

要切换等级，请使用vlookup函数。让我们假设您的前3个coulmns被占用:学生ID Item # Response，您的成绩表在列e和F上，然后在单元格d2上插入以下内容：

=vlookup(a2,e:f,2,0)

现在，您已准备好计算每个学生的平均值。最好的方法是使用透视表。选择所有数据，然后单击插入功能区以插入数据透视表。在列上拖着学生。和列拖动固定的等级(数字)，剩下的唯一要做的就是将值从计数更改为平均值。