ARRAYFORMULAとROWで、1つの式から連番を複数行へ展開します。
5行に範囲を限定した例で、開始番号、奇数、0始まり、展開先が埋まった場合を確認します。
ZIPを展開してXLSXをGoogle Sheetsへ読み込み、対象の記事IDの数式TXTを使って練習します。
実務のシートへ入れる前にシートを複製し、既存の式やルールを控えてください。
5行分の出力先を空ける
配列連番シートのA1を見出しにして、A2:A6を出力先にします。
A2へ次の式を入れる前に、この5セルに必要なデータがないか確認します。
練習XLSXではA2の案内を式へ置き換えます。
=ARRAYFORMULA(ROW(A2:A6)-1)ROW(A2:A6)の行番号2～6から1を引くと、1～5になります。
A2だけに式を入れればA6まで展開するため、同じ式を下へコピーしません。
SEQUENCEで件数を指定する
B2に次の式を入れると、5個の連番がB6まで展開します。
件数を変えたい場合は5を必要な件数へ変え、出力先の空きを確保します。
=SEQUENCE(5)100始まり・奇数・0始まり
C2、D2、E2へ次の3つの式を順に入れます。
どれも5行の範囲を指定し、出力先は6行目までです。
=ARRAYFORMULA(ROW(A2:A6)-ROW(A2)+100)=ARRAYFORMULA((ROW(A2:A6)-1)*2-1)=ARRAYFORMULA(ROW(A2:A6)-2)| 式 | Google Sheetsの確認結果 |
|---|---|
| A列：1始まり | 1、2、3、4、5 |
| B列：SEQUENCE | 1、2、3、4、5 |
| C列：100始まり | 100、101、102、103、104 |
| D列：奇数 | 1、3、5、7、9 |
| E列：0始まり | 0、1、2、3、4 |
展開エラーの原因と復旧
A4へ障害用の値を入れると、A2が#REF!になり、A4を上書きするため配列を展開できないというエラーを確認できます。
練習では障害用の値だけを消すと、A2:A6が1～5へ戻ることも確認しました。
実務のデータが出力先にある場合は、そのデータを消す前に、式の出力先を空いた列へ変えます。
結合セルや必要な式がないかも確認してください。
エラーをIFERRORで空欄にするだけでは、展開先の衝突は直りません。
入力行の有無と固定IDを分けて考える
この式は入力データがある行だけを数える式ではありません。
指定した5行分を連続して表示し、A2:Aのように終端のない範囲へ変えると、シートの末尾までを対象にします。
空白を飛ばして入力済みの行だけ数えたい場合はSUMPRODUCTの空白を飛ばす連番を使います。
並べ替え後も同じ記録へ付ける固定IDが必要なら、IDを値として保存して記録と一緒に管理します。
ROWによる位置の番号や、直前のセルに1を足す式は、その固定IDを保証しません。
元の状態へ戻す
練習を戻すときは障害用の入力だけを外し、A2の式と1～5の表示を確認します。
配列の番号を取り除くときは、先頭セルの式を変更前のコピーへ戻します。
展開した各セルを1つずつ削除する操作と混同しないでください。
公式仕様・参考資料
練習ファイルの使い方
Google用の式は各記事IDのTXTにあります。
XLSXの互換用の式とGoogle用の式の違いはZIPのREADMEで確認できます。
本文の入力を使い、出力先と戻し方を確認してから実務のシートへ適用します。
よくある質問：ARRAYFORMULAで連番を自動生成するときのチェックリスト
この式は、何をしますか？
ARRAYFORMULAとROWで、1つの式から連番を複数行へ展開します。この記事は、5行に範囲を限定した例で、開始番号、奇数、0始まり、展開先が埋まった場合を確認します。
式は、下の行へコピーする必要がありますか？
必要ありません。A2だけに式を入れれば、A6まで展開します。同じ式を下へコピーすると、展開先が衝突するため、しないでください。
SEQUENCE関数との違いは？
SEQUENCEは、件数を指定して連番を作ります。5個の連番なら、B2に式を入れると、B6まで展開します。件数を変えたい場合は、5を必要な件数へ変え、出力先の空きを確保してください。
100始まりや奇数、0始まりにするには？
式の開始番号と増分を変えます。この記事の例では、C列が100始まり(100〜104)、D列が奇数(1、3、5、7、9)、E列が0始まり(0〜4)です。いずれも5行の範囲を指定し、出力先は6行目までです。
「#REF!」が出ました。
展開先のセルに、別の値が入っている可能性があります。この記事の例では、A4に値を入れると、A2が#REF!になり、配列を展開できないというエラーになります。障害用の値だけを消すと、A2:A6が1〜5へ戻ります。
展開先にすでにデータがあるときは？
実務のデータが出力先にある場合は、そのデータを消す前に、式の出力先を空いた列へ変えます。結合セルや必要な式がないかも確認してください。エラーをIFERRORで空欄にするだけでは、展開先の衝突は直りません。
この式は、入力済みの行だけを数えますか？
数えません。指定した5行分を連続して表示します。A2:Aのように終端のない範囲へ変えると、シートの末尾までを対象にします。空白を飛ばして入力済みの行だけ数えたい場合は、SUMPRODUCTの空白を飛ばす連番を使います。
この連番を、固定のIDとして使えますか？
使えません。並べ替え後も同じ記録へ付ける固定IDが必要なら、IDを値として保存して、記録と一緒に管理します。ROWによる位置の番号や、直前のセルに1を足す式は、固定IDを保証しません。元に戻すときは、先頭セルの式を変更前のコピーへ戻してください。最新の仕様は公式ヘルプで確認してください。
