タグ別アーカイブ: Power Query取得

Power Queryエディタ上で複数条件付き合計処理~M関数使用

 今回は、M関数を使用して「SUMIFS関数」と同じ「複数条件付き合計処理」をPower Queryエディタ(以降エディタ)上で行う方法を解説したいと思います

 M言語を学ぶ目的の一つは、Power Queryのメリットである「行列一括で処理を行う」とワークシートでの「細かい操作・計算」を両立させることです

SUMIFS関数は細かい計算を行う関数なので、M言語の良さを学ぶという点で凄くいいテーマです!

 特に記事の中で出てくる「別クエリのテーブル化/入れ子のテーブル作成」と「Table.Select関数による特定の行抽出」はM言語の醍醐味の一つといっていいと思います!

記事の最期にはサンプルファイルも付いていますので、ぜひ、後で実際に手を動かしてみて下さい!

目次

今回使うデータと行いたい事

今回のポイント

カスタム列・作成画面から入れ子テーブル作成

テーブルから条件に合う行を抽出

各行のテーブルを合計処理

<まとめ>

今回は下の図のように、2つのテーブル/A列からとF列からを使用します

画面左は元データとなっており、年度は2016年と2017年の2年分が含まれています

テーブル名は「AllData」で設定してあります

 画面右はH列に「SUMIFS関数」が入力されており、F列とG列にSUMIFS関数の条件が設定されています

例えば、H2セルには297,628と表示されています

 H2セルは画面左の「AllData」テーブルから「2017年度」「軽自動車」の2つの条件を満たす行を抽出し、売上金額の合計値を集計しています

こちらのテーブル名は「Condition」で設定されています

 今回は、上のSUMIFS関数と同じ計算をエディタ上で行い、エクセルシート上に読込めるようにします

 上のGIF画像では、「Condition」テーブルの下に今回作成するクエリの内容を読み込んであります

 「Condition」テーブルの2行目の年度を変え、更新を押したら「Condition」テーブルの下に読込んであるクエリの内容も変わっています

今回のポイント

別クエリのテーブル化/入れ子のテーブル作成

下の画像はテーブル「Condition」のクエリです

 一番右の列にある列/詳細はエディタ上でカスタム列・作成画面を使用して追加しました

Conditionのテーブルの中に、別なテーブルの内容が入れ子で作成されています

 画面下にあるように、詳細列の各行には「AllData」テーブルの内容がそれぞれ紐づけられています

 この各行に紐づけたテーブルから①条件に合う行のみを抽出し、②合計値を出します

2つのM関数の使用

今回の記事では2つM関数を使用します

①条件に合う行のみを抽出

Table.SelectRows

書き方:Table.SelectRows(テーブル名, 条件)

②合計値を抽出

List.Sum

書き方:List.Sum(リスト名)

カスタム関数の使用

 上記のM関数/Table.SelectRowsの第二引数/条件においては、カスタム関数を使用します

カスタム関数を作成する要領としては、下の図のような要領です

(a,X,b) =>ax + b

まず、()内において式に使用するもの/a,X,bを宣言します

その後、実際に宣言した内容を使用した数式を示します

 では、今回行いたい事とポイントを確認したところで本格的な解説を始めたいと思います

カスタム列・作成画面から入れ子テーブル作成

解説は、前述の「AllData」テーブルと「Condition」テーブルをエディタ上に読込んだ状態から始めたいと思います

 まずは「Condition」クエリを開き、列の追加タブから「カスタム列・作成画面」を開きます

カスタム列・作成画面を開いたら、次のように指定します

数式には、もう一つのクエリ名/AllDataを指定します

 上のGIF画像のように、もう一つのクエリ名を指定してOKボタンを押すと、新たな列が追加されます

 新たに追加された列にて、「Table」というテキストの横をクリックすると、画面下に「テーブルの中味」が表示されています

テーブルから条件に合う行を抽出

 上記の状態だと、Conditionの各行には全く同じ内容のテーブルが作成されています

ここから各行のⅰ)年度、ⅱ)製品別の条件にあう行のみを抽出します

条件に合う行を抽出するには、M関数/Table.SelectRowsを使用します

 そして、今回のポイントで記述したように、条件を指定する第二引数にはカスタム関数を使用します

今回は私の好みになってしまいますが、()の中に「myRow」を指定します

上の図がM関数/Table.SelectRowsを、カスタム関数も使用した上で作成した画面です

カスタム関数の部分だけを抜き出すと次のようになります

(myRow)=>myRow[年度]=[年度] and myRow[製品別]=[製品別]

myRowを「AllData」クエリの代用だと割り切って理解すれば、理解は簡単になると思います

 要はカスタム関数内にて、年度と製品別の2つの条件が一致するものを指定しています

 では、上記の指定をした後にカスタム列・作成画面のOKボタンを押すとどうなるでしょうか?

