Excelソルバーで混合線形計画問題を解く方法｜固定レシピと柔軟レシピの2つの事例で解説

本記事では、Excelのソルバー（Solver）を使って混合線形計画（Blending Linear Programming）問題を解く方法をご紹介します。線形計画は、ビジネスの分野でコストを最小化したり利益を最大化したりする際に活用される重要な手法です。また、日々の食習慣や支出を最適化するといった日常生活の場面でも応用できます。混合線形計画は、複数の原材料を混合して作られる製品について、利益やコストの最適化を求める特殊なタイプの線形計画です。ここでは、Excelを使って実際の2つの事例にこの混合線形計画を適用し、その仕組みをわかりやすく解説します。

混合線形計画とは？

化学産業や食品工場で働いている方なら、さまざまな材料を混ぜて製品を作る経験があるはずです。たとえば医薬品を製造する場合、必要な成分を揃え、決められた比率で混合しなければなりません。さらに、各成分をどれだけ購入すべきか、製品の品質がどうなるかといった点も考慮する必要があります。このような場面では、顧客へ納入する製品の混合量を最適化することが求められます。その課題を解決してくれるのが混合線形計画であり、原材料を生産にどのように活用すべきかを導き出すのに役立ちます。

Excelソルバーで混合線形計画問題を解く2つの事例

ここでは、混合線形計画の2つのパターンを取り上げます。ひとつは固定レシピ（Fixed Recipe）の場合の混合LP（線形計画）の解き方、もうひとつは柔軟レシピ（Flexible Recipe）の場合の解き方です。

固定レシピとは、材料の正確な比率や混合量があらかじめ決まっているケースを指します。一方、製品の生産において原材料の使用量を厳密に定めない場合は、柔軟レシピ向けの混合LPを適用します。

最初の例では、固定レシピ問題の解き方を紹介します。ここでは液体材料A・B・Cの3種類があり、それぞれA-B（60%：40%）とA-C（80%：20%）の配合で2つの新製品を作ります。その他のパラメータとしてLiterあたりの収益（Revenue/Liter）、Literあたりのコスト（Cost/Liter）、工場で利用可能な原材料の在庫数があります。このシナリオで利益の最大化を目指します。

柔軟レシピ問題では、異なる原材料を混合して通常品（Regular）・特上品（Exclusive）・超高級品（Super Quality）などの各種鋼材を製造します。原材料の入手可能量、トンあたりのコスト、品質評価値のデータを用意し、さらに各グレードの鋼材について必要な生産量、トンあたりの価格、最低評価基準を設定します。加えて線形化評価値（Linearized Rating）というパラメータも扱います。

1. 固定レシピ問題のための混合線形計画

前のセクションで説明した問題を、以下の手順で解いていきましょう。

手順：

• まず必要な数式を設定します。材料の使用量（Raw Usage）を把握するため、セルC12に次の数式を入力します。

=SUMPRODUCT(C5:C9,$G$5:$G$9)

ここではSUMPRODUCT関数を使用し、材料Aの使用量を算出しています。

• 次に、フィルハンドルを右方向へドラッグし、セルE12までオートフィルします。

• 続いて、セルI11に次の数式を入力して収益（Revenue）を計算します。

=SUMPRODUCT(G5:G9,H5:H9)*C16

この数式では、セルC16の値3.7854を掛けています。これは1ガロン＝3.7854リットルであるため、単位をリットルに換算するためです。

• 次に、セルI12に以下の数式を入力し、Enterキーを押します。

=SUMPRODUCT(C11:E11,C12:E12)*C16

この数式により、生産コストが算出されます。

• その後、次の数式で利益を計算します。

=I11-I12

• 続いて、最低生産要件を設定します。

• その後、データタブ >> ソルバーを選択します。データタブにソルバーアドインを追加する方法がわからない場合は、ファイル >> オプション >> アドイン >> Excelアドイン >> 設定 >> ソルバーアドインにチェックを入れ、OKをクリックしてください。
• ソルバーを開くには、データ >> ソルバーを選択します。

