えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ

Excelで高度な数式を構築する方法 part2

引き続きExcelで高度な数式を組むのに必要な技術のご紹介ということで、第二弾は「文字列sを区切り文字fで分割する数式(SPLIT)の解説を行います。

f:id:Kotori-ChunChun:20190518224059p:plain


はじめに

下記の記事の続編になります。

www.excel-chunchun.com


以前、文字列処理の数式を公開しました。

www.excel-chunchun.com

今回は特に神がかっている難易度が高いであろう 、文字列を任意の文字で分割する式=すなわち、VBAGoogleスプレッドシートのSPLIT関数に相当する数式について解説してみます。

ちなみに、この数式の原型は私が開発したものではありません。現にネットでググると多数の質問サイトで紹介されています。

=INDEX(TRIM(
  MID(
    SUBSTITUTE($A2, ",", REPT(" ",LEN($A2))),
    ROW(INDIRECT("1:"&LEN($A2)))*LEN($A2)-(LEN($A2)-1),
    LEN($A2)
  )
),COLUMN()-1)

本当は超汎用化した下記の式を使いたかったのですが、さすがに複雑すぎるので自重しました。またどこかの機会にでも。

※何故↑の式ではダメなのか、それはこの記事を最後まで読めばご理解いただけるハズです。

=IFERROR(
INDEX(SUBSTITUTE(
  MID(
    SUBSTITUTE($A2, ",", REPT("★",LEN($A2))),
      ROW(
        INDIRECT("1:"&(LEN($A2)-LEN(SUBSTITUTE($A2,",","")))/LEN(",")+1)
      )*LEN($A2)-(LEN($A2)-1),
      LEN($A2)
    ),
    "★",
    ""
  ),COLUMN(B2)-COLUMN($A2)),
"")


使用事例

まずは、下記のようなデータを用意します。

A B C D E F G
1 元データ 1番目 2番目 3番目 4番目 5番目 6番目
2 12,3456,789
3 AB,CDE
4 aaa
5 a,b,c,d,e,f
6 ,12345,6,789,abc

そしてセルB2には下記を入力し、データ部(B2:G6)にコピーしましょう。

=INDEX(TRIM(
  MID(
    SUBSTITUTE($A2, ",", REPT(" ",LEN($A2))),
    ROW(INDIRECT("1:"&LEN($A2)))*LEN($A2)-(LEN($A2)-1),
    LEN($A2)
  )
),COLUMN()-1)

するとこんな結果になるはずです。

A B C D E F G
1 元データ 1番目 2番目 3番目 4番目 5番目 6番目
2 12,3456,789 12 3456 789
3 AB,CDE AB CDE
4 aaa aaa #REF! #REF! #REF!
5 a,b,c,d,e,f a b c d e f
6 ,12345,6,789,abc 12345 6 789 abc

※出力したいインデックスが、元データの文字数をオーバーしている箇所で#REF!が出ます。


解説

説明にあたっての注意

解説にあたっては、入力値のセルを変数名のようにして呼びます。

(事前にセルに名前定義しておくと、この式をそのまま使う事も出来ます。)

=INDEX(TRIM(
  MID(
    SUBSTITUTE(_s_, _f_, REPT(" ",LEN(_s_))),
    ROW(INDIRECT("1:"&LEN(_s_)))*LEN(_s_)-(LEN(_s_)-1),
    LEN(_s_)
  )
),_idx_)

説明で使用するデータはサンプルデータ2行目のデータです。

変数名(名前定義) 入力例
_s_ 12,3456,789
_f_ ,(コンマ)
_idx_ 1(COLUMN()-1)

そして、区切った後のデータは下記表のような配列になります。

要素番号(_idx_) 要素(抽出後の値)
1 12
2 3456
3 789

プログラマにとっては当たり前の用語ですが、抽出後の値123456789のそれぞれを「要素」。まとめて「各要素」と呼びます。


1.MID第一引数【文字列】

=INDEX(TRIM(MID(【文字列】,開始位置,文字数)),_idx_)
           ↑ココ!

第一引数は次のような順で評価されます。

=SUBSTITUTE(_s_, _f_, REPT(" ",LEN(_s_)))
=SUBSTITUTE("12,3456,789", ",", REPT(" ",11))
=SUBSTITUTE("12,3456,789", ",", "           ")
="12           3456           789"

入力データ_s_のうち区切り文字_f_REPT(" ",LEN(_s_))へ置換しています。

REPT(" ",LEN(_s_))とは、入力データ_s_の文字数分の半角スペースを発生させるという意味です。

つまり、区切り文字を入力文字列の最大文字数である11桁の半角スペースに置換することで、下のような状態になります。