実は、一目見ただけでは違いは分かりません

 下のGIF画像のように各行のTable/テーブルの中味を見ると、違いは一目瞭然です

各行のテーブルを合計処理

合計処理は2段階で行います

 まずは、前述の条件に合うように抽出したテーブルを「売上金額・列」でリスト化します

その後、リスト化した内容をList.Sum関数で合計処理します

では、まずは最初に「売上金額・列」でリスト化します

リスト化はカスタム列・作成画面で次のように指定します

 リスト化を行うと、エディタ上の各行は次のGIF画像のような表示になっています

 では、リスト化は行ったので、リスト化した内容をM関数/List.Sum関数で合計処理します

 上の画像のように、List.Sum関数で前述のリストを()で閉じれば、各行の合計が処理されます

 記事冒頭のGIF画像は、上の画像のエディタの内容から次の2つの処理をおこなってエクセルシートに読込んだものです

・「売上金額の列」➡削除

・「詳細の列名」➡SUMIFSへ列名変更

<まとめ>

今回は、「SUMIFS関数」のように複数条件付きの合計処理を行いました

 M関数は2つ使用しましたが、最大のポイントはM関数を使用する前に、カスタム列・作成画面で別クエリの内容を「テーブル」として紐づけた点です

 この点が従来のエクセル操作とは大きく違う点であり、M言語の醍醐味とも言える部分だと思います

今回はM言語の中味を理解するのに、いい内容だったと思います

ぜひ、手を動かして試してみてください

参考までに今回のサンプルファイルも添付しておきます

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

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


にほんブログ村

おすすめ記事_2020年10月10日

最新のエクセル技術を紹介した、おすすめ記事を紹介します

1.スピードメーターグラフテンプレート(散布図グラフ使用)

これまでも円グラフとドーナツグラフを組み合わせたスピードメーターグラフは紹介してきました

今回、紹介するのは針部分に散布図グラフを使用した、よりリアルなスピードメーターグラフのテンプレートです⇒記事

2.XLOOKUP関数を組み合わせて集計上手になる

マトリクス表に関する処理は手間がかかるものです

2つの軸に合うもの選択するのに、目で照合したりする必要がありました

例えば、ある部署の売上成績と前期比から、2つの条件に合うランクを選択する場合などです

ところが、XLOOKUP関数を組み合わせれば、2次元のランク付けを自動化できるのです⇒記事

3.Power Queryで複合キーをうまく活用する

集計上手になるコツのひとつが複合キーをうまく使いこなせることです

例えば、条件1と条件2がともにBの場合は〇を出力する、などのケースです

この場合は複合キーをもとにした別表を作成し、VLOOKUP関数で参照して〇を出力します

今回はPower Queryを使用してもっとスマートに行える方法を紹介します⇒記事

<まとめ>

エクセルが誕生して20年、その間にエクセル機能は目覚ましい進歩を遂げました

そのエクセル機能・進化の果実はしっかり活用していきましょう!

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


にほんブログ村

Power Queryでエクセル操作はどう変わる?~初級講座1回目

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

 読者の皆さんはエクセルのPower Queryと聞いて、具体的に使用するイメージが湧きますか?

 エクセルが誕生して20年以上も経ちますが、海外ではPower Queryはエクセルが生んだ機能の中で最高機能とも言われています

 ところが、日本ではPower Queryはどういう風に使うものなのか?何ができるのかについては、まだあまり知られていません

まずは、このPower Queryはどんな方におススメの機能なのか?について解説したいと思います

Power Queryは、こんな方に特におススメです

・日常的にエクセルで大量のファイル、シートをガッツで扱っている

・Access、関数、VBAを覚えてエクセル作業を効率化したいが、時間がな

 Power Queryはスマホのような直感的なクリック操作で、データをブロック遊びのように結合したり、分解したりできます

 ぜひ、上記のような悩みを持つ方にはこの記事を最後まで読んで頂きたいと存じます!

もし、時間が無い方はぜひ、下の動画をのぞいてみてください!

 この回では、Power Queryの使用イメージついて、読者の皆さんが日常的に使用しているものを例にしながら丁寧に解説していきたいと思います

さて、読者の皆さんは、ガラケーを使ったことはあるでしょうか?

筆者はガラケーが出て来た時点で、既に衝撃を受けていました

以前、使っていたのは固定電話だけでした

 ガラケーが登場した時には、ついに「電話を持ち歩き出来る」新しい時代が来たな、と思いました

