【効率化ナレッジ】広告運用者が入稿時に使いたいピボットテーブルの活用方法

業務でデータ集計やレポート作成をおこなう際に、データ量が多くまとめるのに時間がかかって悩んではいませんか？数字が多く並んでいるのを見るだけでも頭が混乱してしまいますよね・・・。必要なデータのみを手作業でまとめると、工数がかかり他の業務に支障が出てしまうなんてことも。

そんなときは Excel の集計ツールの1つ、「ピボットテーブル」を使って簡単にすばやくデータをまとめてみましょう。

この記事では、ピボットテーブルの基本的な使い方や、レポーティングや分析での活用方法を紹介します。広告運用者の方であれば、広告ラベル別のパフォーマンス確認や推移データの確認などの大量のデータを扱う作業にも活用できます。

ピボットテーブルは、クリック、ドラッグ＆ドロップといった簡単な操作だけで作れるので、難しそうだなと感じていた方は記事を参考に是非チャレンジしてみてください。

ピボットテーブルとは

ピボットテーブルとは Microsoft Excel や Google スプレッドシート、Numbers といった表計算ソフトに備わっている集計・分析ツールの1つです。

記事内では Excel を例に使用方法を紹介しますが、他のソフトにも応用できる内容なので、Excel を使用しない方も参考になるはずです。

ピボットテーブルでできること

ピボットテーブルを使うと、大量のデータを自動で項目別に分類したり、集計することができます。単純な集計機能とは違って複数の項目をかけ合わせるクロス集計もできるため、データの分析の際に非常に役立ちます。

例えば、下記のような店舗別の売上が行ごとに月別で記載されているデータがあるとします。

店舗ごとのデータを見たいときは、フィルターや並び替えを使えば可能ですが、店舗別の総売上や月別の売上合計を見たいときは、毎回フィルターを掛け直すなどの作業が必要です。

ピボットテーブルを使えば、店舗別の売上合計や月別の売上を一覧化でき、データを可視化することができます。必要に応じて行や列も入れ替えることができるので、まとめて見たいデータをすぐに表示することができます。

ピボットテーブルの基本的な使い方

実際にピボットテーブルを作ってみましょう。まず、ピボットテーブルの集計対象にしたいデータのセルをすべて選択します。次に挿入タブの「テーブル」から、「ピボットテーブル」、「テーブルまたは範囲から」をクリックします。

ダイアログボックスが表示されるので、ピボットテーブルの対象データを示す「テーブル／範囲」が正しいかを確認します。範囲がずれている場合は、一旦キャンセルしてセルを選択し直すか、「テーブル／範囲」のセル番地を直接修正します。

ピボットテーブルを配置する場所は、特別な理由がなければ「新規ワークシート」を選択しましょう。ピボットテーブルと対象データが同じシート内にあると、ピボットテーブルの操作がしづらくなるためです。

「複数のテーブルを分析するかどうかを選択」は、対象にしたいデータを「データモデル」という Excel のデータベースのような機能に登録する場合にチェックをつけます。

データを登録しておくと、Excel の別のシートやブックでテーブルを作成する際にデータモデル内からデータを呼び出せるようになります。今回の記事ではデータモデルを使用しないため、チェックはつけずに OK をクリックします。

OK をクリックすると新規シート「Sheet2」が作成され、ピボットテーブル作成画面が表示されます。

画面右側のフィールドリスト（対象データ1行目の項目名）から、縦軸で見たい項目を「行」に、横軸で見たい項目を「値」のボックスにドラッグします。

項目名の左に表示されているチェックボックスを選択することでもボックスに追加することができます。ただし、この方法ではデータの種類が自動判別されて行や値のボックスに割り振られるため、意図しない場所に割り振られることがあります。使い慣れないうちは、ドラッグして割り振ることをおすすめします。

「フィルター」は行や値に設定した項目を絞り込みたい場合に、「列」は値を任意の項目で分割して見たい場合に設定します。今回の例では使用しません。

