今回はソルバー実行ボタンを「マクロの記録」機能を使って作成する方法についてご説明します。何度も同じソルバー実行を繰り返したい時に便利です。実験データへ理論的な数式をフィッティングさせて係数を求めたい時などに、ソルバーを適用させる場面を想定して解説します。変数セル:係数k1,k2目的セル:実験温度データ(T_true)とフィッティング温度(T_fitting)との差の二乗の和(sumsq)としてソルバーを実行することを考えます。目標としては、図中の赤枠のようにソルバー実行のボタンを設置して、このボタンを押せば係数が最適化されるファイルを作成することです。順を追って説明していきます!手順1マクロの記録まずはマクロの記録機能を使って、ボタンを押した時に実行されるVBAコードを作成しましょう。リボンの「開発」→「マクロの記録」をクリックします。下のような、Boxが表示されますのでマクロ名を入力してOKを押します。その他の欄は入力しなくてOKです。補足:ここでショートカットキーの欄を入力すると、設定したショートカットキーでマクロが実行されるようになります。今回はボタンを押して実行することを目標にしているので使いませんが、自分しか使わないマクロを作る場合などにはとても便利な機能です。すると、マクロの記録モードに入ります。これは手作業で実行したことがVBAコード化されてマクロが作成される状態のことです。今回はソルバー実行のマクロを作りたいので、手作業で一通りソルバーを実行してやってマクロを記録します。このマクロの記録モードの間は、作業の取り消し(Ctrl+Z)が効かない状態になるので間違えないように慎重に作業しましょう。リボンの「データ」→「ソルバー」をクリックします。「データ」に「ソルバー」が表示されていない時:アドインとしてソルバーが機能していない状況になっています。アドイン”にチェックを入れてください。実施したいソルバー条件を入力し、「解決」をクリックします。(解決方法の選択方法については、ソルバー解決方法についてを参照ください。)※今回は、オレンジのセルを目的セルとして目標値を最小値に設定し、ブルーのセルを変数セルとして設定しました。すると、ソルバーが実行されて結果が表示されます。「OK」を押して、結果を閉じてください。画面左下ステータスバーの「■」ボタンをクリックして、マクロの記録モードを終了します。これで、ソルマー実行マクロの記録は完了です。手順2ボタンの作成シートにボタンを設置してマクロを登録します。クリックして、長方形のボタンを選んでください。先ほどマクロの記録で作成した「ソルバー実行」というマクロを選択して登録し、OKを押してください。ボタンを設置したい場所に、ドラッグでボタンを設置し、ボタン中に”ソルバー実行”とテキストを入力してください。これでボタン作成の作業は完了です、お疲れ様でした!きちんとボタンが動作するかどうか確認を実施してみてください。ソルバー実行のマクロを実施した場合には、下のような”SubまたはFunctionが定義されていません。”というエラーメッセージが表示されることがあります。これは、お使いのエクセルにVBA用のソルバーライブラリが設定されていないことが原因の可能性が高いです。この場合、下の手順を試してみてください。1OKをクリックしてエラーメッセージを閉じます。2VBAコードが表示されている上図のウインドウ中の「■」ボタンをクリックして実行中のマクロを停止させます。3「ツール」→「参照設定」を開き、下図のように「Solver」にチェックをいれOKを押します。これで、VBA用のソルバーライブラリの設定が完了です。画面右上のエクセルマークをクリックして元のエクセルシートへ戻って、ソルバー実行ボタンが正常に動作するか確認してみてください。ソルバー実行ボタンの用途について実験や観測の条件を様々に変えて、データを取得して比較をする時などに、このソルバー実行ボタンは有効です。今回の例の場合、液体の冷めるスピードに対してある数式をフィッティングさせて係数を求めました。具体的には今回使用したデータはマグカップに入れたお湯が冷めていくスピードを測定したものなのですが、これを違う液体で実施すると係数の値はどうなるか?違う容器で実施すると係数の値はどうなるか?などといった比較をしたい場合に、フィッティングしたい関数は同じなわけですから実施するソルバーも同じなわけで、ボタンで設定しておけば、新しい実験データを入力してすぐに(だれでも)、数式へのフィッティングができるということになり、とても便利だと思います。今回はマクロの記録機能を使ってマクロのVBAコードを作成しましたが、実際に中身がどうなっているのかをここで見ておきましょう。(エクセルシートの画面中でAlt+F11キーを押すと、マクロの開発画面が開きソースコードを確認することができます。Sub上の通り、今回作成されたVBAコードは非常に単純なものです。SolverOK関数(Solverライブラリの関数)呼びだし、条件を設定します。SetCell:目的セルMaxMinVal:最適化方法の設定(1が最大化、2が最小化、1/3が値と一致)ValueOf:値と一致を選んだ場合の目的値byChange:変数セルEngineとEngineDesc:解決方法の設定そして、SolverSolve関数で、ソルバーを実行します。慣れていれば直接コードを記述してマクロを作成してもよいかと思います。ソルバー解決方法の選択についてソルバーパラメータの設定の際に迷ってしまいがちなのが、ソルバー解決方法の選択です。設定画面ではこの3つのエンジンが選択可能ですが、どれを選べば良いのでしょうか?パラメータ設定の説明欄には、こう書かれています。滑らかな非線形を示すソルバー問題にはGRG非線形エンジン、線形を示すソルバー問題にはLPシンプレックスエンジン、滑らかではない非線形を示すソルバー問題にはエボリューショナリーエンジンを選択してください。いうことなので、今回の例では指数関数の数式を使用しましたので選択すべきは「GRG非線形」となるでしょう。ここは実施したいソルバー問題に応じて選択する必要があり、z=ax+byなどといった線形問題なら「LPシンプレックス」を選択すべきです。私の場合は滑らかな非線形問題を解くことが圧倒的に多く、ほとんど場合においてGRG非線形を使用しています。まとめ今回は「マクロの記録」機能を使うことで、ソルバーの実行ボタンを作成しました。VBAを知らなくても感覚的にマクロを作成→ボタンへの登録を行うことができ、今回の方法は他への応用も幅広いと思います。現職の化学系エンジニアです。理系技術者やエンジニア向けのお役立ち情報を不定期に配信中。2019.09.10今回は、エクセルソルバーアドインでエラーや不具合が発生した場合に確認することをケース別にまとめました。コメントをキャンセルメールアドレスが公開されることはありません。*サイト次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。プロフィールえびかずき現職の化学系エンジニア。理系技術者やエンジニア向けのお役立ち情報を不定期に配信中。WordPress.。