在Python中实现Excel的单变量求解功能

标签：Python与Excel,pandas

Excel提供了一个很好的功能——单变量求解，当给出最终结果时，它允许反向求解输入值。它是一个方便的工具，因此今天我们将学习如何在Python中实现单变量求解。

在Excel中如何进行单变量求解

如果你不熟悉Excel的单变量求解功能，它就在“模拟分析”中，如下图1所示。

图1

让我们看看这个简单的例子：有一个方程z=x^2+y^(1/3)，如果我们让x=3，y=10，把它们代入方程，得到z=11.15443。

图2

现在让我们让它更有趣，假设你想要最终结果z=90，保持x为常数3，y应该是什么值？我们可以使用Excel的单变量求解来反向求解y的值。转到功能区“数据”选项卡“预测”组中的“模拟分析->单变量求解”。通过更改y值，设置z=90。如你所见，几秒钟后，Excel能够反求出y的一个非常接近的数字，即531423.3。插入它，我们得到z=89.9991，这非常接近我们期望的结果90。

图3

在Excel单变量求解中发生了什么

如果在求解过程中注意“单变量求解”窗口，你将看到这一行“在迭代xxx中…”，本质上，Excel在单变量求解过程中执行以下任务：

1.插入y值的随机猜测值

2.在给定x=3和上述y值的情况下计算z

3.测量结果z与预期结果90的差距

4.如果第3步表明结果仍然远离所需值，则返回步骤1，调整y值

5.重复第1–4步，直到达到所需的z或满足阈值

那些擅长数学的读者可能会建议你可以从方程中解出y。考虑到这是个简单的方程式，这确实可以，但在很多情况下，解方程式是不可能的，这就是单变量求解可以带来价值的地方。这里的例子可能过于简单，但希望你能理解。

Python中的单变量求解

一旦知道了逻辑，我们就可以用Python实现它了。让我们先建立方程。

def z(x,y):
    return x**2 + y**(1/3)

图4

二分查找算法

接下来，我们需要一个函数来执行反向求解。有很多算法可以反求输入值，这里研究的一种叫做二分查找。其思路如下：

1.我们知道我们试图解决的值可能在某个范围内，但我们不知道该值到底是什么。

2.我们取该范围的中点，代入方程，看看离目标值有多远。根据差异，我们可以确定该值是在范围的下半部分还是上半部分。

3.然后我们取新范围的中点并再次测试。根据需要多次重复步骤2-3，直到差异达到我们的误差范围。

二分查找示例

让我们看一个简单的例子来了解上述算法。假设我们想猜一个0-100之间的数字，答案是85，但假设你不知道答案是85。

让我们开始：

• 原始范围为0-100，初始猜测为50。太低（与答案85相比）->新的可能范围是（50,100）
• 第二次猜测=（50+100）/2=75。过低->新的可能范围为（75,100）
• 第三猜测=（75+100）/2=87.5。过高->新范围为（75,87.5）
• 第四猜测=（75+87.5）/2=81.25。过低->新范围为（81.25,87.5）
• 第五猜测=（81.25+87.5）/2=84.375。过低->新范围为（84.375,87.5）
• 第六猜测=（84.375+87.5）/2=85.9375。过高->新范围为（84.375,85.9375）
• 第七猜测=（84.375+85.9375）/2=85.15625。在这一点上，我们离真实数字只有0.15625，因此可以决定停止。

对函数z进行二分查找

我们的问题比猜测0-100之间的随机数稍微困难一些，但我们仍然可以使用二分查找。让我们设置一个从0到1000000的可能范围，提示：1000000^(1/3)=100，因此目标数字90意味着y值必须在0到1000000之间。我们还定义了一个阈值，它是真实数字和最佳猜测之间差异的容差。下面是二分查找代码，我还添加了一些print语句来帮助可视化当前猜测的范围。

def goal_seek(target, _threshold):
    threshold = _threshold
    lower = 0
    upper = 1000000
    solve = (lower + upper)/2
    while abs(threshold) >=_threshold:
        print(f'阈值是: {threshold}')
        print(f'范围是: {lower} ------ {solve} ------ {upper}')
        if threshold < 0:
            upper=solve
            solve = (lower + upper)/2
        elif threshold > 0:
            lower = solve
            solve = (lower +upper)/2
        threshold = target -z(3,solve)
    print(f'最终结果: 阈值: {threshold}......答案:{solve}......{z(3,solve)}')
    return solve

让我们用计时器来测试：

import time
start = time.time()
goal_seek(90, 0.00001)
print(f'用时 {time.time() - start} 秒')

结果为：

图5