タグ別アーカイブ: クエリ

Power Queryのステップ数を減らす3つの工夫

 Power Queryの便利な点の一つは、「適用したステップ欄」に操作履歴が自動記録される点です

 ただ、後々のクエリの操作性を考えると「ステップ数」は少ない方がいいですよね?

今回は「ステップ数」を減らす工夫を3つ紹介します!

「変更された型」のステップを減らす

 こちらは、過去の記事でも同じような事を書いていますので、ぜひそちらもご参照ください

 Power Queryでは列のデータ型とヘッダーの自動検出により、「ソース」ステップの次に、必ず「変更された型」のステップが追加されます

 後、途中でステップを追加した際、下のGIF画像のように自動的に「変更された型」が自動追加されることもあります

 一方で、変換タブにはきちんと下の画像のように「変更された型/列のデータ型とヘッダーの自動検出」のステップを追加する機能があります

ですので、変更された型ステップは「自動追加」する必要は必ずしもありません

 クエリを読み込む前、もしくは必要になった箇所に「手動追加」すれば変更された型ステップは減らすことができます

 変更された型ステップの自動追加を止める処理は、エディタ内の下の画像の箇所から行います

 ファイルタブ➡オプションと設定 とクリックすると「クエリのオプション」がクリックできるようになります

 クエリのオプションが開いたら、下の画像のように「型の検出」から3つの選択行えるようになっています

 一番下の「非構造化ソースの列の型とヘッダーを検出しない」にチェックを入れると「変更された型」ステップの自動追加は行われないようになります

同じ処理をまとめて行う

仮に、下の画像のようなデータがあったとします

このデータ内で次のように4つの処理を行ったとします

①フィルター:部門の列で「東京」のみを選択

②列の名前変更:部門➡東京

③フィルター:受注金額の列で「150万以上」のみを選択

④列の名前変更:受注金額➡150万以上

すると、次の画像のように4つのステップが①~④の処理に応じて追加されます

4つのステップが追加された結果、エディタ内は次の画像のようになっています

では、前述の①~④の処理の順番を次のように変えてみます

1.フィルター:①と③

2.列の名前の変更:②と④

すると、ステップは4つでなく2つとなります

 このように、同じ種類の処理をまとめて行えば、ステップ数は減らすことができます

列名の変更

 エディタ内で処理を行った際に、自動的に名前が変更、もしくは追加されている時があります

 上のGIF画像では、列の分割処理を行った際に「部門.1」と「部門.2」という名前が自動的についています

この場合、数式バー内では新たに追加された「列名」は赤字になっています

 では、この追加された「部門.1」「部門.2」の列名を下の画像のようにそれぞれ「」「」に変更してみます

 すると、下の画像のように「名前が変更された列」が適用したステップ欄に追加されています

 このステップについては、数式バーで直接、前述赤字 部分を変更することで減らすことができます

 数式バーで名前を直接・変更すれば、下の画像のように「位置によって分割された列」のステップの後のステップは消えています

<まとめ>

今回は、ステップを減らす為の工夫を3つ解説しました

 特に、最初に紹介した「変更された型」のステップを減らすについては、すぐに削減効果が出ると思います

 後に紹介した2つの削減工夫については、「効率的なクエリ作成」を意識していけば、自ずと「ステップ数の削減」につながるものです

今回の記事を機に、「効率的なクエリ作成」をぜひ意識してみてください

長文に最後までお付き合い頂き誠にありがとうございました

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


にほんブログ村

 

エディタからデータ入力してクエリ作成

 Power Queryというと、エクセルシート上に入力してあるデータをPower Queryエディタ(以降、エディタ)に読込んで使用するイメージが強いと思います

実は、エディタ上でデータ入力を行うこともできます

今回は、短い内容ですが「直接入力」する方法と「修正方法」を紹介します

データの直接入力

データを直接入力する作業は、ホームタブの次の画像の画面から行います

上の画像の「データの入力」をクリックすると次の画面が開きます

 通常のエクセルシートと違うのは、見出しの入力とデータの入力が明確に区別されているところです  

*下の画像の商品コードが見出し、下の10001がデータ

上の画像の右側にある「*マーク」をクリックすると新規に列が挿入されます

行の挿入についても同様です

 もし、行列のどちらかを削除したい場合には、該当する行列の上で右クリックすると次の画像が出て削除が行えます

入力が完了して、入力画面下のOKボタンを押せば、クエリが新たに作成されます

作成されたクエリは、通常のクエリと同じ様にエディタ画面左に表示されます

