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

求助：下列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

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

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


帮顶1下

是统计每月每个老师的上了多少节课吗?表的设计真像excel

不太明白表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

不知是否理解正确。可以调整一下思路.原来的表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

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

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

正学习中。。。。。。。