Excel Pivot Tables: Beginner to Advanced

私はまだ、データ分析を3年間間違った方法で行っていたことに気づいた瞬間を覚えています。それは2009年のことで、オハイオ州の中型製造会社の狭い会議室に座り、同僚のサラが50,000行のスプレッドシートを4分未満で包括的な売上分析に変換するのを見ていました。私は前の午後、手動で数式を作成し、シート間でデータをコピーし、6時間かけてチャートを作成していました。サラはピボットテーブルというものを使っていて、その瞬間、私のデータ作業に対するアプローチが根本的に変わりました。

今日、私は15年間、ファイナンシャルアナリストおよびデータコンサルタントとして、フォーチュン500企業から小さな非営利団体までさまざまな相手と仕事をしてきました。ピボットテーブルによって、多くの組織が何千時間もの時間と何百万ドルものコストを節約する姿を見てきました。私はExcelテクニックを2,000人以上の専門家に教えていますが、ピボットテーブルは私が教える中で最も変革的なツールです。それでも、Excelユーザーの30%未満が本当に効果的に使いこなせていると推定しています。

この記事は、その状況を変えようとする私の試みです。私は2009年に誰かが私に教えてくれたらよかったと思うことをすべてお伝えします−完全な基本から、同僚にデータウィザードのように見せることができる高度なテクニックまで。

ピボットテーブルとは何か、なぜ重要なのか？

意味のある定義から始めましょう: ピボットテーブルとは、大規模なデータセットを再編成し、分析するための動的な要約ツールで、数式を1つも書かずに行えます。それはデータ分析のためのスイスアーミーナイフのようなものです。データを様々な方法でスライス、ダイス、グループ化、フィルタリング、計算でき、すべてシンプルなドラッグ＆ドロップインターフェースを通じて行えます。

では、これが実際に重要である理由を見てみましょう。昨年、私は3年間にわたる127,000件の取引記録を持つ小売クライアントと仕事をしました。彼らのマーケティングディレクターは、次のような質問に答える必要がありました: 各地域で最も売れた商品は？ 季節のトレンドは何か？ どの顧客セグメントが最も収益を上げたのか？ 伝統的なExcelの数式と手動分析を使用していたら、これには数日かかっていたでしょう。ピボットテーブルを使うことで、これらすべての質問に約45分で答えることができました。

ピボットテーブルの真正な力は、その柔軟性にあります。新しいデータを追加すると壊れる静的な数式と違い、ピボットテーブルはワンクリックでリフレッシュできます。また、データを異なる方法で表示したいときに再構築が必要な手動の要約とは異なり、ピボットテーブルは即座に分析を再配置できます。私は、アナリストが数式ベースのレポートからピボットテーブルのダッシュボードに切り替えることで、月報の時間をフル2日から2時間未満に短縮するのを目にしました。

しかし、ほとんどの初心者が気づいていないのは、ピボットテーブルは大規模なデータセットだけのものではないということです。数百行のデータであっても、ピボットテーブルは時間を節約し、エラーを減らすことができます。私は個人の支出を分析するためから、クライアントのプロジェクト時間の追跡に至るまで、さまざまな用途でピボットテーブルを使用しています。論理を理解すると、ピボットテーブルを使用する機会がどこにでも見えてくるでしょう。

データの準備: 誰もがスキップする基礎

こちらがほとんどのピボットテーブルチュートリアルが失敗する理由です。彼らは、分析が簡単になるかどうかを決定するデータ構造の重要性を説明せずに、ピボットテーブルの作成に直行します。私は、アナリストがピボットテーブルで苦労する多くの企業と相談してきましたが、ツールを理解していなかったのではなく、データ構造が最初から誤って設定されていたのが原因です。

「ピボットテーブルは、本質的にデータ分析における手作業を90%排除する動的な要約エンジンです。数式で数時間かかることが、ピボットを使うと数分で済みます。」

