カテゴリー別アーカイブ: コラム

Power Queryって何?~列の結合/複合キー活用~

Power Queryについて、名前は知っていて興味はあるけど実際にどう使うの?

という人は多いと思います

私は良くPower Queryをスマホに例えて説明するようにしています

例えば、好みに合うレストランを探してスマホで予約するケースでは、①検索サイトで好みのレストランを探す、②好みのレストランが見つかったら電話番号をクリック、③レストランの電話番号が、スマホの電話アプリ内に自動で読み込まれるのでそのままクリックする、という感じになりますよね

Power Queryでも①シート内、他のファイル、フォルダからデータを取得、②データを変換、③シートやピボットテーブルに取得データを読み込み、という流れになります

そして、詳細は本編に譲りますが、Power Queryの”Query”はスマホ内に設定するBOOKMARKと同じだと説明しています

スマホ内のBOOKMARKは、クリックさえすれば、いつでもお気に入りのデータにアクセスできます

Power Queryがスマホ内に設定するBOOKMARKとの違いは、アクセスしたデータを事前に決めたルールで変換できることです

今回は、このQueryの変換例を紹介します

ところで、エクセル内のデータを自分の都合のいいように集計するコツって何でしょう?

関数を知っている、ピボットテーブルを使いこなせる・・・

上記はいずれも、正しいです

但し、もっと重要なことがあります

データを集計するためのキーをうまく作り出すことです

私が良くセミナーで受講者の方に出す問題があります

ちなみにこの事例は、私が以前、日常業務で行っていたものです

以下から実際の問題です

上記の表にて、条件1と条件2、両方が”B”の時に、表内に◯を加えるにはどうしたらいいでしょう?というものです

しかも、条件がたえず変わる事も考慮して解決せよ、というものです

もしかしたら、AとCの時に〇を出せ、という風に修正されることもあるし、更にCとCの時にも〇を加えろ、ということもありえます

答えは、新たなキーを生み出すことにあります!

条件1と条件2を組み合わせて複合キーを作成するのです

そして、複合キーに対応した値を加えた表を作成します(この表をマスター表とします)

こうすれば、D列にVLOOKUP関数を加えれば、複合キーを参照して関数が自動的に、右横の表から値を引っ張ってきてくれます

もし、条件が修正になったり、条件が加わっても、右横の表を変えれば自動的に対応できます

以上がキーをうまく使いこなすことの効果の説明です

では、今回説明した問題の解決方法をPower Queryでも行ってみましょう!

Power Queryには”列のマージ”という機能があるので、上記で説明したような複合キーも簡単に作成できます

①表をテーブルに変換してPower Query Editorを立ち上げ

エクセルシート上のデータをテーブルにして取得し、変換できるようにします

ちなみに、このPower Query Editorというデータ変換用の画面が、別途、立ち上がるのが、Power Queryの分かりにくさかもしれませんね

➁Power Query Editor画面上の”列の追加”をクリック

③画面内の両列をクリックした上で”列のマージ”をクリック

Ctrlキーを押しながら、複合キーを作成する列を指定するのがポイントです

④③を行うと自由に複合キーを作成できるようになります!

両列をつなぐ文字に”-”を指定して、新たに作成する列名を複合キーと指定します

⑤読込先を”接続の作成のみ”に指定して読込

接続の作成のみとは、シートに表示しないでQueryを作成するという意味です

⑥新たなQueryが画面右に出来ました!

⑦マスター表も接続のみのQueryを作成

⑧作成した2つのQueryをマージします

Queryのマージとは関数で言えば、VLOOKUP関数と同じです

①~⑧で読み込まれた表が以下です!

<まとめ>

 Power Queryを前述の問題に適用しましたが、Power Queryだと最初から複合キーを作成する前提で機能が作られている分、利便性が高いです。

 そして、VLOOKUP関数で同じことを行う場合には、実際には、VLOOKUP関数単独だとマスター表にないものはエラー表示になりますので、”IFERROR関数”も組み合わせる必要があります。エラー対策が必要ない点も、Power Queryが利便性が高いです。

