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