今日を乗り切るExcel研究所

Excel に働かされていませんか

Excel 一覧表の階層をツリーでパカパカしたい

今回は、Excel アウトライン機能を使って、階層的な一覧をツリー状に折りたためるようにする手順を調べます。また、項目の内容から自動でアウトライン化するマクロも紹介します。

Excel で管理してますか

個人的に、 Excel で作られた「〇〇管理表」や「××一覧」といった長いシートが非常に苦手です。

管理項目がズラッと並んだ長大なシートをスロールしながらセルを埋めていると、 次第に脳が空転しはじめて内容に集中できなくなり、ハッと気が付いたら夕方になっていたりしますが、それは筆者だけでしょうか。

理由はわからないのですが、一覧の内容だけの問題ではなく、単に大量の文言が一度に視野に入るだけで、集中力を削ぐ心理的効果があるように思います。

大抵の長い一覧表は項目見出しが階層的に整理されています。 せめて、見なくてもいい範囲は隠せるようにツリー表示でパカパカと折りたためれば、そこだけに集中して進められ、早く帰れるはずです。

アウトラインを使う

ネットで調べると、Excel に「アウトライン」という機能があり、これがツリーの代わりに使えるようです。

「アウトライン」は、小計などの集計行に関連した機能で、集計元の明細行を「グループ化」し折りたたむことができます。 グループ化は入れ子も可能で、集計行を階層的な概要(アウトライン)としてまとめることができます。

本来は集計概要のためのアウトラインですが、今回は一覧のツリー表示にグループの開閉機能を流用します。 集計行を明細行の下ではなく上の行に設定するとことで、一覧を見かけ上、ツリー表示のように階層化できるはずです。

f:id:shego:20180506172625p:plain

ここでは以下の方法でアウトライン作成を検討します。

  • 手作業で「グループ化」してみる
  • 小計から「アウトラインの自動生成」をする
  • 項目階層からアウトライン化するマクロ

手作業で「グループ化」してみる

各階層の「グループ化」を繰り返していくことでアウトラインを作成してみます。

【手順例】

  1. アウトラインの「詳細設定オプション」ダイアログを開きます
    • 「データ」タブ>「アウトライン」セクション>右下にある小さな四角をクリック
    • ショートカット:Alt + A -> L f:id:shego:20180502005324p:plain:w400
  2. 「詳細データの下」チェックボックスをオフにし、「OK」ボタンを押します
    • これでグループ行の上の行が集計行(見出し行)になります
  3. 折りたたみたいセル範囲(または行範囲)を選択します
  4. 「グループ化」を実行します
    • 「データ」タブ>「アウトライン」セクション>「グループ化」ボタンを押します
    • ショートカット: Alt+Shift+(右矢印)
  5. 「グループ化」ダイアログが出たら、「対象」の「行」オプションを選択して「OK」ボタンを押します
    • 行範囲指定(Shift+Space)なら出てきません
  6. 次の折りたたみ範囲を選択し、「グループ化」を繰り返します
    • 繰り返しショートカット: Alt+Enter

操作を間違えた場合は、取り消し(Ctrl + Z)します。

「グループ化」は難しい作業ではないですが、一度に1範囲しか実行できないのが難点です。 効率的な作業には、あらかじめ操作に慣れておく必要があります。

アウトラインに関連した操作ショートカットをまとめておきます。

操作 手順 コメント
グループ化 セルを範囲選択して
Alt+Shift+(右矢印)
グループの解除
(1階層のみ)
セルを範囲選択して
Alt+Shift+(左矢印)
選択範囲をグループ範囲に合わせないと、アウトラインが壊れます
アウトラインの部分削除 セルを範囲選択して「アウトラインのクリア」
AltAUC
セル範囲の全階層がごっそりと抜けて、アウトラインが分断されます
アウトラインの全体削除 任意の1セルを選択して「アウトラインのクリア」
AltAUC
アウトラインが全て削除されます
グループを開く グループが含まれる行で
AltAH
グループを閉じる グループに含まれる行で
AltAJ
アウトラインの表示・非表示切り替え Ctrl+8
特定の階層レベルを一括で開く 開きたい階層番号ラベルをクリック f:id:shego:20180504010116p:plain
特定の階層レベルを一括で閉じる 閉じたい階層番号ラベルをクリック

