残業を減らす！Officeテクニック
オートフィルより効率的！ Excelの新関数「SEQUENCE」で自在に連番生成
「XLOOKUP」と「RANDARRAY」、「SORTBY」を組み合わせてランダムなチーム分けも可能
2022年2月7日 06:55
今回は、Office 2021、Microsoft 365で利用可能な新関数「SEQUENCE」を紹介します。スピルに対応し、連番を生成できる関数で、大量の連番を生成するならオートフィルよりも早く、多機能です。オートフィルの入力で画面をドラッグでスクロールしなければいけないようなときは、こちらのほうが楽でしょう。動作は単純なので、覚えておけば無駄な時間を省けます。
※本稿の内容は「Office Professional Plus 2021」で動作確認しています。
連続する数値を生成する「SEQUENCE」
「SEQUENCE」（シーケンス）は連続する数値を作成できる関数です。1，2，3…、や2，4，6…など、一定の増分値の等差数列を作成します。構文は以下の通りです。指定した［行数］×［列数］の配列を生成します。［列数］［開始値］［増分］を省略した場合は「1」を指定したものとみなされます。
ここでは「=SEQUENCE(100)」と入力して、A列に1～100の連番を入力してみました。オートフィルで事足りるとも言えますが、「1000」を超える連番なら利用価値がありそうです。なお、文字列との組み合わせにも対応。例えば「="SA-"&SEQUENCE(100)」と入力すれば「SA-1」「SA-2」…と文字列＋連番が生成できます。製品番号や顧客番号などに使えますね。
今度は、5行5列の範囲で数列を生成してみます。入力する数式は「=SEQUENCE(5,5,0.1,2.5)」です。［開始値］は「0.1」、［増分］は「2.5」としました。
「SEQUENCE」の実用例
カレンダーを作成する
SEQUENCE関数は、日付や時刻の連番も可能です。カレンダーは、1日ずつ増えるので［増分］は「1」または省略します。［開始値］は「"2021/12/27"」のように直接指定するか、日付を入力したセルを参照します。また、セルの書式はユーザー定義で日付のみ表示されるように変更します。
このほか、時刻の連番なら「=SEQUENCE(16,1,"08:00:00","01:00:00")」のように指定します。この数式では午前8時～午後11時までの1時間おきのタイムテーブルが1列生成されます。TIME関数と組み合わせて「=TIME(SEQUENCE(16,1,8,1),0,0)」と記述することも可能です。
ランダムにチーム分けする
前回RANDARRAY関数の利用例で解説したシャッフルの応用です。20人のメンバーを4つのチームにランダムに振り分けてみます。まず、SEQUENCE関数を使ってセルE2～H6の範囲に連番を生成します。セルE2に入力する数式は「=SEQUENCE(5,4)」です。
セルC2には「=SORTBY(B2:B21,RANDARRAY(20,1))」と入力されており、C列は、B列のメンバーをシャッフルした状態です。セルE2～H6の範囲の連番に該当するC列のメンバーを当てはめればよさそうです。
セルE8には「=XLOOKUP(E2#,A2:A21,C2#)」と入力。「E2#」は、スピルで生成された動的配列（ここではセルE2～H6）を参照していることを表します。「C2#」も同様です。選択したセル範囲が動的配列の場合、セル参照が「セル番号#」の形式に変換されます。
なお、XLOOKUP関数の使い方は、こちらの記事を参考にしてください。
ここでは、XLOOKUP関数の引数として、セルC2、E2に入力した数式の結果である動的配列を参照しましたが、まとめて「=XLOOKUP(SEQUENCE(5,4),A2:A21,SORTBY(B2:B21,RANDARRAY(20,1)))」と記述して、一気に振り分けることも可能です。
「SEQUENCE」などのスピルに対応する関数の結果は動的配列になります。「RANDARRAY」と同じように「値」として固定したい場合は、コピー後に［値］として貼り付ける処理を忘れないようにしましょう。
