如何在Jupyter Notebook中实现类似Excel Solver的求解功能？

你不需要手动写循环，直接用scipy.optimize模块的最小化函数就能实现批量调整Regressed Vc、最小化误差的需求，下面分两种场景给出代码：

场景1：整体优化所有行的Regressed Vc（最小化总误差平方和） #

这种方式会同时调整所有40行的Regressed Vc，让所有行的误差平方和最小，适合需要全局最优的场景。

步骤1：导入依赖模块 #

import numpy as np
import pandas as pd
from scipy.optimize import minimize

步骤2：定义误差计算函数 #

def total_error(regressed_vc, df, A0, A1, A2, A3, A4):
    temp_df = df.copy()
    temp_df['Regressed Vc'] = regressed_vc
    
    # 重新计算相关列（简化原代码中冗余的muy_0计算）
    temp_df['Vpc'] = temp_df['Regressed Vc'] * temp_df['zi']
    temp_df['rho_r'] = (temp_df['Density'] * 62.427) * temp_df['Vpc'] / temp_df['Mwi']
    temp_df['muy_i'] = (34 * 10**-5) * (1 / temp_df['zeta']) * (temp_df['Tri']**0.94)
    temp_df['zeta_T'] = 5.4402 * (temp_df['Tpc']**(1/6)) / ((temp_df['Mwi']**0.5) * (temp_df['ppc']**(2/3)))
    temp_df['RHS'] = A0 + A1*temp_df['rho_r'] + A2*(temp_df['rho_r']**2) + A3*(temp_df['rho_r']**3) + A4*(temp_df['rho_r']**4)
    temp_df['muy_0'] = temp_df['muy_i']  # 原代码分子分母抵消，直接等于muy_i
    temp_df['muy_lbc'] = temp_df['muy_0'] + ((1 / temp_df['zeta_T']) * (temp_df['RHS']**4)) - 0.0001
    
    # 返回总误差平方和
    return ((temp_df['muy_s'] - temp_df['muy_lbc'])**2).sum()

步骤3：执行优化并更新数据 #

# 替换为你的实际常量值
A0 = 0
A1 = 0
A2 = 0
A3 = 0
A4 = 0

# 初始值用现有Regressed Vc
initial_guess = df['Regressed Vc'].values

# 调用最小化函数（如果Regressed Vc有取值范围，可添加bounds参数）
result = minimize(total_error, initial_guess, args=(df, A0, A1, A2, A3, A4), method='L-BFGS-B')

# 更新DataFrame并验证结果
if result.success:
    df['Regressed Vc'] = result.x
    # 重新计算所有列
    df['Vpc'] = df['Regressed Vc'] * df['zi']
    df['rho_r'] = (df['Density'] * 62.427) * df['Vpc'] / df['Mwi']
    df['muy_i'] = (34 * 10**-5) * (1 / df['zeta']) * (df['Tri']**0.94)
    df['zeta_T'] = 5.4402 * (df['Tpc']**(1/6)) / ((df['Mwi']**0.5) * (df['ppc']**(2/3)))
    df['RHS'] = A0 + A1*df['rho_r'] + A2*(df['rho_r']**2) + A3*(df['rho_r']**3) + A4*(df['rho_r']**4)
    df['muy_0'] = df['muy_i']
    df['muy_lbc'] = df['muy_0'] + ((1 / df['zeta_T']) * (df['RHS']**4)) - 0.0001
    df['error'] = (df['muy_s'] - df['muy_lbc'])**2
    
    print(f"优化完成，总误差平方和: {result.fun:.6f}")
else:
    print(f"优化失败: {result.message}")

场景2：每行独立优化Regressed Vc（让每行误差最小） #

如果需要每行单独调整Regressed Vc，让该行的error尽可能接近0，用以下循环实现：

# 替换为你的实际常量值
A0 = 0
A1 = 0
A2 = 0
A3 = 0
A4 = 0

for idx, row in df.iterrows():
    def row_single_error(vc, row_data):
        temp_row = row_data.copy()
        temp_row['Regressed Vc'] = vc
        
        # 计算该行的muy_lbc
        temp_row['Vpc'] = temp_row['Regressed Vc'] * temp_row['zi']
        temp_row['rho_r'] = (temp_row['Density'] * 62.427) * temp_row['Vpc'] / temp_row['Mwi']
        temp_row['muy_i'] = (34 * 10**-5) * (1 / temp_row['zeta']) * (temp_row['Tri']**0.94)
        temp_row['zeta_T'] = 5.4402 * (temp_row['Tpc']**(1/6)) / ((temp_row['Mwi']**0.5) * (temp_row['ppc']**(2/3)))
        temp_row['RHS'] = A0 + A1*temp_row['rho_r'] + A2*(temp_row['rho_r']**2) + A3*(temp_row['rho_r']**3) + A4*(temp_row['rho_r']**4)
        temp_row['muy_0'] = temp_row['muy_i']
        temp_row['muy_lbc'] = temp_row['muy_0'] + ((1 / temp_row['zeta_T']) * (temp_row['RHS']**4)) - 0.0001
        
        return (temp_row['muy_s'] - temp_row['muy_lbc'])**2
    
    # 执行单行长优化
    res = minimize(row_single_error, row['Regressed Vc'], args=(row,), method='L-BFGS-B')
    if res.success:
        # 更新该行数据
        df.loc[idx, 'Regressed Vc'] = res.x[0]
        df.loc[idx, 'Vpc'] = res.x[0] * row['zi']
        df.loc[idx, 'rho_r'] = (row['Density'] * 62.427) * df.loc[idx, 'Vpc'] / row['Mwi']
        df.loc[idx, 'muy_i'] = (34 * 10**-5) * (1 / row['zeta']) * (row['Tri']**0.94)
        df.loc[idx, 'zeta_T'] = 5.4402 * (row['Tpc']**(1/6)) / ((row['Mwi']**0.5) * (row['ppc']**(2/3)))
        df.loc[idx, 'RHS'] = A0 + A1*df.loc[idx, 'rho_r'] + A2*(df.loc[idx, 'rho_r']**2) + A3*(df.loc[idx, 'rho_r']**3) + A4*(df.loc[idx, 'rho_r']**4)
        df.loc[idx, 'muy_0'] = df.loc[idx, 'muy_i']
        df.loc[idx, 'muy_lbc'] = df.loc[idx, 'muy_0'] + ((1 / df.loc[idx, 'zeta_T']) * (df.loc[idx, 'RHS']**4)) - 0.0001
        df.loc[idx, 'error'] = (row['muy_s'] - df.loc[idx, 'muy_lbc'])**2
    else:
        print(f"第{idx}行优化失败: {res.message}")

print("所有行优化完成")

注意事项 #

• 必须替换代码中的A0-A4为你实际使用的常量值。
• 如果Regressed Vc有取值范围限制，可在minimize函数中添加bounds参数，比如整体优化时用bounds=[(min_val, max_val)]*len(df)，单行长优化时用bounds=[(min_val, max_val)]。
• 原代码中muy_0的计算存在冗余，分子分母的zi和Mwi**0.5完全抵消，直接等于muy_i，已在代码中简化。

内容的提问来源于stack exchange，提问作者elvira