CSVからピボットテーブルへPower Queryで時短、怖くないパワークエリ
動画解説
CSVからピボットテーブルへPower Queryで時短、怖くないパワークエリ
動画版「Excelスキルアップ仕事術」です。
Power Queryを使って、CSVからピボットテーブルへ変換します。Power Query(パワークエリ)とは、Excel2016以降から標準になった機能です。
今回からシリーズで、Power Queryの使い方を誰にでもわかるように、詳しく解説していきます。
では2016以前のエクセルでは使えないのかというと、そうではありません。Power Queryは、Excel2010,2013でもAdinnを追加すれば使えます。Microsoft Power Query for Excelのページ
https://www.microsoft.com/ja-jp/download/details.aspx?id=39379
CSVからピボットテーブルへPower Queryで時短、怖くないパワークエリ
Power Queryとは何か?
-
Power QueryとはExcelやPowerBIの為に、様々な外部データソースからデータに接続し、必要に応じてそのデータを変換するETLツールです。
外部ソースと言うとそれはテキストファイルだったり、ワークブックだったり、たくさんのCSVファイルを含むフォルダーだったり、またはwebのsqlデータベースだったりもします。
Power Queryはただ取り込むだけじゃなくて、 データを分割したり不要な列を削除したりして、 データを綺麗な状態にします。使いやすくするということができます。それが Excel ではどこにあるかと言うと、データタブの取得と変換がパワークエリにあたります。
-
CSV ファイルを取得してみましょう。
データタブの中にある データの取得と変換、 ここがパワークエリの入り口になります。
ファイルからをクリックして、テキストまたは CSV をクリックする、というように2段階で選択してもいいです。 -
あるいは、直接その隣のボタン、 テキストまたは CSV からをクリックして、パソコン内部のデータを取り込むこともできます。
-
おおみや店.csvを選択してインポートします。
-
すると取得していますというメッセージが表示され、続いてデータのプレビューが表示されます。
Power Query、データを取得し変換する
-
このまま取り込むこともできますが、それではいつもと同じですね。 パワークエリで取得している意味がありませんので、 データの変換ボタンをクリックします。
-
すると Power query エディターが起動します。
-
プロパティとしてファイルの名前がそのまま使われていますが、この名前を変更することができます。ここでは 売上CSV 変換手順という名前に変更します。
それは、同様の形式のCSVファイルがまだあるので、それを変換するためと、今後定期的に変換する必要があるので、そのときにも再利用するためです。 -
CSV を読み込んだので、数式欄にはこのように、まるでマクロの記録のようにコードが記述されています。
-
日付を昇順に並べ替えました。
Power Queryで時短、クエリの複製を利用する
-
閉じて読み込むと閉じて次に読み込むとの2つの選択肢があります。閉じて読み込むだと、普通のテーブルとなってしまいます。 ここでは、閉じて次に読み込むをクリックします。
-
すると、データのインポートダイアログが表示され、
この取得したデータをブックでどのように表示するか、を選択することができるようになります。
今回は新規ワークシートとピボットテーブルレポートにチェックを入れました。 -
あとは、Excelでの通常操作となりますので、ピボットテーブルを自分で作成していくことになります。
-
ピボットテーブルでレポートは作成できました。さて、CSVファイルはまだ残っていますね。それらにも同様の手順を自動で行ってExcelに取り込みましょう。
-
クエリの複製手順
クエリを複製するには、クエリを右クリックして複製を選択します。 -
クエリエディターが開いたら、クエリの設定でソースをクリックして、おおみや店を他の支店名に変更します。
-
すると複製されたクエリで指定した新橋店のデータが開きます。
-
これを Excel で操作することができますが、その場合閉じて読み込むを選択します。
-
ピボットテーブルを作成する段階で開きましたので、フィールドをボックスにいれてやるだけです。
-
当然ピポッドテーブルへの操作は自分で行います。 それはパワークエリの仕事が接続と変換だからです。
面倒な外部データの取り込みと、変換やデータのクリーニングをこの外部データと Excel を繋いでくれるパワークエリと言うツールを使って短時間でできるわけです。