【愕然…】私の集計速度速すぎ？超速エクセル集計術【ピポットテーブル篇】

基本の「き」費用対効果の見方

広告の成果を見る方法はたくさん。 そのなかでも多く使う指標をまずは確認していきましょう。

さて、上記のものをみて察したかもしれません。 「計算式」がある、と。 そう、どうしたって費用対効果を調べるには計算式が必要です。 しかし、対象はいつだってバラバラ。商品が10種類それぞれの費用対効果を算出するならまだ手計算でいけるかもしれません。 でもたとえばそれが、商品が100あったら。500あったら、1000あったらもう無理でしょう。忙しいマーケ担当者がそんなところに時間をかけている場合ではありません。 そこでおすすめするのがエクセルの「ピポットテーブル」です。

ピポットテーブルより前に、まずはデータを整えよう！エクセル集計のはじめの一歩

とりあえず、データは出力した！けど、商品が異様に細かく分かれている。 もう大きなカテゴリでみないと母数が少なくて数値としてみれない。 なんてことありますよね。 例えば、上記だったら東日本と西日本で分けたい！なんてことがあるかもしれません。 その場合は…… 列ごとコピー！ 項目名が入った行を選択した状態で、フィルタをクリック 新しくできた列に、大カテゴリをいれてあげたりしましょう。 項目を昇順に並べ替えたり、手作業が嫌なひとはVLOOKUP関数を使ったりすると便利ですね。 ちょっとしたひと手間ですが、このひと手間で表が一気に見やすくなります。 「男性」「男」など項目の表記ゆれもここで直しておくとよいでしょう。 ココで便利なのが「置換」機能。 詳しくは後述をみてください。

「ピポットテーブル」の使い方をゆっくり解説

データが整ったら、該当のデータ一覧を選択し、上のラベルから「挿入」→「ピポットテーブル」を選択。 新規ワークシートにチェックが入っていることを確認し、「OK」を選択。 新しいシートがつくられるので、ここで集計したい項目を選択。 これだけで簡単に東日本と西日本の注文数がわかります。 チェック項目を増やせば、さらに詳細な分類もできます。

ここだけは気をつけたい、あるある集計ミスまとめ、ピポットテーブルの注意点

うわ、ピポットテーブル便利！と思った方は多くいるでしょう。 でも世に普及していないのはそれなりに理由があり、それは「ピポットテーブル」の設定の仕方を間違えるとうまく計算出来ないことがあるからです。 えー嫌だと思った方、大丈夫です。 押すべきポイントだけを押さえれば正しい数字を出すことができます。

あるある①　CPAがなんだかおかしい……？

出力した元データですでにコンバージョン単価が計算されていました。 しかしあきらかに数値がおかしい？！ ここでいうと、コンバージョン単価（＝費用／注文数）の数値が正しくありません。 だがこれは設定ミスではなく、ピポットテーブルが数値の合計を出してしまっているところに問題があります。

上記のような、基本データがあったとします。 正しくは、 果物　費用100円　CV2　CPA50円 となるのが正しいはずです。 しかしこの「合計／コンバージョン単価」は、元のデータのりんごCPA50円+みかんCPA50円で「CPAの合計は100円」という計算をしてしまっているのです。 そりゃ変な数字がでますよね……。 そのため費用、コンバージョン件数など単純な数の合計ではない項目は、ピポットテーブルで項目にチェックするのではなく、改めて計算をしなおす必要があります。

あるある②　なぜかピポットテーブルを指定した状態で計算ができない。

CPAは費用÷獲得件数だから、=C4／B4といれて、 あとは╋キーで下までフィルで計算すればいいじゃん♪ ……なぜか、一番上のセルの答えしかでない。 こういった場合には、以下の3つの解決方法があります。

こうなってしまうのはピポットテーブル特有の設定があるのですが、長くなるので細かい説明を省きます。

ピポットテーブルの便利機能、集計フィードの使い方

ピポットテーブルのどこかを選択。 右上に表示されるラベルの「ピポットテーブルツール」の「フィード/アイテム/セット」の「集計フィード」を選択。 名前に自分が計算したい項目、ここだと「CPA」と記入。 数式に。CPAの計算に必要な項目を「フィード」から選択して、「/」「*」など計算式を淹れて「追加」。 最後に、「OK」を押します。 するとあら不思議！ CPAが計算された列ができます。 ①や②の方法だと、集計する項目やカテゴリを変えると計算しなおしたり、貼り付けしなおしたりしないといけないデメリットがありますが、集計フィードを使うことで、選択項目に自動的に計算された数値が表示されるようになります。

あるある③　元のデータのほうの編集しても、ピポットテーブルの数値が更新されない。

例えば、愛知県の数値に誤りがあり、別シートの数値を変えたとしましょう。 しかし、ピポットテーブルの数値がかわらない……。 この場合、上部のラベルの「ピポットテーブルツール」の「分析」の「更新」をクリックすると、ピポットテーブルが更新されます。 同じ原理で、例えば元データがはってあるシートに列を追加したりしても、自動的に更新はされません。 その場合はデータソースの更新をおし、ピポットテーブルが参照している元データの範囲を設定しなおしてあげてください。

応用編　これができたら敏腕Webマーケターその他の使える指標

今回はCPA（コンバージョン単価）という、1件あたりの獲得単価を例にだしましたが、指標はそのほかにもたくさんあります。 どんどんスピードを上げましょう。

おまけ　エクセルを使う上でこれだけは使えておこう。

「置換」機能 ある特定の文字を、別の文字に置き換えてしまう機能です。

ショートカットキー（Ctrl+H）を押します。 「検索と置換」というウィンドウがでてくるので、「検索する文字列」に県をいれます。 ※県を、ケンとかＫＥＮに変えたいのだったら「置換後の文字列」に何かを入れればいいのですが、今回は「県」を消す（＝県をという文字を何もない状態と置換する）必要があるため何もいれません。 その後、「すべて置換」をクリック。 無事、81件の「県」を消すことができました。 この機能はいろいろと応用がきき、例えば、「Ｇ列にある県だけを置換したい！」となった場合には、Ｇ列を選択した状態で置換機能を使えば他の部分に影響を与えません。 そのほか関数の中まで置換してくれるので、関数を誤った際などに大活躍してくれます。 基本的なエクセル集計についてのまとめはコチラ↓

更新日：2018.03.20

データ分析にかかる時間を１/５にするエクセル時短テクニック！

デジタルアスリート株式会社

1,890社以上の支援実績と最新のWebマーケティングノウハウで、あなたのビジネスを支援。売上・集客を最大化します。