ついに Excel のタイムスタンプを自動化し、最大のデータ信頼性の問題を解決しました

データが最新かどうかを心配する人ほど、細かく調整された Excel ワークブックをすぐに無効にするものはありません。手動で日付を入力する代わりに、Power Query に面倒な作業を実行させることができます。

複数のファイルのテーブルを結合したワークブックを管理していると想像してください。すべてが正しく設定されている場合でも、データが最後に更新されたのがいつかを知る必要があります。 「最終更新」タイムスタンプが表示されると、その不確実性が取り除かれ、誰もが作業している数値を信頼できるようになります。設定によっては、単一のレポート レベルのタイムスタンプ、または個々のテーブルのより詳細なタイムスタンプが必要になる場合があります。両方を作成する方法は次のとおりです。

Excel でレポートレベルのタイムスタンプを作成する

スタンドアロンの「すべて更新」トラッカーの構築

通常、すべてのデータを一度に更新する場合は、スタンドアロンの「最終更新」ボックスが最もクリーンなソリューションです。これは、ワークブックに関するすべての情報を保存する README またはダッシュボード ワークシートに最適です。

このメソッドは、クリックするたびに更新される専用のクエリを作成します。 データ > すべて更新。

行う必要があるのは次のとおりです。

1. Excel で、 データの取得 で データ タブ。

2. マウスオーバーします 他の情報源から。

3. 選択 空のクエリ。

次に、Power Query エディターで次のようにします。

1. で クエリ設定 右側のペインで、 名前 のような説明的なものに Manual_Refresh_Timestamp。

2. 数式バーに次のように入力します。 = DateTime.LocalNow() そして押します 入力。これにより、日付と時刻がクエリ出力に追加されます。

3. 一番右にある 変身 タブ、クリック テーブルへ > テーブルへ。

4. 列ヘッダーをダブルクリックして名前を変更します 最後の手動ワークブックの更新。

5. をクリックします。 ABC123 列ヘッダーのアイコンをクリックして選択します 日付/時刻。

この時点で、機能する時計が Power Query エディターに表示されます。これには、更新を実行するマシンのシステム時間が使用されることに注意してください。これは、異なる場所にいる複数の人がワークブックを更新する場合に問題になる可能性があります。

プロセスを完了するには、それをダッシュ​​ボードにロードする必要があります。

1. で 家 タブ、クリック 閉じてロード > 閉じてロード…。

2. で データのインポート ダイアログ、選択 テーブル そして 既存のワークシート。

3. をクリックします。 崩壊 アイコン (上矢印) をクリックし、タイムスタンプを保存したいダッシュボード上の特定のセルを選択して、 を押します。 入力。

4. クリック わかりました。

これで、ファイルの一般的なステータス インジケーターが完成しました。あとは、その部分が見えるようにフォーマットするだけです。たとえば、フィルター ボタンを削除し、別のテーブル スタイルを選択します。 テーブルデザイン タブで、フォント サイズの変更や太字の適用などの直接書式設定を適用します。 家 タブをクリックし、 セルの書式設定 ダイアログ (Ctrl+1）。

個々のクエリが個別に更新される場合、このワークブック レベルのタイムスタンプが古くなる可能性があることに注意することが重要です。クリックしたときのみ更新されます すべて更新 で データ タブ、またはこのセルに関連付けられた特定のクエリが実行されたとき。この制限に対処するために、次のセクションでは、Power Query でテーブル レベルのタイムスタンプを作成する方法を示します。

独立した Power Query テーブル更新タイムスタンプの挿入

非表示列フェイルセーフの使用

レポート レベルのタイムスタンプは個々のテーブルの更新によってバイパスされる可能性があるため、個々のクエリに対してより詳細なフェールセーフを設定すると役立ちます。

この問題を修正するために、別の参照クエリを作成したり、読み込んだデータセットの末尾にタイムスタンプを追加したりするなど、他の方法を試したくなるかもしれません。ただし、参照クエリは元のクエリに関連付けられていますが、独立したタイムスタンプは提供されません。また、テーブルの最後にタイムスタンプを追加すると、データを並べ替えたり、動的配列数式を使用してデータから情報を取得したりするときに Excel が混乱するだけです。

これらの理由から、カスタム列を介してタイムスタンプをデータに直接構築することが最も安全なアプローチです。

1. Excel で、 データ タブをクリックして クエリと接続。

2. サイドバーでメインクエリを右クリックし、 編集。

3. Power Query エディターで、 列の追加 > カスタム列。

4. 新しい列に名前を付けます クエリの最終更新日 次の内容を カスタム列の式 分野： = DateTime.LocalNow()。

5. クリックすると わかりました、新しい クエリの最終更新日 列がテーブルに追加されます。タイムスタンプはすべての行に表示されますが、Power Query は行ごとにではなく、更新ごとに 1 回評価します。をクリックします。 ABC123 列ヘッダーのアイコンをクリックして選択します 日付/時刻。

6. の上半分をクリックすると、 閉じてロード のボタン 家 タブをクリックすると、ワークブック内の既存の場所に更新されたクエリが表示されます。

現状では、テーブルは各行に日付と時刻が繰り返されており、乱雑に見えます。そのため、問題を改善するには、ソース列を非表示にしたまま、テーブルのデータを参照するリンク セルを作成する必要があります。

1. 時間を秒単位でより具体的にしたい場合は、 カスタムフォーマット で セルの書式設定 ダイアログ (Ctrl+1）。

2. クエリ出力のすぐ右側の列の幅を縮小します。 単一のバッファ列を作成する。

3. 次の列のセルに次のように入力します。 =を選択し、 ヘッダ そして 最初のデータセル あなたの クエリの最終更新日 列を編集して、両方のセル参照が数式に追加されるようにします。

4. 押すと 入力の場合、データはこれら 2 つのセルで重複していますが、日付/時刻セルの書式設定が失われる可能性があります。したがって、テーブルから日時セルを選択し、 フォーマットペインタ 独立した複製セルに書式設定を複製します。

5. 最後に、ヘッダーを右クリックします。 クエリの最終更新日 列をクリックして 隠れる。

手動書式設定 (境界線の追加やテキストの中央揃えなど) を適用すると、次の場合に更新される、専門的なテーブル固有の更新タイムスタンプが得られます。

1. クリックすると データ > すべて更新。

2. テーブルを右クリックし、 リフレッシュ。

3. クエリは、設定したスケジュールされた間隔に基づいて自動的に更新されます。

この「乗客」列アプローチを使用すると、クエリの最後に成功した更新とワークブック全体のデータ更新の両方でタイムスタンプが確実に同期されます。テーブルが更新されると列も更新され、列が更新されるとクエリの最終更新日のセルに即座に反映されます。

「最終更新」タイムスタンプを自動化すると、手作業なしで Excel レポートの透明性と信頼性が維持されます。小さな変更で、シートをきれいに保ちながら使いやすさが大幅に向上します。このプロセスで M 言語を使用していることに気づきましたか?これは Power Query の背後にある強力なエンジンであり、基本を知ってしまえば驚くほど親しみやすいものです。標準の数式ではできない方法でワークブックを自動化できる他の M 言語スニペットを少し時間をかけて調べてください。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。