Excel を PC 上のすべてのポータルに変え、ついにファイルを理解しました

Excel を PC を管理するためのツールとは考えていないかもしれません。私もそうではありませんでした。しかし、何年にもわたってファイルを蓄積した後、これを使用して Windows ユーザー フォルダー内のすべてのインベントリを作成したところ、突然、これまで見たことのない方法でコンピューターを確認できるようになりました。その情報が Excel に保存されたら、それをフィルタリングし、さまざまな基準を組み合わせ、ダッシュボードを構築し、偶然見つけたウサギの穴を追跡することができます。

PC のファイルを Excel データセットに変換しました

Power Query が面倒な作業を引き受けます

Power Query は、必ずしもそれに値する評価を得られるわけではありません。私はこれを主に Excel にデータを取り込む前にデータをクリーンアップする方法として考えてきました。このプロジェクトは、それ以上のことができることを思い出させてくれました。

Power Query を使用しました フォルダーから Excel を Windows ユーザー フォルダーにポイントするための接続。クリック後 データの変換これにより、フォルダー自体をリストすることなく、そのフォルダー内のすべてのファイルとその下にネストされたすべてのフォルダーのクリーンなリストが得られました。

Power Query は、各ファイルの名前、拡張子、日付、サイズ、フォルダーの完全なパスなど、必要な基本情報を提供してくれました。インベントリには、ダッシュボードの構築に使用していたワークブックも含まれており、非常に具体的なものになっています。バイナリを削除しました コンテンツ 列をクリックし、 拡大する 上部のアイコン 属性 ファイルサイズを抽出する列。また、拡張子のないファイルに便利なラベルを付けました。 変換 > 値の置換 そのため、チャートでは空白のカテゴリとして表示されません。

次に、クリーンアップした結果を Excel テーブルにロードしました。

ここの利点は、これが 1 回限りのエクスポートではないことです。テスト ファイルを追加し、クエリを更新して、それがインベントリに表示されるのを確認しました。ファイルを削除して再度更新すると、テーブルからファイルが消えました。つまり、ワークブックをファイルの更新可能なビューとして使用できるということです。

在庫をダッシュ​​ボードにしてみました

副操縦士が全体像を教えてくれる

ここ数か月間、Copilot を何度もテストしましたが、このような複雑なデータセットからでも、ダッシュボードを作成するのに非常に優れていることがわかりました。とはいえ、Copilot がそこに到達する唯一の方法というわけではありません。ピボットテーブルとピボットグラフのみを使用して、完全に機能する Excel ダッシュボードを構築しました。

Copilot を使用する場合は、かなり正確な仕様を指定してください。また、最初に Copilot にデータを分析させると、現在のデータセットで際立っているものに特化しすぎたダッシュボードが作成される可能性があることがわかりました。ファイル インベントリの変更に応じて更新できるダッシュボードが欲しかったので、私はそのアプローチを避ける傾向があります。

これが私が使用したプロンプトです。必要に応じて、意図したセットアップに一致するように調整します。

基礎となるファイル インベントリ テーブルからコンパクトな Excel ダッシュボードを作成します。ファイルの合計、合計サイズ、一意のファイル拡張子の数、6 か月以内に変更されていないファイルのカードが含まれます。更新日を基にした各ファイル期間のファイル数を示すグラフと、拡張子別のファイルとファイル数別の上位 10 フォルダーのグラフを追加します。ランク、ファイル拡張子、ファイル名、ファイル サイズ、変更日を含む最大 10 個のファイルを表示するピボットテーブルを追加します。現在のデータを分析してダッシュボードに何を含めるべきかを決定するのではなく、すべてのダッシュボード要素を基礎となるファイル インベントリ テーブルに基づいて決定します。

ダッシュボードでは、PC 上に実際にあるものの概要が表示されました。拡張子グラフでは、ファイルの大部分に拡張子がないことがすぐにわかりました。また、R、PNG、JS、LOG、XML などのファイル タイプも驚くほど頻繁に表示されます。これは、Windows ファイル コレクションには Word 文書、スプレッドシート、PDF よりもはるかに多くのファイルが含まれていることを思い出させます。

最大ファイルのピボットテーブルでは、Excel ワークブックやインストーラーから実行可能ファイルやデータベース ファイルに至るまで、あらゆるものが表示され、フォルダー チャートでは、ほとんどのファイルが集中している場所が示されました。最も使用頻度の高い場所の一部は、アプリケーション フォルダーとキャッシュ フォルダーであることが判明しました。これにより、実際にシステム上にあるものについて別の考え方が得られます。

スナップショットを取得したので、基になるファイル自体の調査を開始したいと思いました。

テーブルは自分の PC を実際に調査できる場所です

Excel でファイルについて質問できる

これが私にとってこのプロジェクトにピンと来た部分です。まず、基礎となるインベントリに戻って調べました。 変更日。列を古いものから新しいものへ並べ替えると、2019 年に遡るタイムスタンプを持つファイルが表示されました。次に、それらの行の 1 つからフォルダー パスをコピーし、ファイル エクスプローラーのアドレス バーに貼り付け、そこに何があるかを確認しました。どのフォルダーにそのファイルが含まれているかを覚えたり、適切な場所を見つけようとしてネストされたディレクトリを探したりする必要はありませんでした。

他にも気になったものをフォローしてみました。たとえば、インベントリ内の 2 つの最大のファイルが両方とも Excel ワークブックであることに気づいたので、他のどのような大きな XLSX ファイルが私が認識しているよりも多くのスペースを占有しているのかを確認したいと思いました。テーブルをファイルサイズで並べ替えてから使用しました テキストフィルター > 次で終わる で 拡大 列にXLSXファイルのみを表示します。 1MB を超えるファイルが 13 個見つかりました。そのうち 2 個は約 369MB、もう 2 個は約 43MB でした。これは、エクスプローラーでフォルダーを参照しているときに簡単に見落とす可能性のある種類のことです。

インベントリはファイルの分析に最適ですが、何かを見つける必要があるときに使用できる検索可能なインデックスとしても機能します。たとえば、特定のハウツー オタク文書を検索しようとしている場合、 名前 「geek」を含むファイルを表示する列。単に押すこともできます Ctrl+F インベントリでキーワードを検索します。

そしてそれはほんの始まりにすぎません。ファイルはデータになったので、スライサーを追加してさまざまなフィールドを組み合わせて、何年もアクセスされていない大きなファイル、特定のフォルダー内の古い PDF、最も多くのスペースを占めているファイルの種類、特定の期間に作成されたファイルなどを見つけることができます。これをさらに進めたい場合は、同じ在庫からピボットテーブルを作成し、タイムラインを完成させることもできます。 変更日。

さらに重要なのは、いつでも同じワークブックに戻ってインベントリを更新し、そのときに目についたものを調査できることです。

Excel はグリッドをはるかに超えて到達できる

Power Query を PC に接続すると、Excel が通常入力するデータを超えてどこまで到達できるかがわかりました。同じ フォルダーから 接続は、同じフォルダーからの同様の週次 Excel レポートを 1 つの完全なデータセットに結合する、より集中的なレベルで機能することもできます。だからこそ、Excel を調べてみる価値があるのです。 データの取得 落ちる。これまで考えもしなかった用途に Excel を使用する方法が見つかるかもしれません。