スプレッドシートで数千数万行に渡って自動で連番する(番号を振る)方法

こんにちは、スプレッドシートが大好きなたぬ(@tanuhack)です!

商品IDや日付、アルファベットの羅列など数行に渡って、番号を振ることってありませんか?

1つのセルごとに値を入力している人は、お疲れ様です。これからも無駄な時間を消耗し続けて下さい。

auto-serial-number1
終わってるやり方

セルの右下を下方向にドラッグしている人は、良い線をいっています。が、もう一工夫欲しいところですね。

auto-serial-number2
あと一工夫ほしいね

ドラッグの範囲が数十行とかだとドラッグで良いですが、これが数千、数万単位になってもあなたはドラッグをし続けますか?

今回紹介する方法を使えば、一瞬で指定の範囲内を連番することができます!

効率化の世界へ、ようこそ。それでは始めます。

数字を連番する方法

auto-serial-number3

1000行、数字で連番する数式
=ARRAYFORMULA(ROW(A1:A1000))

自動で連番させるには、ARRAYFORMULA関数を使います。

簡単に説明すると、『指定したセルの行番号を返す』ROW関数を、A1セルからA1000セルまで適用しているという訳です。

お察しのとおり、範囲をA10000セルにすると1から10000行まで連番することができます。

[aside]ARRAYFORMULA関数の詳細はこちらからどうぞ
スプレッドシートの『ARRAYFORMULA』関数を使って表示速度を高速化させよう! [/aside]

アルファベットを連番する方法

auto-serial-number4

次はアルファベットの連番です。

先程紹介したARRAYFORMULA関数に、いくつか関数を組み合わせて実現させます。

AからZZまで連番する数式
=TRANSPOSE(ARRAYFORMULA(REGEXREPLACE(ADDRESS(ROW(A1:ZZ1),COLUMN(A1:ZZ1),4),”\d”,””)))

  1. ADDRESS:セルの番地を返す
  2. REGEXREPLACE:正規表現が一致するものを別のテキストに置換する
  3. TRANSPOSE:転置行列を作成する

それぞれの関数の詳細は割愛しますが、ざっとどんなことをしているかと言うと…

ADDRESS関数でセルの番地を取得しREGEXREPLACE関数でセルのアルファベットだけ抽出してARRAYFORMULA関数で指定の範囲だけ計算を実行し最後にTRANSPOSE関数で行列を入れ替えています

カラフル過ぎて見づらいかな?笑

ちなみに、小文字のアルファベットで連番したい場合は、LOWER関数を使います。

auto-serial-number5

小文字のアルファベットで連番したい
=TRANSPOSE(ArrayFormula(REGEXREPLACE(LOWER(ADDRESS(ROW(A1:ZZ1),COLUMN(A1:ZZ1),4)),”\d”,””)))

日付を連番する方法

日付だって連番できるゾー。

けっこう目玉なハックかもしれない。

auto-serial-number7

9月1日から100行連番したい
=ARRAYFORMULA(DATE(2018,9,1)+ROW(A1:A100)-1)

日付の計算は、DATE関数に数字を足すことで実現できます。

例えば、9月30日に1足した場合、9月31日は存在しないので、自動で10月1日になります。

最後に1引いているのは、ROW(A1)の値が1になってしまい、A1セルが9月2日になってしまうからです。

DATE関数に入れる日付は、最初に表示したい日付から適宜設定してください。

16進数を連番する方法

さいごに16進数を連番する方法を紹介します。

「16進数?なにそれ、美味しいの?」状態の人は、回れ右です。

誰得感が拭えないですが、めげずにやります。10進数から16進数の変換は、DEC2HEX関数を使います。

auto-serial-number6

16進数で連番したい
=ArrayFormula(DEC2HEX(ROW(B1:B255)))

さいごに

今回は、ARRAYFORMULA関数を使って、数字やアルファベット、日付などを、自動で連番する方法を紹介しました。

いかにパソコンに作業させるか…。腕の見せ所ですね!

スプレッドシートはARRAYFORMULA以外にも、素晴らしい関数がたくさん用意されています。

知っていたらドヤれる!スプレッドシート独自の関数を10個まとめてみた

ぜひ、活用してあなたではなく、パソコンに仕事させてみてください。

それでは