Excel power チュートリアル‌ クエリ (取得と変換)

あなたがデータを頻繁に扱う人であれば、誰でも (会計士、人事、データアナリストなど)、PowerQuery が強力なツールになる可能性があります。

本題に入りますが、Power Query は、学習する必要がある高度な Excel スキルの 1 つです。このチュートリアルでは、PowerQuery を詳しく調べ、PowerQuery を使用してデータを変換する方法を学習します。

始めましょう。

Excelパワークエリとは何ですか

Power Query は、ETL に使用できる Excel アドインです。つまり、さまざまなソースからデータを抽出し、変換して、スプレッドシートに読み込むことができます。 POWER QUERY は、データを変換するためのすべてのオプションを備えているため、データクリーニングマシンであると言えます。リアルタイムであらゆるステップを記録します。

Power Query を使用する理由 (利点)?

この質問を念頭に置いている場合は、次のような私の答えがあります。

さまざまなデータソース: CSV 、TXT、JSON などのさまざまなデータソースから強力なクエリエディターにデータをロードできます。
データを簡単に変換: 通常、データ変換には数式やピボットテーブルを使用しますが、POWER QUERY を使用すると、数回クリックするだけで多くの作業を行うことができます。
リアルタイムです。クエリを一度作成すると、データに変更があるたびにクエリを更新でき、更新した新しいデータが変換されます。

例を共有しましょう:

100 都市のデータを含む 100 個の Excel ファイルがあり、上司がそれら 100 個のファイルのすべてのデータを含むレポートを作成するように要求していると想像してください。各ファイルを手動で開き、これらのファイルからデータをコピーして貼り付けることにした場合、それには少なくとも 1 時間はかかります。

しかし、Power Query を使用すると、数分でそれを行うことができます。興奮していますか？良い。

このチュートリアルの後半では、多くの例を使用して Power Query の使用方法を学習しますが、最初にその概念を理解する必要があります。

電力需要の概念

パワークエリを学ぶには、次の 3 つのステップで機能するその概念を理解する必要があります。

1. データを取得する

Power Query を使用すると、Web、CSV、テキストファイル、1 つのフォルダーからの複数のブックなど、さまざまなソースからデータを取得したり、データを保存できるその他の多くのソースからデータを取得したりできます。

2. データを変換する

データをパワークエリに取り込んだ後は、データを変換してクリーンアップするために使用できるオプションが多数あります。実行するすべてのステップ (ステップごとに順番に) に対するクエリが作成されます。

3. データのロード

アドバンストクエリエディターから、変換されたデータをワークシートに読み込んだり、ピボットテーブルやピボットグラフを直接作成したり、データのみの接続を作成したりできます。

Power Query はどこにありますか (インストール方法)?

以下では、さまざまなバージョンの Microsoft Excel に Power Query Access をインストールする方法を示します。

エクセル2007

Excel 2007 を使用している場合は、申し訳ありませんが、このバージョンではPQ が利用できないため、 latest versionの Excel (Excel for Office 365、Excel 2019、Excel 2016、Excel 2013、Excel 2010) にアップグレードする必要があります。

Excel 2010 および Excel 2013

2010 および 2013 の場合は、このリンクからダウンロードできるアドインを個別にインストールする必要があります。インストールすると、以下のように Excel リボンに新しいタブが表示されます。

まずは、こちら（Microsoft公式サイト）からアドインをダウンロードします。
ファイルがダウンロードされたら、それを開いて指示に従います。
その後、Excel リボンに [Power Query] タブが自動的に表示されます。

何らかの理由でこの「POWER QUERY」タブが表示されない場合でも、心配する必要はありません。 COM アドインオプションを使用して追加できます。

[ファイル] タブ ➜ [オプション] ➜ [アドイン] に移動します。

「アドイン」オプションで「COM アドイン」を選択し、「GO」をクリックします。
その後、「Microsoft Power Query for Excel」ボックスにチェックを入れます。

ティックマーク-microsoft-power-query-for-excel-1

最後に、「OK」をクリックします。

Excel 2016、2019、Office 365

Excel 2016、Excel 2019 を使用している場合、または OFFICE 365 サブスクリプションを所有している場合は、「GET & TRANSFORM」という名前のグループとして[データ] タブにすでに存在します(この名前は気に入っていますね)。

エクセル・マック

