MID関数とSEAQUENCE関数で縦書き文章を作成

猫も杓子もエクセルクイズである。

今回は風柳@furyuteiさんが出題されていたこちらからの学びを備忘したいと思います。

#エクセルクイズ #スピル #54字の物語
入力欄(R4)に入れた文字列の先頭54字（改行は空白に置換して1文字と数える・サロゲートペア等は考慮しない）を画像のようにあてはめたいとき、セルC3に入れる数式を考えてください。 pic.twitter.com/1lALH4nnGG

— 風柳 (@furyutei) 2020年12月10日

取り敢えず自分の回答

=TRANSPOSE(
MID(
   MID(SUBSTITUTE(R4,CHAR(10)," "),
      (6-INT(SEQUENCE(11,,3)/2))*9+1,
      9*MOD(SEQUENCE(11,,3),2)),
   SEQUENCE(1,9),
   1)

回答の意図

以下のような思考の流れで関数を組み立てました。

• 2つ目のMID関数：文字列抽出なのでMid関数だろう
  • (6-INT～：でも後ろから文字を抽出しないといけないので減少する数列を作らなきゃ
  • (9*MOD～：解答欄が１行飛ばしだから、１列飛ばしで行列を作らなきゃ
• １つめのMID関数：このままでは１行単位で抽出してるから１文字ずつに分解しなきゃ
• TRANSPOSE関数：このままだと横書きだから行列交換しなきゃ

２つめのMID関数

ここでは同じ数値が続いたらが減少する数列を作るために減算と割算で工夫した。
もう１つ工夫したのはMID関数の第２引数で９，０が繰り返される数列を使用ている箇所。
MID関数の第２引数(何文字目から抽出するか)に０を指定するとエラーになるが、第３引数(何文字抽出するか)に０を指定すると空白が返ってくることを利用している。

１つ目のMID関数

MID関数２つ目の時点ではまだ抽出したい箇所を行単位で抽出した状態なので、行毎のデータを１文字ずつに分割する必要がある。

ここでSEAQUENCE(9)と使用してしまうと、下記のような解が返ってきて思ったような動作にならない。

１行目の１文字目、２行目の２文字目・・・と返ってきている。 １０行目・１１行目はSEAQUENCEで９までしか指定していないのでエラーになっている。

SEAQUENCE(1,9)と指定することでそれぞれの行で１文字目～９文字目までを分割した答えが返ってきている。

TRANSPOSE関数

現状では横書きのままなので縦書きにする必要がある。
行列入れ替える時はTRANSPOSE関数である。
このように行列が入れ替わった解が返ってきている。

エレガントな解答

ここで解答の方法としては同じ方向性なのに恐ろしくエレガントな数式を紹介したい。
SK@Excel_beginnerさんのこちらの解答です。

=MID(SUBSTITUTE(B2,CHAR(10)," "),
   SEQUENCE(,11,5,-0.5)*9+SEQUENCE(9),
   MOD(SEQUENCE(,11),2))

IF()をなくせた。もっと短くしたいです。 pic.twitter.com/lIyxLS5Wz5

— SK (@Excel_beginner) 2020年12月11日

SEAQUENCE関数+SEAQUENCE関数で行列

自分の方法ではまず行単位で抽出したあとに行を１文字単位で抽出したが、SKさんの解答では１回で１文字ずつ抽出をしている。

SEAQUENCE関数では普通に行列を作成することが出来るが、今回使用する大きさの行列を作成するとこのような数値の並びになってしまい、今回の回答には使用することが出来ない。

必要な行列は左から右に向かって数値が下がって行き、尚且上から下に向かって数値が増える行列。

このような場合SEAQUENCE関数の足し算で解決することが出来る。
１つ目のSEAQUENCE関数(オレンジ)に対して２つ目のSEAQUENCE関数(青色)を足すことで狙い通りの行列を作成することが出来る。

１つ目のSEAQUENCE関数の増分を-0.5刻みにすることで、実質1行飛ばしの行列を作成することと左から右に向かって減少する行列を１つのSEAQUENCE関数で実現している。

まとめ

今回の回答がちゃんと理解できればSEAQUENCE関数や行列をもっと柔軟に使えるようになります。