Googleスプレッドシート SEQUENCE関数 超応用例2（配列操作を制す）
シリーズの途中って感じですが、2023年最後の note です。
Googleスプレッドシートの SEQUENCE関数にフォーカスした記事の2回目となります。
最もシンプルに縦横自由なサイズの連番配列を生成できる SEQUENCE関数、その活用方法について今回もお題にチャレンジする形式で学んでいきましょう。
前回は基本の理解と活用例を幾つか紹介しました。
今回のお題に関しても、以下の SEQUENCE関数 お題演習用シート（有料）に追加掲載しております。
お題用演習シートで Googleスプレッドシートの関数の勉強したい！って人は、必ずこの noteを読む前に先にお題用演習シートを入手してください。（このnote読んだらネタバレになるんで）
ExcelのSEQUENCE関数との違い
今回はSEQUENCE関数の超応用例にチャレンジしていきたいと思いますが、その前に 少しExcelのSEQUENCE関数 についても触れておきましょう。
当noteは Googleスプレッドシートの SEQUENCE関数について紹介していますが、SEQUENCE関数は スピル対応版の Excelでも利用できる関数です。
公式では Excel2021、もしくは 365、Web版で利用可能となってるのですが、なぜか Excel2019でも スピルが動くケースがあって、SEQUENCEが使える（ こいつ…動くぞ！ ）って声もあります。
うちのExcel2019、調子いいときは Excel2021の動きなのに一度閉じて開きなおしたら やっぱ LETとかSORTとか無理っすって 2019に戻ってたりする。
— mir (@mir_for_note) December 24, 2023
謎の挙動～。 pic.twitter.com/Gr9D2krDbS
とはいえ安定した動きとは言えないので、Excel2019で使うのはやめておいた方が良さそうです。
ExcelのSEQUENCE関数は、Googleスプレッドシートとは少し挙動が違うんで、その違いを中心に見ていきましょう。
ExcelのSEQUENCEは 第1引数、第2引数が省略可能
=SEQUENCE(,10)
Googleスプレッドシートの SEQUENCE関数は 第1引数は省略してしまうと 0と見なされ #NUM! エラーとなります。
一方、Excelの場合は 省略時 は 1と扱われるので省略が可能です。ちなみに第3引数、第4引数があるケースでの 第2引数も 省略可能で
=SEQUENCE(10,,2,2)
こういった記述ができます。
1文字でも式を短くしたい！って時には良いですね。
ExcelのSEQUENCEは 小数の利用が可能
=SEQUENCE(10,,0.5,0.5)
Googleスプレッドシートでは工夫が必要だった 0.5ずつ増えるといった増分量が小数のケースですが、なんと Excelの場合は 普通に 第3引数、第4引数に小数を指定することが可能なんです。
うーん、これはうらやまｗ
ちなみに関数の挿入ダイアログ ボックスだと 増分量は 目盛り という名称になっています。
上記のような違いはありますが、基本的には GoogleスプレッドシートのSEQUENCE関数と同じように使えるという認識でOKです。
SEQUENCE関数の活用例 つづき
超応用例の前に、前回取り上げきれなかったSEQUENCE関数の活用例をもう少しだけ紹介したいと思います。
Q1. 掛け算 九九表を簡単に生成したい
上のような掛け算九九表を A1セルに式をいれるだけで生成したい時、どんな式を作成すればよいでしょうか？
これは 割と簡単ですね。考えてみましょう！
ここから回答です。
↓↓↓
A1. 掛け算 九九表を簡単に生成する
回答です。
=ARRAYFORMULA(SEQUENCE(9)*SEQUENCE(1,9))
シンプルに
SEQUENCE(9)で 縦方向に 生成した1～9 と
SEQUENCE(1,9)で 横方向に生成した 1～9 を
掛け合わせばＯＫです。
ただし、配列処理で個々の掛け算を行う為に Arrayformulaをつける必要があります。
ちなみに Excelだったら 自動でスピる上に 第1引数の省略が出来るんで
=SEQUENCE(9)*SEQUENCE(,9)
こんなにシンプルになります。
しかし、この式で 掛け算九九表できました～と言っても、
いやいや 九九表はちゃんと左上が空欄で1の段もないとダメだ！
と、言う人がいたりします。
以下のような1の段もあって、左上が空白の表を求められると面倒ですね。
こちらに関しては 中カッコで配列を連結できる Googleスプレッドシートの方がちょっとだけ短く書けるかなと思います。
=ARRAYFORMULA(LET(a,SEQUENCE(9),b,SEQUENCE(1,9),{"",b;a,a*b}))
ちなみに SEQUENCE回なんで b を SEQUENCE(1,9)としてますが、ここは TOROW(a) とした方が少し文字数を減らすことができます。
このように掛け算九九表生成にもSEQUENCE関数は活用できます。
Q2. 文字列を1文字ずつ取り出して配列化したい
これは以前のnoteでもチラっと書いたことがあるネタです。
A2 セル内のテキストから1文字ずつ取り出して横に配列として展開したい時、A4にどんな式を入れればよいでしょうか？
知ってる人は知ってる問題ですが、知らない人にはちとハードルが高いかもしれません。まずは考えてみましょう！
ここから回答です。
↓↓↓
A2. 文字列を1文字ずつ取り出して配列化する
回答です。
=ARRAYFORMULA(MID(A2,SEQUENCE(1,LEN(A2)),1))
SPLIT関数が空白区切りに対応していれば、サクッといけちゃうんですが、残念ながらSPLIT関数は空白以外の区切り文字の指定が必須となっています。
というわけで、「区切る」じゃなくて「取り出す」という方向で考えましょう。
テキストから部分的に文字を取り出す関数は、左からならLEFT関数、右からならRIGHT関数ですが、自由に取り出す開始位置を指定できる MID関数を今回は使います。
MID関数の構文は
MID(文字列, 開始位置, セグメントの長さ)
このようになっています。
文字列は 入力されているセル A2を参照すれば良いですね。
第3引数の長さは1文字ずつ取り出すので 1です。
で、開始位置を 1文字目から順に 2文字目、3文字目とズラしていけばよいので、ここで SEQUENCE関数の出番です。
最後が 何文字目になるかは、テキストの文字数がわかれば良いので、LEN関数で LEN(A2) とすることで取得できます。
今回は横に展開したいのえ、SEQUENCEの第2引数を LEN(A2)とすればOK
このように 1文字目から順に10文字目までをMIDで取得すればよいわけです。配列処理となるので Arrayformulaを忘れずに！
sort関数のお題でも登場しましたが、たまにこの処理使うことがあります。
ちなみにSEQUENCE関数を使わないので余談になりますが、別解として
このように 無理やり1文字毎に区切り文字を差し込んでから SPLITで分割という方法もあります。
Q3. 1から25までの数字をランダムに 5x5 に並べたい
1～25までの数字を 上のように 5x5 でランダムに並べた配列（重複した数値なし）を生成したい！
こんな時はどうすればよいでしょうか？
ランダムってことなんで RAND系（乱数系）の関数を使いそうってのは、なんとなく予想できますよね。
ちなみに この問題、Excelユーザーからすると
重複ありでいいなら RANDARRAY関数で一撃なんだけどな～。
ってなるかと思います。
ExcelのRANDARRAY関数は
RANDARRAY( 行数, 列数, 最小値, 最大値, 整数指定 )
という引数で、最小値や最大値、さらに整数に限定するという指定が可能だからです。
一方、Googleスプレッドシートの RANDARRAY関数は
RANDARRAY（行数, 列数）
このように引数は2つのみでかなりシンプルです。
当然、最大値や最小値の指定、整数のみに限定といったコントロールは出来ません。
Excelは Googleスプレッドシートの配列系関数をいい感じに改良して投入してくること多いですね。 くーって言いたくなっちゃうｗ
いずれにせよ 今回は 重複はナシなので、ExcelでもRANDARRAYは使えません。
では、どうやって生成するか？考えてみましょう。
ここから回答です。
↓↓↓
A3. 1から25までの数字をランダムに 5x5 に並べる
回答です。
=WRAPROWS(SORT(SEQUENCE(25),RANDARRAY(25),1),5)
まず最初から 5x5 に 1～25を配置することを考えず、縦1列に1～25を出力したものを用意しましょう。
そして、同じサイズ 25行1列の 乱数の配列を RANDARRAYで生成します。
乱数 RANDARRAY(25)を キーとして SORT関数で 1～25を並び替えることで、ランダムな並びの 1～25を 縦1列で生成することができます。
この方法 以前 SORT関数について書いた noteでも解説しております。
最後の5x5 サイズへの成形ですが、自由なサイズで折り返して出力することが出きるのはSEQUENCE関数の魅力の一つではあるんですが、ここはあえて折り返しを WRAPROWSに任せちゃいましょう。
これで完成です。
ちなみに WRAPROWSのような配列を折り返せる最新関数が登場する前は、SORTとRANDARRAYで並び替えた 縦1列の1～25 から、SEQUENCE(5,5)の配列を生成して LOOKUP関数 でピックアップするという処理をしていました。
=ARRAYFORMULA(LOOKUP(SEQUENCE(5,5),SEQUENCE(25),SORT(SEQUENCE(25),RANDARRAY(25),1)))
新関数登場前は、今以上にSEQUENCEを使いこなすことが重要だったのがわかりますね。
この1～〇までのランダムな数字を並び替えて ビンゴカードを作る方法を 過去の noteで紹介しています。
また、SEQUENCE回なので あくまで余談となりますが、 RANDARRAYで生成する乱数配列は、ほぼ（99%以上）重複はないので、SEQUENCE関数を使わずに
RANNDARRAY関数 ＋ RANK関数
で実現する方法もあります。
=ARRAYFORMULA(LET(a,RANDARRAY(5,5),RANK(a,a)))
【オマケ】Googleスプレッドシートで Excelの RANDARRAY(5,5,1,25,TRUE) を代替するには？
同じく余談とはなりますが、Googleスプレッドシートで、重複アリの 1～25までのランダムな数字を 5×5のサイズで出力する、つまり Excel の RANDARRAY(5,5,1,25,TRUE) を代替するには どうすればよいか？
これについても触れておきましょう。
これは RANDBETWEEN関数を使って対処します。
RANDARRAY（行数, 列数）
ただし、Arrayformulaと組み合わせてもうまくいきません。
=ARRAYFORMULA(IF(SEQUENCE(5,5),RANDBETWEEN(1,25)))
たとえば上のような式を作成しても
このように全てのセルが同じ乱数を返してしまいます。
これを解決するのが LAMBDAヘルパー関数です。
今回の場合はMAPかMAKEARRYを使うのが良いでしょう。
=MAKEARRAY(5,5,LAMBDA(r,c,RANDBETWEEN(1,25)))
=MAP(SEQUENCE(5,5),LAMBDA(v,RANDBETWEEN(1,25)))
このように 重複ありで1～25の数字の 5×5の 乱数配列が生成できました。
SEQUENCE関数 超応用例 4選
もう少し難易度を上げたSEQUENCE関数のお題、超応用例を3つ程いってみましょう。
正直SEQUENCE関数以外の難易度が上がってるだけなんで、SEQUENCE関数の超応用例と言ってよいかは微妙なんですが・・・。
Q4. 生成した1ヶ月の日付をカレンダー表示（7日折り返し）にしたい
前回、SEQUENCEを使って縦1列での1ヶ月の日付を生成するお題をやりました。
これを 縦1列ではなく、カレンダー表記（一番左が日曜日で7日折り返し）で以下のように出力したいってお題です。
曜日（1行目）は入力されているものとして、A2セルに一つの式を入れるだけで カレンダーの1ヶ月分の日付を生成するにはどうすればよいでしょうか？
考えてみましょう！
ここから回答です。
↓↓↓
A4. 生成した1ヶ月の日付をカレンダー表示（7日折り返し）にする（基本の理解）
回答・・・の前にSEQUENCEの折り返しの基本と対処の考え方に触れておきましょう。
まず前提としてシート上で扱える（生成できる）配列は、長方形（正方形も含む）の形になっているもののみです。
画像の下のような一部が欠けた形の配列は生成できません。
そして、SEQUENCE関数は
単体では数値以外の値は返せない
開始位置は必ず配列の一番左上
終了位置は必ず配列の一番右下
となります。
画像の右2つのような開始位置が左上ではない（終了位置が右下ではない）、つまり先頭や末尾が空白となるような 配列は生成できません。
このような開始位置や終了位置を調整した数値配列を生成したい場合は
=ARRAYFORMULA(IF((SEQUENCE(8,2,0)>0)*(SEQUENCE(8,2,0)<15),SEQUENCE(8,2,0),))
↑ このようにSEQUENCEの第3引数の開始値を調整した上で、IF関数で左上と右下を条件分岐で空白とするか、
もう一つの方法としては
=WRAPROWS(VSTACK("",SEQUENCE(14)),2,)
↑ SEQUENCEでは縦1列の1～14を用意して、先頭に差し込む分の空白を別途用意、これをVSTACKで連結。
最後に折り返しと末尾の空白は WRAPROWSに任せる。といった方法になります。WRAPROWSは第3引数で 折り返しで余った部分の余白埋めをする値を指定することができるので、 ,) このように何も入れなければ空白を指定した扱いとなります。
今回のカレンダー表記とする方法は、折り返しをSEQUENCEで行うか否かで、変わってくるということです。
それぞれの解法を見ていきましょう。
A4-1. 生成した1ヶ月の日付をカレンダー表示（7日折り返し）にする（回答1）
回答です。
回答1 折り返しをSEQUENCE関数で行う
=LET(
start,"2023/12/1",
padd,WEEKDAY(start)-1,
x,SEQUENCE(CEILING((DAY(EOMONTH(start,0))+padd)/7),7,start-padd),
ARRAYFORMULA(IF(MONTH(start)=MONTH(x),x,))
)1つ目は SEQUENCE関数で7日折り返し配列を生成する方法です。
前回の縦1列で1ヶ月の日付を生成する式
=LET(start,"2023/12/1",SEQUENCE(DAY(EOMONTH(start,0)),1,start))
こちらの開始値 を 1日以前の直近の日曜日に変更必要があるので、WEEKDAY関数を使います。
WEEKDAY関数は曜日を数値で返す関数です。第2引数で調整できますが、基本は
日 1
月 2
火 3
水 4
木 5
金 6
土 7
このようになっています。
だから 1日が 日曜日の時は ズレ 0で、1日が月曜日なら ズレを -1 …としていけばよいので
開始日 - (WEEKDAY(開始日)-1)
これで カレンダーの開始日である、1日 以前の直近の日曜日の日付（1日が日曜日の時は1日）を取得できます。
この WEEKDAY(開始日)-1 をLET関数で padd とおくと
=LET(
start,"2023/12/1",
padd,WEEKDAY(start)-1,
SEQUENCEの第3引数の 開始値は
start-padd
となります。
第2引数の列数は 曜日分の 7ですね。
そして 第1引数ですが、日数に調整分の padd を加算して 7で割ればよいのですが
(DAY(EOMONTH(start,0)) + padd ) / 7
これだと、例えば 2022年11月の場合、11月の日数 30
padd（1日の前の日数） 3
を足して7で割ると 33/7 = 4.714…
と小数値になります。
前回の SEQUENCE関数の基本でやりましたが、引数が小数値の場合は 小数部分を切り捨てで 整数値として扱われるので 4.714… ⇒ 4 となって
このように月末まで表示されなくなってしまいます。
つまり小数値は式内で切り上げが必要ってことです。
切り上げは ROUNDUP関数を使ってもいいんですが、なんとなーく CEILING関数を使った方が玄人っぽい気がしますｗ
これで 必要な部分は出力できました。
この 前月と次月の日付も含んだ状態の結果を xと置きます。
x,SEQUENCE(CEILING((DAY(EOMONTH(start,0))+padd)/7),7,start-padd)
最後に 各セルの日付の月が startの月と違う場合は空白を返すようにIF関数で分岐させれば完成。
ARRAYFORMULA(IF(MONTH(start)=MONTH(x),x,))
変数を start とか padd と置いた分文字数は増えていますが、インデントなしで 166文字の式で結構長いですね。
これでも LET関数のおかげで 無駄な重複記述が簡略化され 昔よりは短くかけるようになりました。
A4-2. 生成した1ヶ月の日付をカレンダー表示（7日折り返し）にする（回答2）
もう1つは 前回の1列の日付を ベースに折り返しを WRAPROWSで処理する方法です。
回答2 折り返しをWRAPROWS関数で行う
=LET(
start,"2023/12/1",
padd,TOCOL(ARRAYFORMULA(IF(SEQUENCE(WEEKDAY(start)-1),)),2),
WRAPROWS({padd;SEQUENCE(DAY(EOMONTH(start,0)),1,start)},7,)
)前回作成した 1列に1ヶ月の日付を出力する式
SEQUENCE(DAY(EOMONTH(start,0)),1,start)
これはこのまま活かし、この上に 中カッコで 開始位置調整用の空白配列（padd）を上に連結して最後にWRAPROWS関数で 7列ごとに折り返す
WRAPROWS({padd;SEQUENCE(DAY(EOMONTH(start,0)),1,start)},7,)
という処理です。
ポイントは 開始位置調整用の 空白の配列
指定した数の 空白生成は WRAPCOLS(,WEEKDAY(start)-1,) の方が短いし、普段はこっち使うんですが、
せっかくの SEQUENCE回なんで
ARRAYFORMULA(IF(SEQUENCE(WEEKDAY(start)-1),)
という式を使ってみました。
SEQUENCE(WEEKDAY(start)-1) は 1以上の数値の配列となるので すべて true 扱いとなり WEEKDAY(start)-1 個分の 空白の配列が生成されます。FALSEの時を指定する必要はありません。
ただし、ここで注意点があって
2023/10/01 のように、1日が 日曜日だった場合、WEEKDAY(start)-1 が 0となってしまう為、SEQUENCEがエラーを返し その後の式に影響が出てしまいます。
この1日が日曜日だった時のエラー処理する為に TOCOL関数を使っています。
TOCOL(ARRAYFORMULA(IF(SEQUENCE(WEEKDAY(start)-1),)),2)
TOCOLの引数を2とすることで、空白は削除せずエラーだけ削除 となり1日が日曜日のケースに対応させたわけです。
式の長さとしては インデントなしで143文字と少し短くなりました。
超応用といいつつも、日付の折り返しテクニックはパターンが決まっているので 慣れれば割と簡単です！
しっかり理解して使いこなせるようになりましょう。
Q5. 複数列データに連番をふりたい
ちょと特殊な連番のケースにチャレンジしてみましょう。
左のような 10行3列のデータがあった場合、右のように 名前（各列のデータ）の左に縦方向に連番を振りたいというお題です。
もちろん式を入れるのは一番右上 E2 セルのみです。どのような式を組めばよいでしょうか？
考えてみましょう。
ここから回答です。
↓↓↓
A5. 複数列のデータに連番をふる
回答です。
=LET(
a,A2:C11,
WRAPCOLS(
TOCOL(
VSTACK(
TRANSPOSE(
SEQUENCE(COLUMNS(a),ROWS(a))
),a
),,1
),10
)
)WRAPCOLS、TOCOL、VSTACK と配列操作系新関数 3連発ですね。
解説していきます。
まず、名簿用の連番配列を生成します。
ポイントは 下に数が増えていくので TRANSPOSEで 縦横変換することを考慮して、
SEQUENCEの行数 ・・・ 名簿（a）の列数
SEQUENCEの列数 ・・・ 名簿（a）の行数
と逆にしておく点です。
ここからは配列コネコネしていくだけですね。
SEQUENCEで生成した連番配列の下に 名簿データ aを VSTACKで連結して
これを TOCOLの第3引数を1にして列方向スキャンで 1列のデータに変換
最後に WRAPCOLSで 10行折り返しにすれば 完成です。10という数値を直接入れましたが、ここは ROWS(a) と出来ます。
SEQUENCEというよりは、新関数による 配列コネコネの応用例となってしまいましたが、SEQUENCEと配列操作系新関数を組み合わせて使うケースはたくさんあります！
Q6. 数字の省略部分（～）を展開したい
今回の SEQUENCE関数の超応用例はこれが最後です。
A2 セルのように
1,2,3 を 1～3
5,6,7,8,9,10 を 5～10
と 連続する数列を全角の ～で簡略化した テキストがあります。
これを A4のように ～部分を展開した形にしたいというお題です。
※ A2セルは 半角数値、カンマ、全角～ のみで構成されいている
※数字の並びは 右が大きくなっており、 3,8,6 や 11～9 のような例外は考慮不要
どうでしょうか、難易度があがってきましたね。途中まででもＯＫなので、まずは自力でチャレンジしてみましょう！
ここから回答です。
↓↓↓
A6. 数字の省略部分（～）を展開する
回答です。
=ARRAYFORMULA(
LET(
a,A2,
x,SPLIT(TOCOL(SPLIT(a,",")),"～"),
y,BYROW(x,LAMBDA(r,IFERROR(SEQUENCE(1,index(r,,2)-index(r,,1)+1,index(r,,1)),r))),
TEXTJOIN(",",true,y)
)
)幾つかアプローチはありそうなんで、1例だと思ってください。もっと短い記述の式もあるかも。
解説です。
まず、 LETで A2をaと置いておきます。
SPLIT(a,",")
続いてSPLIT関数でカンマを区切りとして分割 （横方向に展開される）
TRANSPOSE(SPLIT(a,","))
これを縦に変換。（ここはTOCOLでもOK）
SPLIT(TRANSPOSE(SPLIT(a,",")),"～")
この縦1列の配列を今度は ～ で分割。
ここから Arrayformulaが必要になるので一番外側に付けておく
ここまでを xと置きます。
x,SPLIT(TOCOL(SPLIT(a,",")),"～")このxを行毎に処理していきたいので BYROW関数を使います。
BYROW(x,LAMBDA(r, ← 取り出した個々の行を r としておく
行単位で
このように展開したいわけですから、これは 前回のSEQUENCE関数の基本で登場した お題「xからyまで nずつ増える1列の配列を作りたい」が応用できます。
5 から10 まで の数字配列を横に展開させるなら
SEQUNECE(1,10-5+1,5) となります。
で、左の数字 5は index(r,,1) 、右の数字 10は index(r,,2) と表すことが出来るので、
SEQUENCE(1,index(r,,2)-index(r,,1)+1,index(r,,1))
このように式を組めば 5から10まで を展開できます。
ここまでの式を動かしてみましょう。
～がついていた2列になっている行は 展開されましたが、単体の 12や 30は #NUM!エラー となっています。
これは 2列目（〇までの部分）が空欄、つまり 0扱いであるため、たとえば 30の単体の行は
SEQUENCE(1,0-30+1,0)
で、第2引数の 列数が-29というありえない数字を指定している為です。
単体の数値の場合は そのままで良いので、ここは IFERROR関数を使ってエラーだったら r （そのまま）としておきましょう。
BYROW(x,LAMBDA(r,IFERROR(SEQUENCE(1,index(r,,2)-index(r,,1)+1,index(r,,1)),r)))
これを yと置いて、最後に TEXTJOIN関数でカンマ連結すれば 完成ですね。
TEXTJOIN(",",TRUE,y)
最後を縦1列や横1行の配列で取得したい場合は
TOCOL(y,1) ※1行なら TOROW(y,1)
とすればよいです。
完成した式の動作確認をしてみましょう！
～が無いケースや 単体の数値でも対応できていますね。完成です。
【オマケ】Q7. 数字が連続する部分を 省略系（～）にしたい
先ほどの展開の逆パターン。
3,5,6,7,8,9,10,12 ⇒ 3,5～10,12
といった形で連続する数値を ～でまとめたいってお題です。
ですが、これはSEQUENCE使いそうで結局使わない方が式が短いという・・・、SEQUENCEの超応用例というには微妙です。
ただし、難易度がかなり高い超応用例なんで、自信のある方はチャレンジしてみてください。
ここから回答です。
↓↓↓
A7. 数字が連続する部分を 省略系（～）にする
回答です。
=ARRAYFORMULA(
LET(
a,A2,
b,SPLIT(a,","),
x,b&IF(MAX(b)=b,,IF(COUNTIF(b,b+1),"～",",")),
y,CONCATENATE(x),
REGEXREPLACE(y,"(～\d+)+～","～")
)
)見事に SEQUENCEが登場しませんｗ
式を解説していきましょう。
とりあえず、文字列のままでは扱いが難しいので配列化します。
まずはSPLIT関数でカンマで分割です。
この配列を bと置いておきます。
連続する数値を最終的に ～ でまとめ、連続していない数値が飛ぶ箇所はカンマで区切りたいのですが、1つずらして比較して連続判定は面倒なので
+1した数値が b内に存在するか
で、連続しているか？を判定します。
それが
IF(COUNTIF(b,b+1),"～",",")
この部分です。例えば上のケース
1,2,3,5,6,7,8,9,10,12,15,16,19,20,21,22,23,24
この配列の 2を見たときに 2+1=3 は 存在するので連続している判定で ～ を、3を見たときに 3+1=4 は 存在しないので , を返します。
ここを配列処理する為にArrayformulaが必要になります。
最後の カンマは不要ですね。後で除去するのは面倒なので、この段階で消しておきましょう。
もっといい方法があるかもしれませんが、IFのネストで最後を MAX(b)=bで判別し処理しておくこととします。
IF(MAX(b)=b,,IF(COUNTIF(b,b+1),"～",","))
これを あとで bと結合してもいいんですが、この段階で &で結合しちゃいましょう。これを xと置きます。
x,b&IF(MAX(b)=b,,IF(COUNTIF(b,b+1),"～",","))
これを区切り文字なしで 単純に結合したいので CONCATNATE関数を使います。
CONCATENATE(x)
Googleスプレッドシートの CONCATNATE関数は Excelの CONCAT関数 に相当します。紛らわしい！
ただ今回は 1行データだし 文字数的には
JOIN(,x)
の方が短いので、JOIN関数を区切り文字なしで使っても良いです。
これを y と置きます。
1～2～3,5～6～7～8～9～10,12,15～16,19～20～21～22～23～24
↑ この状態になったものを
置換前 ⇒ 置換後
～2～ ⇒ ～
～6～7～8～9～ ⇒ ～
～20～21～22～23～ ⇒ ～
↑ このように置き換えたいわけです。
これを処理できるのが 正規表現が使える REGEXREPLACE関数です。
置換前の左側は全て
(～\d+)+～
このように表すことができます。これを ～ に置換すればよいので
REGEXREPLACE(y,"(～\d+)+～","～")
このようになります。これで完成。
最後に 展開式と合わせて動作を確認しておきましょう。
きちんと A2 セルに入力した ～で省略した数値が 展開式で A4 に展開されていますね。
そして A4 を参照する A6の式で 再び ～で 省略系に変換されてます。A2 と A6 が一致しているのがわかります。
=ARRAYFORMULA(
LET(
a,A2,
b,SPLIT(a,","),
x,b&IF(MAX(b)=b,,IF(COUNTIF(b,b+1),"～",",")),
y,CONCATENATE(x),
REGEXREPLACE(y,"(～\d+)+～","～")
)
)こちらの式は、あくまでも mirの考えた解法の1つなので、もっとスマートないい式があるかもしれません。
最後は難易度は結構高めでしたが、SEQUENCEを使わない式となってしまいましたねｗ
SEQUENCE関数 超応用例、最後は「あいうえお 50音表」に挑戦
今回の超応用例は 以上となります。
で、本来は全2回を予定していた SEQUENCE関数 超応用例シリーズですが、X（旧 Twitter）で色々 式のアイディアというか学びをいただいたので、次回もう1回だけ超応用例をいってみたいと思います。
合わせてSEQUENCEの変わった挙動についても触れたいと思います！
この記事が気に入ったらサポートをしてみませんか？