Mac で Excel を使用している場合、残念ながら、これ用のパワークエリアドインはなく、既存のクエリを更新することしかできず、新しいクエリを作成したり、クエリを編集したりすることさえできません ( リンク)。

パワークエリエディタ

Power Query には独自のエディターがあり、データを取得し、クエリを作成するすべての手順を実行して、それをスプレッドシートに読み込むことができます。 Power Query エディターを開くには、[データ] タブに移動し、[取得と変換] ➜ [データの取得] ➜ [Power Query エディターの起動] に移動する必要があります。

以下は、エディタを開いたときに表示される最初のプレビューです。

それでは、各セクションを詳しく見てみましょう。

1.リボン

利用可能なすべてのタブを見てみましょう。

ファイル: [ファイル] タブから、データをロードし、エディターを削除し、クエリパラメーターを開くことができます。
ホーム: [ホーム] タブには、列や行の削除、移動など、ロードされたデータを管理するためのオプションがあります。
変換: このタブには、列の結合、転置など、データの変換とクリーンアップに必要なすべてのオプションが含まれています。
列の追加: ここでは、高度なエディターにあるデータに新しい列を追加するオプションがあります。
ビュー: このタブから、アドバンストクエリエディタービューとロードされたデータを変更できます。

2. 適用される手順

エディターの右側には、クエリ名とシーケンスに適用されるすべてのステップを含むクエリ設定ペインがあります。

ステップを右クリックすると、名前変更、削除、編集、上下への移動などを実行できるオプションのリストが表示されます。ステップをクリックすると、エディターによってそのステップで行われた変換が表示されます。

合計 5 つのステップすべてを適用した以下を見てください。4 番目のステップをクリックすると、列名が変更されていないステップ 4 の変換に移動します。

3. 質問

左側のクエリペインには、ワークブック内に現在存在するすべてのクエリがリストされます。基本的に、すべてのクエリを管理できる場所です。

クエリ名を右クリックすると、使用できるすべてのオプション (コピー、削除、複製など) が表示されます。

クエリペインの空のスペースを右クリックし、データソースのオプションを選択するだけで、新しいクエリを作成することもできます。

4. 数式バー

先ほども述べたように、エディターでステップを適用するたびに、そのステップの M コードが生成され、このコードが数式バーに表示されます。数式バーをクリックするだけでコードを編集できます。

M コードの使用方法を学習したら、コードを記述して「FX」ボタンをクリックするだけでカスタムステップを入力するだけでステップを作成することもできます。

5. データの概要

データプレビュー領域は Excel スプレッドシートのように見えますが、セルやデータを直接編集できる通常のスプレッドシートとは少し異なります。エディターにデータをロードすると (これについては後ほど説明します)、すべての列が列名を含むヘッダーとともに表示され、次にデータが含まれる行が表示されます。

各列の上部に、列のデータのデータ型が表示されます。エディターにデータをロードすると、アドバンストクエリは各列に正しいデータ型を (ほぼ毎回) 自動的に適用します。

列ヘッダーの左上にあるボタンをクリックして、列に適用されるデータ型を変更できます。これには、描画できるすべてのデータ型のリストが含まれています。

列ヘッダーの左側には、列の値をフィルタリングするために使用できるフィルタボタンがあります。注:列の値をフィルターすると、高度なクエリはそれを 1 つのステップとしてみなし、[適用されたステップ] にリストします。

列ヘッダーを右クリックすると、データを変換し、適用されたプロセスのステップとして保存されているオプションと PQ の 1 つを使用するために使用できるオプションのリストを含むメニューがあることがわかります。ない。

Power Query のデータソース

Power Query の最も優れた点は、複数のソースからデータを取得し、そのデータを変換してスプレッドシートに読み込むことができることです。「GET & TRANSFORM」で「Get Data」をクリックすると、エディターにロードできるデータソースの完全なリストが表示されます。

次に、データソースのいくつかを見てみましょう。

