Excelで売上目標や予算配分、人員配置などを考えるとき、答えを一つずつ手入力で試す作業には時間がかかります。
そのような場面で役立つ機能が、条件を満たしながら最適な数値を探索するソルバーです。
ソルバーは目標値の調整にも似ていますが、複数の変更セルと制約条件を同時に扱える点が大きな特徴です。
ソルバーで扱う基本要素
・目的セル 最大化、最小化、または指定値にしたい計算結果
・変数セル ソルバーに数値を変更させるセル
・制約条件 予算上限、数量の下限、整数指定などの守るべき条件
この記事では、アドインの有効化から制約条件の設定、アルゴリズムの選び方まで、実務で迷いやすい部分を順に解説します。
サンプルは1行目を見出し行とし、2行目以降にデータを入力する前提です。
エクセルのソルバーによる最適値の算出
それではまず、エクセルのソルバーで最適値を算出する基本的な考え方について解説していきます。
| 商品 | 単価 | 原価 | 販売数 | 利益 |
|---|---|---|---|---|
| 商品A | 500 | 280 | 20 | 4400 |
| 商品B | 800 | 510 | 15 | 4350 |
| 合計 | 35 | 8750 |
ソルバーの役割
ソルバーは、指定した条件の範囲内で、ある計算結果を最も大きく、最も小さく、または特定の値に近づけるExcelアドインです。
たとえば商品Aと商品Bの販売数を変数とし、利益合計を最大化するように計算させることができます。
人が候補を何十通りも試す代わりに、Excelが条件に合う組み合わせを探索するため、複雑な配分問題にも対応しやすくなります。
目的セルには、通常は合計利益、合計コスト、総作業時間のように、数式で計算された結果セルを指定します。
変数セルには販売数、発注量、担当者数、広告費など、結論に応じて変えてよい値を指定します。
利益の基本式
利益 ＝ （単価 － 原価）× 販売数
合計利益 ＝ 各商品の利益を合計した値
目的セルだけを直接書き換えるのではなく、数式の参照先である変数セルをソルバーが動かす仕組みです。
目標値の調整との違い
Excelの目標値の調整は、結果を指定値にするために、原則として一つの入力セルを変更する機能です。
一方のソルバーは、複数セルを同時に変更でき、さらに上限や下限、整数といった条件を追加できます。
売上合計を100万円にするために広告費だけを求めるなら目標値の調整でも足ります。
しかし、複数商品の生産量を変えながら、原材料費を予算内に収め、利益を最大化するならソルバーの領域です。
最適化とは、単に計算結果を出すことではなく、条件の中で最も望ましい解を選ぶことを意味します。
そのため、先に何を最適にしたいのかを明確にする必要があります。
ソルバーの有効化
ソルバーは初期状態ではリボンに表示されていないことがあります。
Excelのファイルタブからオプションを開き、アドインを選択します。
画面下部の管理でExcelアドインを選び、設定へ進みます。
一覧にあるソルバーアドインへチェックを入れ、OKを選択しましょう。
有効化が終わると、データタブの分析グループにソルバーが表示されます。
表示されない場合は、Excelをいったん閉じて開き直すと反映されることがあります。
【操作のポイント】ソルバーはブックごとの機能ではなくExcelのアドインとして有効化します。共有するファイルを開く人の環境でも、必要に応じてアドインを有効にしましょう。
目的セルと変数セルの設定
続いては、最適化の土台になる目的セルと変数セルの設定を確認していきます。
| セル | 項目 | 入力内容 | 役割 |
|---|---|---|---|
| D2 | 商品A販売数 | 20 | 変数セル |
| D3 | 商品B販売数 | 15 | 変数セル |
| E4 | 合計利益 | =SUM(E2:E3) | 目的セル |
目的セルの数式
目的セルには、最適化したい結果が計算される数式を入れます。
サンプルではE2セルに「=(B2-C2)*D2」、E3セルに「=(B3-C3)*D3」を入力します。
E4セルには「=SUM(E2:E3)」を入力し、商品別利益の合計を求めます。
サンプルのセル参照
E2 ＝ （B2 － C2）× D2
E3 ＝ （B3 － C3）× D3
E4 ＝ SUM（E2からE3）
この場合、ソルバーで最大化する目的セルはE4です。
目的セルは必ず数式を含むセルにすることが重要であり、固定値だけが入力されたセルを指定しても計算の意味がありません。
変数セルの範囲
変数セルには、ソルバーが自由に変更してよい入力範囲を指定します。
今回ならD2からD3が変数セルです。
販売数のように複数の数値を調整したい場合は、連続した範囲として指定すると管理しやすくなります。
変数セルに数式が入っていると、ソルバーが値を書き換えられず、期待した結果にならないことがあります。
入力値として扱うセルと、計算式を置くセルの役割を分ける設計が基本です。
また、初期値は必ずしも最適解である必要はありませんが、極端に不自然な数値から始めるより、現実的な仮値を入れるほうが確認しやすいでしょう。
最大化と最小化と目標値
ソルバーのパラメーター設定では、目的セルに対して最大値、最小値、または指定値を選びます。
利益や売上、稼働率を高めたいときは最大値を選択します。
コスト、輸送距離、不良率、残業時間を抑えたいときは最小値が適しています。
目標値は、合計金額を予算額に合わせる、必要人数を一定数にするなど、到達したい数値が明確なケースで使います。
最大化と最小化の選択は、変数セルではなく目的セルの評価方法を決める設定です。
目的が曖昧なまま設定を始めると、数式は正しくても業務上使えない結果になりかねません。
【操作のポイント】目的セル、変数セル、制約条件を紙やメモに分けてから設定すると、セル参照の指定ミスを減らせます。
制約条件による最適化の調整
続いては、現実的な答えを出すために欠かせない制約条件の設定を確認していきます。
| 条件名 | セルまたは式 | 条件 | 意味 |
|---|---|---|---|
| 総作業時間 | F4 | 500以下 | 上限時間内 |
| 商品A販売数 | D2 | 0以上 | マイナス禁止 |
| 販売数範囲 | D2:D3 | 整数 | 端数販売禁止 |
不等号による上限と下限
制約条件は、最適解として許容する範囲を定義する設定です。
たとえば総作業時間が500時間を超えてはいけないなら、総作業時間の計算結果セルに500以下という制約を付けます。
変数セルである販売数には、0以上という制約を設定します。
これにより、利益を増やすためにソルバーがマイナスの販売数を作るような不自然な計算を防げます。
制約条件の考え方
作業時間の合計 ≦ 利用可能な作業時間
原材料費の合計 ≦ 予算額
販売数 ≧ 0
制約条件は多いほどよいわけではなく、業務上必ず守る条件を正確に登録することが大切です。
不要な制約を加えると、探索できる範囲が狭くなり、解が見つからない場合があります。
整数と二値の指定
販売個数、人員数、車両台数のように小数では扱えない項目には、整数の制約を追加します。
整数制約を付けない場合、ソルバーは利益を最大にするために販売数を23.47個のような小数で返すことがあります。
実務で1個単位の判断が必要なら、変数セルの範囲に対して整数を選択しましょう。
採用するかしないか、倉庫を使うか使わないかのような二択には二値制約が使えます。
二値は0または1だけを許可する条件です。
整数制約や二値制約は現実に近い解を作る一方、計算量を増やす要因にもなります。
対象セルを必要最小限に絞ると、処理時間の増加を抑えやすくなります。
制約条件の追加手順
ソルバーのパラメーター画面で追加を選択すると、制約条件の設定画面が開きます。
セル参照には制約をかけるセル、関係には以下や以上、等しい、整数などを指定します。
制約条件には数値、セル参照、または別の計算結果セルを入力できます。
予算額が別セルに入力されているなら、固定の数値ではなく予算セルを参照すると、予算変更にも対応しやすくなります。
設定後は変更を選び、一覧に表示された条件が意図通りかを確認してください。
特に以下と以上を逆にすると結果が大きく変わるため、登録後の見直しが欠かせません。
【操作のポイント】制約条件は計算式のセルに設定する場合と、入力セルに設定する場合があります。どの値を制限したいのかを確認してセルを選びましょう。
ソルバーの実行画面と結果の確認
続いては、ソルバーを実行し、最適化された値をワークシートへ反映する流れを確認していきます。
| 項目 | 指定例 |
|---|---|
| 目的セル | $E$4 |
| 目標 | 最大値 |
| 変数セル | $D$2:$D$3 |
| 解決方法 | Simplex LP |
パラメーター画面の入力
データタブのソルバーを選択すると、ソルバーのパラメーター画面が表示されます。
目的セルの設定にはE4を指定し、最大を選びます。
変数セルの変更にはD2からD3を指定します。
その後、追加から作業時間、予算、非負、整数などの制約条件を登録しましょう。
入力内容に間違いがなければ解決を選択します。
ソルバー画面へ入力するセル参照は、ワークシート上をクリックして指定すると間違いを減らせます。
解決結果の反映
探索が終わると、ソルバーの結果画面が表示されます。
解が見つかった場合は、ソルバーの解を保持するを選ぶことで、計算された変数セルの値をシートに残せます。
元の数値へ戻したい場合は、元の値に戻すを選択します。
検討段階では、結果を保持する前にスクリーンショットや別シートへの記録を残すと比較しやすくなります。
保存前に元の値へ戻す選択をした場合、再計算時の初期値も変わらない点を覚えておきましょう。
結果を保持すると、変数セルだけでなく、それらを参照する数式セルの値も自動で更新されます。
実行後の妥当性確認
最適解が表示されても、そのまま採用する前に業務上の妥当性を確認する必要があります。
たとえば利益が最大でも、在庫の保管場所、取引先との最低発注数、担当者の技能といった未設定の条件を見落としているかもしれません。
制約条件に入れていない事情は、ソルバーには考慮できません。
結果の販売数を見て、端数、異常に大きい値、実施不可能な配分がないかを確認しましょう。
確認したい項目
・変数セルが現実的な数値になっているか
・すべての制約条件を満たしているか
・目的セルの数式と参照範囲に漏れがないか
最適化の品質は、数式と制約条件の品質に左右されます。
【操作のポイント】解決後は必ず変数セルと制約セルを見直します。数値が出たことと、採用可能な計画であることは別の判断です。
アルゴリズムの種類と選び方
続いては、ソルバーで選択できるアルゴリズムと、それぞれに合う問題の特徴を確認していきます。
| 解決方法 | 向いている計算 | 主な特徴 |
|---|---|---|
| Simplex LP | 線形の最大化と最小化 | 高速で基本的な選択肢 |
| GRG Nonlinear | 非線形の連続値 | 滑らかな数式に対応 |
| Evolutionary | 複雑な非線形や条件分岐 | 探索型で時間がかかる場合もある |
Simplex LPの対象
Simplex LPは、目的セルと制約条件が線形の式で表せる場合に使う代表的な解決方法です。
線形とは、変数が一次式で扱われ、変数同士を掛け合わせたり、累乗したりしない関係を指します。
商品ごとの利益を単価差額と販売数の積で計算し、総作業時間や予算を合計で制限するモデルは、一般に線形計画法として扱えます。
生産量、配送量、予算配分のような配分問題では、まずSimplex LPを検討するとよいでしょう。
線形モデルであれば処理が比較的速く、結果の説明もしやすい点が利点です。
GRG Nonlinearの対象
GRG Nonlinearは、変数に応じて割合や効率が変化するような非線形の数式に適した方法です。
たとえば広告費が増えるほど効果が鈍化する数式、複利計算、平方根や指数関数を含む計算では、線形の前提が崩れます。
非線形問題では、初期値によって異なる解へ到達することもあります。
そのため、初期値を変えて何度か実行し、結果の安定性を確かめる姿勢が大切です。
GRG Nonlinearは連続的に変化する値を扱いやすい一方、整数や複雑な分岐を多く含むモデルでは注意が必要です。
数式の形を把握し、線形ではない理由を説明できる状態で選びましょう。
Evolutionaryの対象
Evolutionaryは、遺伝的アルゴリズムの考え方を用い、候補を繰り返し探索していく解決方法です。
IF関数、LOOKUP関数、ROUND関数、二値条件などを多く含み、通常の手法で解きにくい問題で候補になります。
最適解を保証しない場合もありますが、複雑な組み合わせ問題に対して有効な結果を得られることがあります。
処理時間が長くなりやすいため、変数の数と制約条件を必要以上に増やさない工夫が必要です。
アルゴリズムの選択目安
線形の配分問題はSimplex LP
連続値を含む非線形計算はGRG Nonlinear
条件分岐や組み合わせが複雑な計算はEvolutionary
解決方法を変える前に、数式の参照ミスや制約条件の不足がないかを確認することも忘れないでください。
【操作のポイント】解決方法が分からないときは、線形の計算ならSimplex LPから試します。エラーや不自然な結果が続く場合に、数式の性質を見て別の方法を検討しましょう。
ソルバーで起こりやすいエラーと対処
続いては、ソルバーの実行時に起こりやすい問題と見直すべき項目を確認していきます。
| 表示や症状 | 主な原因 | 見直し箇所 |
|---|---|---|
| 解が見つからない | 条件が厳しすぎる | 制約条件 |
| 値が異常に大きい | 上限設定がない | 変数セルの制約 |
| 計算が終了しない | モデルが複雑 | 変数数とアルゴリズム |
実行可能解がない場合
ソルバーが解を見つけられないと表示した場合、制約条件をすべて同時に満たす組み合わせが存在しない可能性があります。
たとえば、販売数を各100以上にしながら、合計作業時間を100時間以下にする設定は、作業時間の単位によっては両立しません。
最初に整数制約を外して計算し、制約条件そのものが矛盾していないかを確認する方法もあります。
また、最低数量、予算、作業時間のうち、どの条件が最も厳しいかを一つずつ見直しましょう。
解がないという結果は故障ではなく、設定した条件では実現できないという重要な判断材料です。
条件を緩めるのか、目標を変更するのかは、業務上の優先順位に沿って決めます。
予期しない数値になる場合
利益最大化を選んだのに一つの商品だけが極端に多くなる場合、上限在庫や需要予測を制約条件に入れていないことがあります。
小数点以下の販売数が出る場合は、整数制約が不足している可能性があります。
また、目的セルのSUM関数に一部の商品行が含まれていない、原価セルの参照がずれているといった数式の問題も確認してください。
数式をコピーした後は、相対参照と絶対参照の違いでセル参照がずれることがあります。
利益率ではなく利益額を最大化しているのかなど、目的セルが本当に求めたい指標かを見直すことも必要です。
計算時間が長い場合
変数セルが多い、整数制約や二値制約が多い、複雑なIF関数を含む場合は、探索に時間がかかります。
まずは対象期間や商品数を絞り、小規模なモデルで期待する結果が出るかを確認しましょう。
不要な制約条件、使っていない変数セル、重複する計算式を削ると処理が軽くなる場合があります。
処理を軽くする考え方
・変数セルを必要な範囲に限定する
・数式を単純化できるか確認する
・小さなサンプルでモデルの正しさを検証する
計算を途中で止める前に、設定したアルゴリズムが問題の性質に合っているかを確認しましょう。
【操作のポイント】最初から完成形の大規模モデルを作らず、目的セルと変数セルを少数にした試作モデルで検証すると、原因を切り分けやすくなります。
まとめ エクセルのソルバーの使い方と最適化設定
エクセルのソルバーは、複数の変数セルを動かし、制約条件を守りながら目的セルを最大化、最小化、または指定値へ近づける機能です。
利益を最大化する販売計画、予算内の配分、作業時間を抑える人員計画など、手作業では比較しにくい問題で活用できます。
最初に目的セル、変数セル、制約条件の三つを整理することが、正確な最適化への近道です。
線形の配分問題ではSimplex LP、非線形の連続値ではGRG Nonlinear、複雑な条件分岐や組み合わせではEvolutionaryを検討します。
ただし、ソルバーが出す答えは、入力した数式と制約条件の範囲での最適解です。
現場の事情が条件に含まれていなければ、数値が最適でも実行できない計画になるかもしれません。
小さなサンプルからモデルを作り、結果と制約の両方を確認しながら、Excelのソルバーを実務の判断に役立てていきましょう。
