[Excel]GoogleスプレットシートのSPLIT関数みたいなやつを改造してみた

Microsoft Office PRO+(Microsoft 365)のExcelには、スピルと言う配列状の結果を返す機能があります。この機能は、GoogleのSpreadsheetには元々備わっている機能ですが、最近になってMicrosoft Officeも対応した機能です。
Google Spreadsheetに搭載されている、スピル関連機能の多くは、Microsoft Office PRO+にも搭載されていますが、ある文字列を指定した文字列で分割する、SPlitと言う機能が搭載されていない。と言うことで、Office TANAKAの田中さんが、SPLIT関数をユーザー定義関数として搭載する手順を、You Tubeで公開していました。

その中で、

  • 縦方向に出力する
  • 開始位置を指定する
  • 取り出す長さを指定する

機能もあったら便利なんじゃ無いかな?とつぶやいていたので、搭載してみました。
田中さんは、=SPLIT2() と言う名前で搭載されていたので、それをベースに、=SPLIT3()と言う名前で書いて見ました。=SPLIT2()の機能もそのまま搭載されています。

ソースも掲載してありますが、サンプル付きのExcelファイルをZIP形式でアーカイブした物をページの最後に掲載して置きました。

=SPLIT3(
文字列,
区切り文字,
各文字で分割(省略時はTRUE),
空は削除(省略時はTRUE),
縦方向に出力(省略時はFALSE),
開始位置(省力時は1),
長さ(省略時は0)
)

基本的には、「文字列」で指定された文字列を、「区切り文字」で指定した文字で区切って、複数の文字列を返します。

「区切り文字」に2文字以上を指定した時、「各文字で分割」をTRUEにすると指定さた文字のいずれかが現れると分割されます。FALSEだと「区切り文字」全体が現れると分割されます。

「空は削除」は「区切り文字」に同じ文字が、「文字列」に2つ以上連続して登場した時、連続して登場した「区切り文字」をどのように扱うかの指定です。TRUEを指定すると連続した区切り文字は1文字だけ登場したと見なされ、空白は出力されません。FALSEの場合には指定された文字を素直に分割し、結果的に空白も出力されます。

「縦方向に出力」は、出力結果を横方向(列方向)へ出力するのか、縦方向(行方向)へ出力するのかの指定です。TRUEの場合横方向へ出力し、FALSEの場合縦方向へ出力します。

「開始位置」は、分割結果が複数文字列になった時の、表示開始位置を指定します。省略すると、1が指定されたことになります。例えば、「ABC」,「DEF」,「GHI」,「jKL」の4文字列が出力文字列の場合、1が指定されると、「ABC」,「DEF」,「GHI」,「jKL」が出力され、3が指定されると、「GHI」,「jKL」が出力されます。
0など1より小さい値が指定された場合や、結果文字列の数より大きな値が指定された場合には、全てを出力します。

「長さ」は、分割結果が複数文字列になった時の、表示する文字列の数を指定します。省略すると、0が指定されたと見なします。0が指定された場合には、結果を全て返します。
例えば、「ABC」,「DEF」,「GHI」,「jKL」の4文字列が出力文字列の場合、2を指定すると「ABC」,「DEF」が返されます。
結果文字列の数よりも大きな値が指定された場合には、0が指定されたと見なし、結果的に全ての結果を返します。

実際の動作は、田中さんの動画や、サンプルを見て頂いた方がピンと来ると思います。

  1. Public Function SPLIT3(文字列 As String, _
  2.                        区切り文字 As String, _
  3.                        Optional 各文字で分割 As Boolean = True, _
  4.                        Optional 空は削除 As Boolean = True, _
  5.                        Optional 縦方向に出力 As Boolean = False, _
  6.                        Optional 開始位置 As Long = 1, _
  7.                        Optional 長さ As Long = 0) As Variant
  8.     Dim Result As Variant, WorkBuff As Variant
  9.     Dim Counter As Long, i As Long
  10.     Dim Start As Long, SLength As Long, ToLength As Long
  11.     If 各文字で分割 = True Then
  12.         For i = 1 To Len(区切り文字)
  13.             文字列 = Replace(文字列, Mid(区切り文字, i, 1), vbTab)
  14.         Next i
  15.         区切り文字 = vbTab
  16.     End If
  17.     Result = Split(文字列, 区切り文字)
  18.     If 空は削除 = True Then
  19.         WorkBuff = Result
  20.         For i = 0 To UBound(Result)
  21.             If Result(i) <> “” Then
  22.                 WorkBuff(Counter) = Result(i)
  23.                 Counter = Counter + 1
  24.             End If
  25.         Next i
  26.         ReDim Preserve WorkBuff(Counter – 1)
  27.         Result = WorkBuff
  28.     End If
  29.     Start = 開始位置
  30.     SLength = Counter
  31.     If Start < 1 Then
  32.         Start = 1
  33.     End If
  34.     If Start > 1 And Start <= Counter Then
  35.         WorkBuff = Result
  36.         SLength = Counter – Start
  37.         ReDim Result(SLength)
  38.         For i = 0 To Counter – Start
  39.             Result(i) = WorkBuff(i + Start – 1)
  40.         Next i
  41.     End If
  42.     ToLength = 長さ
  43.     If ToLength > 0 And ToLength <= SLength Then
  44.         WorkBuff = Result
  45.         ReDim Result(ToLength – 1)
  46.         For i = 0 To ToLength – 1
  47.             Result(i) = WorkBuff(i)
  48.         Next i
  49.     End If
  50.     If 縦方向に出力 = True Then
  51.         WorkBuff = Result
  52.         ReDim Result(Counter – 1, 0)
  53.         For i = 0 To Counter – 1
  54.             Result(i, 0) = WorkBuff(i)
  55.         Next i
  56.     End If
  57.     SPLIT3 = Result
  58. End Function

ダウンロード: SPLIT3.zip

シェアする

  • このエントリーをはてなブックマークに追加

フォローする