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

auto-serial-number

SPONSORED LINK

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

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

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

auto-serial-number1

終わってるやり方

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

auto-serial-number2

あと一工夫ほしいね

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

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

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

数字を連番する方法

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

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

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

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

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

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個まとめてみた

2018.03.22

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

それでは

SPONSORED LINK

auto-serial-number

Time Ticketはじめました
実務で即戦力になるためのスプレッドシート講座開講!

記事を読んでも、よく分からなかった人やもっと深く知りたい人はこちらからご連絡ください。

ブログ特典!!

『クーポンコード:9876』とメッセージいただければ、どのレベルのコースでも初級コースと同じ価格で教えます!!

スプレッドシートをより深く学んで、実務で即戦力になりませんか?

ABOUTこの記事を書いてる人

たぬ(谷口健太)

25歳社会人。本職は福岡の通販会社でWebマーケッターやっとります。 プログラミング✕マーケティング=最強説を唱えており、非効率的な世の中に革命を起こそうと本ブログを設立。 ストレングスファインダー:戦略性・最上志向・目標志向・未来志向・個別化

NEW POSTこのブログの最新記事