ワークシート診断ツール

  1. Home
  2. その他
  3. ワークシート診断ツール

ワークシート診断ツールとは

Excelのブックです。普通のブックです。そう見えます。でも、このブックの中に、どんな名前が定義されているか分かりますか？ユーザー定義の表示形式は、いくつ登録されていますか？無駄な条件付き書式や入力規則は設定されていませんか？どのセルに、どんな数式や関数が使われていますか？ブックに記録される情報は、セルに入力した数値や文字列だけじゃありません。実に、さまざまな情報が記録されています。もちろん、必要であれば、名前でも入力規則でも何でも使ってください。でも、その結果、ブックのサイズが肥大化したり、Excelの動作が重たくなります。分かってやっているのならいいです。でも、ほとんどの人は、そのブックがどんな状態か。いわばブックの健康状態を気にしません。ワークシート診断ツールは、ブック内の情報を調べて、診断結果として提示します。

ブックのサイズが肥大化したり、動作が重たくなる程度なら、まだいいです。ブックの中には個人情報などが記録されることをご存じですか？このツールを開発したとき、実際に試してみました。東京都23区などでは、それぞれホームページを公開しています。たいていは人口統計などの資料をExcel形式のブックでダウンロードできるようになっていますよね。それをダウンロードし、診断ツールでチェックしたところ、PCへのログインユーザー名や、サーバーへのフルパスなどが記録されていたブックもありました。ハッキングとか情報漏洩というのは、こうしたミスがきっかけになります。あなたのブックとて例外ではありません。

ここをクリックするとダウンロードできます
ファイル名：chkBook_105.zip
サイズ：約144KB

診断項目

「ワークシート診断ツール」で診断できるのは、次の項目です。一部をご紹介します。

[概要]シート

ブック全体の情報を一覧表示します。ブックに含まれるワークシートの数や、非表示シート、保護されたシート、ロックされたセルの個数などの一覧リストです。それぞれの詳しい情報は、セルのリンクをクリックすることで詳細ページに移動できます。

[シート]シート

シートに関する情報です。[結合セル]列は"結合されているセルの個数"ではなく"結合されている箇所の数"です。

[表示形式]シート

記録されている"ユーザー定義書式"です。ちなみにこれ、VBAでは取得できません。

[スタイル]シート

ユーザーが独自に作成したスタイルです。別ブックからシートごとコピーなどを繰り返すと、これが増殖する場合もあります。

[名前]シート

項目から説明します。[定義された名前]列はその名のとおり定義されている名前です。[参照]列とは、その名前が参照しているセルまたは値を表します。一般的には"結合!前年売上データ_1"のようにセルやセル範囲を参照しますが、"曜日リスト"のように値も定義できます。「表示」列は、その名前が[名前の定義]ダイアログボックスに表示されるかどうかを表しています。"FALSE"は非表示の名前定義であり、ダイアログボックスに表示されません。そうした非表示の名前定義は、一般的にExcelが内部で使用するために定義されていることが多いです。今回の結果で[参照]列が"=#NAME?"になっているのは、すべてExcelによる内部定義です。おそらくこれらは、古いバージョンのExcelでブックを開いたときの下位互換性のために用意されていると思います。こうした内部で定義された名前として、ほかにも"_FilterDatabase"や"_Print_Area"や"Print_Titles"などが登録される場合もあります。[範囲]列は定義された名前のスコープ(有効範囲)です。ブックレベルの名前定義にはブック名が表示され、シートレベルの名前定義は使用できるシート名が表示されます。[エラー]列は表示される名前定義のうちエラーになっているものです。この"エラーになっている名前定義"は、[名前の定義]ダイアログボックスから削除可能です。

[数式]シート

ブック内に入力されている全ての数式をリストアップします。エラーを返している数式は[エラー]列に"#DIV/0!"などと表示します。Ctrl + Shift + Enterで入力する古い配列数式は[配列数式]列に"配列"と表示します。配列数式の[数式]列に"{}"は表示されません。スピルを使った動的配列数式はチェックしていません。それよりも、古い配列数式をいまだに使っているのなら、新しいスピル形式に変えるべきですね。留意していただきたいのは124行目、[4月]シートセルI9の数式です。この数式は別ブックのセル範囲を参照しています。このとき、デスクトップなどに保存されたブックを参照していると、このように"外部参照"になり、数式内にはOneDriveのURLが記録されます。"***"部分にはOneDriveのユーザーIDが記録されます。怖いですから、できるだけ外部ブックへの参照はしない方が良いでしょう。