長文になりましたが、Power Queryに可能性を感じた方は、ぜひ、Power Query講座をのぞいてみてください!

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


にほんブログ村

 

文字列から空白を取り除く_Power Query

文字列に空白が入っていて処理に困ったことってありませんか?

そういったデータに限って、大量にデータ数があったりしますので、手動で処理するのは困難です

Power Queryであれば直観的クリック操作により、一括で処理できるので、とても効率的です

今回、解説に使用するデータは次の画像のデータです

サンプルデータ

上の画像の商品名の列にある文字は最大で3文字しかないように見えます

実は空白文字が入ってるので、LEN関数で長さを測ると10文字あります

データを確認したので、これから本格的な解説を始めます!

目次

1.PQエディターを立ち上げる

2.トリミングを行う(空白を排除)

3.エディタの内容をシートに出力する

<まとめ>

1.PQエディターを立ち上げる

➀データ上にカーソルを置いて、データタブから”テーブルまたは範囲から”をクリックします

➁テーブル作成のポップアップが出たらOKをします

続きを読む 文字列から空白を取り除く_Power Query

Power Queryって何??パワークエリの使用メリット

今回は今までと違う側面からPower Queryを使用するメリットを解説していきたいと思っています

言うまでもなくエクセルは便利なツールです

但し、問題点が一つあります

”他人の作成したエクセルファイルはとても分かりにくいのです”

コメントが無い記録マクロ・・・ネストの中にネストを繰り返したIF関数・・・

などなど例を上げたらきりがありません

一方、Power Queryでは、Power Queryエディターを開けると、作成されたクエリ(出力内容)の中味と、出力されるまでの履歴が、とても簡単に分かるようになっています

今回は以下のテーマを題材にしてPower Queryのクエリの中味と履歴を把握する方法を解説していきます

<今回のテーマ>

・元データ:2016年と2017年の自動車販売データ(テーブル名:販売データ)

・元データをPower Queryにて年度別に合計金額を出します

・Power Queryの出力データ:年度別・合計金額

1.適用したステップの確認

テーマで紹介した販売データから年度別の販売金額を出力するまでの履歴をPower Queryエディターの右側、<適用したステップの確認>で確認することができます

2.ステップの中味の確認

各ステップ毎の詳細な中身は2つの方で確認できます

➀数式バーを確認する

今回の例で言えばソースをクリックすると、ソース元のテーブルが何かを確認することができます

②ステップ自体の中味の確認

各ステップの中味を確認するには下の画像の黄色の部分をクリックします

すると上の画像の例で言えば、グループ化した時の画面が出てきます

<まとめ>

Power Queryを使ったことの無い人には少し分かりにくい内容だとは思いますが、通常のエクセルの機能とは違い、中身と履歴が簡単に分かるというのは理解して頂けたのではないかと思います。更に分かり易くする為には各ステップの名前(通常は自働設定される)を分かり易く変更したりする方法も可能ですので、ぜひトライしてみてください

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


にほんブログ村

Power Queryって何???連番作成

前回、Power Queryとは
”本来、表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの”
という説明をしました

今回は、Power Queryの数ある機能の中で、Accessと同じように使える機能を一つだけ紹介します

Accessで使える機能の一つがテーブル作成で使用するオートナンバー機能です

この機能に代わる機能がPower Queryにもあります

Power Queryエディターを起動したら、列の追加タブをクリックしてください

するとインデックスというリボンが見えるはずです

0からでも1からでもいいので、どれかをクリックしてみてください

するとインデックスという列が出来ています

これがAccessのオートナンバーの替わりに活用できます

最後に

短い内容でしたが、いかがでしたでしょうか?

個人的にはエクセルでここまでできるようになったことに感慨深いです

ちなみにインデックス列は最終列に追加されるので、最初に持ってくるようにしましょう!(列名を変えるのを忘れずに!)

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

