Excelのパワークエリ:データのインポート、クリーニング、結合、更新
By Mirko Peters · · Updated
Excelのパワークエリを使うと、数式を使わずにデータをインポートしてクリーニングできます。場所の確認、テーブルや範囲からの作成、適用したステップ、追加と結合の違い、読み込みと更新について解説します。
Automatically translated with AI from the original post. Read the English original
Excelのパワークエリは、[データ]タブの[データの取得と変換]グループにあるツールです。データに接続し、整形して加工し、結果をワークシートに読み込みます。クリックした手順は記録され、更新のたびに再実行されるため、毎月のクリーンアップが1クリックで済みます。この記事は、毎週同じエクスポートデータをコピーして貼り付け、修正しているExcelユーザーで、その作業をやめたい方を対象としています。開始場所、テーブルのクリーニング方法、追加と結合の方法、読み込みと更新の方法、MacとWebでの制限について説明します。
重要なポイント
- Excelのパワークエリは、[データ]タブの[データの取得と変換]グループにあります。データに接続し、整形して、ワークシートまたはデータモデルに読み込みます。
- 行った変更はすべて[適用したステップ]にステップとして記録されます。元のソースは変更されず、ステップは更新のたびに再実行されます。
- 「追加」は、同じ列を持つテーブルの行を積み重ねます。「結合」は、一致するキー列を基に2つのテーブルを結合し、対応する列のデータ型が同じである必要があります。
- [データ]、[すべて更新]の順にクリックすると、クエリを再実行できます。[接続のプロパティ]では、一定間隔で更新するか、ファイルを開いたときに更新するよう設定できます。
- Mac版のExcelとWeb版のExcelはPower Queryの一部の機能をサポートしています。どの機能を作成できるかについてMicrosoftのページには違いがあるため、お使いのバージョンで確認してください。
Microsoftは、Power Queryの作業を4つの段階で説明しています。データへの接続、変換、他のソースとの結合、そして読み込みです。元のソースはこの全過程を通じて変更されません。この点があるため、安心して試行錯誤できます。元にしたエクスポートデータを壊してしまうことはありません。
Power Queryとは何か、そしてどこにあるか
Power Queryは、グラフィカルエディターを備えたデータ準備エンジンです。MicrosoftのPower Queryの概要のページによると、抽出、変換、読み込みを行い、独自のストレージは持たず、出力先はホスト製品に任せています。Excelはそのホストの1つです。Power BI Desktopも別のホストで、両者は中核となるエディターを共有しています。コネクタや出力先はホストごとに異なる場合があるため、一方の製品で見たクエリが他方でも同じように表示されると想定しないでください。
Power Queryの場所はExcelのバージョンによって異なります。MicrosoftのExcelにおけるPower Queryの概要によると、次のとおりです。
- Excel 2016、2019、およびWindows版Microsoft 365。標準で組み込まれています。[データ]タブに移動し、[データの取得と変換]グループを確認してください。
- Windows版Excel 2010および2013。Power Queryは、独自のリボンタブを持つ無償のアドインでした。Microsoftは非推奨としていますが、引き続き動作する場合もあります。
- Mac版Excel。複数のソースからの更新をサポートしています。Mac版のExcel 2016および2019では利用できません。
- Web版Excel。一部のみサポートされています。詳細は後述します。
Microsoft では、Windows 上での要件として .NET Framework 4.7.2 以降と、Web コネクタ用の WebView2 Runtime も挙げられています。職場の PC でコマンドが見当たらない場合は、機能が削除されたと判断する前に、ビルドが最新かどうかを IT 部門に確認してください。
Excel で Power Query を使う方法: 最初のクエリ
最も簡単な始め方は、ブックに既にあるデータを使うことです。
- Excel のテーブル内の任意のセルをクリックします。データが通常の範囲である場合は、先に Ctrl+T を押してテーブルに変換してください。
- データ > データの取得と変換 > テーブルまたは範囲から の順に選択します。
- Power Query エディター が開き、データのプレビューが表示されます。
- 必要な変更を加えます(次のセクションを参照してください)。
- ホーム > 閉じて読み込む を選択します。
Microsoft の「Excel テーブルからのインポート」に関するページによると、結果は既定で新しいワークシートに出力され、元のシートは変更されません。別の出力先にしたい場合は 読み込み先 を使用してください。混同しないように、新しいシート名を変更しておきましょう。
別のデータ ソースに接続するには、データ > データの取得 を使用し、ファイル、データベース、オンライン サービス、Web などのソースを選択します。選択後にプレビューが表示され、読み込み(Excel に直接読み込み)か データの変換(先にエディターを開く)かを選べます。データがきれいに見える場合でも、ほとんどの場合は データの変換 を選ぶことをお勧めします。列の型を設定するのがエディターであり、型の誤りが後のほとんどの更新エラーの原因になるためです。
空の状態から始めたい場合は、同じページで データ > データの取得 > その他のソース > 空のクエリ が案内されています。また、データの取得の下にある Power Query エディターの起動 を使うこともできます。
エディターの基本
- データ プレビュー には、テーブルが表示され、変更を加えるたびに更新されます。
- クエリの設定 ウィンドウには 適用されたステップ が一覧表示されます。操作を行うたびにステップが追加され、「ソース」や「変更された型」などが並びます。ステップを選択すると、その時点のデータを確認できます。また、ステップの変更や削除も可能です。
- クエリ ウィンドウには、ブック内のすべてのクエリが一覧表示されます。プレビューの横にある矢印から開くことができます。
- 詳細エディター では、Power Query M 言語で記述された基になるスクリプトを確認および編集できます。Microsoft によると、インターフェイスで作成したすべてのステップは M で記述されます。このツールの価値を得るために、これに触れる必要はありません。
ステップでデータをクリーンアップし、再構成する
エディターで行う一般的なクリーンアップ作業には、行のフィルター、列の削除、データ型の変更、列の分割、値の置換などがあります。エディターはこれらの各操作をステップとして記録します。コマンドはエディターのリボンと、列ヘッダーを右クリックしたときに表示されるメニューにあります。表示される項目は列の型によって異なるため、リボンを探す前に列ヘッダーのメニューを確認してください。
ほとんどのエクスポート データに有効な手順は次のとおりです。
- 先に型を設定します。 各列見出しの横にある型のアイコンを確認してください。日付、数値、テキストは、後のステップやマージで異なる動作をします。
- 不要なものを削除します。 列が少ないほど結果は小さく高速になり、元データが変更されたときの予期しない問題も減ります。
- 行をフィルターします。 列見出しの矢印を使用して、空白や不要な値を削除します。
- テキストを修正します。 誤字や古いコードを置換したり、余分な空白を削除したり、2 つの情報を含む列を分割したりします。
- クエリ名を変更します。 後で見つけやすいように、クエリの設定ウィンドウでクエリ名を変更してください。
適用されたステップはリストになっているため、最初からやり直さずに、前の段階の誤りを修正できます。ソースの変更後にステップでエラーが発生した場合は、最初にエラーが出ているステップを確認してください。多くの場合、名前または型が変更された列が原因です。
列名を変更した場合の注意点があります。Microsoft の更新に関するページによると、新しいソース列は、ソース ステップを確認した後にのみ Power Query に表示されます。また、プレビューが古い可能性があるという黄色のバーが表示された場合は、ローカル キャッシュを更新する必要があります。新しい列が表示されない場合は、まずこの 2 点を確認してください。
データの結合: 追加とマージ
この 2 つのコマンドは役割が異なるため、混同されがちです。
Appendは行を縦に積み重ねます。毎月のエクスポートファイルのように、同じ形式の表が何度も出てくる場合に使います。Microsoftによると、Appendは1つのクエリの行を別のクエリの下に積み重ねる操作です。方式は2種類あります。インラインAppendは既存のクエリを基に処理し、中間Appendは毎回新しいクエリを作成します。
Mergeは、一致する値を基準に2つの表を横に結合します。ルックアップのような使い方です。クエリのマージに関するMicrosoftのページには、次の手順が示されています。
- 照合に使う列を持つクエリが少なくとも2つあることを確認します。照合する列は、テキストや数値など同じデータ型である必要があります。
- エディターでホーム > クエリのマージを選択します。結果は現在のクエリの新しい適用済みステップとして追加されます。別のクエリとして作成したい場合は、「クエリのマージ」の横の矢印を開き、新しいクエリとしてマージを選択します。
- 基準となるテーブルとそのキー列を選び、次に関連するテーブルとそのキー列を選択します。キー列が複数ある場合は、両方のテーブルで同じ順序で選択します。
- ダイアログに表示される一致件数を確認します。想定より少ない場合は、キー列の表記、空白、またはデータ型が異なっている可能性が高いです。
- 結合の種類(下記参照)を選択し、OKを選択します。
- 新しい列が追加されます。その見出しの横にある展開アイコンを選択し、追加したい関連列にチェックを入れます。
Microsoftによる結合の種類は次のとおりです。
- 左外部は、基準テーブルのすべての行と、関連テーブルで一致した行を残します。
- 右外部は、関連テーブルのすべての行と、基準テーブルで一致した行を残します。
- 完全外部は、両方のテーブルのすべての行を残します。
- 内部は、両方のテーブルで一致した行だけを残します。
- 左反対は、一致する行がない基準テーブルの行を残します。右反対は、一致する行がない関連テーブルの行を残します。
- 交差は、基準テーブルのすべての行と関連テーブルのすべての行を組み合わせます。
どちらのテーブルを先に選んだかによって左と右の意味が変わるため、テーブルの順序は重要です。ファジー一致を使用してマージを実行するのチェックボックスを使うと、近似値で一致させられます。Microsoftによると、この機能はテキスト列でのみ動作します。以前からルックアップを使っていた場合は、左外部結合のマージが最も近い代替手段です。また、左反対結合は、顧客レコードがない注文のように、一致しない行を素早く確認する方法になります。
Microsoftはプライバシーレベルにも言及しています。Power Queryは各データソースを「パブリック」「組織」「プライベート」に分類し、非公開情報が別のソースに意図せず漏れるようなデータの結合を防ぎます。マージでプライバシーレベルに関する確認が表示された場合は、内容をよく読んでから先に進んでください。
クエリを読み込んで更新する
ホーム > 閉じて読み込むでエディターを閉じます。クエリの作成、読み込み、編集に関するMicrosoftのページでは、次の出力先が説明されています。
- 閉じて読み込むは、結果をワークシートに出力します。
- 閉じて次に読み込むを使うと、データモデルなど別の出力先を選べます。複数のテーブルを基にピボットテーブルを作成する予定がある場合は、[データの取り込み]ダイアログでこのデータをデータモデルに追加するにチェックを入れます。
- 「読み込み先」が無効になっている場合は、「閉じて読み込む」を使用し、データ > クエリと接続 に移動します。「クエリ」タブでクエリを右クリックし、読み込み先 を選択します。
既定の設定を変更するには、ファイル > オプションと設定 > クエリのオプション > データの読み込みを選択します。標準の読み込み設定を使用することも、ワークシートまたはデータモデルへの読み込みを既定にするカスタム設定を行うこともできます。
後でクエリを編集するには、データ内のセルを選択してクエリ > 編集を選ぶか、クエリと接続でクエリを右クリックして編集を選択します。
更新
更新すると、現在のソースに対してすべてのステップが再実行されます。Microsoftの更新に関するページによる更新方法は次のとおりです。
- データ > すべて更新で、ブック内のすべてのデータが更新されます(Ctrl+Alt+F5)。その横の矢印から、選択した項目に対する更新を実行できます(Alt+F5)。更新を停止するにはEscを押します。
- 自動更新を設定するには、データ > クエリと接続を開き、接続タブで接続を右クリックしてプロパティを選択します。使用状況タブでは、更新間隔(分単位)とファイルを開くときにデータを更新するを設定できます。
- 同じタブにあるバックグラウンドでの更新を有効にするを選択すると、クエリの実行中も別の作業を続けられます。OLAP クエリや、データ モデルにデータを供給する接続では利用できません。
保存されたパスワードは暗号化されておらず、Microsoftも保存しないよう推奨しています。「定義」タブでパスワードを保存するのチェックを外すと、Excelはセッションごとに一度だけ確認を求めます。
ブックを他の人に送る場合、その人がデータ ソースにアクセスできる必要があるかもしれません。ご自身のドライブにあるファイルや、相手が開けないサイトを読み取るクエリは、相手のパソコンでは失敗します。データ ソースを共有の場所に置くか、読み込んだ結果を値として送信してください。
Mac 版 Excel と Excel for the web での Power Query
両プラットフォームでは一部の機能がサポートされていますが、Microsoft のページの記載は完全には一致していません。
Excel for the web。Microsoft のページによると、Excel ブック、Text/CSV、XML、JSON、SQL Server、SharePoint Online リスト、OData などのソースからインポートできるほか、空のテーブルや空のクエリも作成できます。データ > データの取得 > Power Query エディターの起動で Power Query エディターを開き、閉じて読み込むで読み込み、データ > すべて更新で更新できます。クエリの表示と更新はすべての Microsoft 365 サブスクライバーが利用できますが、それ以上の機能にはビジネス プランまたはエンタープライズ プラン(E3 以上)が必要です。一部の機能では、ブラウザーでサードパーティ製 Cookie を有効にする必要があります。
Mac 版 Excel。Microsoft のあるページでは、データ > データの取得 > データの取得 (Power Query) から始めること、クエリの作成には Microsoft 365 とバージョン 16.69 以降が必要なこと、データの変換でエディターが開くことが説明されています。同じページの別のセクションでは、Mac 版 Excel ではエディターでのクエリ作成はできず、Windows では VBA でクエリを記述し、Mac ではそのモジュールをインポートする方法が説明されています。この 2 つの記述は整合しないため、このやり方で業務プロセスを組む前に、ご自身の Mac で必ず検証してください。SQL Server からのインポートは、Insiders Beta 版でのみ説明されています。
Power Query では不十分な場合
率直に言うと、Power Query はデータの準備のためのもので、データを保存するためのものではありません。Microsoft によると、結果の出力先はホスト製品によって決まります。Excel の場合は、ワークシートまたはデータ モデルです。ワークシートには行数の上限があるため、非常に大きなクエリ結果はシートに収まりません。その場合はデータ モデルに読み込むか、データベースや専用のパイプラインに処理を移してください。
知っておくべきその他の制限は次のとおりです。
- クエリは実行時にソースを読み取ります。ブックにソースの変更がリアルタイムに反映されるわけではありません。
- 共同作業は扱いにくくなります。共有ブックで同じクエリを 2 人が同時に編集するのは避けてください。担当者を 1 人決め、クエリにはわかりやすい名前を付けましょう。
- 規制対象のデータを扱う場合、ブックは簡単にコピーできてしまうことを忘れないでください。そのようなファイルの保存場所については、組織のルールに従ってください。
よくある間違い
- データ型を確認せずに、なぜマージで一致しないのか悩むこと。
- 型が異なる列や末尾に空白がある列でマージすること。
- 追加のつもりでマージを使ったり、その逆をしたりすること。
- 接続のみのクエリやデータ モデルが適している場合に、すべてをワークシートに読み込むこと。
- 接続にソースのパスワードを保存すること。
- レポートを送る前に更新するのを忘れること。
- ソース ファイルを移動してから、クエリが失敗する理由がわからなくなること。
関連トピック
- エディターで重複を削除する。Power Query では、テーブルの重複削除を手順として記録できます。リボンの単発コマンドと異なり、同じ手順を何度でも再実行できます。
- 列の逆ピボット。ピボットテーブルを作成する前に、月の列を行に変換します。Microsoft もまさにこの用途として Power Query を挙げています。
- パラメーター。異なる入力で同じクエリを再利用するための、パラメーター クエリに関する Microsoft のページがあります。
- M言語。 Power Query Mのページから始めて、詳細エディターでスクリプトを読む方法を学びます。
- Power BI。 データがExcelの範囲を超えたときに、Power BI Desktopで同じエディターがどのように動作するかを学びます。
次のステップ
毎週手作業で整形しているエクスポートを1つ選びます。それをテーブルに変換し、「テーブルまたは範囲から」を実行して列の型を設定し、新しいシートに読み込みます。翌週は、新しいエクスポートをテーブルに貼り付けて「すべて更新」を押します。これがExcelのPower Queryの本来の働きです。
データソース
- Excel の Power Query について (Microsoft サポート)
- Power Query とは (Microsoft Learn)
- Excel テーブルからのインポート (Microsoft サポート)
- Excel でクエリを作成、読み込み、または編集する (Power Query) (Microsoft サポート)
- クエリの追加 (Power Query) (Microsoft サポート)
- クエリのマージ (Power Query) (Microsoft サポート)
- Excel で外部データ接続を更新する (Microsoft サポート)
- Excel for the web で Power Query を使用する (Microsoft サポート)
- Excel for Mac でのデータのインポートと整形 (Power Query) (Microsoft サポート)