三种排序的实现

1、对下以表格总分进行排序ABCDEFGHI1考试成绩筛选表2序号学号姓名语文数学英语计算机总分排名3190220002张成祥979493954290220013唐来云807358875390213009张雷857167776490213022韩文歧55337381590213003郑俊霞896277858690213013马云燕916876829790213074王晓燕8679809310890213037贾莉莉9373788811990220023李广林94846086121090216034马丽萍55599876131191214065高云河74778444141291214045王卓然88

2、747778方法1 :先求总分，再按总分高低排序ABCDEFGHI1考试成绩筛选表2序号学号姓名语文数学英语计算机总分排名3190220002张成得979493953794790213024王晓燕867980933385890213037贾莉亲937378883326990220023李广材948460863247690213013马云燕9168768231781291214045王卓然887477783179590213003郑俊膏8962778531310390213009张置_8571677730011290220013唐来云80735887298121090216034马丽萍55599

3、876288131191214065高云河7477844427914490213022韩文歧55337381242输入排名，ABCDEFGHI1考试成绩筛选表2序号学号姓名语文数学英语计算机总分排名3190220002张成祥9794939537914790213024王晓燕8679809333825890213037贾莉莉9373788833236990220023李广林948460863244690213013宜云藏91687682317581291214045王卓然8874777831769590213003郑俊霞89627785313710390713009张雷8571677730081

4、1290220013唐来云807358872989121090216034马丽舛5559987628810131191214065高云河747784442791114490213022韩文歧5533738124212此时序号乱了，要重新按序号排名ACDEFGHI1考试成绩筛选表2序号学号姓名语文数学英语计算机总分排名3190220002张成神9794939537914290220013唐来云8073588729895390213009张雷8571677730086490213022韩文酋55337381242127590213003郑俊食896277853138690213013马云矗9168

5、768231759790213024王晓瓣86798093338210890213037贾莉芜93737883332311990220023李E948460863244121090216034马丽弄5559987628810131191214065高云河7477844427911141291214045王卓然88747778317方法二：直接用rank函数，借助辅列总分，一次操作就可 以排名出来。SL - x V A =GAtfK 由 3, $心 3;1 必用函攻来遂行味匮播名jJ投ABCDEFGHrJ考试成绩筛选表2序号学号姓名语文数学英语计算机总分.排名:rank排名3190220002张

6、成祥97949395.3794290220013唐来云8073588729895390213009张雷8571677730086490213022韩文歧5533738124212590213003郑俊丧896277853138690213013马云燕9168768231759790213024王晓燕86798093338210890213037贾莉莉93737888332311990770023李广林9484608632441210902160345559987628810131191214065高云河7477844427911141291214045王卓然887477783176注意：若分数

7、相同则并列ABCDEFGHIJ1考试成绩筛选表2序号学号姓名语文数学英语计算机总分排名rank排名3190220002张成祥97949395379114290220013唐来云80735887298993390213009张雷85716777300886490213022韩文歧5533738124212127590213003郑俊震896277853137B690213013马云燕9168768231759790213024王晓燕867980933382210890213037贯莉莉937378883323311990220023李广林9484608632444121090216034马朋舛5

8、55998762881010131191214065高云河747784442791111111291214045王卓然8874777831765方法三：用VBA编程实现。不允许用”总分“辅助列,vab 实现。UZ-OUH LL与用VBA通？iJK?壮忒剥中Jdl ACDEFGH1JK1考试成绩筛选表按钮2序号学号姓名语文数学英语计算机总分排名3190220002骚成料97949395379111290220013唐来云S0735087298995390213009张雷85716777300886490213022韩文噂5533738124212127590213003郑俊传8962778531

9、3了78690213013耳云避91687682317559790213024王晓懑867980933382210890213037贾莉装937378883323311990220023李广林94646QS632444121090216U34马丽件555998762881010131191214065高云涧747784442791111141291214045王卓燃88741178317S61516不允许用”总分“辅助列，Vab实现17定义“排名” pm宏，宏代码及解释如下:Sub pm()Dim zfArr(1 To 1000, 1 To 2) As Integer定义一个二维数组,数组长度

10、1000Dim gS As Integer, zF As Integergs=有效数据的行数zF=学生总分成绩gS = Cells(Cells(1000, 1).End(xlUp).Row, 1).Value从第1000行1列单元格向上查找有效数的单元格的值=12End(xlUp)非空单元格Cells(Rows.Count,1).End(xlUp).Row，就是取第一列第一次出现的非空单元格的行号For i = 1 To gSzF = 0For j = 1 To 4zF = zF + Cells(3 + i - 1, 4 + j - 1).Value求出某位学生的总分成绩Next jzfArr(i, 1) = zFzfArr(i, 2) = izfArri1=zFzfArri2 = ii表示学生的序号Next i冒泡排序For i = 1 To gS - 1For j = i + 1 To gSIf zfArr(i, 1) zfArr(j, 1) Thenls1 = zfArr(i, 1)临时变量ls2 = zfArr(i, 2)临时变量zfArr(i, 1) = zfArr(j, 1)交换总分成绩 zfArr(