2019年2月11日月曜日

Excelで簡単テーブル結合

  • このエントリーをはてなブックマークに追加

Excelでテーブル結合

Excelでデータを結合する関数に VLOOKUP がありますが、使っていないと直ぐに忘れてしまうし、複数の列で結合するのは、ちょっと面倒です。しかし、Office365、若しくは、Office2016以降であれば[データ]タブの[データの取得と変換]を使えば簡単にテーブル結合ができます。

Power Query を使えば、Excelをリレーショナルデータベースのように簡単にテーブル結合(JOIN)ができます。

では、さっそく、[データの取得と変換](Power Query)を使い方を説明していきます。

使用する表の説明

果物を注文した個数と、注文日の果物価格からなる2つの表です。果物の仕入れ金額は、日によって異なります。

果物の注文個数
注文日 品名 個数
2019/2/1 リンゴ 5
2019/2/1 イチゴ 6
2019/2/2 リンゴ 3
2019/2/2 イチゴ 1
2019/2/2 バナナ 10
2019/2/3 バナナ 5
2019/2/3 イチゴ 7

注文日の果物価格
注文日 品名 価格
2019/2/1 リンゴ 100
2019/2/1 バナナ 50
2019/2/1 イチゴ 200
2019/2/2 リンゴ 100
2019/2/2 バナナ 60
2019/2/2 イチゴ 250
2019/2/3 リンゴ 120
2019/2/3 バナナ 70
2019/2/3 イチゴ 230

上記の表から、下表を作成していきます。

テーブル結合した結果
注文日 品名 個数 価格
2019/2/1 リンゴ 5 100
2019/2/1 イチゴ 6 200
2019/2/2 リンゴ 3 100
2019/2/2 イチゴ 1 250
2019/2/2 バナナ 10 60
2019/2/3 バナナ 5 70
2019/2/3 イチゴ 7 230

データの取得と変換

[データの取得と変換](Power Query)では、Excel, CSV, XML形式の表(テーブル)を読み込むことができます。

データの取得と変換(Power Query)

果物の注文個数」の表を読み込む

今回は、[テーブルまたは範囲から]を使って表を読み込みます。上記の「果物の注文個数」の表をコピーして、Excelに貼り付けてください。

① [データ]タブの[テーブルまたは範囲から]をクリック

テーブルまたは範囲から

② [テーブルの作成] で表全体(A1:C8)を選択
③ [先頭行をテーブルの見出しとして使用する(M)] をチェック

テーブルの作成

④ [OK]をクリック

[Power Query エディター]が起動されます。

Power Query エディター

Power Query エディターでは、データ形式を変更することもできます。
読み込んだ 注文日 の形式は、日付/時刻 となっていますので日付に変更します。

注文日の左側にあるカレンダーアイコンをクリック
⑥ 形式一覧から 日付 を選択

日付形式への変換

⑦ [クエリの設定]で[名前]を注文個数とします(下図①)
⑧ [閉じて読み込む] をクリック(下図②)

クエリの設定

7行のデータが読み込まれます。

クエリの接続

「注文日の果物価格」の表を読み込む


注文日の果物価格」の表をコピーして、Excelに貼り付けて「果物の注文個数」の表と同じように読み込みます。

クエリの名前は「果物価格」とします。

果物価格テーブルの読み込み

2つのテーブルを結合する

2つの表を読み込んだので、結合していきます。

① [クエリと接続] にある 注文個数 のクエリを右クリック
② [結合] をクリック

テーブルの結合

上段に注文個数のテーブルが読み込まれた状態で[マージ]の画面が表示されます。

結合テーブルの選択

③ 下段のテーブルに果物価格を選択

結合テーブルの選択

結合するキーを選択します。注文日品名の2項目を結合キーとします。

注文個数注文日 をクリック
注文個数品名Ctrlキー を押しながらクリック
果物価格注文日をクリック
果物価格品名Ctrlキー を押しながらクリック


テーブル結合項目の選択

複数の連結キーがある場合は、Ctrlキーを押しながら列名をクリックします。

注文日は1、品名には2 と表示されます。

結合の種類

Power Queryでの結合の種類には、いくつかありますが、よく使う左外部と内部について説明します。

Power Query の結合の種類

左外部(最初の行すべて、および2番目の行のうち一致するもの)
[左外部](右外部)結合は、リレーショナルデータベースでの外部結合(OUTER JOIN)です。

最初の行というのは、上記例では、注文個数、2番目の行が果物価格になり、注文個数注文日品名に一致する果物価格の列が追加されます。

[右外部]を選択すると、果物価格の表に一致する注文個数の列が追加されます。

内部(一致する行のみ)
[内部]結合は、リレーショナルデータベースでの内部結合(INNER JOIN)です。
上記例では、注文個数果物価格注文品名が一致する行のみ抽出されます。

⑧ [結合の種類]で[左外部]を選択
⑨ [OK]をクリック

[Power Query エディター]が起動します。

Power Query でテーブル結合

結合した結果に追加する列を選択


注文個数注文日品名個数の右に 注文個数が追加され、各行にはTableと表示されています。注文個数テーブルから必要な列(価格)を選択するために、注文個数の右にある展開アイコン ( 展開 )をクリックします。

マージされたテーブルの列名を指定する

⑩ [すべての列を選択] をクリックして、すべての列をオフにします
価格 を選択
⑫ [OK] をクリック

マージされたテーブルの列名を選択

一番右の列が、選択した 果物価格テーブルの価格になります。

クエリでのテーブル結合結果

⑬ [閉じて読み込む] をクリック

これで、2つのテーブルで注文日品名が一致する価格の列がマージされたシートが作成され完成です。

マージ(結合)結果

まとめ

[データの取得と変換](Power Query)は、データを加工する上で、テーブル結合ができる強力なツールです。10万行ぐらいのデータでも簡単に扱えますので、是非、使い方を覚えてください。


コメントを投稿

アクセス上位(過去30日間)