目次
- Excelの色、配置などすべてに理由がある
- 小数の取り扱いで気を付けたい「演算誤差or丸め誤差」とは？
- 鉄板！Power Queryを活用するための4つのポイント
2021.12.17
ExcelのPower Queryとは？ すぐに理解できる4つのポイント
Excel2016から標準機能となったPower Query。まだまだ新しい機能のため、十分に使いこなせていないという人も多いのではないでしょうか。
毎回一つのテーマに沿って、日本人初のMicrosoft「MVP（Most Valuable Professional）」受賞者田中亨先生よりExcelテクニックについての講義が受けられる「今月のExcelテクニック」。2021年8月のテーマは「Power Query」でした。
この記事では最初の質疑応答から4つのテクニック、そしてデモンストレーションまで、授業のハイライトをお届けします！
Excelの色、配置などすべてに理由がある
小数の取り扱いで気を付けたい「演算誤差or丸め誤差」とは？
続いて取り上げられたのは、CSVファイルを「データの取得」で取得したうえで、元のCSVファイルをExcel上で開いて文字を変更し上書き保存するときに、エラーメッセージが出て保存できないことが度々あるという受講生からの原因と、対処法についての質問でした。
田中先生は「そのような操作は自分ならリテラシー的に絶対にしない」と語ります。それは、“Excelが使っているデータを勝手に変更されては困る場合が多々あるから”。“既にほかの何かが使っているファイルを我々が勝手に使ってはならない”という鉄則は基礎知識として押さえておいた方がよさそうです。
さて、3つ目の質問は、「23.3」のように小数点が含まれる数値を整数部分「23」と小数部分「0.3」に分けた表をつくり、MATCH関数でそれぞれを検索しようとしたところ、整数部分はできたものの、小数部分ではエラーが生じてしまったという受講生からの悩みの声です。
「これは25年前からある鉄板の話です」と田中先生。問題を解き明かすためのキーワードは「演算誤差or丸め誤差」です。コンピュータはすべての数値を「0」と「1」だけで表現する2進数を採用しています。そのため、0.5を除く小数点以下の数値は無限に続く「循環小数」として認識されるのです。すなわち、理論上小数点以下の数値（0.5を除く）は、コンピュータでは処理できないということになります。
ではExcelでどうやって小数を処理しているのかというと、一定の桁数（有効桁数）で無理やり端数処理をして、取り扱えるようにしているのです。すると、ほんのわずかにせよ、実際の数値との間に誤差が生じることになります。これが「演算誤差or丸め誤差」。
これはExcelのみならず、コンピュータに存在する性質の問題であり、Wordなどさまざまなソフトでもエラーの原因となることがあります。なお、「Excelのオプション」の「詳細設定」の中に「表示桁数で計算する」という項目があり、横のボックスにチェックを入れることで少数以下の数値を検索できるようになるということです。
そのことを踏まえて先生は「Excelで時間や時刻を計算するときに24や60で割るようなやり方を見たことはありませんか？ もしある方はすぐに辞めてください！」と忠告します。「演算誤差or丸め誤差」が存在する以上、そのやり方では絶対に誤差を生むことにつながるのです。
自国や時間の計算の場合はTIMEVALUE関数といった関数を駆使しましょう。
鉄板！Power Queryを活用するための4つのポイント
いよいよ授業はメインテーマ「Power Query」に進みました。
Power QueryがないときのExcelでは、元データをすべてExcel内に読み込み、その後Excelの中だけで計算・分類・抽出・加工などの処理を行うという手順が唯一の選択肢でした。
しかし、Power Queryが登場したことで状況は大きく変わりました。変化の内容を理解するうえで重要だと田中先生が話すのが「Power QueryはExcelの外にあるというイメージ」。「取得と変換」を行った時点ではデータはPower Query内に取り込まれただけであり、そこから変換を加えた結果がExcelに提供されているというイメージを持ちましょう。
田中先生が提示する「ExcelユーザーがPower Queryを活用するポイント」は以下の4つです。
1.Excel専用の技術ではない
2.Excelの外にある仕組み（イメージ）
3.「取得」と「変換」の2面性
4.最後はExcelで処理する
Power Queryという「Excel限定でない」「外部の」技術を利用して、「取得」「変換」という2つの機能を駆使し、最後はExcelで処理して完成品を保存するというイメージを持ちましょう。
デモでより深くPower Queryを理解しよう
授業はここからデモンストレーションに進みます。
ひとつめのデモはExcel、CSV、Accessなど複数の種類のデータをまとめてVLOOKUPで検索したいというケース。例えば、日付、名前、地域コード、商品コード、数値が記入されたリストの地域コードをCSV、商品コードをAccessのデータから検索したいというパターンについて考えてみましょう。
元データをPower Query上に読み込み、「クエリのマージ」を実行。テーブルと照合列を選択し、地域コードと地域マスターを結びつけます。すると、地域マスター列が読み込まれ、そこからドロップダウンリストで「地域名」を選択することで、簡単に地域名のリストが読み込めました。商品コードの場合も、Accessのデータに対して同様の操作を行います。
もう一つのデモは、150万行と大量のデータが存在するCSVファイルを取得するというもの。これだけ大量の行が存在するとExcelでは通常読み込めません。しかし、Power Queryならば、行数制限がありません。すなわち、メモリが許す限り何百万件、何億件のデータを取得することができるのです。
「このPower Queryと新しい関数、例えばFILTER関数を使うとものすごい便利です！」と田中先生。場合によっては人間1～2人分の仕事を代替できるくらいの劇的な変化がマクロを書く必要もなく、実現されるとのこと。その内容についても今後の授業で取り扱われるということです。
ここで先生のPower Queryについての講義は終了し、授業は再び質疑応答に入りました。さて、これまでチンプンカンプンだったという方もPower Queryの基礎について理解できたのではないでしょうか。知識を定着させるため、ぜひ実際のデータを取得・操作してみてください。
文＝宮田文机
- 今回取り上げたSchooの授業はこちら！
- 『田中亨先生が教える「今月のExcelテクニック」（2021年8月）』
今日の生放送
「今月のExcelテクニック」は毎回、受講生から寄せられた質問に田中先生が回答するところからはじまります。
今回最初の質問は「Power Queryエディター」から「閉じて読み込む」で作成されるテーブルのカラーリングが青から緑になったことについての話題でした。
先生がその話題をきっかけに「すべてのことには理由があるんですよ」と語ります。例えば「名前を付けて保存する」であれ「セルの書式設定」であれ、ダイアログボックスは必ず“動作を中止するボタンは右、動作を実行するボタンは左」という配置になっています。
これはマイクロソフトが製品のデザインを決定するときのルールブックにおいて定められたれっきとしたルールなのだそうです。そして、この配置は人間工学に基づいた研究成果として決定されているとのこと。
つまり、「Power Queryエディター」から作成されたテーブルの色が青から緑に変わったのにも必ず理由があるのです。おそらく自作のテーブルとの区別の問題ではないかとの先生は推察を語ります。これが必ずしも正解とは限りませんが、ボタンの配置、色の変更一つとっても微細な意図があるということは理解しておいた方がよいでしょう。