Excelで分類表の各分類項目に項番を付番するには

Excel Tips

2022.05.182022.09.10

この記事は約15分で読めます。

「Excel Tips」ではExcelを使用していて気付いたことを取り上げています。

今回はExcelでタイトル行を持たない分類表に項番を付ける方法をご紹介いたします。(タイトル行がある場合の方が使う関数が少なくて済みますが…)

やりたい事のイメージとしては下記のようになります。下記は地方・都道府県・区or特別区の分類表です。

関東	東京	千代田区
		…
	神奈川	横浜市中央区
		…
	千葉	千葉市中央区
	…	…
近畿	大阪	大阪市中央区
		…
	京都	京都市中京区
…	…	…

左の各列に表に項番を付与する。

1	関東	1	東京	1	千代田区
				↓ 23	…
		2	神奈川	1	横浜市中央区
				↓ 15	…
		3	千葉	1	千葉市中央区
			…	↓ 6	…
2	近畿	1	大阪	1	大阪市中央区
				↓ 24	…
		2	京都	1	京都市中京区
	…		…	↓ 11	…

基準リストの項目数やグループの数が少なければ全て手作業でも対応できる事と存じますが、今回想定しているのは全て手作業では時間がかかり過ぎて対応できない場合です。

なおマクロ(VBA)は使わないで対応しています。その代わりにIF関数、FIND関数、ISERROR関数などの他にINDIRECT関数やADDRESS関数なども利用しています。(INDIRECT関数やADDRESS関数はタイトル行があれば使わなくできます。詳細は後述いたします。)

32bit版Excel 2016のバージョン2204(ビルド 15128.20224)を使用して検証しています。

総務省「日本標準産業分類」の分類項目名を使用する
1. 使用しない文字情報を取り除く
分類表の各項目の前に項番を付番するには
分類項目を1行に並べるには
最後に

総務省「日本標準産業分類」の分類項目名を使用する

今回は下記総務省のWebページに掲載されている「日本標準産業分類」をご説明の例題として使用いたします。

総務省｜統計基準等｜分類項目名、説明及び内容例示

ただこのページをそのままコピーしてワークシートに貼り付けると、使わない文字情報まで貼り付いてしまいます。

そのためデータの加工方法を手短にご説明いたしますが、本テーマのみをお知りになりたい方は「分類表の各項目の前に項番を付番するには」の章まで読み飛ばしていただければ幸いです。

使用しない文字情報を取り除く

「大分類 A　農業、林業」から「大分類 T　分類不能の産業」の最後までの範囲を選択コピーして「産業分類」という名前にしたワークシートに「貼り付け先の書式に合わせて」貼り付けると下図のようになります。

①「大分類」(最後は半角スペース)と「中分類　」(最後は全角スペース)を取り除く

分類表に整形するためにタイトルの文字列はそれぞれ空文字に置換して取り除きます。(画面省略)

②「区切り位置」の機能を使ってコードと名称を分割する

つぎにA列を選択して「データ」リボンメニュから「区切り位置」をクリックしてスペースで区切られたデータとして処理します。

デフォルトのまま「次へ」
区切り文字に「スペース」を選択して、「連続した区切り文字は1文字として扱う」にチェックを入れます。
最初の列を選択して、列のデータ形式を「文字列」に設定します。

「完了」ボタンをクリックすると下図のようになりますが、C列の文字情報は使わないので列選択をして削除or消去してください。

③途中のスペース行や”＜項目一覧へ＞”の文字列を行削除する

途中に現れるスペース行や”＜項目一覧へ＞”の文字列は使わないために削除します。もちろん手作業でもできますが、省力化するやり方をご紹介します。

タイトル行はありませんがA1:B2147のセル範囲を選択して「データ」リボンメニュからフィルタを設定します。
- 途中にスペース行があるので必ずすべてのデータ範囲を選択します。
“(すべて選択)”のチェックを外します。
“<項目一覧へ>”をチェック
“(空白セル)”をチェックしてOKボタンをクリックします。

