Microsoft Excel で Power Query を使用する方法

Power Query は技術的すぎると考えているため、多くの人が避けていますが、実際には、データを整理するための最も安全で簡単な方法の 1 つです。直感的なインターフェイスで面倒な作業を処理し、作業中のクリックを記録し、コーヒーが冷める前にワークフローを自動化します。したがって、これまで Excel の基本的な数式から一歩も外に出たことがないのであれば、今日がスタートの日です。

読み進めるには、例で使用されている Excel ワークブックの無料コピーをダウンロードしてください。リンクをクリックすると、画面の右上隅にダウンロード ボタンが表示されます。

はじめに: Power Query インターフェイスへのアクセス

何も壊さずにデータを接続する

Power Query は Excel に組み込まれたデータ変換エンジンで、データをインポート、クリーンアップ、再形成できます。データの別のクエリ バージョンで動作するため、ソースの保持を心配することなく、自由に図を操作できます。

これまで Power Query を使用したことがない場合、使い慣れるための最善の方法は、現在のブックに既に存在するテーブルを読み込むことです。開始方法は次のとおりです。

1. 任意のセルを選択 データセット内で、を押します Ctrl+T (またはクリック テーブル で 入れる タブ) をクリックしてデータをテーブルに変換します。

2. で テーブルの作成 ダイアログ、確認してください 私のテーブルにはヘッダーがあります にチェックが入っているのでクリックします わかりました。

3. テーブル内の任意のセルを選択し、 テーブルデザイン タブ、名前を変更します T_スタッフ そして押します 入力。

4. で データ タブ、クリック テーブル/範囲から をクリックして Power Query エディターを起動します。

Power Query エディターのツアー

新しいデータ ラボに慣れる

Power Query エディターが表示されても、怖がらないでください。はい、これは特殊な環境ですが、効率性を考慮して設計されています。また、ボタンは数十ありますが、作業の 90% では同じいくつかのボタンを使用することになるでしょう。

次のインターフェイス要素に注目してください。

1. クエリペイン: このサイドバーには、現在のワークブック内のすべてのクエリがリストされます。クリーニングしているさまざまなデータセット間をすばやく切り替えることができます。
2. 「ホーム」タブ: これがメインのダッシュボードです。これには、重要な「閉じてロード」終了ボタンと、その他の一般的なツールがいくつか含まれています。
3. 「変換」タブ: 既存のデータ列を変更する必要がある場合にこれを使用します。
4. 「列の追加」タブ: これは、元のデータを保持しながら、それに基づいて新しい列を作成する場合に使用します。
5. 「表示」タブ: ここでオプションを切り替えることで、Power Query エディターに表示される内容を変更できます。
6. 数式バー: これは、クリックによって作成された M 式を示しています。現時点では、これは無視してかまいません。Power Query が独自の言語についてメモを取っているだけです。
7. データのプレビュー: これがライブワークスペースです。データをクリーンアップすると、そのデータがリアルタイムで表示されます。
8. クエリ設定: ここでクエリに名前を付け、これまでに実行した手順を表示します。

タブには必要なツールがすべて含まれていますが、Power Query で最も速く作業できる方法は多くの場合、右クリック メニューです。による 列ヘッダーを右クリック データ プレビューでは、上部のタブを探し回らなくても、最も一般的に使用されるツールの多くにアクセスできます。

数式を書くのをやめてボタンをクリックし始める

リボンに複雑なロジックを処理させます

Power Query は、日常的なコマンドを簡単なクリックに変換します。サンプル データセットでは、名前に一貫性のない大文字とランダムなドットがあり、部門名の先頭に煩わしいスペースがあり、都市と州が 1 つの列に閉じ込められています。通常、これらの問題を解決するには複雑な数式を使用しますが、Power Query ではより簡単に問題を処理できます。

大文字を修正する

1. 右クリックして、 フルネーム 列ヘッダー。

2. クリック 変換 > 各単語を大文字にする。

3. 名前は一貫して大文字で表記されるようになりました。

ドットをスペースに置き換えます

1. をクリックします。 フルネーム 列ヘッダーをクリックして選択します。

2. で 変身 タブ、クリック 値を置き換えます。

3. で 見つける価値 フィールドに「a」を入力します 期間、そして、 次で置換 フィールドに「a」を入力します シングルスペース。

4. クリックすると わかりました、すべてのドットはスペースに置き換えられます。

先頭のスペースを削除する

1. 右クリックして、 部門 カラム。

2. クリック 変形 > トリム。

3. 目に見えない先頭 (および末尾) のスペースがすべて削除されます。

都市と州を分離する

1. を選択します。 市/州 カラム。

2. で 変身 タブ、クリック 列を分割 > 区切り文字で分割。

3. 選択 カスタム ドロップダウン メニューから、 カンマの後にスペース (、 ) をテキストボックスに入力し、クリックします。 わかりました。

4. ダブルクリックして、 列ヘッダー それらの名前を次のように変更します 市 そして 州。

クリーンなデータをワークシートにロードして戻します

データが洗練されたので、今度はデータを Excel ワークブックに戻します。これは、Power Query の「サンドボックス」作業が、使用可能な Excel テーブルに変わる瞬間です。

で 家 タブで、分割の上半分をクリックします 閉じてロード ボタン。 Power Query はエディターを閉じ、既定では、クリーンアップされたデータを含む新しいシートをブックに作成します。

ソース テーブルに新しい行を追加する場合、Power Query を再度開く必要はなく、単に すべて更新 で データ タブをクリックすると、Excel は新しいデータに対してすべてのステップを即座に再実行します。

データを読み込んだ後で、手順を見逃したことに気づいた場合、または新しい変換を追加したい場合でも、Power Query エディターでデータセットを再度開くことができます。クリック クエリと接続 で データ タブをクリックし、サイドバーでクエリをダブルクリックします。

[適用されたステップ]メニューを使用して、変更を監査および編集します

履歴をより良くするために「元に戻す」ボタンを廃止する

標準の Excel では、Ctrl+Z は、実行された順序とはまったく逆の順序で操作を 1 つずつ元に戻す直線的な元に戻す操作です。最後から 3 番目の移動が間違いだったと気付いた場合は、それ以降の移動をすべて元に戻して修正する必要があります。 Power Query では、これを[クエリ設定]ウィンドウの[適用されたステップ]セクション (ユーザーのすべての動きを記録するレシピ カード) に置き換えます。

1. 任意のステップを選択してください 正確な瞬間のデータがどのようになっていたかを確認するには、 × その特定のアクションを削除するには。
2. ステップを右クリックします そして選択します 名前の変更 明確なラベルを付けるため、または プロパティ さらに詳細な説明を追加します。

---

レベルアップ: Power Query を次のレベルに引き上げる

単一テーブルのクリーニングの基本をマスターしたので、データ クリーニングの可能性のまったく新しい世界が開かれました。データの破損を恐れる必要はもうありません。Power Query が、通常の Excel で数式を使用するよりも、わずか数回のクリックでより優れた処理を実行できることがわかりました。

しかし、そこで立ち止まる必要はありません。レベルアップする準備ができたら、同じ原則を使用してさらに大きなタスクを処理します。たとえば、Power Query を使用すると、複数のシートのデータを積み重ねてマスター レポートを作成したり、複数のブックのデータセットを結合してデータ収集を完全に自動操縦したりできます。 Power Query を使えば使うほど、Power Query なしで Excel をどうやって管理していたのか不思議に思うでしょう。