「Googleスプレッドシートから見た！」Excel 14の新関数 -2 WRAPROWS / WRAPCOLS

mir

2022年12月9日 22:30

Excelに追加された 14の新関数を Googleスプレッドシートからの視点で検証する記事 2回目です。

関数の特徴
Excelでのメリット、デメリット、活用
Googleスプレッドシートの機能、関数との違い
Googleスプレッドシートでは無い機能をどう補うか

主にこの 4つの視点で検証していきます。

前回の記事

※この記事は Googleスプレッドシートに WRAPROWS / WRAPCOLS が輸入される前に執筆したものです。現在は Googleスプレッドシートでも WRAPROWS / WRAPCOLS が利用可能となっています。

EXCEL 14の新関数 WRAPROWS / WRAPCOLS

Excel 14の新関数 2回目は 配列折り返し系の WRAPROW、WRAPCOLSを取り上げます。

この2つは データの進行方向が縦か横かの違いだけなんで、2つまとめて検証しましょう。

WRAPROWS / WRAPCOLSの特徴

簡単に言うと、縦1列、または横1行のデータ（範囲、配列）を指定した列数、または行数で 折り返した形にする関数です。

これもスピルが前提となる関数ですね。

=WRAPROWS(vector, wrap_count, [pad_with])

=WRAPCOLS(vector, wrap_count, [pad_with])

vector ・・・　ベクタ（対象となる 1行、または1列の範囲・配列）
wrap_count ・・・　折り返す数（いくつで折り返すか）
pad_with ・・・　最後の余った箇所を埋める文字

WRAPROWS、WRAPCOLS どちらも引数は一緒ですね。

vecto は、数学のベクトルじゃなくてプログラミングの 1次元配列の意味合いの「ベクタ」だと思います。

A-Zを縦1列に並べたサンプルデータを使って検証してみましょう。ちなみにこれも数式で生成できます。

Excelで縦1列に A-Zを生成する式
=CHAR(SEQUENCE(26,1,65))

Excelで横1列に A-Zを生成する式
=CHAR(SEQUENCE(1,26,65))
※Excelはスピル対応バージョンである必要があります
※Googleスプレッドシートの場合は Arrayformulaが必要

※Excelのスピル範囲指定の場合 A1:A26は本当は A1# と記述すべきです

こんな感じで使えます。

WRAPROWSは 左から右にデータを並べ指定した数ごとに下に折り返す
WRAPCOLSは 上から下にデータを並べ指定した数ごとに 右に折り返す
最後の余った場所は指定した pad_with で埋めることが可能（指定しない場合は #N/A と表示）

WRAPROWとWRAPCOLSの違いわかりましたよね？
最初に言った通り、進む方向だけの違いなんです。

WRAPROWSをTRANSPOSEで縦横変換すりゃいいんじゃ？って気もします。

詳しい解説はオフィスタナカさんを参考に

Excelでのメリット、デメリット、活用

メリットはもちろん 簡単にデータを折り返した配列が生成が出来る点でしょう。

一方デメリット（これできたらよかったのに）は、特に思いつきません。使えるシーンが限られているってのもありますね。

しいてあげるなら、先に述べた通り関数を2つに分けないで 1つのWRAP関数 として進方向（縦か横か）は 引数で切り替えでも良かったんでは？と思います。

WRAPROWSの活用シーンは「いきなり答える備忘録」さんが、幾つか検証されていますので、そちらを参考に。

Excel方眼紙への転記や空白行の差し込みなど面白いですね。

過去記事の 1行カレンダー生成で、曜日（7列）単位での日付データ折り返し処理も、Googleスプレッドシートに WRAPROWSがあったら活用できたなと思います。

Googleスプレッドシートの機能、関数との違い

残念ながら WRAPROWS、WRAPCOLSと同じ、または似たような関数は Googleスプレッドシートにはありません。

他の関数の組み合わせで対応できなくはないけど、ちょっと手間がかかるんで、これは個人的には追加して欲しいかも。

でも、世間一般的の人が Excel、Googleスプレッドシートを普通に使う範囲では、あまり出番はないかもしれません。

タナカ氏も「実務ではどんなケースで役立つのでしょうか？」って書いてるくらいだし・・・。