1行目以外のフィルタで絞り込まれた状態で表示されているすべての行(48行から2,142行)を選択して、「ホーム」リボンメニュの「検索と選択」から「条件を選択してジャンプ」をクリックします。(1行目はタイトル行ではなくデータ行ですので削除してしまわないように操作します。)
「可視セル」を選択してOKボタンをクリックします。
可視セルが選択された状態で「行の削除」をします。
最後にフィルタを解除します。

④項目を分類に従って列に振り分ける

Web上では大・中・小・細分類に従って段組みされていましたが、ワークシートに貼り付けると横には広がらずに縦1列に並んでしまうので、元のような段組みの並びに整形する必要があります。これを手作業でやるのは2,109行もあるので大変です。

そこで「日本標準産業分類」には分類コード(大分類は1文字、中分類は2文字、小分類は3文字、細分類は4文字)が付与されているので、これを使って次のような数式を列に入力して対応します。

=IF(LEN($A1)=1,$A1&"-"&$B1,"_")    <--大分類の場合
=IF(LEN($A1)=2,$A1&"-"&$B1,"_")    <--中分類の場合
=IF(LEN($A1)=3,$A1&"-"&$B1,"_")    <--小分類の場合
=IF(LEN($A1)=4,$A1&"-"&$B1,"_")    <--細分類の場合

A1セルの分類コードの長さが大分類は1、中分類は2、小分類は3、細分類は4だったら、A1セルとB1セルの間に”-“を挿入して結合して返します。
長さが1文字でなければ”_”(アンダースコア)を返します。
- “”(空文字)ではなく”_”(アンダースコア)にしているのは、後で一括置換するためです。
  と言うのもExcelの操作では””(空文字)を置換する事ができません。
  では「なぜ置換が必要なのか?」と言うと、セルに空文字も含めて何も入力がない状態でないと「1つのセルに入りきらない文字列を右隣りのセルに拡張して表示」してくれないためです。

C1セルに大分類、D1セルに中分類、E1セルに小分類、F1セルに細分類を入力・コピーして、それぞれを2,109行まで貼り付けたのが下記になります。

以上でデータの整形は完了です。次の章から本題に入ります。

分類表の各項目の前に項番を付番するには

加工した「日本標準産業分類」を例題に使用して、大分類・中分類・小分類・細分類の各項目の前に項番を付番するやり方をご紹介いたします。

まずは各項目の前に細い列をそれぞれ「列の挿入」をしておきます。

分類項目の区切りを見える化する数式を追加する

項番を振るためには、対象列に分類項目が出現するこどにカウントアップする必要があります。

その計算がし易くなるように下記の数式を入力して項目の区切りを見える化します。

=IF(D1<>"_",1,0)

D列は大分類になりますが、分類項目が無い場合は”_”(アンダースコア)になっています。そこで”_”(アンダースコア)は「0」、それ以外は「1」を返します。

上図のようにK1セルに数式を入力したら、それをコピーしてK2:K2109のセル範囲に貼り付けをするかオートフィル機能で数式をセットしてセル幅を調整します。
各分類名の前に項番用の列を追加しているので、K列を列コピーしたら1列空けて「コピーしたセルの挿入」で数式をコピー貼り付けする操作を3回繰り返します。

次に1列空けたL列・N列・P列・R列の4箇所に項番を計算するための数式を入力して行きます。

項番を計算するための数式を追加する

項番の区切りは見える化されたので、各分類の付番規則に基づいた数式をセットします。

付番規則を決める

中分類以下の場合は、上位の分類が変わったら一旦項番をリセットする必要がありますが、大分類は(最上位のために)リセットする必要がない唯一の分類です。

そのため大分類の項番は「K列の値を先頭からカレント行(現在の行)まで合計して求める」事もできますが、この考え方は中分類以下には適用でないので良くありません。

汎用性のある付番規則として今回は次のような仕様にします。なお太字の「色」は、次にご説明する数式での該当箇所と一致させていますのでお含み置きください。

項番をカウントアップするためには、この数式をセットする列の一つ前の行の値にカレント行(現在の行)の見える化した数式の値(0 or 1)を加算する。
- 分類の項目名が変わると見える化した数式の値は「1」なので、これにより1づつカウントアップされます。
大分類以外では、上位の分類の見える化した数式の値が変わった(「1」になった)ら、この数式の値を「0」にする。
- 大分類では、先頭行だけこの数式の値を「1」にします。

