見出し画像
Photo by tomekantyou1

JTCやコンサルで代々引き継がれてきたExcelのブックにありがちなこと

ななし

JTCやコンサルで代々引き継がれてきたブック等にありがちなことを書いてみます。

Excelだけではなく、 WordファイルやPowerPointにも共通する内容も一部あります。
（具体的には、目次の「3.トリミング部分を削除していない」と「6.ブックのプロパティの会社名やタイトルが古いor実際と異なる」がWordやPowerPointでも共通する内容です。）


1.見えない大量の図形が大量に隠れている

中身は大したこのがないのに、なぜか容量が重いブック。
調べてみると、高さ・幅が０で視認できない図形がブック内に大量に隠れている場合があります。

一見何もないように見えますが、右の［選択］ウィンドウを見てわかる通り、オブジェクトが隠れています。

見えない画像が隠れている

図形を選択してみるとわかりやすいです。

すべての図形を選択すると、視認できない図形がどこにあるか分かる

原因は、Excel2003などの古いバージョンのExcelでは、行や列を削除したとき、そのオブジェクトのプロパティで「セルに合わせて移動やサイズを変更する」になっている図形が含まれていると、行や列を削除すると、高さや幅がゼロの図形が残ることがあったためです。
2007以降のバージョンでは行や列に完全に含まれるオブジェクトの場合は、高さや幅がゼロのオブジェクトは生じず、オブジェクトそのものが削除される仕様に変わりました。
しかし、過去に発生してしまった高さ・幅がゼロの図形は、基本的にそのままに残ってしまうため、このような事象が生じすることがあります。

そういうときには、図形を削除して軽量化します。

Sub sb高さと幅の両方が0の図形削除()
'Excelで高さと図形が０で視認性がない図形を削除するマクロ
'例：例えば→などの図形は、高さ幅0にしても▲の部分だけ見えたりするが、高さ・幅０なのでマウスで選択ができない
 
    Dim shapeObj  As Shape '図形用変数
    Dim i         As Long  'アクティブシートの図形カウント用変数
    Dim ws       As Worksheet
    Set ws = ActiveSheet 'ActiveSheetをwsに設定
    Dim has見えない図形      As Boolean

    Const NO_見えない図形_MSG As String = "見えない図形はありませんでした。"
    Const HAS_見えない図形_MSG As String = "見えない図形を削除しました。"
   
    For i = ws.Shapes.count To 1 Step -1  'アクティブシートの図形をカウントダウン
   
         Set shapeObj = ws.Shapes(i)
             With shapeObj
                 If .Height = 0 And .Width = 0 Then     '図形の高さ0、幅0
                    has見えない図形 = True
                     .Delete
                 End If
             End With
    Next i

' 結果メッセージ表示
    Dim myMsg As String
    myMsg = IIf(has見えない図形, HAS_見えない図形_MSG, NO_見えない図形_MSG)
    MsgBox myMsg, , "処理結果通知"
End Sub

いきなり削除してもよいか不安な場合には、高さ幅を大きくして視認できるサイズにしてから、本当に削除してよいか判断するとよいでしょう。

2.何も入力していないテキストボックスが大量に隠れている

なぜそんなことするの？と思うのですが、外枠なし、塗りつぶしもない、テキストも入力していない「テキストボックス」をブックに配置する人がいます。

枠線が透明なテキストボックスがどこかにある

一見何もないように見えますが、右の［選択］ウィンドウを見てわかる通り、オブジェクトが隠れています。
選択してみると、より分かりやすいですね。

枠線が透明なテキストボックスが隠れている

一つならまだマシですが、大量にあると邪魔なだけですし、そのテキストボックスに入力したいことがあるのであれば、改めてテキストボックスを新規に挿入してもらえばよいだけなので削除をおすすめします。
枠線と背景が空白なテキストボックスに対して、役に立つプロシージャを3つ紹介します。もしよろしければご参考にされてください。

【透明空白のテキストボックスをカウントするコード】

Sub sb透明空白テキストボックスカウント()
'Excelで背景と枠線が透明なテキストボックスでテキスト文がない（＝視認困難）図形をカウントするマクロ
   
    Dim shapeObj As Shape    '図形用変数
    Dim i        As Long     'アクティブシートの図形全体をカウントする変数
    Dim j        As Long     '透明空白のテキストボックスをカウントする変数
    Dim ws       As Worksheet
    Set ws = ActiveSheet 'ActiveSheetをwsに設定
    
    j = 0 '初期化
    For i = 1 To ws.Shapes.count   'アクティブシートの図形をカウントアップ
   
        Set shapeObj = ws.Shapes(i)
        With shapeObj
            If .Type = msoTextBox _
                And .Fill.Visible = msoFalse _
                And .line.Visible = msoFalse Then    '図形が枠線も背景も透明のテキストボックスなら
                    If .TextFrame.Characters.text = "" Then
                        j = j + 1
                    End If
            End If
        End With

    Next i
   
    Dim myMsg As String 'メッセージボックス用変数
    myMsg = "処理が終了しました。"
    If j = 0 Then
        myMsg = myMsg & vbCrLf & "透明空白のテキストボックスはありませんでした。"
    Else
        myMsg = myMsg & vbCrLf & "透明空白のテキストボックスは" & j & "個ありました。"
    End If
    MsgBox myMsg, , "処理結果通知"
   
