マスター ピボット テーブル:高度な計算フィールドとマルチソース インサイト

ピボット テーブルはデータ分析に強力であり、計算フィールドや複数のデータ ソースなどの高度なテクニックを使用すると、さらに多くの洞察を得ることができます。この記事では、計算フィールドとピボット テーブルでの複数のデータ ソースの使用に焦点を当て、高度なピボット テーブルのテクニックを説明および示します。

ピボット テーブルでの計算フィールドの作成

計算フィールドは、既存のデータに基づいてピボット テーブル内にカスタム計算を作成します。計算フィールドはピボット テーブルに新しい列を追加し、値は既存のフィールドから導出されます。これは、元のデータセットを変更せずにデータの計算を実行したい場合に便利です。

ピボット テーブルの計算フィールドを使用して、売上データセットから売上に対する 10% の手数料を計算してみましょう。計算フィールドを使用するには、まずピボット テーブルを作成する必要があります。

ステップ 1:ピボット テーブルを作成する .

• データ範囲を選択します。
• 挿入に移動します タブ>> ピボット テーブルを選択します .
• ピボット テーブルを配置する場所 (新しいワークシート/既存のワークシート) を選択します。

次に、 必要なフィールドを行にドラッグ アンド ドロップします。 、コラム 、価値観 、フィルター

ステップ 2:計算フィールドを挿入する .

• ピボット テーブル内の任意の場所をクリックします。
• ピボットテーブル分析に移動します。 タブ>> フィールド、項目、 セット から>> 計算フィールドを選択します .

• 計算フィールドの挿入 ダイアログボックス:
  • 名前 ボックスに、計算フィールドの名前を入力します:販売手数料 (10%)
  • 数式内 ボックスに次の式を挿入します:
    • =0.01 * 「総売上高」
  • [追加 ] を選択します。>> [OK] をクリックします。 ピボット テーブルにフィールドを追加します。

新しい計算フィールド 販売手数料 (10%) が追加されました。 列がピボット テーブルに追加されます。

ヒント: 計算フィールドは加算的な計算に最適に機能しますが、非加算的な計算やより複雑な計算の場合は、Power Pivot を使用してください。

複数のデータ ソースからピボット テーブルを作成する

複数のテーブル (またはソース) のデータを 1 つのピボット テーブルに結合すると、テーブルを手動で結合することなく、より包括的なデータセットを分析できます。

複数のデータ ソースがあると仮定します。1 つは販売情報を含む販売データ、もう 1 つは製品情報を含む製品データです。ピボット テーブルでこれらの複数のデータ ソースを使用する方法を説明します。

方法 1:Excel の組み込みデータ モデルを使用する

データ モデル機能を使用すると、ピボット テーブルで複数のデータ ソースを使用できます。 Excel 2013 以降のバージョンでは、この組み込みのデータ モデル機能が提供されます。

ステップ 1:Excel で各テーブルを設定する .

• セル範囲を選択>> 挿入に進みます。 タブ>> テーブルを選択します .

• わかりやすくするために、各テーブルに名前を付けることができます (例:Sales_Data) Sales テーブルと Products_Table 用 Products テーブルの場合)。

ステップ 2:データ モデルを使用してピボット テーブルを挿入する .

• テーブル を選択します。>> [挿入] に移動します。 タブ>> ピボットテーブルを選択します .
• [ピボットテーブルの作成 ] で ダイアログ ボックス、
  • ピボット テーブルを配置する場所 (新しいワークシート/既存のワークシート) を選択します。
  • [このデータをデータ モデルに追加する] をクリックします。 .
  • [OK] をクリックします。 .

ステップ 3:関係を構築する .

• データ に移動します。 データ ツールのタブ>>>> 関係を選択します .

• 関係の管理 ダイアログボックス:
  • [新規] をクリックします。 をクリックし、Product_ID を選択します。 両方の売上から と製品 関係を確立するためのテーブル。
  • [OK] をクリックします。 関係を維持するため。

ステップ 4:ピボット テーブルを作成する .

• 次に、ピボットテーブルのフィールドに移動します。 ペインに、両方のテーブルのフィールドが表示されます。
• カテゴリをドラッグします と製品 Product_Table から 行へ .
• 販売単位をドラッグします 売上データから テーブルから列へ .
• ドラッグ総売上高 Sales_Data から 値の表 製品ごとの合計数量と売上を確認するには

リレーションシップを設定すると、ピボット テーブルで両方のテーブルからフィールドを取得し、手動で結合することなく売上概要、製品名、カテゴリを表示できるようになります。

方法 2. 複数のデータ ソースに Power Pivot を使用する

Excel の Power Pivot アドインは、Excel 2016、2019、Office 365 Professional および Enterprise などのバージョンで利用でき、さまざまなソースからのテーブル間のリレーションシップを作成できます。このアプローチにより、複数のテーブルに基づいてピボット テーブルを作成し、それらのテーブル全体で分析を実行できます。

ステップ 1:Power Pivot を有効にする (まだ有効になっていない場合)。

• ファイルに移動します タブ>> オプションを選択します>> アドインを選択します .
• ウィンドウの下部にある管理 ドロップダウン>> COM アドイン を選択します>> [移動] をクリックします。 .
• Microsoft Power Pivot for Excel をチェックします。>> [OK] をクリックします。 .

ステップ 2:Power Pivot にデータを読み込む .

• データに移動します タブ>> データの取得を選択します さまざまなソース（複数のシート、外部データベース、CSV ファイルなど）からデータをインポートする
• データ ソース (Excel ワークブック、SQL Server、テキスト/CSV など) を選択し、[読み込み先] をクリックします。 .
• データのインポートから ダイアログボックス:
  • テーブルを選択します .
  • 新しいワークシートを選択します。 .
  • [このデータをデータ モデルに追加する] チェックボックスをオンにします。 .

注意 :ロードする各テーブルには、テーブル間の関係を有効にするための一意の識別子 (製品 ID や顧客 ID など) が必要です。

ステップ 3:テーブル間のリレーションシップを作成する .

• Power Pivot に移動します。 タブ>> [管理] を選択します。 Power Pivot エディタを開きます .

• ダイアグラムビューに移動します すべてのテーブルを視覚的に確認するには。
• テーブル間でフィールドをドラッグ アンド ドロップして、リレーションシップを作成します。 プロダクト ID を接続します。 売上表から プロダクト ID へ 商品テーブル内 ).
• ホームに移動します Power Pivot のタブ>> ピボットテーブルを選択します .
• ピボット テーブルを配置する場所 (新しいワークシートなど) を選択し、[OK] をクリックします。 .

• ピボット テーブルのフィールド リストには、すべてのテーブルのフィールドが表示されます。これらのフィールドを使用して、複数のソースからデータを取得するレポートを作成します。
  • 製品をドラッグします 製品テーブルから 行へ .
  • 販売単位をドラッグします 売上データから コラムへ .
  • 総売上高をドラッグします 売上データから 価値観へ .

結論

これらの高度なテクニックを使用すると、複数のデータ ソースの分析と統合をカスタマイズして、ピボット テーブルをさらに洞察力のあるものにすることができます。計算フィールドをマスターし、Excel のピボット テーブルで複数のデータ ソースを統合すると、複雑なデータセットを効率的に分析することができます。これらのテクニックを使用すると、より迅速に分析情報を取得し、データをより効率的に管理できます。

 
Pixabay からの Councilcle による画像

ソリューション付きの高度な Excel 演習を無料で入手しましょう!