Excel でデータを簡単にクリーンアップおよび変換できる Power Query コマンド

Excelで大きな表を扱うのは、適切なツールを使わないと面倒な作業になりがちです。Power Queryを使えば、手作業で何度も手順を繰り返すことなく、データを迅速かつ正確にクリーンアップ・変換できます。空の列を削除したり、表を結合したり、数式を変換したり、重複値を除外したりしたい場合でも、専用のコマンドを使えば簡単に作業を完了できます。この記事では、データをプロフェッショナルに整理し、手作業による処理時間を大幅に節約するのに役立つ、Power Queryの必須コマンドをいくつかご紹介します。

Microsoft ExcelのPower Queryって聞いたことありますか？でも、Excelの上級者向けだと思っていましたか？いや、違います。実は、Power Queryは使いやすく、さらに重要なのは、データ整理の手間を大幅に省けるように設計されているんです。

通知
このガイドは、Microsoft Excel または Power Query を初めて使用する読者を対象としています。これらのヒントは、Power Query の数式言語の知識や使用を必要としません。この強力なデータ操作ツールで使用できるコマンドに慣れるための良い出発点となります。

1. 区切り文字を使用してセルを分割し、値を分離する

Excelには、テキストを複数の列に分割する方法がいくつかあります。テキストを複数の列に変換ツール、フラッシュフィル、組み込み関数などです。しかし、最も簡単な方法は、Power Queryエディターを起動してそこで操作を実行することです。

見つけたとしましょう Wikipediaの表 Power Queryを使って変換したい場合、テーブルをインポートするには、まずリボンの「ホーム」タブで「データの取得」をクリックし、「 その他のソース > Web から。

次に、ダイアログボックスのテキストフィールドにURLを貼り付けた後、ウェブから をクリックします "わかりました"、 ブラウザの左ペインでテーブルを選択し、クリックしました 「データの変換」。

テーブルを Power Query エディターに読み込むと、[キャリア スパン] 列に 2 つのデータ (各選手がキャリアを開始した年と最後にプレーした年) が含まれていることがわかります。これらの 2 つのデータを別々の列にする必要があります。

これを行うには、列ヘッダーを右クリックし、 「列分割」を選択し、「間隔に応じて。

通知
セパレーターは、シーケンス内の項目を区切るために使用される文字、記号、またはスペースです。

この時点で、Power Query エディターは列内のデータを確認し、改行の可能性をチェックします。この場合、各セルにダッシュが含まれていると判断され、ここがセルを分割する位置であると正しく判断されます。ただし、必要に応じて最初のドロップダウンメニューから別の改行を選択することもできます。

この例では、選択した列の各セルには区切り文字が1つしかないため、ダイアログボックスのオプションをさらに変更する必要はありません。OK「」をクリックし、Power Query エディターで新しく変換されたデータを確認します。

新しい列の名前を変更するには、列ヘッダーをダブルクリックし、新しいデータ ラベルを入力します。

これで、Power Query エディターを使用してデータを正常に分割できましたが、123 つの新しい列のセルの配置に違いがあることに気付きましたか？これは、「最初の実行」列の値がすべて数値（列ヘッダーの横にある XNUMX 記号で示されているように）であるのに対し、「最後の実行」列の値にはテキストと数値の両方が含まれているため、Excel はこの列をテキスト列として扱うためです（列ヘッダーに ABC 記号が表示されています）。

この問題を解決するには、[最終実行] 列で ABC シンボルを選択し、[整数] をクリックします。

これで、両方の列が数値データ セットとしてフォーマットされます。

ただし、「最終実行」列の一部のセルにテキスト値が含まれているため、エラーとして表示されます。ご安心ください。この問題を解決する方法については、以下をお読みください。

2. 計算に使用する誤差を代入します。

Excel の Power Query は、データ内に表示されるエラーを処理するための強力なツールです。

前の例のクエリを見ると、「最終実行」列の一部のセルにエラーが含まれています。これは、以前は整数形式の列にテキスト値「Present」が含まれていたためです。

現在、Power Query エディターの左上隅にある「閉じて読み込む」をクリックすると、結果のテーブルではエラー値を含むセルが空になります。これは理想的に見えるかもしれませんが、Excel 列の空セルは、データの並べ替えやフィルタリング、または数式で列見出しを参照する際に問題を引き起こす可能性があります。そのため、これらのセルには有効な値を入力することをおすすめします。

これを行うには、列見出しを右クリックし、 「エラーを置き換えます。」

通知
右クリックメニューから「エラーを削除」を選択することもできますが、これはエラーだけでなく行全体を削除するため、必要な場合にのみこのオプションをクリックしてください。

次に、「エラーの置換」ダイアログボックスで、エラーを置換する値を入力します。この場合、「Present」と入力されたセルにエラーが発生したため、現在の年を入力できます。

クリックするとOK「」、すべてのエラー値がこの新しい値に置き換えられます。

右側の列のすべてのセルにはエラーのない同じ種類のデータが含まれているため、新しい列を作成して各プレーヤーの合計プレー年数を計算できます。

ここでクリックしました カスタム列[列の追加] タブで、列名を「プレイした年数」に変更し、[使用可能な列] リストの列リストを使用して単純な減算を実行しました。

次に、[OK] をクリックすると、エラーのないこの新しい列がクエリの右側に追加されました。

完了したら、Power Query エディターの [ホーム] タブにある [閉じて読み込む] アイコンをクリックして、テーブルを Excel ブックの新しいスプレッドシートに送信します。

