如何解决 R 中类似 Excel Solver 的问题?

发布于 01-16 02:14 字数 1245 浏览 2 评论 0原文

输入图片这里的描述

我很难实现以下优化问题,我知道Excel中提供的解决方案,现在我尝试在R中实现它。

数据是:

Kurt=4
Skew =-0.2

分别为k和t,如图所示。

在 R 中查找下欧米伽和上欧米伽很简单:

对于下欧米伽:

w1 = c(-Kurt-6,0,3,2,1)
f = polyroot(w1)
w11=max(Re(f));w11
[1] 1.206575
w2 = c(-4-(Skew)^2 ,0,3,1)
g = polyroot(w2);g
w22=max(Re(g));w22
wl = max(w11,w22) ;wl
1] 1.206575

对于上欧米伽:

wu = (-1+(2*(Kurt+2))^(0.5))^(0.5)
wu
[1] 1.569746

对于上欧米伽和下欧米伽 R 与 Excel 一致。

现在的问题是excel求解器发现omega值为1.56425,但我不知道如何在R中验证它。

我也尝试了上下的optimize函数:

m = function(x){ (4+2*( x^2 - ((Kurt +6) /(x^2+2*x+3) )   ))^(0.5) }
om = function(x){(x-1-m(x) )*(x+2+(m(x) /2))^2 - Skew}
optimize(om,interval = c(wl,wu), maximum = TRUE)

但我不知道如何找到照片中所述的上欧米茄和下欧米茄之间的结果 1.56425。它在 Excel 中制作了一个求解器,但我不知道在 R 中执行它。

请原谅我的照片但堆栈溢出没有乳胶来正确呈现它。

有什么帮助吗?我怎样才能在R中做到这一点?

Excel工作表如下图所示:

在此处输入图像描述

enter image description here

I have difficulty implement the following optimization problem which I know the solution as presented in excel and now I am trying to implement it in R.

The data are :

Kurt=4
Skew =-0.2

as k and t respectively as shown in the picture.

Finding the lower and upper omega is simple in R:

For the lower omega:

w1 = c(-Kurt-6,0,3,2,1)
f = polyroot(w1)
w11=max(Re(f));w11
[1] 1.206575
w2 = c(-4-(Skew)^2 ,0,3,1)
g = polyroot(w2);g
w22=max(Re(g));w22
wl = max(w11,w22) ;wl
1] 1.206575

For the upper omega:

wu = (-1+(2*(Kurt+2))^(0.5))^(0.5)
wu
[1] 1.569746

For the upper and lower omega R agrees with Excel.

Now the problem is that the excel Solver finds the omega value to be 1.56425 but I don't know how to verify it in R.

I tried as well the optimize function for the upper and lower:

m = function(x){ (4+2*( x^2 - ((Kurt +6) /(x^2+2*x+3) )   ))^(0.5) }
om = function(x){(x-1-m(x) )*(x+2+(m(x) /2))^2 - Skew}
optimize(om,interval = c(wl,wu), maximum = TRUE)

but I don't know how to to find the result 1.56425 between upper and lower omega as described in the photo.It makes a Solver in Excel but I don't know to perform it in R.

Excuse me for the photo but stack overflow does not have latex in order to present it properly.

Any help ? How can I do it in R?

The excel sheet is this the below picture:

enter image description here

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

妖妓2025-01-23 02:14:16

您需要提供更多信息/解释,以便人们可以真正复制您的示例。 (例如,什么是m,为什么它是x的函数?为什么在最后一步中进行优化,而不是寻找零?)。

无论如何,验证 Excel 解的一种方法是将其代入方程,看看方程是否成立。

此外,您还可以绘制函数图以了解优化可能失败的原因:

plot(x = seq(wl, wu, length.out = 50),
     y = om(seq(wl, wu, length.out = 50)))

更新
感谢您提供额外信息。 (您还想发布描述的来源。)看来您的函数中至少有两个编码错误。尝试:

m <- function(x){
    (4 + 2*(x^2 - ((Kurt + 6) /(x^2 + 2*x + 3))))^(0.5) - 2
}
om <- function(x){
    (x - 1 - m(x))*(x + 2 + (m(x) /2))^2 - Skew^2
}
uniroot(om, interval = c(wl,wu))
## $root
## [1] 1.56425
## 
## $f.root
## [1] 3.204998e-05
## 
## $iter
## [1] 3
## 
## $init.it
## [1] NA
## 
## $estim.prec
## [1] 6.103516e-05

请注意,我使用 uniroot 因为您正在寻找零(也称为根)。

You would need to give more information/explanations so that people can really replicate your example. (For instance, what is m, and why is it a function of x? Why do you optimize in the last step, instead of looking for a zero?).

In any case, one way to verify Excel's solution is to plug it into the equation and see if the equation holds.

Also, you can also plot your function in order to see why the optimization might fail:

plot(x = seq(wl, wu, length.out = 50),
     y = om(seq(wl, wu, length.out = 50)))

Update
Thanks for the additional information. (You also want to post the source of the description.) It seems you have at least two coding errors in your functions. Try:

m <- function(x){
    (4 + 2*(x^2 - ((Kurt + 6) /(x^2 + 2*x + 3))))^(0.5) - 2
}
om <- function(x){
    (x - 1 - m(x))*(x + 2 + (m(x) /2))^2 - Skew^2
}
uniroot(om, interval = c(wl,wu))
## $root
## [1] 1.56425
## 
## $f.root
## [1] 3.204998e-05
## 
## $iter
## [1] 3
## 
## $init.it
## [1] NA
## 
## $estim.prec
## [1] 6.103516e-05

Note that I use uniroot because you are looking for a zero (a.k.a. a root).

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文