Googleスプレッドシート SEQUENCE関数 超応用例1 （連番だけじゃない！）
※2024年8月 追記
サイレントアップデートがあったので追記しました。
Googleスプレッドシートの好きな関数は？ と聞かれたら、どの関数を選びますか？
Googleスプレッドシートを代表する Arrayformula や QUERY は当然最有力候補ですが、便利な FILTERや最新の LET、LAMBDAも捨てがたいし・・・、一つに絞るのは難しいです。
一番は決められませんが、有力候補の中に mirとしては SEQUENCE を入れたいと思います。
結構頻繁にこちらの noteでも SEQUENCE が mirの推し関であることを書いてきましたが、恐らく多くの人は「知らない関数」だったり、「連番をつくるだけの関数」という認識だったりじゃないでしょうか。
とはいえ他の関数と組み合わせて使う際は、副菜というか 主役級ではない関数 なので、あまり目立たない存在です。
しかし SEQUENCE関数がないと出来ない処理は実はたくさんあって、スプレッドシート上で配列を扱う上で、重要かつ重宝する関数であることは間違いありません。
今回は、単に連番をつくる関数ではない 名バイプレイヤーといえる SEQUENCE関数にフォーカスしてみたいと思います。
SEQUENCEを制すものは、シートを制す です！
とはいえ、毎回そうなんですが「超応用例」といいつつ、今回は超応用例まではたどり着けませんが・・・。
前回は Googleドキュメントの ページレスモードについて書きました。
SEQUENCE関数 お題演習用シートのご案内
今回は SEQUENCE関数を活用するお題が10問登場します。
チャレンジしたいけど、自分でお題データ用意するのは面倒くさい！！って人は 有料ですが、以下にお題用演習シートを用意しています。
コピーすれば お題、回答に加え 次週やる予定の 超応用例お題も先行でチャレンジできます。 もちろん、2を公開後は シートも更新予定。
お題用演習シートで Googleスプレッドシートの関数の勉強したい！って人は、必ずこの noteを読む前に先にお題用演習シートを入手してください。（このnote読んだらネタバレになるんで）
興味がある方は是非！
SEQUENCE関数の基本
まずは改めて SEQUENCE関数の基本を理解しましょう。
SEQUENCEは「連続」とか「連鎖」を意味する英語で、これが関数の語源となっていると思われます。読み方はシークエンス、もしくは シーケンスです。
SEQUENCEは 単独でスピる関数
SEQUENCE(行数, 列数, 開始値, 増分量)
※行数は必須。
※列数、開始値、増分量は 省略時は 1として扱われる
SEQUENCE関数は 簡単に言ってしまうと 連番を生成する関数ですが、その魅力は単独でスピることができる、無から自由なサイズの配列を生成できるという点にあります。
引数を見ていきましょう。
=SEQUENCE(10)
このように第1引数のみ 数値（1以上 今回は10）を入れた場合、1から開始して10行分（下方向に）1ずつ増える数値データの配列が生成されます。（縦1列の配列）
=SEQUENCE(1,10)
第2引数の方に10を入れこのようにすると、今度は 1から開始して10列分（右方向に）1ずつ増える数値データの配列が生成されることになります。（横1行の配列）
第1引数は省略できません。
=SEQUENCE(5,5)
第1引数（行）、第2引数（列）を 両方 1以上で指定した場合は、複数行、複数列の二次元配列が生成されます。
上の場合は 5行、5列の配列となります。
数字は 右に1つずつ増えていき、指定した列数（この場合は5）までいったら1段下に続きます。
さらに SEQUENCEは 第3引数、第4引数を使うことで、開始値や増分を調整した連番を生成できます。
では、簡単ですが1つ目のお題いってみましょう！
Q1. 2から20まで偶数のみの 縦1列の配列を 生成したい
こんな感じの2からスタートする偶数のみの数値配列を出力するには、B2セルにどんな式をいれればよいでしょうか？
これは簡単ですね。やってみましょう！
すぐに回答いきます。
↓↓↓
A1. 2から20まで偶数のみの 縦1列の配列を 生成する
正解はこちら。
=SEQUENCE(10,1,2,2)
これは4つの引数を指定することで
第1引数 ▶ 縦方向に10行
第2引数 ▶ 1列
第3引数 ▶ 2からスタートして
第4引数 ▶ 2ずつ増えていく
という 数値配列を生成 しています。
2から開始し ２ずつ増えることで 偶数のみの連番となっています。
これは大丈夫ですね？
Q2. 19から1まで奇数のみで数が右に減っていく 横1行の配列を 生成したい
では、もう1問 基本問題その2です。
このような 右に減っていく奇数の配列を生成するには、B2にどのような式をいれればよいでしょうか？
これも簡単ですね。やってみましょう！
すぐに回答いきます。
↓↓↓
A2. 19から1まで奇数のみで数が右に減っていく 横1行の配列を 生成する
回答です。
=SEQUENCE(1,10,19,-2)
横1行に展開するので、第1引数、第2引数が 1,10 となる、ここはよいですね。
ポイントは 第4引数の 増分量が マイナス指定が可能ってことです。
開始値を19、増分量を -2とすることで、減っていく奇数の 数値配列を生成しています。
SEQUENCEの引数に使用できる数値
第4引数にはマイナスの数値が使えると書きましたが、当然ですが 第1、第2引数（行数、列数）は マイナスや 0は指定できません。
一方、第3引数（開始値）、第4引数（増分量）は マイナスの指定が可能です。
また、第4引数を 0に指定することで 同じ数値（開始値）を繰り返す指定したサイズの配列が生成できます。（これがたまに必要になる時があります）
では、マイナスではなく 小数値だった場合はどうか？
いずれの引数でも小数で指定してもエラーにはならず、小数部分は切り捨てた整数として計算されます。
ただし、これは Googleスプレッドシートの SEQUENCE関数の特徴であってExcelとは微妙に違います。そこは次回触れます。
【2024年8月追記】SEQUENCE関数のサイレントアップデート
サイレントアップデートがあったようで、第3引数の開始値、第4引数の増分量 どちらも小数、分数に対応するようになりました！
決定的瞬間を GIF動画でご覧ください。
アップデート前の 2023年12月、このSEQUENCEの記事を書いた時に作成した式の結果は、第3引数の開始値に 入れた 3.2は 3として、第4引数の増分量に入れた 2.99は 2として扱われ、小数を切り捨てた結果となっています。
これを一度 式を削除してから元に戻すボタンで再計算させると・・・。
なんと、どちらもそのまま 3.2、2.99として 扱われるように！！
Googleスプレッドシートの SEQUENCE関数が、第3引数、第4引数が小数（分数も）対応にアップデートされたことが確認できました。！！
Q3. 5から開始して10まで 0.5ずつ下に増えていく 1列の配列を生成したい
では、少しだけ難易度を上げたお題いってみましょう。
先ほど Googleスプレッドシートの SEQUENCEの引数は、小数点以下を切り捨てた整数として扱われると書きました。
では、今回のような 0.5ずつ増える 数値配列を生成するにはどうすればよいでしょうか？ というお題です。
考えてみましょう！
ここから回答です。
↓↓↓
A3. 5から開始して10まで 0.5ずつ下に増えていく 1列の配列を生成する
回答です。
=ARRAYFORMULA(5+SEQUENCE(11,1,0,1)*0.5)
さすがに SEQUENCEだけでは厳しいので、ARRAYFORMULAの登場です。
まさに困った時に 「私が来た!」って感じで 解決してくれる オールマイトのような頼れる存在ｗ
こちらのポイントは2つ
初期値をSEQUENCEの外に出す
0開始で1ずつ増える配列を用意し、増分の小数値を かける（乗算）
0.5ずつ増える配列は SEQUENCEでは生成できないので普通に1ずつ増える配列を生成して、これに 0.5をかけるという方法をとります。
ただ、0.5をかけてしまうと初期値に影響が出てしまうので 配列の方を0開始（0はなにをかけても0なので）として、あとから 初期値の 5を足すという流れで式を組みます。
配列＋ 値 の計算は、配列の個々の要素に加算された 配列が返る挙動は、結構使う機会が多いです。理解しておきましょう。
Q4. xからyまで nずつ増える1列の配列を作りたい
ここまで触れてきませんでしたが、お題の問いは 〇から〇まで と開始値と終了値を指定してるけど、
第1引数の 行数（列数）ってどうやって算出してるの？
って思いませんでしたか？
1から開始して1ずつ増えていく 一次元の数値配列であれば、終了値がそのまま行数（列数）なので問題ありませんし、数値が小さければ 感覚でわかる人もいるでしょう。
しかし、増分が7ずつや13ずつだったり、開始値、終了値が大きい数だと 計算して求める必要があります。
これを式に組み込んで汎用的にしてみよう。というお題です。
このように LET関数で
開始値 B1 を ｘ
終了値 B2 を y
増分 B3を n
と置いて セルの数値に応じて可変する 1列の数値配列を生成したい場合、どのような式を組めばよいでしょうか？
=LET(x,B1,y,B2,n,B3,SEQUENCE( ここを考える ))
SEQUENCEの中身を考えるお題です。 yとnの整合性がとれていないケースは考えないものとします。
LET関数ってなに？？って人は過去noteを参照ください。
考えてみましょう！
ここから回答です。
↓↓↓
A4. xからyまで nずつ増える1列の配列を作る
回答です。
=LET(x,B1,y,B2,n,B3,SEQUENCE((y-x)/n+1,1,x,n))
ほぼ、算数の問題ですね。このような式になります。
第2引数は 1、第3引数は開始値なので x、第4引数の増量分は n
ここはいいですね。
第1引数の行数ですが、1～10のケースでイメージするとわかりやすいです。
この場合 行数は10ですが 終了値 - 開始値 だと 10-1で 9となってしまうので、1を足してあげる必要があります。
では増分が1ではなく 2や3の場合はどうなるか？
終了値 - 開始値 の結果を 増分で割ってから +1となります。
というわけで、第1引数は式にすると (y-x)/n+1 となります。
※ これも算数ですが y-x をカッコでくくらないと 先に x/n が計算されてしまうので注意
複雑な式の途中過程でSEQUENCEを使う際に必要となるテクニックです。理解できたでしょうか？
Q5. 1～30までの数を縦方向優先で並べた 5行×6列の配列を生成したい
冒頭で SEQUENCEで 複数行・複数列の配列を生成した場合は、横方向に1ずつ増えて下に折り返す（横優先である）と書きました。
横方向が基本的に優先となるのは、SEQUENCEに限らずスプレッドシート全般の仕様なんですが、やはり 上の画像のような縦（下）に1ずつ増えていって右に折り返す 数値配列が欲しい時があります。
では、このような配列を生成するにはどんな式を使えばよいでしょうか？
これは知ってる人には超簡単ですね。考えてみましょう！
すぐに回答いきます。
↓↓↓
A5. 1～30までの数を縦方向優先で並べた 5行×6列の配列を生成する
正解はこちら。
=TRANSPOSE(SEQUENCE(6,5))
SEQUENCEでは進行方向を変えることは出来ないので、TRANSPOSEで丸っと縦横変換が簡単ですね。
最終的に5行×6列のデータとするので、縦横変換前は 6列×5行 のデータを用意するという点に注意。
SEQUENCE関数の基本は理解できたでしょうか？
SEQUENCE関数の活用例
基本に続いて、SEQUENCE関数がどのような場面で活用できるのか？をお題形式で見ていきましょう。
Q6. データに連番（通し番号）をふりたい
B列に2行目から途中に隙間（空白行）なく、名前が入っているとします。この時 簡単にA列に1から通し番号を振りたい場合、A2には どんな式を入れればよいでしょうか？
B2の名前データが今後追加されていった場合でも、可変対応できる式とする必要があります。
Excelだったらテーブルで処理するような 要件ですが、テーブル機能がないGoogleスプレッドシートでは関数での対応となります。
考えてみましょう！
ここから回答です。
↓↓↓
A6. データに連番（通し番号）をふる
回答です。
=SEQUENCE(COUNTA(B2:B))
途中に空白行がないので、2行目からシンプルに連番をふればよいだけです。
SEQUENCEの第1引数の行数は、そのままB列の名前の個数なので、
COUNTA(B2:B)
で値の入ったセルの数を取得すればOK。これでデータが追加された際も自動で連番も変化します。
かつては、連番といえば オートフィルや ROW関数を使った手法でしたが、今の時代は 連番なら SEQUENCE を使うべし！と言って良いでしょう。
ちなみに 途中に空白のある データがあって値があるところにだけ連番をふることも出来るんですが、こちらは SEQUENCEを使いません。
機会があれば、取り上げたいと思います。
ちなみに超応用例としては、以下の左のデータのような 複数列のデータに対して、右のように通し番号をふるというお題を次回やる予定です。
Q7. 1ヶ月分の日にちを 1列データとして生成したい
SEQUENCEは日付の生成にも活用できます。
スプレッドシートでは、日付は1日1ずつ増えていくシリアル値なので、まさにSEQUENCE がそのまま使えますね。
それでは、
=LET(start,"2023/12/1",
このように開始日（1日）を startと置いた時に、SEQUENCEを使ってどのような式を組めば 月末までの 1ヶ月分の日付を生成することが出来るでしょうか？
考えてみましょう！
ここから回答です。
↓↓↓
A7. 1ヶ月分の日にちを 1列データとして生成する
回答です。
=LET(start,"2023/12/1",SEQUENCE(DAY(EOMONTH(start,0)),1,start))
他の書き方もありますが、これがARRAYFORMULAいらずで一番短いんじゃないかと思います。少し解説しておきましょう。
EOMONTHは第1引数の開始日から、第2引数で指定した月数だけ前または後ろの月の最終日（月末）の日付を返す関数です。
この特性を使って、EOMONTHで 第2引数を 0とすることで、 start（2023/12/1）の当月の月末（2023/12/31)を取得します。
ここから DAY関数で日にち（31）を数値で取り出します。これが SEQUENCEの行数となります。
あとは、SEQUENCEの開始値を startとすればOK。
超応用例としては、以下のようなカレンダー表示の7列データとして生成する方法にチャレンジ予定です。こちらは次回。
SEQUENCEは カレンダー（日付）と相性がいいので、日付データの生成では SEQUENCEが使えないか？を考えてみましょう。
Q8. アルファベット(A-Z)を一気に生成したい
SEQUENCEは数値、日付だけではなく、工夫すれば文字列の配列生成にも使えます。
たとえば上のようなアルファベット26文字の大文字（または小文字）を一気に生成したいって時は、先頭のセルにどんな式をいれればよいでしょうか？
考えてみましょう！
ここから回答です。
↓↓↓
A8. アルファベットを一気に生成する
回答です。
アルファベット大文字横展開
=ARRAYFORMULA(CHAR(SEQUENCE(1,26,65)))
アルファベット小文字横展開
=ARRAYFORMULA(CHAR(SEQUENCE(1,26,97)))
このような式になります。使うのは CHAR関数です。
CHAR(10) を改行として利用するイメージが強いですが、本来は文字コード（ユニコード）から 文字に変換する関数です。
ユニコード値では 大文字の Aは 65、小文字の a は97 となりそこから 1つ増えるごとに
65,66,67,68… ⇒ A,B,C,D…
97,98,99,100… ⇒ a,b,c,d…
と続いていきます。
このコード配列を アルファベット26文字分の 列数を 26 を指定してSEQUENCE関数で生成、Arrayformulaの配列処理で 個々のコードに CHAR関数を適用させることで A-Zを生成しています。
ちなみに 大文字の Aは 65、小文字の a は97 は、CHAR関数の逆 で文字をユニコード値に変換することが出来る CODE関数で確認できます。
なので、よりわかりやすく記述するのであれば
アルファベット大文字横展開
=ARRAYFORMULA(CHAR(SEQUENCE(1,26,65)))
↓
=ARRAYFORMULA(CHAR(SEQUENCE(1,26,CODE("A"))))
このようにしても良いかもしれません。
ちなみに、同じ方法で 日本語の「あいうえお」の50音表も出力したいところですが、
残念ながら 日本語のひらがなのコード順は
あぃいぅうぇえぉおかがきぎくぐけげこごさざしじすずせ
このような順番になっているので、うまく「あいうえお」は取得できません。残念！
Q9. Query関数の select句 の記述を簡略化したい
QUERY関数は様々な機能をもった最強クラスの集計関数ですが、SQLライクな独特の記述方法は他の関数とはだいぶ違うもので、どうしても条件によっては長く複雑なものになりがちです。
その中で大変なのが、列数が多いデータでselect句を使うケース。
配列を対象としてQUERY関数を利用する際は、列の指定は Co1,Col2… と記述するのですが、たとえば20列ある対象データのうち1列目だけ 出力しない。（ただし where 条件に使うので 対象からは外せない）といった場合、
欲しい列を全て指定する必要がある為
select Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10, Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20
こんな記述をしなければなりません。書くのも大変ですし、煩雑ですね。
しかし、QUERY関数の条件記述は 文字列なので、上のselect句を 関数で生成することができます。
では上のテキストを生成するには 、どのような式を組めばよいでしょうか？
考えてみましょう！
ここから回答です。
↓↓↓
A9. Query関数の select句 の記述を簡略化する
回答です。
="select Col"&JOIN(",Col",SEQUENCE(19,1,2))
ちと Colを2回記述しているのが イマイチで、すなおに書くなら
=ArrayFormula("select "&JOIN(",","Col"&SEQUENCE(19,1,2)))
このように "Col"を先に&で連結してからカンマ区切りとなるんですが、この為だけに Arrayformulaが登場するのもなーってことで、回答の書き方にしてみました。
SEQUENCEで 2～20までの数値配列を生成し、それをJOIN関数で ",Col"を間に入れて連結しています。
19列を selectで指定するケースだと、普通に記述する場合と比較して 70文字程度式が短くなっています。
ちなみに 先ほどの7問目のアルファベット変換を使えば、範囲の列指定にも使えるんですが、
="select "&JOIN(",",ARRAYFORMULA(CHAR(SEQUENCE(1,25,66))))
Zより右の AA列以降は この方法では取得できず、結構面倒になってきます。
※AA列以降は ADDRESS関数を使って取得する方法があります。
=ArrayFormula(JOIN(",",SUBSTITUTE(ADDRESS(1,SEQUENCE(1,30),4),"1","")))
— 光希桃 (@mikimomo_as) December 24, 2023
AA以降を含む列番号がほしいときはADDRESS関数を使うのはどうでしょうか。 pic.twitter.com/kxBDAA22yR
ちなみに Googleスプレッドシートは 列を削除出来るのが特徴の一つなんですが、このADDRESS関数を使うと シートに存在しない列 であっても列のアルファベットを取得できます。面白いですね。
面倒な場合は 対象範囲を { } で括って配列化して Col1指定にしてから、SEQUENCEで select句の 文字列を生成でもよいかなと思います。（これはお好みでって感じかも）
Q10. DROP関数のようなことがやりたい
Excelの便利関数 TAKE、DROPは なぜか Googleスプレッドシートへの輸入から漏れてしまい、残念ながら Googleスプレッドシートでは利用できません。
特にDROP関数は、タイトル行だけ除いたデータが欲しい時など、
「これがあればもっと楽なのに～」
ってケースが結構あるんで mirとしては追加を待ち望んでおります。
そんな DROPの代替処理をする際にも SEQUENCE関数が活躍します。
ではお題いってみましょう。
=LET(a,HSTACK(A2:J7,A12:J17), ここの式を考える )
HSTACK関数で 2つの範囲を横に連結させた配列を LET関数で aと置きました。
この配列 aの 一番左の列だけ削除した配列を出力したいです。上のLET式を どのようにすればよいでしょうか？（最初から除外して連結すればってツッコミが入りそうですが、あくまでも例ですので）
ただし、先ほど登場したばかりなので QUERY関数の利用はナシとします。
ちなみに Excel（365版）であれば、ここはDROP関数一択です。
Googleスプレッドシートで、DROP関数の代わりの式を考えてみましょう！
ここから回答です。
↓↓↓
A10. DROP関数の簡易的な代替式を作る
回答です。Query関数以外では 2通り考えられます。
1つが FILTER関数で 絞り込む方法
=LET(a,HSTACK(A2:J7,A12:J17),FILTER(a,SEQUENCE(1,COLUMNS(a))>1))
そして2つ目がCHOOSECOLSで 1列単位で取得指定く方法です。
=LET(a,HSTACK(A2:J7,A12:J17),CHOOSECOLS(a,SEQUENCE(COLUMNS(a)-1,1,2)))
どちらの解法もポイントとなるのが、COLUMNS(a) です。
セル範囲であれば、ROW関数やCOLUMN関数、OFFSET関数などを使って1列目を削る方法が思い浮かびますが、これらの関数は配列では利用できません。
では、どうするか？ mirのnoteではよく登場する 配列にバーチャルな行番号・列番号をふる方法を使います。そこで活躍するのが
ROWS関数 ▶ 配列、範囲の 行数（高さ）を取得する
COLUMNS関数 ▶ 配列、範囲の列数（横幅）を取得する
この2つの関数です。
このように ROWS(a)、COLUMNS(a) をSEQUENCEの引数として使うことで、配列に対してバーチャルな連番をふることができます。
これを使ってDROPやTAKEに近い 配列操作が可能となります。
実際にはTAKE、DROPはマイナスで後方からの指定が出来るという強みもあるんで、完全なる代替式となるとさらに複雑になってます。
その辺りは 過去の noteで検証しています。
また、CHOOSEROWS、CHOOSECOLS の 行（列）指定に SEQUENCEが活用できるのもポイントですね。
SEQUENCE関数その他の活用例
まだまだ活用例はあります。過去に note でSEQUENCEが登場した記事から一部を紹介。
REDUCE関数とSEQUENCE関数
REDUCE関数で繰り返し処理をする際の配列を SEQUENCE関数で生成するケースは多いです。
SORT関数とSEQUENCE関数
並び替えを実行するSORT関数では、タイトル行を一番上に固定して並び替えをしたい時に SEQUENCE関数が使えます。
VLOOKUP関数とSEQUENCE関数
XLOOKUP関数の縦横スピルができないという弱点を VLOOKUPでカバー。VLOOKUPの第3引数で SEQUENCE関数が活躍します。
FILTER関数とSEQUENCE関数
FILTER関数でもタイトル行の扱いで利用することがあります。
MMULT関数とSEQUENCE関数
BYROW登場で出番は減りましたが、かつては複数行データの行毎の合計を出力するならMMULT関数でした。その際 1のみで構成された1列の配列が必要になるので、それをSEQUENCEで生成していたって話。
【Excel】TEXTBEFORE関数 / TEXTAFTER関数 と SEQUENCE関数
こちらは Googleスプレッドシートではありませんが、ExcelのTEXTBEFORE、TEXTAFTERも SEQUENCE関数と組み合わせて分割した複数データを取得することが出来ます。
次回こそは SEQUENCE関数超応用例を
今回は SEQUENCE関数の基本と活用例を 10個のお題を解きながら学びました。
今回のお題はそこまで難しいものはなかったので、もしかしたら「超応用例」を求めていた人には物足りなかったかもしれません。
そんな方は次回、SEQUENCE関数の超応用例2 で登場する 超応用問題に是非チャレンジしてみてください。
今回幾つか、次回予定として紹介した 超応用問題に加えて
↑ こんな変換をしてみよう！ってお題を用意しています。
次回、SEQUENCE関数 超応用例 2 お楽しみに～。（12/31だけどｗ）
いいなと思ったら応援しよう！