[関数]シート

ブック内で何の関数が使われているかを、シート別に表示します。難しい関数を多く使っていると、引き継ぎで困るかもしれません。10行目の"SORTBY"ですが、これはおそらく数式内で"セル内改行"されていると思われます。詳しくは[数式]項目を見れば分かります。関数名抽出を高速に行うため、このような表示にしています。まあ、手抜きです。

[参照]シート

数式がアクティブシート外を参照しているかをチェックします。外部ブックを参照していると、ブックのフルパスを表示します。

[条件付き書式]シート

設定されている条件付き書式の一覧です。設定されている適用範囲は[アドレス]列に表示されます。ここは要チェックですね。

[入力規則]シート

設定されている入力規則の一覧です。

[テーブル]シート

存在するテーブルの一覧です。クエリの結果であるテーブルはクエリ名を表示します。

[ピボットテーブル]シート

存在するピボットテーブルの一覧です。Power Queryから取得したピボットテーブルは、上記のようにクエリ名も表示されます。

[オブジェクト]シート

シート上に配置したオブジェクトの一覧です。ワークシート上にある、いわゆる"埋め込みグラフ"もVBA的にはオブジェクトです。登録しているマクロなども表示します。[親オブジェクト]というのは、正直よく分かりません。VBAの仕組み的にそうなっていたので抽出しています。たぶんですけど、いくつかのオブジェクトをグループ化して、そうしたパーツをさらにグループ化して、みたいな複雑なケースかと思いますけど、それほど詳しく検証していません。Excelでやることじゃないです。あと、[種類]列の"OLE コントロール オブジェクト"と表示されるのは、[開発]タブ[挿入]の「ActiveXコントロール」です。Microsoftは、ActiveXコントロールを使わないようにと注意喚起していますから、これがあったら「フォーム」に変更しましょう。セキュリティ的に危険です。

[クエリ]シート

Excelには昔から"クエリ"という機能があるのですが、最近ではもっぱら"Power Query"ですね。なので、それを中心に抽出しています。M言語も表示します。これ、かなり喜ぶ人がいるのでは？実は"クエリ"って、けっこう奥が深くて、やろうと思えばかなり複雑なことも可能です。すみませんが、全てのあらゆるケースには対応していません...と思います。

[データ接続]シート

このへんは難しいです。そもそも"クエリ"と"データ接続"って何が違うの？って話なんですが、難しい話は割愛しますけどExcel的には別物として扱っています。とりあえず、こんな感じに抽出します。

[ハイパーリンク]シート

セルに設定しているハイパーリンクです。[リンク先]列にフルパスなどが表示されるのは、いわゆる"外部ファイルへのリンク"です。

[VBA]シート

プロシージャの一覧などを表示します。[API]列はAPIの宣言をしているかどうか。[注意][警告][互換性]は私の独断です。内容は[ログ]シートをご覧ください。宣言セクションに"Option Explicit"があると、プロシージャがなくても"コードがある"と認識されます。

重要：

VBAの中を診断するには、[Excelのオプション]-[トラストセンター]-[トラストセンターの設定]をクリックして表示される[マクロの設定]で[VBA プロジェクト オブジェクト モデルへのアクセスを信頼する]チェックボックスがオンになっている必要があります。これは、ブックの設定ではなくExcel全般の設定です。診断ツールを実行するPCのExcelでオンにしてください。このチェックボックスをオンにすると、VBAからVBEにアクセスが可能になります。心配な方は、診断ツールを実行するときだけオンにして、終わったらオフにすれば良いでしょう。

[ログ]シート

診断時のログです。見てお分かりのように、ほとんどは1分程度で完了します。

