Excelでデータ接続を自動的に更新し、ワークシートを再計算する方法はいくつかあります。これらのトリックは、Microsoft365のお客様向けのExcelのStockデータ型でより重要です。
通常、Excelは変更すると自動的に更新されます。セルの値。最近では、セルの値は変更されてもExcelがワークシートを更新しない場合があります。つまり、最新のExcelは、Microsoftがまだ完全に適応していない方法で変更されています。
理想的には、 Excelには、「n」秒または分ごとにワークシートを更新する全体的な設定があります。現状では、それを実現するための回避策が必要です。
データの更新または再計算を強制する必要がある場合、または慎重に。
ストックデータタイプ
ストックデータタイプを使用すると、自動的に更新する機能がより重要になります。ユーザーは、ワークシートで緯度を取得する必要があります。推定価格は自動的に設定されます。現在のプレビューリリースでは実行できません。素敵な自動「ティッカー」を使用する代わりに、「更新」をクリックして最新の価格を取得する必要があります。
StockとGeoのデータ型は好奇心旺盛な獣です。これらは外部ソースへのデータ接続ですが、Excelデータ接続としては表示されません。つまり、通常のデータ接続の場合のように、自動データ更新を設定することはできません。実際、StockまたはGeoデータ型の公開されたコントロールはありません。
NOW（）およびその他の揮発性関数
NOW（）関数は、Excelが常に最新の日時に更新されます。ワークシートを再計算します。しかし、それを実現するものが何もない場合、Now（）は値を変更しません。 ExcelでNow（）とワークシートの残りの部分を更新するには、いくつかの外部要因が必要です。言い換えれば、ワークシートを一瞥して、それが秒単位であることを知ることができるはずですが、Excelでは「箱から出して」それは不可能です。
VBAカスタム関数は、を使用して揮発性としてタグ付けすることもできます関数コードの次の行：
Application.Volatile
この行により、Excelがワークシートを更新/再計算するたびに関数が実行されます。
特に注意
念のため、ワークシートを自動的に更新したいですか？多くの古いExcelハンドは、Excelが適切に更新されていない状況を覚えているため、「ベルトとブレース」アプローチが好きです（少なくとも時々）。
コード
自動を強制する標準的な方法Excelの更新は、VBAコードの短いスニペットです。これが私たちが使用するものです。同じテーマには多くのバリエーションがあります。完全なコードは記事の下部にあります。
3つの関数があります。
RefreshAllDataConn
データ接続の実際の更新を行い（Workbooks（ThisWorkbook.Name）.RefreshAll）、最後に更新された時間を下部のステータスバーに表示する2つのオプションの行を追加しました。
特に注意したい場合は、行を追加して明示的に再計算を強制します。 ActiveSheet.Calculateまたは極端なApplication.CalculateFullのいずれか（これにより、大きなワークシートの速度が低下するため、慎重に使用してください）。
AutoRefresh
毎分RefreshAllDataConnサブを実行するか、設定した値を指定します。行Application.OnTimeNow + TimeValue（ “00:01:00″）、 “AutoRefresh”
Workbook_Open
ワークシートを開くと自動的に実行される組み込みのExcel関数。この場合、自動更新が開始されます。
データ接続の回避策
VBAアプローチの欠点は、.xlsmワークシート（マクロ対応のExcelワークシート）が必要なことです。セキュリティ上の懸念から、マクロ対応ファイルの共有で問題が発生する可能性があります。
PowerQuery / Get and Transformの登場により、ワークシートの再計算を強制する別の方法があります。これは回避策であり、完全ではありませんが、可能であり、マクロ対応のワークシートは必要ありません。
つまり、自動更新を使用してデータクエリを設定していることを確認してください。データ接続がない場合は、ワークシートに小さな接続を追加します。
自動更新クエリを実行すると、揮発性関数を含むワークシートも更新されます。
理想的には、StockおよびGeoデータ型も更新する必要があります。
Excelデータクエリには、Queryプロパティにいくつかの更新オプションがあります。それらのほとんどはデフォルトでオフになっています。
nnn分ごとに更新–デフォルトでは60分が推奨されます。
ファイルを開くときにデータを更新する
バックグラウンド更新を有効にする
すべて更新時にこの接続を更新する
自動更新の回避策は、小さくて実質的に重要でないものを作成することです。データの接続。次に、そのデータ接続を構成して、毎分または任意の時間に更新します。これにより、上記の揮発性関数を含むワークシートが強制的に更新されます。
この回避策の一部のバージョンでは、同じコンピューター上の小さなcsvファイルへのリンクが追加されています。ワークシートのテーブルからのデータ接続を使用しても同じ結果が得られます。
単一のセルを持つ小さなテーブルを作成します。セルには何でも含めることができますが、後で説明する理由から、NOW（）を含むセルを作成します。
テーブルを選択し、[データ]、[データ]の順に選択します。データを取得|他のソースから|テーブル/範囲から。正確なメニュー項目は、Excelのバージョンによって異なる場合があります。
クエリエディタが開いたら、閉じてロードするだけです。 。データ接続ペインにクエリが表示されます。
クエリを右クリックし、プロパティを選択して、必要な設定。
自分に合ったリフレッシュレートを設定します。
整理するために、ロードされたクエリ（左）でソーステーブル（右）を同じシートに移動します。ソーステーブルのセルでNOW（）を使用したため、ワークシートがいつ更新されたかを簡単に確認できます。
VBAコードの例
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
自動Excelワークシートリストまたは目次を作成する
休日を含む完全なExcelNetworkDays（）ソリューション&休暇