PowerQueryを使って二次関数グラフを描く（ドリルダウンでパラメータークエリを利用する）
- 今回は、エクセルのPowerQueryを使って二次関数のグラフを描いてみたいと思います。
出来上がりは、下の図のような感じです。
↓
それでは早速
・セルA1に「データ数」、セルA2に「20」と入力
・セルA1:A2を選択してテーブル形式に変換（ctl+T）
・PowerQueryメニューからテーブルからを選択してクエリエディターに移ります。
↓
①名前をテーブル1から「データ数」へ変更
②一行目の20を選択した状態で右クリックでドリルダウンを選択
↓
①このような表記であることを確認
②クエリのアイコン種別を確認
↓
このクエリはここまでとして新たに空のクエリを追加します。
①新しいソース＞その他のソース＞空のクエリを選択
↓
①数式バーに「= {-データ数..データ数}」と入力
②テーブルへ変換＞（とくにオプション変更なしで）OKする
↓
・Column1を選択して（列の追加メニューではなく）変換メニュー＞標準＞除算を選択
↓
・ここでは（仮に）「1」と入力してOK
↓
・数式バーの1を「データ数」に書き換える
↓
・データ数と書き換えるとColumn1の値が-1から0.05刻みに1までに変わっている
↓
↓
・列の追加メニューから指数＞累乗＞２乗を選択する
↓
さてクエリエディターの作業はおえてエクセルに戻りましょう。
・ホームメニュー＞閉じて次に読み込む…から（いったん）接続の作成のみを選択します。
↓
・セルC1（セルA1:A2でなければよいです）を選択
・クエリ1を右クリックして読み込み先…を選択
↓
・テーブルを選択して既存のワークシートのセルC1へ読み込みます
↓
さてグラフを描いてみましょう
・テーブル範囲を選択して挿入メニュー＞散布図（マーカーのみ）を選択します。
↓ 
↓
セルA2の数字を「10」に変更してデータメニュー＞すべて更新をすると
このように描画点数を変更できます。
いかがでしたでしょうか。
バージョン：
エクセル2010
PowerQuery 2.27
別々の予算と実績のデータを対比する EXCEL2010 PowerQuery
予算で作成したデータに実績のデータをPowerQueryのマージ機能（外部結合）で連結しようとしたのですが、以下のように予算になかった顧客分の実績は、予算表の顧客列では空欄になってしまいます。対応方法について考えてみたいと思います。
１．値の置換機能を利用する
・予算表：範囲内を選択→テーブルに変換(ctl+T)→PowerQueryタブ＞テーブルから
閉じて次に読み込む＞接続のみ＞OK
・実績表：範囲内を選択→テーブルに変換(ctl+T)→PowerQueryタブ＞テーブルから
閉じて次に読み込む＞接続のみ＞OK
①Power Queryタブ＞マージ
②テーブル１（予算）を選択
③テーブル２（実績）を選択
④どちらも顧客の列を選択
⑤完全外部を選択
OKをクリック
↓
・NewColumnのボタンをクリック
・すべての列を選択
・チェックボックスをはずす
OKをクリック
↓
①このようにデータがないことを意味するnullとなっているところに富士通と入れたいのですが。。。
さていよいよここでは値の置換の機能を応用してやってみましょう
②（顧客の列を選択しておいて）変換タブ＞値の置換を選びます。
↓
・上段は、「null」とタイプして
・下段は、（後程書き換える目印として何でもいいですが）とりあえず「＠＠＠」とタイプします。
・OKをクリックします。
↓
次の画面になったら数式バーの中で
①見えにくい場合は、クリックで範囲が広くなります。
②「"＠＠＠"」を「each [顧客.1]」と書き換えてenteします。
↓
下のようにnull（空欄）がなくなりました。
あとは、形を整えてエクセルにデータを返します。
↓
・顧客.1の列を削除
・予算と実績の列のnullを０へ置換
２．PowerQueryのif関数を利用する
予算と実績のデータがマージと列の展開がされるまでは同じです。
・列の追加タブ＞カスタム列の追加で上のような画面になりますので
・式に「if [顧客]=null then [顧客.1] else [顧客]」とタイプしてOKをクリックします。
（PowerQueryでのif構文は、
if 条件式 then 動作1 else 動作2 としなければなりません。）
（また、空白を意味するnullは、文字列ではないので"null”とはしません。プレビューのテーブルの中でも斜体で右寄せの書式になっていますね。）
あとは、前述のとおり列の削除、並び順、null→０への置換など形を整えてエクセルにデータを返しましょう。
３．数式バーを修正せずリボンメニューだけで
数式バーでの修正がよくわからなくて面倒という場合にリボンメニューだけでやろうとすると
・予算のテーブルと実績のテーブルをクエリの追加（マージではなく）で結合する。
（この時点でエクセルにデータを返すとピボットで目的の表がつくれますが。。。）
・顧客の列以外は削除する。
・列に対して重複部分の削除を行う。
・このようにして作成した予算と実績の両方を含む重複のない顧客データの列に対して改めて予算と実績をそれぞれマーする。
とPowerQueryのリボンの機能の操作だけで作業ができます。
以下に詳細エディターの内容を掲載します。
画像は、
テキストは、
let
ソース = Table.Combine({テーブル1,テーブル2}),
削除された重複 = Table.Distinct(ソース, {"顧客"}),
削除された列 = Table.RemoveColumns(削除された重複,{"予算", "実績"}),
マージされたクエリ数 = Table.NestedJoin(削除された列,{"顧客"},テーブル1,{"顧客"},"NewColumn",JoinKind.LeftOuter),
#"展開された NewColumn" = Table.ExpandTableColumn(マージされたクエリ数, "NewColumn", {"予算"}, {"予算"}),
マージされたクエリ数1 = Table.NestedJoin(#"展開された NewColumn",{"顧客"},テーブル2,{"顧客"},"NewColumn",JoinKind.LeftOuter),
#"展開された NewColumn1" = Table.ExpandTableColumn(マージされたクエリ数1, "NewColumn", {"実績"}, {"実績"}),
置き換えられた値 = Table.ReplaceValue(#"展開された NewColumn1",null,0,Replacer.ReplaceValue,{"予算", "実績"})
in
置き換えられた値
以上。
バージョン：
エクセル2010
PowerQuery 2.26
30 feature updates to “Get & Transform” in Excel 2016 and Power Query add-in
30 feature updates to “Get & Transform” in Excel 2016 and Power Query add-in
パワークエリの2015年11月のアップデートがあったようです。
エクセルで面グラフ（量率グラフorモザイク図）を作成してみた。（PowerQuery編 その３~列の名称変更および増減への対応～）
さてPowerQueryは便利ですよね。
（前回までの課題では）行のデータが追加されれば、データタブ＞すべて更新でデータ書き換わるのでとっても便利です。
ただし、列の変更（追加、削除、名称変更）には、エラーがでて対応できません。
エラーの内容を見ていくと、どうやら列の名称が見つからないとかなんとか。。。
ということは、列の名称を明示しないでクエリを記述すれば、列の変更に対応できるのか？ということで今回は、列の名称を暗示的に記述する方法に取り組んでみたいと思います。
ここでデータ構造の確認をしておきます。
・一行と一列の項目が並びそのほかは、数値が入っているデータ
→テーブルとしては、左から1列目がテキスト形式で2列目以後は、数値形式となっている。
↓
さてさっそく上記のような表を選択(ctl+shift+*)してテーブルに変換(ctl+T)してPowerQueryへ送ります。
①名称を「元データ」に変更します。
②右クリック＞複製で名前を「修飾データ」とします。
↓
2個のクエリができました。
ここで新しいソース＞その他のソースから空のクエリを追加します。
↓
①「全列名」へ名称変更
②「= Table.ColumnNames(元データ)」と記入
以下のようになります。
（ずべての列名がリストに並んでいることが確認できます。）
↓
同様に空のクエリから
名前：「列１名」とし
関数バー：「= List.FirstN(前列名,1)」とします。
（一番左の列の名称は「地域」と確認できます。）
↓
続いてまたまた空のクエリから
①名前を「全列数」として
②関数バーに「= List.Count(全列名)」とします。
（列数は全部で４つであることが確認できます。）
↓
さらに空のクエリーを作成して
名前を「他列名」として
関数バーに「= List.LastN(全列名,全列数-1)」と記入します。
（2番目から最後までの列の名前が確認できます。）
↓
次からは、「修飾データ」のクエリを作業します。
左端「地域」の列を選択して変換タブ＞その他のピボット解除を選択します。
↓
①グループ化を選択して
②合計を選んで
③「値」の列を選んで
④「合計」とタイプしてOKします。
↓
中略）過去ログを参考にして 総計、比率、累計、座標の列を作成します。
↓
次は新しいソース＞その他のソース＞空のクエリから100行のリストを作成し(
「={1..100}」とタイプして）テーブルに変換します。
続いてクエリのマージより
①Column1を選択
②修飾データを選択
③座標を選択
④デフォルトのままで
OKをクリック
↓
カラムの展開は赤丸のボタンをクリックして
①「地域」と「比率」を選択
②チェックボックスをはずしてOKをクリック
↓
ふたたびクエリのマージから
①地域の列を選択
②元データを選択
③地域を選択
④デフォルトのままでOKをクリック
↓
①ボタンをクリック
②石油、石炭、ガスを選択
③チェックボックスをはずす
OKをクリック
↓
「{"石油","石炭","ガス"}」を2か所とも「他列名」へ書き換えます。
↓
石油、石炭、ガスまでの列を選択して変換タブ＞下にフィルを選びます。
↓
「{"石油","石炭","ガス"}」を「他列名」に書き換えます。
↓
完成です。これで列の名称を変更しても列を増減しても「すべて更新」でPowerQueryが最新データに書き換えてくれます。
としたかったのですが、一部ミスがありました。
「修飾データ」などのクエリの適用したステップの二行目の（エクセルが勝手に追加した）「変更した型」を選択して右クリックから削除してください。
このクリーンアップ作業で完成です。
※さらにクエリ１の「詳細エディター」の内容を以下に示します。
バージョン：
エクセル2010
PowerQuery 2.26
エクセルで面グラフ（量率グラフorモザイク図）を作成してみた。（PowerQuery編 その２~総計、累計の扱い～）
前回は、100本グラフの参照データをPowerQueryをつかって作成しました。今回は、グラフ化したい元々の表に追加している座標（とよんでいる列）を求めるステップもPowerQueryでどうにかできないものかと考えてみました。
ということで前回のスタート地点が今回のゴールになります。
それでは下からスタートしましょう。
・セルA1:D4の範囲は、テーブルに変換（ctl+T）してあります。
↓
・範囲内の任意のセルを選択してPowerQueryのテーブルからをクリック
↓
PowerQueryの画面に移ります。
適用したステップに（エクセルで自動にできあがったものを含めて）
ソース
変更された型
があることを確認します。
↓
まず「合計」の列を追加しましょう。
①「石油」、「石炭」、「原子力」の３列を選択
②列の追加タブ＞標準＞加算を選びます。
↓
①挿入された合計のステップが追加されたこと確認して
②記号をクリックして数式バーの領域を拡大します。
③「Sum」を「合計」に変更します。(列名が「Sum」から「合計」になります。）
↓
つぎは「総計」の列を追加します。途中までは「合計」の追加と同じです。
①「石油」、「石炭」、「原子力」の３列を選択
②列の追加タブ＞標準＞加算を選びます。
③挿入された合計１が追加されたことを確認します。
④「Sum」を「総計」に書き換えます。
⑤「{[石油], [石炭], [原子力]} 」を 「挿入された合計[合計]」に書き換えてenterします。
↓
さて次は、「比率」の列の追加です。
リボンよりカスタム列の追加を選択して
①「Custom」を「比率」に書き換えます。
②使用できる列から列名「合計」を挿入し「/」をタイプして「総計」を挿入し「*100」とタイプします。
③「比率」の列が選択されたまま変換タブ＞データ型＞整数を選びます。
↓
比率ができたところで次は累計をつくります。
累計の追加にはちょっとしたテクニックが必要です。
インデックス列の追加です。
メニューからインデックス列を追加します。（このとき開始番号は０ではなく1を指定します。）
続いて「比率」と「インデックス」を選択しておき、列の追加タブ＞標準＞加算を選びます。
①「Sum」を「累計」に書き換えます。
②「{[比率], [インデックス]}」を「List.FirstN(追加されたインデックス[比率], [インデックス])」に書き換えてenterします。
↓
累計までできましたいよいよ最後です。
座標の列の追加です。これはもう簡単ですね。
カスタム列の追加を選択して
①「Custom」を「座標」に書き換えます。
②図のような式としてenter
↓
完成です。いちおう表示タブ＞詳細エディターで見ることのできる内容を以下に示します。
テーブル１というクエリに行った全ステップが一覧で見渡せます。
以上、「PowerQueryにおける総計および累計の扱い編」でした。
バージョン：
エクセル2010
PowerQuery 2.26
エクセルで面グラフ（量率グラフorモザイク図）を作成してみた。（PowerQuery編 その１）
仕上がり品は今回も同じです。
前回は、量率グラフを100本のグラフで表現してみようとの目的からグラフ用のデータとして100行6列の表を関数でつくりましたが、今回は、題名のとおりPowerQueryで楽できないかなとの考えでやってみたいと思います。
途中までは関数編といっしょです。
座標の列ができたところからはじめます。
さてテーブル１の範囲の中の任意のセルを選択している状態でリボン＞Power Queryタブ＞テーブルからを選びます。
とりあえず名前を「DATA」としておきます。
いったんこのクエリはここまでとしておきます。
続いてホームタブの新しいソース＞その他のソース＞空のクエリを選びます。
空のクエリを選択したのでクエリ１が見えます。
ここで
①数式バーに「={1..100}」と入力して（これだけで縦に1から100まで並んだリストを作ってくれます。）
②テーブルへの変換ボタンを押します。
↓
そのままOKをクリック
次は、クエリのマージを選んで
①最初に取り込んでDATAと名前をつけたテーブルを選びます。
②column1の列を選択
③座標の列を選択
④左外部結合のままでよいです。
OKをクリック
↓
赤丸のボタンをクリックして
①地域、石油、石炭、ガス、比率を選択します。
②チェックボックスははずして
OKをクリックします。
↓
Column1の適切な位置（座標）に値が並べられたのを確認して
①石油、石炭、ガスの列を選択（石油を選択後、shift+→2回で選択できます）
②下にフィルを選びます。
↓
列の順番を整えたら（地域、比率、Column1、石油、石炭、ガスの順）
閉じて次に読み込むを選んで
テーブルを選んで新しいワークシートや既存のワークシートを選んでください。
（エクセルシート上のアクティブセルが既存のテーブルの範囲内にあるとデータを配置する場所を選べないのでいったん接続の作成のみを選んでおき、アクティブセルをなにもないセルとしたのち、クエリの読込先から変更できます。）
↓
シート上にクエリ1というテーブルの範囲が作成できましたのでいよいよグラフの作成です。クエリ1のテーブルの範囲の任意のセルを選択して挿入タブより100%積み上げ縦棒グラフを選びます。
（青色のテーブルが元の参照データで緑色のテーブルがPowerQueryから読み込んだものです。）
残念ながら系列と軸ラベルはエクセル任せでは思った通りになりませんので修正します。
グラフツール＞デザイン＞データの選択
↓
①地域と比率とColumn1は削除ボタンで消します。
②編集を選んでJ2:L101の範囲を軸ラベルにします。
↓
要素の間隔も0%に設定して出来上がり。
※セルA5:D5に追加データを加えたのちリボン>データタブ＞すべて更新でクエリを更新するとグラフにも反映されます。
バージョン：
エクセル2010
PowerQuery 2.26
エクセルで面グラフ（量率グラフorモザイク図）を作成してみた。（関数編）
前回はグラフ100本を使って「量と率」を表現するグラフの作成方法を紹介しました。
データの数値が変更になっただけでもいちいち手作業で修正更新しなければいけないので少しは楽になるかと考えて関数を使ってみましたのでご紹介します。
目標となるデータ（数値は適当です）
関数ということで縦長の方が便利かなということで行列が前回とは入れ替わっています。
・セルA1に「地域」と記入して埋めてセル範囲A1：D4を選択し、テーブルに変換（ctl+T）します。
（セルA1が空のままテーブルに変換するとエクセルが「列１」と埋めてくれます。）
↓
・セルE1に「合計」と記入するとテーブル範囲が自動で広がりますが、気にせずセルE2に関数を代入します。
（E2を選択、数式バーに「=SU」とタイプ入力、↓でsumを選んでtabで確定、←3回、shift+→2回、enterとするとセルE2:E4すべてに関数が自動で適用されます。)
↓
同様に
・セルF1に「比率」と記入
セルF2に関数「=ROUND([@合計]/SUM([合計])*100,0)」を代入
・セルG1に「累計」と記入
セルG2に「=SUM(テーブル1[[#見出し],[比率]]:[@比率])」を代入
・セルH1に「座標」と記入
セルH2に「=[@累計]-[@比率]+1」と代入
↓
ここで一工夫しておきます。H列を一番左に移動させます。
（H列を選択して枠の黒い部分をクリック＞ドラッグで一番左へ）
こちらの表（テーブル1）はひとまず出来上がり。
さて次はグラフ用のデータ範囲をつくります。
セルJ2から下にセルJ101まで1から100までの数字を記入します。
そして順に以下のように関数を記入します。
・セルK2に関数「=IFERROR(VLOOKUP($J2,テーブル1,2,0),"")」
・セルL2に関数「=IFERROR(VLOOKUP($J2,テーブル1,7,0),"")」
・セルM2に関数「=IF($K2="",M1,VLOOKUP($J2,テーブル1,3,0))」
・セルN2に関数「=IF($K2="",M1,VLOOKUP($J2,テーブル1,4,0))」
・セルO2に関数「=IF($K2="",M1,VLOOKUP($J2,テーブル1,5,0))」
101行まで数式をコピー
・セルM1に値「石油」、セルN1に「石炭」、セルO1に「ガス」を記入
そして
・J列の順番を以下のようにM列のすぐ左へ移動させます。
さて グラフ用のデータ範囲は出来上がり。
↓
いよいよ次はグラフです。
セルJ1:O101を選択してリボンの挿入メニューからおなじみ100%積み上げ縦棒グラフを選びます。
ちなみに5行目に下のようにデータを追加するとテーブル１の範囲が自動で拡張されてグラフも更新されます。（便利かも!?）
・地域、石油、石炭、ガスの列の一番下5行目に「ドイツ」、「100」、「100」、「100」と追加するとグラフも更新されます。
以上、「量率グラフをエクセルでつくってみた（関数編）」でした。