End Sub

【透明空白のテキストボックスの外枠線を黒にして視認可能にするコード】

Sub sb透明空白のテキストボックスの外枠線を黒へ()
    Dim shapeObj As Shape    '図形用変数
    Dim i        As Long     'アクティブシートの図形カウント用変数
    Dim ws       As Worksheet
    Set ws = ActiveSheet 'ActiveSheetをwsに設定
  
    For i = ws.Shapes.count To 1 Step -1  'アクティブシートの図形をカウントダウン
  
        Set shapeObj = ws.Shapes(i)
        With shapeObj
            If .Type = msoTextBox _
                And .Fill.Visible = msoFalse _
                And .line.Visible = msoFalse Then    '図形が枠線も背景も透明のテキストボックスなら
                    If .TextFrame.Characters.text = "" Then
                        .line.Visible = msoTrue '外枠線をつける
                        .line.ForeColor.RGB = RGB(0, 0, 0) '線の色を黒へ
                        .line.Weight = 2  '太さ
                    End If
            End If
        End With
 
    Next i
  
    MsgBox "処理が終了しました。", , "処理結果通知"
  
End Sub

【透明空白のテキストボックスを削除するコード】

Sub sb透明空白のテキストボックスの削除()
'Excelで背景と枠線が透明なテキストボックスでテキスト文がない（＝視認困難）図形を削除するマクロ
   
    Dim shapeObj As Shape    '図形用変数
    Dim i        As Long     'アクティブシートの図形カウント用変数
    Dim ws       As Worksheet
    Set ws = ActiveSheet 'ActiveSheetをwsに設定
   
    For i = ws.Shapes.count To 1 Step -1  'アクティブシートの図形をカウントダウン
   
        Set shapeObj = ws.Shapes(i)
        With shapeObj
            If .Type = msoTextBox _
                And .Fill.Visible = msoFalse _
                And .line.Visible = msoFalse Then    '図形が枠線も背景も透明のテキストボックスなら
                    If .TextFrame.Characters.text = "" Then
                        .Delete
                    End If
            End If
        End With

    Next i
   
    MsgBox "処理が終了しました。", , "処理結果通知"
   
End Sub

3.トリミング部分を削除していない

マニュアル作成等でスクリーンショットの一部をトリミングすることも多いと思います。
しかし、単にトリミングした状態では、トリミング部分は隠れている状態なので、元に戻せます。
個人情報等の他部署・他社に見せたくなくてトリミングしている場合は、きちんとトリミング部分を削除しないと、かなりまずいです。
また、トリミング部分を削除していない場合は、容量も残ったままになります。

トリミングした図形

トリミング部分した画像を選択して、「トリミング」をクリックすれば分かりますが、トリミング部分は削除されていません。

トリミング部分はデータとして残っているため復元可能

トリミング部分を削除するには、画像１枚１枚削除する方法もありますが、ブック内に複数画像があるときは１つ１つ作業するは面倒なので、まとめてトリミング部分を削除しましょう。
「名前を付けて保存」＞「ツール(L)」＞「図の圧縮(Ｃ)」で下記の画面にいけるので、任意の解像度を選択して、ブックを上書き保存すればトリミング部分が完全削除できます。

「名前を付けて保存」＞「ツール(L)」＞「図の圧縮(Ｃ)」メニューから

今度はトリミングをクリックしても、トリミング部分は削除されていることが分かります。

今度はトリミングを押してもトリミング部分は残っていない

※なお、トリミング部分は削除しないと戻せるのは、Excelだけでなく、PowerPointやWordでも同様です。

4.大量の名前定義が隠れている

機能として知らない方も多い名前定義。
個人的にはJTCやコンサルの一部で利用する方がいる印象。その一部の名前定義使いの人から他の方に引き継がれるにしたがって、名前定義が大量増殖や#REF!エラーになっていきます。
#REF!エラーになっても、自社のフォルダ構成やSharePointの構成が分かるようなリンク文字列が残っているため、知らぬ間に社外送付時に取引先名を流出させていることがあります。（これってコンサルからすると大問題ですよね？でも、、流出させた張本人は気づいていません；）
取引先等の社外に送付するブックは、一度は名前定義が含まれていないか確認し、もし意図していない名前定義が含まれている場合には削除するべきだと思います。

【非表示の名前の定義を表示するコード】

Sub sb非表示の名前の定義を表示()
'アクティブワークブックの名前の定義をすべて表示
'※まれに非表示の名前が存在する
'参考:  エクセルの神髄
        'https://excel-ubara.com/excelvba5/EXCELVBA214.html
