Google検索

Office2019 Excel2019

Excel2019でAccessのデータをPower Queryで取り込むと更新も簡単

投稿日:2019年7月20日 更新日:

AccessのデータベースをExcelに取り込むと、データベースの分析などはExcelの機能を使って行うことができます。Excel2019では、外部データの取り込みにはPower Query(パワークエリ)が起動するようになっています。Power Query(パワークエリ)でインポートすると、データの更新などは従来の方法より簡単に操作できます。

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

Excel2019の[データ]タブの[データの取得]

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

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

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

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

Power Query の概要 - Excel

Power Query を使用すると、データソースを検索し、接続を作成し、必要に応じてデータを図形(列を削除したり、データ型を変更したり、テーブルを結合したりするなど) を行うことができます。データを整形したら、結果を共有したり、クエリを使用してレポートを作成したりすることができます。(上記ページより)

Excel2019とExcel2016の[データ]タブの違い

Excel2019(永続ライセンス)では、[データ]タブの内容が変わりました。

Excel2019の[データ]タブ

以下は、Excel2016(永続ライセンス)の[データ]タブです。

Excel2016の[データ]タブの[Accessデータベース]

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

AccessのテーブルやクエリのデータをExcelに取り込む

Accessで作成されたテーブルやクエリのデータをExcelのワークシートに取り込む方法です。 Accessのデータを接続しないで取り込む方 ...

続きを見る

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

Excel2016の[データ]タブの[新しいクエリ]

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

Excel2013にPower Queryをインストールして[POWER QUERY]タブを表示

Excel2013にPower QueryアドインをMicrosoftのダウンロードセンターのページからインストールしてみました。OSは、W ...

続きを見る

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

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

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

Microsoft Access データベースから

Microsoft Access データベースからデータをインポートします。

[データ]タブの[データの取得]-[データベースから]-[Microsoft Accessデータベースから]

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

[接続しています]メッセージウィンドウ

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

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

ここでは、[T_売上_0801]を選択します。

[ナビゲーター]ウィンドウ

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

[ナビゲーター]ウィンドウの[表示オプション]

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

[ナビゲーター]ウィンドウの[複数のアイテムの選択]

データベースの読み込み

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

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

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

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

[ナビゲーター]ウィンドウの[読み込み]ボタン

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

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

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

[データのインポート]ダイアログボックス

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

Excelに読み込んだデータ

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

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

テーブルの便利機能(テーブルに変換して効率アップ)

Excelのテーブルはとても便利なものです。 通常の表をテーブルに変換することによって、データベースとしての機能が自動的に設定され、管理や分 ...

続きを見る

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

テーブル間でリレーションシップを設定してピボットテーブルを作成

Excel2013以降は、テーブル間でリレーションシップを設定して1つのレポートを作成できるようになっています。 以下の記事では、[Powe ...

続きを見る

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

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

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

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

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

  • すべて更新[Ctrl]+[Alt]+[F5]
  • 更新[Alt]+[F5]

[データ]タブの[すべて更新」から[更新]をクリック

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

[テーブルデザイン」タブの[更新]ボタン

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

更新されたExcelのデータ

取り込みデータの変更

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

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

クエリと接続

ブック内のすべてのクエリと接続を表示して管理します。

接続は外部データソースのリンクです。クエリを使用して、複数のソースからデータを読み込み、形成し、結合します。

[データ]タブの[クエリと接続]グループにある[クエリと接続]

ウィンドウの右側に[クエリと接続]作業ウィンドウを表示されます。

[クエリと接続]作業ウィンドウ

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

[Power Query エディター]が表示されます。

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

Power Query エディター

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

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

クエリで右クリックしたときのショートカットメニュー

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

[クエリ]タブの[編集]ボタン

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

[Microsoft Excelのセキュリティに関する通知]ウィンドウ

ファイルパスの変更

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

[Power Query エディター]の[クエリの設定]にある[ソース]

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

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

[Microsoft Access データベース]ウィンドウ

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

[データの取り込み]ダイアログボックス

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

[Microsoft Access データベース]ウィンドウ

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

[Power Query エディター]の[閉じて読み込む]ボタン

別のテーブルに変更

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

[Power Query エディター]の[クエリの設定]にある[ナビゲーション]

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

[ナビゲーション]ウィンドウ

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

[Power Query エディター]の[閉じて読み込む]ボタン

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

更新されたExcelのデータ

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

クエリで右クリックした時のショートカットメニュー

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

クエリの削除

〇〇を削除しますか?このクエリを削除すると、このクエリによって読み込まれたデータを更新できなくなります。

[クエリの削除]ウィンドウ

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

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

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

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

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

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

[Excelのオプション]の[データ」タブ

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

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

[データ」タブの[データの取得]- [従来のウィザード」の[Accessから(レガシ)]

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

AccessのテーブルやクエリのデータをExcelに取り込む

Accessで作成されたテーブルやクエリのデータをExcelのワークシートに取り込む方法です。 Accessのデータを接続しないで取り込む方 ...

続きを見る

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

Excel2019でテキストファイルを読み込む(Power Queryとテキストファイルウィザード)

テキストファイルをExcelに取り込む方法は、外部データとして読み込む方法と、Excelで開く方法がありますが、Excel2019では外部デ ...

続きを見る

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

Webページからデータをインポート(Power Queryで2つのテーブルの行を連結)

Excelでは、HTML形式のデータを取り込むことができます。社内イントラネットのデータや、一般的なWebページのデータなどを取り込むことが ...

続きを見る

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

Power Queryでデータベースに整形(マトリックス表をテーブルに)

Power Query(パワークエリ)は、Excel2016とExcel2019には、標準でインストールされています。Excel2010とE ...

続きを見る

先頭行に複数の同じ項目名がある表をPower Queryで分析可能なテーブルに変換

Excel2016から標準で装備されているPower Query(パワークエリ)を使用すると、効率よくデータを加工することができます。 Ex ...

続きを見る

1列の複数行で1レコードになっている表をPower Queryで複数列のテーブルに変換

1列の複数行に1レコードのデータが並んでいる表も、Power Query(パワークエリ)エディターを使って、複数列のテーブルに整形することが ...

続きを見る

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

Profile

執筆者/はま

おすすめ記事と広告

-Office2019 Excel2019

Copyright © 2001-2019 初心者のためのOffice講座 All Rights Reserved.

Copyright© 初心者のためのOffice講座 , 2019 AllRights Reserved Powered by AFFINGER4.