テーブル/範囲から: このオプションを使用すると、アクティブなワークシートから直接アドバンストクエリエディターにデータをロードできます。
ワークブックから: コンピューター上にある別のワークブックから。開くダイアログボックスを使用してこのファイルを見つけるだけで、このファイルからデータが自動的に取得されます。
テキスト/CSV から: テキストファイルまたはカンマ区切りファイルからデータを取得し、スプレッドシートにロードできます。
From Folder : フォルダーからすべてのファイルを取得し、強力なクエリエディターでデータをサポートします。 (こちらを参照してください: 1 つのフォルダーから Excel ファイルを結合する)。
Web から: このオプションを使用すると、Web アドレスからデータを取得します。Web 上に保存されているファイルがあるか、データを取得する必要がある Web ページがあると想像してください。

Power Query エディターにデータを読み込む方法

次に、強力なクエリエディターにデータを読み込む方法を学びましょう。ここには生徒の名前とその得点のリストがあります ( リンク)。

スプレッドシートからデータを直接ロードするため、最初にファイルを開いてから、次の手順に従う必要があります。

まず、Excel テーブルをデータに適用します (適用しない場合でも、データを PQ エディターにロードする前に Excel が自動的に適用します)。
次に、テーブル内のセルを選択し、「テーブル/範囲から」(「データの取得と変換」タブ)をクリックします。

ボタンをクリックすると、Excel は Excel テーブルを適用するデータ範囲を確認します。

この時点で Power Query エディターにデータがあり、次のようになります。

ここで見ることができます:
- 数式バーでは、PQ はエディターにロードしたばかりのテーブルの M コードを生成しました。
- エディターの左側にはクエリペインがあり、そこにクエリのリストが表示されます。
- 右側のクエリ設定には、「適用されたステップ」というセクションがあり、すべてのステップがリストされています。注: 「タイプの変更」を何も行っていないと思われますが、「タイプの変更」というステップがあります。 POWER QUERY のインテリジェンスについて説明します。エディターにデータをロードすると、すべての列に正しいデータ型が自動的にチェックされ、適用されます。

Power Query の例 (ヒントとコツ)

通常 Excel の関数式を使用して行う基本的なタスクのいくつかを実行する方法を学ぶことができますが、PowerQuery を使用すると、数回クリックするだけで実行できます。

1. 値を置き換える

値のリストがあり、1 つ以上の値を別の値に置き換えたいと考えています。 Power Queryを使用すると、クエリを作成してこれらの値をすぐに置き換えることができます。

以下のリストで、私の名前「Puneet」を「Punit」に置き換える必要があります。

まず、高度なクエリエディターでリストを編集します。
その後、Power Query エディターで [変換] タブに移動し、[値の置換] をクリックします。

「検索する値」に「Puneet」と入力し、「置換後の文字列」に「Punit」と入力して、「OK」をクリックします。

「OK」をクリックすると、すべての値が新しい値に置き換えられ、「閉じてロード」をクリックしてデータをスプレッドシートにロードします。

2. データの並べ替え

通常の並べ替えと同様に、PowerQuery を使用してデータを並べ替えることができます。ここでは、上の例で使用したものと同じ名前のリストを使用しています。

まず、強力なクエリエディターにデータをロードします。
「ホーム」タブには、2 つの並べ替えボタン (昇順と降順) があります。
これらのボタンのいずれかをクリックして並べ替えます。

3. 列の削除

どこかにデータがあり、そこからいくつかの列を削除する必要があるとします。重要なのは、新しいデータを追加するたびにこれらの列を削除する必要があるということですよね。ただし、power query を使用するとそれを解決できます。

削除する列を 1 つまたは複数選択します。

次に、右クリックして「削除」を選択します。

クイックヒント: 「他の列を削除」オプションもあり、選択されていない列をすべて削除できます。

4. 列を分割する

テキストを列に変換するオプションと同様に、パワークエリには「列の分割」があります。それがどのように機能するかを説明しましょう。

列を選択し、「ホーム」タブ ➜ 変換 ➜ 列の分割 ➜ 区切り文字での順に移動します。

ドロップダウンリストからカスタムを選択し、「-」を入力します。
ここでは、列を分割するための 3 つの異なるオプションがあります。
- 左端の区切り文字
- 右端の区切り文字
- 区切り文字が出現するたびに

セルに区切り文字が 1 つしかない場合は、3 つすべてが同じように機能しますが、複数の区切り文字がある場合は、それに応じて選択する必要があります。

5. 列の名前を変更する

列の名前を変更するには、右クリックして「名前の変更」をクリックします。

クイックヒント: 列の名前を変更するクエリがあり、他の人が誤ってその名前を変更したとします。ワンクリックでこの名前を復元できます。

