今回は、Excel アウトライン機能を使って、階層的な一覧をツリー状に折りたためるようにする手順を調べます。また、項目の内容から自動でアウトライン化するマクロも紹介します。
Excel で管理してますか
個人的に、 Excel で作られた「〇〇管理表」や「××一覧」といった長いシートが非常に苦手です。
管理項目がズラッと並んだ長大なシートをあちこちスロールしながらセルを埋めていると、 次第に脳が空転しはじめて内容に集中できなくなり、ハッと気が付いたら夕方になっていたりするのですが、それは筆者だけでしょうか。
理由はわからないのですが、仕事の内容だけの問題ではなく、単に大量の文言が一度に視野に入るだけでも、集中力が削がれる心理的効果があるように思います。
大抵の長い一覧表は項目見出しが階層的に整理されています。 せめて見なくてもいい範囲は隠せるように、ツリー表示でパカパカと折りたたむことができれば、そこだけに集中して作業を進められ、早く帰れるよういになるはずです。
アウトラインを使う
ネットで調べると、Excel に「アウトライン」という機能があり、これがツリーの代わりに使えるようです。
「アウトライン」は、小計などの集計行に関連した機能で、集計元の明細行を「グループ化」し、グループごとに折りたたむことができます。 グループ化は入れ子も可能で、集計行を階層的な概要（アウトライン）としてまとめることができます。
本来は概要集計のためのアウトラインですが、今回、一覧のツリー表示にグループの開閉機能を流用します。 集計行を明細行の下ではなく上の行に設定するとことで、一覧を見かけ上、ツリー表示のように階層化できるはずです。
ここでは以下の方法でアウトライン作成を検討します。
- 手作業で「グループ化」してみる
- 小計から「アウトラインの自動生成」をする
- 項目階層からアウトライン化するマクロ
手作業で「グループ化」してみる
各階層の「グループ化」を繰り返していくことでアウトラインを作成してみます。
枝の見出し行となる集計行は、デフォルトで明細の下に付くので、これを明細の上付きに変更しておく必要があります。
【手順例】
- アウトラインの「詳細設定オプション」ダイアログを開きます
- 「データ」タブ＞「アウトライン」セクション＞右下にある小さな四角をクリック
- ショートカット：
Alt+A->L
- 「詳細データの下」チェックボックスをオフにし、「OK」ボタンを押します
- これでグループ行の上の行が集計行（見出し行）になります
- 折りたたみたいセル範囲（または行範囲）を選択します
- 「グループ化」を実行します
- 「データ」タブ＞「アウトライン」セクション＞「グループ化」ボタンを押します
- ショートカット：
Alt+Shift+→（右矢印）
- 「グループ化」ダイアログが出たら、「対象」の「行」オプションを選択して「OK」ボタンを押します
- 行範囲指定（
Shift+Space）なら出てきません
- 行範囲指定（
- 次の折りたたみ範囲を選択し、「グループ化」を繰り返します
- 繰り返しショートカット：
Alt+Enter
- 繰り返しショートカット：
操作を間違えた場合は、取り消し（Ctrl + Z）します。
「グループ化」は難しい作業ではないですが、一度に１範囲しか実行できないのが難点です。 効率的な作業には、あらかじめ操作に慣れておく必要があります。
アウトラインに関連した操作ショートカットをまとめておきます。
| 操作 | 手順 | コメント |
|---|---|---|
| グループ化 | セルを範囲選択して Alt+Shift+→（右矢印） |
|
| グループの解除 （１階層のみ） |
セルを範囲選択して Alt+Shift+←（左矢印） |
選択範囲をグループ範囲に合わせないと、アウトラインが壊れます |
| アウトラインの部分削除 | セルを範囲選択して「アウトラインのクリア」Alt→A→U→C |
セル範囲の全階層がごっそりと抜けて、アウトラインが分断されます |
| アウトラインの全体削除 | 任意の１セルを選択して「アウトラインのクリア」Alt→A→U→C |
アウトラインが全て削除されます |
| グループを開く | グループが含まれる行で Alt→A→H |
|
| グループを閉じる | グループに含まれる行で Alt→A→J |
|
| アウトラインの表示・非表示切り替え | Ctrl+8 |
|
| 特定の階層レベルを一括で開く | 開きたい階層番号ラベルをクリック |  |
| 特定の階層レベルを一括で閉じる | 閉じたい階層番号ラベルをクリック |
長い一覧表を手作業で一つ一つグループ化し、アウトラインツリーを作成するのは時間のかかる面倒な作業になります。
小計から「アウトラインの自動生成」をする
アウトライン関連のコマンドをみると、「アウトラインの自動作成」という機能もあります。 一覧表の作りによっては、これで一括でアウトライン化できる可能性があります。
その「作り」とは
- 一覧表に金額のような数量を入力する項目列があること
- その項目は各階層で小計（集計）行を持つこと
- その小計（集計）には集計関数（SUM, AVEARAGE, SUBTOTALなど）を使っていて、明細行のセル範囲が入力されていること
つまり各階層ごとに小計をとっているような表で、たとえばプロジェクト管理表なら予定工数などの項目列が使えるかもしれません。
「グループの自動生成」は、小計に使われる集計関数への入力セル範囲から階層構造を解析して、自動でグループ化します。 集計関数に指定されたセル範囲が、その上の行（デフォルト）ではなく、下の行の明細を集計している時、ツリー状のアウトラインになるはずです。
【手順例】
- 小計を含む入力項目の列を範囲選択します
- 金額や工数など
- 「グループの自動作成」を実行します
- 「データ」タブ＞「アウトライン」セクション＞「グループ化」ボタン下部のプルダウン＞「アウトラインの自動作成」。
- キー操作：
Alt→A→G→A
- 集計関数の入力範囲にしたがって一括でアウトライン化されます
期待通りの形にならなかったら、取り消し（Cntl+Z）するか「アウトラインのクリア」（Alt→A→U→C）で元に戻せます。
ちなみに、「アウトライン」セクションにはもうひとつ「小計」というボタンがあって気になります。
これは項目内容（項目名）から明細の集計範囲を自動判別して、上か下の行に小計行（SUBTOTAL）を挿入する機能のようです。 これを流用すれば、大項目・中項目・小項目のような分類列をもつフラットな表から、階層構造の一覧表を自動作成できるような気がしますが、今回の話題とは前提データの形式が異なるので見送ります。いつか別記事で扱うかもしれません。
項目階層からアウトライン化するマクロ
アウトラインを自動生成するマクロを作成しました。
本マクロは、範囲選択された項目列のセルの内容を元に、一定の規則に従って行をグループ化し、ツリー状のアウトラインを作成します。
一覧表の階層表現の規則は、組織や現場よって様々だと思いますが、ありそうなパターンをいくつか用意してみました。 必要ならカスタマイズしてください。
インデント階層
項目列のセルのインデント（字下げ）の深さによって項目階層を判断します。 インデントは Excel の機能によるもので、スペース（空白文字）ではありません。
列下げ階層
列下げで項目階層を判断します。 階層項目を含むセル範囲を選択して、「列下げ階層」を実行します。
項番階層
項番の階層に従います。番号の区切り文字で階層の深さを判断します。 項番のカラムを選択後、項番階層を実行します。項番の階層区切りとしてよく見るハイフン「-」を想定していますが、「.」など他の文字にも変更できます。
Option Explicit ' 項番階層の区切り文字 Const NUMBER_SEPARATOR = "-" ' Const NUMBER_SEPARATOR = "." Sub アウトライン_インデント階層() outlineTree probe:="indentLevel" End Sub Sub アウトライン_列下げ階層() outlineTree probe:="columnPosition" End Sub Sub アウトライン_項番階層() outlineTree probe:="multiNumbered" End Sub Private Sub outlineTree(probe As String) If TypeName(Selection) <> "Range" Then Beep: Exit Sub Dim titleRng As Range Set titleRng = Intersect(ActiveSheet.UsedRange, Selection.Areas(1)) If titleRng Is Nothing Then Beep: Exit Sub Application.ScreenUpdating = False ActiveSheet.Outline.SummaryRow = xlAbove titleRng.ClearOutline Call traverseList(titleRng, 0, probe:=probe, doProc:="doGroup") Application.ScreenUpdating = True End Sub Private Function traverseList(curRng As Range, curLevel As Integer, probe As String, doProc As String) As Range Dim i As Integer For i = 1 To curRng.Rows.Count - 1 Dim subRng As Range Dim nextLevel As Integer Set subRng = Intersect(curRng, curRng.Offset(i)) nextLevel = Application.Run(probe, subRng.Rows(1), curLevel) If nextLevel > curLevel Then Set subRng = traverseList(subRng, nextLevel, probe, doProc) Set subRng = Application.Run(doProc, subRng, nextLevel) i = i - 1 + subRng.Rows.Count ElseIf nextLevel < curLevel Then Exit For End If Next Set traverseList = curRng.Resize(i) End Function Private Function indentLevel(itemRow As Range, level As Integer) As Integer With itemRow.Cells(1) indentLevel = IIf(IsEmpty(.Value), 8, .indentLevel) End With End Function Private Function columnPosition(itemRow As Range, level As Integer) As Integer columnPosition = 0 Dim c As Range For Each c In itemRow.Cells If Not IsEmpty(c) Then Exit Function columnPosition = columnPosition + 1 Next End Function Private Function multiNumbered(itemRow As Range, level As Integer) As Integer With itemRow.Cells(1) multiNumbered = IIf(IsEmpty(.Value), 8, UBound(Split(.Text, NUMBER_SEPARATOR))) End With End Function Private Function doGroup(ByVal rng As Range, level As Integer) As Range rng.Rows.Group Set doGroup = rng End Function
【使い方】
- 上記 VBA プログラムを標準モジュールにコピー＆ペーストします
- ツリーとしてアウトライン化したい項目範囲を選択します
- 本マクロを実行します
- ツリー状にアウトラインが作成されます
アウトラインを解除するには、Excel 機能の「アウトラインのクリア」を使ってください。
項目行の追加削除などでアウトラインが壊れたときには、全体を再度マクロ処理すればOKです。
【制限事項】
- 階層の深さは最大８階層までです。
- 項目が空の行は常にグループ化されます。
- セル結合された項目には対応していません。
まとめ
一覧表の階層をアウトライン機能で擬似的にツリー表示化することができました。
しかし、本来 Excel;は「表計算ソフト」ですので、プロジェクト管理などに使うとういう使い方のほうにそもそも無理があるように思います。
業務の全てを Excel に頼るのではなく、プロジェクト管理ソフトなどの専用ツールの活用も検討したいところです。
本記事の内容は Windows 10 / Excel 2013 で動作検証しました。