区切り文字を最大文字数にしておくことで、各要素の前後には必ず大量の余白が入るため、第三引数のLEN(_s_)で値を取り出したときに、2つの要素が混在する可能性がゼロになります。

1 2 3 4 5 6 7 8 9 10
1 2
11 12 13 14 15 16 17 18 19 20
3 4 5 6
21 22 23 24 25 26 27 28 29 30 31
7 8 9

※␣ = 半角スペース


2.MID第二引数 【開始位置】

=INDEX(TRIM(MID(文字列,【開始位置】,文字数)),_idx_)
              ↑ココ!

第二引数は次のように評価されます。

=ROW(INDIRECT("1:"&LEN(_s_)))*LEN(_s_)-(LEN(_s_)-1)
=ROW(INDIRECT("1:"&11))*11-(11-1)
=ROW(INDIRECT("1:11"))*11-10
={1;2;3;4;5;6;7;8;9;10;11}*11-10
={11;22;33;44;55;66;77;88;99;110;121}-10
={1;12;23;34;45;56;67;78;89;100;111}

動的に配列を発生させる式です。


まずは配列の発生源であるROW関数だけを取り出してみます。

ROW(INDIRECT("1:"&LEN(_s_)))
ROW(INDIRECT("1:"&11))
ROW(INDIRECT("1:11"))
ROW(1:11)                   //数式の検証では表示されない。
{1;2;3;4;5;6;7;8;9;10;11}

ROW(INDIRECT("1:"&LEN(_s_)))これから発生させる要素の最大要素数を確保するための式です。

LEN(_s_)の部分ですが、データは_f_で区切られているため、要素数が最大文字数を超えることが論理的にあり得ません。

従って配列の要素数LEN(_s_)で確保しておけば絶対に大丈夫ということになります。

※論理的にありえない理由:最大要素数_s_に含まれる文字の全てが_f_の場合。つまり",,,,,,,,,,"など。


次にROW関数とINDIRECT関数ですが、ROW(INDIRECT("1:11"))を訳すと1行目から11行目までの行を求めるということになります。

ここで一部の人は疑問に思うかもしれません。

ROW(1:11)の答えは1ではないのか? と。

普通の数式であればROW関数は単一のセルしか評価できないため、先頭のA1しか評価されず、結果は1になります。

しかし今回は数式全体をINDEX関数で囲っています。

INDEX関数で囲われた数式のセル範囲は、各セルを一個づつ処理するようになるため、通常は

=ROW(1:11)
=1

となるはずの式が、

{=ROW(1:11)}
={ROW(A1);ROW(A2);ROW(A3);・・・;ROW(A11)}
={1;2;3;4;5;6;7;8;9;10;11}

と化けます。


次に配列に対する計算の部分です。

配列と単一値の計算では、それぞれの配列に同じ単一値による計算を行い、配列を返します。

={1;2;3;4;5;6;7;8;9;10;11}*LEN(_s_)-(LEN(_s_)-1)
={1;2;3;4;5;6;7;8;9;10;11}*11-10
={11;22;33;44;55;66;77;88;99;110;121}-10
={1;12;23;34;45;56;67;78;89;100;111}

まず*LEN(_s_)の部分ですが、これは第一引数で行った半角スペースの分をシフトさせるための演算です。

配列 1 2 3 4 5 6 7 8 9 10 11
*11の結果 11 22 33 44 55 66 77 88 99 110 121


しかし、求めたいのはMID関数の開始位置のため、1から始める必要があります。

そこで-(LEN(_s_)-1)によって、-10します。

配列 1 2 3 4 5 6 7 8 9 10 11
-10の結果 1 12 23 34 45 56 67 78 89 100 111

これで、MID関数の第二引数は完成です。


第二引数の結果を縦向きにして一つの表にするとこのようになります。

ROW() ROW()*11 ROW()*11-10
開始→ 1 11 1
2 22 12
3 33 23
4 44 34
5 55 45
6 66 56
7 77 67
8 88 78
9 99 89
10 110 100
LEN(s)→ 11 121 111


3.MID第三引数【文字数】

=INDEX(TRIM(MID(文字列,開始位置, 【文字数】)),0)
                   ↑ココ!

特にどうってことはありません。

=LEN(_s_)
=11

数式LEN(_S_)により入力文字列_s_の文字数をMID関数の抽出文字数としています。

これは、第一引数で要素間の文字を_s_の文字数分の半角スペースに変換しているためです。


4.MID計算結果のまとめ

というわけで、まとめると、以下の式で

=INDEX(TRIM(
  MID(
    SUBSTITUTE(_s_, _f_, REPT(" ",LEN(_s_))),
    ROW(INDIRECT("1:"&LEN(_s_)))*LEN(_s_)-(LEN(_s_)-1),
    LEN(_s_)
  )
),_idx_)

