動的配列数式の操作

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

VBAから動的配列数式を扱うときのポイントを解説します。なお「動的配列数式」や「スピル」あるいは、動的配列数式を利用した新しいワークシート関数などについては、下記ページをご覧ください。

Excel 2016レビュー - [Excelの使い方が激変する「スピル」]
Excel 2016レビュー - [XLOOKUP関数のすごいところ]
この関数はこう使え

セルの参照式について

まずは、セルの参照について整理しましょう。ここが理解できていないと、動的配列数式に関してもチンプンカンプンです。

  • 単一のセルまたはセル範囲への単純な参照

    1つのセルで、A1やA1:A3などのアドレスまたはセルに定義した名前を使うと、数式を入力したセルでは、指定したセルを参照できます。まぁ、これは普通の使い方です。

  • 共通部分の参照

    あまり知られていないのが、この使い方です。上図のセルB2では「セル範囲A1:A3」を参照しています。このように、1つのセルでセル範囲(複数のセル)を参照すると、指定したセル範囲の中で、参照式を入力したセルと同じ行または同じ列のセルを参照できます。これを「共通部分の参照」と呼びます。次のケースで考えると分かりやすいです。

    C列にはすべて「=A2:A5-B2:B5」という数式を入力してあります。本当はこういう場合、数式をコピーするときのことを考えて絶対参照にしますけど、数式が煩雑になるので、あえて相対参照にしています。さて、たとえばセルC3の「=A2:A5-B2:B5」は、指定したそれぞれのセル範囲「A2:A5」と「B2:B5」の中で、数式を入力しているセルC3と同じ行にあるセルを参照します。

    ここでは行方向でやりましたが、同じ考え方で列方向にも使えます。このように、1つのセルからセル範囲を参照したときは「共通部分の参照」が行われました。今までのExcelでは。これ、後で重要になってきますので、覚えておいてください。

  • 配列数式

    高度な使い方として、Excelには配列数式という仕組みがあります。配列数式について詳細に解説すると長くなりますので、ここでは割愛します。ちなみに、次のようなことができます。

    通常のSUMIF関数だけでは計算できない「曜日の集計」をしています。結果が合っているか確認したい方は、カレンダーをご覧ください。この配列数式と、次に解説する新しい動的配列数式の決定的な違いは、配列数式は結果を表示するすべてのセルに数式を入力しなければいけないということです。これも、覚えておいてくださいね。

  • 動的配列数式

    さて、いよいよ新しい動的配列数式です。

    よく見てください。数式を入力しているのは、セルB1だけです。セルB2とセルB3には何も入力していません。入力した数式は「=A1:A3」のようにセル範囲(複数のセル)を参照しています。するとExcelが、何も入力していないセルB2やセルB3へ、勝手に値(計算結果)を書き込みます。もちろん、次のように計算もできます。

    セルB1に入力した数式を「動的配列数式」と呼びます。動的配列数式は、参照しているセル範囲(ここではA1:A3)の大きさに合わせて、何も入力していないセルにも値を書き込みます。このように、動的配列数式によって値が書き込まれる機能が「スピル」です。スピルによって値が書き込まれたセルB2やセルB3のことを「ゴースト」と呼びます。また、動的配列数式を入力したセルと、スピルによって値が書き込まれたセル全体(ここではB1:B3)を「動的配列範囲」と呼びます。

    動的配列数式によってスピった動的配列範囲を、また別の数式で参照する場合もあるでしょう。

    しかし、動的配列数式による動的配列範囲は、元の参照範囲によって変わります。こんなときは、次のように参照します。

    ここで指定した「#」を「スピル範囲演算子」と呼びます。スピル範囲演算子は、動的配列数式による動的配列範囲を表します。

    さて、ここで先に紹介した「共通部分の参照」を思い出してください。今までのExcelでは、1つのセルに「=A1:A3」のようなセル範囲の参照式を入力すると、"共通部分の参照"が行われました。これ、入力する数式は動的配列数式とまったく同じです。これからは自動的に動的配列数式と認識されてスピります。では、今までの"共通範囲の参照"をしたいときは、どうすればいいのでしょう。そのときは、次のように「＠」を使います。

    この「＠」を「暗黙的なインターセクション演算子」と呼びます。英語では「implicit intersection operator」です。この暗黙的なインターセクション演算子(長ぇーよw)は、現在のExcelでも、テーブルの構造化参照で使われています。

動的配列数式の代入

さあ、いよいよVBAの話です。VBAで動的配列数式を代入するには、どうしたらいいでしょう。まずは基本のおさらいです。セルに入力されている"値"はValueプロパティで表されます。対して、セルに入力されている"数式"はFormulaプロパティで取得します。

  • Range("B1").Value → 60
  • Range("B1").Formula → =SUM(A1:A3)

