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

SPONSORED LINK

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

商品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年3月22日

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

それでは

SPONSORED LINK

Time Ticketはじめました

スプレッドシートで、こんなお悩みありませんか?

  • 「シートの読み込みを高速化させたい」
  • 「あるサイトのデータを自動で定期的にスプレッドシートに記入させたい」
  • 「別のスプレッドシートからデータを持ってきたい」
  • 「条件付き書式を使って、自動でシートに色をつけたい」
  • and more …

当講座に対してのレビュー

ABOUTこの記事をかいた人

たぬ

26歳。作業の90%をPythonにやらせているデータサイエンティスト兼ブロガー。 単純作業を自動化させ、クリエイターの生産性を上げるために当ブログを設立。毎月15万人に読まれています。 ストレングスファインダー:戦略性・最上志向・目標志向・未来志向・個別化