マスターピボットテーブル:強力なデータ洞察のための 5 つの高度なテクニック
ピボットテーブルは Excel の最も強力なデータ分析ツールの 1 つであり、対話型のダッシュボードの作成に役立ちます。しかし、ほとんどのユーザーは自分の可能性を最大限に活用することがほとんどできません。ピボットテーブルは、単純な合計とカウントの操作を超えて、生データをインタラクティブで洞察力に富んだレポートに変換できる高度な機能を提供します。
このチュートリアルでは、データのより深い洞察を引き出すための 5 つの重要なピボットテーブル トリックを検討します。
サンプル データセット (売上テーブル)
Excel に売上データがあると想像してください。このデータセットは以下のすべての例で使用します。
データをテーブルに変換する:
- データセット全体を選択します。
- 挿入に移動します タブ>> テーブルを選択します .
- 「テーブルにヘッダーがある」 にチェックを入れます。 .
- [OK] をクリックします。 .
- テーブル デザインに移動します。>> テーブルに「SalesData」という名前を付けます。
基本的なピボットテーブルを作成する:
<オル>
ピボットテーブル フィールドから リスト:
- 領域をドラッグします 行へ .
- カテゴリをドラッグします 列へ .
- 収益をドラッグします 価値観へ .
これで、地域別およびカテゴリ別の収益に関する標準レポートが作成されました。私たちはそれをもっと賢いものに変えていきます。
トリック 1:インタラクティブなフィルタリング用にスライサーを挿入する
スライサーは、クリック可能な対話型フィルタリング ツールです。静的なレポートやダッシュボードを対話型のレポートやダッシュボードに変えることができます。スライサーはすべてのフィルタリング オプションをボタンとして表示するため、データ探索が直感的かつ迅速になります。
手順:
<オル>
Excel には、ボタン付きの 3 つのスライサー ボックスが表示されます。
- 営業担当者をクリックしてピボットテーブルをフィルタリングし、その担当者のデータのみを表示します。
- Ctrl を押したままにします クリックしながら複数の名前を選択します。
- スライサーのじょうごアイコン (右上) をクリックしてフィルターをクリアします。
スライサーを複数のピボットテーブルに接続する:
最も強力な機能の 1 つは、単一のスライサーを複数のピボットテーブルに接続できることです。
- スライサーを選択します。
- 右クリック>> [レポート接続] を選択します。 .
- すべてのピボットテーブルを選択>> OK をクリックします .
これが、実際のダッシュボードを 1 枚のシートに構築するための鍵です。スライサーを使用すると、マネージャーはピボットテーブルのフィールドに触れることなく、営業担当者、顧客、または製品ごとの指標をすばやく確認できます。
スライサーの外観のカスタマイズ:
- スライサーを選択します。
- スライサーに移動します タブが表示されます。
- さまざまなスライサー スタイルから選択します ギャラリーで。
- 列を調整します ボタンを複数の列に表示します。
- スライサー設定を使用する (右クリック> [スライサー設定]) して、ヘッダー キャプションを変更します。
- スライサーのサイズと位置を変更して、きれいなダッシュボード レイアウトを作成します。
トリック 2:タイムラインを使用して時間の傾向を即座に分析する
タイムラインは、日付フィールド専用に設計された特殊なスライサーです。ピボットテーブル データを期間でフィルタリングできるため、日、月、四半期、年をすばやく切り替えることができます。
手順:
- ピボットテーブルをクリックします。
- ピボットテーブル分析に移動します。 タブ>> タイムラインの挿入を選択します .
- 日付を選択します>> 次に、[OK] をクリックします。 .
日付範囲を示す水平バーが表示されます。
- タイムラインのプルダウンを使用して、年、四半期、月、日の間でレベルを切り替えます。
- 左または右のハンドルをドラッグして、特定の期間を拡大します。
- 2025 年 1 月などの単一の月をクリックすると、その月の売上だけが表示されます。
- タイムラインのフィルター アイコンをクリックしてフィルターをクリアします。
売上動向を瞬時に確認できます。スライサーと組み合わせると、「1 月の Alex の北部地域の電子機器の売上はいくらでしたか?」などの質問に答えることができます。数回クリックするだけで完了します。
トリック 3:データを年齢層または売上層にグループ化する
グループ化により、データをより大きく、より意味のあるバケットに分類できます。これは、分布の分析、年齢層の作成、範囲によるセグメント化、日付のカスタム期間への整理などに非常に役立ちます。
顧客を年齢層にグループ化する:
- 新しいピボットテーブルを作成します。
- 顧客の年齢をドラッグします 行へ .
- 収益をドラッグします 価値観へ .
- 右クリック 行ラベルに任意の年齢を入力し、グループを選択します。 .
- 「グループ化」ダイアログで、次のように設定します。
- 開始日: 20
- 終了時刻: 70
- 作成者: 10
- [OK] をクリックします。 。ピボットテーブルには年齢層が表示されます。
これで、どの年齢層が最も多くの収益を生み出しているかを観察できるようになりました。最大の注文は年配の顧客からですか、それとも若い顧客からですか?
グループ収益を売上金額帯に分割:
- 別のピボットテーブルを作成します。
- 収益をドラッグします 行へ .
- 収益をドラッグします 価値観へ .
- 領域をドラッグします 列へ .
- 行ラベル内の収益値を右クリックし、グループ… を選択します。 .
- 妥当な範囲を設定します:
- 開始日: 0
- 終了時刻: 40000
- 作成者: 2000
- [OK] をクリックします。 .
次のバンドが表示されます。
商品の価格階層:
- 開始日: 0
- 終了時刻: 500
- 作成者: 100
例:0～100 (予算)、100～200 (エコノミー)、200～300 (スタンダード)、300～400 (プレミアム)、400～500 (高級)。
トリック 4:計算フィールドを使用してカスタム指標を構築する
場合によっては、生のフィールドだけでは十分ではありません。計算フィールドを使用すると、既存のピボットテーブル フィールドに基づいた数式を使用して新しいフィールドを作成できます。つまり、利益、利益率、差異、コンバージョン率、その他のカスタム指標をピボットテーブル内で直接計算でき、ソース データに追加の列は必要ありません。
手順:
- ピボットテーブル内の任意の場所をクリックします。
- ピボットテーブル分析に移動します。 タブ>> フィールド、項目、 セットをクリックします。>> 計算フィールドを選択します .
- 計算フィールドの挿入 ダイアログ:
- 名前を入力します あなたの分野（例:「利益率」）。
- 数式をクリックします。 ボックスをクリックして数式を作成します。
- フィールド リストからフィールド名をダブルクリックして挿入します。
- 標準演算子を使用します:+ (加算)、− (減算)、* (乗算)、/ (除算)。
- [OK] をクリックします。 .
利益:
- 名前: 利益
- 式:
利益率の割合:
- 名前: 利益率
- 式:
= (Revenue - Cost) / Revenue
- パーセンテージとしてフォーマットします .
収益の 30% として「利益」を生み出す:
- 名前: 収益の 30% としての利益
- 式:
計算フィールドを使用すると、ピボットテーブル内でビジネス固有の KPI を定義できます。 SalesData にさらに行を追加して更新すると、計算フィールドは自動的に更新されます。
トリック 5:「値の表示形式」でパーセンテージと累計を表示する
ピボットテーブルには通常、生の数値が表示されますが、多くの場合、相対的なビューのほうが洞察力が高まります。ピボットテーブルには、合計のパーセント、行/列のパーセント、一定期間の累計など、いくつかの表示オプションが用意されています。
地域別の総収益の割合:
- リージョンを使用してピボットテーブルを作成する 行と収益 値で。
- 「収益」列の任意の数値を右クリックします。
- 値の表示方法を選択します。>> 総計の % を選択してください .
各地域が収益全体に占める割合を表示するようになりました。セグメントを比較する場合、生の金額よりもはるかに解釈しやすくなります。
以下を選択することもできます。
- 列合計の% 各領域をその列のパーセンテージとして表示します。
- 行合計の% 各カテゴリを各地域内のパーセンテージとして表示します。
ヒント:明確な分析情報を得るにはピボットテーブルの書式設定を使用します
適切にフォーマットされたピボットテーブルにより、分析情報が理解しやすくなります。
数値形式の追加:
- 任意の値を右クリック>> 数値形式>> 通貨、パーセンテージなどを選択します。
トップ 5 の貢献者を表示:
<オル>条件付き書式を使用する:
<オル>洞察: 最高のパフォーマンスを強調表示すると、ベストセラー商品や最も強力な地域を素早く特定するのに役立ちます。
すべてをまとめる:シンプルなインタラクティブな販売ダッシュボード
これら 5 つのトリックを使用すると、1 つのワークシート上にミニ ダッシュボードを構築できます。要素を 1 つのシートにまとめて配置して、まとまりのあるダッシュボードを作成します。
- 営業担当者と顧客のスライサーを使用した地域別およびカテゴリ別の収益
- トリック 3 を使用してグループ化された年齢層別の収益。
- タイムラインは、すべての時間ベースのピボットテーブルの日付範囲を制御します。
- スライサーはレポート接続を使用してすべてのピボットテーブルに接続されます。 なので、ワンクリックでビュー全体が変更されます。
- 計算フィールドには、すべてのレポートにわたる利益と平均価格が表示されます。
すべての設定が完了したら、新しい行を SalesData に貼り付けて [更新] をクリックするだけです。 。ピボットテーブル、計算フィールド、スライサー、タイムライン、グループ化は自動的に更新されるため、レポートを再構築しなくても新しい洞察が得られます。
結論
これら 5 つのピボットテーブル テクニックを使用すると、データのより深い洞察を引き出し、静的レポートを対話型の分析ダッシュボードに変換できます。スライサー、タイムライン、計算フィールド、およびグループ化を使用すると、パターンをより迅速に解釈し、結果を明確に表示できます。独自のデータを使用して練習し、ダッシュボードでそれらのデータを組み合わせて実験してください。お使いのバージョンの Excel に機能がない場合 (タイムラインには Excel 2013 以降が必要です)、更新があるかどうかを確認してください。
ソリューション付きの高度な Excel 演習を無料で入手しましょう!-
Excel で空白行を削除する (8 つの適切な方法)
Excel ワークシートに一連のデータがあり、不要な空白行がいくつかある場合を想像してください。このような予想外の空き列は、誰にとっても迷惑であり、作業に支障をきたし、作業のペースを妨げていることは間違いありません。そのため、Excel でこのようなデータセットを操作する前に、これらの不要な空白行を削除したいと考えています。 . Microsoft Excel には、このタスクを実行するための多数の手法と方法があります。そのうちの 8 つを例と適切な図で示します。 次の練習用ワークブックをダウンロードして、それに沿って練習することをお勧めします。 Excel で空白行を削除する 8 つの効果
-
Microsoft Office Online Review
Microsoft Office Onlineは、ワードプロセッサ、スプレッドシート、プレゼンテーションプログラムで作成されたファイルを編集および共有したり、OutlookやOneNoteにアクセスしたりできるため、無料のMSOfficeの代替手段として機能します。 オンラインバージョンのOfficeを介して行われることはすべて、Webブラウザーを介して実行され、オンラインで保存されるため、どこからでもファイルにアクセスできます。 概要 私たちが好きなもの ソフトウェアのダウンロードはありません。 MSOfficeデスクトップアプリがサポートするすべてのファイルタイプを開きます。