Excel VBA(マクロ)でゴールシークを複数のセルに対して実行するには?

スポンサードリンク

Excelが持つ便利な機能に、ゴールシークというものがあります。

これは、数式が書かれたセルの演算結果が、目標として設定する値になるように、計算に使った値を変更してくれるもの。答えが目標値になるように、逆算してくれる機能といってもいいかもしれませんね。

しかし、ゴールシークをかけたいセルが複数ある場合は、設定が面倒。10箇所を対象にゴールシークを使うという場合でも、ちょっとうんざりしてしまいます。

今回はExcelが持つ便利なゴールシーク機能を、憂鬱な気持ちにならずに、何箇所にでも使えるようにしてしまう方法を紹介します。

さて、今回の問題にどう対処するか？

実はこの問題、Excelのマクロを使うと簡単に処理できるんですよ。

ゴールシークを効率よく使いたいあなた！

是非この方法を参考にしてみてくださいね。
このページの中では、今回紹介するゴールシークを複数のセルに対して実行するマクロを組み込んだExcelファイルをダウンロードできる
ようにしています。

これを使えば、一からExcelを作り込む必要もありませんよ。ファイルを手にした瞬間、作業スピードは一気に早まります！是非使ってみてくださいね。

では本編スタートです！

スポンサードリンク

Contents

1 『ゴールシークを複数のセルに対して実行』には？まずは問題を整理しよう！
2 『ゴールシークを複数のセルに対して実行する』方針をチェック！
3 『ゴールシークを複数のセルに対して実行する』作業をExcelで自動化！
4 まとめ
5 『ゴールシークを複数のセルに対して実行する』Excelサンプルをプレゼント
- 5.1 goalseek.xlsm
- 5.2 goalseek.bas

『ゴールシークを複数のセルに対して実行』には？まずは問題を整理しよう！

今回解決するのはゴールシークを複数のセルに対して実行するという大変な作業をExcelにまかせて手軽に自動で終わらせたいという課題。

この作業を手作業で行うには、

1.「データ」メニューの中の「What-If分析」を選択
2.表示されるメニューから「ゴールシーク」を選択

3.さらにセルや目標値の設定を行い、ゴールシークを実行

4.この動作をゴールシークを実行したい全てのセルに対して実施

という手順をとるのが普通なのではないでしょうか。1.、2.の作業も何度も行うとなると面倒ですし、なにより3．の作業が大変。いくつも設定しているうちに間違いも起こしそうですよね。

では、この手順をExcelで自動化してしまいましょう！

『ゴールシークを複数のセルに対して実行する』方針をチェック！

ここでは、ゴールシークを複数のセルに対して実行するという作業をExcelに実施させるためにはどうすればよいかをみていきます。
一つひとつ手作業で行うには、上で書いた

この作業を手作業で行うには、

「データ」メニューの中の「What-If分析」を選択
表示されるメニューから「ゴールシーク」を選択
さらにセルや目標値の設定を行い、ゴールシークを実行
この動作をゴールシークを実行したい全てのセルに対して実施

という手作業が必要でしたよね？

今回はこの手順をExcelのマクロ（VBA）をつかって自動で行っていくことにしましょう。仮にゴールシークの対象は次のようなものとしましょうね。

2つの種類の異なったデータがあり、一方にある値（倍率）をかけたものと、もう一方のデータの差分をとります。ゴールシーク実行前の差分は右のグラフに表示された通りです。

ここから、各差分が0になるように、倍率をゴールシークで調整していきますよ！Excelマクロでで次の手順の処理を行わせます。

1.ゴールシークを実行する回数をInputBoxを使って取得

2.ゴールシークの目標値をInputBoxを使って取得

3.ゴールシークを指定の回数だけ実行

これをVBAで書けば今回のゴールシークを複数のセルに対して実行するという作業は一発で一瞬で完了！

しかも処理はプログラムした内容に従って、いつでも何度でも実施できるので、処理を間違ってしまうということもありません！

スポンサードリンク

『ゴールシークを複数のセルに対して実行する』作業をExcelで自動化！

