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

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

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

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

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

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

１．クエリのマージ

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

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

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

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

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

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

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

２．計算処理

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

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

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

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

にほんブログ村

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

前回は項目が違う２つのクエリをマージ（組み合わせ）ました。今回は、今回はあるクエリに、項目が同じだけど別のクエリを追加する方法を紹介します

加えて、実践的なPower Queryの活用方法として、ピポットテーブルとの連携方法も紹介します

ピボットテーブルは便利なのですが、取得元のデータに追加があった場合には少し面倒ですよね

例えば、売上の集計データを毎月報告するケースなどです

1月のデータに、2月のデータを足し、取得範囲を修正する・・・という処理が毎月続きます

今回紹介するクエリの追加機能とピボットテーブルを連携させれば、そんな悩みは一発で解決します！

解説する内容の概要ですが、下の図のようにサンプルファイルにある1月の売上データに、今回の解説の中心である「追加クエリ」機能を通じ、2月の売上を追加してピボットテーブルを新たに作成します

そして今回の次（中級編5日目）の回では、更に”追加クエリ”機能を通じて、ピボットテーブル側では特にデータ範囲の追加は行わないまま、3月の売上データを追加します

＜今回のポイント＞

　今回の解説の中心となる”追加クエリ”機能自体は特段難しいところはありません　

　ただし、追加クエリを行うパターンが以下の①②の２つに分かれる点は意識しておいてください

①と②の違いは、２つのクエリから新たなクエリーを生み出すか、そうでないかの違いになります

では、サンプルファイルをダウンロードしたら、具体的な解説に入りましょう

中級4回目_サンプルファイル

1 ファイル 159.27 KB

ダウンロード

１．各シートからクエリーを作成

サンプルファイルの各シートが”各月”のデータになっています

画面上のデータタブから”テーブルまは範囲から”をクリックして、各シートのデータからクエリを作成しましょう

クエリ作成時は以下の２つを指定してください

➀読込先　⇒接続専用

➁クエリ名　⇒シート名

２．クエリの追加_1回目

１．が終了したらシートの右側が、以下の画面のような状態になっているはずです

もし、”クエリと接続”が出ていない場合には、画面上のデータタブから”クエリと接続”をクリックしましょう

画面の状態が確認できたら、以下➀⁻③の処理を通じてクエリ_2020年1月にクエリ_2020年2月を追加します

前述の＜今回のポイント＞の①のパターン（⇒新しいクエリを作成）で追加処理を行います

➀クエリ_2020年1月上で右クリック⇒クエリ追加

クエリ上で右クリックをすると”追加”が選択できます

➁追加するテーブルを選択 ⇒2 020年2月を選択

➀の処理を通じて追加画面が立ち上がったら、追加するテーブルを選択してOKボタンを押します

③読込先をピボットテーブルを指定する

➁の後に、Power Queryエディターが立ち上がります

画面の右のプロパティでクエリーの名前を”売上データ”に変更したら、今度は画面右上で”閉じて次に読み込む”を選択します

次に”データのインポート”画面が立ち上がりますので、”ピボットテーブルレポート”を選択してください

ピボットテーブルの読込先を既存のシートにするか、新規のシートにするかを選択できますが、ここでは新規のシートを選択します

２つの選択処理を行い、OKボタンを押すと、1月と2月のデータが含まれたピボットテーブルが作成されます

３．ピボットテーブルの設定

2020年1月と2月の両方の月のデータが含まれていることが分かるように設定しましょう！

行ボックスに出荷日、値ボックスに金額をドラックします

行ボックスに”出荷日”とあわせて”月”も出現したら、出荷日は行ボックスから外しましょう（エクセルのバージョンによって多少、違いがある可能性があります）

そうしたら、以下のようなピボットテーブルになっているはずです

＜まとめ＞

　今回はあるクエリに別のクエリを追加する処理を行いました。この時には２つ方法があり、新たなクエリを作成する方法と、そうでない方法があるのですが、今回は前者で行いました。

　また、クエリをPower Queryエディターから読み込む際に、読込先にピボットテーブルを選択しました

　このようにPower Queryとピボットテーブルを連携させることで、ピボットテーブルの元データを自由に操作できるようになります

　次回はPower Queryを通じて、ピボットテーブルに新たなデータを加えます

　ピボットテーブルについては「ピボットテーブルの役割を変える」と題し、別途シリーズで記事を書いています

ぜひ、そちらもご参考にしてください

　　　　　　　　⇒ピボットテーブル記事

にほんブログ村

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

前回は1月と2月のデータを追加して新たなクエリを作成しました

そして、新たに作成したクエリからピボットテーブルを作成しました

今回は前回作成したクエリに、更に3月のデータを追加します

この時、ピボットテーブルのデータ範囲変更や、新たに追加された範囲を反映するための更新処理を、Power Queryとの連携により省けるようにします！

もし、これまでPower Queryについては全く知識が無い、という読者の方がいらっしゃいましたら、ぜひ、このページを事前にご確認ください

エクセルシート上の表を⇒テーブル⇒クエリ⇒シート上に読込、の順に変換することさえ分かれば、この後の内容も十分理解していけるはずです！

今回のポイント

前回は以下の図で➀の方法でクエリの追加（1月＋2月）を行いました

今回は①の方法で作成したクエリを編集して、②の方法で3月のデータを追加します

仮に➀の方法で追加処理を行うと、既存のピボットテーブルの範囲外に新たに新たなクエリ（1月＋2月＋3月）ができてしまい、ピボットテーブルの範囲指定や更新処理を行う必要がでてきてしまいます

では、本格的な解説をはじめます

１．売上データクエリ（1月＋2月）上で編集をクリック

Power Queryエディターを開きます

２．Power Editorエディター上で”クエリーの追加”をクリック

Power Queryエディター上で”クエリの追加”をクリックします

３．追加画面で追加するデータを指定する

追加するデータをクリックしたらOKボタンを押します

追加が終了したら”閉じて読み込む”をクリックしましょう！

すでにピボットテーブルに3月のデータが自動的に追加されています

＜まとめ＞

　Power Queryとピボットテーブルを今回の解説のように、事前に連携させておけば、ピボットテーブルの範囲指定の追加や変更はとてもスムースになります。

　毎月、定期的に売上などのレポートを作成している人にはとても便利な技術なのではないでしょうか？

　ピボットテーブルについては、毎回の報告を楽にするために「ピボットテーブルの役割を変える」と題して別途、記事を書いています

興味があるという方がいたらそちらの記事もぜひご覧になってください

　中級編1~5回では複数のクエリを扱う方法を解説しました。次回からはまた違うテーマを解説していきます

にほんブログ村