Excelソルバーを自動化する方法

「Excelソルバーをさらに活用する方法」では、モデルを強化して分析を強化するための手法について説明しました。 私が最も多くの質問を受けたトピックの1つは、ソルバーを自動化して複数の値を解決することです。

前回の記事で簡単な説明がありましたが、最初から最後までそれを実装する方法に専念するものは何もありませんでした。 この記事では、概念と方法を段階的に説明しながら、最初から最後まで詳しく説明します。 最終的には、独自の個人モデルを自動化し、分析をスケーリングできるようになります。

どうやってやるの？

Excelのマクロレコーダーを利用して、実際のコードのほとんどを処理します。 マクロ記録ツールを使用すると、Excelで手動の手順をVBAコードに変換できます。

次に、変更する重要なコードを特定します。 次に、これらの値を動的に選択して置換し、ソルバーを再度実行するループを作成します。

これは半技術的ですが、このスタイルの開発を通じて、コードの記述方法に焦点を合わせるのではなく、プロセスと概念に焦点を合わせることができます。

マクロレコーダの設定

マクロレコード機能は、リボンの[開発者]タブにあります。 これはデフォルトでは使用できませんが、実装は簡単です。 リボンオプションに移動すると、[開発者]タブのチェックボックスが表示されます。

以下のスクリーンショットはMac用ですが、PCベースのExcelも同様です。

ソルバーアドオンをインストールしていない場合は、[アドイン]メニューからインストールできます。 Microsoftサポートサイトには、すべてのプラットフォームの手順があります。Excelにソルバーアドインをロードします。

マクロの記録

セットアップが完了したので、記録する準備が整いました。 すべての数式と参照を使用して、ワークスペースを適切に設定します。 ソルバーモデルを構築する前に、すべてをポイントに設定する必要があります。

リボンの[開発者]タブに戻ります。 赤い点が付いた一枚の紙が表示されます。 そのボタンをクリックしてレコーダーを開きます。

マクロに名前を付け、必要に応じて説明を入力します。

[OK]をクリックすると、レコーダーが起動します。

ソルバーを設定する手順を実行して、それを解決します。 これが完了したら、[開発者]タブに戻り、記録を停止します。 録音を開始するために使用したボタンと同じ場所になります。

これでベースコードができたので、それをクリーンアップして次のステップの準備をすることができます。

コードをクリーンアップします

これで、コードを編集し、不要な部分を削除して、マクロ記録の重要な部分のみを使用していることを確認できます。

ExcelのVBAエディターを使用してコードを編集できます。 記録されたコードにアクセスするには、[マクロ]ボタンをクリックします。

新しいメニューでマクロに移動し、[編集]をクリックします。

コードエディタを開いたので、微調整を開始できます。

不要なコードの削除

録音中に他の多くのものをクリックした場合は、今すぐそれらをクリアして、以下のようなものを残すことができます。 余分なコードがたくさんあり、それを壊すのが心配な場合は、いつでも再録音できます。

ソルバー参照の設定

先に進む前の最後のステップとして、[ツール]> [参照]に移動し、[ソルバー]を選択します。 これを行わないと、パッケージがマクロに読み込まれず、エラーが発生します。

これで、パラメータを変更してマクロメニューを開き、マクロを選択して実行を押すと、結果が更新されます。

ソルバーモデルに対してコードを並べてみましょう。 あなたはそれがどのように並んでいるかを見るでしょう。 言語は異なりますが、それが何を意味するのかをつなぎ合わせることができます。

次に、変更が必要な部分を分離して、自動化をまとめます。

すすぎ、繰り返し–ループを構築する

これで、ソルバーが機能するようになりました。 それを複数回繰り返す必要があります。 例をまとめて計画を固めましょう。

最初のモデルでは1,000ドルの予算から始めましたが、同じことを100ドルから2,500ドルまで増やしたいと考えています。

飛び込みましょう！ 列Bに値のリストがあり、列Cに出力があります。プロセスを書き出すと、次のようになります。

1. 総支出をB10の値に設定します。
2. モデルを解きます。
3. 出力をC10に配置します。
4. 次のB値を選択します。
5. C行に出力を設定します。
6. Bのすべての値について、手順2〜5を繰り返します。

ループの開始

ループは、論理的なエンドポイントに到達するまでアクションを繰り返すコーディング構造です。 ここでは、値の最後に到達するまで、すべての値に対して同じプロセスを繰り返し、ソルバーを実行するためのループが役立ちます。

この例では、行数でエンドポイントを定義します。 B10：B25には16個の値があります。

ループを開始し、ソルバーコードにラップします。 ループ番号（i）を保持する変数を設定し、各値を調べて、毎回ループを解きます。

ソルバーのリセット

コードの先頭にさらに条件を追加します。 反復ごとにソルバーを再実行する必要があるため、ループごとにソルバーをリセットします。 これにより、設定がクリアされ、新しいモデルが開始されます。

SolverResetを介してこれを行うことができます。

セル参照の更新

これで16回解決しましたが、セル参照を更新する必要があります。 最初はB10を参照していましたが、制限を更新するには、実行ごとに1セル下に移動する必要があります。 これは、オフセット関数を介して行うことができます。 「$ B $ 10」を更新してセルを参照し、i行（最初の実行で0、2回目の実行で1行というように）オフセットすることができます。 ループが実行されるたびに、iは1ずつ増加します。

出力の収集

出力は毎回変わるので、保存したいと思います。 上記と同じプロセスに従うことができますが、C10からオフセットし、解決されたモデルの値と等しくなるように設定します。 次に、ループが最初からやり直します。

C10を呼び出して、適切な行数でオフセットしてから、出力の値を貼り付けます。

ソルバー通知の回避

少しの間、例を挙げて学ぶのではなく、頭痛の種を避けましょう。 ソルバーを実行したときに、ソリューションを受け入れるかどうかを尋ねるメニューがポップアップ表示されたことを覚えていますか？ 無効にしないと、毎回ポップアップします。 数百の値でループを設定した場合、毎回[同意する]をクリックする必要があると想像してください。

先に進み、ソルバーコードの後に​​次の行を追加し、SolverSolveに（TRUE）を追加します。 これは、変更を受け入れることを模倣します。

これで完成したコードができました！

稼働中

これで、マクロリストからコードを選択して実行することにより、コードを実行できます。 ブックをテンプレートとして使用する場合は、使いやすいようにマクロをボタンに割り当てることができます。 これらは[開発者]タブにあります。

比較グラフを追加することもできます。 この場合、支出の増加率とコンバージョン量の増加率を比較します。 この例は興味深いものではありませんが、実際のデータは効率のブレークポイントを明らかにするのに役立ちます

結論

かなりカバーしました！ 自動ソルバーを起動して実行しました。おめでとうございます。 ここで取り上げた例は非常に単純ですが、同じ概念を使用して、任意のソルバーモデルに適用できます。

これにより、複数回解決する必要のあるモデルの時間を大幅に節約し、ユーザーにさまざまなシナリオを試すように促すことができます。

元の公開日：2019年7月30日