直接入力したデータの修正

 入力したデータを修正する際には、適用したステップの欄の「ソース」の右横にあるマークをクリックします

すると、下の画像のようにデータ修正画面が表示されます

<まとめ>

 今回は、エディタ画面から直接データ入力を行ってクエリを作成する方法を解説しました

使う機会は少ないとは思いますが、マージ用クエリとして新規に作成する場合で、データ入力量が少ない場合には有効なクエリ作成方法だと思います

ですので、覚えておいて損はないかと思います!

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

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

にほんブログ村

クエリの複製、参照、そしてコピーとの違い~

 クエリを右クリックしたときに出てくる「複製」と「参照」そして「コピー」、この3つは何が違うのか?と不思議に思ったことはありませんか?

 このブログでは、中級編/クエリのマージの解説の中で「参照」の方は登場しました

では、複製はどのような時に使用するのでしょうか?

そしてコピーは?

上の3つはどちらもある種「既存クエリ」のコピーです

但し、この3つには明確な違いがあります

 この3つの違いをきちんと理解しておくと「既存クエリ」の活用の仕方がかなり効率的になります!

 この「複製」と「参照」の違いを理解し易くするため、次の画像のデータから「元クエリ」*という名前のクエリを事前に作成します

 *コピーは別のクエリで解説します

元データ

 「元クエリ」はPower Queryエディタ(以降エディタ)にて、次の画像のように「トラック」でフィルターをかけています

元クエリ

ですから、適用したステップは次の画像のように3つになります

適用したステップ

ではこの「元クエリ」を元にして「複製」と「参照」の違いを解説します

コピーについては、前述のように別のクエリで解説します

複製

では「元クエリ」の上で右クリックして「複製」を行います

元クエリ

開いたエディタで「適用したステップ」を確認します

適用したステップ

適用したステップは3つあり、「元クエリ」と変わりません

 ですので、上の画像の最後のステップを修正して、フィルターを「トラック」から「軽自動車」に変えることもできます

行のフィルター

元のクエリを一部変えるだけで活用したい場合には「複製」するのが便利です

参照

では今度は、参照を行います

元クエリ

開いたエディタの適用したステップを確認します

適用したステップ

上の画像のように適用したステップには最初のステップだけです

つまり、データソースとして参照元の最終ステップを参照しています

上の図は参照したクエリですが、既に「トラック」でフィルターされています

後、数式バーにははっきり参照元が「元クエリ」となっています

既存のクエリをそのまま活用していきたい場合には「参照」が便利です

コピー

最後にコピーについて解説します

コピーについては、違うクエリで解説します

 上の画像の「売上集計」クエリは、「商品台帳クエリ」「売上台帳クエリ」などのクエリと依存関係(他のクエリをマージ)になっています

 他のクエリと依存関係になっているクエリをコピーして貼り付けると、依存関係にあるクエリも下の図のように一緒にコピーされます

依存関係にあるクエリがなければ、前述の複製と機能は同じです

<まとめ>

今回は、「複製」と「参照」そして「コピー」の違いについて解説しました

両方ともクエリをある種「コピー」するという点では一緒です

違うのはコピーされるステップです

複製 最後のステップまでコピー

参照 ⇒参照元の最終ステップをデータソースとして参照 

コピー⇒依存関係(マージされている)にあるクエリがあれば、そちらもコピー

 この3つの違いを意識しながら、既存クエリを活用する目的にあわせてうまく使い分けしましょう!

今回は以上です!

最後までこの記事をお読み下さり、誠にありがとうございました!

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

にほんブログ村

Power Queryって何~既存クエリのステップを一括で削除する方法

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

今回は、既に作成したクエリをステップ毎に分割する方法を解説します

 例えば、以下のように5つのステップ(最初のソース以外)があるクエリがあったとします

最後のステップで右クリックします

こちらで、「前のステップの抽出」をクリックします

すると次の画面になるので、新たなクエリ名を入力してOKボタンを押します

すると元のクエリは次の様になります

右クリックしたステップが残っています

一方、新しいクエリは次のようになります

右クリックしたステップより前のステップが残っています

では、新たに作成したクエリにて、途中のステップで「前のステップの抽出」を同じように行ったらどうなるでしょう?

更に新しく作成したクエリでは、上の画像の黄色の箇所の1つ前「変更された型」のみが抽出されています

今回は以上です

いつもご愛読頂きまことにありがとうございます

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

にほんブログ村

クエリのコピー・バックアップ・削除他~上級編16回

今回は、各種クエリに関する処理について解説します

解説する処理内容は全部で5つになります

