Excel を使って、親番や枝番に対応した複雑な連番を作成する

はじめに

データを扱う仕事をしていると、管理番号やIDといった、ある法則に基づいた連番をよく目にすることがあると思います。Excel などの表計算ソフトでいろいろな連番を作成する方法を紹介します。

タイトルには「Excelで」、とありますが、Google のスプレッドシートなどでも使用できるよう、基本的な数式のみ使用して紹介しています。

連続した親番、枝番からなる連番の作成

親番、枝番とは、階層化されたデータ構造を表現するときに使用されることが多いと思います。たとえば 1グループあたり 15 個のデータがあり、これが全部で 100 グループあるようなデータの場合、グループ番号(親番)、グループ内のデータ番号(枝番)に区切って、連番「ID-001-01、ID-001-02、ID-001-03、…、ID-001-15、ID-002-01、…、ID-100-15」のように、区切り文字をはさみながら付けていくかと思います。

一般化すると、「ID-<グループ番号>-<グループ内データ番号>」の形式となります。Excel ではこのような連番が工夫次第で作成できます。

インデックス番号作成

親番、枝番を持つ連番は構造化された表現ですが、これを一元的に表現した「インデックス番号」を作成します。たとえば、「ID-002-01」は 2グループ目の1つ目のデータなので、全体としては 16 番目のデータとなります。よって、インデックス番号は 16 とします。このような考え方で、全部で 100 [グループ] × 15 [データ/グループ] ＝ 1,500 [データ] となり、最大のインデックス番号は 1,500 となります。

セル A1 に「1」を、セル A2 に「=A1 + 1」をそれぞれ入力してください。
セル A2 をコピーして、セル A3 から A1500 を選択してから貼り付けてください。

インデックス番号から親番を作成

インデックス番号を 15 個ずつ区切れば 100 のグループを表現できます。つまり、インデックス番号を 15 で除算する事がここでは重要な考え方になります。もちろん、単純に 15 で割ると、小数の値になってしまいますので、工夫が必要です。

ステップ1 (未完成)

セル B1 に「=A1/15」を入力してください。
セル B1 をコピーしてセル B2 から B20 を選択してから貼り付けてください。

小数の値になりました。これを整数にします。

ステップ2 (未完成)

セル B1 に「=INT(A1/15)」を入力してください。
セル B1 をコピーしてセル B2 から B20 を選択してから貼り付けてください。

整数にはなりましたが、値が 0 からのスタートになっています。1 からスタートするように修正します。

ステップ3 (未完成)

セル B1 に「=INT(A1/15) + 1」を入力してください。
セル B1 をコピーしてセル B2 から B20 を選択してから貼り付けてください。

セル B15 に注目してください。ここは 1 とならなければなりませんが、繰り上がって 2 となっています。セル B14 は 1 ですので、列 B を全体的に1行下げられれば解決します。この場合、「列 B を 1 行下げる」＝「1 行上の列 A (の値)を参照する」と考えることができますので、列 A の値から 1 を引いたものに置き換えます。

ステップ4 (未完成)

セル B1 に「=INT((A1 – 1) / 15) + 1」を入力してください。
セル B1 をコピーしてセル B2 から B20 を選択してから貼り付けてください。

無事、B15 と B16 の間を境に、1 繰り上がるようになりました。これで、インデックス番号をもとに、15 個おきに 1 ずつ繰り上がる値を作成できました。最後の仕上げとして、ゼロ埋めします。

ステップ5 (完成)

セル B1 に「=RIGHT(CONCATENATE(“000”, INT((A1 – 1) / 15) + 1),3)」を入力してください。
セル B1 をコピーしてセル B2 から B1500 を選択してから貼り付けてください。

ゼロ埋めの考え方は、値の左側にゼロ埋め用の文字列を結合 (CONCATENATE関数) して、右から目的の桁数だけ取り出す (RIGHT関数) 、といったものになります。これで、グループ番号(親番) を作成できました。

インデックス番号から枝番を作成

枝番はインデックス番号とグループ番号(親番)から作成します。枝番を計算する考え方はグループ内で自分が何番目になるか、を求めることです。「自分のインデックス番号」から「1 つ目のグループから自分のグループの 1 つ前のグループまで、にあるすべてのデータ数」を引けば、自分のグループが先頭に来ることになります。そしてこの時の値が枝番になります。

ステップ1 (未完成)

セル C1 に「=A1 – (B1 – 1) * 15」を入力してください。
セル C1 をコピーしてセル C2 から C20 を選択してから貼り付けてください。

初めの考え方のとおり数式にできましたので、これだけで目的の値を作成できました。親番と同様に、ゼロ埋めして仕上げます。

ステップ2 (完成)

セル C1 に「=RIGHT(CONCATENATE(“00”, A1 – (B1 – 1) * 15),2)」を入力してください。
セル C1 をコピーしてセル C2 から C1500 を選択してから貼り付けてください。

これで、データ番号(枝番) を作成できました。

連番作成

いよいよ、目的の連番を作成します。ここまでできれば、もうあとは結合するだけですね。

セル D1 に「=CONCATENATE(“ID-“, B1, “-“, C1)」を入力してください。
セル D1 をコピーしてセル D2 から D1500 を選択してから貼り付けてください。

断続的な値や文字が入った連番の作成

連番には、連続した数字だけでは表現できないものも多くあります。ただ、規則性さえあれば、Excel でも作成することができます。

今回、次のように 4 つの色と 5 つのサイズのラインアップがある商品について、連番を作成したいと思います。

