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

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

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

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

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

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

＜今回のポイント＞

A．条件付き集計

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

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

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

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

B．一括計算

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

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

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

１．条件付き集計

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

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

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

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

➁クエリのマージ

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

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

③条件付き集計処理

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

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

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

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

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

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

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

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

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

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

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

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

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

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

２．一括計算

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

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

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

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

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

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

③計算方法の指定

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

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

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

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

④読込処理

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

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

＜まとめ＞

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

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

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

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

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

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

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

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

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

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

今回の解説は以上です

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

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

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

にほんブログ村