大体で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」を使って入力です。

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

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

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

関連する記事から探す

カテゴリから探す

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

サイト内を検索する

↓キーワードを入力する

アーカイブから探す