「Googleスプレッドシートから見た！」Excel 14の新関数 -1 EXPAND

mir

2022年12月3日 16:25

旬なネタというには少し遅いかもしれませんが、Excel ではテキスト操作・配列操作の 14の新関数が 2022年8月から一般ユーザーも利用できるようになりました。

365ユーザーだけでなく 無料版の Excelオンライン でも使えるようになったので、Googleスプレッドシート使いの mirも検証してみようかなと。

別れた彼女（EXCEL）とこっそり再開ではなく、あくまでも Googleスプレッドシート視点での検証ですｗ

Excel新関数を理解したうえで、Googleスプレッドシート上でこれらの関数の処理を

どう代替が出来るのか？

という考察が中心になります。

だから Excelユーザー向けではありません。

べ、別に Excelに新関数いっぱい追加されて羨ましいとか、そんなんじゃないんだからね！（というツンデレ要素もあるかもｗ）

前回の記事　※今回とは別のネタ Googleスプレッドシートの最新機能タイムラインビューについての記事です

Excel 14の新関数概要

今回の 14の新関数は、大きく テキスト操作系、配列操作系の2つに分けることが出来ます。TEXTから始まる3つがテキスト操作系、残りの11の関数が配列操作系です。

■テキスト操作系
TEXTBEFORE
TEXTAFTER
TEXTSPLIT

■配列操作系
VSTACK
HSTACK
TOROW
TOCOL
WRAPROWS
WRAPCOLS
TAKE
DROP
CHOOSEROWS
CHOOSECOLS
EXPAND

テキスト操作系は単体で使っても便利な関数なんですが、配列操作系は単体で使うというよりは 他の関数と組み合わせで使うイメージですね。

オフィスTANAKAさんはメインディッシュというよりは調味料的と表現してましたが、確かにその通りだなと思います。

なんだよ調味料かー。と侮るなかれ、

昔は胡椒を求めて争いがおこったくらい調味料は重要なエッセンスで、これらの新関数をスピル、LAMBDA・ヘルパー関数と組み合わせる（調理する）ことで、今までExcelでは作業列、作業シートを使わないと出来なかった処理が、式内で完結できるようになったわけです。

こりゃ関数の宝石箱やで～。

これって EXCEL的には 大きな進歩なんですよね！（1行数式内での処理は Googleスプレッドシートに後れをとっていたので）

スピル機能、LAMBDA関数ときたけど

Excel職人から Googleスプレッドシート使いにジョブチェンジしてから、めっきり Excelに触れる機会が減りまして、たまにExcelで作業すると「あれ、これExcelで出来なかったんだ」ってなる時があります。

買い切り版はともかく Web版ならスピル系関数も使えるし、GoogleスプレッドシートとExcelお互いに関数を輸入しあってるんで、操作感や出来ることは近づいてきたつもりでいても、やっぱり2人は違う立場、違う境遇。

「もしかしたらExcel… お前とオレが… 逆だったかもしれねェ…」

ってセリフが脳内再生されますねｗ

意外と不便だった文字列操作系

新関数に TEXTSPLIT があったことで、そういや SPLIT はExcelになかったのねって気づきましたｗ

Excel側にも TEXTJOINは結構前からあったんで忘れてましたが、 SPLIT（分割）処理は出来なかったんですね。

区切り文字で分割する TEXTSPLITに加え、 TEXTBEFORE や TEXTAFTER といった区切り文字の前後を取り出せる関数も追加されました。

正規表現系の関数は無いものの、これらの活用で Excelでの「セル内の文字列から一部分を取得する処理」が、だいぶ柔軟かつ簡単に出来るようになったんじゃないでしょうか。

ようやく SUBSTITUTEと REPTで指定した文字を 空白100個に置き換えて、バッファみてMIDして最後にTRIMで空白除去なんていう、古の職人技に頼らなくても済みますねｗ（初めてこの方法知った時は、先人の知恵に感動したもんですが）

Excelの中カッコによる配列結合は範囲、数式には使えなかった