ピボットテーブルに適したデータのための譲れないルールは次のとおりです。まず、データは適切なテーブル形式で、最初の行にヘッダーがあり、空の行や列がないことが必要です。すべての列には明確で記述的なヘッダーが必要です−「Column1」や空のセルではなく。かつて、私はクライアントのピボットテーブルをトラブルシューティングするのに3時間を費やしましたが、データセットの真ん中に空の1行があるのが原因で、すべての計算が失敗していました。

第二に、各列には1種類のデータのみを含めるべきです。これは明白に聞こえますが、誰かが情報を組み合わせて「Q1 2023」を1つのセルに入れるようなスプレッドシートを常に目にします。データが適切に分離されていると、強力な方法でグループ化やフィルタリングができます。結合されていると、限られた選択肢に縛られてしまいます。

第三に、データ範囲内でマージされたセル、サブトータル、フォーマットは避けてください。これらはピボットテーブルの敵です。私は美しくフォーマットされたレポートを持つ金融サービス会社と仕事をしましたが、20行ごとにマージされたヘッダーとサブトータル行がありました。人間の読者には素晴らしく見えましたが、ピボットテーブルには完全に使えませんでした。 meaningfulな分析を行う前に、すべてのフォーマットを取り除き、データ構造を再構築しなければなりませんでした。

ここに実用的な例があります。悪いデータ構造: 「Sales Info」とラベル付けされた列が「John Smith - $45,000 - Northeast」というテキストを含む。良いデータ構造: データを適切に分離した「Sales Rep」、「Revenue」、「Region」とラベル付けされた3つの別々の列。2番目のバージョンは初期のセットアップに30秒長くかかりますが、その後の混乱を何時間も節約します。

初めてのピボットテーブル作成: ステップバイステップの説明

初めてのピボットテーブルを一緒に作成しましょう。私は、先月完了したeコマース会社のプロジェクトに基づいた現実的なシナリオを使用します。5,000件の注文があるデータセットを想像してください。注文日、顧客名、商品カテゴリ、商品名、数量、単価、合計売上の列があります。

データ範囲内のどこかをクリックすることから始めます-全体のデータセットを選択する必要はありません。挿入タブに移動し、「ピボットテーブル」をクリックします。Excelは自動的にデータ範囲を検出し、ピボットテーブルを配置する場所を尋ねてきます。最初の数回のピボットテーブルでは、「新しいワークシート」を選択することをお勧めします。これにより、生データが分離され、壊れることを恐れずに実験しやすくなります。

OKをクリックすると、左側に空のピボットテーブル、右側にピボットテーブルフィールドペインが表示されます。ここが魔法が起こる場所です。フィールドペインにはすべての列ヘッダーが表示され、その下には4つのエリアがあります: フィルター、列、行、値。これらはあなたの分析の構成要素です。

簡単な質問に答えてみましょう: 各商品カテゴリの総売上は? 「商品カテゴリ」を行エリアにドラッグし、「合計売上」を値エリアにドラッグしてください。すぐに、Excelは各カテゴリの売上合計を計算します。数式も手動追加もなく、ただドラッグ＆ドロップです。これを初心者に見せたとき、彼らがどれだけの時間を無駄にしていたかを理解する瞬間を文字通り見ることができます。

さて、もっと面白くしてみましょう。「注文日」を列エリアにドラッグします。Excelは自動的に日付を月と年でグループ化し、時間の経過に伴う各カテゴリのパフォーマンスを示します。これは、従来の方法を使用すれば複雑な数式と複数のステップを必要とする分析ですが、ピボットテーブルを使えば約5秒で済みます。

私が苦労して学んだプロのヒント: 値エリアでExcelが使用している計算を常に確認してください。デフォルトでは、数値を合計し、テキストをカウントします。しかし、時には平均、パーセンテージ、その他の計算が必要です。値エリアでフィールドの横にある小さな「i」アイコンをクリックして、計算タイプを変更します。私は一度、設定を確認し忘れて「売上のカウント」を表示してしまったことがあり、実行時に恥ずかしい思いをしました。完全に防ぎうるものでした。