VBAサンプル集
スピルに対応したXSPLITユーザー定義関数(文字区切り)

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
公開日:2020-03-15 最終更新日:2020-08-26

スピルに対応したXSPLITユーザー定義関数(文字区切り)


エクセルにスピルが登場して、VBAのユーザー定義関数の用途も広がってきていると思います。
そこで、文字列を指定した記号・文字列で区切て、セルに出力するユーザー定義関数を作ってみましょう。
VBAには Split関数 がありますが、これをワークシート関数として使えるようにしてみましょうという事です。

・Split関数 ・Split関数注意点 ・Split関数ま使用例

マクロ VBA スピル 文字区切り SPLIT

スピルについては、以下を参照してください。

スピルについて|エクセル入門
・スピルとは ・スピルの数式例 ・ゴースト ・スピル範囲での独特な挙動について ・スピルのエラー表示 ・スピル範囲演算子 ・暗黙的なインターセクション演算子 ・従来のスピルしないエクセルとの互換性についての注意点 ・スピル関連記事

スピルでVBAの何が変わったか|VBA技術解説
・Rangeオブジェクトに追加されたプロパティ ・セルに数式を設定する場合 ・1次元配列を返すユーザー定義関数 ・2次元配列を返すユーザー定義関数 ・JAG配列を返すユーザー定義関数 ・スピルのVBAでの活用について

区切り位置ウィザード

「データ」→「区切り位置」

マクロ VBA スピル 文字区切り SPLIT

マクロ VBA スピル 文字区切り SPLIT

マクロ VBA スピル 文字区切り SPLIT


ワークシートの関数で文字区切りする場合

マクロ VBA スピル 文字区切り SPLIT

=MID(A1,
UNIQUE(IFERROR(FIND(",",","&A1,SEQUENCE(1,LEN(A1))),1),TRUE),
UNIQUE(IFERROR(FIND(",",A1,SEQUENCE(1,LEN(A1))),LEN(A1)+1),TRUE)
-UNIQUE(IFERROR(FIND(",",","&A1,SEQUENCE(1,LEN(A1))),1),TRUE))


かなり複雑な数式になっていますが、

UNIQUE(IFERROR(FIND(",",","&A1,SEQUENCE(1,LEN(A1))),1),TRUE)
1 4 8 13
このような配列になります。
つまり、各区切りの先頭位置の数値配列です。

UNIQUE(IFERROR(FIND(",",A1,SEQUENCE(1,LEN(A1))),LEN(A1)+1),TRUE)
3 7 12 18
このような配列になります。
つまり、各区切りの終り位置の数値配列です。

結局、
=MID(文字列,各区切りの先頭位置,各区切りの終り位置-各区切りの先頭位置)

この数式は、縦にスピルさせられません。
そして、もちろん文字区切りの数式は様々な方法が考えられます。
上記数式は、スピル後の新関数を使った一例になります。

※追記
上記数式は、最後がカンマで終わっていると、最後の列に#N/Aが出力されてしまいます。
これに対処するには、全体をIFERRORで囲んでください。
IFERROR(MID(・・・),"")


では、いよいよ本題の「スピルに対応したXSPLITユーザー定義関数(文字区切り)」に入ります。


ユーザー定義関数のVBAコード

Function XSPLIT(範囲, 区切り文字)
  Dim i As Long, j As Long
  Dim rngAry, tmpAry
  Dim rtnAry() As String
  
  If IsArray(範囲) Then
    rngAry = 範囲
  ElseIf 範囲.Rows.Count > 1 Then
    rngAry = 範囲.Value
  Else
    ReDim rngAry(1 To 1, 1 To 1)
    rngAry(1, 1) = 範囲.Value
  End If
  
  ReDim rtnAry(1 To UBound(rngAry, 1), 1 To 1)
  
  For i = LBound(rngAry, 1) To UBound(rngAry, 1)
    tmpAry = Split(rngAry(i, 1), 区切り文字)
    If UBound(tmpAry) + 1 > UBound(rtnAry, 2) Then
      ReDim Preserve rtnAry(1 To UBound(rtnAry, 1), _
                 1 To UBound(tmpAry) + 1)
    End If
    For j = LBound(tmpAry) To UBound(tmpAry)
      rtnAry(i, j + LBound(rngAry, 1)) = tmpAry(j)
    Next
  Next
  
  XSPLIT = rtnAry