言葉だけでは分かり難いと思いますので実際の数式でご説明いたします。なお下記数式を実際に入力するセルはL1セル、N1セル、P1セル、R1セルになります。

なお「何でこんなに分かり難い関数を使うのか?」と思われるかもしれないので、先に「この数式をセットする列の一つ前の行の値」の部分の数式をご説明いたします。

今回は「タイトル行が無い」分類表でご説明していますが、もしもタイトル行が存在していれば「データの先頭行は2行目以降」になるのでセル参照を使って「すべての行が同じ形式の数式」で表す事ができます。

ところが今回は先頭行(「ROW関数」が1を返す行)で、「一つ前の行の値」を取得する数式を作らなければなりません。

これを実装するための関数が、INDIRECT関数とADDRESS関数になります。

INDIRECT 関数 - Microsoft サポート

Excel で INDIRECT 関数を使用して、数式自体を変更せずに数式内のセルへの参照を変更する方法。

INDIRECT関数は「文字列で渡された値」を「セル参照」に変換してくれる関数です。

“A1″という文字列はあくまでも文字列でしかありませんが、INDIRECT(“A1”)とするとこれは「A1セルをセル参照する」のと同じ意味合いになり、「A1セルの値を取得する」事ができます。

ADDRESS 関数 - Microsoft サポート

書式: ADDRESS(行番号, 列番号, , , )

ADDRESS関数を使うと「セル参照を表す文字列」を「ADDRESS(行番号,列番号)」の形式で指定する事ができます。

これに合わせてROW関数、COLUMN関数を使うと「同列の1つ前の行」は

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

と記述できます。

もしろんこの数式をA1セルにこのまま入力したら「ROW()-1」が値を返させないのでエラーになりますが、1行目の時はIF関数で別の数式や値などにセットすればエラーにはなりません。

もっともタイトル行があれば、「A2セル」であれば「A1」の形で一つ前の行をセル参照することはできますがタイトル行が存在しないために、このような複雑な数式を使う事になります。

分類	数式
大分類	=IF(ROW()=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+K1)
	分類表では先頭行は必ず大分類になるので、ROW関数が1の時は1を返します。
中分類	=IF(K1=1,0,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+M1)
	中分類が存在する時は大分類の次に来るので大分類が1の時は0でリセットします。
小分類	=IF(OR(K1=1,M1=1),0,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+O1)
	小分類は中分類の次にくるのですが、大分類を条件に入れないと先頭行の時はエラーになります。
細分類	=IF(OR(K1=1,M1=1,O1=1),0,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+Q1)
	大分類は先頭行でのエラー回避のためですが、中分類を条件に入れているのは「中分類が変わった時でも細分類の項番がリセットされていない」という見た目上の理由からです。従って見た目を気にしないのであればM1=1の条件は省略可能です。

後はL1セル、N1セル、P1セル、R1セルの数式をそれぞれの列の最後の2,109行までコピー貼り付けするかオートフィル機能で数式をセットしてセル幅を調整します。

項番列に数式をセットする

前章までで各分類の項番は既に計算された状態ですので、後は各分類の項番列に数式をセットするだけです。

まずは大分類の項番列のC1セルに次の数式を入力します。

=IF(K1=1,L1,"_")

大分類を見える化したK列の値が1の時に、算出された項番がセットされているL列の値を返し、それ以外は”_”(アンダースコア)を返します。

この数式をコピーしてC2:C2109のセル範囲に貼り付けをするかオートフィル機能で数式をセットします。
後は、C列を列コピーして、E列、G列、I列に数式で貼り付けをすれば完了です。

値貼り付けをして整形する

前章までで分類表への項番の付番は完了しているので、後は最終的な整形の操作になります。

C1:J2109のセル範囲をコピーして、新しいワークシート「項番付き産業分類」を追加して値貼り付けして、すべてのデータ範囲を選択して、ホームメニュタブの「検索と選択」から「置換」をクリックして”_”(アンダースコア)を「何も入力なし」で置き換えします。
完成した項番付き分類表になります。