このことから、セルに数式を代入するときは、次のように「Formulaプロパティへ代入する」というのが一般的な考え方です。

Range("B1").Formula = "=SUM(A1:A3)"

私も以前は、そう考えていました。セミナーでもそう教えましたし、書籍にもそう書いてきました。でも、数年前から考え方が変わりました。手動操作でセルに数式を代入するとき、何も特別なことはしません。数値の100や、文字列の"田中"などを代入するのと同じ操作で数式を代入します。代入した文字列の先頭が「=」で始まっていると、Excelは自動的に「あ、これって数式だよね」と判断してくれます。その後、代入した数式をFormulaプロパティへ入れ、計算結果をValueプロパティに入れてくれます。つまり、数式を代入するのって、100や"田中"を代入するのと同じ操作なんじゃないかと。あとは、Excelに任せればいいのではないかと。手動でそうやっているのだから、VBAでそうしてもいいのではないかと。そう考えるようになりました。もちろん、Formulaプロパティへ代入するというのは間違っていません。でも、次のようにすれば簡単になるよなと。

Range("B1").Value = "=SUM(A1:A3)"
'あるいは
Range("B1") = "=SUM(A1:A3)"

どっちでも同じだ思います。なので最近は、セミナーでも「数式はValueプロパティへ代入すればいいんだよ」と教えています。もちろん、セルの数式を取得するときはFormulaプロパティを使います。では、その考え方で動的配列数式を代入してみましょう。

Sub Macro1()
    Range("B1").Value = "=A1:A3*2"
End Sub

うまくいきます。では、次にFormulaプロパティを使ってみましょう。

Sub Macro2()
    Range("B1").Formula = "=A1:A3*2"
End Sub

なんと、Formulaプロパティだと動的配列数式になりません。共通範囲の参照になっちゃいます。これ、動的配列数式を利用した「SORT関数」や「UNIQUE関数」でも同じです。Formulaプロパティに代入すると望む結果になりません。

Sub Macro3()
    Range("B1").Formula = "=SORT(A1:A3,1,-1)"
End Sub

じゃあ、どーしたらいいかというと。私が個人的に思っている「Valueでいいんじゃね？」という発想でもいいのですけど、ここはMicrosoftが新しいプロパティを用意してくれました。Formula2プロパティです。

Sub Macro4()
    Range("B1").Formula2 = "=A1:A3*2"
End Sub


Sub Macro5()
    Range("B1").Formula2 = "=SORT(A1:A3,1,-1)"
End Sub

なので、次のように考えるといいでしょう。

  • 動的配列数式として代入したいのなら、ValueプロパティまたはFormula2プロパティを使う
  • 共通範囲の参照として代入したいのなら、Formulaプロパティを使う

もちろん、Valueプロパティだろうが、Formulaプロパティだろうが、Formula2プロパティだろうが、セルに数式が代入されちゃえば、その数式はひとつなのだから、数式を取得するときは、Formulaプロパティだろうが、Formula2プロパティだろうが、得られる結果は同じです。

【追記 2020/05/08】

本稿は、2019/09/13に動作確認をして執筆しましたが、その後確認したところ、どうやら動作(仕様)が変わったようです。上記のように「Valueプロパティ」に動的配列数式を代入すると、共通範囲の参照として代入されてしまいます。もちろん、Formulaプロパティに代入したときも、同じように共通範囲の参照になります。したがって、動的配列数式として代入するときは、新設された「Formula2プロパティ」を使わないといけないようです。まぁ、その方が自然です。正直、上記を執筆したとき「へ？なんで？」って感じました。

動的配列数式の取得

ここでは、次のようなケースで考えてみます。

セルA4には、通常のSUM関数が入力されています。セルB1には動的配列数式が入力されています。セルB1の動的配列数式によってスピってる動的配列範囲はセル範囲B1:B3です。この状態で、動的配列数式が入力されているセルB1を特定してみます。まず、セルに数式が入力されているかどうかは、RangeオブジェクトのHasFormulaプロパティで分かります。

Sub Macro6()
    Dim C As Range, A As String
    For Each C In ActiveSheet.UsedRange
        If C.HasFormula = True Then
            A = A & C.Address(False, False) & vbTab & C.Formula & vbCrLf
        End If
    Next C
    MsgBox A
End Sub

セルに数式が入力されていると、HasFormulaプロパティがTrueを返します。さて、入力されている数式が、動的配列数式かどうかを判定します。これには、HasSpillプロパティを使います。HasSpillプロパティも、今回新しく追加されたプロパティです。そのセルの数式がスピってるかどうかを表します。動的配列数式を、1つのセルに対して行うことはありません。1つのセルは配列じゃありませんから、それは普通の数式です。であるなら、動的配列数式では必ず「スピル」が起きているはずです。それを判定するのがHasSpillプロパティです。

