Excel中利用OFFSET函数解决很多个分块的一次性转置问题(非纯手动)
今天遇到一个问题,需要将Excel中的一门课的平时成绩根据每个同学的作业次数进行转置排列。
原始EXCEL表中内容如下:
上表每个同学对应一个学号,该学号同学的作业和分数相应已知,现在为了统计方便,我要得到如下表的效果:
因为学生人数众多,所有不能手动一个个调,需要利用excel的强大功能,因此我查到了可以使用OFFSET函数。
下面我将给出步骤和解释,如何通过上面第一张表来获得第二张表的效果:
首先,在第一张表的基础上进行修改,选中C2单元格,然后再输入框中输入OFFSET($B$1,(ROW()-2)*7+COLUMN()-COLUMN($B$1),0,1,1) 如下表,
offset函数的语法格式在百度上可以得到,这里为了方便大家,给出如下:
offset函数的语法格式
=offset(reference,rows,cols,height,width)
=Offset(参照单元格,行偏移量,列偏移量,返回几行,返回几列)
我们将B1作为参照单元格,写成$B$1的形式(注意这里我们将B1作为ROW()的固定参数使用,即不随单元格的变化而发生变化,所以不能写成B1的形式)
ROW()表示选定当前单元格的行号(如选定C2后,ROW()表示返回C2的行号2),同理,COLUMN()表示选定当前单元格的列号。若在选中当前单元格C2的情况下,想要获得其他单元格的行号,且不随单元格变化而变化,(如B1),则写为COLUMN($B$1)。因此OFFSET($B$1,(ROW()-2)*7+COLUMN()-COLUMN($B$1),0,1,1)表示以B1作为参照单元格不变,向下偏移(ROW()-2)*7+COLUMN()-COLUMN($B$1)个单位,向右偏移0个单位(即向右不偏移),最后,返回值为1行1列(即1个数)。
在选中C2单元格的情况下,点击回车之后,生成95:
右拉单元格,得到:
这样,由上表可以发现,第一个学号同学的六次成绩已经由纵列转化为行。
所有其他同学的成绩,只要下拉C2-H2单元格即可得到:
上表中C-H列即为转化之后的成绩,但是学号和每一行成绩不匹配,因此,我们新建一张表,将每一个学号所占的间距缩小到一行单元格,并将倒数第二章表的
C-E列复制到新表中,最终得到的结果如下:
因此,到这里,我们就解决了目标问题。