地域レポート専用の社内ツールやピボットテーブルなどを使っている方は、何の問題もないと思いますがそんな便利ツールを使わず関数を利用して頑張っている方も多いかと思われます。
そんなあなたにオススメしたい関数がございます。
その名も「SUMIF関数」です。
SUMIF関数とは？
SUMIF関数は、条件に合うものだけを加算する関数です。
計算式は ＝SUMIF（範囲,検索条件,合計範囲) となります。
参考ページ →【エクセル活用術】データ集計の小技【リスティング広告】
準備
①地域別レポートの用意
今回はGoogle地域レポートを基準に作成致しますがYahooも同じような形で使用可能です。
※クリック
②データ抽出の欄を作成
- A列、B列、C列（できればD列も）を挿入して追加しましょう。
- 地域名＋分析したい数値の名前（費用、CVなど）を記載しましょう。
- 地域名の一覧に47都道府県を記載しましょう。
※クリック
SUMIF関数の設定方法
上記画像にて用意した「費用欄」を埋めていきましょう。
※B3列に関数を導入していきます。
①SUMIF関数の範囲
「範囲」は、下記画像のように地域の一覧を指定します。
この時、F列3行からデータがある最後のセルまで指定しています。
※今回は仮に最後のセルがF2385としています。
【注意】
今後B列3行以外のセルにこの関数をコピペする場合、F3:F2385の範囲指定はコピペされる毎にF4:F2386～F5:F2387～といった具合にずれて行きますのでF3:F2385にカーソルを合わせて「F4」を押し絶対値としてください。
- 【通常】＝SUMIF(F3:F2385、検索条件、合計範囲）
- 【絶対値】＝SUMIF($F$3:$F$2385、検索条件、合計範囲）
②SUMIF関数の検索条件
「検索条件」には、先ほど「範囲」で指定した中からどの地域を抜き出すかの条件となります。
この場合は、「範囲」の中から地域名「東京都」を調べたいのでA列３行を指定します。
※今回は絶対値にしなくて良い。
③SUMIF関数の合計範囲
「合計範囲」は「範囲」の一覧から「検索条件」で指定した地域の値を読み込むための範囲指定です。
今回は、費用一覧のN列3行～N列2385行を指定しています。
※「F4」を押して絶対値にしてください。
こうすることで、「合計範囲」を費用一覧で指定した場合、地域一覧の「範囲」の中から
「検索条件」東京都に当てはまる費用の合計を算出します。
④SUMIF関数の設定結果
「範囲」で指定した地域一覧から 東京都 が検索されて、「合計範囲」で指定した費用一覧から 東京都 の数値が全て合計された結果「￥3,728,765」という結果が出ました。
この状態でB列3行のコピーを他の地域に適用をさせると以下のようになります。
同じようにCVのデータを算出したい場合は、
「合計範囲」の部分をCV一覧の P列3行～P列2385行に範囲指定し直しましょう。
※絶対値にするのを忘れず
【注意】
①他の列の合計を算出する時に、そのままコピーをすると「検索条件」がずれる可能性があるのでご注意してください。
※B列3行の内容をC列3行にコピーした場合「検索条件」がA3からB３になってしまうなど。②「範囲」「合計範囲」を指定する時は、両方の行をずれないように注意！
※「範囲」B列3行～、「合計範囲」F列3行～はいいが、「範囲」B列3行～、「合計範囲」F列4行～はダメです。正しくデータが算出できなくなります。
最終的には下記のような数値にまとめることが可能です。
より詳細なデータを集計したい場合は、セル挿入を行い「CPA」などを追加しましょう。
※CPA追加版、計算式は 費用÷CV です。（CV0件の場合エラーが起きないようIF関数を使用）
より詳細な地域分析を行う場合
上記のような形で「都道府県」別にデータをまとめましたが、都道府県毎の「市区郡」単位で分析を行えばより詳細な地域分析ができます。
例えば上記でもCVが一番多くとれている「東京都」の分析を行う場合に、東京都の「市区郡」一覧を[メニューバー] ＞ [データ] ＞ [重複の削除]などの機能を駆使して作成します。
先ほど都道府県が設定してあった「地域」一覧に東京都の「市区郡」一覧を設定します。
そして下記画像のようにSUMIF関数の「範囲」を「地域」から「市区郡」に変更することで東京都の「市区郡」別のデータが算出されます。
さらにCVも同じように算出し、CPAも計算しまとめると、
このような形で「市区郡」単位のより詳細なデータが算出でき分析も行うことができます。
まとめ
東京都という「都道府県」単位で見るとCV218件、CPA\17,104という状態ですがその「市区郡」単位でみるとCV0件でCOSTをとんでもなく使用している地域が存在します。
その地域を除外及び抑制を行うことでCPAを下げることも可能なので分析はより詳細な部分で行っていきましょう。
※本当は除外後のデータの動きなど見せられたらよかったのですが、それはまた別の知っとくで・・・。
