【Excel VBA】セルに数式を入力/取得する(Formula/FormulaR1C1/Formula2プロパティ)｜Excel VBA

Formulaプロパティの構文です。

セル範囲.Formula = "数式"

"数式"は、Excelで入力する計算式を文字列として記述します。必ず「=」から始める必要があります。
「Formula =」の後に、さらに「=」から数式を始める点に注意しましょう。また、数式はダブルクォーテーション（" "）で囲みます。

例1：Formulaプロパティを使って、D2セルに、B2セルからB5セルの合計値を計算します。

Sub Formula1()
	Range("D2").Formula = "=SUM(B2:B5)"
End sub

マクロ実行前

マクロ実行後

文字も数式も入力されていなかったD2セルに、「=SUM(B2:B5)」が入力されて計算が実行されました。

Excelは、「A1」や「C5」のように列をアルファベット、行を数字で表す形式が一般的です。この形式を「A1形式」といいます。
A1形式で絶対参照にする場合は列・行に「$」をつけます。例えば、「$A$1」は列・行とも絶対参照、$A1は列のみ絶対参照です。
一方、行（Row）をRと行番号、列（Column）をCと列番号で表す形式を「R1C1形式」といいます。
例えば、「R1C2」はB1セル、「R3C4」はD3セルを指します。
相対参照にする場合は、基準セルからの位置をR[数値]、C[数値]のように指定します。基準セルから見て、右と下は正の数、左と上は負の数です。例えば、R[3]C[-2]は「基準セルから下に3行、左に2列」を意味します。この考え方はOffsetプロパティで相対位置を指定する方法と似ています。

※Offsetプロパティについての詳細は、「【Excel VBA】これだけはおさえるセル操作（1）−Offsetで自由自在」を参照してください。

FormulaR1C1プロパティを使うと、R1C1形式でセルに数式を入れたり、その数式を読み取ったりできます。この方法は、プログラムの中で基準セルから見て「下に3行、左に2列」などの位置を指定して、相対参照を扱うときに特に便利です。

FormulaR1C1プロパティの構文です。

セル範囲.FormulaR1C1 = "数式"

例2：例1と同じシートで、FormulaR1C1プロパティを使って、D2セルに、B2セルからB5セルの合計値を計算します。

Sub Formula2()
	Range("D2").FormulaR1C1 = "=SUM(RC[-2]:R[3]C[-2])"
End sub

まず、アクティブセルはD2セルにあります。D2セルからみて、B2セルとB5セルはどこにあるでしょうか。
B2セル：D2セルから左に2列の位置
B5セル：D2セルから下に3行、左に2列の位置

FormulaR1C1形式を説明しましたが、固定されたセルに単純な数式を入れるだけなら、Formulaプロパティで十分です。FormulaR1C1プロパティが本領を発揮するのは、数式を相対参照で設定したいときです。
たとえば、データ範囲が変わる場合や、基準セルから見て「下に3行、左に2列」などの位置を参照する場合には、FormulaR1C1を使うとコードがわかりやすくなります。

例3： 1行目が見出しになっている表があります。B列の最終行の下に「B列の合計」を入れます。なお、行数は都度変わります。

Sub Formula3()
    Dim lastRow As Long		'最終行を格納する変数lastRow

    lastRow = Cells(Rows.Count,"B").End(xlUp).Row	'最終行を取得
    Range("B" & lastRow + 1).FormulaR1C1 = "=SUM(R[-" & lastRow - 1 & "]C:R[-1]C)"
End Sub

いま選択しているアクティブセルに数式を入れることもできます。「ActiveCell.FormulaR1C1」を使って、アクティブセルに数式を設定します。

例4：アクティブセルの行で、左3列から左1列までの平均を表示します。

Sub Formula4()
    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-3]:RC[-1])"
End Sub

アクティブセルがF2セルの場合、F2セルから左3つのC2セルから、F2セルから左1つのE2セルまでの範囲を計算します。

Excelの「マクロの記録」を使うと、セルに数字や文字を入力しただけなのに、記録されたコードに「ActiveCell.FormulaR1C1」が出てきます。これは、マクロの記録機能が「セルへの入力」をすべてFormulaR1C1プロパティを使って記録するからです。
たとえば「あ」のような文字を入力しただけでも、「マクロの記録」では「セルに数式（または値）を設定した」とみなして 次のように書きます。数式を入れる場合も、値を入れる場合も同じプロパティ（FormulaR1C1）で設定する仕組みです。

ActiveCell.FormulaR1C1 = "あ"

このコードの意味は、「いま選んでいるセルに、この内容を設定する」ということです。同じ内容を手動で書く場合は、次のようにもっとシンプルに書くことができます。

Range("A1").Value = "あ"

Formula2 プロパティは、Excel の Range オブジェクトに対して、動的配列関数（スピル機能）などの新しい数式機能に対応した数式を設定・取得するためのプロパティです。FormulaR1C1形式も利用できますが、A1形式が見やすいのでおすすめです。

Formula2プロパティの構文です。

セル範囲.Formula2 = "数式"

Formulaプロパティでは、「スピル（動的配列）」をうまく扱えないことがありますが、Formula2ならスピルを含む数式をそのまま設定できます。新しい関数（UNIQUE、SORT、SEQUENCEなど）やスピルを使う場合はFormula2プロパティを使うと効果的です。
スピルについては、「【Excel】スピルとは？基本的な使い方と関数との組み合わせ」を参照してください。

例5：生徒のテストの点数一覧があります。右の表に、「国語の点数が高い順」にSORT関数（Excel 2021/2024/365 で使用可能）を使って並び替えます。生徒の人数は変動するため、表の行数も都度変わります。

Sub Formula5()
    Dim lastRow As Long	'最終行を格納する変数lastRow

    lastRow = Cells(Rows.Count,"A").End(xlUp).Row	'最終行を取得
	
    If lastRow < 2 Then Exit Sub
	
    Range("G2").Formula2 = "=SORT(A2:D" & lastRow & ",2,-1,FALSE)"
End Sub