=IF(D2>3,3,(IF(D2>2,2,(IF(D2>1,1)))))

エクセル上のGPAの連続値(D2以下のセル)を1,2,3のカテゴリに変換する関数 
もし4を設定したい場合は
(if(d2>4,4をイコールの後に加え最後に )

Monte Carlo感度分析 import numpy as np from scipy.optimize import minimize def sensitivity_analysis(base_scores, impact_factors, target_improvements): """ 感度分析による最適化 base_scores: 現在のポリシースコア impact_factors: 影響度係数行列 target_improvements: 目標改善値 """ def predict_outcomes(policy_changes): predictions = {} # 各アウトカムに対する予測計算 for outcome in ['examPass', 'dropout', 'profJob', 'satisfaction']: base_value = get_baseline_outcome(outcome) predicted_change = 0 for policy, change in policy_changes.items(): if policy in impact_factors and outcome in impact_factors[policy]: # 非線形効果を考慮(飽和効果) impact = impact_factors[policy][outcome] saturation_factor = 1 / (1 + np.exp(-0.1 * change)) # シグモイド関数 predicted_change += change * impact * 0.01 * saturation_factor predictions[outcome] = base_value + predicted_change return predictions def optimization_objective(policy_vector): # ポリシーベクトルを辞書に変換 policy_changes = { 'consistency': policy_vector[0] - base_scores['consistency'], 'specificity': policy_vector[1] - base_scores['specificity'], 'hierarchy': policy_vector[2] - base_scores['hierarchy'], 'contradiction': policy_vector[3] - base_scores['contradiction'] } predictions = predict_outcomes(policy_changes) # 目標との差異を最小化(重み付き) objective = 0 weights = {'examPass': 0.4, 'dropout': 0.3, 'profJob': 0.2, 'satisfaction': 0.1} for outcome, target in target_improvements.items(): if outcome in predictions: normalized_diff = (predictions[outcome] - target) / target objective += weights.get(outcome, 0.1) * normalized_diff ** 2 return objective # 制約条件(現実的な変更範囲) constraints = [ {'type': 'ineq', 'fun': lambda x: x[0] - max(0, base_scores['consistency'] - 10)}, {'type': 'ineq', 'fun': lambda x: min(100, base_scores['consistency'] + 20) - x[0]}, {'type': 'ineq', 'fun': lambda x: x[1] - max(0, base_scores['specificity'] - 10)}, {'type': 'ineq', 'fun': lambda x: min(100, base_scores['specificity'] + 20) - x[1]}, {'type': 'ineq', 'fun': lambda x: x[2] - max(0, base_scores['hierarchy'] - 10)}, {'type': 'ineq', 'fun': lambda x: min(100, base_scores['hierarchy'] + 20) - x[2]}, {'type': 'ineq', 'fun': lambda x: x[3] - max(0, base_scores['contradiction'] - 15)}, {'type': 'ineq', 'fun': lambda x: min(50, base_scores['contradiction'] + 5) - x[3]} ] # 最適化実行(複数回実行して最良解選択) best_result = None best_objective = float('inf') for _ in range(10): # 複数の初期値で実行 initial_noise = np.random.normal(0, 2, 4) initial_guess = [ base_scores['consistency'] + initial_noise[0], base_scores['specificity'] + initial_noise[1], base_scores['hierarchy'] + initial_noise[2], base_scores['contradiction'] + initial_noise[3] ] result = minimize(optimization_objective, initial_guess, method='SLSQP', constraints=constraints) if result.success and result.fun < best_objective: best_result = result best_objective = result.fun if best_result: optimal_scores = { 'consistency': best_result.x[0], 'specificity': best_result.x[1], 'hierarchy': best_result.x[2], 'contradiction': best_result.x[3] } optimal_changes = {k: optimal_scores[k] - base_scores[k] for k in optimal_scores} predicted_outcomes = predict_outcomes(optimal_changes) return optimal_scores, predicted_outcomes, best_result.fun else: return None, None, None