大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、数式を取得するには「.Formula」を使います。絶対参照でコピーする場合は「.Formula」で、相対参照の場合は「.FormulaR1C1」です。配列数式を入力は「.FormulaArray」を使います。数式の扱いについて、マスターしていきましょう。

はじめに

この記事では、数式の取得やコピー、入力する方法について、ご紹介します。

数式を取得するには、「.Formula」を使います。

数式のコピーは、絶対参照の場合は「.Formula」で、相対参照の場合は「.FormulaR1C1」を使います。

数式を入力する場合は、ダブルクォーテーション「"」で数式を囲んで入力します。

複数セルを対象に数式を入力すれば、相対参照で数式を複数セルに入力することができます。

配列数式を入力したい場合は、「.FormulaArray」を使って入力です。

数式と値は一括で、入力することができますので、数式を含めてすべて取得したあと、値のみを変更してセルに一括で入力すると、高速化することができます。

数式の取得やコピー、入力方法についてマスターしていきましょう。

では、数式の取得やコピー、入力する方法について、解説していきます。

この記事を読むメリット

  • 数式の取得やコピー、入力方法がわかります。

本記事の内容を動画でまとめています

目次から見たい項目へ移動すると便利ですよ。

目次

数式を取得する

数式を取得してみます。

Formulaを使う

数式を取得したい場合は、「.Formula」を使って、取得します。

Sub TEST1()
  
  '数式を取得
  Debug.Print Range("A2").Formula
  
End Sub

では、実行してみます。

数式を取得する

セルに数式を入力しておきました。

数式を取得したいです

実行すると、「Formula」を使って、数式を取得できます。

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を使います

配列数式を入力したいという場合は、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」を使って入力です。

数式と値は一括で、入力することができますので、数式を含めてすべて取得したあと、値のみを変更してセルに一括で入力すると、高速化することができます。

数式の取得やコピー、入力方法についてマスターしていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す