ソルバー 設定 できない

【エクセルのソルバーの使い方】最適解を瞬時に求める、無名の超便利機能

最適解を瞬時に求められるエクセルのソルバー機能を実務でどう使えるかと、それを使う前の準備をする３つのステップのコツをご紹介し、それを使った事例を二つ図解して、今日からこのソルバー機能を使って頂きたいと思います。

（動画時間：10:20）

目次

• エクセルのソルバー機能の概要
• エクセルのソルバー機能を使う準備をする３ステップ
• 誰でもやれるソルバーの有効化の仕方
• エクセルのソルバー機能の設定画面の使い方
• 応用範囲の広いソルバー機能の実務での使い方
  • エクセルのソルバー機能の前のワークシート上の準備
  • エクセルのソルバー機能の設定画面での実行

エクセルのソルバー機能の概要

こんにちは、リーンシグマ、ブラックベルトのマイク根上です。
業務改善コンサルをしています。

前回の動画でエクセルのソルバー機能をご紹介しました。
⇒「非線形の回帰分析を最小二乗法とエクセルのソルバー機能でやる方法【回帰分析シリーズ５】」

その動画では統計の話題で使ったのですが、
普段の仕事でもかなり使えます。
エクセル内の機能なのに一般的には全く知られていないのですが、
実際に会社のポートフォリオをどう組むべきかに利用したり、
生産計画、労務スケジュールの作成で使われていて、
使い方のコツさえ分かれば他にも応用できます。

今日はそのコツと、それを使った事例を二つ実演して、
今日からこのソルバー機能を使って頂きたいと思います。

まず、ソルバーとは何か？、ですが、
「ある条件下でほしい結果が得られるようにする最適解を
自動で計算してくれる機能なのです。」
って言われても良く分かりませんね。簡単なシナリオで見てみましょう。

エクセルのソルバー機能を使う準備をする３ステップ

ある部署で５人の従業員がいて、
一週間のスケジュールを立てようとしています。
一番下に各曜日の必要な人数が書いてあります。
従業員に出勤させる日に下図内の薄黄色のセルに１を書いていき、
一週間のスケジュールを完成させます。

ソルバーの操作画面の前にその準備をする
３つのステップのコツをご紹介します。

ステップ１は「求めたい変数のセルを指定します。」
ここではこの薄黄色のセルですね。

ステップ２が「最適解を求める目的を決定する」です。
最終的にどうしたいのか？、
ここでは「人件費を最小にする」事を目的とします。
その為には各従業員の日給が必要であり（下図内Ｊ列）、
その横（Ｋ列）に各従業員の出勤日数を出す数式を入れています。

これで、SUMPRODUCT関数で人件費の合計を求められます。
それをセルＫ２で出力しています。

セルK2内
=SUMPRODUCT(J5:J9,K5:K9)

このSUMPRODUCT関数は二つの同じ大きさのセル範囲を引数にする事で
その二グループの各値を掛けて、足し合った合計を求められます。
ここでは各従業員の日給と出勤日数を掛けて全て足すので
全体の人件費になるのです。

最後にステップ３で「その他の必要な条件を数式化する」です。
例えば、毎日必要な人数に達しているかを見るために
各曜日の合計人数を出すようにしています。（上図内１０行）

また各従業員に休みを取らせなければいけないので
最後の列に最大出勤日数を書いています。 （上図内Ｌ列）
これらを必要な条件として使うのです。
ここまで準備する事で実際にソルバーを使えます。

誰でもやれるソルバーの有効化の仕方

しかし、ソルバーを初めて使う方は初期設定が必要です。
簡単ですので、次の手順に従ってやってみましょう。

1. メニューバーの「ファイル」
2. （画面左下）「オプション」
3. 次の画面の左ペインで「アドイン」
4. 管理で「Excel アドイン」の状態で「設定」
5. 次の小画面で「ソルバー アドイン」にチェックをつけて「OK」

エクセルのソルバー機能の設定画面の使い方

これでメニューバーの「データ」から
ソルバーを使える様になります。

これがソルバーの作業画面です。
最初が「目的セルの設定」で、
今回は人件費合計を最小にするのが目的なので、
その数式があるセル「K2」を選びます。

次の「目的値」は人件費合計を最小にしたいので、
「最小値」を選びます。
その他に「最大」にする事や
ある値にする「指定値」も設定できるのです。

次の「変数セル」では最適解を求めたい
薄黄色セル（C5:I9）を選びます。

次に「制約条件」を設定しましょう。
「追加」をクリックして、下記の４つの条件を設定します。

最初の条件は「各曜日の出勤人数が必要人数と同じである事」で、
[出勤人数のセル範囲] = [必要人数のセル範囲]で設定します。

