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

スポンサードリンク

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

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

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

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

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

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

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

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

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

では本編スタートです!

スポンサードリンク

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

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

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

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

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

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

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

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

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

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

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

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

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

今回はこの手順を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術をマスターしよう!

 

『ゴールシークを複数のセルに対して実行する』Excelサンプルをプレゼント

今回紹介した

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

という処理を行うマクロを実装して『ゴールシークを複数のセルに対して実行する』という機能を持たせたExcelファイル、プログラムを記載したbasファイルが下からダウンロードできます。

お好きな形式のファイルをダウンロードして、実際に動かしてみたり、あなたの課題にあわせてExcelを調整して作業時間短縮、効率化に役立ててみてください!

basファイルって何?どう使うの?という方やマクロはどのようにして実行するの?と疑問に思った方は、関連記事もチェックしてみてくださいね。


 

この記事が気に入ったら
いいね!しよう

最新情報をお届けします

Twitter でExcel GO! GO!をフォローしよう!

スポンサードリンク

関連記事

コメント

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)

話題をチェック!

  1. 2018-11-1

    Excelマクロ(VBA)で複数のシート上のグラフに同一の処理を行うには?

    Excelファイル内に複数のシートがあり、この中にグラフがいくつも入っているとします。 これら…
  2. 2018-9-30

    Excelの関数を使ったデータ比較法!INDIRECTとMATCHを活用する!

    最新データと一週間前のデータにどんな違いがあるのか比較してチェックしたい! でも、ひとつひとつ…
  3. 2018-7-16

    Excel マクロ(VBA)でグラフ(散布図)を大量自動作成!

    Excelはグラフを簡単につくれるのも魅力ですが、つくらなければいけないグラフが多いと、この作業も大…
ページ上部へ戻る