ExcelVBAで逆L字型の表（カテゴライズド・ビュー）

Excelの自作マクロをシステム開発のドキュメント風に紹介するコーナーです。

# ○要件定義
## １．システム化の目的
　ユーザー（注１）はExcel における資料作成時、必要に応じて項目ごとに逆L字型に分類された表（以下、カテゴライズド・ビュー（注２））を作成している。
　カテゴライズド・ビューは行や列追加時に罫線や着色の乱れなどが発生しやすくカテゴライズド・ビューの加工に時間がかかり、作業効率が低下している。
　本件ではカテゴライズド・ビューを自動的に整形する機能を開発することでカテゴライズド・ビュー作成・編集時の手作業を効率化することを目的とする。

　注１）私のこと
　注２）本案件内での呼称であり学術的な正式名称ではない


## ２．業務要件

### ２．１．カテゴライズド・ビューの定義

　カテゴライズド・ビューの例を下図に示す。
　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/e5a03995-aa9f-0702-414c-00157e61e2df.png)

 【前提事項】
- カテゴリの数は表によって変動する
- カテゴリの深さは表によって変動する
- カテゴリの深さはカテゴリによっても変動する
- カテゴリの値は最下層以外にも表れる
- セルの結合は行っていない

### ２．２．業務フロー

　現在とシステム化後の業務フローをそれぞれ下図に示す。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/6eba0b65-de8f-b2ac-8a4a-d9c5cbc50edf.png)


　**＜現行フロー＞**
　ユーザーは表の内容を考え、入力しながら、同時にカテゴライズド・ビュー形式の加工を行っている。
　このため、途中で幅が不足した場合や階層に変化が生じた場合、それまで整形していた表の修正をする必要があり、非効率となっている。
　また、一度完成した表に階層が増えるなどの修正が発生した場合、列や行を手で追加するが、このとき罫線の乱れなどが生じる場合があるため、その修正も手で行う必要が生じている。

　**＜システム化後フロー＞**
　「カテゴライズド・ビュー化」機能を用いて自動的に加工が行えるため、ユーザーは値の配置決めと入力にのみ集中できる。
　値の配置は完成後の表を意識して決めておく必要はあるが、カテゴライズド・ビューへの加工前であるため修正は容易であり、非効率な作業が解消される。
　また、表の修正においても、修正後の表を指定して「カテゴライズド・ビュー化」機能を呼び出すことでカテゴライズド・ビューに加工されるため、罫線の乱れの修正が不要となる。

## ３．機能要件
### ３．１．カテゴライズド・ビューの定義に対する機能要件
　「カテゴライズド・ビュー化」機能のI/O関連図を以下に示す。
　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/e041d8a7-af94-f33d-56f2-f231e227ac4a.png)
　＜処理概要＞
　　選択されたセル範囲に対して、値の探索を行い、値のあるセルを起点とした逆L字型描画（罫線）を行う。
　　この繰り返しにより、カテゴリの数・カテゴリの深さについて柔軟に対応可能とする。
　　※逆L字型描画の方法は詳細設計にて確定する。
　![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/38947874-115e-2f99-75ba-7fc3da94e0ba.png)



### ３．２．業務フローに対する機能要件

　ユーザーがどのような Excel に対してもカテゴライズド・ビュー化が行いやすいよう、個人用マクロブックへマクロとして追加する。
　作成したマクロはクイックアクセスツールバーに登録する。
　再実行時に描かれている罫線を意識しなくてもよいように、マクロ実行の先頭において、表の罫線の初期化を行う。

　
## ４．非機能要件（ある場合のみ記載）

### ４．１．障害復旧要件
　本件では Excelブック・ワークシート、およびカテゴライズド・ビューへ加工する前の表のバックアップは行わない。
　実行に際し懸念がある場合はユーザーにより事前に保存等の退避を行った後、マクロを呼び出して頂く。

---

# ○詳細設計前の実装方法の検討

逆L字型の描画とは、逆L字そのものの描画ではなく、カテゴリの起点(x,y)から終点(x2,y2)への四角形の集合によって表現されると考えられる（下図参照。座標は起点Aの場合を示す）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/40d30c55-12c2-2910-6e34-b567f54c677c.png)

このとき以下の条件が成立する。

