Excelのセルに数式を代入し数式だけを参照/コピーするにはVBA/マクロ便利Tips

業務効率化に役立つVBA/マクロのさまざまなTipsをコード例を交えて紹介していきます。今回は、セルに数式を手作業で入れるときの問題を解決するために、セルに数式を代入するマクロと数式を取得して別のセルにコピーするマクロについて。

» 2014年06月11日 18時00分 公開
[薬師寺国安PROJECT KySS]
「VBA/マクロ便利Tips」のインデックス

連載目次

※本Tipsの環境:Windows 8.1 Enterprise(64ビット)+Excel 2013


セルに数式を手作業で入れるときの問題

 今回は、セルに数式を入れているときに役立つTipsの紹介だ。

 例えば、入力された値の合計を求める場合に、セルに数式を入れる必要に迫られる場面は多いと思う。もちろん、Excelのメニューからでも計算式は入力できるが、いくつものシートでデータを管理している場合などでは、各シートごとに、いちいち手作業で数式を入力していたのでは、手間が掛かることこの上ない。

 VBAを使えば数行のマクロを記述するだけで、任意のセルに「数式を代入」したり、「同じ数式を別のセルに代入」したりすることも可能だ。

セルに数式を代入する

 例えば、図1のようなデータがあったとして、C15に合計を求める数式を代入してみよう。

図1 サンプルの月別売上表

 まず、VBEのメニューから、[挿入]→[標準モジュール]と選択する。プロジェクトにModule1が追加されるので、Module1をダブルクリックして、表示されるエディター画面内にコードを記述する。

 「数式を代入」というプロシージャを作成し、その中に以下の4行目を記述する。

Option Explicit
Sub 数式を代入()
 Range("C15").Formula = "=SUM(C3:C14)"
End Sub
リスト1 合計を求めるコード

 Range("C15")は合計を表示させるセルだ。Formulaプロパティに"=SUM(C3:C14)"と指定して、「C3〜C14」までの合計を計算させる「SUM関数」を代入する。代入するには関数の前に必ず「=」を追加する必要がある。

 平均を求める場合には下記のように記述する。

Range("C15").Formula = "=AVERAGE(C3:C14)"

 VBEメニューの[実行]→[Sub/ユーザーフォームの実行]と選択して実行すると図2のようにC15の位置に数式が代入され合計が表示される。

図2 合計が表示された

 次に、セル内に代入されている数式を取得するには、どうすればいいのだろう。同じ数式を別のセルにコピーする必要に迫られる場面は、日常の業務にもあると思う。その数式を取得して別のセルにコピーする方法を解説しよう。

数式を取得して別のセルにコピーする

 書式は、先のサンプルと同じくFomulaプロパティを使用する。

書式

{オブジェクト1}={オブジェクト2}.Formula


 {オブジェクト1}には対象となるRangeオブジェクトを指定する。

 例えば図3のような表があった場合、G3のセルにC15のセルに代入していた数式を代入するにはリスト2のコードを記述する。

図3 G3のセルにC15に代入されていた数式を入れるための表
Sub セル内の数式を取得()
  Range("G3") = Range("C15").Formula
End Sub
リスト2 セル内の数式を取得するコード

 G3のセルにFormulaプロパティを使ってC15に代入されていた数式を代入するだけだ。

 実行すると、図4のように表示される。

図4 G3のセルにC15に代入されていた数式が代入された

 図4では、1月から12月の金額が変更されると、当然G3に入力されている金額も変化する。数式を代入しているわけだから当然のことだ。しかし、月の金額は変更されても、合計金額だけは以前の金額のまま残しておきたい場面も、日常の業務には出てくるだろう。これについては、次回のTipsで紹介する。

著者プロフィール

薬師寺 国安(やくしじ くにやす) / 薬師寺国安事務所

薬師寺国安事務所代表。Visual Basicプログラミングと、マイクロソフト系の技術をテーマとした、書籍や記事の執筆を行う。

1950年生まれ。事務系のサラリーマンだった40歳から趣味でプログラミングを始め、1996年より独学でActiveXに取り組む。

1997年に薬師寺聖とコラボレーション・ユニット「PROJECT KySS」を結成。

2003年よりフリーになり、PROJECT KySSの活動に本格的に参加。.NETやRIAに関する書籍や記事を多数執筆する傍ら、受託案件のプログラミングも手掛ける。

Windows Phoneアプリ開発を経て、現在はWindowsストアアプリを多数公開中。

Microsoft MVP for Development Platforms - Client App Dev(Oct 2003-Sep 2012)。

Microsoft MVP for Development Platforms - Windows Phone Development(Oct 2012-Sep 2013)。

Microsoft MVP for Development Platforms - Client Development(Oct 2013-Sep 2014)。


Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。