ExcelマクロVBA入門 | 第38回.セルに計算式を設定(Formula) | Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説



最終更新日:2016-12-01

第38回.セルに計算式を設定(Formula)


セルに計算式(関数)を設定する場合です。

マクロでは、セルに計算式を設定することは、そんなに多くないと思いますが、

使わないわけではありません。


しかし、この計算式の設定には、何種類ものプロパティがあり、

結構やっかいなのです。


計算式を設定できるプロパティ

.Value

.Formula

.FormulaLocal

.FormulaR1C1

.FormulaR1C1Local

これら全てを覚える必要はありませんが、一応の知識として紹介しておきます。


.Value

これは、文字・数値をセルに入れる場合に使用してきました。

同様に、計算式も入れる事が出来ます。

A1セル = B1セル + C1セル

Range("A1").Value = "=B1+C1"

これは、普通に計算式を指定すれば良いです。

関数でも同じです。

A1セル = B1〜B10セルの合計

Range("A1").Value = "=SUM(B1:B10)"


.Formula
.FormulaLocal

これは、.Valueとほとんど同じですので、使う必要はないでしょう。


.FormulaR1C1

.FormulaR1C1Local

これが解りづらいかもしれません。

計算式をR1C1参照形式で指定します。


R1C1参照形式

セルの相対位置、つまり、計算式を設定しようとしているセルからの移動量を、

行位置をRに続けて記述し、列位置をCに続けて記述します。

R ・・・ Rの後に何も記述しない場合は同一行

C ・・・ Cの後に何も記述しない場合は同一列

R1 ・・・ Rの後に数値のみ記述した場合はその絶対行数

C1 ・・・ Cの後に数値のみ記述した場合はその絶対列数

R[1]・・・ Rに続けて[数値]とした場合は、数値行数分、行位置をづらした行

C[1]・・・ Cに続けて[数値]とした場合は、数値列数分、列位置をづらした列

これらを組み合わせて、計算式を設定するセルからの位置を指定します。


例.C5セルに計算式を設定する場合


RC1 ・・・ A5セル

R1C ・・・ C1セル

R[1]C[2] ・・・ E6セル

RC[-1] ・・・ B5セル

このようになります。


Localについて

と付くものは、PC環境に依存する設定を有効にする場合です。

書式の設定では、

NumberformatLocalを使いましたが、それと同じような事です。

書式では、マシンに依存する表示を使う事が多いので、Localを使う事を勧めました。

しかし、計算式ではそんなに多くはないですが、あるにはあります。


例えば、JIS関数を設定するなら、Localを使わないとエラーとなります。



それぞれの違い(Localは除く)

Range("A1").Value = "=B1+C1"
Range("A2").Formula = "=B1+C1"
Range("A3").FormulaR1C1 = "=B1+C1"
Range("A4").Value = "=RC[1]"
Range("A5").Formula = "=RC[1]"
Range("A6").FormulaR1C1 = "=RC[1]"


上記では、A3セルのみ正しく設定されません。

R1C1の場合は、R1C1参照形式で設定する必要がある訳ですが、まあ当然ですね。

それなら、結果として、.Valueだけで良い事になります。

結論からすれば、そういう事なのですが、

マクロを書くときには、可読性を考慮しますので、

R1C1参照形式で設定する場合は、R1C1を使うようにします


何故、こんなに多くのプロパティが存在しているのか

多くのプロパティが存在して、
そして、このブログでも何故紹介したのかと言うと、

それは、設定する時ではなく、その設定された計算式を参照する時に使うからです。

以下の設定をした場合、

Range("A1").Value = "=B1+C1"

設定後に、そのプロパティを参照すると、

Range("A1").Value ・・・ 計算結果の値
Range("A1").Formula ・・・ =B1+C1
Range("A1").FormulaR1C1 ・・・ =RC[1]


のように、参照結果が違ってきます。

つまり、計算式を設定する場合は、

.Value

で良いが、

計算式を参照する場合は、

.Formula

または、

.FormulaR1C1

を使うと言う事になります。


R1C1を使うメリット
R1C1参照形式は、少し面倒に感じるかもしれませんが、

以下のような場合には、有効な指定方法になります。
Sub サンプル1()
  Dim i As Long
  For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    Cells(i, 2).FormulaR1C1 = "=RC[-1]*100"
  Next i
End Sub

R1C1参照形式は、設定するセルからの、相対参照・絶対参照の組み合わせになっていますので、

設定するセルが、どのセルであろうと、同一の計算式を設定する事が出来ます。


さらに、工夫すれば

Sub サンプル2()
  Range(Range("B1"), Cells(Rows.Count, 1).End(xlUp).Offset(0, 1)).Value = "=A1*100"
End Sub

このように、1行でも書く事が出来るようになります。
(Offsetについては、後々説明します。)

この「マクロ超入門」を最期まで読み進めれば、これをすんなり書けるようになります、

ただし、少し真面目に勉強すればですけどね(笑)




同じテーマ「ExcelマクロVBA入門」の記事

第39回.セルのクリア(Clear)
第40回.セルのコピー・カット&ペースト(Copy,Cut,Paste)
第41回.セルのコピー&値の貼り付け(PasteSpecial)
第42回.セルをコピーするとは
第43回.総合練習問題5
第44回.VBA関数について
第45回.VBA関数(Format)

新着記事 ・・・新着記事一覧を見る

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



  • >
  • >
  • >
  • セルに計算式を設定(Formula)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。

    ↑ PAGE TOP