[求助]求变动成本和固定成本 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

Dim x As List(Of String) = DataTables("表A").GetValues("销量", Tables("表A").filter)
Dim y As List(Of String) = DataTables("表A").GetValues("费用", Tables("表A").filter)

Dim avg1 As Double
Dim avg2 As Double
For i As Integer = 0 To x.count - 1
    avg1 += x(i) / x.count
    avg2 += y(i) / y.count
Next
Dim sum1 As Double
Dim sum2 As Double
For i As Integer = 0 To x.count - 1
    sum1 += (x(i)-avg1)*(y(i)-avg2)
    sum2 += (x(i)-avg1)*(x(i)-avg1)
Next
msgbox(sum1 / sum2)

Dim x As List(Of String) = DataTables("表A").GetValues("销量", Tables("表A").filter)
Dim y As List(Of String) = DataTables("表A").GetValues("费用", Tables("表A").filter)

Dim avg1 As Double
Dim avg2 As Double
For i As Integer = 0 To x.count - 1
    avg1 += val(x(i)) / x.count
    avg2 += val(y(i)) / y.count
Next
Dim sum1 As Double
Dim sum2 As Double
For i As Integer = 0 To x.count - 1
    sum1 += (val(x(i))-avg1)*(val(y(i))-avg2)
    sum2 += (val(x(i))-avg1)*(val(x(i))-avg1)
Next
msgbox(sum1 / sum2)

Dim t As Table = Tables("表A")
Dim avg1 As Double
Dim avg2 As Double
Dim sum1 As Double =0
Dim sum2 As Double =0
For Each r As Row In t.rows
sum1 += val(r("销量"))
sum2 += val(r("费用"))
Next

avg1 = sum1 / t.rows.count
avg2= sum2 / t.rows.count

sum1=0
sum2=0
For Each r As Row In t.rows
sum1 += (val(r("销量"))-avg1)*(val(r("费用"))-avg2)
sum2 += (val(r("销量"))-avg1)*(val(r("销量"))-avg1)
Next
msgbox(sum1 / sum2)

[此贴子已经被作者于2017/7/24 16:15:09编辑过]

Dim t As DataTable = DataTables("表A")
Dim bjs As List(Of String) = t.GetValues("部门")
For Each bj As String In bjs
    Dim drs = t.Select("部门 = '" & bj & "'")
    Dim avg1 As Double = 0
    Dim avg2 As Double = 0
    Dim sum1 As Double = 0
    Dim sum2 As Double = 0
    For Each r As DataRow In drs
        sum1 += val(r("销量"))
        sum2 += val(r("费用"))
    Next

    avg1 = sum1 / drs.count
    avg2= sum2 / drs.count
    sum1=0
    sum2=0
    For Each r As DataRow In drs
        sum1 += (val(r("销量"))-avg1)*(val(r("费用"))-avg2)
        sum2 += (val(r("销量"))-avg1)*(val(r("销量"))-avg1)
    Next

    Dim b As Double = sum1 / sum2
    Dim a As Double = avg2 - b*avg1
    msgbox(bj & " " & a)
Next

Dim Filter As String = "1=1" '设置筛选条件
With e.Form.Controls("RQ1") '在前一筛选条件控制下设置第二项筛选条件,其内容根据第一列的变化而变化
    If .text > "" Then
        Filter = Filter & " and 日期 >= #" & .value & "#"
    End If
End With
With e.Form.Controls("RQ2") '在前一筛选条件控制下设置第二项筛选条件,其内容根据第一列的变化而变化
    If .text > "" Then
        Filter = Filter & " and 日期 < #" & .value.adddays(1) & "#"
    End If
End With
Dim t As DataTable = DataTables("表A")
Dim bjs As List(Of String) = t.GetValues("部门",Filter)
For Each bj As String In bjs
    Dim drs = t.Select("部门 = '" & bj & "' and " & filter)
    Dim avg1 As Double = 0
    Dim avg2 As Double = 0
    Dim sum1 As Double = 0
    Dim sum2 As Double = 0
    For Each r As DataRow In drs
        sum1 += val(r("销量"))
        sum2 += val(r("费用"))
    Next

    avg1 = sum1 / drs.count
    avg2= sum2 / drs.count
    sum1=0
    sum2=0
    For Each r As DataRow In drs
        sum1 += (val(r("销量"))-avg1)*(val(r("费用"))-avg2)
        sum2 += (val(r("销量"))-avg1)*(val(r("销量"))-avg1)
    Next

    Dim b As Double = sum1 / sum2
    Dim a As Double = avg2 - b*avg1
    For Each r As DataRow In drs
        r("第四列") = a
    Next
Next

主题：[求助]求变动成本和固定成本