6. 列を複製する

Power Query には、重複した列を作成する簡単なオプションがあります。複製列を作成したい列を右クリックし、「列の複製」をクリックするだけです。

7. 列または行を入れ替える

パワークエリでは、転置は簡単です。はい、ワンクリックするだけです。

データをアドバンストクエリエディターにロードしたら、必要なのは列または行を選択することだけです。
[変換] タブ ➜ テーブル ➜ 転置の順に移動します。

8. エラーの置換/削除

通常、Excel でエラーを置換または削除するには、検索と置換オプションまたは VBA コードを使用できます。しかし、PowerQuery ではそれがはるかに簡単です。エラーのある以下の列を確認し、削除して置き換えることができます。

列を右クリックすると、両方のオプションが表示されます。

エラーを置換する
エラーを削除する

9. データ型の変更

列にデータがありますが、その形式が正しくありません。したがって、その形式を変更する必要があるたびに。

まず、強力なクエリエディターでデータを編集します。
その後、列を選択し、「変換」タブに移動します。
ここで、データタイプからタイプとして「日付」を選択します。

10. 例から列を追加する

高度なクエリには、実際には現在の列に関連する例ではない例の列を追加するオプションがあります。

例を挙げてみましょう。

日付列から曜日の名前が必要だと想像してください。数式やその他のオプションを使用する代わりに、「例から列を追加」を使用できます。

その方法は次のとおりです。

列を右クリックし、「例から列を追加」をクリックします。

ここでは空の列が得られます。列の最初のセルをクリックして、挿入できる値のリストを取得します。

「日付からの曜日名」を選択し、「OK」をクリックします。

ブーム！新しいコラムはここにあります。

11. ボックスを変更する

PowerQuery でテキストの大文字と小文字を変更するには、次のオプションがあります。

小さい
大文字
各単語を大文字にします

これを行うには、列を右クリックし、上記の 3 つのオプションのいずれかを選択します。または、[変換] タブ ➜ [テキスト列] ➜ [形式] に移動します。

12. トリミングしてきれいにする

データを消去したり、不要なスペースを削除したりするには、PowerQuery の TRIM および CLEAN オプションを使用できます。手順は簡単です。

列を右クリックするか、複数の列がある場合はすべての列を選択します。
[変換] タブ ➜ [テキスト列] ➜ [形式] に移動します。
1. TRIM: セルの先頭と末尾から空白を削除します。
2. CLEAN: セルから非印刷文字を削除します。

13. プレフィックス/サフィックスを追加する

したがって、値のリストがあり、このリストから各セルに接頭辞/接尾辞を追加したいとします。 Excel では連結方法を使用できますが、PowerQuery では両方に使用できる簡単なオプションがあります。

まず、プレフィックス/サフィックスを追加する必要がある列を選択します。
次に、 [変換] タブ ➜ テキスト列 ➜ 形式 ➜ 接頭辞の追加/接尾辞の追加に移動します。

いずれかのオプションをクリックすると、テキストを入力するためのダイアログボックスが表示されます。

そして、テキストを入力したら、「OK」をクリックします。

14. 値の抽出

あなたが数式マニアなら、セルからテキストや数値を抽出するにはさまざまな関数を組み合わせる必要があるということに同意していただけると思います。しかし、PowerQuery はこれらの問題の多くを解決しました。セルから値を抽出するには 7 つの方法があります。

15. 日付または時刻のみ

日付と時刻の両方が 1 つのセルに含まれているにもかかわらず、どちらか一方が必要になることがよくあります。

日付と時刻を組み合わせた列を選択します。
あなたが望むなら：
- 日付: 右クリック ➜ 変換 ➜ 日付のみ。
- 時間: 右クリック ➜ 変換 ➜ 時間のみ。

16. 日付と時刻を結合する

これで、日付と時刻を区切る方法がわかりました。しかし次に、それらを組み合わせる方法を知る必要があります。

まず、日付列を選択し、「日付のみ」オプションをクリックします。
その後、両方の列 (日付と時刻) を選択し、[変換] タブに移動し、[日付と時刻の列] グループから [日付] に移動し、[日付と時刻を結合] をクリックします。

17. 四捨五入された数字

数値を四捨五入するために使用できるオプションは次のとおりです。

