这张表是学生的犯错记录表,一个人可能有不同的错误。
现在需要一张表,表的第一列就是学生,第二列就是它翻过的所有错误。
第一列的学生姓名不能重复。
sql实现
这个问题如果使用数据库,写几行sql就搞定了
excel实现
但有时操纵数据也不能总是依赖数据库,下面我说的是我自创的五步分组法,使用excel来达到sql语言中group_concat的效果
第一步,数据透视表
通过数据透视表可以查清每个人都犯过哪些错误,犯过错误的计1。这里的话一个错误只犯了一次,因此要么是1要么是空白的
第二部,复制数据透视表并整理表格
使用按数值复制,并且把多余的东西(比如说总计行和总计列这些删掉)。
最后将姓名这一列原封不动地复制出来,要求顺序不能乱
第三步,使用if函数
这里我解释以下,就是说H列姓名后面,使用if函数,如果B2数值是1,那么就是说陈颖犯过A类错误,把B1单元格--也就是A类错误给它。如果没有就给个空格
这里同样出现了绝对引用和相对引用。B2为什么是相对引用?因为陈颖后面还有李四所以行要变,A类错误后面还有B类错误所以列要变。
那么B$1为什么是绝对引用呢。看函数,如果B2单元格是1,说明陈颖犯过A类错误。单元格往下拉的的时候B列还是B列,但是第一行是不能变的,因为错误ABC都在第一行。想象一下,如果往右拉,列就变了,B$1就变成了C$1。函数变为=if(c2=1,c$1,"")。看的就是c2单元格是1吗(也即是看他有没有犯过B类错误),如果是就返回c1这个值--也就是B,如果不是就返回空白。
第四步,concat函数
第三步匹配完后,所有错误都列出来了。使用concat函数将错误都连接起来。
concat函数都作用是将选中的单元格中的文本全都连接在一起。
第五步复制粘贴
将结果复制出来即可
优劣对比
这个需求使用sql的优势就是方便,几行代码搞定。劣势就是如果数据库系统出问题用不了就歇菜了
使用excel的劣势就是步骤有点多,操作起来没sql那么方便。而且如果错误类型过多,例子中只有3种,如果是30种,300种呢?假如类型过多,那么用excel就不行了必须启动数据库。
优势就是excel启动方便,随时随地可以使用