Power Queryって???合計を計算する

前回はPower Queryを使って区切り文字によりデータを分割する方法を紹介しました。今回はPower Query上で合計などを計算する方法を紹介します

<使用するデータ>

・年度別売上データ

<前提操作>

➀データタブから”テーブルまたは範囲から”クエリを作成するを選択します

②”テーブルの先頭行を見出しにする”にチェックを入れたままOKを押す

③Power Queryを立ち上げおく

1.合計値をグループ別(年度別)に算出する

➀売上金額の列にカーソルを置いたままグループ化をクリックする

②以下の画面のように各項目を設定する

③年度別に合計金額が算出されているのを確認する

④エクセルシートにクエリを読み込む

2.四則演算をする

➀カスタム列を追加する

売上金額の列にカーソルを置いたまま列の追加タブから”カスタム列”をクリックする

②計算したい列を追加する

事前に列名を”千円単位”に変えておく

③割り算(/1000)をする

④正しく値が算出されていることを確認する

<まとめ>

1.2.の事例ともに直感的に操作できている印象を持たれたのでしょうか?特に1についてはピボットテーブルの内容をより簡単に使えている印象です。エクセルが苦手な方でも抵抗なく活用できるのでないでしょうか?ぜひ有効活用してみてください

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


にほんブログ村

Power Queryって???区切り文字による分割

前回

”本来は表計算ソフトであるエクセルにAccessのデータベース管理機能を加えたもの” として、代表的な機能を組み合わせて活用例(結合やピボット解除、マージ等)を紹介しました

今回はもう少し細かい特徴も紹介したいと思います

今回、紹介するPower Queryの特徴は関数を不要にする機能です

Power Queryはスマホのように直感的に操作できます。直感的に操作して関数を不要にする事例を1つ紹介します

<区切り文字でデータを分割する>

もし、次の様なデータを”-”で区切る場合には通常なら”区切り位置”の機能を使うと思います

ところが、データを都度、追加していく場合には複数の処理が必要な”区切り位置”機能を使うのが面倒なので、関数を使うことになります

上の画像だと3つの種類の関数を使っています

とても、作成が面倒ですし、管理も大変です

ところが、Power Queryだと実に簡単に区切り処理ができます

下のGIFはPower Queryでの処理です

テーブル更新するだけで、区切りが行われています

実は事前にテーブルへ区切り処理を埋め込んであります

➀テーブル上にカーソルを置いてPower Qeryエディターを起動する

②Power Queryエディタ上で変換タブから”列の分割”を指定して読込処理を行う

実はこれだけです

とても直感的だと思います

<最後に>

今回は関数を置き換える例としては、特殊な事例だったと思います(FIND関数を知っている人もそもそも少ないと思います)

もっと標準的な機能としてはPower Queryエディタ上にこんな機能があります

指定した列を四則演算するものです

これを使えば関数を使用する必要性はあまりありません

ちなみに行の合計はテーブル自体で行うことが可能です(過去記事

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

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


にほんブログ村

Microsoft Power Query~AccessとExcel連携~

Power Queryを使えばAccessとExcelのいいところを組み合わせて利用することができます

昔、電話は電話帳をめくりながら、ダイヤルを回しましたが、今はスマホで検索して電話をしています

PowerQueryを使えば、電話帳のような大量のデータをAccessにて一括管理して、必要な時にエクセルで抽出して加工することができます

VBAのような専門知識は必要とせず、流れを覚えてさえしまえばとても簡単です

以下、概要だけ羅列します

1.PowerQueryを起動

2.抽出条件を指定します

3.抽出条件は後から削除することもできます

4.エクセルのセルで条件を指定できるようにします_1/2

5.エクセルのセルで条件を指定できるようにします_2/2

上記だけでは分かりずらいと思いますが、直感的にできるようになっていると思うのでぜひ試してみてください

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


にほんブログ村