Excel クロステーブル抽出、Microsoft Query の全機能

テーブル間でのデータの抽出 多くのパートナーの最初の反応は、VLOOKUP や INDEX SMALL IF 式などの関数です。実際、複数列のデータを抽出する場合は、長い間多くの人が片隅に置いてきた Microsoft Query が王様です。操作が簡単で、「1 対多」の問題を簡単に解決できるだけでなく、生成される結果テーブルはデータ ソースとの動的リンクを形成できます。データ ソースが変更されると、結果は動的に更新されます。

今日は、めったに使用されないが、奇跡的な効果をもたらす関数を共有したいと思います。「1 対多」の問題を解決するのに役立つ Microsoft Query 2 つのテーブルのデータ抽出、または 1 つのテーブルを使用して別のテーブルを照合して特定のデータを生成する問題を解決します。

次の図に示すように、同じワークブック内に 2 つのワークシートがあり、「部門人事情報テーブル」には各部門の従業員と対応する上司の名前がリストされ、「地方売上データ テーブル」は各従業員が担当する複数の州と、対応する州の 3 か月の売上データをリストします。次に、名前列に基づいて 2 つのテーブルを 1 つのテーブルに要約する必要があります。

元のテーブル

必要な結果

Microsoft Query の使用方法?

STEP 01 Microsoft Query を有効にしてデータをロードする

(1) 新しいワークブックを作成し、 をクリックします。 [データ]タブの[外部データの取得]グループで、[他のソースから]ドロップダウン メニューから[Microsoftから]を選択します。

[データ ソースの選択] ウィンドウの [データベース] オプションの下にある [Excel ファイル] をクリックし、[[クエリ ウィザード] を使用して

[ワークブックの選択]ウィンドウの右側にあるディレクトリでデータ ソースの場所を見つけ、そのファイルを見つけます。

(2) 「データ ソースには表示可能なテーブルが含まれていません。」というウィンドウが表示される場合がありますが、無視してください。

下の左側にある [クエリ ウィザード] ウィンドウに入り、下の [オプション] ボタンをクリックして、右側の [テーブル オプション] ウィンドウを開きます。

このようにして、[クエリ ウィザード] ウィンドウにデータ ソース内のワークシートが表示されます。これは、Excel が呼び出しているためです。独自のワークシート「システム テーブル」を確認すると、それが表示されます。

次に、2 つのワークシートを選択し、中央の「>」ボタンをクリックして、左側の「使用可能なテーブルと列」を右側の「結果の列をクエリ」に追加して、「次へ」をクリックします。

別のウィンドウがポップアップ表示され、「テーブルをリンクできないため、「クエリ ウィザード」を続行できません。クエリが進行中です。あなたはマイクロソフトにいるはずです 手動でリンクするには、クエリ内のテーブル間でフィールドをドラッグします。 「心配しないで、[OK] をクリックしてください。

ステップ 02 必要に応じてデータを一致させます

この時点で、Microsoft Query ウィンドウに入ります。上部は EXCEL に似たメニュー バーで、中央は追加した 2 つのテーブルと対応するフィールドを表示するテーブル領域です。データ領域以下は 2 つのテーブルの結果を融合したものです。

現時点では、データ領域の結果が乱雑です。理由は、テーブルを追加していないためです。 2 つのテーブルとの関係。2 つのテーブルは名前列で区切られています。1 つは対応しています。

(1) 左側の「部門人事情報テーブル」の「名前」をマウスで選択し、右側のテーブルの「地方売上データテーブル」の「名前」までドラッグして放します。ハツカネズミ。このとき、２つのテーブルの「名前」フィールドの間に、両端に小さなノードを持つ接続線が表示されます。以下のデータ領域は即時に更新されます。

(2) 同名の列が2つあるので、どちらかの列を選択し、メニューバーの[レコード]の「列の削除」をクリックします。

#ステップ 03 結果データを Excel ワークシートに返します

最後にあとは結果をEXCELに返すだけです。

(1) メニューバーの「SQL」の左側のボタンをクリックし、データをExcelに戻します。

(2) EXCELで[データのインポート]ウィンドウが表示されるので、「表」として表示することを選択し、既存のワークシートに配置します。

返される結果は次のとおりです:

これらの簡単な 3 つのステップで、必要なデータのマッチングが完了しました。新しいデータシートを生成しました。

特別な喜び

Microsoft Query によって生成されたデータはスーパー テーブルであり、ピボット テーブルやピボット チャートを直接作成することもできることがわかりました。

同時に、このテーブルはデータ ソースに動的にリンクされます。たとえば、元のデータを変更する場合は、[保存] をクリックして閉じます。

返された結果を右クリックして更新します。

データは同期されます。

適用条件

この方法を使用する場合、データソースの標準化が確実に行われる必要があることに注意してください。ワークシートにはデータ ソースに関係のないデータを含めることはできず、テーブルの最初の行は列ヘッダーである必要があります。動的リンクを実装する場合、ワークブックとワークシートの名前と場所は変更できません。

どうですか、学びましたか? PQ よりも単純ですか、それとも関数よりも単純ですか?

関連する学習の推奨事項:

Excel チュートリアル

以上がExcel クロステーブル抽出、Microsoft Query の全機能の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。