1.クエリを別ファイルにコピー

2.クエリのバックアップ、復元

3.クエリの数が多い場合のグループ化

4.クエリの一括削除

5.依存関係

最後依存関係は前述の1.~4.全てに関わります

この回で解説する依存関係とは、データソースやクエリのマージ(結合)のことになります

 順序は逆になりますが、今回の事例として使用するファイルを解説する意味もありますので、5.依存関係から解説を行います

5.依存関係

今回使用するファイルは、過去の記事でも使用したファイルです

元データ

 この時には、商品台帳と売上台帳をマージして「売上金額クエリ」を作成し、売上金額を計算しました

では早速、「売上金額クエリ」の依存関係を見てみます

解説対象のクエリ

 まず「売上金額クエリ」の上で右クリックして、Power Queryエディタ(以降、エディタ)を開きます

クエリの編集

エディタが開いたら、表示タブをクリックします

クエリの依存関係

すると、上の画像右の「クエリの依存関係」をクリックできるようになります

下の画像が、「クエリの依存関係」をクリックした時に開く画面です

依存関係

上の図を見ると「売上金額クエリ」は①商品台帳クエリと②売上台帳クエリとマージしており、この2つのクエリに依存しているのが分かります

①と②についても、依存の状況は分かります

①商品台帳クエリ

このクエリは「e:\パワークエリ講座・・・」のフォルダにあるファイルからデータを取得しているのが分かります

依存関係

この 「e:\パワークエリ講座・・・」の上にカーソルを置くと更に詳細が分かります

②売上台帳クエリ

 売上台帳クエリは①の商品台帳クエリと違い、売上金額クエリが存在するファイル内からデータを取得していることが分かります

 上の関係図にはないですが、売上台帳クエリは現在ブック内の「売上台帳テーブル」からデータを取得しています

 ちなみに上の図に、「読み込まれていません」とありますが、こちらはクエリの読込先が「接続のみ」になっていることを表します

 今回、事例とするクエリのことも解説したところで、前述の1.~4.についても解説を行って行きます

1.クエリを別ファイルにコピー

まず、商品台帳クエリを別ファイルにコピーします

商品台帳クエリ

商品台帳クエリ上で右クリックし、「コピー」をクリックします

クエリのコピー

そして、別ファイルで貼り付けます

クエリの貼り付け

では、他2つのクエリに依存している「売上金額クエリ」を別ファイルにコピーしてみます

すると下の図の通り、依存している2つのクエリも同時にコピーされます

売上台帳クエリは「現在のブック」内のテーブルを取得元にしています

別ファイルには、もちろん「売上台帳クエリ」の取得元になるテーブルはありませんので、売上台帳クエリはエラーになります

そして、「売上台帳クエリ」に依存している「売上金額クエリ」もエラーになります

以降は有料ページ/課金サービスへ

クエリの追加・結合~中級編5回目~

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

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

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

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

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

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

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

今回のポイント

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

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

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

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

1.売上データクエリ(1月+2月)上で編集をクリック

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

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

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

3.追加画面で追加するデータを指定する

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

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

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

<まとめ>

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

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

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

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

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

 

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

にほんブログ村

Power Queryって??

Power Queryって言葉は知っているけど、具体的には何も分からないという方も多いと思います

私も色々と研究してみました

私の個人的な感想は

”本来は表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの”

という感じでした

早速、”論より証拠!”、ということで実際にPower Queryで処理するところを、概要だけになってしまいますがお見せしたいと思います

<実施すること>

・1つのフォルダに、販売個数データに関する3つファイルが入っている

・3つのファイルにデータを1つにまとめた上で、価格表を参照して販売金額を算出して表にまとめる

・しかも、クリック処理だけで行う

<手順>

・ファイル結合

ファイルが入っているフォルダを指定してファイルを結合する

するとPowerQueryエディタ上でこんな感じになっています

・横持ちのデータを縦持ちに変える

PowerQueryエディタ上にある”列のピボット解除”をクリックすることにより、データの構造自体を一発で変えます

・PowerQueryエディタ上で縦持ちになったデータを価格表とマージします

・PowerQueryエディタ上で掛け算(販売個数X価格)を行いシート上に読み込む

最後までクリックだけで処理を完結することができました

<まとめ>

いかがでしたでしょうか?本来は関数などを使いながら、手間暇がかかった処理がクリックだけで簡単に出来そうなのが伝わりましたでしょうか?

今回は概要だけお見せしましたが、もっと詳細な解説ができるように準備を進めていきます! (Power Queryって2??はこちらから)

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

にほんブログ村