Windows TIPS

［Office Master］

→ Windows TIPS TOPへ → Windows TIPS全リストへ → 内容別分類一覧へ ソルバー機能による人員配分シミュレーション → 解説をスキップして操作方法を読む WINGSプロジェクト 本田 将輝 2004/03/06 　 対象ソフトウェア Excel 2000 Excel 2002 Excel 2003

■ Excelに用意されている分析機能である「ソルバー」を利用すると、数式や与えられた条件を元にした逆算を行うことができる。 ■ 逆算により、さまざまなシミュレーションが可能になる。

　

解説

　Excelにはさまざまな分析を支援する機能が用意されている。中でも、ゴールシークやソルバーを利用することで、与えられた条件と数式に基づいてさまざまなシミュレーションが可能になる。

　別稿「TIPS―PMT関数とゴールシーク機能によるローン・シミュレーション」でも紹介したように、ゴールシークは1つの値を変化させながら目標値を探し出すための機能だ。複数の変数を設定することはできないものの、ソルバーよりシンプルで分かりやすい操作性を提供する。一方、複数の変数値を含む、より複雑なシミュレーションを実行したい場合には、ソルバーを利用する。ソルバーは、単純な目標値のみならず、条件を満たすための最大値・最小値など、幅を持った結果を取得することができる。また、ゴールシークに比べ、より木目細やかな制約条件を設定できるのが特徴だ。

　本稿では、ソルバーを利用して、アルバイトを雇用している、架空の店舗を例に人件費低減のシミュレーションを実践してみる。

• サンプル・ファイルのダウンロード
  （注：サンプル・ファイルworkshift_simulation.xlsをダウンロードするには、上のリンクを右クリックして、workshift_simulation.xlsというファイル名で保存してください。）

アルバイト人員の配分のシミュレーション

アルバイトのシフト・パターン（勤務表）と曜日ごとに必要な人員表を元にして、人件費が最小となるパターンの組み合わせを求める（アルバイトごとの勤務パターンを求めるのではなく、必要なパターンごとに割り当てるアルバイトの人数と、その人件費の総額を求める）。

　週4日の勤務を前提とした7種類のシフト・パターンと（ここでは話を簡単にするため、4勤3休というパターンのみを考慮している）、曜日ごとに必要な人員数を元に、ソルバー機能を利用して、各シフトに配置すべきアルバイトの人数および必要な人件費を算出する。

操作方法

手順1―Excelのアドインを設定する

　本TIPSで紹介するソルバーは、「ソルバー アドイン」というExcelのアドインを有効にすることで利用可能になる。「ソルバー アドイン」アドインはExcelに標準でインストールされているが、利用のためには初期設定が必要になる。

　初期設定のためには、［ツール］メニューから［アドイン］を選択し、［アドイン］ダイアログを表示する。［アドイン］ダイアログの［有効なアドイン］欄から［ソルバー アドイン］のチェック・ボックスをオンにし、［OK］ボタンをクリックする。この際、環境によっては「このアドインはMicrosoft Excelで実行できません。この機能は現在インストールされていません。インストールしますか？」というメッセージが表示される場合があるので、その場合は［はい］ボタンをクリックする。その後、環境によってはExcel（Office）のインストール用イメージ（セットアップ用CD-ROMなど）を要求するメッセージが表示されるので、指示どおりにインストール媒体をセットする。

手順2―シフト・パターン表

　Excelを開いて、アルバイトなどのシフト・パターン表を作成してみよう。ここでは勤務のパターンを「1」と「0」で表現している。

シフト・パターン表

縦軸をシフト・パターン(7通り)、横軸を曜日とする。勤務する曜日には「1」、それ以外の曜日には「0」を設定する。「アルバイト人数」欄は後にソルバーで自動計算した結果を格納するため、初期値としては「0」をセットして表を作成する。欄外のセル「D12」には各シフトのアルバイト人数の総和を合算する数式「=SUM(D5:D11)」を入力する。

　シフト・パターン表が完成したら、ワークシート上に残りの部分も作成する。