'参考:  https://qiita.com/n_sato/items/11fb4265e7a856f64094
'削除すると壊れる名前定義は除外して表示

    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
        If nm.Name Like "*!_FilterDatabase" Then
            ' FilterDatabaseは表示しない
        ElseIf nm.Name Like "*!ExternalData_*" Then
            'ExternalDatは表示しない
        ElseIf nm.Visible = False Then
            nm.Visible = True
        End If
    Next
    MsgBox "処理が完了しました。", vbOKOnly, "処理結果通知"
End Sub

【名前定義を削除するコード】

Public Sub sb安全に名前定義を削除()
' ワークブックの名前定義を削除すると壊れる可能性がある名前を除いて削除する
' 非削除対象:
' - "_FilterDatabase" にマッチする名前定義
' - "ExternalData_" にマッチする名前定義
' - "Print_Area"（印刷範囲の設定）
' - テーブルの範囲に属する名前定義
    
    Dim nm As Name
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim isDeletable As Boolean
    
    On Error Resume Next ' エラーハンドリングを有効にする
    
    ' 名前定義の削除処理
    For Each nm In ActiveWorkbook.Names
        isDeletable = True ' 初期状態で削除可能とする
        
        ' 削除を回避すべき名前定義の条件を確認
        If nm.Name Like "*!_FilterDatabase" Or _
           nm.Name Like "*!ExternalData_*" Or _
           nm.Name Like "*Print_Area" Then
            isDeletable = False
        Else
            ' テーブル範囲に属しているかチェック
            For Each ws In ActiveWorkbook.Worksheets
                For Each tbl In ws.ListObjects
                    If Not Intersect(nm.RefersToRange, tbl.Range) Is Nothing Then
                        isDeletable = False
                        Exit For
                    End If
                Next tbl
                If Not isDeletable Then Exit For
            Next ws
        End If
        
        ' 削除可能な名前定義のみ削除
        If isDeletable Then
            nm.Delete
        End If
    Next nm
    
    On Error GoTo 0 ' エラーハンドリングを解除
    MsgBox "処理が完了しました。", vbOKOnly, "処理結果通知"
End Sub

5.大量の「スタイルのユーザ設定」がある

こちらも名前定義同様、コンサル会社の一部で使う方がいる印象。
他の方の記事かつQiitaの記事ですが、下記の記事をご参照ください。
【Qiita】Excelファイル内に勝手に増殖したセルの書式をまとめて削除する方法

6.ブックのプロパティの会社名やタイトルが古いor実際と異なる

WordやExcelで、プロパティを開くと下記の図のように、「タイトル」や「作成者」や「会社」は、既定の値が表示されます。

Excelのプロパティ（もちろんダミー）

上記画像は、意図的に情報削除しているので基本的に空欄になっていますが、意図して削除していない限り、会社名や自分（や他の方）の名前で「会社」「作成者」に表示されています。
ただ、自社で作成して自社の名前が「会社名」で表示されていればよいのですが、大昔に何らかの手段で他社から受領したものを元に自社用に作り替えたんだろうなぁというファイルだと、会社名に全然違う会社が表示されていたりします。
そのファイルをさらに他社に送付する（例えば取引先や監査法人に提出する）場合に、とても恥ずかしいので、もし他社が「会社名」として表示されている場合は「プロパティや個人情報を削除」から情報を削除しておきましょう。（特に取引先から、「このファイルって、他社のデータをパクッって作成したの？」と思われるのはめちゃくちゃ恥ずかしいですよね。）

なお、「タイトル」は基本的には現在のファイル名になっているか、空欄になっているのが普通ですが、コピーを繰り返してファイル名を変更するうちに、コピー元の変更前のファイル名のままになっていることがあります。（例：実際のファイル名が「情報セキュリティ規則」なのに、プロパティのタイトルが「就業規則」）これも恥ずかしいので、中身と一致しないタイトルの場合は削除しておきましょう。
ただ、こちらの方は自社ではない社名が会社名に記載されているときと違って、気づかれなければ恥ずかしくない、という人もいるかもしれません。
しかし、「タイトル」とファイル名の相違は、実害が生じることがあります。それはExcel等で作成したデータをPDF保存した場合です。
PDF化したときに下記のようにタイトルバーに表示される名前は、Excel等のプロパティの「タイトル」の情報なので、実際のファイル名が「情報セキュリティ規則」　→タイトルが「就業規則」のような場合には、下記のようになります。

PDFの中身とタイトルバーの表示が一致しない

こういう事例では、開いているPDFデータ自体は正しいものであっても、タイトルバーに表示される名前が誤っており混乱の元なので、PDF化前にExcel等のプロパティのタイトル情報を削除してからPDF化することをおすすめします。

以上、いかがでしたでしょうか？
他にも「あるある」があったら、ぜひコメントで教えてください！

もしよろしければサポートをお願いします。今後の執筆のかてにします。