今回は広告ラベルごとのパフォーマンスを見るために「ラベル」を行に、インプレッション数、クリック数、コスト、コンバージョン数を値に設定します。

ピボットテーブル作成後、値の並びを変更したいときは簡単に並べ替えることが可能です。移動したい値の名前のセルをクリックし、値が表示されているセルとの境にポインタを置くと、十字のカーソルが表示されます。そのまま移動したい列にドラッグすると並び替えることができます。

ピボットテーブルのフィールドのボックス内で項目名をドラッグすることでも並び替えることは可能ですが、先に紹介した方法の方が視覚的に確認しながらおこなえるためオススメです。

使用上の注意

簡単に表を作成できるピボットテーブルですが、使用するうえで注意したいことがいくつかあります。

ピボットテーブルの作成には、対象データに見出し行が必要

ピボットテーブルを作成するには、対象データに見出し行（フィールドリストに表示される項目名）が必要です。

Google 広告や Yahoo!広告といった広告配信媒体からダウンロードするレポートのように、あらかじめ表示回数やクリック数といった項目名が入っているデータであればそのままピボットテーブルに使用できます。

項目名がないデータでピボットテーブルを作りたい場合は、自分で項目名を入れて見出し行を作成しましょう。

表記揺れがあると別のデータとして判別される

対象データに表記揺れがあるとピボットテーブル上で別のデータとして判別されるため、同じ項目や値として扱いたいデータの表記は統一する必要があります。

例えば「ラベル B」の B の部分の表記が全角と半角の2種類あると、ピボットテーブルで別のラベルとして表示されます。同じ種類として扱いたいデータは、半角か全角のどちらかに表記を統一しましょう。

値の計算方法が変わる場合がある

値に設定した項目は、対象データが数値であれば「合計」値が表示されます。ただし、対象データにテキスト値（テキストや空白など）が含まれる場合、「個数」が表示されます。

例えば、広告配信媒体からダウンロードしたレポートでコンバージョンが0の箇所に「-」が表示されている場合、ピボットテーブルを作成すると、コンバージョンの値にコンバージョン数の合計ではなく、データの個数が表示されてしまいます。

個数表示された値を合計に変更したい場合は、項目名をダブルクリック、もしくは右クリックし、「値の集計方法」から「合計」を選択します。OK をクリックすると、テーブル1行目の項目表示が「合計／コンバージョン数」に変わり、値も合計値が表示されるようになります。

対象データの変更は自動反映されない

ピボットテーブル作成後に対象データを編集しても、変更後のデータはピボットテーブルに反映されません。

ピボットテーブル上で右クリックし、ショートカットメニューで「更新」をクリックすれば変更を反映することができます。対象データを編集した後にピボットテーブルに反映したい場合は、忘れずに「更新」をおこないましょう。

csv 形式で保存すると情報が消える

仕様上ピボットテーブル作成後、csv 形式で保存して Excel を閉じると、ピボットテーブルのデータは消えてしまいます。

広告配信媒体からダウンロードできるレポートは csv 形式のデータであることが多いので、特に注意が必要です。

作成したピボットテーブルのデータを保持しておきたい場合は「Excel ブック（*.xlsx）」形式で保存しましょう。

広告運用で活かせるピボットテーブルの使い方

ここからは、広告配信データのレポート作成や広告入稿データのチェックでピボットテーブルを活用する方法を紹介します。

レポート・分析用データの作成

レポートや分析用にデータをまとめたいときに、手動でデータを分けたり関数を使って合計したりすると手間がかかってしまいますが、ピボットテーブルの行の設定やフィルター機能を組み合わせることで素早く正確にデータをまとめることができます。

期間別に配信結果を集計する

広告配信データを年や四半期など管理画面では分割できない場合は、管理画面から月や日単位で「任意の期間で分割したレポート」をダウンロードして、ピボットテーブルを作成します。

