Rss & SiteMap

Foxtable(狐表) http://www.foxtable.com

新一代数据库软件,完美融合Access、Foxpro、Excel、vb.net之优势,人人都能掌握的快速软件开发工具!
共27 条记录, 每页显示 10 条, 页签: [1] [2][3]
[浏览完整版]

标题:求助:下列FOXTABLE文件中“课时变化”表中的“正课统计”按纽公式如何设置?

1楼
abcdzabcdz 发表于:2008/11/9 16:31:00
 

求助:下列FOXTABLE文件中“课时变化”表中的“正课统计”按纽公式如何设置?


目的:为克服用FOXTABLE设计的畏难情绪和提高对FOXTABLE学习兴趣,用易表做了一个文件希望升级到FOXTABLE:一是为了比较易表、FOXTABLE设计文件的区别,二也是为了解决速度问题。


一、原易表文件:

表达式变量:%A1%  

表达式为:If([正课_上午]=-1 And [正课_星期] > "",Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_上午_1]"")") And SetVariable("%B1%",[%B1%]+1) And Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_上午_2]"")") And SetVariable("%B1%",[%B1%]+1) And Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_上午_3]"")") And SetVariable("%B1%",[%B1%]+1) And Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_上午_4]"")") And SetVariable("%B1%",[%B1%]+1),"") And If([正课_下午]=-1 And [正课_星期] > "", Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_下午_5]"")") And SetVariable("%B1%",[%B1%]+1) And Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_下午_6]"")") And SetVariable("%B1%",[%B1%]+1) And Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_下午_7]"")") And SetVariable("%B1%",[%B1%]+1) And Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_下午_8]"")") And SetVariable("%B1%",[%B1%]+1),"") And If([正课_读报]=-1 And [正课_星期] > "",Eval("EvalFor(""表1"","""+Text([%B1%])+""",""[课程表!"+[正课_星期]+"_班主任]"")") And SetVariable("%B1%",[%B1%]+1),"")

数值型变量:%B1%

课时变化中“正课统计”按纽公式为:Eval("EvalFor(""表1"","""+Substitute(ListCols("表1"),"|",""","""") And EvalFor(""表1"",""")+""","""")") And AddCol("表1","k",1, False ) And EvalFor("表1","k","Row") And AddCol("课程表","k",1, False ) And EvalFor("课程表","k","Row") And AddRelation("表1","k","课程表","k") And SetVariable("%B1%",1) And First() And Loop("rowsel < Rows+1", "Eval([%A1%]) And Next()",Rows) And DeleteCol("课程表","k") And DeleteCol("表1","k") And ClearGrid("另表") And evalfor("另表",2,"eval(""AppendFor(""""表1"""",""""""+Substitute(ListCols(""表1""),""|"","""""",""""姓名"""") And AppendFor(""""表1"""","""""")+"""""",""""姓名"""")"")",1,1) And DelFor("另表","姓名","=","") And AddCol("另表","A",1,false) And evalfor("另表","A","if(Instr(""DU"",[姓名])>-1 Or Instr(""/"",[姓名])>-1,0.5,1)") And evalfor("另表","姓名","Substitute([姓名],""DU"","""")") And evalfor("另表","出现次数","SumFor(""另表"",""A"",""姓名"",""="",[姓名])") And DelReptRows("另表","姓名") And evalfor("另表","A","if(Instr(""/"",[姓名])>-1,Split([姓名],""/"",2),"""")") And eval("AddRow(""另表"",""姓名"","""+Substitute(ListFor("另表","A"),"|",""") And AddRow(""另表"",""姓名"",""")+""")") And evalfor("另表","出现次数","if([出现次数]>0,[出现次数],FindText(""另表"",""出现次数"",""A"",""="",[姓名]))") And evalfor("另表","姓名","if(Instr(""/"",[姓名])>-1,Split([姓名],""/"",1),[姓名])") And DeleteCol("另表","A") And ClearGrid("流水表")  And AddRows("流水表",[另表!Rows]-1) And evalfor("流水表","正课_姓名","cell(row,1,""另表"")") And evalfor("流水表","正课_课时","cell(row,2,""另表"")") And   msgbox("正课统计结束",0)


二、设计思路:根据“课时变化”中的情况,将“课程表”中相应的时段内容复制到“表1”中,再对“表1”中姓名计数(见“另表”),最后将“另表”中的数据过到“流水表”。

     注意:1、“课程表”中的“班主任”姓名前均加“DU”,指班主任上“读报课”,读报课只能算0.5节正课。2、对“课程表”中两人名用“/”隔开的,如“蔡欣/唐黎”指两个人各上半节课,计算时拆分为“蔡欣”、“唐黎”两个人,出现次数均为“0.5“。


三、具体操作:可在易表文件的“课时变化”中进行设置,再点出“正课统计”按纽,计算完毕后,可详查其余各表数据。注:表1中的内容来源于“课程表”中相应时段。

 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:1234.rar

2楼
gdtgl 发表于:2008/11/9 19:25:00

1楼的公式看着已是头晕,理解也晕.

我现在认为是foxtable的代码容易理解些:第1步做什么,第2步做什么,第3步.........很清晰


帮顶1下

3楼
gdtgl 发表于:2008/11/9 21:16:00

是统计每月每个老师的上了多少节课吗?图片点击可在新窗口打开查看表的设计真像excel

4楼
gdtgl 发表于:2008/11/10 0:49:00

不太明白表1是用来做什么统计的(要这么多列),写1下填数据代码(不知有没有用)

'将1-31号是星期天有读报的18个班的老师写入表中的F1列中


Dim r1 As List(Of DataRow)  = dataTables("课时变化").select("[正课_星期] <> ''")
Dim na As String = tables("课程表").Cols("星期天_班主任").Name.SubString(0,3)
Dim r2 As List(Of DataRow)  = dataTables("课程表").select("[班级] <> ''")
for each i as datarow in r1
  if i("正课_星期") = na and i("正课_读报") = true then
      for each i2  as datarow in r2
         Dim r As Row = Tables("表1").Rows.AddNew()
               r("F1")  = i2("星期天_班主任")
      next
  end if

[此贴子已经被作者于2008-11-10 0:51:44编辑过]
5楼
yuanbin 发表于:2008/11/10 2:43:00

不知是否理解正确。可以调整一下思路.原来的表1取消,增加一个表“课时名单”作为过度表,为“另表”和“流水表”计算做准备。代码如下。速度很快的,我的电脑耗时4秒。

e.Form.Controls("Label1").text="稍等..."
dim timestart,timeend as date
timestart=date.now
dim s as string ="上午,下午,读报"
dim h as integer =0
DataTables("课时名单").DataRows.Clear()
For Each dr As DataRow In DataTables("课时变化").DataRows
    if dr("正课_星期") isnot nothing  then
        dim ksweek as string =dr("正课_星期")
        for Each dc as Datacol In DataTables("课时变化").Datacols
            if dc.name.IndexOf("_") >0 then
                dim ksspan as string =dc.name.split("_")(1)
                if s.IndexOf(ksspan)>=0 then
                    if dr(dc.name)=true then
                        For Each drkc As DataRow In DataTables("课程表").DataRows
                            for Each dckc as Datacol in DataTables("课程表").Datacols
                                if ksspan="读报" then
                                    ksspan="班主任"
                                end if
                                if dckc.name.IndexOf(ksweek)>=0 and dckc.name.IndexOf(ksspan)>=0 and  drkc(dckc) isnot nothing  then
                                    for j as integer =0 to (drkc(dckc) & "/") .split("/").Length - 1                                       
                                        if (drkc(dckc) & "/") .split("/")(j) <> "" then
                                            Dim drmd As DataRow
                                            h=h+1
                                            drmd = DataTables("课时名单").DataRows.AddNew()
                                            drmd("星期") = ksweek
                                            drmd("时段") = ksspan
                                            drmd("姓名") = (drkc(dckc) & "/") .split("/")(j).Replace("DU","")
                                            IF (drkc(dckc) & "/") .split("/").Length >2 OR (drkc(dckc) & "/").split("/")(j).IndexOf("DU")>=0
                                                drmd("计分") = 0.5
                                            ELSE
                                                 drmd("计分") = 1
                                            END IF
                                        end if   
                                    next                                   
                                end if
                            next
                        next
                    end if
                end if
            end if
        next
    end if
next
DataTables("另表").DataRows.Clear()
dim f As New Filler
f.SourceTable = DataTables("课时名单") '指定数据来源
f.SourceCols = "姓名"   '指定数据来源列
f.DataTable = DataTables("另表") '指定数据接收表
f.DataCols = "姓名" '指定数据接收列
f.Fill() '填充数据
DataTables("另表").datacols("出现次数").Recalc()

DataTables("流水表").DataRows.Clear()
f.DataTable = DataTables("流水表") '指定数据接收表
f.DataCols = "正课_姓名"  '指定数据接收列
f.Fill() '填充数据
DataTables("流水表").datacols("正课_课时").Recalc()
timeend=date.now
e.Form.Controls("Label1").text="耗时" & (timeend-timestart).TotalSeconds & "秒"
Messagebox.Show("处理完毕!")

 下载信息  [文件大小:   下载次数: ]
图片点击可在新窗口打开查看点击浏览该文件:1234.table

6楼
狐狸爸爸 发表于:2008/11/10 8:01:00
老袁是超人,我一看楼主的公式,我就晕了,别说分析和改写了图片点击可在新窗口打开查看
7楼
abcdzabcdz 发表于:2008/11/10 11:08:00
完全正确,调整过后的思路较原思路更简洁,谢谢yuanbin版主!

在易坛上很少见yuanbin版主出手,出手就不同凡响!
8楼
abcdzabcdz 发表于:2008/11/10 12:28:00
以下是引用gdtgl在2008-11-10 0:49:00的发言:

不太明白表1是用来做什么统计的(要这么多列),写1下填数据代码(不知有没有用)

[此贴子已经被作者于2008-11-10 0:51:44编辑过]

原设计中的"表1"是放从"课程表"中相应日期和时段上课教师姓名的.

9楼
yuanbin 发表于:2008/11/10 12:53:00
以下是引用abcdzabcdz在2008-11-10 12:28:00的发言:

原设计中的"表1"是放从"课程表"中相应日期和时段上课教师姓名的.

表1的格式不是数据库处理的好办法。实际上“课程表”属于交叉统计报表,所增加的“课时名单”表才是数据库存储的表现形式,它便于后续统计,也便于生成交叉报表。上述代码的作用就是将“课程表”(交叉报表)到“课时名单”进行一个逆向转换再进行其它的统计分析。

10楼
abcdzabcdz 发表于:2008/11/10 13:10:00

正学习中。。。。。。。

共27 条记录, 每页显示 10 条, 页签: [1] [2][3]

Copyright © 2000 - 2018 foxtable.com Tel: 4000-810-820 粤ICP备11091905号

Powered By Dvbbs Version 8.3.0
Processed in .03906 s, 4 queries.