分類項目を1行に並べるには

ただし完成した「項番付き分類表」を、よくよく見ると冒頭でご説明した「やりたい事のイメージ」と少し違いがあります。

「やりたい事のイメージ」では分類項目が1行に並んでいるのですが、完成した項番付き分類表では大分類から細分類まで階段状に並んでいます。

もっともこの原因は総務省「日本標準産業分類」をデータ加工する際に分類項目が1行に並ぶように整形していなかった事によりますが、あらためてここで分類項目が1行に並ぶようにするために必要な変更箇所をまとめて置きたく存じます。

なお下記にご説明する変更箇所以外の数式は直さなくて大丈夫です。最初からすべてやり直す分けではありませんのでご安心ください。

「項目を分類に従って列に振り分ける」際の変更箇所

大分類から細分類まで階段状に並んでしまう原因は全ての分類項目を下記数式で処理をした事によります。

=IF(LEN($A1)=1,$A1&"-"&$B1,"_")    <--大分類の場合
=IF(LEN($A1)=2,$A1&"-"&$B1,"_")    <--中分類の場合
=IF(LEN($A1)=3,$A1&"-"&$B1,"_")    <--小分類の場合
=IF(LEN($A1)=4,$A1&"-"&$B1,"_")    <--細分類の場合

項目の区切りで分類項目が1行に並ぶようにするためにはそれぞれの分類で数式を次のように修正する必要があります。

なお下記の数式を大分類はD1セル、中分類はF1セル、小分類はH1セル、細分類はJ1セルに入力するものとします。

分類	数式	説明
大分類	=IF(LEN($A1)=1,$A1&”-“&$B1,”_”)	前と同じ数式になります。
中分類	=IF(LEN($A2)=2,$A2&”-“&$B2,”_”)	1行に並べるために中分類の場合は1行下のセルを参照します。
小分類	=IF(LEN($A3)=3,$A3&”-“&$B3,”_”)	1行に並べるために小分類の場合は2行下のセルを参照します。
細分類	=IF(LEN($A4)=4,$A4&”-“&$B4,”_”)	1行に並べるために細分類中の場合は3行下のセルを参照します。

階段状にならないようにするためには、中分類は1行、小分類は2行、細分類は3行ずらしてセル参照を設定する必要があります。

D1セルに大分類、F1セルに中分類、H1セルに小分類、J1セルに細分類を入力・コピーして、それぞれを2,109行まで貼り付けます。

その結果として下図のように分類の区切りで空白行ができる事になります。

付番規則の変更箇所

付番規則で必要な変更箇所は中分類・小分類・細分類でリセットする時の値になります。

分類	数式
大分類	=IF(ROW()=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+K1)
	前と同じになります。
中分類	=IF(K1=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+M1)
	赤字の部分が0→1になります。
小分類	=IF(OR(K1=1,M1=1),1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+O1)
	赤字の部分が0→1になります。
細分類	=IF(OR(K1=1,M1=1,O1=1),1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+Q1)
	赤字の部分が0→1になります。

階段状の場合は「0」にリセットしていましたが、分類項目が1行に並んでいるためにリセットする値を「1」にする必要があります。

後はL1セル、N1セル、P1セル、R1セルの数式をそれぞれの列の最後の2,109行までコピー貼り付けするかオートフィル機能で数式をセットします。

修正後の結果

変更箇所の修正がすべて終わったら下記の操作を実行します。

C1:J2109のセル範囲をコピーします。
新しいワークシート「項番付き修正産業分類」を追加して値貼り付けします。
すべてのデータ範囲を選択し、先の章と同様にホームメニュタブの「検索と選択」から「置換」をクリックして”_”(アンダースコア)を「何も入力なし」で置き換えします。

なお途中の空白行を削除するのはデータの加工方法の「途中のスペース行や”＜項目一覧へ＞”の文字列を行削除する」のところでご説明したフィルタを使用した空白行の削除と同じやり方をご使用ください。フィルタを設定する時のセル選択の範囲はA1:H2106で、フィルタを設定した後の行の削除は3行目から2,105行目までを可視セルに設定します。

以上を実施した後の結果は下図のようになります。