例えば、管理画面から「日」で分割したデータをダウンロードし、ピボットテーブルで「日」の列を含めて対象データを選択し、ピボットテーブルを作成すると、キャンペーンごとの結果（表示回数やクリック数など）を日別で見ることができます。

行に「日」を設定し、値にはデータを見たい項目を設定します。下のキャプチャでは表示回数、クリック数、費用、コンバージョンを値に設定しています。

もとは日別且つキャンペーン別に分かれていたデータを、日別ごとにまとめることができます。

キャンペーンや広告グループごとに広告配信結果のデータをまとめる

レポートや分析のために、キャンペーンや広告グループといった階層別でデータをまとめたい場合も、ピボットテーブルを活用すると簡単に表を作成することができます。

行に複数項目を設定し、キャンペーン別かつ広告グループ別でデータを集計する

ピボットテーブルの行には、値と同じく複数の項目を設定することができます。データを複数の項目で分割した表を作成したいときに便利です。

例えばキャンペーン別かつ広告グループ別のパフォーマンスを見たい場合は、管理画面から広告グループで分割したデータをダウンロードし、ピボットテーブルを作成します。

キャンペーンを行に設定し、その下に広告グループを設定します。値にはデータを見たい項目を設定します。下のキャプチャではインプレッション数、クリック数、コスト、コンバージョン数を値に設定しています。

データをキャンペーンや広告グループ含む他の軸で見たいときにフィルターを使う

キャンペーン数が多くて見づらい場合や、特定のキャンペーンのパフォーマンスだけを確認したい場合は、ピボットテーブルを作成したあとで、フィルターを併用しましょう。

ピボットテーブル上で行ラベルのプルダウンをクリックします。「フィールドの選択」で絞り込みたい対象（ここでは「キャンペーン名」）が選択されていることを確認します。

自分が見たいキャンペーンを選択します。検索ボックスに入力した値が含まれるデータがリストで表示されるので、意図した結果になっているか確認します。過不足がある場合は、検索する値を変更するか、チェックボックスで直接選択します。

OK をクリックすると、選択したキャンペーンのデータだけが表示されます。

フィルターを解除したい場合は、行ラベルの横に表示されているフィルターマークをクリックし、「キャンペーンからフィルターをクリア」をクリックします。

入稿前の csv データをチェックする

csv で広告の入稿データを作成した時に、広告エディターや管理画面にインポートしてみてから意図しない広告データを作成してしまっていることに気づく場合もありますよね。

ピボットテーブルは数値の合計を確認する以外にも、データの個数を数える使い方もできます。この使い方は、広告の入稿作業で csv データを作成した時に、入稿件数が正しいかを入稿前にチェックできます。

例えば、新規広告を広告グループに1件ずつ追加で入稿したい場合、1つの広告グループに正しい広告データが1件ずつ作成できているかをひと目で確認することができます。

確認したい入稿データを使って、行に広告グループ、値に広告名を設定したピボットテーブルを作ります。これで、どの広告グループに、広告データが何件（何個）あるかを確認することができます。

さらに行に広告名を追加することで、各広告グループに入っている広告名も確認することができます。これにより、誤った広告を設定していないか、判別することができます。

広告グループに1件ずつ広告を追加したい場合、ピボットテーブルの「個数 / 広告名」（B列）が各広告グループで1となっていれば、正しい入稿データといえます。キャプチャでは、広告グループ「東京」と「北海道」が2件、「大阪」は空欄（0件）となっているので、東京、北海道、大阪の入稿データが誤っていることがわかります。

行ラベルに（空白）とでているのは、広告名が空白であることを指しています。また、「個数 / 広告名」（B列）が空欄のものについては後述します。

入稿先の広告グループを確認する

ピボットテーブルでデータの個数を確認する方法は、上記のように入稿件数を確認する以外にも、広告の入稿先の広告グループが間違っていないかの確認にも活用することができます。

ピボットテーブルを使えば、どの広告グループにどの広告データが作成されているか一覧で確認できるため、csvデータやエディターで1件ずつデータを絞り込んで確認する必要がありません。

