スポンサードリンク

文字列を区切り文字で区切って配列化する関数

ここ最近twitter#エクセルクイズというタグで出題されている問題に色々とチャレンジしてきました。

その中で配列数式を使用することも多かったのですが、自分の思いもよらない使い方をされているのを見かけて勉強になりました。

今回はその集大成として文字列を区切り文字で区切って配列化する関数を紹介したい思います。

文字列配列化関数

・関数の効果:指定した文字列を指定した区切り文字毎に区切って1次元配列にして返す。

・_str_ : 配列化する文字列。セル参照するか、文字列を直接入力する。直接入力の場合はダブルクオーテーションで括る。

・_del_:配列化の基準となる区切り文字。1文字のみ指定可。区切り文字をダブルクオーテーションで括る。

{=MID( _str_ ,
      SMALL(
         IFERROR(
            INDEX(
               (ROW(INDIRECT("1:"&LEN( _del_ & _str_ & _del_ )))/(--(MID( _del_ & _str_ & _del_ ,ROW(INDIRECT("1:"&LEN( _del_ & _str_ & _del_ ))),1)= _del_ ))),
               0),
            ""),
      ROW(INDIRECT("1:"&LEN( _str_ )-LEN(SUBSTITUTE( _str_ , _del_ ,""))+1))),
         MMULT(
            SMALL(
               IFERROR(
                  INDEX(
                     (ROW(INDIRECT("1:"&LEN( _del_ & _str_ & _del_ )))/(--(MID( _del_ & _str_ & _del_ ,ROW(INDIRECT("1:"&LEN( _del_ & _str_ & _del_ ))),1)= _del_ ))),
                     0),
                  ""),
               ROW(INDIRECT("1:"&LEN( _str_ )-LEN(SUBSTITUTE( _str_ , _del_ ,""))+1))+{0,1})
               *{-1,1}-{1,0},
            {1;1}))}

どうだろうか。

この関数を使用するとこのような返り値になる。

f:id:ExcelLover:20190721194256j:plain

「A1,A12,B123」という文字列を「,」で区切って1次元配列として返している。

使用用途

このような文字列を分割してセルに表示する。
f:id:ExcelLover:20190721194317j:plain

このような文字列を分割してsum関数の引数とする。
f:id:ExcelLover:20190721194329j:plain

返り値が1次元配列ですので様々な用途に使用可能です。

まとめ

ここしばらく頭の片隅に居座って気になっていたことが解決できてホッと一息です。

実用性が高いわけではないので、まぁ正直実務で使うことは無いとは思うのですが。

Excelクイズの回答の中でお目にかかるかも知れません。

良い勉強になりました。