はじめに
一年以上前の話になるが、グーグルのアプリの記事を眺めているとモダンエクセルなるものが出てきた。暫く引っかかっていたが、利用のイメージがつかなかったのでスルーしていた。
最近になり経産省のDX教育なるものの無料版を視聴し、その中でエクセルのピボットを活用した演習があり、モダンエクセルのことを思い出した。そういえば、記事の中にパワーピボット、パワークエリという文言があったなと。
正直ピボットには不慣れだったが、演習を通じて何となく掴めてきた。その経験からモダンエクセルって活用できそうというマインドに切り替わり参考書を読んだ。ここではどんな活用ができるのかについて一つ例をあげたい。
ポイント
通常のピボットとの違いは、とあるデータベースにアクセスして情報を吸出し、加工まで出来ること。吸出し方も、ただのコピペではなくパワークエリで定義した手順に則るので楽である。
例題
受け取った情報を分解する。情報はそれぞれレコードとして存在する。レコードを上手くつなげて可視化したい。
元のデータはこんな状態
これをこの形に成形したい
やっていることのイメージだが、
- 区分Bに対しA001という要求が来る。
- これを分解してB001~003にする。
- それぞれの価格は併記
- これをピボットで表現
やること
・エクセルでdbを作る
・別のエクセルから情報を読み取り整形する(パワークエリ)
・整形したデータを表やグラフに変換(パワーピボット)
実践
パワークエリのお仕事
エクセルからデータモデルの作成を行う。ヘッダーの位置の調整や、作りたい表に応じた加工が必要。例えば、カンマ区切りのデータを違うデータとして扱いたい場合は列の分割で、別のレコードにする。他、インデックスをつけるとか。。↓にクエリが完成した結果を示す。
ちなみに一番右の列のデータは「価格」というクエリの情報をマージしてつけている。
気になっていたのがパワークエリやパワーピボットのデータってどこに保存される？だったが、使ったエクセル自体のようだ。単体のデータはないと思う。
パワーピボットのお仕事(ピボットの仕事)
クエリから表示したい項目に対し、行、列、値、フィルターのどこに置くかを決めていく。ここでは並びを調整したくなり、パワーピボット上のデータモデルで列を足した。一番右の色違いがそれに該当。
値の部分にはテキストを使いたいため、メジャーによりテキスト化。やり方は２通りあるようだが、2010の場合は1通りしかできなかった。
1番目：=IF(HASONEVALUE(columnName),VALUES(columnName),"")
2番目：=CONCATENATEX(クエリ名,columnName)
2010では2番目の関数がなかった。。
パワーピボットでは複数のテーブルをリレーションでつないだり、パワークエリで処理したものをさらに加工することに強みがあると思うが今回はあまり活用できなかった。パワークエリで表の要素を1つ作って、ピボットで形を作り込むと割り切ってもよいかも。。
tips
- 値のところに行や列で絞られた結果が複数入る場合は、ちょっとおかしなことになるので要注意。2010では何も表示されなくなった。使った関数の問題？？
- 出したい表示が元のエクセルに対し列の追加をしないと出ない場合、パワークエリでもパワーピボットでも出来る。
- VLOOKUP的な使い方をする場合、パワークエリの「クエリのマージ」、パワーピボットの「リレーションの作成」があるが、筆者の場合、パワーピボットのリレーションが上手くいかない。「リレーションシップが検出できませんでした」と出た。ちゃんと結んでいるのに。。どちらも重複なく設定すると出てはくるが、、ちょっと惜しい
おわりに
以上が基本的な概要だ。
モダンエクセルの書物は少なく、書籍の評価も色々。↓におすすめにあげたのは悪くないと思う。一度では理解できないので何回も読み込むことが必要。2回目からは図だけに着目して見ると理解が早いと思う。何冊か本はあるが、おすすめ本を数周読むだけで大分理解が進むと思う。他の本も読んではみたが1冊でよかったなあ。中々アマゾンのkindleフェアに出ないので泣く泣く定価で購入。。いちばんやさしい～はセールやっていたので思わず購入したものの。。
これがおすすめ
安くてまあまあ。ただおすすめ1冊でよいと思う
読んでみたが。。
コメント