Microsoft Excel ファイルには非表示のバックエンド ワークシートが必要です

私が所有するほぼすべての Microsoft Excel ワークブック (特に他の人と共有しているワークブック) には、非表示のバックエンド ワークシートが含まれています。これは基本的にワークブックのエンジンルームであり、スプレッドシートを整理して機能的に保つのに役立ちます。あなたも持っておくべき理由は次のとおりです。

ワークシートを非表示にすることは、ワークブックの内部構造を見えないようにする良い方法ですが、確実なセキュリティ対策ではないため、機密情報やパスワードには使用しないでください。

バックエンドワークシートを非表示にする方法

よく知られている方法でワークシートを非表示にする

Excel ワークブック内に表示されているすべてのワークシートは、Excel ウィンドウの下部にタブとして表示されます。ただし、ワークブック内に複数のワークシートがある場合は、数回クリックするだけで特定のシートを非表示にすることができます。

Excel ウィンドウの下部にあるバックエンド ワークシート タブを右クリックし、[非表示]をクリックするだけです。

その後、シートはワークシート メニューから即座に消えます。

誰かがバックエンド ワークシートを再表示した場合に備えて、変更を加える必要がないことを明確にするために最上部にメモを追加する価値があります。 「このワークシートは参照専用です」などの文で十分です。

再度表示する必要がある場合は、表示されているタブを右クリックし、[再表示]をクリックします。次に、[再表示]ウィンドウで関連するワークシート (または Ctrl キーを押しながら複数のワークシートをクリック) を選択し、[OK]をクリックします。

ただし、この方法でワークシートを非表示にしたり再表示したりすることについては多くの人が知っているので、ワークブックが目で見るだけの場合にのみこのオプションを使用することをお勧めします。

ワークシートを非常に非表示にする

ワークブックを他の人と共有する予定がある場合は、さらに一歩進んで、VBA エディターを使用してバックエンド ワークシートを非常に非表示にします。これは、この追加の非表示レベルを認識している人が少ないためです。

これを行うには、まず F12 を押してワークブックを保存します。次に、Alt+F11 を押して VBA ウィンドウを開きます。ワークシートを完全に非表示にする前に、ワークシートの表示ステータスを変更できる 2 つのペインをアクティブにする必要があります。まず、リボンの「表示」をクリックし、「プロジェクトエクスプローラー」を選択します。次に、もう一度「表示」をクリックします。今回は「プロパティウィンドウ」を選択します。

次に、[プロジェクト エクスプローラー]ウィンドウで、ワークブック内のワークシートのリストが表示されるまでオプションを展開し、非表示にするバックエンド ワークシートをクリックします。

次に、[プロパティ]ウィンドウで、[表示]ドロップダウン メニューの[xlsSheetVeryHidden]をクリックします。

最後に、VBA ウィンドウの左上隅にある[保存]をクリックし、右上隅にある[X]をクリックして VBA ウィンドウを閉じます。

これで、誰かがシート タブを右クリックして非表示のワークシートを再表示したとしても、非表示になったシートは再表示されません。

非表示のシートを再度表示するには、VBA ウィンドウに戻り (Alt+F11)、プロジェクト エクスプローラー ペインでバックエンド ワークシートを選択し、VBA ウィンドウを保存して閉じる前に、表示ステータスを「xlSheetVisible」に変更します。

ルックアップテーブルを邪魔にならないようにする

スペースを節約して間違いを避ける

私にとって、非表示のバックエンド ワークシートを設定する主な理由は、ルックアップ テーブルを保存する場所を確保するためです。これは、目に見えるワークシートに保存すると貴重な領域が占有されることが多いためです。さらに、ルックアップ テーブルには通常、重要な情報が含まれており、安全に保管しておかないと誤って編集されてしまう可能性があります。

この例では、XLOOKUP 関数を使用して、生徒のスコアに基づいて生徒の成績を生成します。実際、ワークブックの各タブで同じ設定を行っています。

計算そのものと同じワークシートではなく、非表示のバックエンド ワークシートに参照テーブルを保存すると、表示する必要がある情報を表示するための各ワークシート内のスペースが節約されます。また、ワークブックを他の人と共有する場合、ルックアップ テーブルを邪魔にならないようにしておくと、重要な情報に集中できるようになります。

ピボットテーブルにフィードするデータセットを非表示にする

すべてを凝縮

ワークシートの整理整頓を維持するというテーマを継続すると、ピボットテーブルから主要なデータの分析情報を取得する場合、非表示のバックエンド ワークシートが特に役立ちます。結局のところ、表示されるワークシート上でピボットテーブルにきちんと変換されていれば、大きくて不格好なデータセットを誰も見る必要はありません。

