【VBA】数式の取得とコピー、入力をする【Formulaを使います】
Excel VBAで、数式を取得するには「.Formula」を使います。絶対参照でコピーする場合は「.Formula」で、相対参照の場合は「.FormulaR1C1」です。配列数式を入力は「.FormulaArray」を使います。数式の扱いについて、マスターしていきましょう。
はじめに
この記事では、数式の取得やコピー、入力する方法について、ご紹介します。
数式を取得するには、「.Formula」を使います。
数式のコピーは、絶対参照の場合は「.Formula」で、相対参照の場合は「.FormulaR1C1」を使います。
数式を入力する場合は、ダブルクォーテーション「"」で数式を囲んで入力します。
複数セルを対象に数式を入力すれば、相対参照で数式を複数セルに入力することができます。
配列数式を入力したい場合は、「.FormulaArray」を使って入力です。
数式と値は一括で、入力することができますので、数式を含めてすべて取得したあと、値のみを変更してセルに一括で入力すると、高速化することができます。
数式の取得やコピー、入力方法についてマスターしていきましょう。
では、数式の取得やコピー、入力する方法について、解説していきます。
この記事を読むメリット
- 数式の取得やコピー、入力方法がわかります。
本記事の内容を動画でまとめています
目次
数式を取得する
数式を取得してみます。
Formulaを使う
数式を取得したい場合は、「.Formula」を使って、取得します。
Sub TEST1()
'数式を取得
Debug.Print Range("A2").Formula
End Sub
では、実行してみます。
数式を取得する
セルに数式を入力しておきました。
実行すると、「Formula」を使って、数式を取得できます。
Formulaで数式を取得できました。
数式をコピーする
次は、数式をコピーしてみます。
「絶対参照」でコピーしたい場合は、「.Formula」を使います。
「相対参照」でコピーしたい場合は、「.FormulaR1C1」を使ってコピーできます。
絶対参照でコピー（Formula）
「Formula」を使って、絶対参照でコピーしてみます。
Sub TEST2()
'絶対参照で数式をコピー
Range("A3") = Range("A2").Formula
End Sub
実行してみます。
絶対参照で数式をコピー
「絶対参照」で数式をコピーできました。
相対参照でコピー（FormulaR1C1）
次は、「FormulaR1C1」を使って、「相対参照」でコピーしてみます。
Sub TEST3()
'相対参照で数式をコピー
Range("A3") = Range("A2").FormulaR1C1
End Sub
実行すると、相対参照で数式をコピーできます。
相対参照で数式をコピー
相対参照で数式をコピーできました。
手動でコピーしたみたいに、相対参照でコピーすることができます。
数式を入力する
次は、数式をセルに入力してみます。
数式を入力してみる
数式を入力したい場合は、数式をダブルクォーテーション「"」で囲んで入力します。
Sub TEST4()
'数式を入力
Range("A2") = "=B2+C2"
End Sub
実行すると、数式を入力することができます。
ダブルクォーテーションで囲む
ダブルクォーテーションで囲んで数式を入力しました。
数式自体は、一旦セルに入力して、コピーして使うといいです。
ダブルクォーテーションには「"」を付ける
数式の中にダブルクォーテーション「"」が入力されている場合は、さらに「"」を追加します。
Sub TEST5()
'「"」は、「"」を追加する
Range("A2") = "=IF(B2=""1"",1,0)"
End Sub
というような感じで、数式の中にダブルクォーテーションがある場合は「"」を追加します。
「"」がある場合は「"」を追加
ダブルクォーテーションがある数式を、入力することができました。
複数セルに相対参照で入力
複数セル範囲を対象に数式を入力すると、複数セルに相対参照で入力することができます。
Sub TEST6()
'複数セルに相対参照で入力する
Range("A2:A5") = "=B2+C2"
End Sub
実行してみます。
複数セルに相対参照で入力
複数セルに相対参照で数式を入力できました。
相対参照で複数セルに入力したい場合は、複数セル範囲を対象にして、一括で入力しましょう。
数式と値を一括で入力
数式と値は、一括で入力することができます。
Sub TEST7()
Dim A
ReDim A(1 To 4, 1 To 1)
A(1, 1) = 1
A(2, 1) = "=B3+C3"
A(3, 1) = 3
A(4, 1) = "=B5+C5"
'数式と値を一括で入力
Range("A2").Resize(UBound(A, 1)) = A
End Sub
実行すると、数式でも値でも一括で入力することもできます。
数式と値は一括で入力できる
数式と値を一括で入力することもできました。
配列数式を入力する（FormulaArray）
配列数式を入力したい場合は、「FormulaArray」を使います。
Sub TEST8()
'配列数式を入力する
Range("C2").FormulaArray = "=SUM(A2:A4*B2:B4)"
End Sub
実行してみます。
配列数式を入力する
配列数式を入力したいという場合は、FormulaArrayを使いました。
数式と値を一括で入力して高速化
数式と値を一括で入力して高速化する、というのをやってみます。
1セルずつ入力した場合
数式はそのままで、数式以外を変更したい、ということになります。
数式以外を変更したい
1セルずつ変更する方法で、数式以外を変更してみます。
1セルずつ変更してみる
数式ではないセルを探して、連番を入力するVBAコードになります。
Sub TEST9()
t = Timer
Dim A
k = 0
For i = 1 To 5000
For j = 1 To 4
'値が入力されている場合
If Not Cells(i, j).HasFormula Then
k = k + 1
'数式以外を変更する
Cells(i, j) = k
End If
Next
Next
Debug.Print Timer - t & " 秒"
End Sub
実行すると数式はそのままで、数式以外を変更できます。
数式はそのままで、数式以外を変更できました。
結果をみてみます。
結果は、ちょっと遅いという感じです。
これを数式と値を一括で入力して、高速化してみます。
数式と値を一括で入力した場合
同じように数式はそのままで、数式以外を変更してみます。
数式以外を変更したい
数式と値を一括で入力するという方法で、時間を計測してみます。
数式と値を一括で入力してみる
流れとしては、次のようになります。
- 「.Formula」で数式と値を取得
- 配列の中の数式以外に連番を入力
- 数式と値を一括で入力
という流れになります。
Sub TEST10() t = Timer Dim A '数式と値を配列に入力 A = Range("A1").CurrentRegion.Formula k = 0 For i = 1 To 5000 For j = 1 To 4 '値が入力されている場合 If Left(A(i, j), 1) <> "=" Then k = k + 1 '数式以外を変更する A(i, j) = k End If Next Next '数式以外を一括で変更 Range("A1").Resize(UBound(A, 1), UBound(A, 2)) = A Debug.Print Timer - t & " 秒" End Sub
実行すると、数式はそのままで、数式以外を変更できます。
数式はそのままで、数式以外を変更できました。
結果は、みてみます。
かなり高速化できました。
データベースから、数式を含む出力用のシートに、データを取得する際に高速化したい、という場合に使えます。
おわりに
この記事では、数式の取得やコピー、入力する方法について、ご紹介しました。
数式を取得するには、「.Formula」を使います。
数式のコピーは、絶対参照の場合は「.Formula」で、相対参照の場合は「.FormulaR1C1」を使います。
数式を入力する場合は、ダブルクォーテーション「"」で数式を囲んで入力します。
複数セルを対象に数式を入力すれば、相対参照で数式を複数セルに入力することができます。
配列数式を入力したい場合は、「.FormulaArray」を使って入力です。
数式と値は一括で、入力することができますので、数式を含めてすべて取得したあと、値のみを変更してセルに一括で入力すると、高速化することができます。
数式の取得やコピー、入力方法についてマスターしていきましょう。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す