- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- 如何写班级的排名公式 (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=1100)
-- 发布时间：2008/11/11 15:29:00
-- 如何写班级的排名公式
"统计年级排名公式"仿了一个,但是"按班级统计排名"怎么也写不出来了,弄了一个下午头都大了,大家看看
总分及各学科按班级排名怎么写
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:学生成绩统计分析表.rar |
-- 发布时间：2008/11/11 16:25:00
--
班级列列类型改成字符型。
e.Form.Controls("Label1").text="请稍等,正在计时..."
dim timestart,timeend as date
timestart=date.now
CurrentTable.Redraw = False \'暂停刷新表
For Each c as Datacol in DataTables("学生各科成绩表").Datacols \'遍历学生各科成绩表中的所有列
if c.name = "语文" Or c.name = "数学" or c.name = "英语" or c.name = "化学" or c.name = "物理" or c.name = "总分" Then \'如果等于这些列
Dim pm As New List(Of String) \'定义一个字符型的集合
Dim pmc As Integer \'定义一个数值型变量
pm = CurrentTable.DataTable.GetUniqueValues("","班级") \'从学生各科成绩表中提取不重复的班级保存到集合中
For i As Integer = 0 To pm.Count -1 \'遍历班级集合
CurrentTable.Filter= "[班级] = \'" & pm(i) & "\'" \'\'筛选
Currenttable.sort = c.name & " DESC" \'按降序排序
pmc = 0 \'赋于变量初始值
For n As integer = 0 to Currenttable.rows.Count -1 \'遍历学生各科成绩表中的所有行
pmc = pmc +1
If n > 0 AndAlso Currenttable.rows(n)(c.name) = Currenttable.rows(n-1)(c.name) Then \'如果不是第一行且计算行等于上一行的值
CurrentTable.Rows(n)(c.name & "班级排名") = CurrentTable.Rows(n-1)(c.name & "班级排名") \'就赋于上一行的值
Else
CurrentTable.Rows(n)(c.name & "班级排名") = pmc \'当前计算行的行号加 1(变量pmc的步长值为1)
End If
Next
Next
End if
Next
CurrentTable.Filter = "" \'取消筛选
CurrentTable.Redraw = True \'刷新表
Tables("学生各科成绩表").Sort = "总分 DESC" \'排序,对总分列以降序排序
timeend=date.now
e.Form.Controls("Label1").text="耗时" & (timeend-timestart).TotalSeconds & "秒"
Messagebox.Show("统计完毕!")
-- 发布时间：2008/11/11 17:04:00
--
为什么要改为字符型,不太懂!!!!!!!!!
-- 发布时间：2008/11/11 17:12:00
--
如果不改也可以：
CurrentTable.Filter= "[班级] = \'" & pm(i) & "\'" \'\'筛选
改为
CurrentTable.Filter= "[班级] = " & pm(i)
-- 发布时间：2008/11/11 17:15:00
--
如果不改也可以：
CurrentTable.Filter= "[班级] = \'" & pm(i) & "\'" \'\'筛选
改为
CurrentTable.Filter= "[班级] = " & pm(i)
呵，忘了双精度小数现在也可以筛选了
-- 发布时间：2008/11/11 17:24:00
--
速度太慢了,用易表才用26秒的时间,用它40多秒,天啊!!!!!
-- 发布时间：2008/11/11 17:27:00
--
改成字符型后,更慢了,没有办法,又改回来了.
-- 发布时间：2008/11/12 6:58:00
--
统计班级各项排名相当慢,是不是把简单问题弄复杂了.看看图
此主题相关图片如下：2.jpg
-- 发布时间：2008/11/12 8:22:00
--
呵呵，我怎么测试才一秒左右啊。
用下面的代码应该好一点。
还有要注意不是所有的列都应该设置成双精度型，排名、班级、学号这样的列应该是整数型。
e.Form.Controls("Label1").text="请稍等,正在计时..."
dim timestart,timeend as date
timestart=date.now
Dim pm As List(Of String) = CurrentTable.DataTable.GetUniqueValues("","班级")
Dim pmc As Integer
Dim ColNames() As String = {"数学","英语","化学","物理","总分"}
Dim TotalName As String
CurrentTable.Redraw = False \'刷新表
For Each ColName As string In ColNames
For i As Integer = 0 To pm.Count -1
CurrentTable.Filter= "[班级] = \'" & pm(i) & "\'"
Currenttable.sort = ColName & " DESC"
pmc = 0
TotalName = ColName & "班级排名"
For n As integer = 0 to Currenttable.rows.Count -1
pmc = pmc +1
If n > 0 AndAlso Currenttable.rows(n)(TotalName ) = Currenttable.rows(n-1)(TotalName ) Then
CurrentTable.Rows(n)(TotalName ) = CurrentTable.Rows(n-1)(TotalName )
Else
CurrentTable.Rows(n)(TotalName ) = pmc
End If
Next
Next
Next
Tables("学生各科成绩表").Sort = "总分 DESC" \'排序,对总分列以降序排序
CurrentTable.Filter = "" \'取消筛选
CurrentTable.Redraw = True \'刷新表
timeend=date.now
e.Form.Controls("Label1").text="耗时" & (timeend-timestart).TotalSeconds & "秒"
Messagebox.Show("统计完毕!")
| 下载信息 [文件大小： 下载次数： ] | |
| 点击浏览该文件:学生成绩统计分析表.rar |
-- 发布时间：2008/11/12 9:06:00
--
呵呵，我怎么测试才一秒左右啊。
用下面的代码应该好一点。
还有要注意不是所有的列都应该设置成双精度型，排名、班级、学号这样的列应该是整数型。
e.Form.Controls("Label1").text="请稍等,正在计时..."
dim timestart,timeend as date
timestart=date.now
Dim pm As List(Of String) = CurrentTable.DataTable.GetUniqueValues("","班级")
Dim pmc As Integer
Dim ColNames() As String = {"数学","英语","化学","物理","总分"}
Dim TotalName As String
CurrentTable.Redraw = False \'刷新表
For Each ColName As string In ColNames
For i As Integer = 0 To pm.Count -1
CurrentTable.Filter= "[班级] = \'" & pm(i) & "\'"
Currenttable.sort = ColName & " DESC"
pmc = 0
TotalName = ColName & "班级排名"
For n As integer = 0 to Currenttable.rows.Count -1
pmc = pmc +1
If n > 0 AndAlso Currenttable.rows(n)(TotalName ) = Currenttable.rows(n-1)(TotalName ) Then
CurrentTable.Rows(n)(TotalName ) = CurrentTable.Rows(n-1)(TotalName )
Else
CurrentTable.Rows(n)(TotalName ) = pmc
End If
Next
Next
Next
Tables("学生各科成绩表").Sort = "总分 DESC" \'排序,对总分列以降序排序
CurrentTable.Filter = "" \'取消筛选
CurrentTable.Redraw = True \'刷新表
timeend=date.now
e.Form.Controls("Label1").text="耗时" & (timeend-timestart).TotalSeconds & "秒"
Messagebox.Show("统计完毕!")
| 下载信息 [文件大小：127.3 KB 下载次数：1] | |
 点击浏览该文件:学生成绩统计分析表.rar |
一个字 好