Excel備忘録

はじめに

Excelで使う数式、小技などの備忘録です。
完全に備忘録なので、詳細な説明は気が向いたものだけ記載します。

1. インデントのある表の項番

こんな表の項番を自動で振る数式です。
インデントがわかるように罫線はすべて表示しています。

こんな表の項番をどう振っていくかが悩みどころです。
まず、単純にこんな感じにする人も多いのではないでしょうか？

しかし、せっかくインデントがあるのに、項番が単なる通番では少し恰好が悪いですね。
この手の表は大体行がすごくたくさんあるので、項番もバカほど増えるのが定番です。
項番とは、誰かに説明するときに使ったりするものですから、見やすいに越したことはありませんし、せっかく表計算ソフトを使っているのだから、フィルタ機能も使いたいですね。
そうなると、自然と項番の形式はこうなります。

こうすると、見やすいですし、説明の時にも大項目や中項目ごとの番号になるので、やりやすいですね。
項目ごとにフィルタすることもできます。
しかし、項番を振っていくのがすごく面倒くさいわけです。
と、いうことで、人力ではなく数式によって項番を自動入力できるようにしたいわけですが、たかが項番と言っても、中項目や小項目になると、上の行の番号を引き継がないといけないとか、いろいろ考えることがあって、結局数式も途中で断念してしまうわけです。
備忘録と言いながら長々と書いてしまいましたが、こんな感じの数式をメモしておきます。

1.1. 項番と項目列の対応

説明不要かと思いますが、項番と項目の列はこのように対応しています。

1.2. 1行目の数式

1行目は気にすることが少ないので、単純です。
単純に、対応する項目に入力があるあるかどうかを見て、入力があれば1を入力します。

■数式の考え方
自分に対応するレベル列が空のセルかどうか判定し、空でなければ1を入力します。
例ではROW関数を使用して1を入力します。

■数式例
IF(E3<>"",ROW()-2,0)

1.3. 2行目以降の数式

2行目以降は自分より右のレベルと、一つ上の行を気にする必要があるので、ちょっとめんどくさいです。
数式は、一番右の列とそれ以外で分けます。

1.3.1. 一番右の列以外の数式

■数式の考え方
自分に対応するレベル列が空のセルかどうか判定し、空でなければ一つ上の行の番号にカウントアップした値を入力します。
空のセルであれば一つ右のレベルの入力があるかを確認し、入力があれば一つ上の行の番号を引き継ぎます。

一つ右のレベルの入力があるかどうかは、自分の一つ右の項番が0であるかどうかを判定します。

■数式例
IF(E4<>"",A3+1,IF(B4<>0,A3,0))

1.3.2. 一番右の列の数式

■数式の考え方
一番右の列はもう自分より右のレベルはないので、単純に自分衣対応するレベルのセルが空のセルであるかどうかを判定し、空でなければ一つ上の行の値をカウントアップした値を入力します。

■数式例
IF(H4<>"",D3+1,0)

2. カテゴリごとの表

項番シリーズ第2弾。こんな表です。

見やすいように全部文字表示してますが、本来はこうなるはずです。

掲載はしてますが、ちょっと怪しいです。

2.1. 一番上の行の数式

一番上の行は、基本的にすべて1になるはずなので、数式が必要である必要もないので、割愛します。

2.2. 2行目以降の数式

2行目以降の数式は1番左の列の、その中で1番右の列以外の数式、そして1番右の列の数式にわかれます。

2.2.1 一番左の列の数式

■数式の考え方
対応する列に入力がある場合、一つ上の値と同じであれば値を引き継ぎ、違うようならカウントアップした値を入力します。

■数式例
IF(E3<>"",IF(E3<>E2,A2+1,A2),0)

2.2.2. 1番左でなく、かつ1番右でもない列

■数式の考え方
対応する列に入力がある場合、左の列の値が同じであるかどうかを確認し、それを1番左の列につくまで判定を繰り返します。

■数式例
IF(F3<>"",IF(F3=F2,B2,IF(E3=E2,B2+1,1)),0)

2.2.3. 1番右の列の数式

■数式の考え方
1番右の列も2.2.2と同じ数式で実現できますが、こちらはANDを使った方が楽です。
自分の列に入力がある場合、自分より左の列の入力がすべて同じ場合のみ、カウントアップした値を入力します。

■数式例
IF(AND(G3=G2,F3=F2,E3=E2),D2+1,1)

3. 罫線

階層ごとの罫線を引きたい場合のマクロです。
こちらは、完璧なサンプルを公開していくれているページがあるので、そちらをご覧ください。
使い方は簡単、値を入力済みの範囲を選択してマクロを実行するだけです。

筆者は知りませんでしたが、下記で公開されているような罫線を「カスケードタイプ」と呼ぶそうです。

■今日を乗り切るExcel研究所
http://www.shegolab.jp/entry/excel-macro-hierarchical-border-lines

4. 右クリックにツールメニュー追加

こちらはVBAで実装するマクロです。
いつも、ちょっとしたことをマクロで自動化するときは、忘れたころにやってくるのでいい加減こちに備忘を残しておきます。
記載のマクロは右クリックメニューに自分好みの図形を追加する処理を追加したものです。

' *****************************************************************************
' [ファイルオープン処理]
'
'  右クリックメニュー追加処理を呼び出す。
' *****************************************************************************
Sub Auto_Open()
    ' 右クリックメニュー追加処理呼び出し
    AddContextMenu
End Sub

' *****************************************************************************
' [ファイルクローズ処理]
'
'  右クリックメニュー削除処理を呼び出す。
' *****************************************************************************
Sub Auto_Close()
    DelMenu
End Sub

' *****************************************************************************
' [右クリックメニュー追加処理]
'
'  右クリックに吹き出し挿入を追加する。
' *****************************************************************************
Sub AddContextMenu()
    ' エラーハンドリング
    On Error GoTo ErrHandle

    ' まずはメニューを一旦リセット
    Application.CommandBars("Cell").Reset


    ' メニュー追加
    With CommandBars("Cell"). _
            Controls.Add(Before:=1, Type:=msoControlPopup)
        .Caption = "OptionMenu"

        With .Controls.Add
            .Caption = "吹き出し追加(黒)"
            .OnAction = "'AddCommentShape 0, 0, 0'"
        End With

        With .Controls.Add
            .Caption = "吹き出し追加(赤)"
            .OnAction = "'AddCommentShape 255, 0, 0'"
        End With
    End With

' エラーハンドリング
ErrHandle:
    ' 何もしない
End Sub
' *****************************************************************************
' [右クリックメニュー削除処理]
'
'  右クリックに追加したメニューを削除する。
' *****************************************************************************
Sub DelMenu()
    ' エラーハンドリング
    On Error GoTo ErrHandle


    ' 右クリック削除
    Application.CommandBars("Cell").Controls("OptionMenu").Delete

' エラーハンドリング
ErrHandle:
    ' 何もしない
End Sub

' *****************************************************************************
' [コメント吹き出し挿入処理]
'
'  吹き出しをいい感じの線と文字の大きさにして挿入する。
' *****************************************************************************
Sub AddCommentShape(R As Long, G As Long, B As Long)
    ' 図形追加
    ActiveSheet.Shapes.AddShape(msoShapeLineCallout1, ActiveCell.Left, ActiveCell.Top, 108, 40.5). _
        Select
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset1

    ' 図形書式設定
    With Selection.ShapeRange.Line
        .Visible = msoTrue
        .ForeColor.RGB = RGB(R, G, B)
        .Weight = 0.75
    End With
    Selection.ShapeRange.TextFrame2.TextRange.Font.Size = 9
End Sub