ではゴールシークを複数のセルに対して実行するという課題を解決する、具体的なマクロの記述をみていきましょうね！VBAで書いたリストは次の通りです！

'***********************************************************
'**　仕事サクサク！定時退社のために　EXCEL GO! GO!
'**　(URL:https://excelgogo.net/)
'**　是非ご活用ください♪
'***********************************************************
Sub ゴールシークを複数回実行()
Attribute ゴールシークを複数回実行.VB_ProcData.VB_Invoke_Func = " \n14"
Dim i As Integer
Dim Target As Single
'
On Error GoTo errhandler
'
Numofdata = InputBox _
("ゴールシークを実行する回数を入力", "入力（1/2）")
If Numofdata <> Int(Numofdata) Then
    MsgBox "整数を入力してください"
    Exit Sub
End If
Target = InputBox _
("ターゲットとする数字を入力", "入力（2/2）")
'
If IsNumeric(Target) Then
    For i = 1 To Numofdata
        Cells(3 + (i - 1), 6).GoalSeek Goal:=Target, _
        ChangingCell:=Cells(3 + (i - 1), 4)
    Next
    Exit Sub
Else
    MsgBox "ターゲットには数字を入力してください"
End If

errhandler:
    MsgBox "正しく入力してください"
End Sub

大事なゴールシークを複数回実行する部分は、

Cells(3 + (i - 1), 6).GoalSeek Goal:=Target, _ ChangingCell:=Cells(3 + (i - 1), 4)

の一文。シンプルでしょ？Cells( , )を使ってiを変えながら複数回ゴールシークを実行します。

ここで、Numofdataはゴールシークを繰り返す回数。Targetはゴールシーク実行後に対象のセルがとってほしい値（目標値）です。ゴールシークを繰り返す回数は整数であってほしいので、If Numofdata <> Int(Numofdata) でInputBoxから入力されたものが整数かどうかを確認し、整数でなければメッセージを表示して入力内容を見直すようアラームを出します。

同様に、Targetは、文字であっては困りますので、If IsNumeric(Target) ThenでインプットされたTargetが数字かどうかを判断し、これが数字であればゴールシークを指定回数実行。もし数字でなければ、メッセージを表示して入力を見直すようアラームを出します。

まとめ

今回は、『ゴールシークを複数のセルに対して実行する』という問題をExcelのマクロをつかって自動化し、作業を効率化する方法を紹介しました。

・「データ」メニューの中の「What-If分析」を選択
・表示されるメニューから「ゴールシーク」を選択
・さらにセルや目標値の設定を行い、ゴールシークを実行
・この動作をゴールシークを実行したい全てのセルに対して実施

という地味で辛い作業を、

・ゴールシークを実行する回数をInputBoxを使って取得
・ゴールシークの目標値をInputBoxを使って取得
・ゴールシークを指定の回数だけ実行

という手順で操作を行うマクロにするところがポイントです。

上で紹介したExcelファイルも是非お使いくださいね。プログラムを書く時間も短縮できると思います。

ある機能をExcelで実現するとき、その方法は一つだけとは限りません。

ExcelVBAで使用できる機能をフルに使ってシンプルに作ることもできます。ただこれにはそれなりの勉強が必要。すぐには使えず、今すぐ問題を解決したいとい状況にはあいません。ですので、このブログで紹介するマクロは、できるだけ簡単なもの、VBAにそう詳しくなくても読めそうなもの、手を加えやすいものになるように心がけています。

マクロって難しそう、と敬遠していた方にマクロって意外とお手軽ね！と感じていただき、これを応用すればあの作業も自動化できるかも！と次のステップやアクションにつなげていただければと思います。

Excelのスキルを一気に向上させて、ライバルに差をつけたい、仕事を一気にさばいてスピードアップしたい、スキルを身に着けて転職や就職に役立てたいと思ったあなた！ぜひ下のボタンをクリックしてください！
募集締め切りが迫っているので期間限定で「短期間で実力の向上が見込めるお手頃な方法」を紹介していますよ。

ホントは管理人Goも早く知りたかった！オンラインブートキャンプで、
見やすいデータをより早く仕上げる Excel術をマスターしよう！