近期接了一个小任务，对本单位学生的每学期的成绩进行重新估算。获得的数据是長数据，为了处理方便，需要将其转化成宽数据。
長数据转成宽数据
在此之前，需要根据研究目的对数据进行清理。拿到的原始数据是这样的：
原始数据展示
本次清理对象只针对学生初次考试成绩，因此如上图所示的张三同学在“船舶与海洋工程结构力学”课程中的补考成绩就被剔除在外。当然，进一步可以探索用补考成绩替代正常考试成绩，该功能在本例中不涉及。
在Excel中实现从长数据向宽数据的转换，使用的命令是vlookup。
其原理是：每个学生 + 每门课程对应到一个考分，利用vlookup寻找到这样的数据，将它赋予在一个新的格子里。由于学生可能出现重名，而学生的学号是唯一的，因此我们使用“学号”与“课程名称”的匹配。
Step 1: 考虑到学号是否有录入错误，所以需要做一个学号重复的筛查。
重复学号筛查
筛查结果
Step 2：在原始数据中生成一个新的变量，该变量是“学号+课程名称”。使用的命令是 [=A1&"+"&B1]，A列是学号，B列是课程名。需要注意，这里是对字符串类型数据的相加。
生成 学号+课程名称 新变量
问题来了，如何把该变量（C列）中的格值填满。最常用的是用格值右下角的十字符号下拉。
这里有个十字加号
但我们有44433条数据，这得拉到啥时候……
快速填充公式的方法如下：①首先判断一下该列数据最后一条的序号是多少，可以用“Ctrl+向下箭头”1秒到达。
最后1条数据是44433号
在下图红框位置数据数据范围，公式覆盖的第1个格是C1，公式覆盖最后1个格是C44433，用冒号[ C1:C44433 ]区分。输入后用“Ctrl+Enter”选取全部数据范围。
数据范围
而后将鼠标挪到公式栏中公式最后1个字符后，再次用“Ctrl+Enter”将该数据范围将公式填满。
用公式覆盖后的数据范围
Step 3：生成一个新的sheet，将学号copy到新sheet中，并删除重复值，每个学号只留1个；将课程名称变量也copy到新sheet中，删除重复值，转置后作为新sheet中的第1行。
完成后
需要注意，Excel中每个sheet的总行数 和 总列数有最大值，超出最大值会出现无法转置粘贴（尤其是列数）。
Step 4：原数据在“长数据”sheet中，待转换的数据在“宽数据”sheet中。现在要做的是从“长数据”sheet中检索“学号+课程名称”变量，并在“宽数据”sheet中找到对应的位置，并填充数据。
使用的命令如下：
主命令
检索到第1个数据
下面对主命的构成令进行解释：
解释1：修订
vlookup是命令主体
解释2：数据源的sheet名
解释3：目标数据sheet
在解释3图中，A2表示“2010011603”学号，B1表示“船舶与海洋…”课程，其中的美元符号表示固定，其后跟的字母（如A）或数字（如1）表示固定的列（或行），即检索时只能在固定列（或固定行）中进行，不能跨列（或跨行）。
下面要做的就是在宽数据sheet的数据范围内将用公式覆盖，覆盖方法见之前所述。
覆盖数据
最终结果
该做的都做完了，剩下的处理就是后续的问题了。
注意，如果电脑运算能力不强，这个过程比较缓慢。一定要让电脑自己把数据都跑完，否则没跑到的数据会展示为0值。
网友评论