Googleスプレッドシート連番Tips

特定列を対象にした連番はGoogleスプレッドシート、Microsoft Excel問わず、定番のTipsです。先日、チームメンバーから質問されたのでイロハのイから実用的なTipsまで紹介します。

●一括登録

連番の使われ方で多いのは、A列に数字を打ち、Bより右側の列にデータを管理するパターンです。A列に都度、数字を入れるのは手間なので、一括 or 自動で採番したいのが人間心理。
例えば、以下のようにB列の2〜6行目にデータがある状態でA列に数字を採番したい。

この場合、A2セルの右下角（かど）を選択してダブルクリックすると、

同じデータ、1がコピーされます….。こうならないためには、

1. A3セルに "2"を

2. 1と2を選択（つまり、A2とA3セルを選択）

3. 2(=A3セル)の右下角（かど）をダブルクリック

すると、B列でデータが入っている6行目まで、期待通り採番されます

これが、2だけを選択して、ダブルクリックすると失敗します…。

●関数活用

上述の連番方法では、課題が残ります。
例えば、BBBというデータは最後に回したい。

とドラッグ＆ドロップ（カット＆ペースと）すると、

連番が崩れます。あるいは、連番後に行挿入した際も、番号の振り直しが必要です。

つまり、固定された数字は保守が煩雑になります。
この課題解決には、row関数が有効です。row関数は、文字通り行番号を返す関数です。
例えば、A2セルに

=row()

を入力数と、"2"となるので、

ヘッダー1行分を除くために、マイナス1

=row()-1

=row()-1
を設定したら、セルの右下角（かど）をダブルクリックで完成です。

こうすれば、特定行をドラッグ＆ドロップしても、

ドラッグ＆ドロップしても、

崩れません。行を挿入すると、

数字が調子され、挿入した行に関数をコピーするだけで済みます。

●IF活用

row関数を使っても、課題が残ります。
データを追加するたびに、関数をコピーする必要があります。

予め余裕を持って関数を用意する方法もあります。

でも、これダサい。やはり、B列にデータがある場合にのみ数値を表示した。という場面に便利なのがIFです。

• 対象セルが空白だったら、空白を表示

• 対象セルに値があれば、関数で処理

との場合分けを考えると、空白は、="" で処理でき、A1セルを対象に考えると、

• B2セルが空白だったら、空白を表示

• B2セルに値があれば、row関数-1で処理

は、

=IF(B2="","",ROW()-1)

と記述します。この条件をA列縦にコピーします

A7〜A10セルにデータはあるけど、非表示になります。

●配列関数活用

IFを使った方法でも課題は残ります。上述の方法では余裕を持ってIF〜をコピーしておく必要があります。こういうシートにGASのようなプログラミングを使ってデータを自動挿入しようとすると、

データが入っている最終行の次の行にデータが挿入されてしまう恐れがあるからです。。。（自分も、GASでlastRow = sheet.getLastRow(),  lastRow+1というコーディングをよく使います）

そこでArrayFormulaの登場です。

=ArrayFormula(IF(B2="","",ROW()-1))

と囲み、2行目以下の列全体(B2:B、A2:A)を処理するように変更

=ArrayFormula(IF(B2:B="","",ROW(A2:A)-1))

この記述をA2セルに設定します。

見た目は以前と変わらないものの、A2以下のセルに関数の設定は不要です

B列にデータを追加したら自動的にArrayFormulaが採番（延伸）します

この設定であれば、システムが見当違いの行にデータを挿入する恐れはなくなります。

●最後のTipは見出しに忍ばす

まだ課題は残りますが。ArrayFormulaはメジャーな関数でないため、誤って消されてしまう恐れが。そこで、A2セルでなく、A1セルに関数を忍ばすのがおすすめです。再度、IF関数を活用します。

• もし、1行目なら、列名"No."で

• 1行目以外(=2行目以降)なら、

IF(ROW(A1:A)=1,"No.",〜〜)

という分岐を使います。先ほどまでの

• B列が空白だったら、空白を表示

• B列に値があれば、ROW関数-1で処理

を維持すると

IF(ROW(A1:A)=1,"No.",IF(B1:B="","",ROW(A1:A)-1))

IFが2回でてくるので難しく見えますが、

• もし、1行目なら、カラム名"No."で

  • 1行目以外(=2行目以降)で

    • B列が空白だったら、空白を表示

    • B列に値があれば、ROW関数-1で処理

という構造です。そして、ArrayFormulaで括れば完成です。

=ArrayFormula(IF(ROW(A1:A)=1,"No.",IF(B1:B="","",ROW(A1:A)-1)))

こうすれば、A2セル以下の列は空っぽなのに連番されます。
もし、列名を変更したい場合は、"No."を書き換えるだけですみます。
A1セルを書き換えられるリスクは残りつつも、色が塗られた見出し行を書き換える人は少ないです。

それでも課題は残ります…。
一番考えらえるケースが、1行目の上に行挿入される場合です

この場合は、

=ArrayFormula(IF(ROW(A2:A)=2,"No.",IF(B2:B="","",ROW(A2:A)-2)))

と

• 2行目だったら列名

• 2行目以降を処理

• 行数-2

1 を2 に書き換える必要があります。

さいごに

長々と説明を書きましたが、

=ArrayFormula(IF(ROW(A1:A)=1,"No.",IF(B1:B="","",ROW(A1:A)-1)))

をA1に貼り付ければ良いだけです。

また、Array Formulaはとっつき難い関数なので、以前書いた記事を参考に

良きGoogleスプレッドシート・ライフを！