タグ別アーカイブ: マージ

【豆知識】どこの行が変わったかを調べる方法

大量のデータを扱っていると、2つの表を比べて「一体、どこの行が変わったのか?」を調べる時がたまにありますよね?

1万行を超すデータだと、関数を使ってもとても面倒な作業です

実はPower Queryの「クエリのマージ」において、結合種類を「完全結合(両方の行すべて)」に指定した上で、「条件列」を組み合わせると、簡単にできます

使用データと行いたいこと

下の図のように、「元データ」と「修正後」の2つの表があったとします

元データと修正後の間で「金額」が変わった行を「OK」「×」で判定して、新たな列に出力します

この「金額」が変わったという判定に当たっては、「ID」「客先」の組み合わせも考慮します

ですので、判定すべき個所は次の画像のようになります

今回のポイント

通常、クエリのマージを行った場合、結合の種類は次の画像の方法を使用するのが一般的だと思います

この場合は、2つのクエリの結合関係は次の画像のようになります

ところが、マージ方法を「完全外部」とすると次の画像のようになります

このマージ方法の場合、「片方の円しか無いものはどう出力されるか?」が今回の大きなポイントです!

では、それぞれの表からクエリ名が「元データ」「修正後」という2つのクエリが作成してあることを前提として、本格的な解説を始めます

2つのクエリのマージ

今回、2つのクエリをマージする際に気を付けることは、次の画像(列名の右横に番号が採番されていることに注目)のように照合列を複数指定することです

でないと、行の列の中で「客先」が変わっていても「判定」されません

照合列を複数指定する時は、Ctrlキーを押しながら選択を行ってください

条件列の追加

前述のクエリのマージを、展開する際には2つのクエリの違いが分かるように「元の列名のプレフィックス」にチェックを入れておきます

そして、マージしたクエリを展開すると次の画像のようになります

ちょっと上の画像だと見づらいので、1つのクエリの列だけ表示したのが次の画像です

「元データ」のクエリにしかない行は、プレフィックスが「修正後」の列では全て「null/空欄」となります

逆に「修正後」のクエリにしかない行は、プレフィックスが無い列が「null」になります

今回のポイントでも前述しましたが、金額が変わった行とは「金額自体」が変わった行と、「ID」や「客先」などの「行の情報」が変わった行の2つのケースがあります

「金額の列」はプレフィックスが「無し」と「有り」の2つがありますが、「行の情報」が変わった場合はどちらかが「null」になります

つまり、条件列を挿入して「金額」を比べれば、金額変更の2のケースが網羅できます

ですので、次のように条件列を指定します

まず条件設定です

上の条件が満たされた場合は「OK」を出力します

条件が満たされない場合には「×」を出力します

条件列を指定してエクセルシートに出力すると、次の画像のようになります

IDに注目して、「×」を見ていくと変わった行が一目瞭然です

<まとめ>

 今回は、マージの結合にて「完全結合」の仕組みを活用して2つの表から「変化した行」を判定する方法を解説しました

 Power Queryでは「元の列名のプレフィックス」を指定すれば、マージしたク エリを展開した際に「どのクエリ」の分なのかが列名で分かるのも便利です

 今回はクイズやパズルのような内容でしたが、Power Queryの便利さに触れるにはとても良い内容だったと思います

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


にほんブログ村

複数データの組み合わせ/クエリのマージ~中級編1回目~

今回から、中級編をはじめます

初級編では、データを取得してエクセルシートに読み込むだけでした

中級編では、取得したデータを編集・加工していきます

このデータ編集・加工手法を身に付ければ、様々な箇所から集めた大量のデータを関数やVBAを使わないでも、サクッと一括処理ができるようになります!

次回から2回は、関数やVBAを使用した場合には作成に手間がかかりそうな表を サクッと2つの表を組み合わせるだけで作成する方法を紹介します

今回は、次の回から主に使用する技術の概要だけ紹介しておきます

1.クエリのマージ

イメージとしては下の図のようなことを行います

関数で言えば、VLOOKUP関数も同じような事ができますが、このPower Queryのマージには、VLOOKUP関数で処理する時と比べて次のようなメリットがあります

・他の表から抽出する項目が多く、データ自体が大量な場合

 ⇒ワンクリックで処理できる

・VLOOKUP関数の場合は参照する値の位置が左にないと使用できなかった

 ⇒位置は関係なく処理できる

ちなみに、Power Queryにて実際にマージを行う画面は、以下の画面になります

2.計算処理

Power Query上では、2つの表を組み合わせた上での計算処理が、とても簡単に行えます

計算処理がもし±X÷を行う四則演算であれば、対象となる列を指定するだけで計算処理が行えます

SUMIFやSUMIFSなどの、関数であれば条件設定が伴う、少しややこしい計算も、下の図のような画面にて、直感的に計算処理が行えるようになっています

