Excel表の中に数字で連番を振る際、グループやカテゴリで枝番を設けたい場合があります。数が多くなければ手作業で一つ一つ入力していくのも有りですが、ケアレスミスを防ぐ目的でも数式で簡単に入力できると便利です。
目次
完成イメージ
サンプルとして、各都道府県の地方区分別に枝番を振ることとしています。
枝番を振る方法
関数の組み合わせ次第では様々な方法で実現可能です。
IF関数
地方区分が一つ上の行と一緒のであれば上の行＋1した値を枝番とし、一つ上の行と異なる場合は1を振りなおしています。条件付き書式で表内のデータを塗り分ける際によく使う数式です。
COUNTIF関数 × INDIRECT関数
INDIRECT関数は「セルの参照を文字列で指定することが出来る関数」になります。セルの参照というとやや分かりにくいので以下例を参照ください。
C3セルに入力した数式は「=INDIRECT(A1)」となっています。INDIRECT関数は指定されたA1セルを見に行きますが、A1セルに入力された「"A3"」という文字列をセル番地として扱うため、最終的な数式の結果としてはA3セルの値「あいうえお」が表示されています。
最終的に表示される「A3に入力された値」を変更すると、元のINDIRECT関数の数式はそのままに結果のみを差し替えることが可能です。これだけではやや伝わりにくい部分があるので続けます。
INDIRECT関数は引数の文字列をセル番地と読み替えることが出来るため、カッコ内に"A1"と入力すればA1セルの値を読みにくことが出来ます。
上記では「"A" & 2*2」 →「"A4"」という文字列がINDIRECT関数のカッコ内に含まれており、最終的にA4セルの"たちつてと"が表示されています。どんなやり方をしてもExcel上で存在するセル番地をINDIRECT関数に渡してあげればセル番地指定で値を引っ張ってくることが出来るのが特徴です。
話を戻しますが、サンプル数式内のINDIRECT関数では、"D3:D"にROW関数を用いた現在行を付与することで範囲を開始行～現在行までに絞り込んでいます。
INDIRECT関数に動的な範囲を指定することで、現在行までの範囲における数の合計を表示することが出来ます。
ROW関数 × MATCH関数
Excelの現在行から「地方区分が最初に出現する表内の行番号」をマイナスしています。上記例では「D2:D48」の範囲で最初に"東北地方"がヒットするのはD3セルとなり表内では2番目となります。現在のExcel行番号「3」に対しMATCH関数の結果「2」をマイナスすることで最終的に「1」が表示されます。完全に整列された表でなければ使用できませんが、動くことは動くということでご紹介でした。
