如何将GoalSeek Goal:=527.585不设定为指定数值

wwallace · 发表于 2014-9-5 16:34

为了求一个公式的T值，采用了单变量求值和录制下面的宏我想把指定值变成某一单元格值，随着单元格的值得变化自动求得t值求各位大神指点！

Sub myGoalSeek()

Range("B3").GoalSeek Goal:=527.585, ChangingCell:=Range("C16")

End Sub

wwallace · 发表于 2014-9-5 17:12

╮(╯▽╰)╭！都没有能解决的吗？
、

灰袍法师 · 发表于 2014-9-5 17:35

Range("B3").GoalSeek Goal:=Range("B18").Value, ChangingCell:=Range("C16")
===============
这样就可以把目标值设置为 B18 单元格的数值

wwallace · 发表于 2014-9-5 20:06

灰袍法师发表于 2014-9-5 17:35
Range("B3").GoalSeek Goal:=Range("B18").Value, ChangingCell:=Range("C16")
===============
这样就可 ...

高手！感谢感谢！

香川群子 · 发表于 2014-9-5 20:32

这种小事情……也可以自己写代码，进行逼近计算求解。

香川群子 · 发表于 2014-9-5 20:33

灰袍法师发表于 2014-9-5 17:35
Range("B3").GoalSeek Goal:=Range("B18").Value, ChangingCell:=Range("C16")
===============
这样就可 ...

呵呵，我自己写了【单变量求解】的代码：

Function fuc(x, y, z, k, l, t)
a = 2E-22 * t ^ 6 - 3E-18 * t ^ 5 + 0.00000000000002 * t ^ 4 - 0.00000000005 * t ^ 3 + 0.00000006 * t ^ 2 - 0.000005 * t + 0.311
b = -1E-20 * t ^ 6 + 7E-17 * t ^ 5 - 0.0000000000002 * t ^ 4 + 0.0000000003 * t ^ 3 - 0.0000003 * t ^ 2 + 0.0003 * t + 0.3844
c = 3E-20 * t ^ 6 - 2E-16 * t ^ 5 + 0.0000000000004 * t ^ 4 - 0.0000000004 * t ^ 3 + 0.0000002 * t ^ 2 - 0.000009 * t + 0.3584
d = 2E-20 * t ^ 6 - 9E-17 * t ^ 5 + 0.0000000000002 * t ^ 4 - 0.0000000002 * t ^ 3 + 0.0000001 * t ^ 2 + 0.00002 * t + 0.312
e = -5E-20 * t ^ 6 + 1E-16 * t ^ 5 - 0.00000000000005 * t ^ 4 - 0.00000000007 * t ^ 3 - 0.00000003 * t ^ 2 + 0.0002 * t + 0.414
fuc = (a * x + b * y + c * z + d * k + e * l) * t * 4.182
End Function
Sub myGoalSeek()
x = [c6]: y = [c8]: z = [c10]: k = [c12]: l = [c14]: s = [c16]
t0 = 0
For m = 0 To 10
t = t0 + 10 ^ m
r = fuc(x, y, z, k, l, t)
If r > s Then Exit For
Next
t = 10 ^ (m - 1) + 10 ^ (m - 15)
For i = 1 To m
For n = 1 To 9
Mid(t, i, 1) = n
r = fuc(x, y, z, k, l, t)
If r > s Then Exit For
Next
Mid(t, i, 1) = n - 1
Next
For i = m + 2 To 16
For n = 1 To 9
Mid(t, i, 1) = n
r = fuc(x, y, z, k, l, t)
If r > s Then Exit For
Next
Mid(t, i, 1) = n - 1
Next
[c18] = t
MsgBox t
End Sub

复制代码

香川群子 · 发表于 2014-9-5 20:40

本帖最后由香川群子于 2014-9-5 20:43 编辑

计算过程如下：
t    计算值r             和目标值s的差
1  1.37985080208412 -526.205149197916
10  13.8137389285253 -513.771261071475
100  139.749520439354 -387.835479560646
1000  1558.66000488  1031.07500488
100.000000000001 139.749520439355 -387.835479560645
200.000000000001 283.326726679227 -244.258273320773
300.000000000001 430.889093049489 -96.6959069505112
400.000000000001 582.387687931105  54.8026879311049

310.000000000001 445.863822567237 -81.7211774327631
320.000000000001 460.877875170209 -66.7071248297908
330.000000000001 475.931114117023 -51.6538858829775
340.000000000001 491.02339338506 -36.5616066149398
350.000000000001 506.154558183749 -21.4304418162515
360.000000000001 521.324445417613 -6.2605545823867
370.000000000001 536.532884099906  8.94788409990633

361.000000000001 522.843557358261 -4.74144264173935
362.000000000001 524.363054644386 -3.22194535561391
363.000000000001 525.882937100031 -1.70206289996906
364.000000000001 527.403204548491 -.181795451508606
365.000000000001 528.923856812322  1.33885681232186

