エクセルでA,Bデータの組み合わせを全て取得したい時、どうする？
Excel PowerQuery初心者の操作方法・作成方法の動画です。
二つの表を組み合わせて結果を全て取得、固定のカスタム列を追加して作成してみました。
youtu.be
https://youtu.be/0B3aSCOKd2I
目次
00:00 0.あいさつ やりたいこと
00:45 1.データの準備シートにテーブルを二つ準備する
01:09 2.仕掛け、トリック1として、カスタム列に固定の値を追加
01:27 2.1 作成した表にパワークエリーで接続する
03:04 2.2 カスタム列を追加する
05:43 3.テーブルを結合します
07:51 3.1 表示する(出力する)フィールドを選択します
11:34 4.タネあかし、仕掛けの説明
12:27 4.1 普通につなげて、販売明細を作ります。
15:14 4.2 つなげるデータを間違えてみる
18:13 5.よくある質問 組み合わせの問題をやってみる
#ms365 #エクセル #パワークエリ
#Excel #PowerQuery
#組み合わせ #展開 #カスタム列
#クエリー #テーブル結合 #組み合わせ全て
#初心者 #作り方
0.あいさつ
少し、面白そうだったので、チャレンジしてみてハマった話。
( 隙あれば自分語り 自己満足かよ・・・)
知恵袋の質問
detail.chiebukuro.yahoo.co.jp
で
PowerQuery（パワークエリ）に関する質問になります。
宜しくお願い致します。テーブルＡ
山形店
弘前店
仙台店テーブルＢ
商品Ａ / 150円 / 10個
商品Ｂ / 200円 / 20個
商品Ｃ / 300円 / 30個
上記２つのテーブルがあったとし、作成したいのが
作成したいテーブル
山形店 / 商品Ａ / 150円 / 10個
山形店 / 商品Ｂ / 200円 / 20個
山形店 / 商品Ｃ / 300円 / 30個
弘前店 / 商品Ａ / 150円 / 10個
弘前店 / 商品Ｂ / 200円 / 20個
弘前店 / 商品Ｃ / 300円 / 30個
仙台店 / 商品Ａ / 150円 / 10個
仙台店 / 商品Ｂ / 200円 / 20個
仙台店 / 商品Ｃ / 300円 / 30個を作成したいのですが可能でしょうか？
回答にあった
＞テーブル1にカスタム列の追加でテーブル2を参照して それを展開すれば
＞可能です。
が、気になったので、やってみた。
1.データの準備シートにテーブルを二つ準備する 00:45
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=00m45s
テーブルＡ
店名
山形店
弘前店
仙台店
テーブルＢ
商品名 / 単価 / 数量
商品Ａ / 150円 / 10個
商品Ｂ / 200円 / 20個
商品Ｃ / 300円 / 30個
勝手にフィールド名をプラスしたのは内緒です。
って、言ってるじゃん。
2.仕掛け、トリック1として、カスタム列に固定の値を追加 01:09
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=01m09s
左右の結合で使いたいので、
カスタム列に普通はやらない、
固定の値・同じ値
1を追加する追加する。
(計算列・演算フィールドなんだけど、計算しない固定値の1をセットする)
2.1 作成した表にパワークエリーで接続する
作り方、いろいろあると思いますが、
Excel上のデータを表にして、
パワークエリーのテーブルを作成します。
※表＝テーブルだから、なんて説明してんだが・・・
データ メニューから
データの取得 (左上のアイコン)
その他のデータソース (から)
テーブル または 範囲 (から)
を選択して、接続する
2.2 カスタム列を追加する 03:04
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=03m04s
列の追加
カスタム列
を選択して(選択すると)
式の入力画面が表示されます。式のフィールドなので計算列・演算フィールドってよく言ってます。
(私だけの方言かもしれないけど、計算列・演算フィールドって言ってます、言い間違ったらごめん)
ここで、
ID = 1
と固定の値にする。※なぜだ・・・
2.3 テーブル・接続の名前を変更して閉じる
名前が テーブル1 とか なっているので、
わかりやすい 接続名に変更します。
(と、いいつつ、テーブルA,Bと付けました)
２つの接続をまず、作ります。
(※ここまでで、疲れたよ・・・続きは明日だね。と、心の声と視聴者の声を無視して)
3.テーブルを結合します 05:43
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=05m43s
つなげたいデータがそろったので、
データ メニューから
データの取得 (左上のアイコン)
クエリの結合
マージ
を選択します。
テーブルA
のカスタム列 ID(固定の値1)
と
テーブルB
のカスタム列 ID(これも固定の値1)
を
左外部 (左側1件と右n件の結合？イメージかな※と言っても、今回は細工でn×nにするんだけど)
で、結合します。
テーブルBが展開になっていることを確認します。
↑デフォルトが展開だと思いますが、確認する
3.1 表示する(出力する)フィールドを選択します 07:51
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=07m51s
おっと、その前に、表示する出力するフィールドを選択します。
お疲れ様です。
これで、
それっぽくできたかなぁ。
アレンジして使ってみてください。
では、またぁ。
4.タネあかし、仕掛けの説明 11:34
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=11m34s
別にトリック、タネ、があるわけじゃないんだけど、
社員マスター
担当者ID 社員名 所属グループID 備考
1 相川 1 1課リーダー
2 伊藤 1
3 宇野 1
4 遠藤 2 遊軍巡回セールス担当
販売データ
明細ID 日付 販売者 品名 単価 数量 備考
1 2月25日 2 リンゴ 90 10
2 2月25日 1 みかん 50 150 さすがリーダー
3 2月25日 1 バナナ 30 50 と備考欄で
4 2月26日 1 リンゴ 100 200 ゴマすってみた
5 2月27日 3 みかん 55 15
6 2月27日 4 バナナ 33 20
と、ありがちな、データを用意しました。
4.1 普通につなげて、販売明細を作ります。 12:27
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=12m27s
担当者別に販売データの表が見たかったので、
社員マスターの担当者ID
と
販売データの販売者(売った人のID)
をつなげて、表を作ります。
だるいな、パワークエリにする方法は、
知ってるので(※ほかのわかりやすい動画見るので)、
仕組みだけ教えてよ
↑なんて、声を無視して(いつもの準備不足なだけなんだけど)
4.2 つなげるデータを間違えてみる 15:14
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=15m14s
通常運転はできたので、
次は、つなげるデータを間違えてみます。
社員マスターの担当者ID ← を間違えて、
所属グループID で結合すると・・
と
販売データの販売者(売った人のID)
あら不思議、データが増えた・・・？
↑このミスを応用した感じかなぁ。
5.よくある質問 組み合わせの問題をやってみる 18:13
https://www.youtube.com/watch?v=0B3aSCOKd2I&t=18m13s
毒を食らえば皿まで、、、
ここまで、ひっぱったので、
最後に(※ホントに最後です)
組み合わせを全て出力したい・・・なんて、質問があると思います
データ1
A
B
C
データ2
あ
い
う
これを
A-あ
A-い
A-う
B-あ
B-い
B-う
C-あ
C-い
C-う
とか応用できると思います。
パワークエリ作成の参考となれば幸いです。。。
(SQLとかクエリとか、結合・展開、、奥が深いですね・・・)
※下記、冒頭の動画と一緒です。本文が長くなっているので、
末尾にも目次と動画のリンクを置きました
Excel パワークエリでAとBの組み合わせ 全て を展開する方法 カスタム列を追加してテーブルを結合してみた PowerQuery初心者の操作方法 - YouTube
目次
00:00 0.あいさつ やりたいこと
00:45 1.データの準備シートにテーブルを二つ準備する
01:09 2.仕掛け、トリック1として、カスタム列に固定の値を追加
01:27 2.1 作成した表にパワークエリーで接続する
03:04 2.2 カスタム列を追加する
05:43 3.テーブルを結合します
07:51 3.1 表示する(出力する)フィールドを選択します
11:34 4.タネあかし、仕掛けの説明
12:27 4.1 普通につなげて、販売明細を作ります。
15:14 4.2 つなげるデータを間違えてみる
18:13 5.よくある質問 組み合わせの問題をやってみる