そして、ガラケーからスマホに変えたときに、更に大きな衝撃を受けました。

 Power Queryを初めて使った時のイメージは、スマホに変えた時のイメージに近いです

 さて、ここでPower Queryをイメージしやすくする為に「電話帳でレストランを探して、ガラケーで電話予約」するシーンを思い浮かべてみましょう!

そして「スマホでレストランを検索して、スマホで電話予約」するシーンも思い浮かべてみて両者を比較しましょう!

この2つにはどんな違いがありますでしょうか?

この2つの違いを意識しながら、Power Queryの使用イメージの解説を読み進めていってください!

データ取得

さきほどのレストランを予約するシーンでは、ガラケーで電話をする場合は、電話帳を見ながら電話をしていました

つまり、ガラケーの中にはレストランの電話番号というデータはありません。

一方、スマホの場合もスマホの中に電話番号というデータが無いのはガラケーと一緒です

但し、ネットから電話番号を取得しています。

この「データ取得」が従来のエクセル操作方法とPower Queryの違いの一つです

Power Queryでは、下の画像の「データの取得」を通じて、様々な箇所からデータを取得します

データの取得画面

取得できるのはⅰ)シートデータ、ⅱ)ファイルデータ、ⅲ)フォルダ内のファイルデータなどのエクセルデータに加え、なんとⅳ)PDFファイル内やⅴ)Webページ内のデータも取得できるのです!

ここで次の疑問を持つ人もいるとおもいます

これらの取得したデータはファイル内に保持することになるのか?

この点ですが、Power Queryでデータを取得すると「クエリ」というものが画面右にできます

クエリー

このクエリーは、スマホ内のものに例えるなら「ブックマーク」だと思ってください

ブックマーク

このクエリーをクリックすれば、一度取得したデータに再アクセスできます

変換・読込

ガラケーではレストランに電話をするときに電話番号を0、3、4・・・とひとつずつ打ち込んでいました。

一方、スマホでは、Webサイト内の電話番号のマークをクリックすれば、スマホに電話番号がセットされます。

つまり、Webサイトを通じて取得した電話番号を、スマホでそのまま電話できるように変換しています

そして、変換したデータをスマホ内の電話画面に読込先を変更しているのです

Power Queryでは、取得したデータはPower Queryエディター(以降、エディタ)で様々な変換処理を行うことができます

Power Queryエディター

以下は、Power Queryエディター内・変換処理の1例です

①複数のクエリーの組み合わせ

こちらは、VLOOKUP関数の処理に相当します

複数のテーブルデータを、クリック操作のみでブロックのように結合します

 下のGIF画像では、「売上結果」というクエリと「商品台帳」というクエリを結合しています

➁計算処理(四則演算処理

下のGIF画像では、エディタ内で「販売単価」と「販売個数の合計」の列を選択乗算をクリック のみで販売金額を一括で算出しています

 そして、変換したデータは必要に応じて、様々な箇所、様々な方法で読み込むことができます

データのインポート

①テーブル

テーブルデータとして既存シートの中や、新規シートに表示することができます

➁ピボットテーブル

変換データをそのまま、ピボットテーブルにすることもできます

③接続の作成のみ

取得したデータをシート上に「読込まない」「表示しない」という方法を選択することができます

ここがPower Queryの使い方の大きなポイントの一つです

ところで、エクセルを使用していて、ファイルが重くなって困った事はありませんか?

エクセルシートに表示されているデータは、あくまで表示されているだけであって、表示されている箇所にデータ自体が存在するわけではありません

データはエクセルシートとは別の箇所に保管されています

そのため、シート上に他の箇所にあるデータを表示する際、PCに負担をかけてしまい、動作が重たくなるのです

表示行数が制限内でも、表示行数が多くなればファイルの動きも重くなります

そんな悩みを解決するのが「接続の作成のみ」です

表示しないクエリーを作成しておいて、前述の変換処理、①複数のクエリーの組み合わせ により、必要なデータだけ抜き出して表示する、もしくは利用するということができます

表示しないクエリーを利用することで、エクセルの処理を格段にスピードアップさせることができます!

<まとめ>

 今回は、Power Queryでは操作の仕方がどう変わるかについて、スマホを例えに使用して操作のイメージを解説しました

スマホはネットの情報をそのまま利用できます

 Power Queryでも、スマホと同様に「シート上のデータ」や「他のファイルのデータ」を取得し、そのまま結合・変換して直接利用することができます

 そして、読込先を「接続の作成のみ」に指定することで、ファイルを重たくしないように工夫することもできます。

 さらに、今回ご紹介できなかったPower Queryの魅力の一つは、スマホのように「直感的なクリック操作」に使用できることです。

 次回からはイメージだけでなく、実際に使用してみることにより、Power Queryの「直観的な操作」に触れてみましょう!

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


にほんブログ村