Excel を PC 上のフォルダーに接続すると、週次レポートが自動的に更新されるようになりました

私は Excel の週次レポートを結合するのが嫌いでした。あるワークブックから別のワークブックにデータをコピーするだけでも十分に機能しますが、毎週行う必要があると面倒になります。次に、それを実行したかどうかを思い出すか、既存のデータを上書きすることを心配する必要があります。ありがたいことに、Excel はフォルダーに接続してそれらのファイルを結合できます。Excel のウィザードのように聞こえますが、セットアップは 4 つの簡単な手順だけで完了します。

ステップ 1: 毎週のファイルを設定する

構造の一貫性を保つ

このワークフローには、データを結合、変換、クリーニングするための使いやすいインターフェイスである Power Query が必要です。正しく動作させるには、まずファイルが正しく設定されていることを確認する必要があります。

私の例では、というフォルダーを作成しました。 週次レポート。毎週、という名前の Excel ファイルを追加します。 売上_週_## を週番号に置き換えます。 売上_週_1 はすでにフォルダー内にあるので追加します 営業_週_2 そして 営業_週_3 後で。

各ワークブックには、と呼ばれるワークシートが含まれています。 販売データ。このワークシートには同じ 6 つの列があります。

• 日付

• 製品

• 地域

• 販売員

• 量

• 収益

データと行数は週ごとに変化する可能性があります。何 する ただし、一貫性を保つ必要があるのは、ワークシート名、列見出し、および基本的なデータ構造です。言い換えれば、すべての週次レポートが同じテンプレートに従っている場合、Excel ではそれらを結合する作業がはるかに簡単になります。

また、ソース データを通常のワークシート範囲として保持します。このワークフローでは、各週次レポートを Excel テーブルに変換するために時間を無駄にする必要はありません。

次に、マスター ワークブックを作成する必要があります。マスター ワークブック内で右クリックして、すべての週次データが最終的に結合されます。 週次レポート フォルダーにカーソルを合わせる 新しい、クリック Excelワークブック、そしてそれに名前を付けます 週次売上レポート。私はそれを同じままにしておくのが好きです 週次レポート フォルダーに保存すると、すべてが一緒に保たれます。 Power Query に週次レポートを検索するときに無視するように指示するので、これは問題ありません。

ステップ 2: Excel をフォルダーに接続する

反復的な作業を Power Query に任せる

マスターを接続するには 週次売上レポート ワークブックを個々のファイルに移動し、それを開いて、 データ > データの取得 > ファイルから > フォルダーからを選択し、 週次レポート フォルダを選択し、 開ける。

Excel は見つけたファイルを表示します。次にクリックします データの変換 を開くには パワークエリエディタここでは、複雑な数式を作成したり、コードを記述したりすることなく、Power Query にいくつかの指示を与えることができます。

まず、をクリックする必要があります フィルター矢印 で 名前 列ヘッダー、選択 テキストフィルター > 次で始まらない、 タイプ 毎週をクリックしてフィルターを適用します。これにより削除されます 週次売上レポート などのファイルを残したままリストから 売上_週_1 そして 営業_週_2 組み合わせてご利用いただけます。

次のフェーズでは、ファイルを結合し、どのワークシートを使用するかを Excel に指示し、結合されたデータをマスター ワークブックにロードし直します。

これを行うには、次のことを見つけます。 コンテンツ 列をクリックして、 ファイルを結合する 列ヘッダーのボタン。 Excel でどのワークシートを使用するかを尋ねられたら、選択します 販売データ、毎週のファイルごとに表示されるワークシートをクリックし、 わかりました。

次に、Power Query がフォルダー内のブックのデータを結合するので、分割の上半分をクリックできるようになります。 閉じてロード ボタンをクリックして結果を Excel に戻します。マスター ワークブックには、第 1 週からの 12 個のデータ行が含まれています。 ソース.名前 列は、各行がどの週次ファイルから来たかを示します。

これは、完了したプロセスの少し面倒な部分ですが、良いニュースは、これらの Power Query の手順を再度実行する必要がないことです。

ステップ 3: 別の週のレポートを手動で追加する

ワンクリックで完了です

ここで、第 2 週のレポートが表示されたときに何が起こるかを見てみましょう。初めてこれを実行したとき、すべてがうまく機能することに驚きました。コピー アンド ペーストする必要はなく、Power Query に戻る必要もありませんでした。まず、私が移動します 営業_週_2 に 週次レポート フォルダ。

次に、 週次売上レポートに向かいました。 データ タブをクリックして すべて更新。

この時点で、Excel はフォルダーをチェックして 2 番目のブックを見つけ、そのデータを既存のレポートに追加して、12 行のレポートを 24 行のレポートに変換します。

ここで、すべてを適切に設定することの利点が明らかになります。 Excel に新しいブックについて通知したり、クエリを再構築したりする必要はありません。ファイルをフォルダーに追加して更新するだけです。ただし、プロセスをさらに簡単にするもう 1 つの設定があります。

ステップ 4: Excel でレポートを自動的に更新する

設定したらあとは忘れる

前の手順では、マスター ワークブックを開いて、 すべて更新。これはすでにコピーして貼り付けるよりも簡単ですが、Excel にそのステップを自動的に処理させることができます。

マスター ワークブックが開いたままの状態で、次の場所に移動できます。 データ > クエリと接続をクリックし、クエリを右クリックして、 プロパティ。次に、 使用法 タブ、確認してみます ファイルを開くときにデータを更新する、 クリック わかりましたをクリックしてワークブックを保存し、閉じます。

さて、 週次売上レポート 追加すると閉店しました 営業_週_3 に 週次レポート フォルダを作成して再度開く 週次売上レポートExcel は自動的にクエリを更新し、フォルダーを確認し、3 番目のブックを見つけて、そのデータをレポートに追加します。

これで、何も手動でコピーすることなく、3 つの週次レポートをすべて結合できるようになりました。それが見返りです。初期セットアップ後は、ファイルを適切なフォルダーに置くだけで、互換性のある別のレポートを追加できます。移動することもできた 営業_週_3 から戻って 週次レポート フォルダーを開き、次に開いたときに 週次売上レポート、それらのデータ行は消えてしまいます。

今後レポートを追加するときは、ステップ 1 で設定したのと同じ構造、つまり同じワークシート名、列見出し、基本データ構造を確実に維持する必要があります。ファイルがそのテンプレートに従っている限り、Excel はファイルが到着するたびにそれらを結合し続けることができます。

毎週の Excel 作業が 1 つ減ります

週次レポートを手動で結合することから、最新のファイルをフォルダーにドロップしてマスター ワークブックを開くだけになりました。これは私のワークフローに対する小さな変更ですが、これまで毎週覚えていなければならなかった繰り返しの Excel ジョブの 1 つが削除されます。

レポートでピボットテーブルを使用している場合は、自動化できる手順がもう 1 つあります。ピボットテーブルを自動的に更新する VBA ツールを作成したので、手動で更新することを忘れる必要もありません。