はじめに
表計算ソフトExcelで利用できる色々な形式のファイルを読み込みデータを整形・加工できるPower Queryについて紹介します。
この記事を読むと、次の疑問について知ることができます。
★Power Queryの使い方はどうするの？
・CSVファイルからデータの読み込み
・Power Queryエディターの構成
リボン
クエリ設定画面
ビューウインドウ
クエリー画面
・読み込んだデータの整形
クエリ設定画面のステップの名前変更と不要な先頭行の削除
不要な列の削除
1行目をヘッダー行として指定
ヘッダーの名前変更
時刻列の削除、グループ化
★エクセル上でグラフ化はどうするの？
OS、機種などで説明の仕方が変わってくることがありますので、私の使用しているパソコン環境について載せておきます。
パソコンOS ： Windows10 Pro
Windowsバージョン ： 21H1
Power Queryとは？
Power Queryは、冒頭でも述べたようにExcelを利用する場合には、各種データにアクセスして取り込み、それらデータを整理・加工するためのアドオンに相当する付加機能です。
では、どのようなデータが取り込めるかの見てみましょう。
| ファイルから | データベースから | Azureから | オンラインサービスから | その他のデータソースから |
| Excelブック | SQL Server データベース |
Azure SQL データベース | SharePoint Online | テーブルまたは範囲 |
| text、csvなど | Microsoft Access データベース | Azure Synapse Analytics | Microsoft Exchange Online | Web |
| xml | Analysis Services | Azure HDInsight（HDFS）） | Dynamics 365(オンライン) | Microsoft Query |
| JSON | SQL Server Analysis Services データーベース | Azure BLOB ストレージ | Salesforce オブジェクト | SharePoint リスト |
| Oracleデータベース | Azure テーブル ストレージ | Salesforce レポート | OData フィード | |
| フォルダー | IBM DB2 データベース | Azure Data Lake Storage | Hadoop ファイル (HDFS） | |
| Share Point フォルダー | MySQL データベース | Azure Data Explorer | Active Directory | |
| PostgreSQL データベース | Microsoft Exchange | |||
| Sybase データベース | ODBC | |||
| Teradata データベース | OLE DB | |||
| SAP HANA データベース | 空のクエリ |
ここでPower Queryの特徴などをまとめてみましょう。
Power Queryは、Office2016以降のExcelであれば、標準でPower Queryが利用できるようになっていますが、2010年や2013年でのExcelでは、アドインをインストールする必要があります。
アドインのインストールは次のサイトから行えます。
Power Queryの使い方
CSVファイルからデータの読み込み
Excelを起動して、上部メニューの「データの取得」から上述の表にあるような取り込みができます。
ここでは、読み込むデータをiphoneのヘルスケアのデータの中で体重データを取り込んでみたいと思います。
iphoneのヘルスケアデータをcsvに落とすアプリは、「HealthExport」というアプリを使いました。
その中から体重データ（HKQuantityTypeIdentifierBodyMass_2021-08-234_18-40-56_SimpleHealthExportCSV.csv）を読み込みます。
上部メニューの「データ」⇒「データの取得」⇒「ファイルから」⇒「テキストまたはCSV」をクリックして読み込み、文字コードが「シフトJIS」⇒「unicode utf-8」に変更してから「データの変換」をクリックします。
最初にデフォルトでは、「シフトJIS」になっており、これで読み込むと、日本語の部分は文字化けしてしまいますので、ヘルスケアデータが「unicode utf-8」形式なので、この文字コードで読み込んだわけです。
Power Queryエディターの構成
Power Queryエディター画面は、大きく分けて4つの画面に分けれます。
リボン
取り込んだデータに対して、整形。加工する部分で、「ホーム」、「変換」、「列の追加」、「表示」メニューがあり。それぞれのメニューに対していろいろな操作ができるメニューが表示されます。
クエリ設定画面
クエリの名前、取り込んだデータのソースがあり、その下にデータに整形・加工するステップごとにその工程が記録され、その操作を消すことにより、容易に前の状態に戻すことが可能です。
ビューウインドウ
取り込んだデータにリボンでのメニューにより操作して、くわえられた結果が表示される画面です。
画面上でも、右クリックすることによりデータに変更・加工をすることが可能です。
クエリ画面
この画面は実施したことがあるクエリが表示されます。
現在実施しているクエリの複製をすると、新たに複製されたクエリが表示されるようになります。
読み込んだデータの整形
クエリ設定画面のステップの名前変更と不要な先頭行の削除
「クエリーの設定」画面で、データに加えられ変更されるたびにシステム上で自動で名前が付きますが、分かりずらいので、あなた自身が見て変更した部分がどのように変更したかを簡単に記述して、変更をしてくださいね。
また、先頭行は必要のないものなので削除します。
削除は、リボンの「ホーム」⇒「行の削除」⇒「上位の行の削除」で上位の行削除ウインドウが表示されるので行数に「１」を入れて「OK」をクリックし、右のクエリステップの「削除された最初の行」⇒「最初の行を削除」に変更します。
不要な列の削除
取り込んだデータで、必要な列はcolumn7（endDate）と体重が記録されたcolumn9（value）なので、その他の列をすべて選択した後、右クリックして表示されるメニューの「列の削除」クリックします。
右のクエリ設定のこのステップの名前「削除された列」⇒「不用な列の削除」に変更します。
1行目をヘッダー行として指定
1行目は、endData、valueとなっているので、この行はヘッダー行として指定してやりります。
右のクエリ設定のこのステップの名前「昇格されたヘッダー数」⇒「先頭行をヘッダーに設定」に変更します。
ヘッダーの名前変更
ヘッダーの名前が、「endDate」⇒「日付」、「value」⇒「体重」に変更するとともに、日付部分を分割します。
日付は「2020/03/13 18:22:26」のように記入されていますので、年月日と時刻の間に半角スペースがあることを利用して日付を年月日と時刻に分割します。
時刻列の削除、グループ化
時刻列は必要ないので、時刻列を選択して右クリックして、「削除」をクリックします。
日付列を分割した際に自動で付いた名前「日付.1」⇒「日付」に変更します。
日付列を選択して、日付がダブって記録されている部分があるので。この部分はグループ化してやり、同じ日付での体重は平均を指定します。
これで、日付と体重のデータが整形されましたので、閉じてExcelに読み込みましょう。
リボンの左にある「閉じて読み込む」をクリックします。
エクセル上でグラフ化
データが整形されましたので、この表を用いてグラフ化ができます。
グラフ化については、詳細は省きますが、挿入から「折れ線グラフ」を選択し、軸タイトル、線の色、日付の傾き、背景などを色々設定して、できたグラフが下図となります。
おわりに
如何だったでしょうか？
Power Queryとは？、Power Queryの使い方はどうするの？、CSVファイルからデータの読み込み、Power Queryエディターの構成、リボン、クエリ設定画面、ビューウインドウ、クエリー画面、読み込んだデータの整形、クエリ設定画面のステップの名前変更と不要な先頭行の削除、不要な列の削除、1行目をヘッダー行として指定、ヘッダーの名前変更、時刻列の削除、グループ化、エクセル上でグラフ化はどうするの？などについて解説してきました。
この記事が少しでもあなたにとって役に立てればこれほど嬉しいことはありません。
以上です。
コメント