- (ア) x2 は常に表全体の終端座標(xe,ye)の xe である。
- (イ) y2 は行方向に探索して値があるセルの直前のセルまたは終端座標 ye である。
    - すなわち、カテゴリAの y2 は J-1 である。
    - すなわち、カテゴリCの y2 は F-1 である。
    - すなわち、カテゴリFの y2 は I-1 である。
    - すなわち、カテゴリJの y2 は終端座標 ye である。
- (ウ) y2 は上位カテゴリの y2 が決定している場合、上位カテゴリの y2-1 である。
    - すなわち、カテゴリBの y2 は上位カテゴリJ-1である（終端 ye ではない）。
    - すなわち、カテゴリHの y2 は I-1 である（終端 ye ではない）。
    - すなわち、カテゴリMの y2 は F-1 である（ N-1 ではない）。

＜探索順序について＞
　通常このようなロジックは左上から右下に向かって処理することが自然である。
　人が四角形の集合をもって逆L字型を描画しようとする場合、カテゴリBの終点をQではなくPと判断できるのは、Jの存在を先読みしているためである。J より上に位置するカテゴリからは、いかなる場合も J を越えて罫線が引かれることはない。このため J の行を決定行と呼ぶことにする。
　この先読みをプログラムに実装するのは複雑である。起点となるカテゴリより左下側にあるすべてのセルを毎回探しなおす必要がある（下図左）。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/c6c684b2-fda0-efc3-b537-d8cc036562bc.png)

　この複雑さを回避するため、本プログラムでは、右下から左上に向かって探索する方法を採用する（上図右）。
　右下から処理することによって、カテゴリJの探索はカテゴリBより先に行われる。その際、決定行を記憶する。これにより、カテゴリBの処理時には、カテゴリB の終点は 決定行 J-1であること（(ウ)1段目）を適用することができる。
　同様に、カテゴリIの処理はカテゴリHより先に行われるため、カテゴリIを処理したとき、決定行は I に更新されている。従って、カテゴリHの終点 y2は I-1 であること（(ウ)2段目）を適用できる。
　ところで、カテゴリB とカテゴリI では、カテゴリ I のほうが先に処理されてしまう。カテゴリIを処理したときに決定行が I に代わってしまうと、Bを処理するとき、y2 は J-1 ではなくなってしまう。上図のBを下に見ていけばJが決定行であり、Hを下に見ていけばIが決定行であることがわかる。このことから、**決定行は列ごとに異なる値である必要がある**ことがわかる。

　以上の検討を踏まえて詳細設計を行う。


# ○詳細設計

## １．処理フロー

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/55e5ddc5-5f52-c457-8e2e-5686911c86a6.png)
　※実装上のセル参照はセル(行, 列)の形であるが、フロー上のセル(x,y)はセル(列,行)を表している。

## ２．処理内容

１．セル範囲の罫線をクリアする
２．変数x0にセル範囲の開始列を代入する
３．変数y0にセル範囲の開始行を代入する
４．変数xeにx0＋セル範囲の列数-1を代入する
５．変数yeにy0＋セル範囲の行数-1を代入する
６．変数x に xe を代入する
７．変数y に ye を代入する
８．y が y0 未満となるまで以下の処理を繰り返し行う
　（１）x が x0 未満となるまで以下の処理を繰り返し行う
　　　ア. セル(x,y)に値がある場合
　　　　（ア）x2 に xe を代入する
　　　　（イ）以下の処理を繰り返し行う
　　　　　　i．セル(x, y2+1)が値あり、またはy2が ye、またはy2が決定行(x)の場合ループから抜ける
　　　　　　ii. y2を+1する
　　　イ．セル(x,y)からセル(x2,y2)まで外枠罫線を引く
　　　ウ．i が x から終端列まで以下の処理を繰り返し行う
　　　　（ア）決定行(i)に x を代入する
　（２） x に x-1を代入する
９．x に xe を代入する。
１０．y に y-1 を代入する。


