Excel の配列数式で、配列を文字列として連結する

概要

エクセルで、配列数式の配列を連結して1つの文字列にする関数は、ユーザー定義関数として作ることができる
そのユーザー定義関数では、配列を Variant 型の引数で受けて、使うときは1～2次元配列として扱う
どんな深さ（次元数）の配列でも、For Each ～ Next で全要素にアクセスできる

配列数式の配列を文字列として連結するとは？

配列数式についてはこのあたりなどを参照。

エクセルで配列数式を使い始めると、こんなのできるかな？→できた！という範囲が広がってきて楽しいのだけど、これは無理っぽいなというのもあって、そのひとつがこの文字列の連結。

つまりこんな表があったときに、

20110115_vba_0

C列の地域毎に、B列の都道府県を列挙して表示したい場合、標準のワークシート関数だけで考えると、どうしても配列中の文字列を連結させるところで行き詰まってしまう。

20110115_vba_1

そのため、こんな表現の配列数式で実現できるよう、ユーザー定義関数 strcat を作りたいなと考えていた。

{=strcat(IF(C$1:C$5=E1,B$1:B$5,""),"、")}

そして最近になって、同僚の人も同じ問題にぶちあたり、CONCATENATE 関数を使って失敗していたので、ちょっとあれこれ調べてみたところ、それっぽいものを作ることができた。

コード（VBA）

Function strcat(elements As Variant, Optional separator As String = vbNullString) As String
    Dim output As String, tmp As String, e As Variant
    output = vbNullString

    For Each e In elements
        tmp = CStr(e)
        If 0 < Len(tmp) Then
            output = output & tmp & separator
        End If
    Next

    strcat = Left(output, Len(output) - Len(separator))
End Function

この関数の機能

第一引数にセルの範囲もしくは配列を指定し、その要素を第二引数の文字列で連結した文字列を返す。
第二引数は省略でき、その場合の連結に使う文字列は空文字列となる。
ただし、文字列として評価したときに空文字列となる要素は無視される。

配列数式の配列を引数にとるときのポイント

引数は Variant 型で定義

配列数式中の配列を引数で受け取るときは、Variant 型で受け取る。

これ、数値しか入っていないセルの配列なら Integer 型だったりしてもいいのかな、と思って試してみたら #VALUE! になってしまったので、必ず Variant でないといけないっぽい。

セル範囲によって一次元配列だったり二次元配列だったりする

そして、引数で受けた配列は VBA でも配列として表現されているので、各要素には添字を使ってアクセスしたり、UBound や LBound の各種関数にも使うことができる。

ただし、配列の元になったセルの範囲の行数が1（例えば A1:D1）の場合は一次元配列で、それ以外の場合（例えば A1:A4 や A1:D4）であれば二次元配列として扱う必要があり、注意が必要。

最初 strcat 関数を作り始めたときは、配列の要素に For ～ Next で添字を使ってアクセスしていたので、一次元配列か二次元配列の条件分岐が発生し、ちょっと面倒なコードになっていた。

最終的に、For Each ステートメントを使うと、配列の深さ（次元数）に関係なく配列のすべての要素にアクセスできることがわかり、すっきりしたコードにできた。

また、For Each を採用したことによるプラスの副作用として、strcat 関数の引数に、配列ではなく普通のセル範囲を指定した場合でも、直感通りに動作するようになった。

その他今回勉強したポイント

省略可能な引数は、変数名の前に Optional というキーワードをつける。ただし最後の引数だけ。
可変長引数は、変数名の前に ParamArray というキーワードをつける。ただし最後の引数だけ。ゆえに Optional と併用不可（？）
引数の変数名にカッコをつけなくても、Variant 型にしたら配列を受け取ることができる。
VBA の引数は、数値なども含め参照渡しされる。値渡しするときは ByVal キーワードを変数名の前につける。
配列の次元数を調べるには、UBound 関数の第二引数を変えていってエラーになるかどうかでしか調べられない（？）

参考文献

配列数式のガイドラインと例 – Excel – Microsoft Office?<http://office.microsoft.com/ja-jp/excel-help/HA010228458.aspx>
配列研究室 STEP 3 <http://www.clayhouse.jp/array/array03_e.htm>
＠IT：Windows TIPS — Tips：Excelのユーザー定義関数で複数の結果値を返す <http://www.atmarkit.co.jp/fwin2k/win2ktips/517exmlt/exmlt.html>
『複数セルの内容を１セルに表示』（デッドボ?ル）エクセル Excel [エクセルの学校] <http://www.excel.studio-kazu.jp/kw/20071004184327.html>

NYORO PRESS

Just another WordPress weblog