SUMPRODUCTで入力済みの行を先頭から数え、空白を飛ばした連番を作ります。
見出しを1行目に置き、A2からの入力にB列で番号を付ける例です。
ZIPを展開してXLSXをGoogle Sheetsへ読み込み、対象の記事IDの数式TXTを使って練習します。
実務のシートへ入れる前にシートを複製し、既存の式やルールを控えてください。
空白・空文字・スペース・0を用意する
| セル | 入力 |
|---|---|
| A2 | りんご |
| A3 | 実際の空白 |
| A4 | 数式で空文字を返すセル |
| A5 | 半角スペース1個 |
| A6 | 数値0 |
| A7 | バナナ |
A4は練習XLSXに空文字を返す式を入れています。
半角スペースは見た目が空白でも文字数が1あり、数値0も1文字として扱われます。
B2へ式を入れ、7行目までコピーする
文字数が0のセルを除外するには、次の式をB2へ入れ、B7までコピーします。
=IF(LEN(A2)=0,"",SUMPRODUCT((LEN(A$2:A2)>0)*1))LEN(A2)=0で現在のセルを判定し、SUMPRODUCTでA2から現在行までの文字数が0より大きいセルを数えます。
A$2の開始行は固定し、後ろのA2はコピー先に合わせて変わるようにします。
確認結果と入力の変更
| 入力 | Google Sheetsの確認結果 |
|---|---|
| りんご | 1 |
| 実際の空白 | 番号なし |
| 数式の空文字 | 番号なし |
| 半角スペース1個 | 2 |
| 数値0 | 3 |
| バナナ | 4 |
| A3へ追加と入力 | 1、2、番号なし、3、4、5へ更新 |
A3の追加を消すと、元の1、番号なし、番号なし、2、3、4へ戻ります。
練習表のC列の文字数も確認すると、見た目だけで空白を判断せずに済みます。
空白に「なし」を表示する場合
空欄の代わりになしと表示する式は次のとおりです。
空白と空文字の行だけがなしになり、数値0には番号が付きます。
=IF(LEN(A2)=0,"なし",SUMPRODUCT((LEN(A$2:A2)>0)*1))従来の比較式もGoogle Sheetsで使える
空文字との比較で数える従来の式も、今回のGoogle Sheetsの入力で同じ番号になることを確認しました。
既存のシートで正しく動く式を、誤りとして置き換える必要はありません。
=IF(A2="","",SUMPRODUCT((A$2:A2<>"")*1))XLSXのローカル計算では0や配列の文字数に挙動の違いがあったため、練習XLSXのB列はC列の文字数を使う式にしています。
Googleで本文の式を試すときは、B2へ数式TXTの式を入れてコピーします。
番号が合わない場合と戻し方
番号が見出しから数えられる場合は、範囲の開始をA$2にそろえます。
空白に見える行が数えられる場合は、文字数やスペースを調べます。
入力にエラー値がある場合は、元のA列のエラーを直してから確認してください。
この番号は現在の並びに合わせた表示番号です。
並べ替えや入力の追加で変わるので、記録を識別する固定IDの代わりには使いません。
元へ戻すときはB列の追加した式を変更前のコピーへ戻し、A列の入力を残します。
全行に連番を作りたい場合はARRAYFORMULAとSEQUENCEの連番を使います。
公式仕様・参考資料
練習ファイルの使い方
Google用の式は各記事IDのTXTにあります。
XLSXの互換用の式とGoogle用の式の違いはZIPのREADMEで確認できます。
本文の入力を使い、出力先と戻し方を確認してから実務のシートへ適用します。