Sub Macro7()
    Dim C As Range, A As String
    For Each C In ActiveSheet.UsedRange
        If C.HasFormula = True Then
            If C.HasSpill = True Then
                A = A & C.Address(False, False) & vbTab & C.Formula & vbCrLf
            End If
        End If
    Next C
    MsgBox A
End Sub

動的配列数式が入力されているのは、セルB1と分かりましたので、ここからは、セルB1について調べてみます。まず、動的配列数式によってスピってる範囲(動的配列範囲)は、これも新しく追加されたSpillingToRangeプロパティで分かります。SpillingToRangeプロパティは、動的配列数式によって値が書き込まれた"動的配列範囲"を返します。

Sub Macro8()
    MsgBox Range("B1").SpillingToRange.Address(False, False)
End Sub

SpillingToRangeプロパティはセル範囲を返しますので、For Eachでセルを1つずつ操作することもできます。

Sub Macro9()
    Dim C As Range, A As String
    For Each C In Range("B1").SpillingToRange
        A = A & C.Value & vbCrLf
    Next C
    MsgBox A
End Sub

また、今回のように、動的配列数式が入力されているセルのアドレスが特定されているのなら、SpillingToRangeプロパティだけでなく、上記で解説したスピル範囲演算子を使って、次のようにすることも可能です。

Sub Macro10()
    Dim C As Range, A As String
    For Each C In Range("B1#")
        A = A & C.Value & vbCrLf
    Next C
    MsgBox A
End Sub

セルB1に動的配列数式が入力されているとき、Range("B1#")とRange("B1").SpillingToRangeは同じセル範囲を返します。いずれにしても両者は、動的配列数式によってスピったセル範囲(ここではセルB2とセルB3)だけでなく、動的配列数式が入力されているセルB1も含まれる点に留意してください。

では、Range("B1#")やRange("B1").SpillingToRangeで取得できた動的配列範囲の中で「動的配列数式が入力されているセル(B1)」と「動的配列数式によって値が書き込まれたセル(B2とB3)」を区別するには、どうしたらいいでしょう。これには、いくつかの考え方があります。まず、動的配列数式が入力されているセルのHasFormulaプロパティはTrueを返します。それで調べてみましょう。

Sub Macro11()
    Dim C As Range, A As String
    For Each C In Range("B1#")
        If C.HasFormula = True Then
            A = A & C.Address(False, False) & vbTab & "数式" & vbCrLf
        Else
            A = A & C.Address(False, False) & vbTab & "値" & vbCrLf
        End If
    Next C
    MsgBox A
End Sub

あるいは、数式が入力されているセルのFormulaプロパティは、入力されている数式を返しますが、スピルによって値が入力されているセルのFormulaプロパティは空欄を返します。それでも判定できます。

Sub Macro12()
    Dim C As Range, A As String
    For Each C In Range("B1#")
        If C.Formula <> "" Then
            A = A & C.Address(False, False) & vbTab & "数式" & vbCrLf
        Else
            A = A & C.Address(False, False) & vbTab & "値" & vbCrLf
        End If
    Next C
    MsgBox A
End Sub

そして、実はここでも新しく追加されたプロパティを活用することができます。それはSpillParentプロパティです。SpillParentプロパティは、動的配列範囲内の各セルで"親"つまり、実際に動的配列数式が入力されているセルを返します。

Sub Macro14()
    Dim C As Range, A As String
    For Each C In Range("B1#")
        A = A & C.SpillParent.Address(False, False) & vbCrLf
    Next C
    MsgBox A
End Sub

最後にトリッキーな方法をご紹介します。動的配列数式というのは、必ず「動的配列範囲の左上セル」に入力されているはずです。動的配列数式の上や左にはスピりませんから。だったら、動的配列数式が入力されているセルというのは、次のように取得できます。

Sub Macro15()
    MsgBox Range("B1#")(1).Address(False, False)
End Sub

Range("B1#")(1)というのは、Range("B1#").Item(1)の略です。最も簡単に調べるのなら、これでもいけます。

長くなったので、このへんにしておきます。本稿執筆時点(2019年9月)では、まだExcelに動的配列数式は搭載されていません。でも、近いうちに必ず実装されます。Microsoftが、そう公言しましたから。動的配列数式や「SORT関数」「UNIQUE関数」「FILTER関数」「XLOOKUP関数」などが追加されると、Excelの使い方や考え方が大きく変わります。当然、シート内のデータを操作するマクロにも、新しい技術や知識が必要になります。ぜひ、今から備えておきましょう。