【スプレッドシート関数】空白を詰めたい
textjoin関数とsplit関数を使うと、空白セルを簡単に詰めることができます。
textjoinで区切って、splitで分ける
textjoin関数は、各セルに割り振られた値をつなげることができます。
例えば、A列に市町村名が飛び飛びに記載されています。
これを、C1から始まる1行目に空白を詰めて表示されるようにします。
C1のセルには以下の関数を入れています。
= split(textjoin(",",true,A1:A23),",",true)
まず、
textjoin(",",true,A1:A23)では、A1:A23の文字を空白を抜かしてカンマ(,)で結合します。
C1セルに
= textjoin(",",true,A1:A23)
と入力すると、C1セルに
三つ目の引数が結合する文字列の範囲です。今回はA1:A23の範囲となります。
一つ目の引数は、文字列を結合するときに使用する区切文字です。ダブルクォーテーションで囲んでください。
二つ目の引数で、Trueとすると、空白を無視します。
しかしこのままでは、空白を詰めたというよりも、文字を連結しただけになってしまいます。そこでスプレッドシートの独自関数であるsplitを使います。
split関数は以下のように使います。
= split(分割する前の文字列,どの文字を目印に文字列を分割するか,空白を削除するかどうか)
今回は
- 分割する前の文字列はtextjoin(",",True,A1:A23)
- カンマで分割。分割する目印となる文字はダブルクオーテーションで囲みます。今回は "," ですね。
- 空白を削除(今回は空白はありませんが)。Trueと入力します。
となります。
これらを合わせて
C1セルに
=split(textjoin(",",True,A1:A23),",",True)
とすれば冒頭の画像のように、C1セルに富山市、C2セルに高岡市というように空白を詰めて表示してくれます。
まとめると、カンマで区切って連結し、カンマを目印にセルに分割表示するというやり方になります。
今回は富山市や高岡市など、もとの文字列にカンマが含まれないため、区切り文字にカンマを用いましたが、区切り文字は元の文字列に含まれない文字にすると良いでしょう。
\\のように2文字使っても構いません。その場合は
=split(textjoin("//",True,A1:A23),"//",True)
となりますね。同じ結果になるはずです。
エクセルで空白セルを詰めて表示したい場合は、match関数やindex関数を駆使していたような記憶がありますが、スプレッドシートだと、とても楽にできますね。
おまけ
C1セルからQ1セルまで横向きに展開しましたが、これ、縦にしたいこともありますよね。
そんな場合には縦と横を入れ替えてくれるtransposeという関数を使います。
transposeはエクセルでもサポートしている関数です。
縦方向のセルの値なら横方向に並び替え、横方向のセルの値なら縦方向に並び替えてくれます。
今回の場合は、C1セルの関数にさらにtransposeをかませます。
= transpose(split(textjoin("//",True,A1:A23),"//",True))
C1~C15に並び替えができました。
ひとりごと
普段は、C#を使うことが多いのですが、そんなにレベルが高いプログラマーではないので、エクセルやスプレッドシートの関数って便利だなぁと思ってしまいます。
エクセル関数ベースのプログラミング言語なんていうのができたのですね。
Microsoft Power Fx
という言語らしいです(ITMediaの記事はこちら)。
Visual Studioとの親和性も高そうです。