ここで、非表示のバックエンド ワークブックには、下 700 行、横 13 行のデータセットが含まれています。

一方、この対応するピボットテーブルはより圧縮されており、操作が簡単で、占めるセルが少なく、読みやすくなっています。

その結果、データセットの表示バージョンは、非表示バージョンよりも解釈と操作がはるかに簡単になり、ピボットグラフ、スライサー、タイムラインなどの追加のデータ視覚化のためのスペースがワークシートに多く残されます。

ルックアップ テーブルと同様に、ソース データセットを非表示にすると、不要な変更からも保護されます。

計算を実行する値を保存する

目に見えずに数値を処理する

私と同じように、値をハードコーディングするよりも数式内のセルを参照することを好む場合は、非表示のバックエンド ワークシートに、計算を促進する値を保存するのに最適な場所となります。さらに、バックエンド ワークシート内の先行セルに名前を付けて、参照しやすくすることができます。

20% の税金を加えた後の各製品の合計コストを計算する必要があるとします。ただし、税金は 12 か月ごとにのみ変更されるため、この変数を含むセルに常にアクセスする必要はありません。

代わりに、バックエンド ワークシートに名前付き先行セルを作成できます。まず、計算値を入れるセルを選択し、Excel ウィンドウの左上隅にある名前ボックスを使用して名前を付けます。次に、セルに値を入力し、値が何を表すかがわかるように隣接するセルにテキストを追加します。

これで、ワークシートが非表示または完全に非表示になっている場合でも、数式内でこの名前付きセルを参照できるようになります。

=(@Cost)*Tax

その後、翌年税額が変更された場合、非表示のバックエンド ワークシートを一時的に再表示して値を更新できます。

3D 参照のエンドポイントを作成する

データセットが相互に流出しないようにする

私は学術校正者として、毎月新しいワークシートを作成して、月ごとの単語数 (セル D2) を集計しています。次に、これらの月次値を使用して、全体的に読んだ単語数の現在までの合計を記録します (セル H2)。

その結果、翌月の新しいワークシートを追加するときに、Excel でそれを累計に含める必要があります。ここで、非表示のバックエンド ワークシートが役に立ちます。

セル H2 に次のように入力しました。

=SUM('Feb 2023:Backend'!D2)

どこ

• 2023 年 2 月 ワークブックの最初のワークシートを参照します。
• バックエンド は非表示のバックエンド ワークシートの名前です。これは、非表示になる前は一番右のタブでした。
• D2 は、合計したい数値を含む各ワークシートのセルです。

バックエンド ワークシートを非表示にしたとき、バックエンド ワークシートはワークブックの右端のタブにあったため、新しいワークシートを追加した場合でも、この位置に留まります。これは、後で追加する月次ワークシートが 3D リファレンスで取得されることを意味します。

最後に、非表示のバックエンド ワークシートのセル D2 に誤って値を追加しないように、セルを赤色で塗りつぶし、「赤色のセルは空白のままにする」という注記を追加しました。

安全に保管する必要があるデータを失わないようにする

それらの番号をロックダウンする

非表示のバックエンド ワークシートを使用して、保存する必要があるが、現在は非表示のワークシートに表示する必要がないデータを保存できます。

屋根裏部屋を片付けるときと同じように、将来必ず必要になるだろうが、現時点では必要ではないものがいくつかあるため、それらを箱に入れて保管します。非表示のバックエンド ワークシートも同じ目的を果たします。

たとえば、ここでは、数式で参照されなくなったにもかかわらず、前年のルックアップ テーブルを保持しています。

これは、過去の傾向を分析したい場合や古い数値を再利用したい場合など、必要に応じて後日このデータを参照できることを意味します。

個人的なメモやリマインダーを保存する

指導にも最適です

非表示のバックエンド ワークシートの最終的な目的は、指示、連絡先の詳細、リマインダー、期限のリストなど、必要に応じて参照できるテキスト情報を保存することです。これにより、別のワークブックや Word ドキュメントを作成する必要がなくなり、詳細が見えなくなるため、ワークブックが適切に整理された状態に保たれます。

非表示のバックエンド ワークブックに情報が多すぎる場合は、単に情報を追加してください。不要なデータを保存し、その結果として Excel ファイルが必要以上に大きくならないように、すべてのデータを適切に維持するようにしてください。

---

バックエンドを構築したので、ホームページをセットアップします

非表示のバックエンド ワークシートを使用するだけでなく、共同作業者を歓迎し、ワークブックの使用方法を説明し、サポートに最適な場所にユーザーを誘導し、データ スナップショットや締め切り日などの重要な情報を含むホームページ ワークシートを Excel ファイルに追加する必要もあります。