セル結合＆解除が不要なカテゴリ付き一覧シートをつくる

課題

まあタイトルの通りです。カテゴリ（分類）を持つ一覧資料をExcelでつくる場合、以下のような問題点があります。

カテゴリ列のセル結合作業がやたら面倒くさい。ナンバー列などあるともはや地獄（つまり現実的に運用不可能）。
一覧の内容を推敲して行を上下に動かしたり添削したりすると、結合、結合解除、再結合の繰り返しとなる。

f:id:m12i:20130816180845p:plain

カテゴリ列のセルを結合せず、結合するはずだったセルのすべてに同じ内容を記載、その先頭（一番上）の行のセルのみ文字色を付け、他を無色にして隠すという手法もありますが、これは結局面倒の発生源を変えただけですね。

……いえ、たしかにセルの結合と結合解除の手続きよりは若干手間が軽減されるかも知れません。行の移動もちょっとやりやすくなります。まあしかしその程度です。動かしたあとは文字色の調整をしないといけません。何よりもこの方法ではオートフィルタにより、あるカテゴリの先頭にある文字色付きカテゴリの行が隠されてしまうと、当該カテゴリに属する他の行の個別項目が一覧上でどのカテゴリに属しているのか視覚的にはわからなくなる問題があります（無色のカテゴリの行しか表示されない状態になるので）。

……却下ですね。

要件

そういうわけで、ここで実現したい要件は以下の通りです。

セル結合と結合解除の手続きを必要としないカテゴリ付き一覧シートをつくる。
便宜上カテゴリの階層はカテゴリとサブカテゴリと個別項目（これが1行に対応）の2層までとする。
カテゴリ、サブカテゴリ、個別項目の3層のそれぞれに自動でナンバリングを行う。
オートフィルタ機能で各行の表示・非表示を制御しても、カテゴリ所属が不明な個別項目を発生させない。

それからあらかじめ制約事項についても述べておきましょう。

行を追加したい場合、単純な挿入を行ってはならない。行の追加は既存の行のコピー＆挿入で増やす必要がある。
とくにカテゴリとサブカテゴリの部分については、他のExcelシートから書式付きでコピー＆ペーストしてはならない。

これは計算式や条件付き書式を使うExcelドキュメントに共通の制約なので、とくに業務の中でExcelを使用しているひとにはそれほど抵抗感のない制約であろうと思います。

イメージ

まずは実装後のイメージを示しておきます。先ほどのセル結合を用いたバージョンとほとんど変わらない見た目になっていると思います。

f:id:m12i:20130816192526p:plain

スクリーンショットからは、列のグルーピングをしているのがわかります。これは一覧シートの本文たるNO〜カテゴリ〜サブカテゴリ〜アイテム〜説明〜備考という一連の列の左側にいくつも計算式を入力したセルが挿入されているためです。単純な非表示化でもよかったのですが、実際の運用時に必要なものが隠されているということが示唆されるようにこのようにしました。これを展開すると以下のようになっています。

f:id:m12i:20130816193623p:plain

……うわー、これはひどい。コメントがうじゃうじゃしてますね。でもこうしないと、実装者自身にも、どこに何があるか分からなくなってしまうのだから仕方ありません。。

実装

ヘッダーとボディ（式と文字列）を入力

Excelシートの1行目のセルに、ヘッダーとなるテキストを入力しておきます。今回は「課題管理表」を念頭に置いているので、イメージのセクションで提示したような内容になっていますが、もちろん一覧表の目的に合わせて変更してください。
Excelシートの2行目以降のセルのA〜M列（非表示の列グループ）とN・P・Q列（NO系列グループ）に、下表に示す計算式を設定します。表に示した式はすべて2行目のセルに入力するものです。2行目より下の行にはコピー＆ペーストすることで相対位置指定が自動でB2→B3→B4…と変化していくはずです。なおA列は「NO」の区切り文字を指定する物で1行だけ、つまりA1セルに入力するだでOKです。
その他の列は、カテゴリ名、サブカテゴリ名、アイテム名等を手入力する列です。ここにはダミーで「あああああ」とか「AAAAA」とかの文言を詰めておけばOKでしょう。
ExcelシートのN列〜U列には末尾に「e」を入力しておきます。

