今回は、ExcelのSEQUENCE関数を使い、行数や列数に応じた連番を自動生成する方法を紹介します。
SEQUENCE関数とは
SEQUENCE関数は、指定した行数と列数に合わせて、連続する数値の配列を返す関数です。1から始まる縦の連番だけでなく、任意の開始値、増分、横方向や表形式の数列も作れます。
従来は、最初の二つの数値を入力してオートフィルで伸ばす方法が一般的でした。SEQUENCE関数なら一つの数式から必要な範囲へ結果がスピルし、行数の指定を変えるだけで連番の長さを変更できます。
番号だけでなく、日付、月、時間、一定間隔の値なども生成できます。規則的な値の並びを数式として管理できる点が特徴です。
縦方向の連番を作る
基本的な連番は、SEQUENCE関数へ必要な行数を指定して作ります。10行を指定すれば、通常は1から10までの値が縦方向へ表示されます。
数式を入力したセルから下方向へ結果が展開されるため、展開先を空けておきます。途中に値や結合セルがあるとスピルエラーになります。
行数をセルへ入力し、そのセルをSEQUENCE関数から参照すれば、利用者が件数を変えられる連番表になります。行数には0より大きい整数を使い、空白や負の値が入った場合の表示も確認します。
横方向や二次元の連番を作る
行数と列数を指定すると、複数行・複数列へ連続する番号を展開できます。一行と複数列を指定すれば横方向の連番になります。
たとえば、3行4列を指定すると、左上から右へ進み、次の行へ続く番号表を作れます。座席表、ラベル台紙、月別の枠などの土台に利用できます。
縦方向だけの番号と違い、二次元配列では番号がどの順序で進むかを確認します。列ごとに番号を増やしたい場合は、別の計算や配列の変換を組み合わせます。
開始値を変更する
連番を1以外から始めたい場合は、開始値を指定します。101から始まる受付番号、0から始まる経過値、前回の続きから始まる管理番号などを作れます。
開始値を別セルへ置くと、年度や案件ごとに番号の起点を変更できます。ただし、正式な伝票番号や重複してはいけないIDをSEQUENCE関数だけで管理するのは注意が必要です。
行の削除や開始値の変更で既存番号が変わるため、発行済み番号の保存には向きません。SEQUENCE関数は、表示用の行番号、仮番号、計算用の系列など、再計算で変化してもよい用途に使います。
増分を指定する
増分を指定すると、2ずつ、5ずつ、0.5ずつなど、任意の間隔で値を増やせます。開始値を10、増分を10にすれば、10、20、30という系列になります。
負の増分を使えば、10から1へ向かうカウントダウンも作れます。終了値を直接指定する関数ではないため、行数、開始値、増分から最後の値を考えます。
小数を使う場合は、計算上の誤差や表示桁数に注意します。セルの表示形式で小数点以下の桁数を揃え、後続計算で厳密な比較を行う場合は丸め処理を検討します。
日付の一覧を作る
Excelの日付は内部では連続する数値として扱われます。開始値に日付セルを指定し、増分を1にすると、連続する日付を生成できます。
一週間ごとの日付なら増分を7、月初一覧ならEDATE関数と組み合わせるなど、目的に応じて作ります。単純に30ずつ増やしても月の日数は一定ではないため、月単位の処理には日付関数を使います。
生成結果が数値に見える場合は、セルの表示形式を日付へ変更します。数列の生成と日付表示を別の設定として考えると理解しやすくなります。
営業日だけの日付を作る
SEQUENCE関数で0、1、2のような連番を作り、その配列をWORKDAY関数へ渡すと、土日を除いた営業日一覧を作れます。祝日一覧も指定すれば、休日を除外できます。
開始日を営業日一覧へ含めるか、翌営業日から始めるかによって連番の開始値を調整します。祝日範囲は日付として正しく入力し、参照範囲に見出しや空白文字を含めないようにします。
会社独自の休日や勤務体系がある場合は、週末パターンを指定できる関数を検討します。作成後は実際のカレンダーと照合します。
データ件数に応じて連番を作る
一覧の行数に合わせて番号を生成したい場合は、COUNTA関数やROWS関数で件数を求め、その結果をSEQUENCE関数の行数へ渡します。
空白を含む列を基準にすると、番号の長さと実際のデータ行が合わないことがあります。必ず入力されるID列や、Excelテーブルのデータ行数を基準にします。
FILTER関数で条件抽出した結果へ連番を付ける場合は、抽出結果の行数をROWS関数で数えます。条件に該当する行がない場合のエラー処理も用意します。
文字を付けた番号を作る
SEQUENCE関数の結果へ文字列を結合すると、「No.1」「項目01」「A-101」のような表示を作れます。桁数を揃える場合はTEXT関数で表示形式を指定します。
たとえば1、2、3を001、002、003として表示したい場合、数値のままセル書式で見せる方法と、TEXT関数で文字列へ変換する方法があります。
後で計算や数値順の並べ替えに使うなら、元の値を数値のまま保持して表示形式で整えるほうが扱いやすくなります。外部システムへ渡すコードとして文字列が必要なら、TEXT関数などで確定した形式を作ります。
INDEX関数などの位置指定へ使う
SEQUENCE関数で作った連番は、データの位置を指定する配列としても利用できます。INDEX関数と組み合わせ、先頭から指定件数の行を取り出す、一定間隔の行を参照するといった処理が可能です。
単なる表示用番号ではなく、複数の計算を一度に実行するための土台になります。数式が複雑になった場合は、SEQUENCE関数だけの結果を別セルで確認し、期待する位置番号が生成されているか確かめます。
動的配列の関数を複数組み合わせると、Excelの対応バージョンが限られます。共有相手の環境も確認します。
スピルエラーへの対処
SEQUENCE関数の結果が展開できない場合は、予定範囲に値、数式、結合セルがないか確認します。行数や列数を増やすと、以前は空いていた範囲へ衝突することがあります。
Excelテーブルの計算列内では、スピル配列をそのまま展開できない場合があります。結果はテーブル外へ置き、必要に応じて参照します。
行数や列数へ大きな値を指定すると広い範囲を占有します。入力規則で上限を設ける、IF関数で空白時は結果を出さないなど、誤入力への対策を加えます。
実務での注意点
- 発行済みIDなど、後から変えてはいけない番号には使わない
- 連番の行数を数える基準列は必須入力の項目にする
- 日付系列では日単位と月単位の増やし方を使い分ける
- 数値の表示形式と文字列への変換を目的に応じて選ぶ
- 結果が展開される範囲を空けておく
連番を固定値として渡す必要がある場合は、結果をコピーして値として貼り付けます。その際は、元の数式を残した作業用シートを保管すると再作成しやすくなります。
まとめ
ExcelのSEQUENCE関数は、指定した行数、列数、開始値、増分に基づいて連続する値を生成します。縦横の連番、日付一覧、一定間隔の数列、データ件数に連動する行番号などを一つの数式で作れます。
利用時は、番号が再計算で変化してよい用途かを確認します。正式な管理番号ではなく、表示や計算のための系列に使うのが基本です。開始値、増分、必要件数を分けて設計することが、SEQUENCE関数を使いやすくするコツです。