[EXCEL] 番号付けの応用編 通番・親番・枝番を自動で入れる
【まとめ】
・連番/親番/枝番は自動で付ける。
・連番：=SEQUENCE(COUNTA(E:E)-1) E:データが入る列
・親番：=IF(E2="","",XLOOKUP(E2,I:I,H:H))
E2：データが入るセル I:データ一覧 H：親番一覧
・枝番：=IF(E2="","",COUNTIFS(E$2:E2,E2))
E2：データが入るセル
いずれも、E2にデータが入ると、番号が自動で入る。
却ってわかりづらい？
こちらも、判りづらいですね
【説明】
これまでの記事で
① 番号（通番）付けの重要性
[EXCEL] データには番号を振る。番号付けは効率化の基本。
② 番号（通番）の付け方
[EXCEL] エクセル超基本 番号（通番）の入力方法
③ 親番・枝番、項目別の番号の付け方
について説明してきました。
これらは、「すでにデータが入っている表」への番号付けについてでした。
実務では、（申請書等の）データを入力しながら番号を付けていくことが多いかと思います。
その際、番号を手入力するのではなく（誤りの元）、自動で番号を付ける方法の一例をお示しします。
○前提
こんな表へデータを入力していくと想定します。
実際は、事業者名の後に、住所や連絡先、代表者名、項目（申請内容や申請額等）が並んでいくかと思いますが、そこは省略します。
また、「コード表」を隣に並べていますが、本来は別シートに作るべきです。
データが入力されたら以下の番号を入れます。
① 通番 データの上から１、２、３と順番に振ります。
いずれデータを並び替えた際に、この通番で並び直せば、入力時の並び順に戻せるものにします。
② 親番 「都道府県別のコード番号」です。右の「コード表」から該当する都道府県に番号を表示します。
③ 枝番 都道府県別の通し番号です。１から順に１ずつ増えていきます。
④ 番号 親番＋枝番です。
データと番号が入ると以下の通りとなります。
番号は手入力も可能ですが、面倒かつ間違える恐れがありますので、自動で表示される方法を考えます。
方法は色々あると思いますので、以下は、一例として捉えてください。
① 通番
これは、すでに説明したとおりです。
参考：[EXCEL] エクセル超基本 番号（通番）の入力方法
A2セルに以下を入れます。
=SEQUENCE(COUNTA(E:E)-1)
COUNTA関数で、E列のデータ数（都道府県）を数え（１行目である表頭の「都道府県」は除くため、１を引きます）、その数だけ SEQUENCE関数で番号を入れます。
SEQUENCE関数はスピル（溢れる）という機能があるので、下のセルに計算式を入れなくても、A３セルから下にも同じ内容が入ります。
ただし、E列にデータが入っていない状態だと、
A2セルには「＃CALC！」というエラー表示されます。
これは、COUNTA関数の計算結果が０になるためです。
「#CALC！」は「計算できません」エラーです（CALCは、calculate/カリキュレート/計算する の意味）。
これは、E２セルにデータを入れれば解決します。
上の図では、E列にはデータが１つしか入っていないので、通番も「１」だけです。それ以下は空欄になっていますが、E列にデータが入ると自動で番号が入ります。
なお、SEQUENCE関数で番号を付けた表は、並び替え（例：都道府県の親番順）ができません。
並び替えをしたい場合は、全部の通番が確定した後、列全体をコピー（CTRL＋C）してから、そのまま「値のみ貼り付け」（Alt ⇒ E ⇒ S⇒ V 等）しておきます。
これにより、並び替えが可能となるとともに、並び替えをした後でも、この「通番」を元に並び替えせば、元の順番に戻せます。
② 親番
親番号は、前の記事で以下の通り入れていました。
参照：[EXCEL] 番号を振る（応用編）親番/枝番/項目別の通し番号を振る
=XLOOKUP(E2,I:I,H:H)
「都道府県」欄から、同じものをコード表のI列から探し、見つかった行のHの番号を表します。
ただし、Ｅ２セルにデータが入っていないと正しい親番が表示されません。
そこで、Ｅ２セルにデータが入っている（Ｅセルが空欄でない）場合のみ、この数式を有効にします。
具体的には、Ｅ２セルが空欄ならＢ２セルも空欄にし、そうでなければ（＝Ｅ２セルにデータが入ってれば）、Ｂ２セルには数式（=XLOOKUP(E2,I:I,H:H)）の結果を表示させます。
数式は以下の通りとなります。
=IF(E2="","",XLOOKUP(E2,I:I,H:H))
これを下にコピーすると、E列にデータが入っていなければ、B列も空欄になります。
Eセルにデータが入れば、自動で番号が表示されます。
＊数式は以下でも同じです。
=IF(E2<>"",XLOOKUP(E2,I:I,H:H),"")
これは、Ｅ２セルが空欄ではなければ数式の結果を表示し、そうでない（＝空欄である）なら空欄を表示する、という意味です。
前者は「空白なら○（空白）、空白でなければ■（数式の結果）」、
後者は「空白でなければ■（数式の結果）、空白なら○（空白）」となります。
どちらでも結果は同じですが、条件分岐を考えると、後者の方が判りやすいかもしれません。
私は、最初に セル=””,"" と入れる方が楽かつ分かりやすいのでこちらを多用しています（ 「<>””」は直感的ではないという印象もあるためです）。
ここは好みですが、同一のファイルでは統一しておいた方が、後任は理解しやすいでしょう。
＊XLOOKUP関数を使う場合、Eセルに入る都道府県名は、コード表にあるものでなければいけません。一字でも違ったり、半角と全角が違ったり、大文字と小文字（ケとヶ）が違ったりしても、正しい「親番」が表示されません。
そのため、そして、入力を簡単にするために、「都道府県」の欄はプルダウンリストを使っています。
プルダウンリストは、「データの入力規則」で設定します（詳細は省略）。
Alt ⇒ A（データ）⇒V（データの入力規則）⇒ V でも開きます。
（なぜ、「D」ではなく、「A」が「データ」なのか、理解に苦しむところです。）
③ 枝番
次は枝番です。この枝番は、同じ項目（都道府県）における通し番号になります。都道府県はまとまって入力されるわけでないので、いちいち目で確かめていたら面倒＆間違います。
前の記事では、自動で入れる数式として以下を示しました。
参照：[EXCEL] 番号を振る（応用編）親番/枝番/項目別の通し番号を振る
=COUNTIFS(E$2:E2,E2)
Eセルに入れた都道府県と同じものがE列にいくつあるか、を出します。
ただし、「いくつあるか」を調べる範囲は、２行目から、現在のセルまでです。その範囲にある同じ都道府県の数が枝番になります。
この数式も、Eセルが空欄だと正しく計算できません。
従って、親番と同じく、Eセルが空欄なら空欄に、Eセルにデータが入っていれば数式の結果を表示（返す）するようにします。
=IF(E2="","",COUNTIFS(E$2:E2,E2))
理屈は、親番と同じです。
これで、E列が空欄の場合、枝番も空欄になります。
E列にデータが入れば、枝番も入ります。
上の図では、Ｃ４セルに「神奈川県」が入ると、Ｃ２セルにも神奈川県があるので、Ｃ４セルの枝番が「２」になってていることがわかります。（「参照元のトレース」はＣ５で表示しています。Ｃ４セルだと見づらくなるので）
④ 番号 （親番＋枝番）
これは単に親番と枝番を繋ぐだけです。
＝親番&"-"&枝番でもいいのですが、１-１、１-２、１-３となっていきます。枝番が２桁以上になると「桁ずれ」が起きます。
1-1
1-21
そもそも、文字データ（文字列）になってしまうので、数値にします。
前記事同様、親番号に1000を掛け（枝番が999までの場合）、枝番号を足します。
「セルの書式設定」で親番号を２桁表示の頭ゼロ、親番と枝番の間には「-」を入れます。
その際、数式を IFERROR関数で「包み」ます。
=IFERROR(B2*1000+C2,"")
これは、Eセルにデータが入っていない場合、親番も枝番も空欄となり、結果、「B2*1000+C2」という計算がエラーになります。
その際、エラーを表示しないで空欄にするためです。
これをしないと、以下の通りとなります。
Eセルにデータが入れば、このエラー表示は消えますが、きれいではないので、エラー処理をして空欄にしておくのです。
自分だけが見るファイルならエラー表示されたままでも構いませんが、エラー表示があると目が迷いますので、なるべく表示しないようにしておいた方がいいでしょう。なお、人（上司）に見せる表は、エラーの非表示は必須です。
以上で、通番・親番・枝番を自動付ける方法でした。
実施の入力の仕方
実際の入力は以下の通り行います。
① 入力を始めるセルを選択
② CTRL＋A で表全体を範囲指定
③ セルに入力
④ 右端まで行ったら、Tabキー押下で下の行の左端に移動
⑤ Tabキー連続押下で番号欄をスキップ
⑥ 都道府県を入力
⑦ 左の番号が自動で入る
なお、上のように表を範囲指定しなくても、一番右端のセルを入力した後にTabではなくEnterを押すと、次の表の左端にセルが移動します。
これでも問題ありませんが、「今のどのセルを入力しているか」をはっきりさせるためには、表全体を範囲指定することで、表全体を灰色に、選択セルを白抜きにできます。
＊最新のエクセルであれば、選択しているセルを、より目立たせることが可能です。
詳しくはこちら [EXCEL]選択セルは「セルにフォーカス」で目立たせる！（マクロ不要）
あるいは、番号の列以外の列を範囲指定してしまえば、番号列をスキップする必要がなくなります（ただし、列を手動で範囲指定する必要あり）。
データ入力⇒Tab でセルが右に移動します（Enterだと下に移動してしまう）。
実際の入寮方法は以上です。
数式は最初から入れておくべきか？
実務でデータを入力していく場合、全部で件数がいくつあるか、は通常わかりません。データを入れ終わらなければわかりません。
従って、表の長さ（行数）をどれだけにすればいいか、つまり、自動で番号を入れる数式を何行目までコピーしておけばいいか、わかりません。
そんな時は、とりあえずの想定行数まで数式をコピーしておき、あとは、データが増えるたびに数式をコピーしていく、というやり方があります。
「データが増える度に数式をコピーするのは面倒ではないか？」と思う方もいるかと思いますが、そんなことはありません（そもそも、A列はスピルが機能しているので、コピー不要です）。
上のセルのコピーは、コピー元の数式をCTRL+C等でコピーしておかなくても、コピーしたいセルで CTRL+D を押すだけでできます。
あらかじめ数式を入れておくか、データ入力の都度コピーしていくかは、状況とお好み次第です。
（表をテーブル化することで、数式も自動で入っていく、という方法もありますが、今回は省略します）。
都道府県別に件数がいくつあるかを出す
既に「枝番」という形で、都道府県別に件数がいくつあるか、は出ています。
しかし、データは件数が多いと一部分しか見えないので、都道府県別の件数はわかりません。
都道府県別の件数は、「コード表」に並んでいる都道府県の一覧に併せて表示した方が判りやすいでしょう。
ついでに、例えば、何かの申請額などの場合、額の合計も一緒に出るといいでしょう。
あるいは、日々受付⇒入力をしているとして、日別の処理（受付）件数を把握したい場合もあります。
例えば、こんな感じ。
実際は、「藤堂府県別集計表」や「日別受付状況」は別シートにします。
上限にも「並べない方がいいでしょう。
もはや、「番号の付け方」からは脱線しますので、これについては別記事にしますが、上の図のように、「入力したら、自動で集計されている」が理想です。
以上、参考になれば幸いです。
もし何か「発見」があったら、「スキ」「フォロー」「チップ」していただける嬉しいです！
（作業 １日 ３ｈ）
