引き続きExcelで高度な数式を組むのに必要な技術のご紹介ということで、第二弾は「文字列sを区切り文字fで分割する数式(SPLIT)の解説を行います。
はじめに
下記の記事の続編になります。
以前、文字列処理の数式を公開しました。
今回は特に神がかっている難易度が高いであろう 、文字列を任意の文字で分割する式＝すなわち、VBAやGoogleスプレッドシートの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 |
プログラマにとっては当たり前の用語ですが、抽出後の値12、3456、789のそれぞれを「要素」。まとめて「各要素」と呼びます。
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 | １２␣␣␣␣␣␣␣␣␣ |
| 2 | 22 | 12 | ␣␣３４５６␣␣␣␣␣ |
| 3 | 33 | 23 | ␣␣␣␣␣␣７８９ |
| ... | |||
| 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・)
