Microsoft Excel の Power Query でテーブルを作成し、データを入力する | TechRepublic

Microsoft Excel の Power Query でテーブルを作成し、データを入力する | TechRepublic
Microsoft Excel ロゴ編集イラスト
画像: Renan/Adobe Stock

Microsoft Excelブックのデータ更新は一般的ですが、Power Queryで簡単なMコードを実行することで、手入力を置き換えることができるケースに遭遇するでしょう。このタスクは、静的なテーブルを作成する場合に最適です。例えば、手数料率のテーブルを参照して手数料額を計算する場合などです。これらの値はほとんど変更されません。Power Queryのコードを使用することは、そのデータを別のアプリにエクスポートする場合に適した代替手段です。

このチュートリアルでは、Mコードを使ってPower Queryで静的なコミッションテーブルを生成し、データを入力する方法を説明します。データセットには、売上ベンチマーク値とそれに対応するパーセンテージが含まれます。テーブルをExcelにインポートしたら、その値を参照してコミッション額を計算できます。

参照: Google Workspace vs. Microsoft 365: チェックリスト付き比較分析 (TechRepublic Premium)

最初は、M コードを書くのは手動で値を入力するよりも速くはありません。Power Query でクエリを作成したら、必要に応じて M コードを変更し、いつでもデータセット全体を簡単に再生成したり、別のアプリから呼び出したりすることができます。

私は Windows 10 64 ビット システムで Microsoft 365 を使用していますが、Excel 2010 までの以前のバージョンも使用できます。

このチュートリアルの Microsoft Excel デモ ファイルをダウンロードできます。

Power Queryで空のクエリを開く方法

Power Queryでデータセットを作成して入力するため、空のクエリを開く必要があります。そのためには、「データ」タブをクリックし、「データの取得と変換」グループの「データの取得」ドロップダウンから「Power Queryエディターの起動」を選択します。

空のクエリを追加するには、クエリパネル(左側)を右クリックし、「新しいクエリ」を選択します。表示されるサブメニューから「その他のソース」を選択します。さらに、次のサブメニューで「空のクエリ」を選択します(図Aを参照) 。

図A

Power Query で空のクエリを作成します。
Power Query で空のクエリを作成します。

Power Queryの数式バーが開き、クエリに「Query1」という名前が付けられます。これを「Commission Rates」に変更できます。名前をダブルクリックし、キーボードから新しい名前を入力します。

Power Queryで#tableを使用してテーブルを生成する方法

操作するデータがないため、利用できる機能はほとんどありません。代わりに、Mコードを使ってデータセットを生成します。「ホーム」タブの「クエリ」グループにある「高度なクエリエディター」をクリックして、空白のウィンドウを開きます。必要に応じて、「表示オプション」ドロップダウンをクリックし、「行番号を表示」を選択してください。これは必須ではありませんが、行番号については後ほど説明します。

図Bに示すように、以下のコードを入力してください。間違いがあった場合は、下部の構文エディタに情報が表示されます。ご覧の通り、構文エラーはありませんので、「完了」をクリックして、図Cに示すデータセットを生成します。

#table ( type table [ #"Benchmark" = Int64.Type, #"Rate" = Number.Type ], { {0,00}, {800,.002}, {10000,.003}, {12000,.0035}, {15000,.004}, {20000,.055} } )

図B

このコードをキーボードから手動で入力してください。
このコードをキーボードから手動で入力してください。

図C

コードはこのデータセットを生成し、入力します。
コードはこのデータセットを生成し、入力します。

このコードはMの#table関数の簡易版です。#table関数の包括的な解説はこの記事の範囲を超えています。1行目から3行目はテーブル関数を実行します。4行目から7行目は、列名、ベンチマーク、レート、データ型を定義します。8行目から15行目は、両方の列の値を指定します。最初の列はベンチマーク値を生成し、2番目の列は対応するレートを生成します。16行目は、2行目の最初の括弧を閉じることで関数を完了します。

Excelにデータを読み込む方法

新しいデータセットを生成したら、Excelに再度読み込むことができます。読み込むには、「ホーム」タブの「閉じる」グループにある「閉じて読み込む」オプションをクリックします。図Dは、新しいデータセットの使用例です。ご覧のとおり、20,000ドル未満の手数料は低くなっていますが、売上が基準額に達すると手数料が大幅に高くなります。

図D

データセットを参照して手数料を計算します。
データセットを参照して手数料を計算します。

確かに、このような単純な例では、Power Query を使うのはやり過ぎのように思えます。一見すると、時間も労力も節約できないように思えます。しかし、はるかに大規模なデータセットを扱うことを想像すると、その利便性はより明らかになります。

Power Queryで元のクエリを取得したら、パラメータを介して新しい値を渡す関数を作成することで、Mコードを簡単に変更し、いつでもデータセットを再生成できます。このタスクは単純ですが、はるかに大規模なプロセスの一部となることもあります。

Tagged: