Excelの新しいバージョンに搭載された、連番を作成するSEQUENCE関数の動作がかなり面白いので紹介します。
この関数は連番の一覧表を作成する関数なので、スピル範囲を出力します。
スピルについては、こちらの記事が詳しいです。
SEQUENCE関数はどうやら単純な連番作成だけではないようです。
SEQUENCE関数のキホン
SEQUENCE関数は単体では単純に連番の一覧表を作成します。
=SEQUENCE(5)
の結果は次の通り、縦に１から５までの連番です。
=SEQUENCE(1,5)
の結果は次の通り、横に１から５までの連番です。
=SEQUENCE(5,3)
の結果は次の通り、縦５行、横３列の１から１５までの連番です。
=SEQUENCE(5,3,11)
の結果は次の通り、縦５行、横３列の１１から２５までの連番です。
=SEQUENCE(5,3,11,10)
の結果は次の通り、縦５行、横３列の１１から１０ステップの１５１までの連番です。
IF関数と組み合わせる
SEQUENCE関数とIF関数を組み合わせてみます。
=IF(SEQUENCE(5,3)<=3,{"A","B","C"},SEQUENCE(5,3))
１から５までの１行目には、A、B、Cの項目名が入り、それ以下では連番になります。
連番が４から始まるのは気に入らないので、１から始まるように、連番作成部分を-2から始まるようにします。
=IF(SEQUENCE(5,3)<=3,{"A","B","C"},SEQUENCE(5,3,-2))
さらにMOD関数と組み合わせる
SEQUENCE関数とIF関数にMOD関数を組み合わせてみます。
=IF(MOD(SEQUENCE(5,3),3)=1,{"A";"B";"C";"D";"E"},SEQUENCE(5,3))
１列目にアルファベットを表示しました。
SEQUENCE関数を複数使う
SEQUENCE関数の中にSEQUENCE関数を使ってみます。
その前に、セルE1に、次の関数を入れてみます。
=SEQUENCE(5,1)
セルF1に、次の関数を入れてみます。
=SEQUENCE(5,1,10,10)
セルG1に、次の関数を入れてみます。
=SEQUENCE(5,1,100,100)
ここまでで３ブロックが出来上がります。
次にセルA1に、次の関数を入れてみます。
=SEQUENCE(1,3)
横に１から３の数字が並びました。
この数字ごとに、E、F、G列の内容をぶら下げてみましょう。
セルA1の数値ごとに3つのSEQUENCE関数に振り分けるので、CHOOSE関数を使います。
=CHOOSE(SEQUENCE(1,3),パターン1,パターン2,パターン3)
となります。
この各パターンにE、F、G列の計算式を入れるので、
=CHOOSE(SEQUENCE(1,3),SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))
それぞれの列ごとにそれぞれの連番が入ります。
はじめのSEQUENCE関数が１行しかありませんが、その下に５行がそれぞれ構成されます。
これ以降は数式バーは長くなるので表示しません。数式はすべてセルA1に入力します。
それぞれの列の行数を変えてみます。
=CHOOSE(SEQUENCE(1,3),SEQUENCE(3,1),SEQUENCE(4,1,10,10),SEQUENCE(5,1,100,100))
列ごとに行数を変えることもできました。
データのない部分は#N/Aエラーです。
もしエラー値を空白にしたい場合は
=IFERROR(CHOOSE(SEQUENCE(1,3),SEQUENCE(3,1),SEQUENCE(4,1,10,10),SEQUENCE(5,1,100,100)),"")
です。
１行目をタイトル行にしたいので、
=CHOOSE(SEQUENCE(2,3),"A","B","C",SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))
３行目以降が#N/Aエラーとなりました。
値がないということで解釈しています。
１行だけでそれぞれの列にSEQUENCE関数を入れるのは問題ないようなのですが、複数行だと問題があるようです。
はじめのSEQUENCE関数を５行にしてみると
=CHOOSE(SEQUENCE(5,3),"A","B","C",SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))
３行目が#VALUEエラーになります。エラーの種類が変わりました。
#VALUEエラーは、数値データではないものを四則計算したりなどの文字を算術計算してしまった時に出るエラーですが、配列の個数が違う時にも出るので、そのケースかと思われます。
これを実現するには、１列５行を作り、その中で１行目ならば横にA、B、Cの項目名、そうではなければ各列ごとのSEQUENCE関数とします。
=IF(SEQUENCE(5,1)=1,CHOOSE(SEQUENCE(1,3),"A","B","C"),CHOOSE(SEQUENCE(1,3),SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100)))
なんだか日本語が合っていないような、スピル範囲にも計算式が入ってしまいエラーになるのではないかと疑問になるような数式ですがこれで表示されます。
もっと日本語にマッチするような組み合わせもあると思うので、もしお気づきの方はコメントを頂戴したいと思います。
SORT関数との組み合わせ
同じく追加されたSORT関数と組み合わせることもできます。
上記の式にSORT関数でA列の降順に並べ替えます。
=SORT(IF(SEQUENCE(5,1)=1,CHOOSE(SEQUENCE(1,3),"A","B","C"),CHOOSE(SEQUENCE(1,3),SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))),1,-1)
並べ替えもできています。
IF関数でも並べ替えができるのですね。
昇順にすると項目名が下にきます。
INDEX関数
中の値を１つだけ取り出すのはINDEX関数です。
左上の値を取り出してみましょう。
=INDEX(SORT(IF(SEQUENCE(5,1)=1,CHOOSE(SEQUENCE(1,3),"A","B","C"),CHOOSE(SEQUENCE(1,3),SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))),1,-1),1,1)
右下の値を取り出してみましょう。
=INDEX(SORT(IF(SEQUENCE(5,1)=1,CHOOSE(SEQUENCE(1,3),"A","B","C"),CHOOSE(SEQUENCE(1,3),SEQUENCE(5,1),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))),1,-1),5,3)
VLOOKUP関数と組み合わせる
２列目の値を使って、VLOOKUP関数と組み合わせます。VLOOKUP関数の一覧表範囲は、せっかくなので配列で指定します。
=SORT(IF(SEQUENCE(5,1)=1,CHOOSE(SEQUENCE(1,3),"A","B","C"),CHOOSE(SEQUENCE(1,3),VLOOKUP(SEQUENCE(5,1),{1,1000;2,2000;3,3500;4,4200;5,4900},2,FALSE),SEQUENCE(5,1,10,10),SEQUENCE(5,1,100,100))),1,-1)
１なら１０００、２なら２０００、３なら３５００、４なら４２００、５なら４９００という一覧表の値に変換されています。
MID関数との組み合わせ
ここからは文字列関数との組み合わせです。
MID関数で文字列をバラバラにすることができます。
元の文字列をセルA1に入力しておきます。
LEN関数でセルA1の文字数を調べ、その分を分割します。
=MID(A1,SEQUENCE(LEN(A1)),1)
バラバラになりました。
ではこのままUNICODEの番号を一つ進めた文字に変換してみます。
=UNICHAR(UNICODE(MID(A1,SEQUENCE(LEN(A1)),1))+1)
そしてこの文字をCONCAT関数でひとまとめにします。
=CONCAT(UNICHAR(UNICODE(MID(A1,SEQUENCE(LEN(A1)),1))+1))
ちょっとしたお遊びですが、このようなこともできます。
まとめ
SEQUENCE関数について面白い使いかたを紹介してみました。
もしかしたらSEQUENCE関数は、従来のExcel関数ではなかった、ループを実現する関数なのかもしれません。
私もただの連番関数だと思っていたのですが、やり込んだ結果、これだけ面白いことができるということがわかりました。
とても面白いのは動的にワンライナーで表を書けるということで、その出力した値はVLOOKUP関数やINDEX関数で処理でき、また、SUM関数、COUNT関数などでも集計ができます。
もっともっと実務で使えるような使いかたが必ずあると思っていますので、もし発見されましたらコメント欄などでお知らせいただくか、もしくはQiitaの記事としてアップしていただければと思います。
今回紹介した数式は、Microsoftで想定しているものではないと思われるため、動作しなくなるケースもあります。その場合はご了承ください。