では次回から2回に分けて、1.クエリのマージ、2.計算処理を中心に解説を行って行きます!

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


にほんブログ村

クエリのマージ/項目追加~中級編2回目~

中級編2回目は前回紹介したマージの技術を、実際にサンプルを使用して解説を行います

下の図にある商品台帳売上台帳が解説に使用するデータです

こちらの表2つを組み合わせる(マージする)ことにより、2つの表に分かれていた項目を組み合わせて活用できるようにします

下の図のように、売上台帳に商品台帳の「商品名」と「販売単価」を追加します

今回のポイント

A.キー列を設定します

2つの表を組み合わせるためのキー列を指定します

B.組みあせる項目、組み合わせ方を選択します

下記の画面で組み合わせる項目と組み合わせ方を指定します

上の図に”集計”とありますが、集計については次回に解説します

では早速、下記にサンプルファイルをダウンロードして演習をすすめていきましょう!

1.クエリ作成

商品台帳と売上台帳ともに、次の①~③の処理を行い、クエリを作成しておきます

①Power Query Editorを開く

データタブから”テーブルまたは範囲から”を指定して、Power Query Editorを開きます

範囲を指定する際、自動的に題名の部分(下の図であれば商品台帳)がテーブルの見出しとして範囲に入ってしまうケースがあります

こちらについては、題名部分を除いて範囲を指定し直してください

②Power Query Editor上の処理

Power Query Editorの画面右にて、クエリに名前をつけましょう!

マージ処理をする時にクエリー名が分かり易くつけてあると、処理がとても進めやすいです

③読込処理

ホームタブの右上の「閉じて読み込む」ボタンの右に▼マークがあるので、こちらをクリックしてください

すると、次の画面のように「閉じて次に読込む」ボタンが出てきますので、こちらをクリックします

そうすればデータのインポート画面が開くので、読込先を指定できるようになります

下の図のようにデータのインポート画面が開いたら、読込先は”接続の作成のみ”を指定してください

ここまで行えば、クエリが画面右に出来ています!

上の①~③の処理を通じて2つのクエリを作成したら、2.のマージ作業に移ります

2.マージ作業

2つのクエリをマージする場合、事前に2つの方法が選択できます

ⅰ)既存のクエリにマージする方法

ⅱ)新規にマージ用のクエリを作成する方法

今回はⅱ)で行います

➀売上台帳クエリを参照

新規にクエリを作成するといっても、全く一からクエリを作成するわけではありません

売上台帳クエリを基にして新規のクエリを作成します

まずは、売上台帳クエリ上にカーソルを置いて右クリックし、更に”参照”をクリックします

「参照」をクリックしたら、Power Query Editorが開きます!

➁クエリのマージをクリック

新しいクエリのクエリ名は、次の画面のように変更しておきましょう!

③クエリのマージ

次に画面上にある、「クエリのマージ」ボタンをクリックします

④マージするテーブルを指定

マージをクリックした後に、新規に次のGIFにあるように「マージ」方法を指定する画面が立ち上がります

この画面では マージするテーブルとキー列を次のように指定します

テーブル⇒商品台帳、キー列⇒商品コード

⑤マージする項目、方法を指定

④が終了すると下記の画像のように、商品台帳の内容が一番右の列に追加されます

次に、商品台帳の右横にあるマーク(2つの矢印の組み合わせたマーク)をクリックし、実際に組み合わせる項目と組み合わせ方法を指定します

上のGIFのように、マークをクリックした段階では全てにチェックが入っています

ここでは商品名と販売単価はチェックを入れたままにしましょう!

「元の列名をプレフィックスとして使用します」についてはチェックを外しましょう

チェックを入れたままだと下の図のように、余計な名前(商品台帳)が商品名と販売単価の左横に表示されるようになってしまいます

⑥読込処理

今回は”閉じて読み込む”を指定し、接続の作成のみでなく、実際にシートに読み込みます

これでマージ処理は完成です!

<まとめ>

はじめてのマージ処理はいかがだっだでしょうか?

2つの表を組み合わせるにあたり、VLOOKUP関数を2つの列 (商品名、販売単価 で作成する)よりずっと簡単だったのではないでしょうか?

もし、”しまった、他の項目も追加が必要だった”ということになっても、関数を追加したりしなくても、簡単に追加が行えます

具体的な方法については次回、詳細に解説します

次回は単に項目の組み合わせだけでなく、計算処理も行います!

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

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

にほんブログ村
</a

クエリのマージ/計算処理~中級編3回目~

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

前回は2つのクエリーをマージして、項目が追加されたクエリーを作成しました

今回は、追加した項目を更に深堀活用し、本来であれば関数やVBAの使用が必要な処理をサクッと行う方法を習得しましょう!

解説は前回と同じサンプルファイルを使用して行います

今回使用するデータ

今回の解説では、商品台帳と売上台帳の2つの表から、下の表を作成します

