Excel2019でAccessのデータをPower Queryで取り込むと更新も楽になる

AccessのデータベースをExcelに取り込むと、データベースの分析などはExcelの機能を使って行うことができます。

Excel2019では、外部データの取り込みにはPower Query（パワークエリ）が起動するようになっています。Power Query（パワークエリ）でインポートすると、データの更新などは従来の方法より簡単に操作できます。

データの管理は、ExcelよりAccessで行ったほうが、データの正確性は保持できます。Accessでデータの正確性は保ちつつ、データの分析や表作成はExcelで行うという構築をしておくと業務が楽になります。

ここでは、Excel2019（永続ライセンス） バージョン 1906での解説です。従来の方法で取り込む設定についても解説しています。

参考Power Query（パワークエリ）については、以下の記事が参考になると思います。分かりやすく解説されています。Power Queryって何？って方は、ぜひお読みください。お勧めの記事です。

【初心者向け】Power Queryで元データを整形しよう（概要編）

以下は、Microsoftのページです。

Power Query の概要 - Excel

Excel2019とExcel2016の［データ］タブの違い

Excel2019（永続ライセンス）では、［データ］タブの内容が変わりました。

以下は、Excel2016（永続ライセンス）の［データ］タブです。

Excel2016の［Accessデータベース］から取り込む方法については、以下の記事で解説しています。

Excel2016でPower Queryを使用するには、［新しいクエリ］をクリックして［データベースから］をポイントして［Microsoft Access データベース］をクリックします。

参考Power Queryは、Excel2010とExcel2013にもアドインとしてインストールして、使用することができます。

［Microsoft Access データベースから］を選択してインポート

Excel2019の［Microsoft Accessデータベース］から取り込む方法です。

まず、接続するAccessデータベースは終了させておきます。環境によってはエラーが出ることがあります。

［データ］タブの［データの取得と変換］グループにある［データの取得］をクリックします。そして、［データベースから］をポイントして［Microsoft Accessデータベースから］をクリックします。

以下のように［接続しています］のメッセージが表示されます。

しばらくすると、［ナビゲーター］が表示されます。

取り込み可能なテーブルとクエリが表示されますので、左側のウィンドウで目的のデータベースを選択すると、右側にデータプレビューが表示されます。

ここでは、［T_売上_0801］を選択します。

参考もし、プレビューが表示されなければ、［表示オプション］をクリックして、［データプレビューを有効にします］をオンにします。

また、［複数のアイテムの選択］をオンにすると、複数のテーブルやクエリを選択できるようになります。

データベースの読み込み

AccessのデータベースをExcelに読み込むには、［読み込み」ボタンをクリックします。

［読み込み］ボタンの▼ボタンをクリックすると、［読み込み］と［読み込み先...］がありますが、［読み込み先...］を選択した場合は、［データのインポート］ダイアログボックスが表示されますので、表示形式と読み込み先を指定できます。

［読み込み］をクリックした場合は、既定どおり［新規ワークシート］に［テーブル］として読み込まれます。

ここでは、［読み込み先...］を選択します。

［データのインポート］ダイアログボックスが表示されます。

データベースをどのように表示するかと、出力先を選択することができます。

既定では［テーブル］と［新規ワークシート］が選択されていますが、ここでは［テーブル］と［既存のワークシート］を選択して［OK］ボタンをクリックします。

以下のようにAccessのテーブルをExcelのテーブルとして読み込むことができます。

参考Accessのデータを読み込む場合は、取り込む前にAccessで最適化を実行しておいた方が良さそうです。私の環境では、削除したり作成したオブジェクトが反映されていないことがありました。Accessでデータベースの最適化を実行することで解決できました。

Excelのテーブルにはとても便利な機能があります。

また、テーブルであればExcelでリレーションシップの設定ができます。

データの更新を反映させる

Accessのデータベースが更新された場合、Excelのテーブルにもすぐに反映させることができます。

接続するAccessデータベースは終了させておきます。環境によってはエラーが出ることがあります。

［データ］タブの［クエリと接続］グループにある［すべて更新」から［更新］をクリックします。