セル位置	内容	解説
E17:K17	[E17の場合] =$D$5*E5+$D$6*E6 +$D$7*E7 +$D$8*E8 +$D$9*E9 +$D$10*E10 +$D$11*E11 ※F17〜K17はE17の内容をドラッグ＆コピー	シミュレーション結果(該当曜日が勤務日となっている各シフトの配置人数を算出)
D19	\7,200（固定額）	1人当たりの日当
D20	=D12*D19	人件費／週（各シフトのアルバイト人数の総和に、1人当たりの日当を乗じた金額）
セルの式の設定

手順3―最適なアルバイト人数および必要な人件費を逆算する

　ここまでの下準備が完成したら、さっそくソルバーを利用してみよう。［ツール］メニューから［ソルバー］を選択し、［ソルバー：パラメータ設定］ダイアログで必要な設定を行う。

「ソルバー：パラメータ設定」ダイアログ

逆算するための各種必要情報を設定する。

ソルバーの計算対象となるセル。 　 値の最大値を求めるのか、最小値を求めるのか、などの指定。 　 値を変化させるセルの指定。 　 この条件を満たすように値を変化させる。条件を追加するには、右側の［追加］ボタンをクリックする。 　 これをクリックするとソルバーを実行する。

　ダイアログの各欄は、次のような意味を持っている。

入力項目	解説
［目的セル］	最大値、最小値、または特定の値にするセルの参照または名前を指定。このセルには数式が入力されている必要がある
［目標値］	目的セルを[最大値]、[最小値]、[値]のいずれの値に変化させるかを指定する。特定の値を指定するには、[値]ボックスに値を入力する
［変化させるセル］	変化させるセルを指定する。ここで指定したセルの値は、問題の制約条件が満たされ、[目的セル]欄で指定したセルが目標値に到達するまで変化する。また、変化させるセルは、目的セルに直接的または間接的に関連付けられている必要がある。［自動］ボタンをクリックすると、Excelによって変化させるセルの類推が実行され、セルの参照が自動入力される
［制約条件］	逆算シミュレーションに当たっての制約条件式を入力する
［オプション］ボタン	[ソルバー：オプション設定]ダイアログを表示する(詳細は後述)
［リセット］ボタン	設定した内容をすべてクリアする
［実行］ボタン	設定した内容に基づいて、逆算シミュレーションを開始する
ソルバーのパラメータの詳細

　本サンプルにおいてソルバーを利用して逆算する目的は、最適な人員配置および最適な人件費の判断にあるため、の［目的セル］には「$D$20（人件費/週）」を指定する。また、［目標値］には「最小値」を選択する。の［変化させるセル］には「$D$5:$D$11（各シフトのアルバイト人数）」を指定する。

　制約条件としては、本サンプルでは次の3つを指定する。

• 「$D$5:$D$11（各シフトのアルバイト人数）」は整数であること。
• 「$D$5:$D$11（各シフトのアルバイト人数）」は負数ではないこと。
• 「$E$17:$K$17（シミュレーション結果の人数）」が「$E$16:$K$16（必要人数）」を下回らないこと。

　制約条件を指定するには、［制約条件］欄の［追加］ボタンをクリックして［制約条件の追加］ダイアログを表示させる。このダイアログでは画面上で数式を1つずつ登録していく。「［左辺］−［演算子］−［右辺］」という並びで指定するので、違和感なく操作することができるだろう。以下は、最初の制約条件を入力しているところである。

［制約条件の追加］ダイアログの例

このダイアログを使って制約条件を入力する。これは最初の制約条件を入力しているところ。

対象となるセル。 　 演算子の指定。「≦」「＝」「≧」「区間」「データ」が選択できる。 　 制約条件となる数値、もしくは［整数］（「区間」の場合）、［バイナリ］（「データ」の場合）という文字列を入力する。