「追加」をクリックして、次の条件を設定します。
「各従業員の出勤日数が最大出勤日数以下になる」必要がありますね。
[ 出勤日数 のセル範囲] <= [ 最大出勤日数 のセル範囲]で設定します。

もう一つ条件がありで、「変数セルは０か１である必要がある。」です。
そこで、まず１以下にする条件を追加し、
[ 変数セル範囲] ] <= [1] で設定します。

もう一度同じセルを指定して、
0.5などの「小数を省いた整数にしたい」ので、
「int」を選びます。 [ 変数セル範囲] ] int
これは英語のIntegerの略語で「整数」という意味です。
これで一度「OK」を押します。

次の「制約のない変数を非負数にする」にチェックを入れると
マイナスの数を指定しなくなります。

その下の「解決方法の選択」は既定の「GRG 非線形」のままでいいです。
そして「解決」をクリックして、
「解が見つかりました」と出ると成功です。
そして変数セルに人件費を最小にするスケジュールが作成されています。

この様にソルバーは目的を達成するため、
また色んな制約条件をクリア―しながら
最適解を求めてくれるのです。

さっきの簡単な例でも自分で結果を
確認しながら手作業で最適解を探そうとしたら
相当な時間がかかります。
それをソルバーを使ったら数秒でできてしまうので、すごいですね。
次にもう一つ、実務的な例でやってみましょう。

応用範囲の広いソルバー機能の実務での使い方

エクセルのソルバー機能の前のワークシート上の準備

ある魚屋さんで、ある日の
マグロ、サーモン、ハマチの各注文の合計が
40キロ、49匹、13匹であったとします。（下図内、１０行）

しかし、その日の在庫が、それぞれ、
30キロ、40匹、8匹しかありません。 （下図内、１２行）

在庫が足りないのでこれをどのように割り振って配達をしたらいいのかを
このソルバーを使って考えるのです。
さっきと同じステップで準備をしてみましょう。

最初のステップ１で「求めたい変数のセルを指定します。」
これは各顧客の各商品の配達量のセルです。（下図内、薄黄色セル範囲）

次のステップ２は「最適解を求める目的を決定する」です。
お客によって特別価格が設定されていて、（下図内、I5:K9のセル範囲）
それによりお客によって粗利が変わってきます。
ですので、「粗利の合計が最大になる」のを目的としましょう。

粗利を計算するのに各商品の原価が必要です。
それがI12:K12のセル範囲にあります。（上図内参照）

これらのデータを使ってさっきのSUMPRODUCT関数を二つ使い、
今回配達する売上合計と原価の合計を出して、
その差で粗利合計を出せます。（上図内、セルＫ１）

セルK1内
=SUMPRODUCT(F5:H9,I5:K9)-SUMPRODUCT(F10:H10,I12:K12)

最後のステップ３で「その他の必要な条件を数式化する」で、
各商品の在庫量以上に出荷ができないので、
商品毎の配達量の合計を出す式を用意します。 （上図内、F10:H10のセル範囲）

その他にも条件がありますが、後で確認します。
ここまで準備したらソルバーで粗利を最大化する
配達量を求める事ができます。やってみましょう。

エクセルのソルバー機能の設定画面での実行

「データ」タブからソルバーの画面を出します。
最初の「目的セルの設定」で粗利合計の数式セル（セルＫ１）を選択し、
「目標値」は「最大値」ですね。
「変数セル」は配達量のセル（薄黄色セル）を選びます。

制約条件」を設定しましょう。
「追加」をクリックして、下記の３つの条件を設定します。

まずは、各商品の合計配達量が在庫量と同じでなくてはいけません。
[ 合計配達量] = [在庫量] で設定します。

また、各配達量は各受注量以下になる必要があります。
[ 各配達量] <= [各受注量] で設定します。

次の条件で、配達量は1.5匹とかの小数ではだめなので
整数である条件を設定して、
これで終わりなので「ＯＫ」をクリックします。

今回も配達量はマイナス値にしたくないので
「制約のない変数を非負数にする」にチェックを入れて、
「解決」をクリックします。
「解が見つかりました。」と出てきて、
配達量の結果が出てきました。
これで全ての制約条件を満たして粗利合計が最大になる配達量を
瞬時に求める事ができたのです。

どうですか、この様な結果を自分で計算しようと思ったら
莫大な時間がかかります。

このソルバー機能は全然知られていませんが、
今日ご紹介した手順で直ぐに使えます。

最適な数値配分を出したい時にどうあるべきかと、
制約条件を数値化したらこのソルバー機能で瞬時に最適解を出す事ができるので、
このソルバー機能をぜひ、活用してみて下さい。

「こちらの記事も読まれてます｡」

• 非線形の回帰分析を最小二乗法とエクセルのソルバー機能でやる方法
• 全体最適化と部分最適化の本質的な違いと、どう使い分けるか？