実用的なケースを見つけたら、今後取り上げていきたいと思います。

Googleスプレッドシートでは無い機能をどう補うか

前回の Expandに続いてこれも実は過去記事でやっちゃってるんですよね。

上の活用例でもリンクを掲載している1行カレンダー生成の回に使った方法です。

LOOKUP(XLOOKUP)とSEQUENCEと組み合わせることで、WRAPROWと同じような処理が可能です。(他のやり方もあります）

過去記事の方法そのまんまってわけでもないんで、せっかくなんで QA方式でやってみましょう。

Q. Googleスプレッドシートで、 WRAPROW と同じ結果を返す式を作れるか？

vector （対象となる 1行、または1列の範囲・配列）、count （いくつで折り返すか）、pad （余った箇所を埋める文字）を引数とする

面白そうだなと思ったら、A-Zの縦1列データを使って作成してみてください。

↓↓
回答はここから。

↓↓

A. Googleスプレッドシートの既存関数で WRAPROWS を再現する

XLOOKUPを使う方法を重点的に解説します。
他のやり方も後半で触れます。

考え方、手順

既存関数の組み合わせなんで、そう簡単にはいきません。

対象となるデータを縦1列にする（横1行の場合は TRANSPOSE）
縦1列のデータに連番を振る
指定した折り返しの数（列数）のデータが入る用の連番配列を生成
XLOOKUPで連番をキーにデータを引っ張ってくる
WRAPROWSと同じ引数をLAMBDAで外に出す
WRAPCOLSの場合は、最後にTRANSPOSEで縦横変換

このように、それなりに手順を踏む必要があります。
順にみていきましょう。

1. 対象となるデータを縦1列にする（横1行の場合は TRANSPOSE）

WRAPROWS、WRAPCOLSは対象（vector）が縦 1列、または横 1行 どちらでも対応できます。（もちろん複数列・複数行はNG）

ここが結構面倒なんで、最初に横1行データの場合は TRANSPOSE で縦1列に変換して、その後の処理は縦1列を対象とした式にしちゃいましょう。

縦1列か横1行かの判断は範囲（配列）の幅が1かどうかで判別。ここは COLUMNS関数を使います。

=IF(COLUMNS(C1:AB1)=1,C1:AB1,IF(ROWS(C1:AB1)=1,TRANSPOSE(C1:AB1),NA()))

範囲 C1:AB1 の列数を確認し 1なら縦1列と判断しそのまま、列数が1以外なら念のため1行であるか？をROWS関数でチェックして TRANSPOSEで縦横変換しています。

1列・1行どちらでもない（複数列・複数行）の範囲を指定していた場合は、この時点で NA関数で NAを返します。

本当は IFSを使いたいところですが、返す結果の配列サイズの違いからかエラーになるので、ここはIFの入れ子で組み立ててます。

この引数 vector が 縦1列のみ受け付ける（横1行の時は TRANSPOSE を自分でつけてね）だと、この部分が丸々不要で式がだいぶ短くなります。これについては後で触れます。

2.縦1列のデータに連番を振る

1の式が長いんで、ここでLAMBDA（ラム）っておきましょう。

=LAMBDA( x , これ以降の数式を入れていく）(IF(COLUMNS(C1:AB1)=1,C1:AB1,IF(ROWS(C1:AB1)=1,TRANSPOSE(C1:AB1),NA())))

縦1列にしたデータ（x）に連番を振るのは xの行数を ROWSで取得し、SEQUENCEに入れればよいです。

SEQUENCE(ROWS(x))

3.指定した折り返しの数（列数）のデータが入る用の連番配列を生成

同じく SEQUENCEを使ってデータが入る用の箱（入れ物）を用意します。

折り返し数(count）を仮に 5 としましょう。この場合列数は 5 をそのまま入れればいいんですが、SEQUENCEは 行数を指定する必要があります。

行数は対象データの範囲の数 ROWS(x) を列数で割ればいいですね。ただし、SEQUENCEの引数は 小数を指定した場合に切り捨てとなる点に注意。

今回実際のデータはアルファベット A-Zなんで 26なんですが、単純に 26/5 (26÷5) としてしまうと、5.2 という小数値になり SEQUENCEの切り捨てルールで5と見なされ 5行5列の 1～25までの配列が生成されます。これだと 26個のデータを入れる箱として足りません。

だから、切り上げ処理をする関数 ROUNDUP （CEILINGでも可）を組み合わせています。

SEQUENCE(ROUNDUP(ROWS(x)/5),5)
または
SEQUENCE(CEILING(ROWS(x)/5),5)

ROUNDUP、CEILINGは第2引数を省略すると整数に切り上げる

4.XLOOKUPで連番をキーにデータを引っ張ってくる

これで準備が完了しました。いよいよデータを引っ張ってくる処理です。

これは LOOKUPでもいいんですが、「見つからない場合の値」を引数で処理できる新関数の XLOOKUPを使うのがベストでしょう。

いままでの手順 1～3 （わかりやすいように ①～③とする）で作った式を XLOOKUPの引数としてはめ込んでいくと

XLOOKUP( 検索値, 検索範囲, 結果範囲
↓
XLOOKUP( ③ , ② , ①
↓
XLOOKUP(
　SEQUENCE(ROUNDUP(ROWS(x)/5),5) ,
　SEQUENCE(ROWS(x)) ,
　x

上記のようになります。

③の検索値は配列なので、Arrayformulaをつける必要がありますね。

これを全部まとめれば完成。

=LAMBDA(x,ARRAYFORMULA(XLOOKUP(SEQUENCE(ROUNDUP(ROWS(x)/5),5),SEQUENCE(ROWS(x)),x,"🍎")))(IF(COLUMNS(C1:AB1)=1,C1:AB1,IF(ROWS(C1:AB1)=1,TRANSPOSE(C1:AB1),NA())))

指定した 5列折り返しのデータが生成されました。余った箇所は 🍎で埋めています。

引数が複雑なだけで XLOOKUPに関しては特殊な使い方をしているわけではないので、そこまで難しくはないと思いますが・・・。

XLOOKUP がまだよくわかってませーん、って人は過去に全4回の検証をしていますので参考に。

5. WRAPROWSと同じ引数をLAMBDAで外に出す

最後に WRAPROWS と同じ形にするために再度LAMBDAって、引数を外に出しましょう。

=LAMBDA(vector,count,pad,LAMBDA(x,ARRAYFORMULA(XLOOKUP(SEQUENCE(ROUNDUP(ROWS(x)/count),count),SEQUENCE(ROWS(x)),x,pad)))(IF(COLUMNS(vector)=1,vector,IF(ROWS(vector)=1,TRANSPOSE(vector),NA()))))(C1:AB1,5,"🍎")

引数字応じて変化しているのがわかりますね。
WRAPROWSの代替 完成です。

6. WRAPCOLSの場合は、最後にTRANSPOSEで縦横変換

では WRAPCOLSの方はどうでしょうか？

これは単純に先ほどの式を、最後に全体をTRANSPOSEして縦横を変えるだけでよいです。

=LAMBDA(vector,count,pad,LAMBDA(x,ARRAYFORMULA(TRANSPOSE(XLOOKUP(SEQUENCE(ROUNDUP(ROWS(x)/count),count),SEQUENCE(ROWS(x)),x,pad))))(IF(COLUMNS(vector)=1,vector,IF(ROWS(vector)=1,TRANSPOSE(vector),NA()))))(C1:AB1,5,"🍎")

※TRAPSPOSEはARRAYFORMULAの外側でもOK

同じ (C1:AB1,6,"🍎") という引数を渡しても WRAPCOLS用に作った式の方は、下方向に進んで 6行ごとに折り返しているのがわかります。

WRAPCOLSの代替も完成です。

実用的な折り返し処理を考える

今回は、「WRAPROWS / WRAPCOLS と同じ引数で同じ結果を返す式を作る」としたので、やや長くて複雑な式になっています。

でも手順1で書きましたが、 vector の 縦1列、横1行どちらでも対応できるって条件さえ外せば、もうちょっと短くすっきりした式になります。

「vectorが縦1列か横1行かを判別し、横1行なら縦に変換する」
この前提条件を

vector は縦1列の範囲・配列のみを受け付ける。
※横一行の場合は TRANSPOSEをつけて縦1列に変換する

こうしちゃえば、手順1を丸々カットできて、もう少し実用的な式になりますね。

=LAMBDA(vector,count,pad,
ARRAYFORMULA(XLOOKUP(SEQUENCE(ROUNDUP(ROWS(vector)/count),count),SEQUENCE(ROWS(vector)),vector,pad)))(A3:A28,5,"🍎")

実用的なWRAPROWS的処理の数式

LAMBDAも一つ減らせて、だいぶ短くなりました。
これで十分ですね。

別解：LAMBDA ヘルパー関数でも出来る

XLOOKUPの処理部分なんですが、単純に上から1,2,3,4…となっているものを引き当てるだけなんで、INDEX関数が使いたいところですよね？

INDEX関数がArrayformulaと組み合わせて使えない（スピらない）ので、XLOOKUPでの処理にしていますが、今だったら新関数の LAMBDA + ヘルパー関数を使えば INDEXをスピらせることが出来ます。

今回は活用例の少ない MAPを使った方法を紹介しておきます。

ヘルパー関数の MAPについても検証記事を書いてます。そちらも参考に。

=LAMBDA(vector,count,pad,
MAP(SEQUENCE(CEILING(ROWS(vector)/count),count),
LAMBDA(i,IFERROR(INDEX(vector,i,1),pad))))
(A3:A28,5,"🍎")

実用的なWRAPROWS的処理の数式（vectorは縦1列のみに対応）

SEQUENCE(CEILING(ROWS(vector)/count),count) で入れ物をつくる部分は一緒で、XLOOKUPの処理を MAP + INDEX に置き変えています。

これによって、さらに記述が短くなりシンプルな式になりました。
これがベストかも。

LAMBDAも無い時代の折り返し処理は、Arrayformula に LOOKUPとIFERRORを組み合わせてやっていましたが、XLOOKUP登場で式内で pad（エラー時に埋める値）を返せるようになり、さらにArrayformula不要で MAPを使って INDEXがスピるようになり。

本当にスプレッドシート関数は、日々進歩してますね！

ちなみに MAPより記述が長くなるんで割愛していますが、MAKEARRAYでも代替式を作れますし、pad（埋め文字）が空白を返す限定でよければ BYROWを使う方法もあります。

※BYROW,BYCOLは最新アップデートで配列を扱えるようになったので活用の幅が広がりました。

興味ある方は MAKEARRAY、BYROWバージョンにも挑戦してみてください。

WRAPROWS / WRAPCOLS Googleスプレッドシート代替式まとめ

まとめです。

WRAPROWS の代替式

=LAMBDA(vector,count,pad,
LAMBDA(x,ARRAYFORMULA(XLOOKUP(SEQUENCE(ROUNDUP(ROWS(x)/count),count),SEQUENCE(ROWS(x)),x,pad)))(IF(COLUMNS(vector)=1,vector,IF(ROWS(vector)=1,TRANSPOSE(vector),NA()))))(C1:AB1,5,"🍎")

WRAPCOLS の代替式

=LAMBDA(vector,count,pad,
LAMBDA(x,ARRAYFORMULA(TRANSPOSE(XLOOKUP(SEQUENCE(ROUNDUP(ROWS(x)/count),count),SEQUENCE(ROWS(x)),x,pad))))(IF(COLUMNS(vector)=1,vector,IF(ROWS(vector)=1,TRANSPOSE(vector),NA()))))(C1:AB1,5,"🍎")

前提条件アリだけど実用的な WRAPROWSの代替式

=LAMBDA(vector,count,pad,
MAP(SEQUENCE(CEILING(ROWS(vector)/count),count),LAMBDA(i,IFERROR(INDEX(vector,i,1),pad))))(A3:A28,5,"🍎")

vectorは縦1列のみ対応、WRAPCOLS化させたい場合はこれを再度TRANSPOSE

代替は出来たけど、これをさらに他の関数と組み合わせて処理するケースだとゲンナリしますね。COLSはなくてもいいから、WRAPROWS だけでも輸入希望です！

今回の検証は以上となります。残りは11関数。

鎌倉殿の13人はいよいよ大詰め 12月18日が最終回ですが、EXCEL殿の14の新関数シリーズは まだまだ続きます！

■このシリーズの次の記事

いいなと思ったら応援しよう！

チップ大歓迎です。やる気がアップしますｗ