色は “W”(白), “K”(黒), “R”(赤), “GY”(灰) の4つ。
サイズは “S”, “M”, “L”, “LL”, “3L” の5つ。

マスターの定義

まず、色とサイズのマスターを作成します。インデックス番号を元に、どの色、どのサイズを並べるかを決めていく時に使用します。

色マスターの定義

セル I1 に「1」、セル J1 に「W」を入力してください。
セル I2 に「2」、セル J2 に「K」を入力してください。
セル I3 に「3」、セル J3 に「R」を入力してください。
セル I4 に「4」、セル J4 に「GY」を入力してください。

サイズマスターの定義

セル L1 に「1」、セル M1 に「S」を入力してください。
セル L2 に「2」、セル M2 に「M」を入力してください。
セル L3 に「3」、セル M3 に「L」を入力してください。
セル L4 に「4」、セル M4 に「LL」を入力してください。
セル L5 に「5」、セル M5 に「3L」を入力してください。

インデックス番号作成

親番、枝番の考え方と同じです。今回、100 個の商品があるとして、各商品に色違い、サイズ違いが 20 種類、合計 2,000 個の連番を作成します。

セル A1 に「1」を、セル A2 に「=A1 + 1」をそれぞれ入力してください。
セル A2 をコピーして、セル A3 から A2000 を選択してから貼り付けてください。

インデックス番号から商品番号を作成

これは先の親番の考え方と同じです。インデックス番号を 20 個ずつ区切れば 100 の商品を表現できます。

ステップ1 (完成)

セル B1 に「=RIGHT(CONCATENATE(“0000”, INT((A1-1)/20)+1), 4)」を入力してください。
セル B1 をコピーしてセル B2 から B2000 を選択してから貼り付けてください。

数式については、先の『インデックス番号から親番を作成』を参照してください。

インデックス番号と商品番号から色を取得

色はインデックス番号と商品番号を基準に 5 個 (サイズの種類数) ずつ並べます。枝番を計算したときの考え方をベースに、親番を計算したときの考え方を適用します。

ステップ1 (未完成)

セル C1 に「=A1 – (B1 – 1) * 20」を入力してください。
セル C1 をコピーしてセル C2 から C25 を選択してから貼り付けてください。

商品の中で、1 から 20 の値が割り当てられました。この値を使って、今度は親番を計算します。

ステップ2 (未完成)

セル C1 に「=INT(((A1 – (B1 – 1) * 20) – 1) / 5) + 1」を入力してください。
セル C1 をコピーしてセル C2 から C25 を選択してから貼り付けてください。

1 から 4 の値になりました。勘の良い方は、これらの値と色マスター(1 から 4 の値) に気が付いたかもしれません。そう、これらの値を使って、VLOOKUP関数で色を取得します。

ステップ3 (未完成)

セル D1 に「=VLOOKUP(C1, I:J, 2)」を入力してください。
セル D1 をコピーしてセル D2 から D25 を選択してから貼り付けてください。

インデックス番号、商品番号を元に、マスターを使って値を取得できました。これまで、同じセルの中で数式を発展させていきましたが、マスターを参照するような場合は、参照前の値がどのようになっているか確認しやすいよう、数値をそのままにしておくといいと思います。

ステップ4 (完成)

セル C1 と D1 をコピーしてセル C2 から D2000 を選択してから貼り付けてください。

これで色の部分が完成しました。

インデックス番号と商品番号、色コードからサイズを取得

サイズはインデックス番号と商品番号、色コード(変換前の数値)を基準に 1 から 5 の値を繰り返すように並べます。この数値を使って、サイズを表す文字列に置き換えます。

ステップ1 (未完成)

セル E1 に「=A1 – (B1 – 1) * 20」を入力してください。
セル E1 をコピーしてセル E2 から E25 を選択してから貼り付けてください。

商品の中で、1 から 20 の値が割り当てられました。この値と色コードを使って、さらに枝番を計算します。

ステップ2 (未完成)

セル E1 に「A1 – (B1 – 1) * 20 – (C1 – 1) * 5」を入力してください。
セル E1 をコピーしてセル E2 から E25 を選択してから貼り付けてください。

1 から 5 の値になりました。
　かなり複雑な式に見えますが、次のように置き換えると今まで使ってきた式だとわかります。
　　<インデックス番号> – (<親番> – 1) * <枝番の数>

ステップ3 (未完成)

セル F1 に「=VLOOKUP(E1, L:M, 2)」を入力してください。
セル F1 をコピーしてセル F2 から F25 を選択してから貼り付けてください。

インデックス番号、商品番号、色コードを元に、マスターを使って値を取得できました。

ステップ4 (完成)

セル E1 と F1 をコピーしてセル E2 から F2000 を選択してから貼り付けてください。

これでサイズの部分が完成しました。

連番作成

結合して目的の連番を作成します。

セル G1 に「=CONCATENATE(B1, “-“, D1, “-“, F1)」を入力してください。
セル G1 をコピーしてセル G2 から G2000 を選択してから貼り付けてください。

おわりに

Excel で連番を作成するときのキモは、連番の規則性を見つけ出すこと、Excel の関数をどれだけ知っているか、に尽きると思います。

Excel の関数については、今回紹介したものを使いこなすことを一つの目標にしてみてください。

「こんな連番を作りたいけどどうしたら…」なんてものがありましたら、お気軽にコメント欄で質問してください。時間があれば考えたいと思います！