前回、売上台帳に商品台帳の「商品名」と「販売単価」項目を追加しましたが、今回は、更に「販売個数の合計」と「売上金額」も算出した表にします

<今回のポイント>

A.条件付き集計

今回の集計処理では、同じ商品コードに紐付く「複数の値」を集計します

エクセル関数でいえば、SUMIFS関数と同じ処理です

しかも、SUMIFS関数よりももっとシンプルな方法で集計します

前回、クエリの項目を追加する時に使用した画面と同じ画面にて、単純にマージ方法として「集計」を選ぶだけです!

B.一括計算

1.で商品コード毎に販売個数を集計したら、販売単価を乗じます

乗じる処理は、乗じる列を選択して計算方法を指定するだけです

2つのポイントを確認したところで、早速、詳細な解説に入りましょう!

1.条件付き集計

前回と同じ様に、クエリーを参照して新たなクエリーを作成するところから処理を開始します

➀参照するクエリーの指定

今回は商品台帳を参照元にします

商品台帳クエリー上で右クリックし、下の画像の様に「参照」が選択できるようになったら、参照のテキストをクリックしましょう!

➁クエリのマージ

Power Query Editorが開いたら、ホームタブの上にある「クエリのマージ」をクリックしましょう!

下の画像のようなマージ画面が開いたら、前回と同じく商品コードをキーにして売上台帳クエリをマージしましょう

③条件付き集計処理

事前に新たに作成したクエリの名前は変えておきましょう

クエリの名前を変更したら、マージした売上台帳の項目を、下の画像の黄色の箇所をクリックしましょう!

次に開いたマージ方法を詳細に設定する画面では、前回と違い「展開」ではなく「集計」をチェックしましょう

その際に”元の列名を・・・”はチェックを外しておきましょう

そして、本来であれば上の画像の一番下「Σ販売個数の合計」をチェックして指定してください、というべきところです

試しに、「Σ販売個数の合計」の一つ上、「Σ商品コードの合計」をチェックして指定してみてください

すると次の画像のように表示されます

もちろん、商品コードを合計しても意味はありません

もし、指定を間違えてしまった時の対処は、Power Queryであればどうするのか?ということを解説するため、敢えて本来とは違うものを指定してもらいました

間違いを回復するには、下の画像の「適用したステップ欄」の黄色の箇所をクリックしてください

すると前回行ったチェック指定がやり直せるようになります

Power Queryでは、上の画像のように「やり直し」を行う場合には「適用したステップ欄」を使用します

詳細は後日、解説を行います

それでは「やり直し」を行い、下の画像のように本来の”販売個数の合計”が表示されたところで2.一括計算の解説に移ります

2.一括計算

計算したいのはⅰ)販売単価とⅱ)販売個数の合計を乗じた売上金額です

➀計算対象の列、ⅰ)ⅱ)を選択する

Ctrlキーを押しながら、販売単価の列と販売個数の合計の列を選択しましょう

➁画面上のタブから「列の追加」を選択

画面上のタブから列の追加をクリックすると、右側に計算機のようなマークがついた「標準」が見えます

次に「標準」マーク下の▼をクリックしましょう!

③計算方法の指定

マークをクリックした後に表示された「各種計算方法」から乗算を選択してクリックしましょう!

すると上のGIFのように、一番右に新たな列が追加されます

この新たな列には、計算結果が出力されています

列の名称は”乗算”から”売上金額”に変更しておきましょう

④読込処理

列の追加タブからホームタブにもどり、エクセルシートへの読込処理を、下の画像の「閉じて読み込む」ボタンを通じて行いましょう

販売個数と売上金額が出力されているのを確認したら処理は終了です

<まとめ>

 今回は商品台帳と売上台帳をマージして、商品コード毎に「a:販売個数」を集計処理しました

 そして、集計した「a:販売個数」と「b:販売単価」とをPower Query Editor上で更に乗じて「c:売上金額」まで計算した表を作成しました!

 エクセル関数のVLOOKUP関数とSUMIF関数を使用しても同じことが行えますが、もっと楽に表を作成できているのではないでしょうか?

関数と違って、カーソルで対象を指定しながら直観的に行えるのもとてもうれしいです

 それに、もし、表の作成の仕方を間違えたとしても、間違いを起こした場所からやり直せます

さて、計算の元になる表の値が変わった場合にはどうすればいいのでしょうか?

関数で表を作成していれば、自動計算されています

 Power Queryの場合には、関数を使用した場合と違って、1つ余分な処理を行う必要があります

 この場合は下の画像の中にある「すべて更新」をクリックします

そうすれば、表の中で再計算が行われます

今回の解説は以上です

次回は、今回と同じように複数のクエリを組み合わせて扱う方法を解説します

但し、今回のように商品台帳と売上台帳といった、違う内容のクエリを組み合わせるのではありません

同じ項目から構成される複数のクエリを追加して、組み合わせる方法の解説になります

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

にほんブログ村