• 今回は利益の最大化が目的なので、目的セルには利益を格納したI13を指定します。
• 変数となるのは製品の混合率です。そこで「変数セル」欄に範囲G5:G9を追加します。
• 続いて、「制約条件の追加」ボタンをクリックして制約条件を登録します。

• 原材料の使用量は利用可能な在庫量を超えてはいけません。そこで最初の制約として、範囲C12:E12がC14:E14以下であることを指定します。
• 入力後、「追加」ボタンをクリックします。

• 同様に、生産量が最低必要生産量以上になるという制約も追加します。
• 次に、「OK」をクリックします。

• 続いて、「制約されていない変数を非負にする」にチェックを入れます。
• 解決方法として「Simplex LP」を選択します。
• 最後に「解決」ボタンをクリックします。

• すると確認メッセージが表示されるので、「OK」をクリックします。

• これで、最大の利益を得るために各原材料をどれだけ使用すべきかの値が表示されます。
• 同時に、収益、生産コスト、利益の結果も確認できます。

以上の手順で、Excelソルバーを使って固定レシピにおける混合線形計画問題を解くことができます。

2. Excelソルバーによる柔軟レシピの混合線形計画問題

このセクションでは、柔軟レシピの場合の混合線形計画問題の解き方を紹介します。まず前述の説明を確認したうえで、以下の手順に進みましょう。

手順：

• まず、解答に必要な数式を設定します。セルF5に次の数式を入力し、Enterキーを押します。

=SUM(C5:E5)

この数式はSUM関数を使い、1つ目の利用可能資源から生産される鋼材（Regular・Exclusive・Super）の合計量を計算しています。

• 次に、フィルハンドルを下方向へドラッグし、セルF7までオートフィルします。

• 続いて、Regular鋼材の総生産量を計算するために、次の数式を入力します。

=SUM(C5:C7)

• 同様に、セルC14に次の数式を入力して線形化評価値を計算し、隣接するセルE14までオートフィルします。

=SUMPRODUCT($J$5:$J$7,C5:C7)

• その後、セルC16に次の数式を入力し、E16までオートフィルします。

=C12*C8

この数式により、生産された鋼材の線形化評価値が求められます。

• 次に、セルI10に以下の数式を入力して収益を求めます。

=SUMPRODUCT(C11:E11,C8:E8)

• 生産コストを計算するには、次の数式を使用します。

=SUMPRODUCT(I5:I7,F5:F7)

• そして、次の数式で利益が算出されます。

=I10-I11

• その後、目的関数・変数セル・制約条件の設定方法については、方法1で説明した手順と同じです。ここでは、それぞれの不等式の意味を簡単に解説します。
• 利益額を最大化したいので、利益が格納されているセル（I12）を目的セルとして参照します。
• 次に、変数セルは鋼材の生産量が保存される範囲C5:E7です。
• そのうえで、いくつかの制約を追加します。線形化された原材料の評価値は線形化された最低必要評価値以上である必要があるため、範囲C14:E14がC16:E16以上であることを指定します。
• 続いて、生産量が必要数量以上であることから、範囲C8:E8がC10:E10以上であることを指定します。
• さらに、原材料の使用量が利用可能な原材料の量を超えないように、範囲F5:F7がH5:H7以下であることを指定します。

• 「解決」ボタンをクリックすると、最大の利益を得るために各原材料をどれだけ使用すべきかの値が表示されます。
• 同時に、収益、生産コスト、利益の結果も確認できます。

以上の手順で、Excelソルバーを使って柔軟レシピにおける混合線形計画問題を解くことができます。

練習セクション

ここでは、ご自身で練習できるよう、本記事で使用したデータセットをご用意しました。ぜひ実際に操作しながら理解を深めてください。

まとめ

本記事を通じて、Excelソルバーを活用した混合線形計画によって、現実の最適化問題をどのように解決すればよいか、基本的な考え方をつかめたのではないでしょうか。より良い方法や質問、本記事に関するフィードバックがあれば、ぜひコメント欄でお聞かせください。今後の記事作成の参考にさせていただきます。その他のご質問については、弊サイトExcelDemyをご覧ください。