Excelファイルの容量が重い時に確認すること

Excelファイルの内容見ても、大したことをやっていないはずなのに、何故か容量が大きいファイル。
操作中にクラッシュすることがあるなら、軽量化できないか下記のようなことを試してみましょう。

1.使用していないセル範囲（UsedRange）が広がっていないか

Excelは「使っているセル範囲」を内部的に記憶しています。
実際には何も表示されていなくても、

• 一度入力して削除した
• 書式だけ設定した
• 行・列を大量にコピーした

といった履歴があると、最終行・最終列が無駄に遠くまで認識されます。

確認方法

Ctrl + G キーを入力して、セル選択を選択します。

「最後のセル」を選択して、「OK」ボタンを押します。

数式等を入力していないとんでもない位置に飛ばされたら、最終行・最終列が誤って認識されているので、正常な位置から最後のセルまで（またはCTRL＋↓等で最下部等まで）を削除して、最後のセルの位置を補正しましょう。

それだけで、容量がかなり軽くなるはずです。（また、最後のセルの位置が変な場合に、スクロールバーのサイズもかなり小さくなっていたはずなので、それも適正なサイズに戻ると思います。）

2.不要な書式設定が大量に残っていないか

不要な書式設定があると、容量が重くなります。Excelは「値」よりも「書式」を多く持ちます。

• 背景色
• 罫線
• フォント
• 条件付き書式

特に、条件付き書式がシート全体に適用されていると容量が激増することがあります。

確認方法

ホーム → 条件付き書式 → ルールの管理
適用先が A:XFD や 1:1048576 になっていないか

もし不要なセルにまで条件付き書式が設定されている場合は、必要な範囲のみに適用し直してください。または、不要なルールは削除しましょう。

3.見えない大量の図形が大量に隠れていないか確認する

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

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

図形を選択してみるとわかりやすいです。（なお、実際は下記画像例のように斜めに並んでいるというより、同じ行に大量に存在することが多いと思います。）

原因は、Excel2003などの古いバージョンのExcelでは、行や列を削除したとき、そのオブジェクトのプロパティで「セルに合わせて移動やサイズを変更する」になっているシェイプ（図形）が含まれていると、行や列を削除すると、高さや幅がゼロのシェイプが残ることがあったためです。

2007以降のバージョンでは行や列に完全に含まれるオブジェクトの場合は、高さや幅がゼロのオブジェクトは生じず、オブジェクトそのものが削除される仕様に変わりました。
しかし、過去に発生してしまった高さ・幅がゼロの図形は、基本的にそのままに残ってしまうため、このような事象が生じすることがあります。

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

コード

ExcelVBA

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

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

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

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

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

そのため画像を貼り付けする場合には、最初からスクリーンショットを、範囲選択で取得する等の工夫をするとよいでしょう。
もしそれが面倒である、又は既にトリミング部分が隠れたファイルをどうにかしたい場合は、トリミング部分を削除するには、画像１枚１枚削除する方法もありますが、ブック内に複数画像があるときは１つ１つ作業するは面倒なので、まとめてトリミング部分を削除しましょう。

「名前を付けて保存」＞「ツール(L)」＞「図の圧縮(Ｃ)」で下記の画面にいけるので、任意の解像度を選択して、ブックを上書き保存すればトリミング部分が完全削除できます。

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

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

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

機能として知らない方も多い名前定義。

個人的にはJTCやコンサルの一部で利用する方がいる印象。その一部の名前定義使いの人から他の方に引き継がれるにしたがって、名前定義が大量増殖や#REF!エラーになっていきます。

なぜか分からないのですが、名前定義が数千レベルで設定されていることもあります。

容量としては些細なレベルではあるのですが、情報漏洩防止のためにも#REF!エラーの名前定義は削除しましょう。

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

ExcelVBA

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

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

ExcelVBA

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

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

こちらも名前定義同様、コンサル会社の一部で使う方がいる印象。
何万個にスタイルが増殖していると容量が重いので、削除しましょう。
Excelファイル内に勝手に増殖したセルの書式をまとめて削除する方法

7.数式が必要以上に多い

何万行にもコピーされた数式は、当然、容量が重くなる原因になります。
現実的な利用想定行数より、多少バッファーは設ける必要はありますが、不必要に最下部まで数式を設定する必要はありませんので、考えて数式設定しましょう。

また、使用している関数（VLOOKUP関数やSUMIF関数等）によっては計算に負荷がかかり、操作中にクラッシュする原因になることがあります。
数式を設定している範囲は適正だが、関数自体が計算負荷が重いものである場合には、セルを変更するたびに自動計算する設定を「手動計算」に変更するのも選択肢の一つです。

ちなみに、自動計算から手動計算への切り替えは、「数式」タブの「計算方法の設定」を選択し、「自動」から「手動」に切り替えることができます。

ただし、手動計算は下記のようなミスが起きる原因にもなります。

• 計算結果を信頼していたら再計算されておらず、間違った結果を参照していた。
• 手動計算のブックと同時に、別のブックを編集していたら、そのブックが意図せず手動計算に変わってしまった。

社内で利用する場合にはチーム全体で共通認識をした上で共通のExcelフォーマットに利用しましょう。（例：手動計算にしているブックのファイル名に「手動計算」とつける）

以上、いかがでしたでしょうか？

なお、テーマとして、重なる部分も多く、自分の過去記事である代々引き継がれてきたExcelブックにありがちなことからの焼き直しが多くなっております。