参考ブック内で複数の外部データとの接続がある場合、［すべて更新］をクリックすると、ブック内のすべての外部データの接続が更新されます。特定のデータのみ更新したい場合は、［更新］をクリックしてください。

ポップヒントで分かりますが、ショートカットキーは以下のとおりです。

• すべて更新［Ctrl］+［Alt］+［F5］
• 更新［Alt］+［F5］

または、［テーブルデザイン」タブの［外部のテーブルデータ］グループにある［更新］ボタンから操作します。

データの変更が反映されます。

参考更新中は、ステータスバーの左側に以下のように［バックグラウンドクエリを実行しています...］と表示されます。

取り込みデータの変更

取り込んだAccessのデータの保存場所が変更になったり、別のデータベースを参照しなければならなくなった場合も、Excelのデータを更新することができます。

［データ］タブの［クエリと接続］グループにある［クエリと接続］をクリックします。

ウィンドウの右側に［クエリと接続］作業ウィンドウが表示されます。

変更する必要があるクエリをダブルクリックします。

［Power Query エディター］が表示されます。

参考クエリの列や行を削除する場合もこのエディターで操作します。編集した後は、［ホーム］タブの［閉じて読み込む］ボタンをクリックします。

参考［Power Query エディター］を表示する方法は他にもあります。

［クエリと接続］の目的のクエリで右クリックして、ショートカットメニューから［編集］をクリックしてもいいですし、

［クエリ］タブの［編集］ボタンをクリックしてもいいです。

また、Excelを保存した後、再度、表示して［Power Query エディター］を起動しようとすると、［Microsoft Excelのセキュリティに関する通知］ウィンドウが表示されることがあります。［OK］ボタンをクリックすると、［Power Query エディター］が表示されます。

Power Queryエディターを起動するショートカットキーは、［Alt］+［F12］です。

ファイルパスの変更

パスが変更になった場合は、［Power Query エディター］の［クエリの設定］にある［適用したステップ」の［ソース］をダブルクリックします。

以下の［Microsoft Access データベース］ウィンドウが表示されます。

［ファイルバス］の［参照］ボタンをクリックします。

［データの取り込み］ダイアログボックスが表示されます。データベースを選択して［インポート］ボタンをクリックします。

［Microsoft Access データベース］ウィンドウで［ファイルパス］が変更されたことを確認して［OK］ボタンをクリックします。

［Power Query エディター］に戻りますので、［ホーム］タブの［閉じて読み込む］をクリックします。

別のテーブルに変更

［Power Query エディター］の［クエリの設定］にある［適用したステップ」の［ナビゲーション］をダブルクリックします。

［ナビゲーション］ウィンドウが表示されます。移動先のリソースを指定して、［OK］ボタンをクリックします。

データが変更されていることを確認して、［ホーム］タブの［閉じて読み込む］ボタンをクリックします。

接続されたデータベースが変更され、データも更新されます。

クエリの名前の変更や、削除などは、各クエリで右クリックしてショートカットメニューから選択して行うことができます。

ショートカットメニューから［削除］をクリックすると、［クエリの削除］メッセージウィンドウが表示されます。クエリを削除すると、データの更新はできなくなります。

おすすめPower Queryについて学習するなら、以下のテキストがお勧めです。

従来の方法でインポートするには

Power Queryを使用せずに従来の方法でインポートしたい場合は、設定が必要です。

［ファイル］タブをクリックして、［オプション］をクリックします。

［Excelのオプション］ダイアログボックスが表示されます。

［データ］タブの［レガシデータインポートウィザードの表示］にある［Accessから(レガシ)］のチェックボックスをオンにして［OK］ボタンをクリックします。

再起動の必要はありません。

［データ」タブの［データの取得と変換］グループにある［データの取得］をクリックします。

メニューの中から［従来のウィザード」をポイントして［Accessから(レガシ)］をクリックします。

［データファイルの選択］ダイアログボックスが表示されます。その後の操作については以下の記事で解説しています。

参考Excel2019でテキストファイルを読み込む方法については、以下の記事で解説しています。

PDFファイルのデータも取り込むことができます。

Webページのデータを読み込む方法も解説しています。

Power Queryを使用すると、データベースの整形も行うことができます。