Question

I have a table of several independent variables that I need to calculate a formula from to generate the dependent variable. Though trial I have come up with a value for the dependent variable. For example, I have a table like this:

x1 | x2 | x3 || z(value found by experiment)
-------------------
1  | 2  | 3  || 10
3  | 4  | 5  || 14
2  | 3  | 3  || 15
1  | 2  | 7  || 9

我现在想有一个这样的公式:

f(x1,x2,x3) = z

现在,我如何走下去,获得价值? 你们能否向我指出一些资源?

我感到,我从我的学日,但我想记住这一点。

<<>Also>: 你们是否知道对我来说这样做的任何工具? 我有前言,但我无法说明如何以不止一个变量来扭转。

Answer 1

你们谈论的是regression analysis。如果关系是线性关系,则是,多线性回归。 (不止一个独立变量、一个依赖变量、线性关系) 见进一步信息链接

<>载体> 与2007年Excel公司进行这一分析: 你们必须首先使办公室日志(左上)和“超选”分析工具Pak;Add-Ins >

然后,你可以选择“分析”; 回归数据栏,你可以把多个栏目作为你们的投入范围。 http://www.jeremymiles.co.uk/regressionbook/extras/appendix2/excel/“rel=“nofollow noreferer”>here 。关于使用回归工具的更多细节,它倾向于采用旧版本的Excel,但回归工具也一样。

Answer 2

简单的办法是使用VBA,而不是复杂的回归方法。您可使用多种变量,并取得多重结果。

表(表1):

x1 is cell A1 in VBA sheet1.cells(1,1)
x2 is cell B1 in VBA sheet1.cells(1,2)
x3 is cell C1 in VBA sheet1.cells(1,3)
z is cell D1 in VBA sheet1.cells(1,4)

计算方法是第2版(表2);

cell A1 = x1 (variable; input for formulas)
cell A2 = x2 (variable; input for formulas)
cell A3 = x3 (variable; input for formulas)
cell A4 = z1 (result)

B. 创建Button和VBA的类型代码

Private Sub CommandButton1_Click()

 rowCount is numbers of rows in your table(list1)
for m = 0 to rowCount-1

      set table data to calculations
      set x1
     sheet2.Cells(1, 1) = sheet1.Cells(2 + m, 1)
      set x2
     sheet2.Cells(1, 2) = sheet1.Cells(2 + m, 2)
      set x3
     sheet2.Cells(1, 3) = sheet1.Cells(2 + m, 3)

      get z
     sheet1.Cells(2 + m, 4) = sheet2.Cells(1, 4)

next m
End Sub

友情链接