ExcelでPower QueryとPower Pivotをプロのように使う方法

スプレッドシートのパワーユーザーになりたいなら、ExcelのPower QueryとPower Pivot機能について学ぶ必要があります。Excelだけでも多くのことができますが、これらの組み込みの「Power」機能を使用することで、上級のプロフェッショナルなExcelユーザーに変身します。このガイドでは、Power Queryを使用して数百万行の異なるファイルをインポートし、Power Pivotを使用してインポートした大規模データモデルに対して複雑な分析を生成する方法を学びます。

目次

• 1. 複数のファイルからデータをインポートする
• 2. インポートしたファイルと行をフィルタリングする
• 3. フィルタリングしたデータをデータモデルに読み込む
• 4. ピボットテーブルでデータを分析する
• 5. PowerChartでデータを提示する
• よくある質問

あなたの作業を覗き見から守りたいですか？Excelワークブックにパスワードを追加する方法を学びましょう。

1. 複数のファイルからデータをインポートする

Power Queryを使用すると、単一のフォルダー内の異なるファイルからデータをインポートできます。ただし、すべてのデータが同じ形式に従っていることを確認する必要があります。列の数と列ヘッダー名を同じに保ち、各列の値のデータ型が一貫していることを確認してください。

このガイドでは、Kaggleからの約1億行のデータを含む5つのファイルを使用しています。続けて学ぶためにダウンロードするか、自分のデータを使用してください。それを念頭に置いて、データをインポートする手順は次のとおりです：

1. リボンの「データ」タブをクリックします。
2. 「データを取得 -> ファイルから -> フォルダーから」を選択し、フォルダーを参照して選択します。

1. 新しいウィンドウがポップアップします。私たちのケースでは、サイズに応じて名前が付けられた5つのテストファイルがあります：”adult10m”は1000万行のデータを持ち、”adult1m”は100万行、”adult100k”は10万行、などです。「データを変換」をクリックしてPower Queryエディターを開きます。

2. インポートしたファイルと行をフィルタリングする

Power Queryエディターでは、設定した基準に一致するファイルのみをインポートするためにフィルターを適用できます。これは、不要なデータソースを排除するのに非常に便利です。

1. 拡張子のないファイルが4つありますが、.DATA拡張子のファイルが1つあります。奇妙なものを削除するために、「拡張子」の隣にある矢印をクリックします。

1. すべてのファイルの拡張子のリストが表示されます。この例では、これらは（空白）と.dataだけです。不要な拡張子のチェックを外します。

1. 適用した各フィルターは「適用されたステップ」セクションに表示されます。
2. 「名前」の隣にある矢印をクリックしてファイル名でフィルタリングすることもできます。

1. 例えば、「adult100k」のように「k」を含むファイルをフィルタリングしたいとしましょう。これは、100万行未満の小さなファイルを扱いたくないからです。テキストフィルターを適用できます。多くのオプションがありますが、私たちの目的には「含まない」フィルターが適切です。

1. ファイル名に表示したくないテキストを入力します。

1. 右にスクロールして「作成日」と「変更日」列が見えるまでスクロールします。特定の時間範囲で作成されたファイルのみを選択するようにフィルタリングしましょう。「作成日」の隣にある矢印をクリックします。

1. 「日付/時刻フィルター -> の間」を選択します。

1. 2つの条件の時間値を入力し、「OK」を押して確認します。

1. ファイルごとのデータのフィルタリングが完了しました。
2. 残りのファイルを結合してデータ自体をフィルタリングできるようにしましょう。「コンテンツ」ヘッダーの隣にある二重矢印をクリックします。

1. 各行の個々の値が表示されるようになりました。各行には、元のファイルを示す追加の列「Source.Name」があります。

1. データをさらに改善するために：CSVファイルには元々列ヘッダー名がなかったため、いくつかの列ヘッダーを右クリックして「名前の変更」をクリックして適切な名前を付けることができます。

1. フィルターもここで機能します。数値範囲フィルターを適用できます。例えば、「年齢」列ヘッダーの隣にある矢印をクリックし、「数値フィルター -> より大きい」を選択します。

1. 30のような数値を入力し、「OK」をクリックします。指定した数値未満の年齢のすべての行が除外されます。

ヒント：Microsoft Excelでスプレッドシートを扱う際、列を移動する方法を知っておくと便利です。

3. フィルタリングしたデータをデータモデルに読み込む

データのクリーンアップとフィルタリングを続けることができますが、分析を開始するためにデータモデルに読み込みましょう。

1. Power Queryを終了するには、「閉じる & 読み込む -> 閉じる & データモデルに読み込む」をクリックします。
2. 開くポップアップで、「接続のみを作成」を選択し、「このデータをデータモデルに追加」をチェックします。

1. データが読み込まれるのを待ちます。大量のデータがある場合、数分かかることがあります。

ExcelでのPower Pivotデータモデルの使用

1. データの読み込みが完了したら、「データ -> データツール -> データモデルの管理」をクリックしてデータモデルに変更を加えることができます（新しい列を追加するなど）。

1. Power Pivotウィンドウが表示されます。この列にDAX（データ分析式）フォーミュラを追加しましょう。DAXフォーミュラは、あなたがすでに慣れているExcelフォーミュラに非常に似ています。データの最後の列が見えるまで右にスクロールし、「列を追加」をクリックします。
2. この列にすべての行に適用される簡単な計算を行うフォーミュラを書きましょう。Excelフォーミュラと比較して、DAXの構文はわずかに異なります。例えば、「Description」という列を追加し、フォーミュラ=CONCATENATE([Column9],[Column10])を入力します。角括弧（[ ]）の使用と列ヘッダー名を介したアクセスに注意してください。

1. 必要に応じて、列ヘッダーを右クリックして名前を変更したり、列を削除したりすることもできます。

4. ピボットテーブルでデータを分析する

データモデルへの変更が完了したら、Excelワークシートでピボットテーブルを使用してデータを分析する時間です。

1. Power Pivotウィンドウのリボンで「ホーム -> ピボットテーブル」をクリックして、「ピボットテーブルの作成」ウィンドウを開きます。

1. 「新しいワークシート」を選択し、「OK」を押します。

1. ピボットテーブルフィールドパネルがメインExcelウィンドウの新しいシートに開きます。検索バーの下のボックスに、データソースがリストされているはずです。この例では「kaggle_adult_csv」です。これをクリックして展開し、すべての列を表示します。

1. データ内の各「Description」の数をカウントする分析を行いましょう。作業したい列を選択するには、名前の横にあるチェックボックスをクリックします。「Description」を選択すると、焦点が自動的に下の「行」エリアに移動します。これはPower Pivotがその知性を示していることです - 選択に適切なエリアを正確に推測できます。空のピボットテーブルから始めているので、最初の選択が行であるのは理にかなっています。

1. チェックした「Description」列を「値」の空のスペースにドラッグします。Power Pivotは、データ型がテキストであるため、「Descriptionのカウント」を求めていることを再び正しく推測します。また、私たちのテーブルは、各「Description」値の出現回数を提供する列で更新されました。

1. カウントに別の次元を追加できます。例えば、「年齢」列を空の「列」エリアにドラッグして、年齢ごとの説明の数をカウントすることでテーブルに詳細を追加します。

1. テーブルにフィルター選択機能を追加するには、「WorkClass」列を「フィルター」エリアにドラッグします。テーブルの上に新しいインタラクティブなセルが表示されます。

1. 「すべて」の横にある矢印をクリックし、「Never-worked」を選択します。これにより、計算に「Never-worked」のWorkClassを持つ人々のみが含まれるようにテーブルが変更されます。フィルターはいつでも変更でき、異なる視点からテーブルを表示できます。

Excelワークブック内の多くの重複を処理していますか？重複を見つけて削除し、スプレッドシートをクリーンアップする方法を学びましょう。

5. PowerChartでデータを提示する

異なる方法でデータを分析するために、さらにワークシートを作成できます。今回は、PowerChartを使用して視覚的に印象的な結果を作成しましょう。

1. 「Power Pivot for Excel」ウィンドウに戻ります。閉じている場合は、「データ -> データツール -> データモデルの管理」からExcelウィンドウを開くことができます。
2. 「ピボットテーブル -> ピボットチャート -> 新しいワークシート」をクリックします。

1. 右側のパネルでデータソース（「kaggle_adult_csv」）をクリックして展開し、すべての列を表示します。
2. データソースから列を4つのエリアのいずれかにドラッグアンドドロップしてチャートを構築します。「OccupationType」列を「軸（カテゴリ）」エリアに移動します。

1. 再度「OccupationType」をドラッグし、今度は「値」エリアにドロップします。すぐに各職業の数を示す棒グラフが自動的に生成されます。

1. 「Ethnicity」列を「凡例（系列）」エリアにドラッグすることもできます。これにより、各職業を構成する各民族の数を比較するより詳細なチャートがすぐに表示されます。

1. 次に、凡例にフィルターを適用してチャートを簡素化し、特定の値のみを表示します。「Ethnicity」の上にマウスを移動すると、右側に矢印が表示されます。矢印をクリックします。

1. 削除したい値の横にあるチェックボックスをクリックし、2つだけを残します。

1. 新しいことを試してみましょう。「値」セクションの「OccupationTypeのカウント」をクリックし、「フィールドを削除」をクリックします。「年齢」列をドラッグアンドドロップして置き換えます。

1. 値が自動的に「年齢の合計」と定義されますが、これは実際の世界ではあまり役に立ちません。それを変更しましょう。クリックして「値フィールドの設定」を選択します。

1. 年齢は数値なので、このセクションに適用できる計算がたくさんあります。例えば「平均」を選択してみてください。

1. ビジュアルが更新され、特定の民族（フィルタリングしたもの）ごとの各職業の平均年齢が表示されます。

Excelの基本を復習したいですか？Excelのすべてのキーボードショートカットに関する包括的なガイドがあります。

よくある質問

Power Pivotの起源は何ですか？

Microsoftは、2010年にリリースされたMicrosoftのSQL Server 2008 R2によって提供される別のアドオンとしてPower Pivotを導入しました。当時は「PowerPivot」とスペースなしで呼ばれていました。2013年に「Power Pivot」と改名されました。2016年からExcelの組み込み機能として利用可能になりました。

データ分析式とは何ですか？Excelのフォーミュラとどのように異なりますか？

データ分析式は、ExcelのPower PivotおよびPower BIで使用されるフォーミュラ言語です。要するに、前述のアプリケーションで計算のためのフォーミュラを書く方法です：使用する文、構文、および関数。

DAXの主な利点は、集計データで動作するように設計されていることです。標準のExcelフォーミュラは、行ごとに計算を実行できます（例えば、行内の複数の値の合計を計算するなど）が、DAXフォーミュラは列ごとに動作したり（例えば、特定の値の数を列内でカウントするなど）、さらにはテーブル全体で動作することもできます。

「このテーブルはクエリによって作成されました。このテーブルを変更するには、代わりにクエリを変更してください」というエラーを修正するにはどうすればよいですか？

このエラーは、Power Pivot内でデータモデルを変更しようとしたときに発生しますが、そのデータモデルは元々Power Queryを介して読み込まれていました。これを行うことはできないため、元の読み込み方法であるPower Queryを介して変更する必要があります。

Excelの右側で「クエリと接続」アイコンをクリックし、クエリデータソースを見つけてダブルクリックすると、テーブルを変更できるウィンドウが開きます。

画像クレジット：Pexels。すべてのスクリーンショットはBrandon Liによるものです。