重要: Office 2016 と Office 2019 のサポートは、2025 年 10 月 14 日に終了します。 Microsoft 365 にアップグレードすることで、どんなデバイスでも、どこからでも仕事ができて、かつサポートを受けることができます。 Microsoft 365 を入手する
この記事では、What-if 分析に使用できる Microsoft Excel アドイン プログラムであるソルバーを使用して、最適な製品ミックスを決定する方法について説明します。
収益性を最大化する毎月の製品ミックスを決定するにはどうすればよいですか?
多くの場合、企業は、毎月生成する各製品の数量を決定する必要があります。 最も簡単な形では、 製品ミックス の問題は、利益を最大化するために1ヶ月間に生産する必要がある各製品の量を決定する方法を含みます。 通常、製品ミックスは次の制約に従う必要があります。
-
製品ミックスでは、使用可能なリソースよりも多くのリソースを使用することはできません。
-
各製品に対する需要は限られています。 余分な生産が無駄になるため、需要が指示するよりも多くの製品を1ヶ月間生産することはできません(例えば、生鮮薬)。
次の製品ミックスの問題の例を解決してみましょう。 この問題の解決策は、図 27-1 に示すファイル Prodmix.xlsx にあります。
たとえば、工場で 6 つの異なる製品を生産する製薬会社で働いているとします。 各製品の生産には、労働と原料が必要です。 図27-1の行4は、各製品のポンドを生産するために必要な労働時間を示し、行5は、各製品のポンドを製造するために必要な原材料のポンドを示しています。 たとえば、製品 1 のポンドを生産するには、6 時間の労働と 3.2 ポンドの原材料が必要です。 各薬剤について、1ポンドあたりの価格は行6で与えられ、1ポンドあたりの単位コストは行7で与えられ、1ポンドあたりの利益貢献は行9で与えられる。 たとえば、Product 2 は 1 ポンドあたり 11.00 ドルで販売され、1 ポンドあたり 5.70 ドルの単価が発生し、1 ポンドあたり 5.30 ドルの利益が発生します。 各薬剤に対する月の需要は、行8で与えられます。 たとえば、製品 3 の需要は 1041 ポンドです。 今月は4500時間の労働と1600ポンドの原料が利用可能です。 この会社はどのようにして月間利益を最大化できるのでしょうか。
Excel ソルバーについて何も知らなかった場合は、製品ミックスに関連する利益とリソースの使用状況を追跡するワークシートを作成することで、この問題を攻撃します。 それから私達は利用できるより多くの労働か原料を使用しないで利益を最大限に活用するためにプロダクトミックスを変えるために試行錯誤を使用し、要求を超過する薬剤を作り出さない。 このプロセスでは、試行錯誤の段階でのみソルバーを使用します。 基本的に、ソルバーは、試行錯誤の検索を完璧に実行する最適化エンジンです。
製品ミックスの問題を解決するための鍵は、特定の製品ミックスに関連するリソース使用量と利益を効率的に計算することです。 この計算を行うために使用できる重要なツールは、SUMPRODUCT関数です。 SUMPRODUCT関数は、セル範囲の対応する値を乗算し、それらの値の合計を返します。 SUMPRODUCT評価で使用される各セル範囲には同じディメンションが必要です。つまり、2 行または 2 列のSUMPRODUCTを使用できますが、1 つの列と 1 行では使用できないことを意味します。
製品ミックスの例でSUMPRODUCT関数を使用する方法の例として、リソース使用量を計算してみましょう。 当社の労働使用量は、
(1ポンドあたりの使用労働量 1)*(医薬品1ポンド生産)+ (1ポンドあたりの使用労働 2)*(医薬品2ポンド生産) + ... (1ポンドあたりの使用労働 6)*(医薬品6ポンド生産)
D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 のように、より面倒な方法で労働使用量を計算できます。 同様に、原料使用量は D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5 として計算できます。 ただし、6 つの製品のワークシートにこれらの数式を入力するのは時間がかかります。 たとえば、工場で 50 個の製品を生産している会社と一緒に作業していた場合、どのくらいの時間がかかるかを想像してください。 労働と原材料の使用量を計算するはるかに簡単な方法は、式 SUMPRODUCT ($D$2:$I$2,D4:I4) を D14 から D15 にコピーすることです。 この数式では 、D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (労働使用量) を計算しますが、入力がはるかに簡単です。 私は範囲D2:I2で$記号を使用しているので、数式をコピーしても2行目から製品ミックスをキャプチャしていることに注意してください。 セル D15 の数式は、原材料の使用量を計算します。
同様の方法で、当社の利益は、
(薬 1 ポンドあたりの利益)*(薬剤 1 ポンド生産) + (薬剤 2 ポンドあたりの利益)*(医薬品 2 ポンド生産) + ... (医薬品 6 ポンドあたり利益)*(医薬品 6 ポンド生産)
セル D12 では、数式 SUMPRODUCT(D9:I9,$D$2:$I$2) で簡単に利益が計算されます。
これで、製品ミックス ソルバー モデルの 3 つのコンポーネントを特定できるようになりました。
-
ターゲット セル。 目標は、利益を最大化することです (セル D12 で計算)。
-
セルの変更。 各製品の生産ポンド数 (セル範囲 D2:I2 に記載)
-
制約。 次の制約があります。
-
使用可能な分より多くの労働や原材料を使用しないでください。 つまり、セル D14:D15 (使用されるリソース) の値は、セル F14:F15 (使用可能なリソース) の値以下である必要があります。
-
要求されているよりも多くの薬物を生成しないでください。. つまり、細胞D2:I2の値(各薬剤のポンド)は、各薬剤の需要以下でなければならない(細胞D8:I8に記載されている)。
-
私たちは、任意の薬剤の負の量を生成することはできません.
-
ターゲット セルを入力する方法、セルを変更する方法、および制約をソルバーに入力する方法について説明します。 次に、[解決] ボタンをクリックして、利益を最大化する製品ミックスを見つける必要があります。
開始するには、[データ] タブをクリックし、[分析] グループで [ソルバー] をクリックします。
注: 第 26 章「Excel ソルバーによる最適化の概要」で説明されているように、ソルバーは Microsoft Office ボタン、Excel オプション、アドインの順にクリックしてインストールされます。 [管理] ボックスの一覧で、[Excel アドイン] をクリックし、[ソルバー アドイン] ボックスをチェックし、[OK] をクリックします。
図 27-2 に示すように、[ソルバー パラメーター] ダイアログ ボックスが表示されます。
[ターゲット セルの設定] ボックスをクリックし、利益セル (セル D12) を選択します。 [セルを変更して] ボックスをクリックし、各薬剤で生成されたポンドを含む範囲 D2:I2 をポイントします。 ダイアログ ボックスは図 27-3 のようになります。
これで、モデルに制約を追加する準備ができました。 [追加] ボタンをクリックします。 図 27-4 に示す [制約の追加] ダイアログ ボックスが表示されます。
リソース使用量の制約を追加するには、[セル参照] ボックスをクリックし、範囲 D14:D15 を選択します。 中央の一覧から [<=] を選択します。 [制約] ボックスをクリックし、セル範囲 F14:F15 を選択します。 [制約の追加] ダイアログ ボックスが図 27-5 のようになります。
変化するセルに対してソルバーが異なる値を試みると、 D14<=F14 (使用される労働が使用可能な労働以下) と D15<=F15 (使用される原材料が使用可能な原材料以下) の両方を満たす組み合わせのみが考慮されるようになります。 [追加] をクリックして、需要制約を入力します。 図 27-6 に示すように、[制約の追加] ダイアログ ボックスに入力します。
これらの制約を追加すると、ソルバーがセル値の変更に対して異なる組み合わせを試みるときに、次のパラメーターを満たす組み合わせのみが考慮されます。
-
D2<=D8 (医薬品 1 の生産量が、医薬品 1 の需要以下)
-
E2<=E8 (医薬品 2 の生産量が医薬品 2 の需要以下)
-
F2<=F8 (医薬品 3 の生産量は、医薬品 3 の需要以下)
-
G2<=G8 (医薬品 4 の製造量は、医薬品 4 の需要以下)
-
H2<=H8 (医薬品 5 の生産量は、医薬品 5 の需要以下)
-
I2<=I8 (医薬品 6 の生産量は、医薬品 6 の需要以下)
[制約の追加] ダイアログ ボックスで [OK] をクリックします。 ソルバー ウィンドウは図 27-7 のようになります。
[ソルバー オプション] ダイアログ ボックスに、セルの変更が負ではない必要がある制約を入力します。 [ソルバー パラメーター] ダイアログ ボックスの [オプション] ボタンをクリックします。 次のページの図 27-8 に示すように、[線形モデルの想定] ボックスと [負でないと仮定する] ボックスをオンにします。 [OK] をクリックします。
[負でないと仮定する] ボックスをオンにすると、各変更セルが負以外の値と見なされる変更セルの組み合わせのみがソルバーによって考慮されます。 製品ミックスの問題は線形モデルと呼ばれる特殊な種類のソルバー問題であるため、[ 線形モデルの想定] ボックスをオンにしました。 基本的に、ソルバー モデルは次の条件下で線形です。
-
ターゲット セルは、フォームの用語 (セルの変更) *(定数) を加算して計算されます。
-
各制約は、"線形モデル要件" を満たします。 つまり、各制約は、フォームの用語を一緒に追加して (セルを変更する)*(定数) 、合計を定数と比較することによって評価されます。
このソルバーの問題が線形である理由 ターゲット セル (利益) は、次のように計算されます。
(薬 1 ポンドあたりの利益)*(薬剤 1 ポンド生産) + (薬剤 2 ポンドあたりの利益)*(医薬品 2 ポンド生産) + ... (医薬品 6 ポンドあたり利益)*(医薬品 6 ポンド生産)
この計算は、フォームの用語を一緒に追加することによってターゲット セルの値が派生するパターンに従います (セルの変更)*(定数))。
当社の労働制約は、 (医薬品1ポンドあたり労働量)*(医薬品1ポンド生産)+(医薬品2ポンドあたり労働量)*(医薬品2ポンド生産)+から得られた値を比較することで評価されます。(私たちを労働するed per pound of Drug 6)*(Drug 6 pounds produced) to the labor available.
したがって、労働制約は、フォームの項 (変更セル)*(定数) を加算し、合計を定数と比較することによって評価されます。 労働制約と原材料制約の両方が線形モデル要件を満たしています。
需要制約は次の形式になります
(医薬品1生産)<=(医薬品1需要) (医薬品2生産)<=(医薬品2需要) §(医薬品6生産)<=(医薬品6需要)
各需要制約も線形モデル要件を満たします。これは、フォーム (変更セル)*(定数) の項を一緒に追加し、合計を定数と比較することによって評価されるためです。
私たちの製品ミックスモデルは線形モデルであることを示したのに、なぜ私たちは気にする必要がありますか?
-
ソルバー モデルが線形であり、[線形モデルの想定] を選択した場合、ソルバーはソルバー モデルに対する最適な解を見つけることが保証されます。 ソルバー モデルが線形でない場合、ソルバーは最適な解を見つける場合と見つからない場合があります。
-
ソルバー モデルが線形であり、[線形モデルの想定] を選択した場合、ソルバーは非常に効率的なアルゴリズム (simplex メソッド) を使用してモデルの最適解を見つけます。 ソルバー モデルが線形であり、[線形モデルの想定] を選択しない場合、ソルバーは非常に非効率的なアルゴリズム (GRG2 メソッド) を使用し、モデルの最適解を見つけるのに苦労する可能性があります。
[ソルバー オプション] ダイアログ ボックスで [OK] をクリックすると、図 27-7 で前に示した [ソルバーのメイン] ダイアログ ボックスに戻ります。 [解決] をクリックすると、ソルバーは製品ミックス モデルに最適なソリューション (存在する場合) を計算します。 第26章で述べたように、製品ミックスモデルに対する最適なソリューションは、すべての実行可能なソリューションのセットに対して利益を最大化する変化する細胞値(各薬物の産生ポンド)のセットであろう。 ここでも、実行可能な解決策は、すべての制約を満たすセル値を変更するセットです。 図 27-9 に示すセル値の変化は、すべての運用レベルが負ではなく、運用レベルが需要を超えず、リソース使用量が使用可能なリソースを超えていないため、実現可能なソリューションです。
次のページの図 27-10 に示されているセル値の変更は、次の理由 から不可能な解決策 を表しています。
-
私たちは、それの需要よりも多くの薬物5を生産しています。
-
私たちは、利用可能なものよりも多くの労力を使用します。
-
私たちは、利用可能なものよりも多くの原料を使用しています。
[解決] をクリックすると、ソルバーは図 27-11 に示す最適なソリューションをすばやく見つけます。 ワークシート内の最適なソリューション値を保持するには、[ソルバー ソリューションの保持] を選択する必要があります。
当社の製薬会社は、596.67ポンドの薬物4、1084ポンドの薬物5、および他の薬剤を生産することにより、$6,625.20のレベルで毎月の利益を最大化することができます! 他の方法で $6,625.20 の最大利益を達成できるかどうかを判断することはできません。 私たちが確信できるのは、限られたリソースと需要があれば、今月6,627.20ドル以上を作る方法がないということです。
各製品の需要を満たす 必要があると します。 (ファイル Prodmix.xlsx の「 実現可能なソリューションなし 」ワークシートを参照してください)。 その後、需要制約を D2:I2<=D8:I8 から D2:I2>=D8:I8 に変更する必要があります。 これを行うには、ソルバーを開き、D2:I2<=D8:I8 制約を選択し、[変更] をクリックします。 図 27-12 に示す [制約の変更] ダイアログ ボックスが表示されます。
[>=] を選択し、[OK] をクリックします。 これで、ソルバーがすべての要求を満たすセル値のみを変更することを検討できるようになりました。 [解決] をクリックすると、"ソルバーで実現可能なソリューションが見つかりませんでした" というメッセージが表示されます。 このメッセージは、モデルを間違えたという意味ではなく、限られたリソースでは、すべての製品の需要を満たすことはできません。 ソルバーは、各製品の需要を満たす場合は、より多くの労働、より多くの原材料、または両方の多くを追加する必要があることを私たちに伝えています。
各製品の無制限の需要を許可し、各薬剤の負の量を生産できる場合はどうなるかを見てみましょう。 (このソルバーの問題は、ファイル Prodmix.xlsx の [値を収束しない ] ワークシートで確認できます)。 このような状況に最適なソリューションを見つけるには、[ソルバー] を開き、[オプション] ボタンをクリックし、[負でないと仮定] ボックスをオフにします。 [ソルバー パラメーター] ダイアログ ボックスで、需要制約 D2:I2<=D8:I8 を選択し、[削除] をクリックして制約を削除します。 [解決] をクリックすると、ソルバーは "セル値を収束しない" というメッセージを返します。 このメッセージは、(この例のように) ターゲット セルを最大化する場合、任意に大きなターゲット セル値を持つ実行可能なソリューションがあることを意味します。 (ターゲット セルを最小化する場合、「セル値を収束しない」というメッセージは、任意に小さいターゲット セル値を持つ実行可能なソリューションがあることを意味します)。 私たちの状況では、薬物の負の生産を可能にすることによって、我々は事実上、他の薬剤の任意に大量の生産に使用することができるリソースを「作成する」。 私たちの無制限の需要を考えると、これは私たちが無制限の利益を上げることができます。 実際の状況では、無限の金額を稼ぎることはできません。 つまり、"値の設定は収束しない" と表示された場合、モデルにエラーが発生します。
-
私たちの製薬会社は、現在の労働コストよりも1時間あたり$ 1で最大500時間の労働を購入することができるとします。 どうすれば利益を最大化できるでしょうか。
-
チップ製造工場では、4 人の技術者 (A、B、C、D) が 3 つの製品 (製品 1、2、および 3) を生産します。 今月、チップメーカーは製品1の80ユニット、製品2の50ユニット、および製品3の最大50ユニットを販売することができます。 技術者 A は、製品 1 と 3 のみを作成できます。 技術者 B は、製品 1 と 2 のみを作成できます。 テクニシャン C は、製品 3 のみを作成できます。 テクニシャン D は、製品 2 のみを作成できます。 生産されたユニットごとに、製品は次の利益を提供します: 製品 1, $6;製品 2、$7。製品 3、$10。 各技術者が製品を製造するために必要な時間 (時間単位) は次のとおりです。
製品
テクニシャン A
テクニシャン B
テクニシャン C
テクニシャン D
1
2
2.5
実行できません
実行できません
2
実行できません
3
実行できません
3.5
3
3
実行できません
4
実行できません
-
各技術者は、1 か月あたり最大 120 時間勤務できます。 チップメーカーはどのようにして毎月の利益を最大化できますか? 小数部の単位を生成できるものとします。
-
コンピューター製造工場では、マウス、キーボード、およびビデオ ゲームジョイスティックを製造しています。 次の表に、単位あたりの利益、単位あたりの労働使用量、月次需要、単位あたりの機械時間あたりの使用量を示します。
マウス
キーボード
ジョイスティック
利益/単位
$8
$11
$9
労働使用量/単位
.2 時間
.3 時間
.24 時間
機械時間/単位
.04 時間
.055 時間
.04 時間
月間需要
15,000
27,000
11,000
-
毎月、合計13,000時間の労働時間と3000時間の機械時間を利用できます。 どのようにメーカーは、工場からの毎月の利益貢献を最大化することができます?
-
各薬剤に対して200単位の最小需要を満たす必要があると仮定して、私たちの薬物例を解決してください。
-
ジェイソンはダイヤモンドブレスレット、ネックレス、イヤリングを作ります。 彼は月に最大160時間働きたいと考えています。 彼はダイヤモンドの800オンスを持っています。 各製品の製造に必要なダイヤモンドの利益、労働時間、オンスを以下に示します。 各製品の需要が無制限の場合、ジェイソンはどのように彼の利益を最大化できますか?
製品
ユニット利益
単位あたりの労働時間
単位あたりのダイヤモンドのオンス
腕輪
¥30,000
.35
1.2
首飾り
¥20,000
.15
.75
璫
¥10,000
.05
0.5