End Function

スピル対応のユーザー定義関数作成時の注意点

前半は、引数が配列の場合や、1セル(配列にならない)の場合にも対応するためのコードとなっています。

戻す配列は、1次元、2次元、要素数一定のJAG配列になります。
上記VBAでは、2次元配列にしているので、要素数を随時最大数にRedimしています。

配列のデータ型ですが、
tnAry() As String
ここは、型を指定せずにVariantにしてしまうと空欄が0で出力されてしまいます。

マクロ VBA スピル 文字区切り SPLIT

XSPLIT関数の使用例

普通の関数と同様に使えます。

マクロ VBA スピル 文字区切り SPLIT

マクロ VBA スピル 文字区切り SPLIT

=XSPLIT(A1:A5,",")

指定できる範囲は、縦方向(行方向)だけになります。
横方向(列方向)に区切った文字を出力しているので、当然なのですが・・・
もちろん、
引数を増やすか、範囲の縦横を自動判別して戻す配列の縦横を自動的に作成することも可能です。
ですが、ワークシート関数には便利な関数が用意されています。
TRANSPOSE関数があるので、これを使えばこのVBAでも対応できてしまいます。

=TRANSPOSE(XSPLIT(TRANSPOSE(A1:E1),","))

マクロ VBA スピル 文字区切り SPLIT

データ件数が10万件くらいなら、ほとんど一瞬でスピルします。
このようなスピルは非常に高速に処理されていると思います。

スピルしないエクセルで使う場合

文字区切りで展開される範囲をあらかじめ選択して、配列数式で入力すれば使う事が出来ます。

マクロ VBA スピル 文字区切り SPLIT

=XSPLIT(A1:A5,",")
これを、Ctrl+Shift+Enterで入力してください。

ユーザー定義関数の実践使用例

ユーザー定義関数の作り方
・簡単な例でユーザー定義関数を作ってみましょう ・この関数の使い方 ・ユーザー定義関数の実践使用例

ユーザー定義関数でフリガナを取得する(GetPhonetic)
ワークシート関数の、「PHONETIC」では、他のソフト等からコピペした漢字は取得できません。そこで、VBAでユーザー定義関数を作成し、読みを取得できるようにします。A列はメモ帳よりコピペしました。B列に、ユーザー定義関数を指定して、振り仮名を取得しています。

ユーザー定義関数でハイパーリンクのURLを取得(Hyperlink)
ネットから、何らかの一覧をエクセルにコピペすると、文字列や画像等に、リンクの設定がくっついてきます。URLが表記されていれば良いですが、表示されていない事の方が多いでしょう。そこで、VBAでユーザー定義関数を作成し、URLを取得できるようにします。

スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
・スピルとXLOOKUP関数の練習問題 ・従来の関数+スピルで数式を作る ・ XLOOKUP関数に書き換える ・LET関数に書き換える ・VBAでユーザー定義関数を作成 ・スピルと新関数の練習のまとめ



同じテーマ「マクロVBAサンプル集」の記事

1次元配列の並べ替え(バブルソート,挿入ソート,クイックソート)

・検証方法 ・バブルソート ・挿入ソート ・クイックソート ・最後に
2次元配列の並べ替え(バブルソート,クイックソート)
・検証方法 ・バブルソート ・クイックソート ・複数キーでの並べ替えについて ・ワークシートを使って並べ替え・・・番外編
Dir関数で全サブフォルダの全ファイルを取得
・Dir関数でサブフォルダも含むファイル一覧を取得するVBA ・ファイル一覧を取得するVBAの使用例と解説 ・Dir関数の関連記事
順列の全組み合わせ作成と応用方法
・順列とは ・順列の全組み合わせを作成するFunction ・使用例1 ・・・ 配列の順列を作成しシートへ出力 ・使用例2 ・・・ 区切り文字で区切られた文字列の順列を作成 ・使用例3 ・・・ 任意の要素数の順列
スピルに対応したXSPLITユーザー定義関数(文字区切り)


新着記事NEW ・・・新着記事一覧を見る

エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。


このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