Microsoft Query を使用して外部データを取り出す
Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 その他…表示数を減らす
Microsoft Query を使用して、外部ソースからデータを取得できます。 Microsoft Query を使用して会社のデータベースやファイルからデータを取得することで、Excel で分析するデータを再入力する必要はありません。 また、新しい情報でデータベースが更新されるたびに、元のソース データベースから Excel のレポートと概要を自動的に更新することもできます。
__
Microsoft Query の詳細
Microsoft Query を使用すると、外部データ ソースに接続し、それらの外部ソースからデータを選択し、そのデータをワークシートにインポートし、必要に応じてデータを更新して、ワークシートのデータを外部ソースのデータと同期させることができます。
アクセスできるデータベースの種類 Microsoft Office Access、Microsoft SQL Server、Microsoft SQL Server OLAP サービスなど、さまざまな種類のデータベースからデータを取得できます。 Excel ブックやテキスト ファイルからデータを取得することもできます。
Microsoft Office には、次のデータ ソースからデータを取得するために使用できるドライバーが用意されています。
Microsoft SQL Server Analysis Services (OLAP プロバイダー)
Microsoft Office Access
dBASE
Microsoft FoxPro
Microsoft Office Excel
Oracle
パラドックス
テキスト ファイル データベース
他の製造元の ODBC ドライバーやデータ ソース ドライバーを使用して、ここに記載されていないデータ ソース (他の種類の OLAP データベースなど) から情報を取得することもできます。 ここに記載されていない ODBC ドライバーまたはデータ ソース ドライバーのインストールについては、データベースのドキュメントをチェックするか、データベース ベンダーにお問い合わせください。
データベースからのデータの選択 データベースからデータを取得するには、外部データベースに格納されているデータについて質問するクエリを作成します。 たとえば、データが Access データベースに格納されている場合は、リージョン別の特定の製品の売上数値を知りたい場合があります。 データの一部を取得するには、分析する製品と地域のデータのみを選択します。
Microsoft Query では、目的のデータの列を選択し、そのデータのみを Excel にインポートできます。
1 回の操作でワークシートを更新する Excel ブックに外部データが含まれると、データベースが変更されるたびに、データを 更新 して分析を更新できます。サマリー レポートやグラフを再作成する必要はありません。 たとえば、月次売上の概要を作成し、新しい売上の数値が入ったときに毎月更新できます。
Microsoft Query でデータ ソースを使用する方法 特定のデータベースのデータ ソースを設定した後は、すべての接続情報を再入力することなく、クエリを作成して、そのデータベースからデータを選択して取得する場合にいつでも使用できます。 Microsoft Query では、データ ソースを使用して外部データベースに接続し、使用可能なデータを表示します。 クエリを作成してデータを Excel に返すと、データを更新するときにデータベースに再接続できるように、クエリとデータ ソースの両方の情報が Excel ブックに提供されます。
Microsoft Query を使用したデータのインポート Microsoft Query を使用して Excel に外部データをインポートするには、次の基本的な手順に従います。それぞれの基本的な手順については、次のセクションで詳しく説明します。
__
データ ソースに接続する
データ ソースとは データ ソースは、Excel と Microsoft Query が外部データベースに接続できるようにする、格納されている情報のセットです。 Microsoft Query を使用してデータ ソースを設定する場合は、データ ソースに名前を付け、データベースまたはサーバーの名前と場所、データベースの種類、ログオンとパスワードの情報を指定します。 この情報には、OBDC ドライバーまたはデータ ソース ドライバーの名前も含まれます。これは、特定の種類のデータベースへの接続を行うプログラムです。
Microsoft Query を使用してデータ ソースを設定するには:
[ データ ] タブの [ 外部データの取得 ] グループで、[ その他のソースから] をクリックし、[ Microsoft クエリから] をクリックします。
次のいずれかの操作を行います。
データベース、テキスト ファイル、または Excel ブックのデータ ソースを指定するには、[ データベース ] タブをクリックします。
OLAP キューブ データ ソースを指定するには、[ OLAP キューブ ] タブをクリックします。 このタブは、Excel から Microsoft Query を実行した場合にのみ使用できます。
[新しいデータ ソース] ><ダブルクリックします。
-または-
[ 新しいデータ ソース ><] をクリックし、[OK] をクリック します 。
[ 新しいデータ ソースの作成 ] ダイアログ ボックスが表示されます。
手順 1 で、データ ソースを識別する名前を入力します。
手順 2 で、データ ソースとして使用しているデータベースの種類のドライバーをクリックします。
注:
* アクセスする外部データベースが、Microsoft Query でインストールされている ODBC ドライバーでサポートされていない場合は、データベースの製造元など、サード パーティベンダーから Microsoft Office 互換 ODBC ドライバーを取得してインストールする必要があります。 インストール手順については、データベース ベンダーにお問い合わせください。
* OLAP データベースでは、ODBC ドライバーは必要ありません。 Microsoft Query をインストールすると、Microsoft SQL Server Analysis Servicesを使用して作成されたデータベースのドライバーがインストールされます。 他の OLAP データベースに接続するには、データ ソース ドライバーとクライアント ソフトウェアをインストールする必要があります。6. [ 接続] をクリックし、データ ソースへの接続に必要な情報を指定します。 データベース、Excel ブック、テキスト ファイルの場合、指定する情報は、選択したデータ ソースの種類によって異なります。 ログオン名、パスワード、使用しているデータベースのバージョン、データベースの場所、またはデータベースの種類に固有のその他の情報を指定するように求められる場合があります。
重要:
* 大文字、小文字、数字、記号を組み合わせた強力なパスワードを使用してください。 これらの文字を混在させていないパスワードは強固とはいえません。 たとえば、Y6dh!et5 は安全性の高いパスワードです。 House27 は推測されやすいパスワードです。 パスワードは、8 文字以上にする必要があります。 理想は 14 文字以上のパス フレーズです。
* パスワードは忘れないようにしてください。 パスワードを忘れた場合、Microsoft でパスワードを回復することはできません。 紙に記録したパスワードを、保護する必要がある情報から離れた安全な場所に保管してください。7. 必要な情報を入力したら、[ OK] または [完了] をクリックして [ 新しいデータ ソースの作成 ] ダイアログ ボックスに戻ります。
データベースにテーブルがあり、特定のテーブルをクエリ ウィザードに自動的に表示する場合は、手順 4 のボックスをクリックし、目的のテーブルをクリックします。
データ ソースを使用するときにログオン名とパスワードを入力しない場合は、[データ ソース定義のチェックボックスで [ユーザー ID とパスワードを保存する] ボックスを選択します。 保存されたパスワードは暗号化されません。 [チェック] ボックスが使用できない場合は、データベース管理者に問い合わせて、このオプションを使用できるかどうかを判断してください。
セキュリティ メモ: データ ソースに接続するときは、ログオン情報を保存しないでください。 この情報はプレーン テキストとして格納されることがあり、悪意のあるユーザーが情報にアクセスしてデータ ソースのセキュリティを侵害する可能性があります。
これらの手順を完了すると、データ ソースの名前が [データ ソースの 選択 ] ダイアログ ボックスに表示されます。
__
クエリ ウィザードを使用してクエリを定義する
ほとんどのクエリでクエリ ウィザードを使用する クエリ ウィザードを使用すると、データベース内のさまざまなテーブルとフィールドのデータを簡単に選択してまとめられます。 クエリ ウィザードを使用して、含めるテーブルとフィールドを選択できます。 内部結合 (2 つのテーブルの行が同じフィールド値に基づいて結合されることを指定するクエリ操作) は、ウィザードが 1 つのテーブルの主キー フィールドと 2 番目のテーブルで同じ名前のフィールドを認識すると自動的に作成されます。
ウィザードを使用して結果セットを並べ替えたり、簡単なフィルター処理を実行したりすることもできます。 ウィザードの最後の手順では、データを Excel に返すか、Microsoft Query でクエリをさらに絞り込むことができます。 クエリを作成した後は、Excel または Microsoft Query で実行できます。
クエリ ウィザードを起動するには、次の手順を実行します。
[ データ ] タブの [ 外部データの取得 ] グループで、[ その他のソースから] をクリックし、[ Microsoft クエリから] をクリックします。
[データ ソースの選択] ダイアログ ボックスで、[クエリ ウィザードを使用してクエリを作成/編集チェック ボックスが選択されていることを確認します。
使用するデータ ソースをダブルクリックします。
-または-
使用するデータ ソースをクリックし、[OK] をクリック します 。
他の種類のクエリに対して Microsoft Query で直接作業する クエリ ウィザードで許可されているよりも複雑なクエリを作成する場合は、Microsoft クエリで直接作業できます。 Microsoft Query を使用すると、クエリ ウィザードで作成を開始するクエリを表示および変更したり、ウィザードを使用せずに新しいクエリを作成したりできます。 次の操作を行うクエリを作成する場合は、Microsoft Query で直接作業します。
フィールドから特定のデータを選択する 大規模なデータベースでは、フィールド内のデータの一部を選択し、不要なデータを省略することができます。 たとえば、多くの製品の情報を含むフィールド内の 2 つの製品のデータが必要な場合は、 抽出条件 を使用して、必要な 2 つの製品のデータのみを選択できます。
クエリを実行するたびに異なる条件に基づいてデータを取得する 同じ外部データ内の複数の領域に対して同じ Excel レポートまたはサマリーを作成する必要がある場合 (リージョンごとに個別の売上レポートなど) 、 パラメーター クエリを作成できます。 パラメーター クエリを実行すると、クエリがレコードを選択するときに条件として使用する値の入力を求められます。 たとえば、パラメーター クエリを使用して特定のリージョンを入力するように求め、このクエリを再利用して各地域の売上レポートを作成できます。
さまざまな方法でデータを結合する クエリ ウィザードで作成される内部結合は、クエリの作成に使用される最も一般的な結合の種類です。 ただし、別の種類の結合を使用したい場合があります。 たとえば、製品販売情報のテーブルと顧客情報のテーブルがある場合、内部結合 (クエリ ウィザードによって作成された型) により、購入していない顧客の顧客レコードが取得されなくなります。 Microsoft Query を使用すると、これらのテーブルを結合して、すべての顧客レコードを取得し、購入した顧客の売上データを取得できます。
Microsoft Query を開始するには、次の手順に従います。
[ データ ] タブの [ 外部データの取得 ] グループで、[ その他のソースから] をクリックし、[ Microsoft クエリから] をクリックします。
[データ ソースの選択] ダイアログ ボックスで、[クエリ ウィザードを使用してクエリを作成/編集チェック] ボックスがオフになっていることを確認します。
使用するデータ ソースをダブルクリックします。
-または-
使用するデータ ソースをクリックし、[OK] をクリック します 。
クエリの再利用と共有 クエリ ウィザードと Microsoft クエリの両方で、変更、再利用、共有できる .dqy ファイルとしてクエリを保存できます。 Excel で .dqy ファイルを直接開くことができます。これにより、ユーザーまたは他のユーザーが同じクエリから追加の外部データ範囲を作成できます。
Excel から保存したクエリを開くには:
[ データ ] タブの [ 外部データの取得 ] グループで、[ その他のソースから] をクリックし、[ Microsoft クエリから] をクリックします。 [ データ ソースの選択 ] ダイアログ ボックスが表示されます。
[ データ ソースの選択 ] ダイアログ ボックスで、[ クエリ ] タブをクリックします。
開く保存済みのクエリをダブルクリックします。 クエリは Microsoft Query に表示されます。
保存したクエリを開き、Microsoft Query が既に開いている場合は、[Microsoft クエリ ファイル ] メニューをクリックし、[ 開く] をクリックします。
.dqy ファイルをダブルクリックすると、Excel が開き、クエリが実行され、結果が新しいワークシートに挿入されます。
外部データに基づく Excel の概要またはレポートを共有する場合は、外部データ範囲を含むブックを他のユーザーに付与するか、 テンプレートを作成できます。 テンプレートを使用すると、外部データを保存せずに概要またはレポートを保存して、ファイルを小さくすることができます。 外部データは、ユーザーがレポート テンプレートを開いたときに取得されます。
__
Excel でデータを操作する
クエリ ウィザードまたは Microsoft クエリでクエリを作成した後、データを Excel ワークシートに返すことができます。 その後、データは、書式設定および更新できる 外部データ範囲 または ピボットテーブル レポート になります。
取得したデータの書式設定 Excel では、グラフや自動小計などのツールを使用して、Microsoft Query によって取得されたデータを表示および集計できます。 データの書式を設定できます。外部データを更新すると、書式設定は保持されます。 フィールド名の代わりに独自の列ラベルを使用し、行番号を自動的に追加できます。
Excel では、範囲の末尾に入力した新しいデータを、前の行と一致するように自動的に書式設定できます。 また、前の行で繰り返された数式を自動的にコピーし、追加の行に拡張することもできます。
注: 範囲内の新しい行に拡張するには、前の 5 行のうち少なくとも 3 行に書式と数式を表示する必要があります。
このオプションは、いつでもオン (またはもう一度オフ) できます。
- [ファイル]、[オプション]、[詳細設定] の順にクリックします。
[ Excel 2007: [Microsoft Office] ボタン をクリックし 、[Excel のオプション] をクリックし、[ 詳細 ] カテゴリをクリックします。
- [編集オプション] セクションで、[データ範囲の形式と数式の拡張] チェック選択します。 データ範囲の自動書式設定をもう一度オフにするには、このチェック ボックスをオフにします。
< c0>外部データの更新</c0>。 外部データを更新すると、クエリを実行して、仕様に一致する新しいデータまたは変更されたデータを取得します。 クエリは、Microsoft Query と Excel の両方で更新できます。 Excel には、ブックを開くたびにデータを更新し、時間指定された間隔で自動的に更新するなど、クエリを更新するためのオプションがいくつか用意されています。 データの更新中に Excel で引き続き作業できます。また、データの更新中に状態をチェックすることもできます。 詳細については、 「 target=”_blank” title=”Excel での外部データ接続を更新する” rel=”noopener”>Excel での外部データ接続を更新する」を参照してください。
ページの先頭へ