# ○実装
```visualbasic
Sub S_カテゴライズドビュー()

    Dim lRowBgn         As Long
    Dim lColBgn         As Long
    Dim lRowEnd         As Long
    Dim lColEnd         As Long
    Dim lRow            As Long
    Dim lCol            As Long
    Dim lRowTmp         As Long
    Dim lColTmp         As Long
    Dim lDecisionRows() As Long
    Dim lCnt            As Long

    '罫線クリア
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone


    lRowBgn = Selection.Row
    lColBgn = Selection.Column
    lRowEnd = lRowBgn + Selection.Rows.Count - 1
    lColEnd = lColBgn + Selection.Columns.Count - 1
    ReDim lDecisionRows(lColBgn To lColEnd)
    
    lRow = lRowEnd
    lCol = lColEnd
    Do Until lRow < lRowBgn
        
        Do Until lCol < lColBgn
        
            '右下から左上に向かって値のあるセルを探索
            If Cells(lRow, lCol).Value <> "" Then

                lColTmp = lColEnd
            
                '下方向に探索
                '   終端もしくは値もしくはその列における決定行にぶつかる場合は探索を終了
                lRowTmp = lRow
                Do
                    If Cells(lRowTmp + 1, lCol).Value <> "" Or lRowTmp = lRowEnd Or (lRowTmp + 1) = lDecisionRows(lCol) Then
                        Exit Do
                    End If
                    lRowTmp = lRowTmp + 1
                Loop
                
                '外枠に罫線描画
                Range( _
                      Cells(lRow, lCol) _
                    , Cells(lRowTmp, lColTmp) _
                ).Select
                Selection.Borders(xlEdgeLeft).LineStyle = xlContinuous
                Selection.Borders(xlEdgeTop).LineStyle = xlContinuous
                Selection.Borders(xlEdgeBottom).LineStyle = xlContinuous
                Selection.Borders(xlEdgeRight).LineStyle = xlContinuous
                
                'その列以降の決定行を更新
                For lCnt = lCol To lColEnd
                    lDecisionRows(lCnt) = lRow
                Next lCnt

            End If
        
            lCol = lCol - 1
        
        Loop
    
        lCol = lColEnd
        lRow = lRow - 1
    Loop

End Sub
```

# ○稼働確認

リボンにマクロを登録し、サンプルの表に対して実行する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/d8a7caec-08dd-44db-59b8-75c3e613938a.png)

１．範囲選択
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/6627924e-a954-8385-3077-f1f6a73de5d2.png)

２．S_カテゴライズド・ビューを実行
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/33b430bc-df28-c69b-612c-71c8ad37982d.png)

３．キャレットを移動する
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/425c2e20-3da0-bc92-1fac-9f79bab53ab0.png)

４．表の配置を変えて逆L字型ではない表に実行
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/3288f36f-96ef-d522-5426-8eda903dccb1.png)
　　↓
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/601827/8bea3718-c953-8157-5c27-d8b47323ebf3.png)




# ○評価

　これまで整形に手間取るので作りたくても敬遠しがちだったカテゴライズド・ビューが簡単に作成できるようになり、満足している。今のところ実業務でも二次加工することなく使えている。意図していなかったことだが普通の表にも使えるのも良かった。　実業務では逆L字型の部分に色がついたものを見かけることが多い。自動で色がつき、さらに階層ごとに淡くなっていくような機能があると良いと思った。


# ○感想
　こんな需要がなさそうな文書を最後まで見て頂きありがとうございました。
　最後だけ見ている方もありがとうございます。
　初めてQiitaに投稿してみましたが、長くなりすぎた気がします。
　仕事ではよくこういう様式の要件定義書を作っていましたが、自分で使うツールをこのようにドキュメント化して作ることはなかったので「仕事風に」やってみました。案外、書いてみると深く考えてなかったけどこうしたかったんだな、ということが整理できて良いものだなと思いました。
　「逆L字型は四角形の集合体である」というアルゴリズムは試行錯誤の末辿りついたので気に入っています。よく見る形の表なので、ExcelVBAerはマクロ化を試みたこともあるのではないでしょうか？　色んなやり方がありそうなので共有して頂けたら嬉しいです。
　コーディングは自分で使うだけなので、動けばいいレベルに留めており完成度は微妙です。lDecisionRows の定義を lColBgn To lColEnd にしているのを極めて不快に思われた方もおられると思いますが、配列にアクセスするのに lColEnd-i とかしなくていいので個人利用レベルでは気に入っています。ご容赦ください。　最初にセル範囲を二次元配列に入れた方が速いとか、下方から探索しているにも関わらず、起点から下方向に探しなおすのは無駄なことをしている気がするので、どうにかしたかったところですね。
　もし興味がわきましたら、実際にお使いになり研究して頂けると幸いです。
　機会がありましたら、同じ様式でほかのマクロも紹介したいと思います。

以上です。