タグ別アーカイブ: 結合

(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている?

前回の続きで、パワークエリを使用してだけでなくについても、の変更もあわせて変更箇所を特定できるようにしたいと思います

ポイント

2つの表を結合するのは前回と一緒です

今回はピボット解除を行い、複合キーを作ったうえで結合を行います

使うデータ

Before、Afterの2つの表を用意するのですが、キー(りんご、みかん・・)を行単位で作成し、列は月単位で作成します

最後は2つの表でどこが変わったかを明確にできるようにクエリを作成します

ピボット解除&複合キー作成

BefoerとAfter、それぞれのクエリはピボット解除を行い、縦横並びを縦縦並びに変えます

その後、「列のマージ」を使用して、複合キーを作成します

この複合キーを使用して、BeforeとAfterの2つの表を結合(マージ)します

マージ処理

ポイントでも前述したように、2つの表を複合キーを使用してマージ処理します

マージ種類は通常でいけば、完全結合がよいです

「完全外部」の結合であればAfterの表にて追加・削除されたものが全て網羅できます

但し、こちらは用途にあわせて6つの結合種類を使いわければよいかと思います

各種調整

判定列の追加

マージ後は、条件列を使用して〇×を判定する列を追加することができます

複合キーの分解

マージ後は複合キーを分解して必要な情報だけ抜きだすこともできます

複合キーの分解は「抽出」の「区切り記号の後のテキスト」を使用し、複合キーから「月」を抜き出します

そうすると、BeforeとAfterの比較が行いやすくなります

<まとめ>

今回はマージを更に有効活用して、行だけでなく、変更があった列と値を特定できるようにしました

ポイントはピボット解除と複合キーの作成により、2つの表を比較しやすい形で結合することです

ぜひ有効活用してみてください

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

2つの表にてどの行が追加、削除されている?~結合の6種類~

2つの表の間でどの行が追加されているのか?削除されているのか?を適時確認・集計できるととても便利です

受注のキャンセルデータの管理やミスの発見など、2つの表の適時確認・集計の活用の場はかなり広いです

Power Query結合(マージ)には6種類が用意されていますので、それぞれを使い分けるとかなりの集計上手になれます!

アイコン

結合サンプル 67.09 KB 2 downloads

...

今回使用するデータ

今回は1から始まる連番で管理された2つの表を使用します

結合(マージ)

結合の種類は下の画像の赤色の箇所で選択・指定できます

左外部

左の表の全てと、右の表にて左の行と一致する行を結合します

左外部

右外部

右の表の全てと、左の表にて右の行と一致する行を結合します

右外部

完全外部

一致する一致しないに関わらず、2つの表に存在する行を網羅します

右の表で追加された行や削除された行が一目でわかるようになります

完全外部

内部

2つの表の間で一致する行のみ結合します

内部

左反

左の表から右の行と一致しないものを抽出します

右の表にて削除された行が一目でわかるようになります

左反

右反

右の表から左の表の行と一致しないものを抽出します

右の表で追加された行が一目でわかります

右反

<まとめ>

Power Queryの結合にて一つ問題になるのは、表現がわかりにくいことです

「最初の行」とは左にある表、「2番目の行」とは右にある表と置き換えればわかりやすいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

M言語に慣れる_9回目~複数シートをコード1行で結合~

【M言語は難しくない】今回は、空のクエリM関数を1つ入力するだけで複数シートを一気に結合できるようにします!今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます

 Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います

今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します

1つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます

これで、シート間のバケツリレーの作業からは抜け出すことができます!

但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します

目次

今回のポイント

今回使用するデータ

準備作業/空のクエリを作成

M関数の入力/Excel.CurrentWorkbook

読み込み処理

データの循環対策

シート追加テスト

<まとめ>

今回のポイント

今回はM関数を入力した後に、1点だけ注意点があります

それは、循環問題です

複数シートを1つにまとめた内容/クエリが、M関数に反映されてしまいます

ですので、クエリを更新すると読み込み行数が倍になります

対策として、適用したステップに1つのステップを追加します

今回使用するデータ

今回使用するデータには、シートが3つ含まれています

1シートが1か月分の出荷データになっています

各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります

次のファイルが今回、実際に使用するサンプルデータです

続きを読む M言語に慣れる_9回目~複数シートをコード1行で結合~

フォルダから複数ファイルデータを一括取得~初級講座4回目

こんにちは、Excellent仕事術ガッツ鶴岡です

前回はエクセルファイルからデータを取得しました

今回はフォルダにある複数ファイルから一括でデータを取得します。

フォルダ内データ一括取得

この機能はPower Queryの機能の中でも一押しの機能のひとつです

何故なら、複数ファイルに分散しているデータを一括で処理しようとしたら、通常はガッツのある人の手が必要となるからです。

その一括処理の内容はとても面倒くさく、ミスも起こり易いです

ところが、今回紹介するPower Queryのある機能を使うと、フォルダにファイルを入れておきさえすれば、全て一括で取得してくれるのです!

記事を最後まで読む時間が無い、という方はぜひこちら↓の動画をご覧ください

ちなみに、動画内で使用しているサンプルファイルは下からダウンロードできます

https://www.youtube.com/watch?v=LgjzjTSylcs

本題に入る前に、今回の解説に使用するデータを紹介します

今回の解説に使用するフォルダ(Data)の中には、3つファイルが入っています

アイコン

フォルダ用データ 22.26 KB 238 downloads

...

営業の部署 にて、営業担当者毎に販売内容をフォルダ内に入れていく想定です

ファイルに書き込まれている内容(シート:Data)は次のような項目です

ちなみにDataシート以外にもメモというシートがあります

では、本格的な内容の解説に入りたいと思います

解説は 「1.取得元のフォルダを指定する」と「2.ファイルの中で取得するシートを指定する」の2つに分けて行います

1.取得元のフォルダを指定する

今回もデータタブの操作から処理を開始します

➀フォルダから取得を指定します(下の図参照)

➁実際に使用するフォルダを指定します

今回の解説で使用するのは「Data」フォルダーです

③指定するフォルダ名を確認 ⇒「OK」を押すとフォルダの指定は終了です

フォルダの指定が終了した後は、1~3回目の解説では出てこなかった画面が登場します

2.ファイルの中で取得するシートを指定する

➀フォルダに入っているファイル名が表示されます

下の図の中に「結合」のボタンが見えますでしょうか?

一番下に4つのボタンが見えますが、一番左のボタンです

このボタンについては次の➁の解説で操作します

➁ボタン右横の▼マークを押し、「データの結合と変換」を押します

この時、フォルダ内のファイルが1つでも開いていると、データの結合と変換がうまくいかないので注意が必要です

「データの結合と変換」をクリックした後は、また更に新たな画面が開きます

③取得するシートを指定します

新たに開いた画面(下の図参照)の左側に、ファイルに含まれるシート名(Data、メモ)が表示されるので、Dataシートを選択します

Dataシートを選択したら、OKボタンを押します

そうすると、Power Queryエディターが開きます

④Power Queryエディター画面で読込処理を行う

Power Queryエディター画面には複数ファイルの情報が一括表示されます

まず、設定されるクエリの名前を「一括データ」に変更しましょう!

次にPower Queryエディター画面左上で「閉じて読み込む」ボタンをクリックします

これでフォルダ内のデータが一括で読み込まれます

ファイル別に分かれていた注文NOも1~6まで一括で表示されています

さて、ここからがこのフォルダ一括読込機能の最大の醍醐味です

フォルダ内にもう一つ、ファイルを追加します

アイコン

追加用1 9.66 KB 108 downloads

...

通常は、これまで行ってきた1、2の作業を行う必要がありそうですよね

ところが、ワンクリックで追加ファイルを一括で読み込むことができます

エクセルシートの右に”一括処理”という前回作成したクエリがあります

こちらのクエリの上にカーソルを置き、右クリックします

そうすると最新の情報に更新できるようになります

更新処理を行うと、フォルダ内に新規追加したファイル内の注文NO:78が下の図のようにシート上に反映されています

<まとめ>

この機能はこれまでのエクセル処理の常識を覆す内容です

今までは私もそうでしたが、フォルダ内にある複数ファイルのデータを一括で取得しようとしたらVBAのコードを書いていました

しかも、そのVBAコードはVBA初心者が書ける内容ではないです

2-②「データの結合と変換のクリック時」でも説明しましたが、フォルダ内のファイルが一つでも開いているとうまく行かない点だけは注意が必要です

後はPower Queryの画面操作に慣れてくれば、とても簡単な処理だと思います

ぜひ、職場で有効活用してみてください!

次回は読込先の変更について解説します!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村