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

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

[薬師寺国安，PROJECT KySS]

連載目次

※本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）。