確認したい入稿データを使って、行に広告タイトル1、列に広告グループを設定したピボットテーブルを作ります。値には広告グループを設定します。値は自動判別で「個数」が表示されます。

この作業をすることで、広告グループごとにどのタイトルのデータが何件（何個）あるかを確認できます。入稿件数の確認はもちろん、入稿先の広告グループが間違っていないかも確認することができます。

例えば、行ラベルでタイトル1を「北海道」に絞り込んでみます。この状態で、「北海道」以外の広告グループに1以上の値が表示されていると、間違った広告グループに北海道用の広告が入稿されようとしているのに気づくことができます。

ピボットテーブルをより使いやすくする機能の紹介

基本的な使い方をするだけでも便利なピボットテーブルですが、細かい設定や機能を利用すると、更に効率よく作業を進められます。ここからは、私がレポート作成時によく使用する機能を紹介します。

ピボットテーブル内で計算する

ピボットテーブル作成後、まとめたデータから更にコンバージョン単価やクリック率といった数値を計算したいこともあると思います。しかしピボットテーブル以外のセルに関数を入力して計算するのは手間がかかってしまいます。

集計フィールド機能を使えば、ピボットテーブル上で数式を使った計算結果を表示することができます。

ピボットテーブル分析タブの計算方法グループで、「フィールド／アイテム／セット」から「集計フィールド」を選択します。

集計フィールドの挿入ダイアログで、名前には追加したい項目名、数式には任意の数式を入力します。

今回は CPA（コンバージョン単価）を求めたいので、名前に「CPA」と入力します。

数式には「=」を入力した後、「フィールド」のリストから「コスト」を選択して「フィールドの挿入」をクリックします。

次に割り算を表す「/」を入力し、「フィールド」のリストから「コンバージョン」を選択して「フィールドの挿入」をクリックし、OK をクリックします。

これでピボットテーブルの値に CPA を追加することができました。小数点以下の数値が表示されているなど、表示形式が整っていない場合は、通常のセルと同じ操作で表示形式を整えることができます。

値の入っていないセルに任意の値を設定する

値がないセルを含むデータを使ってピボットテーブルを作成すると、ピボットテーブル上にも値がないセルができることがあります。

ピボットテーブルで表示したデータを他のレポートに使いたい場合など、空白があると体裁が悪かったり、不便なときがあります。そんなときは、ピボットテーブルの機能で代わりに表示する値を設定しましょう。

ピボットテーブル上で右クリックをしてピボットテーブルのオプションを表示し、書式の「空白セルに表示する値」に任意の値を入力します。今回はH列の値がないセルに0を表示させたいので、0を入力します。

書式の「空白セルに表示する値」を使えば、値がないセルに手動で値を入力する手間が省けます。

選択したセルの行に追加した項目でフィルターをかける

行に設定した項目が多い場合、フィルターをかけようとするとプルダウンからどの項目でフィルターをかけるか選ばないといけないため、少し面倒です。

以下のキャプチャでは、行にキャンペーン名、広告グループ名、ラベルを設定しています。ラベルでフィルターをかけたい場合、フィルターのプルダウンに表示される項目の中から「ラベル」を探す必要があり、やや手間がかかります。

そんなときは、テーブル上でフィルターをかけたい項目をクリックしてからフィルターのプルダウンを表示すると、クリックした項目がはじめから選択された状態でプルダウンが表示されます。

例えばラベルでフィルターをかけたい場合、テーブル上のいずれかのラベル名をクリックしてからフィルターボタンをクリックすると、ラベルが選択された状態でプルダウン表示されます。

ピボットテーブルを使いこなして効率よくデータと向き合おう

ピボットテーブルを使用すると、大量のデータを扱うときも効率よくまとめられ、頭の中での整理がしやすくなります。ぜひ活用して、効率的に業務をすすめていただければ幸いです。

また、今回紹介した機能以外にも便利な機能がたくさんあるので、業務に合わせていろいろな使い方を試してみてください。