V****e 发帖数: 858 | 1 有点复杂。比如学校里一学期一个学生可以选几门课:有的选两门,有的3门,还有的4
门。怎样在上万个ID中(包括重复的ID),按照课程,把选了两门,选了3门和选了4门课
的学生分别挑选出来。
我真是有点糊涂了。我知道excel可以找到duplicated, 但找到后只能remove
duplicated,不能保存,同时怎样安课程分开这些ID,课程很多。
请帮忙给点建议。谢谢! | a*******s 发帖数: 138 | 2 excel supports sql query.
select student,
count(distinct course) as ncourses
group by 1
order by ncourses; | V****e 发帖数: 858 | 3 谢谢这么快回答我。
那我需要有SQL的软件吗?我工作的computer没有。
在这样的情况下还可以用sql吗?有formula 可以用吗?主要是需要区分student ID,
course level by term.
非常感谢!
【在 a*******s 的大作中提到】![](/moin_static193/solenoid/img/up.png) : excel supports sql query. : select student, : count(distinct course) as ncourses : group by 1 : order by ncourses;
| d*****y 发帖数: 178 | 4 直接excel就能搞定啊。加一个column,用countif把每个学生的选课数计算出来。然后
选了两门的就是2,三门的就是3。我觉得挺简单的,不过大概我没懂你的问题。。。你
可以再问清楚点,应该excel就能搞定。 | V****e 发帖数: 858 | 5 Sorry, not able to type in Chinese now.
The difficulties are: there are three terms per academic year: SU, FA, SP.
Each term the same courses are repeatedly offered. I have three years' data,
so the same courses were offered 9 times.
Do I have to create 9 separated files?
Another difficulty is if the students fail one course, they can re-take the
course at any following term.
By the way, what would be the formula for the "countif":).
Thanks very much for your help!
【在 d*****y 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 直接excel就能搞定啊。加一个column,用countif把每个学生的选课数计算出来。然后 : 选了两门的就是2,三门的就是3。我觉得挺简单的,不过大概我没懂你的问题。。。你 : 可以再问清楚点,应该excel就能搞定。
| a*z 发帖数: 294 | 6 countifs, although sometime it does not work. | d*****y 发帖数: 178 | 7 Sorry that I just see you message here.
Created a file for you to take a look.
check your 站内邮箱
you will need to use countifs here.
data,
the
【在 V****e 的大作中提到】![](/moin_static193/solenoid/img/up.png) : Sorry, not able to type in Chinese now. : The difficulties are: there are three terms per academic year: SU, FA, SP. : Each term the same courses are repeatedly offered. I have three years' data, : so the same courses were offered 9 times. : Do I have to create 9 separated files? : Another difficulty is if the students fail one course, they can re-take the : course at any following term. : By the way, what would be the formula for the "countif":). : Thanks very much for your help!
| T******o 发帖数: 136 | 8 他们说的全部都太复杂,我这个不用键盘就能搞定:select the entire data, insert
, pivot table. creat a pivot table then pull id into a coulmn, then pull
course into the numbers area and set it to be count instead of sum |
|