使用上の注意

  • 診断を行うPCの性能にもよりますが、ほとんどのブックは、おおよそ1分以内で診断は終了します。ただし、何らかの異常があるブックでは数分以上の時間を要するケースもあります。たとえば、気づいていない名前定義や表示形式などが数千個も登録されていたり、条件付き書式や入力規則が数百万～数千万セルに設定されているとか。これ、安易に列単位に設定してあるブックでは、ときどき起こります。予想外に時間がかかっていても、できればそのまま待ってください。
  • 診断を行うときは、Excel上に「診断ツール」と「診断対象ブック」の2つだけを開いた状態にしてください。3つ以上のブックを開いていると、その旨を表示して診断を中止します。
  • 診断のとき、診断対象ブックを、一度閉じてから開きなおします。ブックに読み取りパスワードが設定されていたり、ブックを開くとき巨大なクエリが自動的に更新されるようなときは、ちょっと困ります。一応自動実行マクロは抑止していますけど、マクロの書き方によっては抜け道もありますし、できればそういうブックを診断するときは、あらかじめパスワードを解除したり、クエリの自動更新を止めたり、自動実行マクロを抑えるなどの工夫をしてください。
  • 万が一、診断の途中でエラーが発生した場合、診断対象ブックの再計算が手動のままになる場合があります。エラーになったら、そこを確認してください。
  • ネットからダウンロードしたマクロ入りのブックを開くと「強制的にマクロが無効になる」場合があります。本ツールも、それに該当するかもしれません。そのときの対処法は下記ページをご覧ください。
    「ネットから入手したブックでマクロが使えない件」

AI時代の必須ツール

実はこの「ワークシート診断ツール」ですが、開発のきっかけは"マイクロソフトからの依頼"でした。Excel 2007が発売されて数年が経った頃、何かの用事でマイクロソフトと打ち合わせをしていたとき、Excelの担当者から「2003で作ったブックを2007で開くと、描画がズレるみたいな問い合わせが来てるんですよ。あと、機能的にも大きく変わったじゃないですか。条件付き書式とか。なので、2003までで作ったブックの内容を調べて、2007以降で開いても問題ないかどうか確認するツールが欲しいんですけど、田中さん作ってくれません？」みたいな。簡単に言うわ～ｗ と思いましたけど、面白そうだったので「そうですね、作りましょうか」という感じでスタートしました。最初は画面描画や、Excel 2007の新機能だけ調べるつもりだったのですが、作っていくうちに「あれも欲しいな、これも欲しいな」と確認項目が増えていき、そして現在に至ると。

そんな経緯で、今からおよそ10数年前に作った「ワークシート診断ツール」ですが、最近のAIブームにおいて、改めて脚光を浴びています。大きな理由は次の二つです。

  • 今のAIにExcelブックを添付しても、AIが認識できるのは「セルに表示されている値」など、ごくわずかです。名前定義や条件付き書式やPower QueryのM言語などは見えないです。なぜならAIは、添付したブックを一度CSVに変換してからAIに渡すからです。しかし、それでは適切な回答ができません。ブック内に定義されている多くの情報を知りたいのですが、それはかないません。そこで、ブック本体を添付するのではなく、診断ツールが出力した「診断結果ブック」を添付してやります。AIにとって必要なのは、セルに入力されている値ではなく、ブックの構造なのです。
  • 実務で使うブックには、もちろん機密情報なども含まれます。そのブックをAIに添付するのは、コンプライアンス的にもよろしくありません。でも、実際のブックを示さないと数式の作成やマクロの修正など、正確な回答をAIは返してくれません。だから「診断結果のブック」を渡すんです。大事なことなのでもう一度書きますが、AIにとって必要なのは、ブック内の値(情報)ではなくブックの構造です。診断結果に、ブック内の情報は一切記録されません。構造解析には必要ないからです。これなら、安心してAIに添付できます。

開発してから十数年経ちますが、今や「ワークシート診断ツール」は、単にブック内を調べるツールではなく、実務の現場とAIをつなぐ架け橋になったわけですね。いやぁ～ようやく時代が私に追いついたみたいなｗ

ここをクリックするとダウンロードできます
ファイル名：chkBook_105.zip
サイズ：約144KB

改変履歴

  • Ver1.04 2023/11/07
    関数名の判定方法を変えた
    データ接続とクエリの強化(Power Queryに対応)
  • Ver1.05 2026/02/02
    Power QueryのM言語を取得するようにした
    診断結果ブックのセルB1に診断ファイル名を表示するようにした
    ピボットテーブル自動更新を追加した
    関数名がシート内で重複していたのでCollectionで処理した
    診断結果ブックの[ワークシート]を[シート]にした
    「ユーザー定義書式」の不具合解消