三種排序的實(shí)現(xiàn)

1、對(duì)下以表格總分進(jìn)行排序ABCDEFGHI1考試成績篩選表2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分排名3190220002張成祥979493954290220013唐來云807358875390213009張雷857167776490213022韓文歧55337381590213003鄭俊霞896277858690213013馬云燕916876829790213074王曉燕8679809310890213037賈莉莉9373788811990220023李廣林94846086121090216034馬麗萍55599876131191214065高云河74778444141291214045王卓然88

2、747778方法1 :先求總分，再按總分高低排序ABCDEFGHI1考試成績篩選表2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分排名3190220002張成得979493953794790213024王曉燕867980933385890213037賈莉親937378883326990220023李廣材948460863247690213013馬云燕9168768231781291214045王卓然887477783179590213003鄭俊膏8962778531310390213009張置_8571677730011290220013唐來云80735887298121090216034馬麗萍55599

3、876288131191214065高云河7477844427914490213022韓文歧55337381242輸入排名，ABCDEFGHI1考試成績篩選表2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分排名3190220002張成祥9794939537914790213024王曉燕8679809333825890213037賈莉莉9373788833236990220023李廣林948460863244690213013宜云藏91687682317581291214045王卓然8874777831769590213003鄭俊霞89627785313710390713009張雷8571677730081

4、1290220013唐來云807358872989121090216034馬麗舛5559987628810131191214065高云河747784442791114490213022韓文歧5533738124212此時(shí)序號(hào)亂了，要重新按序號(hào)排名ACDEFGHI1考試成績篩選表2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分排名3190220002張成神9794939537914290220013唐來云8073588729895390213009張雷8571677730086490213022韓文酋55337381242127590213003鄭俊食896277853138690213013馬云矗9168

5、768231759790213024王曉瓣86798093338210890213037賈莉蕪93737883332311990220023李E948460863244121090216034馬麗弄5559987628810131191214065高云河7477844427911141291214045王卓然88747778317方法二：直接用rank函數(shù)，借助輔列總分，一次操作就可 以排名出來。SL - x V A =GAtfK 由 3, $心 3;1 必用函攻來遂行味匱播名jJ投ABCDEFGHrJ考試成績篩選表2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分.排名:rank排名3190220002張

6、成祥97949395.3794290220013唐來云8073588729895390213009張雷8571677730086490213022韓文歧5533738124212590213003鄭俊喪896277853138690213013馬云燕9168768231759790213024王曉燕86798093338210890213037賈莉莉93737888332311990770023李廣林9484608632441210902160345559987628810131191214065高云河7477844427911141291214045王卓然887477783176注意：若分?jǐn)?shù)

7、相同則并列ABCDEFGHIJ1考試成績篩選表2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分排名rank排名3190220002張成祥97949395379114290220013唐來云80735887298993390213009張雷85716777300886490213022韓文歧5533738124212127590213003鄭俊震896277853137B690213013馬云燕9168768231759790213024王曉燕867980933382210890213037貫莉莉937378883323311990220023李廣林9484608632444121090216034馬朋舛5

8、55998762881010131191214065高云河747784442791111111291214045王卓然8874777831765方法三：用VBA編程實(shí)現(xiàn)。不允許用”總分“輔助列,vab 實(shí)現(xiàn)。UZ-OUH LL與用VBA通？iJK?壯忒剝中Jdl ACDEFGH1JK1考試成績篩選表按鈕2序號(hào)學(xué)號(hào)姓名語文數(shù)學(xué)英語計(jì)算機(jī)總分排名3190220002騷成料97949395379111290220013唐來云S0735087298995390213009張雷85716777300886490213022韓文噂5533738124212127590213003鄭俊傳8962778531

9、3了78690213013耳云避91687682317559790213024王曉懣867980933382210890213037賈莉裝937378883323311990220023李廣林94646QS632444121090216U34馬麗件555998762881010131191214065高云澗747784442791111141291214045王卓燃88741178317S61516不允許用”總分“輔助列，Vab實(shí)現(xiàn)17定義“排名” pm宏，宏代碼及解釋如下:Sub pm()Dim zfArr(1 To 1000, 1 To 2) As Integer定義一個(gè)二維數(shù)組,數(shù)組長度

10、1000Dim gS As Integer, zF As Integergs=有效數(shù)據(jù)的行數(shù)zF=學(xué)生總分成績gS = Cells(Cells(1000, 1).End(xlUp).Row, 1).Value從第1000行1列單元格向上查找有效數(shù)的單元格的值=12End(xlUp)非空單元格Cells(Rows.Count,1).End(xlUp).Row，就是取第一列第一次出現(xiàn)的非空單元格的行號(hào)For i = 1 To gSzF = 0For j = 1 To 4zF = zF + Cells(3 + i - 1, 4 + j - 1).Value求出某位學(xué)生的總分成績Next jzfArr(i, 1) = zFzfArr(i, 2) = izfArri1=zFzfArri2 = ii表示學(xué)生的序號(hào)Next i冒泡排序For i = 1 To gS - 1For j = i + 1 To gSIf zfArr(i, 1) zfArr(j, 1) Thenls1 = zfArr(i, 1)臨時(shí)變量ls2 = zfArr(i, 2)臨時(shí)變量zfArr(i, 1) = zfArr(j, 1)交換總分成績 zfArr(