Googleスプレッドシートを使う上では、これないともう生きていけない！ってくらい重宝している 中カッコ{ } による範囲・配列の縦横結合ですが、Excelだと微妙に挙動が違っています。

数値や文字列には使えます。だからスピル対応バージョンのExcelなら、

={1,2}
={"あ","い","う"}
={1;2}

として配列を返すことが可能です。
これを式内で引数としても活用できます。

でも、

={A1,C1}　単体セル同士
={A1:A3,C1:C3}　セル範囲の連結
={{1,2},{"あ,"い"}}　中カッコをネストした記述
={SEQUENCE(2),SEQUENCE(2)}　関数の連結

これらは Excelでは全部エラー

これらはGoogleスプレドシートでは普通に使える記述なんですが、Excelでは使えずエラーとなってしまいます。

これ出来ないって、かなり不便だわー。

このような今までの Excelの文字列操作、配列操作の不満（物足りなさ）を解決すべく登場した、いや 馳せ参じた 14の新関数は、まさに

「Excel殿の 14人」

と言えるでしょうｗ

新関数を使うことで、今まで複雑な数式で 天文学的な時間を要していた 処理が「トンットーン拍子やろ？」って感じで簡単に出来るようになったわけです。

「おつかれ、今までのオレ。」

と涙を流しちゃうかもｗｗ

Googleスプレッドシートでは前から出来ていた。でも・・・

追加された新関数は、文字列操作にしろ配列処理にしろ Googleスプレッドシートでは以前から普通に使えてた機能、もしくは存在していた関数の組み合わせで対応できるものがほとんどです。

だからLAMBDAみたいに Googleスプレッドシートに輸入されたからといって、大きな変化が起こるわけではありません。

でも、さすがに後から実装してきただけあって、いい感じの引数や機能があるんですよねー。でも、あえて互換性をなくしに来てるような気も・・・。

Excel新関数で出来る処理の中には、Googleスプレッドシートでは苦労するものもあるんで、やっぱりちょっと羨ましいかもｗ

というわけで、Excelの14の新関数を

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

このような Googleスプレッドシート視点で検証していきましょう！

Excel視点の新関数解説は、おなじみ Office TANAKAさんがわかりやすいです。

EXCEL 14の新関数 1. EXPAND関数

さて、一発目どれにしようかなと考えましたが、今回は初回ってことで前振りが長くなっちゃったんで、軽めの EXPAND関数を取り上げようかなと。

EXPAND関数の特徴

14の関数は、結構ニコイチというかペアになってるんですが、このEXPANDだけは孤高というか対になる関数がないんですよね。

無理にペアを組ませるとしたら、Googleスプレッドシートにしかない関数ですが ARRAY_CONSTRAIN でしょうか。

ARRAY_CONSTRAIN が配列を縮小する関数なのに対して、EXPANDはその名の通り配列を拡張する関数です。

=Expand(array, rows, [columns], [pad_with])

array ・・・対象の範囲・配列
rows ・・・行数（array の行数以上を指定）
columns ・・・列数（array の列数以上を指定）
pad_with ・・・拡張したセルを埋める文字列

第1引数の範囲・配列は、セル範囲はもちろん 数式で生成した配列でもOK

たとえばこんな感じも使えます。

=EXPAND(SEQUENCE(5,5),7,7,"外堀")

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

このEXPAND関数のメリットですが、当然 配列を拡張できることです。

具体的にどんな需要があるのか はわかりませんが、たとえば縦横のサイズが違う幾つかの配列があって、それらを全部同じサイズに揃えたい！って時には使えますね。

LAMBDA + MAP 関数と合わせて使えるケースがあるかも？

EXPANDのデメリットは 逆、つまり配列の縮小ができないことでしょうか。

対象の配列の行数、または列数以下を指定するとエラーになります。

これ別に対応できたんじゃね？

って思いますけどね。

配列の一部を切り出したり、切り捨てる新関数 TAKE・DROPと被るから忖度したってことでしょうか？？

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

残念ながら Googleスプレッドシートには、この EXPANDに該当する関数がありません。

とはいえ、TANAKA氏が、「はっきり言って、これを便利に使うシーンが思い浮かびません。」というくらい、使いどころが微妙な関数。

応用ネタとしては、中身が同じ配列を簡単に生成できるってのがあるけど、これも使いどころがあるのか不明。

まぁ中身が同じ文字列の配列生成は LAMBDA + MAKEARRAY でも出来るし、SEQUENCEをIFと合わせる方法もあるんで別に・・・って感じですが。

■すべて同じ値の配列を作成する式（Excel）
=EXPAND("^_^",10,4,"^_^")
※Googleスプレッドシートには無い

=IF(SEQUENCE(10,4)>0,"^_^",)
※Googleスプレッドシートの場合は Arrayformulaが必要

=MAKEARRAY(10,4,LAMBDA(r,c,"^_^"))
※Googleスプレドシートも同様

さすがに記述は EXPANDが一番短くてシンプルですが、無くても困らないような気も・・・。

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

この EXCELにしかない EXPAND関数を、どうやって Googleスプレッドシートで実現させるか。

当然、既存関数の組み合わせとなります。GASを使う必要はありません。

でもこれ、実は LAMBDAヘルパー関数の検証記事２回目、MAKEARRAYの回で既に検証済みなんです。

  =LAMBDA(arr,makerow,makecol,padd,
    MAKEARRAY(makerow,makecol,
      LAMBDA(r,c,
        IFERROR(
          INDEX(arr,r,c),padd)
        )
      )
    )
  (A1:C3,I8,I9,I10)

ネタの使いまわし感がありますがｗ一応、改めて見直してみましょう。

EXPAND の Googleスプレッドシート代替数式

=LAMBDA(arr,r,c,pad,MAKEARRAY(r,c,LAMBDA(r,c,IFERROR(INDEX(arr,r,c),pad))))(A2:D5,8,3,"×")

式としては単純で、MAKEARRAY で指定したサイズの配列を生成して、 r（行番号） c（列番号）を使って対象となる範囲 arr の各セルから INDEX(arr, r, c) で値を返す。

もし対象の配列より大きい r、c （行番号、列番号）だった場合は、INDEXはエラーを返すので、IFERROR( INDEX( arr, r, c ), pad ) として、INDEXの外を参照したら pad(埋める文字）を返すとしています。

EXPANDと同じ引数の形にする為に最後に全体をLAMBDA（ラム）って、
arr, r, c, pad を外に出しています。

完全にEXPAND と同じではなく、こちらは 配列の縮小にも対応できるんで、ぶっちゃけ上位互換ですｗ

そのまま EXPANDって名前付き関数にしちゃっても良いですね。

というわけで、式を作るのは面倒ですが Googleスプレッドシートは EXPANDがなくても代替できることが確認できました。

でも、他の EXCEL新関数からは

「くくく・・、やつは Excel 14の新関数の中でも最弱」

って声が聞こえてきそうな

俺たちの Excel 14の新関数との闘いはこれからだ！

mir先生の次回作をご期待ください（完）
敵の幹部を一人倒して打ち切り・・・とはなりません。続きます。

別に新関数と戦ってるわけでもありませんｗ

こんな感じで次回も EXCELの新関数を Googleスプレッドシート視点で、

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

という 4つの切り口で検証していきます。

さすがに毎週1つずつで 14週も引っ張ったら打ち切りになりそうですがｗ

一回で2つくらいのペースで書いていこうかなと。

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

この記事が気に入ったらサポートをしてみませんか？

「Googleスプレッドシートから見た！」Excel 14の新関数 -1 EXPAND

Excel 14の新関数 概要

スピル機能、LAMBDA関数ときたけど

意外と不便だった文字列操作系

Excelの 中カッコによる配列結合 は 範囲、数式には使えなかった

Googleスプレッドシートでは 前から出来ていた。でも・・・