3. 分析を容易にするためにデータをデピボットする

Microsoft Excel で新しいデータセットを作成するときは、可能な限りレコード フィールドの原則に従います。

• 各行には、関連しているが異なるデータ型（レコードとも呼ばれる）のコレクションが含まれており、
• 各列には、各レコードに関連する 1 つの一意のデータ型 (フィールドとも呼ばれます) が含まれます。

この単純な例では、各国がレコードであり、その大陸、人口、通貨がフィールドです。

この形式を使用すると、データのフィルタリングや並べ替え、数式内の列見出しの参照、統計の視覚化などを簡単に行うことができるため、データをさらに分析するための準備が整います。

上記の例のテーブルと、各列 (フィールド) のデータ型が同じである次のスクリーンショットを比較してください。

その結果、データを年でフィルタリングすることは不可能であり、各企業の最も収益性の高い年を簡単に特定することはできません。こうした分析を行うには、データをデピボット（またはフラット化）する必要があります。これは、各年列を行に変換することで、幅の広い表から長い表へとデータを変換することを意味します。つまり、すべての財務値を単一のフィールドに格納する必要があります。

まず、データ内の任意のセルを選択し、リボンの [データ] タブで [テーブル/範囲から] をクリックします。

通知
この時点で、データが Excel テーブルとしてフォーマットされていない場合は、Power Query エディターがデータをより簡単に読み取ることができるようにこの問題を修正するように求めるダイアログ ボックスが表示されます。

次に、Power Query エディターで、ピボットを解除したいすべての列の見出しを選択します。この例では、「2020」という見出しをクリックし、Shiftキーを押しながら「2024」という見出しをクリックします。

次に、リボンの [変換] タブで、[列のピボット解除] ドロップダウン メニューを展開し、[選択した列のみをピボット解除] をクリックします。

現在、各列は個別のフィールド (会社、年、利益) であり、各行は会社の詳細な記録です。

最後に、列ヘッダーをダブルクリックして、列が表すフィールドと一致するように名前を変更し、数値書式設定アイコンをクリックして、各列に正しいデータ型が含まれていることを確認します。

さて、クリックすると 「閉じてロード」 リボンの [ホーム] タブでは、ピボット以外のテーブルが Excel ブックの新しいスプレッドシートに送信されます。

その結果、年別、企業別にデータをフィルタリングしたり、企業収益を降順に並べ替えるなど、テーブルを正規化する以前には不可能だったデータ分析が可能になります。

通知
元のテーブルにさらにデータを追加した後（例えば、年次データを含む別のテーブルなど）、Power Query で先ほど作成したテーブルに移動し、「クエリ」タブで「更新」をクリックします。Excel は新しいデータをピボットではない形式で結果テーブルに追加するため、毎回 Power Query エディターに戻る必要がなくなります。

4. 上のセル（または下のセル）に基づいて空のセルを入力します。

前述したように、データセット内の空のセルは、データの並べ替えやフィルタリング、列見出しを参照する数式の使用時に問題を引き起こす可能性があるため、入力することをお勧めします。

この例では、各チームの最初の選手にのみ列Aのチーム番号が割り当てられているため、データを並べ替えると、どの選手がどのチームに所属しているかがわかりません。また、現時点ではチーム番号でデータをフィルタリングすることはできません。

理想的には、セルA3からA5には数字の1、セルA7からA9には数字の2、といった具合に入力する必要があります。これらの数字を手動で入力するのは時間がかかり、特にこの例のようにデータセットに多数の行が含まれている場合はなおさらです。Power Queryを使えば、空のセルを数秒で埋めることができます。

まず、データ内の任意のセルを選択したあと、リボンの「データ」タブで「テーブル/範囲から」をクリックします。

Power Query エディターを開くと、問題の列の空のセルに「null」という単語が含まれていることがわかります。これを修正するには、列の見出しをクリックして選択し、「変換」タブで「塗りつぶし」をクリックします。

次に、データを降順で入力するか昇順で入力するかを選択します。「下へ」をクリックすると、選択範囲内で値を含むセルが検索され、その下の空白セルに同じ値が入力されます。「上へ」をクリックすると、値を含むセルの上にある空白セルに値が入力されます。この場合、各グループの最初の行にのみ数値が含まれているため、降順で入力する必要があります。

これで、各プレーヤーにチーム番号が正しく割り当てられたので、[ホーム] タブの [閉じて読み込む] をクリックして、クエリを新しいワークシートに送信できます。

この簡単だが重要な変更により、データセットを並べ替えたりフィルタリングしたりすることができ、どのプレーヤーがどのチームに所属しているかを把握できるようになります。

複数のExcelワークシートに同じ列見出しを持つテーブルが複数ある場合、Power Queryを使ってデータを1つのテーブルに統合できます。Power Queryエディターでクエリを結合する前に、各テーブル間にデータリンクを作成するという追加の（ただし簡単な）手順が必要です。

Power Query を効果的に使うのに、深いプログラミング知識は必要ありません。いくつかの基本コマンドをマスターするだけで、データの扱い方が大きく変わります。ここで紹介したコマンドを使えば、乱雑なデータを整理し、複雑な表を整理された形式に変換し、最小限の労力で正確な結果を得ることができます。一度試してみると、Power Query が Excel の操作を、単なるスプレッドシートから、柔軟でインテリジェントなデータ処理環境へと変革することを実感していただけるでしょう。