Excel ソルバーを使用して混合線形計画問題を解く方法

この記事では、混合線形計画法の解き方を紹介します Excel ソルバーの問題 .線形計画法は、ビジネス セクターでコストを最小化したり、利益を最大化したい場合に重要なアプリケーションです。食習慣やその他の支出を最適化する必要があるため、日常生活にとっても重要です。 混合線形計画法 は特殊な 線形計画法 です 利益またはコストの最適化は、他の原材料の混合物から作られた 1 つまたは複数の製品で計算されます。 Excel を使用して、この混合線形計画法を適用します。

混合線形計画法とは

化学工業や食品工場で働くと、さまざまな材料を混ぜ合わせて製品を作る必要があります。たとえば、医薬品を製造したい場合、それに必要な要素が必要であり、それらを定義された比率で混合する必要があります。さらに、これらの要素をいくつ購入する必要があるか、製品の品質がどのようになるかなどを覚えておく必要があります.この場合、お客様に届けたい製品の配合量を最適化する必要があります。この問題の解決策は、 混合線形計画法 によって提供されます。 応用。このアプリケーションは、生産で原材料を使用する方法を学ぶのに役立ちます。

Excel ソルバーを使用して混合線形計画問題を解く 2 つの例

この記事では、混合線形計画法の 2 種類の例を説明します。 .一例として、 ブレンディング LP の解決方法を示します。 (線形計画法 ) 固定レシピ もう 1 つは 柔軟なレシピ 用です .

固定レシピ 材料の正確な比率または混合量がわかっているシナリオを指します。一方、商品の生産に一定量の原材料を使用する必要がない場合は、 Blending LP を適用する必要があります。 柔軟なレシピ用 .

最初の例は、 固定レシピ を解決する方法を示すことに専念しています 問題。ここでは、3 種類の液体材料 A があります。 、B 、および C . A-B を使用して 2 つの新しい製品を作成します そしてA～C 60%-40% の組み合わせで 80%～20% それぞれ。 収益/リットルのような他のパラメータがあります 、コスト/リットル 、および工場で入手可能な原材料の数。 最大化します このシナリオからの利益。

柔軟なレシピの場合 問題、通常のようなさまざまな種類の鋼を生産します 、排他的 そして 最高の品質 異なる原材料の混合物からの鋼。これらの原材料の入手可能量、トン当たりのこれらの原材料のコスト、およびそれらの品質評価に関するデータがあります。また、必要な生産量、生産されたさまざまなクラスの鋼のトンあたりの価格、およびそれらの最低定格も設定します。 Linearized Rating と呼ばれる別のパラメータがあります。 .

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

この問題については、前のセクションで説明しました。以下のプロセスを見てみましょう。

手順:

• まず、必要な式をいくつか設定します。 生の使用法 について知りたい セル C12 で次の式を使用します

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

ここでは SUMPRODUCT 関数 を使用しました Raw Usage を返します 材料Aの .

• その後、塗りつぶしアイコン をドラッグします。 右側の オートフィル E12までの細胞 .

• 後で、 I11 で次の式を使用します 収益の計算 .

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

ここでは、式にセル C16 の値を掛けました。 これは 3.7854 です 1 ガロンは 3.7854 に等しいため リットル。

• 次に、セル I12 に次の式を入力します ENTER を押します .

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

この式は生産コストを返します。

• その後、次の式を適用して利益を計算します .

=I11-I12

• 次に、生産の最小要件を設定します。

• その後、 データ に移動します>> ソルバー . Solver Add-in の追加方法がわからない場合 データ タブ 、[ファイル] を選択します>> オプション >> アドイン >> Excel アドイン >> 行く>> ソルバー アドイン [OK] をクリックします またはこのリンクに従ってください .
• ソルバー アドインを開くには 、データに移動>> ソルバー .

• 利益を最大化したいので、客観的参照を I13 に設定します 利益の保存場所
• また、変数は製品の混合率です。そこで G5:G9 を追加しました 「変数セルを変更する」までの範囲 ' セクション。
• その後、[追加] をクリックします。 制約を追加するには .

• 原材料の使用 利用可能な材料を超えることはできません したがって、最初の制約は範囲 C12:E12 です。 C14:E14 以下になります .
• その後、[追加] をクリックします。 .

• 同様に、生産量が最低限必要な生産量よりも多いことを意味する別の制約を追加しました。
• 次に、[OK] をクリックします .

• その後、「制約のない変数を非負にする」にチェックを入れます
• その後、シンプレックス LP を選択します 解決方法として .
• 後で、[解決] をクリックします。 .

• その後、確認メッセージ ボックスが表示されます。 OK をクリックするだけです .

• 最後に、原材料の値を取得します 最大利益を得るために使用する必要があります .
• さらに、収益の結果も得られます 、 コスト 生産量、利益

したがって、 混合線形計画法 を解くことができます 固定レシピの Excel ソルバーの問題 .

2.柔軟なレシピ ブレンディング線形計画問題の Excel ソルバー

このセクションでは、 混合線形計画問題 の解き方を紹介します 柔軟なレシピの .この問題については、この リンク にアクセスしてください。 この記事の。それでは、次の説明を見ていきましょう。

手順:

• まず、ソリューションの数式を設定します。次の式をセル F5 に入力します ENTER を押します。

=SUM(C5:E5)

式は SUM 関数 を使用しています タイプ 1 Steel の合計金額を計算する (レギュラー 、 排他的 そしてスーパー ) 最初の Available から生成されます リソース。

• 次に、塗りつぶしをドラッグします 下のアイコンで F7 までセルを埋めます .

• 後で、次の数式を入力して合計 通常鋼 を計算します 生産量。

=SUM(C5:C7)

• 同様に、次の数式をセル C14 に書き留めます。 線形評価を計算する 隣接するセルを E14 まで埋めます .

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

• その後、次の数式 C16 を入力します E16 までセルを埋めます .

=C12*C8

The formula will give us the Linearized Rating of the produced Steels .

• Next, write down the formula below in cell I10 to determine the Revenue .

=SUMPRODUCT(C11:E11,C8:E8)

• To calculate the production cost, use the following formula.

=SUMPRODUCT(I5:I7,F5:F7)

• And the following formula will return the Profit

=I10-I11

• After that, to enter the Subject , Changing Variable and Constraints , please follow the link of Method 1 that will lead you to the process. I’ll simply explain these inequalities in the following description.
• We want to maximize the profit amount, so we reference the cell that contains profit (I12 ).
• Next, our changing variables are the Steel products, so this range will be C5:E7 where the amount of production will be stored.
• After that, we added some constraints. The Linearized Raw Rating will be greater than or equal to Linearized Minimum Required Rating , so the range C14:E14 will be greater or equal to C16:E16 .
• Thereafter, the production amount will be greater than the required amount. So the range C8:E8 will be greater than C10:E10 .
• And the usage of raw materials will be lower than the available raw materials. So the range F5:F7 will be greater than H5:H7 .

• After clicking on Solve , you will get the values of how much Raw Materials you should use to get the Maximum Profit .
• In addition, you will also get the results of Revenue 、 コスト of production and Profit .

Thus you can solve the Blending Linear Programming problem with Excel Solver for the Flexible Recipe .

練習セクション

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

結論

Suffice to say, you will achieve the basic idea of how to apply Blending Linear Programming to solve real life optimization problems with Excel Solver .  If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy .