ソルバーを使用して、最適な製品の組み合わせを決定するには

注:  最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。 このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。私たちの目的は、このコンテンツがお客様の役に立つようにすることです。お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。 簡単に参照できるように、こちらに 英語の記事 があります。

この記事では、ソルバーを使用して、Microsoft Excel アドイン プログラムを使用できます what-if 分析] の最適な製品の混在を決定するします。

収益を最大毎月の製品の組み合わせを調べることができますか。

企業は、月単位で作成するには、各製品の数量を決定する必要があります。最も簡単なフォームでは、製品の組み合わせの問題には、利益を最大化する月間生産製品ごとの容量を確認する方法が含まれます。製品の組み合わせは、次の制約通常従う必要があります。

  • 製品の組み合わせは、使用可能なその他のリソースを使用できません。

  • 各製品の制限需要があります。おことはできませんので、作成、製品のニーズに応じてサポートより 1 か月の中に (たとえば、生鮮食品製品) 無駄に余分な製品。

製品の組み合わせの問題の次の例を解決してみましょう。Prodmix.xlsx、図 27-1 に示すように、ファイルでは、この問題の解決策を確認できます。

本の画像
図 27-1 製品の組み合わせ

自社工場で 6 つの異なる製品を生成する製品会社に勤務してみましょう。各製品の製造には、労働と raw 教材が必要です。行 4 図 27-1 ポンド、各製品の作成に必要な作業の時間を表示して、行 5 の raw 数量単価型の各製品のポンドの作成に必要なポンドを示しています。たとえば、製品 1 ポンドを生成すると、6 労働時間と 3.2 ポンド raw 数量単価型が必要です。各製品シャープあたりの価格は行 6 で示されます、シャープあたりの単位コストは行 7 で示されますおよびポンド利益への投稿は行 9 で示されます。たとえば、製品 2 ポンド 11.00 ドルの販売シャープ、5.70 ドルの単価を終わらせるし、5.30 ドルの収益シャープを提供します。各製品の月のオンデマンドは 8 行で示されます。たとえば、製品 3 の要求は、1041 ポンドします。今月の労働 4500 時間と 1600 ポンド raw 数量単価型を利用できます。この会社は、その月の利益を方法できます最大化します。

何も Excel ソルバー場合、製品の組み合わせに関連付けられている利益とリソースの利用状況を追跡するワークシートを作成することによってこの問題は攻撃します。[要求を超える任意の製品を生成せずにその他の労力や数量単価型の使用可能なではなく raw を使わない利益を最適化する製品の組み合わせを変更する試行錯誤を使います。このプロセスで試用版およびエラーのステージでしかソルバーを使用するとします。基本的には、ソルバーは完璧試用版およびエラーの検索を実行する最適化エンジンです。

製品の組み合わせの問題を解決するためにキーでは、リソース配分状況と指定された製品の組み合わせに関連付けられている利益を効率的に計算します。計算に使用できる重要なツールは、SUMPRODUCT 関数です。SUMPRODUCT 関数では、セル範囲に含まれる値を乗算し、これらの値の合計を返します。SUMPRODUCT の評価で使用されている各セルの範囲は、2 つの行または 2 つの列が 1 つの列と 1 つの行ではなく、SUMPRODUCT を使ってできることを意味するを同じサイズが必要です。

製品の SUMPRODUCT 関数を使用して方法の例との組み合わせの例、[リソース配分状況を計算してみましょう。によって、労働力使用量が計算されます。

(労働数 1) + *(Drug 1 pounds produced)
(労働 2 数ごとに使用) * (薬品 2 の生産ポンド) +...
(労働 6 数ごとに使用) * (薬品 6 の生産ポンド)

その他の面倒な方法での作業の使用可能性のある計算D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4します。同様に、raw 数量単価型の配分状況として計算する可能性のあるD2 * D5 + E2 *E5 ながら F2 * f5 キー + G2 * G5 + H2 * H5 + I2 * I5します。ただし、製品の 6 つのワークシートでこれらの数式を入力することは時間がかかるです。たとえば、どのくらい時間がかかる作業かどうかなど、作成した会社と自社工場で 50 製品です。労働および raw 数量単価型の使用を計算するために、もっと簡単な方法では、数式をコピーする D14 から D15 にSUMPRODUCT($D$2:$I$2,D4:I4)します。この数式を計算D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (これは、労働使用) が非常に簡単に入力です。確認できるように、行 2 の製品の組み合わせをまだキャプチャした数式をコピーしたときに、範囲を d2: i2 に $ 記号を使用します。セル D15 の数式では、raw 数量単価型の使用を計算します。

同様の方法では、利益によって決定されます。

(薬品 1 の収益) * (製品 1 ポンド生産) +
(ポンド製品 2 利益) * (薬品 2 の生産ポンド) +...
(ポンド製品 6 利益) * (薬品 6 の生産ポンド)

利益が簡単に d12 SUMPRODUCT(D9:I9,$D$2:$I$2)数式で計算されます。

この製品の組み合わせソルバー モデルの 3 つのコンポーネントこれで識別できます。

  • ターゲット セル。利益 (d12 で計算されます) を最大化する目標ことです。

  • 変化させるセル。ポンドの数が (d2: i2 セル範囲に記載されている) 各製品の生成

  • 制約。次の制約があります。

    • その他の労働または数量単価型の利用可能なではなく raw を使わないでください。つまり、セル d14: d15 (使用されているリソース) の値では、以下の値にセル f14: f15 (使用可能なリソース) にする必要があります。

    • オンデマンドではなく、製品の詳細を生成しません。つまり、セル d2: i2 にある (各製品の生産ポンド) の値が (セル d8: i8 に記載されている) 各製品の需要以下である必要があります。

    • 負の数、金額を生成することはできません。

方法を紹介する、ターゲット セルを入力するセル、および制約条件を変更するソルバーにします。[すべて実行する必要があるユーザーを利益最大製品の組み合わせを見つけるには [解決] ボタンをクリックします。

[データ] タブをクリックし、[分析] グループで [ソルバー] をクリックします。

注: 章 26、「の概要を最適化で Excel ソルバー、」で説明するようには、Microsoft Office ボタンをクリックし、Excel のオプション] のアドインが続くしてソルバーがインストールされています。管理] ボックスの一覧で Excel アドイン] をクリックし、[ソルバー アドイン] ボックスで、確認し、[OK] をクリックします。

図 27-2 に示すように、[ソルバー: パラメーター設定] ダイアログ ボックスが表示されます。

本の画像
図の 27 2 の [ソルバー: パラメーター設定] ダイアログ ボックス

目的セル] ボックスをクリックし、[収益のセル (セル D12) を選択します。変化させるセル] ボックスをクリックし、各製品の生産ポンドが含まれている d2: i2 範囲] をポイントします。ダイアログ ボックスに、図 27-3 が表示されます。

本の画像
目的セルとセルの定義を変更する図 27-3 [ソルバー: パラメーター設定] ダイアログ ボックス

制約をモデルに追加する準備ができました。[追加] をクリックします。図 27-4 が表示される [制約条件の追加] ダイアログ ボックスが表示されます。

本の画像
図 27-4 の制約条件の追加] ダイアログ ボックス

リソース配分状況] の制約条件を追加するには、セル範囲] ボックスをクリックし、d14: d15 の範囲を選択します。選択 < = 中央のリストからします。[制約の指定] ボックスをクリックし、セル範囲 f14: f15 を選択します。[制約条件の追加] ダイアログ ボックスは図 27 ~ 5 時のようになります。

本の画像
[リソース配分状況] の制約条件が入力図 27 ~ 5 時の制約条件の追加] ダイアログ ボックス

ソルバーでは、変更するためのさまざまな値とセル、両方を満たす唯一の組み合わせを確認して今すぐD14 < = F14 (使用労働を使うと、利用可能な労働小さいです) とD15 < = F15 (raw 資材が小さく、一致するraw 資料) になります。需要の制約条件を入力する追加] をクリックします。図 27-6 で示すように、[制約条件の追加] ダイアログ ボックスを入力します。

本の画像
需要の制約条件を入力した図 27 時 ~ 6 時の制約条件の追加] ダイアログ ボックス

これらの制約を追加すると、ソルバー変化させるセルの値の組み合わせを変えてしようとすると、次のパラメーターを満たすための組み合わせのみが考慮するようにします。

  • D2 < = D8(製品 1 の生産量が以下のより製品 1 の需要)

  • E2 < = E8(量製品 2 の作成は製品 2 の需要以下)

  • F2 < = f8 キーを押し(生産行った製品 3 の量を使うと、製品 3 の需要以下です)

  • G2 < = G8(製品ラインが行われた生産量が以下のより製品 4 の需要)

  • H2 < = H8(行った製品 5 の生産量が以下のより製品 5 の需要)

  • I2 < = I8(行った製品 6 の生産量が以下のより製品 6 の需要)

[制約条件の追加] ダイアログ ボックスで [ok] をクリックします。[ソルバー] ウィンドウは、図 27 ~ 7 のようになります。

本の画像
図 27 ~ 7 製品の組み合わせの問題の最終的な [ソルバー: パラメーター設定] ダイアログ ボックス

セルを変更する必要がある制約条件を入力して [ソルバー: オプション] ダイアログ ボックスで負です。[ソルバー: パラメーター設定] ダイアログ ボックスで、[オプション] ボタンをクリックします。次のページで、図 27 ~ 8 を示すように、[線形モデル] ボックスと [非負数を仮定ボックスを確認します。[Ok] をクリックします。

本の画像
図の 27 8 ソルバー オプションの設定

ソルバーで変化させるセルを各変化させるセルは正の値の組み合わせだけを考慮することにより、チェック ボックスをオン非負数を前提としています。線形モデル] ボックスは製品の問題を混在させるため、特殊な線形モデルと呼ばれるソルバーの問題を確認します。基本的には、ソルバー モデルは、次の条件を満たす行です。

  • フォームの条項してターゲット セルが計算される(合算を変更するします。

  • 各制約"線形モデルの要件を満たしています"フォームの用語を加算することで各制約が評価されることになります(合算を変更すると合計を定数を比較します。

このソルバーの問題を線形はなぜですか。目的セル (利益) として計算されます。

(薬品 1 の収益) * (製品 1 ポンド生産) +
(ポンド製品 2 利益) * (薬品 2 の生産ポンド) +...
(ポンド製品 6 利益) * (薬品 6 の生産ポンド)

計算に依存してフォームの条件によってターゲット セルの値を算出するパターン(合算を変更するします。

(労働製品 1 ポンドを使用) から派生した値を比較することによって、労働制約が評価される * (製品 1 ポンド生産) + (労働製品 2 の 1) + *(Drug 2 pounds produced).(人件へのご協力 ポンド製品 6 の数は) * (薬品 6 の生産ポンド)利用可能な労働にします。

フォームの条件によって労働制約条件を評価するために、 (合算を変更すると合計を定数を比較します。労働制約と raw 数量単価型の制約の両方線形モデルの要件を満たします。

需要の制約条件をフォーム

(製品 1 生成) < = (製品 1 必要に応じて)
(製品 2 が生成される) < = (製品 2 必要に応じて)
参照
(製品 6 が生成される) < = (製品 6 必要に応じて)

需要の制約条件は、フォームの用語を加算することで各が評価されるためも線形モデルの要件を満たす(合算を変更すると合計を定数を比較します。

製品の組み合わせモデルが線形モデルを示すが、理由が重要ですか。

  • ソルバーのモデルが線形線形モデルを選択する、ソルバーをモデルに最適な解決策を見つけるには保証されます。ソルバー モデルが線形ではない場合は、ソルバーかまいません最適なソリューションが見つからない場合があります。

  • ソルバーのモデルが線形線形モデルを選択する、ソルバーをモデルの最適な解決策を見つけるには非常に効率的なアルゴリズム (シンプレックス方法) を使用します。ソルバー モデルが線形いない線形モデルを選択しない場合は、ソルバー アルゴリズム (GRG2 方法) を使用し、モデルの最適な解決策を見つけることが困難があります。

ソルバーのオプション] ダイアログ ボックスで [ok] をクリックすると、それ以前のバージョンの図 27 ~ 7 を示すメイン [ソルバー] ダイアログ ボックスに戻ります。解決] をクリックしたときソルバーは製品の組み合わせモデルの最適なソリューション (ある場合) を計算します。章 26 で前述は、製品の組み合わせモデルに最適なソリューションは一連のセルの値 (ポンド各製品の作成) を変更するすべての解のセットの利益を最大化になります。もう一度、解を的なすべての制約条件を満たすセルの値を変更します。図 27-9 に示すように変更するセルの値では、解はすべての生産レベルは負、生産レベルは、必要に応じてを超えていないおよびリソース配分状況] が使用可能なリソースを超えていないためです。

本の画像
製品の図の 27 9 解の組み合わせの制限内での問題に収まってします。

変化させるセルの値図 27-10 で次のページでは、次の理由により、不可能ソリューションを表します。

  • その需要製品 5 の生産します。

  • 使用可能なより労働を使用します。

  • も、何を利用できるその他の raw 教材を使用します。

本の画像
図の 27 10 不可能な解製品の組み合わせの問題には、定義された制限内で収まらないです。

解決をクリックすると、ソルバーは、図 27-11 を示すように最適なソリューションをすばやく検索します。ワークシートに最適なソリューションの値を保持するソルバーの解の保持を選択する必要があります。

本の画像
図 27-11、製品の組み合わせの問題に最適な解決策

製品会社は、製品 4、製品 5 の 1084 ポンド、およびその他の医薬品の 596.67 ポンドを生成する 6,625.20 ドルのレベルでの月間の収益を最大化ことができます。かどうかは、その他の方法で 6,625.20 ドルの最大の利益を実現できるようおを決定することはできません。すべてのことができます、ことと、限られたリソース需要、なし今月 6,627.20 ドル以上を作成する方法です。

各製品必要がありますの要求が満たされていると仮定します。(ファイル Prodmix.xlsxなしの解のワークシートを参照してください)。需要の制約条件を変更するのには、[あるd2: i2 < = d8: i8d2: i2 > = d8: i8します。これを行うには、ソルバーを開く、選択、d2: i2 < = d8: i8 制約、および [変更] をクリックします。図 27 ~ 12 を示す [制約条件の変更] ダイアログ ボックスが表示されます。

本の画像
図 27 ~ 12 の制約条件の変更] ダイアログ ボックス

選択 > =、およびし、[OK] をクリックします。これにより、すべてのニーズを満たすセルの値だけを変更するソルバーを検討することです。解決をクリックしたときにメッセージが表示されます、「ソルバー見つかりませんでした解」このメッセージには間違いでのモデルではなく、限られたリソースで、すべての製品のオンデマンド満たすことができないことはありません。ソルバーは、単に指示へのご協力、各製品のニーズに応える必要がある場合が必要な労働、raw 材料またはその両方を追加します。

動作を確認してみましょう場合は各製品の無制限需要を許可して、各製品の生産量が負の値を許可することです。(このソルバーの問題は Prodmix.xlsx ファイルで設定する値が収束しませんワークシートの参照できます)。このような状況の最適なソリューションの検索、[ソルバーを開き、[オプション] ボタンをクリックして非負数を仮定ボックスをオフにします。[ソルバー: パラメーター設定] ダイアログ ボックスで選択需要の制約条件 d2: i2 < = d8: i8 し、[制約条件を削除します] をクリックします。解決] をクリックすると、[ソルバー: メッセージを返します「設定するセルの値が収束しません」します。このメッセージは、目的のセル (この例では) のように最大化する場合が任意の大きなターゲット セルの値を持つ解を意味します。(ターゲット セルが最小化する場合は、「を設定するセルの値が収束しません」メッセージは、小さなターゲット セルの値を持つ解があります。)この状況で、製品の製造を負の値を許可することによって有効「リソースを作成」任意に大量の他のドラッグを生成するために使用できます。需要を制限しないを指定するには、これにより、無制限の利益を行えるようになりました。実際の状況での無限の金額も行うことはできません。簡単に言うと、「を設定する値が収束しません」を表示する場合は、モデルが、エラーがします。

  1. たとえば、製品会社は、最大 500 時間 $1 1 時間に現在の労働コストをよりで作業を購入できます。利益を最大化して方法はよいですか。

  2. 設備を製造チップでは、4 つの技術者 (A、B、C と D) は、3 つの製品 (製品 1、2、3) を生成します。80 単位製品 1 の 50 単位製品 2、および製品 3 の 50 単位で、this month、チップ メーカーを販売できます。技術者 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

    実行できません。

  3. 各技術者には、1 か月あたり最大 120 時間を操作できます。方法チップ メーカーを最大化月間の収益ですか。単位数の小数部が生成されるものとします。

  4. 設備を製造するコンピューターでは、マウスやキーボード、ビデオ ゲーム ジョイスティックが生成されます。単位あたり利益、単位あたり労働使用、毎月の必要に応じて、および単位あたりマシン時間の配分状況] は、次の表に与えられます。

    マウス

    キーボード

    ジョイスティック

    利益/単位

    ¥ 8

    $ 11

    $ 9

    米国労働使用/単位

    .2 時間

    .3 時間

    .24 時間

    コンピューターの時間/単位

    .04 時間

    .055 時間

    .04 時間

    毎月の要求

    15,000

    2万7,000

    11,000

  5. 1 か月、13,000 の労働時間とコンピューターの 3000 の作業時間の合計を利用できます。工場の月の利益寄与度を製造元はできる最大化するですか。

  6. この製品の例と仮定した各製品の 200 単位の最小要件を満たす必要がある解決されます。

  7. ひし形のブレスレット、ネックレス、およびイヤリングを製作 Jason を使用するとします。最大 1 か月あたり 160 時間を操作するたいと考えています。ひし形の 800 オンスを持ち、します。下にある、利益、労働時間、および各製品の作成に必要なひし形のオンスが与えられます。各製品の需要が制限されている場合どう Jason を最大化できます収益ですか。

    製品

    単位の収益

    作業の時間単位

    単価のひし形のオンス

    ブレスレット

    ¥30,000

    .35

    1.2

    首飾り

    $200

    .15

    .75

    イヤリングを製作

    \10,000

    .05

    0.5

Office のスキルを磨く
トレーニングの探索
新機能を最初に入手
Office Insider に参加する

この情報は役に立ちましたか?

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×