当ブログ「エビワークス」では、過去にエクセルソルバーの使用方法に関連する記事を多数公開してきました。
基本的な使用方法をはじめとして、VBAでの応用方法やエラー時の対応、Mac版での使用方法などについても詳細に説明してあります。
エクセルソルバーを使ってみたいけど使い方がわからない、あるいはエラーが起こって困っているといった方の助けになれればと思います。
エクセルソルバーとは
エクセルソルバーとは、Microsoft Office Excelにデフォルトで用意されているアドイン機能であり、設定した条件に合致する解を自動で探索し問題を解決するツールです。
例えば、関数の最小値を探索したり、最小二乗法でのフィッティングを行なったり、はたまた組み合わせ問題を解決したりと、単純なエクセルシート内での演算だけでは解決できないような問題を取り扱うことが出来ます。
それではまず基本的な使い方から始めていきましょう。
基本的な使い方
上図の通りエクセルを開いて、
①「データ」→「ソルバー」から、エクセルソルバーを使用することが出来ます。
※はじめてソルバーを使用する場合には、「ファイル」→「オプション」からアドインの有効化を実施する必要があります。
（これをしなければ「データ」のタブにソルバーが現れません。）
詳細は、エクセルソルバーアドインの出し方【Excel2010-2016対応】に説明がありますので、わからない方は確認してみてください。
②ソルバーを開くと、下図の様な設定画面が現れます。
基本的には赤枠で囲った、目的セル・目標値・変数セルの変更の三箇所の条件を指定して使います。
それぞれに指定する条件は以下の通りです。
- 目的セル：変数セルを使用した数式が記述されたセルを指定する
- 目標値：目的セルの着地点を定める
- 変数セルの変更：変数セルを指定する
目標値へ目的セルの値がなるべく近づくように変数セルの値を探索して、答えを探すというのが、ソルバーの機能になります。
③条件を入力して、「解決」ボタンを押して、条件に合致した解が見つかれば、
下の様な結果画面が表示されます。
ここで「OK」を押すと、エクセルシートの変数セルの値が、ソルバーで見つけた解に修正された状態で保持されます。
抽象的な説明だとどうしてもわかりづらいと思いますので、以下にソルバーの使用をいくつかご紹介します。
使用例
年間の読書計画を立てる
まずは簡単な例からです。
「年間100冊の本を読む」という目標を立てる場合、1日何時間の読書時間を確保すれば良いか？
という問題を考えます。
その場合、例として以下の手順でこの問題を解決することが出来ます。
①エクセルシートを開いて、下図の様に、自分の読書スピード・1日の読書時間・期間・1冊の想定ページ数・冊数の入力欄を設けます。
②各セルへ以下の様に値もしくは数式を入力します。
- 自分の読書スピード：自分の読書スピード(ページ/時間)を入力。
- 1日の読書時間：初期値として１(時間)を入れておく。あとでソルバーが最適化する。
- 期間：365(日)を入力。
- 1冊の想定ページ数：1冊の想定ページ数を入力。（普通の小説ならだいたい300ページ程）
- 冊数：自分の読書スピード×1日の読書時間×期間÷冊数となる様に数式を入力する。あとでここが100になる様にソルバーが最適化する。
③下図の様に、目標セルに「冊数」、目標値に「指定値」を選択し「100」を入力、変数セルに「1日の読書時間」を指定して、「解決」ボタンを押す。
すると、変数セルの1日の読書時間が最適化されて、
1日あたり1.37時間(約82分)読書をすれば、年間100冊の本を読めるということがわかりました。
実はこの程度の簡単な問題であれば、ソルバーを使うまでもなく、
エクセルに標準装備されている「ゴールシーク」という機能を使うだけでも解決できます。
「ゴールシーク」はソルバーの簡易版機能のようなツールですが、詳細は以下の記事にまとめてありますので、使用したい方は以下よりどうぞ。
関数の最小値の探索
次に関数の最小値を探すという問題について説明します。
ここでは
\(y=x^2+3x+7\)の\(y\)の最小値何か？
という問題を考えることにします。
①下図の様に、x,yの入力欄を設ける。
②各セルに以下の様に値または数式を入力します。
- x：初期値として0を入力（あとでソルバーが最適化する）
- y：\(x^2+3x+7\)となる様、xを参照して数式を入力（あとでソルバーがxの値を変更して最小化する）
③下図の様に、目標セル：y,目標値：最小値,変数セル：xとなる様に条件を設定する。
④解決ボタンを押す。
すると、xの値が修正されて、x=-1.5のときにyが最小値4.75をとることがわかりました。
こんな問題手計算で解けるからソルバーなんて必要ない！と思われる方もいらっしゃると思いますが、さらに高次の関数や、特殊な関数系では最小値を手計算で出すことが難しい場合もあります。
そういった時に、この方法が役に立ちます。
最小二乗法によるフィッティング
次に最小二乗法によるフィッティング方法についてご紹介します。
例として、
100℃のお湯が冷めていく時間推移を観測したデータを、
\(T=(T_{i}-T_{f})e^{kt}+T_{f}\)
T:お湯の温度
Ti:最初の温度
Tf:収束する温度
k:冷める速さの係数
t:経過時間という数式に当てはめてフィッティングする。
ということを考えます。
①エクセルシートを開いて、下図の様に実験データを入力します。
②fitting用のお湯の温度の欄を設けて、経過時間tを変数とする数式を入力します。
またこの時に、k,Tfも必要になりますので、入力欄を設けて初期値として適当な値を入力しておきます。
③差の二乗の欄を設けて、
{お湯の温度(fitting)-お湯の温度(実測)}の二乗
となる様に数式を入力します。
④差の二乗を全て足し合わせたセル（差の二乗の総和）を設けます。
⑤「データ」→「ソルバー」を開いて、設定画面で下記条件を設定します。
目的セル：差の二乗の総和
目標値：最小値
変数セル：k,Tf
⑥解決ボタンを押します。
すると、お湯の温度(fitting)が最適化されて、実測データを数式に当てはめた場合に最適解を導くことが出来ました。
制約条件の使い方
上記でソルバーの基本的な使い方を説明しましたが、さらに複雑な条件設定を指定して問題を解決することも可能です。
特に、制約条件を工夫することで様々な問題を解く方法について以下の記事で説明していますので参考までに。
ここでは連立方程式の解き方や、組み合わせ問題の解き方などを説明しています。
VBAマクロで動かす方法
エクセルソルバーはアドインツールですが、便利なことにVBA用のライブラリも用意されており、VBAコードを記述することでマクロとしても活用することが出来ます。
これについては下の記事で説明していますのでご参考にしてください。
VBAマクロを作成して、ソルバーを実行するためのボタンを作成するという方法について書いています。
エラー時の対応【付録１】
ソルバーを使用していると、何らかのエラーでソルバーが実行できない、あるいはどうしても解が収束しない、などといったトラブルに遭遇することがあります。
そんな時に確認すべきことについてまとめたものが下の記事になります。
ケース別に３種の確認事項をまとめています。ソルバーに慣れている人でも見落としている確認事項があるかもしれませんので、お困りの際にはご一読してみることをお勧めします。
Macでのエクセルソルバーの使い方【付録２】
ここでは、Macでエクセルを使っている人向けにソルバーの使い方を解説しています。
筆者自身は、会社ではWindows、家ではMacという使い方をしています。
家のMacPCにもエクセルをインストールしていて、ソルバーを使うことも多々ありますので、下の記事ではMacユーザー目線で使い方を解説してみました。
ご存知のとおり、エクセルはMicrosoft社の製品でWindowsメインのアプリケーションになっているため、Macでは互換性が悪い面があります（特にVBA）。
しかしソルバーに関しては、今のところ筆者自身は不便を感じておりません。
ソルバーの探索アルゴリズムについて【付録３】
ソルバーの探索アルゴリズムは、設定画面にて以下の三つの中から選択することが出来ます。
- GRG非線形
- LPシンプレックス
- エボリューショナリー
デフォルトでは「GRG非線形」が選択されていますが、これは非線形の関数向きの探索アルゴリズムです。
なにも考えずにGRG非線形を使ってもあまり問題になることは少ないと思いますが、例えば線形の連立方程式を解く様な場合には「LPシンプレックス」を、微分不可能な特殊な関数を扱う場合には「エボリューショナリー」を選択すると、より良い解を得られるかと思います。
まとめ
今回はエクセルソルバーの使い方について、これまでに公開してきた記事を集約して、まとめ記事として紹介させていただきました。
ソルバーは非常に便利な機能ですが、使い方がわからないという方も多いかと思います。
メーカー勤務やエンジニアなどの理系技術職であればソルバーを使用すべき場面は多数あると思いますので、これを機に使用してみてはいかがでしょうか。