長い一覧表を手作業のグループ化でアウトラインにするのは時間のかかる面倒な作業になります。

小計から「アウトラインの自動生成」をする

アウトライン関連のコマンドをみると、「アウトラインの自動作成」という機能もあります。 一覧表の作りによっては、これで一括でアウトライン化できる可能性があります。

その「作り」とは

  • 一覧表に金額のような数量を入力する項目列があること
  • その項目は各階層で小計(集計)行を持つこと
  • その小計(集計)には集計関数(SUM, AVEARAGE, SUBTOTALなど)を使っていて、明細行のセル範囲が入力されていること

つまり各階層ごとに小計をとっているような表で、たとえばプロジェクト管理表なら予定工数などの項目列が使えるかもしれません。

「グループの自動生成」は、小計に使われる集計関数への入力セル範囲から階層構造を解析して、自動でグループ化します。 集計関数に指定されたセル範囲が、その上の行ではなく、下の行の明細を集計している時、ツリー状のアウトラインになるはずです。

【手順例】

  1. 小計を含む入力項目の列を範囲選択します
    • 金額や工数など
  2. 「グループの自動作成」を実行します
    • 「データ」タブ>「アウトライン」セクション>「グループ化」ボタン下部のプルダウン>「アウトラインの自動作成」。
    • キー操作: AltAGA
  3. 集計関数の入力範囲にしたがって一括でアウトライン化されます

期待通りの形にならなかったら、取り消し(Cntl+Z)するか「アウトラインのクリア」(AltAUC)で元に戻せます。

ちなみに、「アウトライン」セクションにはもうひとつ「小計」というボタンがあって気になります。 これは項目内容(項目名)から明細の集計範囲を自動判別して、上か下の行に小計行(SUBTOTAL)を挿入する機能です。 これを流用すれば、大項目・中項目・小項目のような分類列をもつフラットな表から、階層構造の一覧表を自動作成できるような気がしますが、それは別記事の話題ということにします。

 



項目階層からアウトライン化するマクロ

アウトラインを自動生成するマクロを作成しました。

本マクロは、範囲選択された項目列のセルの内容を元に、一定の規則に従って行をグループ化し、ツリー状のアウトラインを作成します。

一覧表の階層表現の規則は、組織や現場よって様々だと思いますが、ありそうなパターンをいくつか用意してみました。 必要ならカスタマイズしてください。

  • インデント階層
    項目列のセルのインデント(字下げ)の深さによって項目階層を判断します。 インデントは Excel の機能によるもので、スペース(空白文字)ではありません。
    f:id:shego:20180504010029p:plain

  • 列下げ階層
    列下げで項目階層を判断します。 階層項目を含むセル範囲を選択して、「列下げ階層」を実行します。
    f:id:shego:20180504010045p:plain

  • 項番階層
    項番の階層に従います。番号の区切り文字で階層の深さを判断します。 項番のカラムを選択後、項番階層を実行します。項番の階層区切りとしてよく見るハイフン「-」を想定していますが、「.」など他の文字にも変更できます。

f:id:shego:20180504010100p:plain

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)
    
    Application.ScreenUpdating = True
End Sub

Private Function traverseList(curRng As Range, curLevel As Integer, probe As String, Optional doProc As String = "doGroup") 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

Private Function doInsertParent(ByVal rng As Range, level As Integer) As Range
    rng.Rows(1).EntireRow.Insert
    Set doInsert = Range(rng, rng.Offset(-1))
End Function

【使い方】

  1. 上記 VBA プログラムを標準モジュールにコピー&ペーストします
  2. ツリーとしてアウトライン化したい項目範囲を選択します
  3. 本マクロを実行します
  4. ツリー状にアウトラインが作成されます

注意点として

  • 階層の深さは最大8階層までです。
  • 項目が空の行は常にグループ化されます。
  • セル結合された項目には対応していません。

まとめ

一覧表の階層をアウトライン機能で擬似的にツリー表示化することができました。

しかし、本来 Excel;は「表計算ソフト」ですので、プロジェクト管理などに使うとういう使い方のほうにそもそも無理があるように思います。

業務の全てを Excel に頼るのではなく、プロジェクト管理ソフトなどの専用ツールの活用も検討したいところです。

本記事の内容は Windows 10 / Excel 2013 で動作検証しました。

参考

関連記事

www.shegolab.jp