列	式	説明
A列	.	「.」（ドット）だけです。この列は他とちがって、区切り文字を設定するための列で、入力するのもA2セルだけでOKです。
B列	1	「1」（半角数字）だけです。別の式で使用するダミー列。厳密に言うとこの列は必要ないのですが……。
C列	=SUBTOTAL(109, INDIRECT("$"&CHAR(64+ COLUMN($B$1))&"$"& (ROW($B$1)+1)&":$"& CHAR(64+COLUMN($B$1))& "$"&ROW()))	表示行数。オートフィルタで非表示にされている行をカウントしない場合の行数です。
D列	=ROW()-C2-1	累積非表示行数。表示行数と通常の行数とを比較して、当該行までの累積非表示行数を計算しています。
E列	=D2-SUM(INDIRECT("$"& CHAR(64+COLUMN($E$1))&"$"& (ROW($E$1))&":$"& CHAR(64+COLUMN($E$1))& "$"&(ROW()-1)))	非表示フラグ。累積非表示行数と当該行数を比較して、当該行が非表示にされているかどうかを判定しています。
F列	=IF($E2=0,O2,"")	E列の非表示フラグを考慮して、O列の入力内容を加工しています。
G列	=IF(AND(F2<>"", MATCH(F2,$F:$F,0)= ROW()),1,0)	カテゴリ初出フラグ（表示状態考慮あり）。F列の計算結果を考慮して、「当該カテゴリ名が表示行のなかで初出かどうか」を判定しています。
H列	=IF($E2=0,K2,"")	E列の非表示フラグを考慮して、K列の計算結果を加工しています。
I列	=IF(AND(H2<>"", MATCH(H2,$H:$H,0)= ROW()),1,0)	カサブテゴリ初出フラグ（表示状態考慮あり）。H列の計算結果を考慮して、「当該サブカテゴリ名が表示行のなかで初出かどうか」を判定しています。
J列	=IF(MATCH(O2,$O:$O,0) =ROW(),1,0)	カテゴリ初出フラグ（表示状態考慮なし）。すべての行（表示状態を問わず）のなかで、「当該カテゴリ名が表示行のなかで初出かどうか」を判定しています。
K列	=O2&"$"&Q2	別の計算式で使用するため、カテゴリ名とサブカテゴリ名を連結しています。
L列	=IF(MATCH(K2,$K:$K,0) =ROW(),1,0)	サブカテゴリ初出フラグ（表示状態考慮なし）。
M列	=N2&$A$2&L2	別の式で使用するため、カテゴリNOを加工しています。
N列	=""&COUNTIF(INDIRECT("$"& CHAR(64+COLUMN($J$1))& "$"&(ROW($J$1)+1)&":$"& CHAR(64+COLUMN($J$1))& "$"&ROW()),"=1")	カテゴリNO。表示状態にかかわらず当該カテゴリが何番目のものかを示す番号です。
P列	=N2&$A$2& COUNTIF(INDIRECT("$"& CHAR(64+COLUMN($M$1))& "$"&(ROW($J$1)+1)&":$"& CHAR(64+COLUMN($M$1))& "$"&ROW()),"="&N2&$A$2&"1")	サブカテゴリNO。表示状態にかかわらず当該サブカテゴリが何番目のものかを示す番号です。
R列	=P2&$A$2& COUNTIF(INDIRECT("$"& CHAR(64+COLUMN($P$1))& "$"&(ROW($J$1)+1)&":$"& CHAR(64+COLUMN($P$1))& "$"&ROW()),"="&P2)	アイテムNO。表示状態にかかわらず当該の個別項目が何番目のものかを示す番号です。

各セルに書式の設定

N列から右側の列のうちN・P・R列（NO系列グループ）以外のセルは、末尾の「e」行のセルを除き、四方を罫線で囲うようにします。
N・P・R列（NO系列グループ）のセルは末尾の「e」行のセルを含めて、上下罫線なし、文字色は白もしくはそれに近い色に設定します。つまりこの3列は「罫線がなく、文字が非表示」というのがデフォルトになるわけです。
N2・O2セルを同時選択して「=OR($G2=1,$G2="")」という式で条件付き書式を設定します。書式は文字色は上側のみ罫線あり、文字色は黒です。
続いて、P2・Q2セルを同時選択して「=OR($I2=1,$I2="")」という式で条件付き書式を設定します。書式は同上です。
N2〜Q2セルを同時選択して書式コピーを行って、そこから下方の「e」行のセルまで含めた範囲を対象にして書式ペーストをします。
「e」行のセルのうちN〜Q列のものに、左右罫線が付いてしまうのでこれを取り除きます。