　［制約条件の追加］ダイアログで直感的に分かりづらいのは、演算子の選択肢として用意されている「区間」と「データ」だ。「区間」は、整数の制約条件を設定したい場合に利用する。「区間」を選択すると自動的に［制約条件］欄（右辺）には「整数」という値が入力される。「データ」は、制約条件としてバイナリを指定したい場合に利用する。この場合は、「データ」を選択すると自動的に［制約条件］欄（右辺）には「バイナリ」という値が入力される。さらに演算子として「区間」または「データ」を選択した場合は、［OK］ボタンをクリックして制約条件の追加を確定しようとすると「入力した参照が正しくないか、または必要な参照が入力されていません。」というエラーメッセージが必ず表示される。この場合、エラーメッセージのダイアログに対して「OK」ボタンをクリックすることで実際には制約条件として正常に追加される。

　［ソルバー：パラメータ設定］ダイアログにある［オプション］ボタンをクリックすると、逆算処理を行うに当たっての詳細なオプション設定を行うことができる。ここから計算処理の制限時間設定や反復計算の回数、最適値との許容誤差などを指定することができる。ただし、本TIPSで紹介している程度の逆算であれば、あまり複雑ではないので、特にオプション設定を変更しなくてもすぐに結果が得られる。本稿ではこのオプション設定に関する詳細は省略するが、［ソルバー：オプション設定］ダイアログの［ヘルプ］ボタンをクリックすると詳細な説明が確認できるので必要に応じて参照していただきたい。

［ソルバー：オプション設定］ダイアログ

ソルバーが結果を逆算する場合の計算アルゴリズムなどのパラメータを指定する。また複雑な計算の場合は、計算を途中で打ち切らせるために、時間制限や回数制限を行わせることもできる。詳細については［ヘルプ］を参照のこと。

　一通りのパラメータ設定やオプション設定が完了したら、［ソルバー：パラメータ設定］ダイアログで［実行］ボタンをクリックすると、実際の計算が開始される。処理が終了すると、次のような［ソルバー：探索結果］ダイアログが表示される。

「ソルバー：探索結果」ダイアログ

計算結果が見つかるとこのようなダイアログが表示される。結果をどこに記録するかなどを指定することができる。

結果をセルに書き戻すかどうかの指定。 　 結果のレポートを新しいシートに記入させるには、これらの項目を選択してから、［OK］をクリックする。

　探索結果を踏まえて内容をセルに反映する場合は「解を記入する」を、記入せずに元の状態に戻す場合は「元の値に戻す」を選択してから［OK］をクリックする。

　また、今回の計算に関するレポートが必要な場合は、［レポート］欄から必要なものを選択する。レポートには「解答」、「感度」、「条件」の3種類があり、複数選択することが可能である。それぞれの詳細についてはヘルプを参照していただきたい。ただし制約条件として「整数」が指定されている場合は「感度」と「条件」のレポートは作成する意味がないため、作成できない。［レポート］欄から必要なものを選択して［OK］ボタンをクリックすることにより、それぞれのレポートがワークシートとして自動的に生成される。

「解答レポート」の例

これは［解答］を選択した場合に作成されるレポート。それぞれの制約条件が満たされているかどうかなどの結果が表示されている。

　「解答レポート」の場合、「目的セル」、「変化させるセル」、「制約条件」という3種類の内容が1つのシート上に自動出力される。「目的セル」と「変化させるセル」については「ソルバー」の計算前と後でどのように値が変化したかが出力される。一方の「制約条件」は、設定した制約条件に応じて個々のセルの値がどのように変化し、また条件との差がどうなるのかが出力される。例えば、「制約条件」のセル「$F$17」の所では（上の結果画面の行25を参照）、月曜日のアルバイト人数として、実際に必要としている人数「24人」に対してシミュレーション結果が「26人」となっており、2人分の差異が出たことを確認することができる。

　本サンプルの場合は、ゴールシークおよびソルバーの利用によって、必要な総アルバイト人数は延べ50人、一週間当たりにかかる人件費は\360,000という結果が得られている。結果の値は最初のシート上のセルに反映されているので、実際にサンプル・ファイルをダウンロードして確認していただきたい。

「Windows TIPS」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）