以下のデータを与えると

変数 入力例
_s_ 123,456,789
_f_ ,(コンマ)
_idx_ 1(COLUMN()-1)

以下のような数式で

=INDEX(TRIM(
  MID(
    SUBSTITUTE("12,3456,789", ",", REPT(" ",LEN("123,456,789"))),
    ROW(INDIRECT("1:"&LEN("123,456,789")))*LEN("123,456,789")-(LEN("123,456,789")-1),
    LEN("123,456,789")
  )
),1)

各引数の計算結果はこうなり

=INDEX(TRIM(
  MID(
    "12           3456           789",
    {1;12;23;34;45;56;67;78;89;100;111},
    11
  )
),1)

MID関数の結果はこうなり

=INDEX(TRIM(
  {"12         ";"  3456     ";"      789";"";"";"";"";"";"";"";""}
),1)

結果の配列を表にすると、このような形になります。

ROW ROW*11 ROW*10-10 MIDの結果
1 11 1 12␣␣␣␣␣␣␣␣␣
2 22 12 ␣␣3456␣␣␣␣␣
3 33 23 ␣␣␣␣␣␣789
...
11 121 111


5.TRIM(文字列)

あとは前後に半角スペースが付属しているため、TRIM関数

で取り除きます。

実行前はこんな状態ですが、

=INDEX(TRIM(
  {"12         ";"  3456     ";"      789";"";"";"";"";"";"";"";""}
),1)

TRIMが実行されることでこうなります。

=INDEX(
  {"12";"3456";"789";"";"";"";"";"";"";"";""}
,1)


6.INDEX(式,idx)

INDEXには2つの役割があります。

・第一引数に記述した式を配列として処理させるため

・配列から任意のインデックスの値を取り出すため


配列として処理させるため

INDEXの第一引数は配列を受け取るため、中の式は配列を返すように配列数式と同じように計算されます。※例外多数

今回では既に説明したとおり、INDEXによってROW(INDIRECT("1:"&LEN(_s_)))の部分で配列化が起きています。


任意のインデックスの値を取り出すため

そのまんまの意味ですが、_idx_で指定したインデックス(1が開始値)で任意の要素を取り出してセルに表示して完了です。


数式のテスト(解説が信じられない人へ)

と、ココまでの説明を読んでも、本当に間違いないのか納得がいかないかもしれません。

簡単にですが、極限テストしてみます。

入力値 1文字目 2文字目 3文字目 4文字目 5文字目 6文字目 7文字目
123456, 1 2 3 4 5 6 ,
12345,2 1 2 3 4 5 , 2
123,123 1 2 3 , 1 2 3
1,12345 1 , 1 2 3 4 5
,123456 , 1 2 3 4 5 6


<諸事情によりこの部分は省略します>


この数式の問題点

尚、今回の解説で使用した式にはいくつかの問題点があります。

  • 前後、内部の連続する半角スペースがTRIMにより抹消される。
  • 元データの文字数が出力したインデックスより少ないと#REF!が出る。
  • 区切り文字が2文字以上の場合に対応していない。

これらの問題が支障をきたすことは少ないと思いますが、こうした特殊な状況に於いては更に複雑なこちらの式を使うことになります。

=IFERROR(
INDEX(SUBSTITUTE(
  MID(
    SUBSTITUTE($A2, ",", REPT("★",LEN($A2))),
      ROW(
        INDIRECT("1:"&(LEN($A2)-LEN(SUBSTITUTE($A2,",","")))/LEN(",")+1)
      )*LEN($A2)-(LEN($A2)-1),
      LEN($A2)
    ),
    "★",
    ""
  ),COLUMN(B2)-COLUMN($A2)),
"")



まとめ

というわけで、文字列を分割する数式について解説でした。

この数式は

  • ROW(INDIRECT("1:"&LEN(_s_)))で文字数分の配列を生成
  • MID(~)で各要素の文字列を抽出して配列へ
  • TRIM(~)で配列の各要素からゴミを除去
  • INDEX(~)で配列から任意のインデックスの値だけを抽出

という流れで処理していることを説明しました。


この説明だけで文字列処理の数式の全てを解読することは出来ませんが、ある程度は読み解けるようになるのではないかと思います。

今回の式だけでも執筆が大変でしたので、次があるかは分かりませんが、配列を使った数式の構築はかなり頭の柔軟性を求められますので、興味の湧いた方は新規数式の開発に挑戦してみてください!

次回は高度な数式作成におけるハマりポイント等を紹介できればと考えております


以上


何か御座いましたらコメント欄、またはTwitterからどうぞ♪

それではまた来週♪ ちゅんちゅん(・8・)