セルへ数式を設定する方法

セルに数式を設定するには Formula プロパティを使用します。実際に数式を入力するようにイコール”=”につづけて、算術式やワークシート関数を設定します。

レンジで範囲を指定してまとめて数式を設定することも可能ですが、この場合は数式の相対参照絶対参照に注意しましょう。

スポンサーリンク

数式の相対参照・絶対参照を誤った使用例

下図はマクロ実行前の状態です。赤破線にマクロで店舗ごとの合計とその順位を設定します。

sample6_29の実行前
sample6_29の実行前

以下が数式を設定するためのマクロです。数式の設定に誤りがありますが、このまま実行してみます。

'数式(RANK)の設定に誤りがある例
Sub sample6_29()
    '合計
    Range("E4:E13").Formula = "=SUM(B4:D4)"
    '順位
    Range("F4:F13").Formula = "=RANK(E4,E4:E13)"
End Sub

マクロを実行すると、合計と順位が設定されましたが、見るからに順位がおかしいです。何がおかしいか調べるため、セル内の数式を確認してみます。

sample6_29の実行結果
sample6_29の実行結果

まずは、合計欄の先頭と終わりの数式を確認してみます。

E4セルの数式
E4セルの数式

合計欄の数式は相対参照で数式がコピーされていて、特に問題ないようです。

E13セルの数式
E13セルの数式

次に順位欄の数式を確認してみます。

F4セルの数式
F4セルの数式

先頭の数式は問題ありませんでしたが、先頭より後ろの行の数式に問題がありそうです。具体的には、RANK関数の第2引数に注目すると、コピー先にあわせて第2引数の参照先がずれてしまっていることがわかります。

F13セルの数式
F13セルの数式

数式の相対参照・絶対参照使用例(修正後)

前述のマクロを以下のように修正しました。

合計欄におけるRANK関数第2引数を行方向に対しては絶対参照に(行番号の前に$を付与)し、参照先を固定するようにします。

Sub sample6_30()
    '合計
    Range("E4:E13").Formula = "=SUM(B4:D4)"
    '順位(行を絶対参照にする)
    Range("F4:F13").Formula = "=RANK(E4,E$4:E$13)"
End Sub

下図がマクロ実行後です。順位欄が正しく表示されているように見えますが、一応数式を確認してみます。

sample6_30の実行結果
sample6_30の実行結果

順位欄の数式を確認してみると、第2引数の参照先がずれることなくコピーされ、正しく順位が表示されているのがわかります。

E13セルの数式(数式修正後)
E13セルの数式(数式修正後)

数式のR1C1形式

A1形式(上記の数式設定形式)ではなく、R1C1形式で数式を設定する場合は FormulaR1C1 プロパティを使用します。

R1C1形式にもA1形式同様に相対参照、絶対参照があり、カッコ[]でくくると相対参照、カッコでくくらずに行または列番号を指定すると絶対参照となります。

以下のサンプルマクロは前述の sample6_30 をR1C1形式にしたものです。

Sub sample6_31()
    '合計
    Range("E4:E13").FormulaR1C1 = "=SUM(R[0]C[-3]:R[0]C[-1])"
    '順位(行を絶対参照にする)
    Range("F4:F13").FormulaR1C1 = "=RANK(R[0]C[-1],R4C[-1]:R13C[-1])"
End Sub

 セルをクリアする 数式のあるセルを調べる

タイトルとURLをコピーしました