切り捨て: 数値を切り捨てること。
四捨五入: 数値を四捨五入します。
四捨五入: 小数点以下の桁をどこまで四捨五入できるかを選択できます。

手順は次のとおりです。

列を選択し、右クリック ➜ 変形 ➜ 丸めを選択します。
1. 切り捨て: 数値を切り捨てること。
2. 四捨五入: 数値を四捨五入します。
3. 四捨五入: 小数点以下の桁をどこまで四捨五入できるかを選択できます。

注: 「#3 四捨五入」オプションを選択した場合は、四捨五入する小数点以下の桁数を入力する必要があります。

18. 計算

計算を実行するために使用できるオプションがあります (多数あります)。これらのオプションはすべて、[変換] タブ ([数値列] グループ内) にあります。

基本
統計
科学者
三角法
ラウンド
情報

これらの計算を実行するには、列を選択してからオプションを選択する必要があります。

19. グループ化

大規模なデータセットがあり、要約テーブルを作成したいとします。行う必要があるのは次のとおりです。

[変換] タブで [グループ化] ボタンをクリックすると、ダイアログボックスが表示されます。

このダイアログボックスから、グループ化する列を選択し、名前を追加して、操作と値が含まれる列を選択します。

最後に、「OK」をクリックします。

注: 「グループ化」オプションには、マルチレベルのグループテーブルの作成に使用できる詳細オプションもあります。

20. 負の値を削除する

私のブログ投稿の 1 つで、負の符号を削除する7 つの方法をリストしました。パワークエリもその 1 つです。列を右クリックして「変換」オプションに移動し、「絶対値」をクリックします。

これにより、値からすべての負の符号が即座に削除されます。

他の例

スプレッドシートにデータをロードする方法

データを変換したら、それをスプレッドシートにロードして、さらなる分析に使用できます。ホームタブには「閉じてロード」というボタンがあります。これをクリックすると、追加オプションを提供するドロップダウンリストが表示されます。

閉じてロードする
閉じて読み込む

ボタンをクリックすると、次のオプションが表示されます。

このデータをスプレッドシートに表示する方法を選択します。
- 絵画
- ピボットテーブルレポート:
- テーブルピボット
- 接続を 1 つだけ作成する
- データをどこに置きますか?
  - 既存のワークシート
  - 新しいワークシート。
- このデータをデータモデルに追加します。
テーブルオプションと新しいワークシートを選択し、データモデルのチェックを外したままにして [OK] をクリックするだけです。

「OK」をクリックすると、データを含む新しいワークシートが追加されます。

さらに学ぶべき例

クエリを自動的に更新する

ここで引用したすべての例の中で、これが最も重要です。クエリを作成すると、クエリを自動的に更新できます (タイマーを設定できます)。

手順は次のとおりです。

[データ] タブで [クエリと接続] をクリックすると、ウィンドウの右側に [クエリと接続] ペインが表示されます。

次に、クエリを右クリックして「更新間隔」にチェックを入れ、分を入力します。

Power Query で数式と関数を使用する方法

Excel スプレッドシートで関数や数式を使用できるのと同じように、アドバンストクエリには使用できる関数の独自のリストがあります。 PowerQuery の関数と数式の基本は Excel のスプレッドシート関数と同じです。

PQ では、関数または数式を追加するには、新しいカスタム列を追加する必要があります。

例を見てみましょう: 以下のデータ (すでに PQ エディターにある) には、姓名 (ダウンロードリンク)があります。

2 つの名前を結合し、フルネームの列を作成する必要があると想像してください。この場合、簡単な数式を入力して 2 つの列の名前を連結できます。

まず、「列の追加」タブに移動し、 「カスタム列」をクリックします。

次に、カスタム列ダイアログボックスで、新しい列の名前「フルネーム」または新しい列に付けたい任意の名前を入力します。

- カスタム列の式には、式を入力する必要があります。したがって、以下の式を入力します。

 [First Name]&" "&[Last Name]

「カスタム列の式」に式を入力すると、PQ は入力した式をチェックし、「構文エラーは検出されませんでした」というメッセージを表示します。エラーがある場合は、エラーの種類に基づいてメッセージエラーが表示されます。
数式を入力し、エラーがなければ、そのまま「OK」を押します。
データの最後に、2 つの列 (名と姓) の値を含む新しい列ができました。