364.100000000001 527.555252461117 -2.97475388829298E-02
364.200000000001 527.707304221719  .122304221719105

364.110000000001 527.570457464021 -1.45425359787623E-02
364.120000000001 527.585662505405  6.62505404989133E-04

364.111000000001 527.571977966428 -1.30220335719287E-02
364.112000000001 527.57349846922 -1.15015307802651E-02
364.113000000001 527.575018972396 -9.9810276038852E-03
364.114000000001 527.576539475957 -8.46052404278907E-03
364.115000000001 527.578059979903 -6.9400200969767E-03
364.116000000001 527.579580484234 -5.41951576599331E-03
364.117000000001 527.58110098895 -3.89901105052104E-03
364.118000000001 527.58262149405 -2.37850595010514E-03
364.119000000001 527.584141999535 -8.58000464972974E-04

364.119100000001 527.584294050105 -7.05949895177582E-04
364.119200000001 527.584446100678 -5.53899321630524E-04
364.119300000001 527.584598151256 -4.01848744331801E-04
364.119400000001 527.584750201837 -2.49798163054038E-04
364.119500000001 527.584902252422 -9.77475780246095E-05
364.119600000001 527.585054303011  5.43030108701714E-05

364.119510000001 527.584917457481 -8.2542519294293E-05
364.119520000001 527.584932662539 -6.73374605639765E-05
364.119530000001 527.584947867598 -5.21324016062863E-05
364.119540000001 527.584963072657 -3.69273429896566E-05
364.119550000001 527.584978277716 -2.17222840319664E-05
364.119560000001 527.584993482775 -6.51722507427621E-06
364.119570000001 527.585008687834  8.68783365604031E-06

364.119561000001 527.584995003281 -4.99671932630008E-06
364.119562000001 527.584996523787 -3.47621335095027E-06
364.119563000001 527.584998044292 -1.95570760297414E-06
364.119564000001 527.584999564799 -4.352015139375E-07
364.119565000001 527.585001085304  1.08530434772547E-06

364.119564100001 527.584999716849 -2.83151052826724E-07
364.119564200001 527.584999868899 -1.31100591715949E-07
364.119564300001 527.58500002095  2.09502104553394E-08

364.119564210001 527.584999884105 -1.15895318231196E-07
364.119564220001 527.58499989931 -1.00690385806956E-07
364.119564230001 527.584999914515 -8.54851123222033E-08
364.119564240001 527.58499992972 -7.02800662111258E-08
364.119564250001 527.584999944925 -5.50750201000483E-08
364.119564260001 527.58499996013 -3.98699739889707E-08
364.119564270001 527.584999975335 -2.46650415647309E-08
364.119564280001 527.58499999054 -9.46010914049111E-09
364.119564290001 527.585000005745  5.74516434426187E-09

364.119564281001 527.58499999206 -7.9395476859645E-09
364.119564282001 527.584999993581 -6.41887254460016E-09
364.119564283001 527.584999995102 -4.89853846374899E-09
364.119564284001 527.584999996622 -3.37786332238466E-09
364.119564285001 527.584999998143 -1.85741555469576E-09
364.119564286001 527.584999999663 -3.36854100169148E-10
364.119564287001 527.585000001184  1.18370735435747E-09

364.119564286101 527.584999999815 -1.84854798135348E-10
364.119564286201 527.584999999967 -3.27418092638254E-11
364.119564286301 527.585000000119  1.19143805932254E-10

364.119564286211 527.584999999983 -1.75077730091289E-11
364.119564286221 527.584999999998 -2.38742359215394E-12
364.119564286231 527.585000000013  1.29602995002642E-11

364.119564286221 527.584999999998 -2.38742359215394E-12
364.119564286222 527.584999999999 -9.09494701772928E-13
364.119564286223 527.585000000001  6.82121026329696E-13

因此最后结果是：t=364.119564286222

wwallace · 发表于 2014-9-5 20:57

本帖最后由 wwallace 于 2014-9-5 20:58 编辑

香川群子发表于 2014-9-5 20:40
计算过程如下：
t 计算值r 和目标值s的差
1 1.37985080208412 -526.205149197916

大师！膜拜！感谢！只能评这么多分了！最大能力感谢！

wwallace · 发表于 2014-9-5 22:20

香川群子发表于 2014-9-5 20:32
这种小事情……也可以自己写代码，进行逼近计算求解。

为什么数据改变后不能自动加载宏？？？

求t.zip (16.68 KB, 下载次数: 5)

香川群子 · 发表于 2014-9-6 08:23

1. 跨工作表使用

2. "$a$1" <> "$A$1"

		自动登录	找回密码
密码			免费注册

[已解决] 如何将GoalSeek Goal:=527.585不设定为指定数值

评分

评分

评分

评分