スプレッドシートは強力な分析機能を提供しますが、追加のインサイトが欠けているように感じることがあります。データが大量にある場合、基本的なスプレッドシートのビューから要約したり結論を導き出したりするのは困難です。
そこでピボットテーブルの出番です。ほとんどの Excel パワー ユーザーは、ピボット テーブルをパンとバターとして使用しています。ただし、 Google スプレッドシートでピボット テーブルを使用することもできます。
ここでは、Google スプレッドシートでピボット テーブルを作成する方法について説明します。手順に従うには、 デモ用のスプレッドシートをコピーし、チュートリアルを進めながら操作してみてください。
Microsoft Excel と Google Sheets のどちらにするか迷っていますか?Google Sheets vs. Excelのアプリ対決をチェックして、自分に最適なものを見つけてください。
Google スプレッドシートのピボット テーブルとは何ですか?
ピボット テーブルは、通常はスプレッドシート内の大量のデータを取得して要約します。
小さなスプレッドシートで作業している場合、ピボット テーブルは必要ない可能性があります。これは、通常、小さなデータ セットは、情報を理解するためにざっと目を通すのに十分シンプルであるためです。しかし、スプレッドシートが大きくなり始めると、結論を導き出すにはもう少し力が必要になります。そこでピボットテーブルの出番です。
このように考えてください。通常のスプレッドシートには、基本的に、水平 (列) と垂直 (行) の 2 つの軸で表される「フラット データ」があります。
以下の例では、各販売が独自の行として表示され、各列にはその販売に関する異なる情報が表示されます。
より多くのインサイトを得たい場合は、別のレベルでデータを追加する必要があります。これを行うには、テーブルの軸をシフト (またはピボット) する必要があります。以下の例に示すように、個々の売上ごとにデータを表示することはなくなりました。代わりに、集計データを確認します。各営業担当者が各製品を何個販売したかなどです。
これが大まかなアイデアだ。2次元のテーブルを取り、それを回転させて3次元を導入する。
数式を使用してこれらのインサイトの多くを引き出すこともできますが、ピボット テーブルを使用すると、ほんのわずかな時間でそれを抽出でき、人的エラーの可能性も低くなります。こうすることで、上司が同じデータ セットに基づく新しいレポートを要求したときに、最初から作成するのではなく、数回クリックするだけでレポートを生成できるようになります。
Googleスプレッドシートでピボットテーブルを使う方法
Google スプレッドシートのピボット テーブルの使い方を簡単に説明します。(さらに詳しい内容を学び、デモのスプレッドシートで練習するには、スクロールし続けてください。)
Google Sheets スプレッドシートを開きます。
使用するソース データを含むすべてのセル (列ヘッダーを含む) を選択します。
[挿入]をクリックし、 [ピボット テーブル]を選択します。
表示される [ピボットテーブルの作成 ] パネルで、ピボットテーブルを新しいシートに挿入するか、既存のシートに挿入するかを選択します。次に、 「作成」をクリックします。
ピボット テーブル エディターパネルで、行と列の横にある各行と列の追加をクリックします。これにより、分析するデータを選択できます。
同じパネルで、 [値]の横にある[追加]をクリックします。これにより、行と列内に表示する値を選択できます。
特定の条件を満たす値のみを表示する場合は、フィルターを追加します。
ソース データが変更されると、ピボット テーブルのデータも自動的に変更されます。ピボットテーブルに変更が反映されない場合は、ページを更新します。データの変更量によっては、更新に 1 分ほどかかる場合があります。
Googleスプレッドシートでピボットテーブルを作成する方法
Google スプレッドシートのピボット テーブルは、作成が簡単で、機能も豊富です。Google スプレッドシートでピボット テーブルを作成して使用する方法について、詳しく説明します。
列ヘッダーを含め、使用するソース データを含むすべてのセルを選択します。すべてのデータを一度にハイライトするには、キーボード ショートカットを使用します: Mac の場合は
command+A、Windows の場合はctrl+A(データ セットにヘッダーのない列が含まれている場合は、ピボット テーブルを作成するためにこれらの列に名前を付ける必要があります。)[挿入]をクリックし、 [ピボット テーブル]を選択します。
表示される [ピボットテーブルの作成 ] パネルで、ピボットテーブルを新しいシートに挿入するか、既存のシートに挿入するかを選択します。次に、 「作成」をクリックします。
Google スプレッドシートは、指定した場所（新規または既存のスプレッドシート）にピボット テーブルを作成します。そして、ここから作業することになります。
Googleスプレッドシートでピボットテーブルを編集する方法
Google スプレッドシートで初めてピボット テーブルにデータを入力すると、ピボット テーブル エディタパネルが自動的に表示されます。ここでピボット テーブルをカスタマイズしてさまざまなレポートを作成します。
エディターを閉じた後で再度アクセスする必要がある場合は、ピボット テーブルにマウス カーソルを合わせて[編集]をクリックします。
エディタでは、Google スプレッドシートの提案を使用するか、テーブルのサイズを手動で選択するかという 2 つの方法でデータを分析できます。
Google スプレッドシートのデータ分析の提案を使用する
Google スプレッドシートは Google 製品なので、ユーザーが知りたいと思う前に、ユーザーが知りたい情報を把握している可能性が高くなります。ピボット テーブル エディターパネルには、データ セットを分析するための推奨方法のリストが表示されます。(リストが表示されない場合は、[提案] の 横にある下向き矢印 [ ∨ ] をクリックします。)
たとえば、Google スプレッドシートでは、サンプル データ セットの例に対して次の分析を提案します。
各プロジェクト タイプの平均費やされた時間数
各プロジェクトタイプのクライアント名の数
各プロジェクトタイプごとに請求される金額の合計
提案されたオプションのいずれかをクリックすると、Google スプレッドシートによってピボット テーブルが自動的に作成されます。たとえば、 「プロジェクト タイプごとの請求金額の合計」をクリックすると、Google スプレッドシートでは、列 A にプロジェクト タイプが入力され、列 B にそれぞれの請求金額の合計が入力されます。
また、テーブルを変更する場合は、 ピボットテーブルエディター パネルで設定を編集できます。
データ分析をカスタマイズする
提案された分析が目的のものではない場合、または別の種類の分析を実行したい場合は、エディターで行、列、値、フィルターの 4 つのオプションをカスタマイズして、ピボット テーブルを手動で作成できます。
Googleスプレッドシートでピボットテーブルをカスタマイズする方法
Google スプレッドシートでピボット テーブルをカスタマイズする方法は、少なくとも19 、 329通りあります。各ピボットテーブル要素の使用方法を理解していただくために、ピボットテーブルを編集して、さまざまなプロジェクトタイプにわたる各クライアントに対して 2023 で請求された金額を分析する方法を紹介します。便利なことに(それは間違いなく設計によるものでした)、私たちが探している各要素(クライアント、プロジェクトタイプ、請求総額、年)は、エディターのオプションとうまく一致しています。
行と列は、 3 番目の次元の値を計算できる 2 次元データ セットの構築に役立ちます。この例では、基本データはクライアント名(行) とプロジェクト タイプ(列) です。
クライアント名と プロジェクトタイプ が一致するセルで取得する 値は 、合計請求金額です 。
フィルターを使用すると、特定のデータのサブセットのみを分析できます。この場合、 2023のデータのみを使用します。
レポートを作成する前に、空のピボット テーブルから始める必要があります。前の手順で Google スプレッドシートの提案された分析のいずれかを使用した場合は、 行 と 値 オプションの横にある X をクリックして選択をクリアします。
それでは、実際にこれを構築しましょう。念のため言っておきますが、これは私たちが尋ねている質問です。
さまざまなプロジェクト タイプにわたる各クライアントに対して 2023 でいくら請求しましたか?
ピボットテーブルに行を追加する
まず、クライアントとプロジェクトタイプの両方のリストを表示するようにテーブルを設定する必要があります。ピボット テーブル エディターパネルの[行]の横にある[追加]をクリックし、 [クライアント名]を選択します。
Google スプレッドシートは、クライアント名のソース データを自動的に取得し、重複を削除して、一意の値のリストをピボット テーブルの行として入力します。デフォルトでは、クライアント名はアルファベット順(AからZ)で表示されます。
もちろん、これまでに行ったのは、ピボット テーブルに既存の列を追加するだけです。レポートから本当に価値を得たいのであれば、さらにデータを追加する必要があります。
ピボットテーブルに列を追加する
次のステップでは、プロジェクト タイプを列として追加します。エディターで、 [列]の横にある[追加]をクリックし、 [プロジェクト タイプ]を選択します。これで、テーブルには、各プロジェクトタイプが列BからGまでの個別の列ヘッダーとして表示されます。
ピボットテーブルに値を追加する
ここで、各クライアントとプロジェクト タイプの合計請求金額を入力するために、Google スプレッドシートが必要です。
エディターで、 [値]の横にある[追加]をクリックし、 [請求金額]をクリックします。「集計方法」のドロップダウンメニューが「SUM」に設定されていることを確認します。この方法では、請求される合計金額と、たとえば平均請求金額との比較ができます。
現在、ピボットテーブルは、特定のクライアントに対して完了したプロジェクトの種類ごとに請求される合計金額など、いくつかの有用な情報を提供します。
また、各クライアントに請求された合計金額と、すべてのクライアントにわたる特定のプロジェクト タイプに対して請求された合計金額を把握できるように、Google スプレッドシートでは列 G で総計が自動的に計算されます。
ピボットテーブルにフィルターを追加する
最初の質問への答えまであと一歩ステップです。ここで、 2023値のみを表示するフィルターを追加する必要があります。(注: ソース データの任意の列に基づいてピボット テーブル内のデータをフィルターできます。)
エディターで、 [フィルター]の横にある[追加]をクリックし、 [年]を選択します。デフォルトでは、ソース データから使用可能なすべての年 ( 2022と2023 ) が選択されます。2022を選択解除し、 [OK]をクリックします。
それです！これで、「さまざまなプロジェクトタイプの各クライアントに対して2023でいくら請求しましたか?」という質問に答えるピボットテーブルができました。
Googleスプレッドシートのピボットテーブルの読み方
このセクションで作成したピボット テーブルを使用すると、ソース データに関するほぼすべての質問に答えられるようになります。これはピボットテーブルのプロになるための最後のステップです。
Google スプレッドシートのピボット テーブルの読み方を理解しやすくするために、さらに 2 つの例題に取り組みます。
2023で最も多く請求したクライアントはどれですか?
平均時給が最も高かったプロジェクトタイプはどれですか?
2023で最も多く請求したクライアントはどれですか?
この質問に答えるには、行としてのクライアントの名前と、値として請求された金額の合計のみが必要です。良いニュースは、これはピボットテーブルを簡素化する必要があることを意味します。
まだ表示されていない場合は、 ピボットテーブルエディタ パネルを開きます。
列セクションのプロジェクト タイプの横にあるXをクリックして、このオプションを削除します。
[行] セクションの [ クライアント名] オプションで、[並べ替え] の 下のキャレット(
∨) をクリックします。次に、 「請求金額の合計」をクリックします。
ピボットテーブルは自動的に更新され、クライアントごとに請求された金額の合計が昇順で表示されます。今、私たちは 2023年に私たちが最も多く請求した会社、合計1ドル、700。00、だった クエストインダストリーズ。
平均時給が最も高かったプロジェクトタイプはどれですか?
この質問に答えるために、分析を請求された合計金額から、各プロジェクトタイプの最高平均時給に移します。
始める前に、ピボット テーブル エディターパネルを開き、既存のフィルター以外のすべてのオプションを削除します。
注:ピボット テーブル エディターパネルでは、ソース データはオプションごとに 1 回だけ使用できます。たとえば、プロジェクト タイプを行として追加した場合、このデータを列に使用することはできません。
[行] の横にある[追加]をクリックします。次に、 「プロジェクト タイプ」をクリックします。
[値] の横にある[追加]をクリックします。次に、 「時給」をクリックします。
[値]セクションの[集計基準]で、下向き矢印 (
∨) をクリックし、 [平均]をクリックします。
今、私たちは答えを持っています:2023で請求された最高の平均時給は68ドルでした。コピー編集用の00。
Googleスプレッドシートでピボットテーブルを更新する方法
元のソースデータを編集したとします。これらの 変更は 、ピボットテーブルに自動的に反映されます。そうでない場合は、Web ブラウザの更新アイコンをクリックしてください。
ただし、元のソース データに新しい行または列のデータを追加した場合は、単純な更新ではうまくいきません。代わりに、ピボット テーブルのデータ範囲を更新して、新しい行または列を含めます。
ピボット テーブルにマウスを移動し、 [編集]をクリックします。
ピボット テーブル エディター の[データ範囲] フィールドに、新しいデータ範囲を入力します。
それです。
Google スプレッドシートでピボット テーブルを作成して使用する方法: よくある質問
Google スプレッドシートでピボット テーブルを使用する方法についてまだ質問がありますか?スプレッドシートを最大限に活用できるように、よくある質問への回答を確認してください。
Google スプレッドシートではピボット テーブルは自動的に更新されますか?
元のソース データのセル値を編集し、それらのセルがピボット テーブルで選択したデータ範囲内にキャプチャされている場合、これらの更新はピボット テーブルに自動的に反映されます。
ただし、ピボット テーブルで選択したデータ範囲外に新しい行または列のデータを追加する場合は、これらの新しい値を取得するためにピボット テーブルのデータ範囲を手動で更新する必要があります。
ピボットテーブルは複数のワークシートから取得できますか?
Google スプレッドシートのピボット テーブルは 1 つのワークシートのみを参照できます。ピボット テーブルで複数のワークシートのデータを参照する場合は、まずそのデータを 1 つのワークシートに結合する必要があります。その後、通常どおりに ピボットテーブルを作成できます 。
1つのシートに2つのピボットテーブルを含めることはできますか?
1 つの Google スプレッドシート ワークシートに複数のピボット テーブルを挿入できます。
通常どおりにピボットテーブルを作成します。
表示される [ピボット テーブルの作成 ] パネルで、[ 既存のシート] を選択し、新しいピボット テーブルを追加するワークシートとセルを入力します。
[作成]をクリックします。
2つのピボットテーブルをマージできますか?
Google スプレッドシートで 2 つのピボット テーブルを結合する方法はありませんが、回避策はあります。両方のピボット テーブルの元のソース データを 1 つのワークシートに結合し、新しいピボット テーブルを作成します。
Googleスプレッドシートを自動化する
ピボット テーブルを使用すると、複雑な数式を必要とせずに大規模なデータ セットを簡単に理解できます。基本をしっかり理解したら、Zapier を使用してGoogle スプレッドシートを他のよく使うアプリに接続し、スプレッドシート関連のタスクをさらに自動化できるようになります。たとえば、フォーム送信からの見込み客データと情報を既存のスプレッドシートに自動的に追加できます。
始めるための Zap テンプレートをいくつか紹介します。
ボタンをクリックするだけで、Zapテンプレート（Zapierが提供する既製のワークフロー）の使用を開始できます。セットアップは数分で完了します。Zapの設定方法について詳しくはこちらをご覧ください。
関連資料：
この記事は、ジョン・トーマスによって 2018 年9月に公開されたものです。最新の更新は2024年3月でした
