![Chimpanzine[チンパンジン]](https://chimpanzine.digital/wp-content/uploads/2019/11/logo.png){kind=logo}
ウェブ広告担当者を悩ませるレポート作成。
中には毎月数十時間をレポートに費やしているという方もいるのではないでしょうか。
日別、期間別、キャンペーン別、広告グループ別、クリエイティブ別など
もしも何種類もの分析軸のレポートを手動で作成しようとすると、
何度も媒体からデータをダウンロードしたりピボットを組んだりと
時間と工数がかなりかかってしまいます。
今回はそんなわずらわしいレポート作成がたった3ステップで完了できる
エクセル（Excel）関数や便利な機能を使った自動化レポートの作成方法をご紹介します。
レポートの作成時間を今の5分の1、
レポートボリュームによっては10～20分の1に短縮できたとしたら……
できた時間であなたは何がしたいですか？
1.エクセル自動化レポートの基本構成と準備
まず前提として
誰が見ても作成可能な自動化のレポートフォーマットを作成していきます。
会社にお勤めの場合、担当業務は日々変わっていくため
作成者しか理解できないような自動化レポートを作ってしまうと
引き継ぎやその後に時間をとられることになるからです。
また今回ご紹介する自動化では、分析軸や提出頻度が多いレポートほど時間短縮できます。
エクセル四則演算や基本操作、ショートカットは省きます。
基本のシート構成
基本的にフォーマットとなるエクセルファイルは
以下の4種類のシートで構成されます。
エクセル関数による自動化の前に、自動化に必要なシートを作成しておきましょう。
①提出シート
実際に提出するレポートシートです。
提出シート
②貼付けシート
媒体からダウンロードしたエクセルデータを張り付けるシートで、媒体の数だけ作成します。
レポート作成時には、前回のレポート作成で張り付けた4行目以下をすべて削除してから
媒体からダウンロードした今回分のエクセルデータを項目ごと貼り付けます。
最上部1行目には、同列2行目と4行目を参照するEXACT関数を組み、
媒体データの項目をそのまま2行目にコピペしておきます。
データ貼り付け時の項目のずれやデータ間違いを一目で確認するためです。
貼付けシート
③作業用シート
“作業手順”欄には、誰か見ても理解、作成できるよう作成手順や注意事項などをまとめます。
“更新”欄には、作成時の更新箇所（黄色セル）をまとめておきます。
日付や毎月変わるキャンペーン名など全てこの一ヵ所のみを更新することで各シートに反映されます。
“参照”欄には、各提出レポート内の関数の参照セルをまとめておきます。
参照セルをこの一ヶ所にまとめることで、
レポート完成後に各提出シートの参照セルを削除する時間と手間を省きます。
作業用シート
④確認用シート
最終の数値確認を1シートで完了できるシートです。
あらかじめ数式で確認すべき数値をこの1シートにまとめることによって、
目視確認による確認箇所漏れや数値ずれの見逃しを防ぎ、確認時間を短縮します。
たとえば、媒体別とキャンペーン別のと広告文別のレポート抽出期間が同じ場合は
全レポート数値が同じになるはずなので、その正誤を一目で確認できます。
またもし数値が合わない場合にも
どの項目の数値がどれだけずれているのかが一目でわかるので原因を見つけやすく、
修正にかかる時間も短縮できます。
確認用シート
貼付データの準備
グーグル（Google）やヤフー（Yahoo!）、フェイスブック（Facebook）、
グーグルアナリティクス（Google Analytics）といった各広告媒体の管理画面では、
あらかじめ抽出したいレポート項目を登録し保存しておくことができます。
その際レポートに名前を付けることができるので、
媒体に登録するレポート名とエクセルの貼付シート名を統一しておきます。
貼付けデータの間違い防止と、誰が見ても理解できるようにするためです。
また媒体に登録するレポートは広告粒度にしておくことをおすすめします。
求められるレポートの分析軸が増えても関数を組むだけで対応できるからです。
配信当初は、キャンペーン別のみだったレポート分析軸が
改善や施策を進めるうちに広告文別、広告グループ別、LP別と増えていくことはよくあります。
そのたびに媒体に登録したレポートを更新し貼付シートを編集しても対応はできますが、
今回はレポート作成の時短化と工数削減が目的なので、想定できる事には先に備えておきます。
レポート作成手順
自動化してしまえば3ステップで完成です。
１、各媒体からダウンロードしたデータを貼付シートに張り付ける
２、作業用シートで更新箇所を更新
３、確認用シートで数値確認
それでは、自動化していきましょう。
2.フォーマット作成に役立つ関数や数式
「日付セル+1」で日別レポートを一括更新
=日付セル+1
日付も+1、-1などで指定できます。
例）日別レポートの日付一括更新
画像では、セルB21以下にすべて「上のセル+1」という計算式を組んでいるので
セルB21を更新すると以下の日付セルが全て更新されます。
日別レポートの日付一括更新
TEXT関数で書式を指定する
=TEXT（値, 表示形式）
値で文字列に変換したい数値を指定して、希望の表示形式を「”」で囲んで指定できる関数です。
たとえば日時だと”yyyy/mm/dd” なら「2018/09/01」、
“m/d” なら「9/1」、”yyyy” なら「2018」と表示されます。
例）日付の表示形式を指定
TEXT関数による表示形式の指定
3.数値抽出に役立つ関数や数式
EXACT関数で正誤確認
=EXACT(セル1, セル2)
セル同士の正誤確認ができる関数です。
指定したセル同士が同じなら「TRUE」、異なる場合は「FALSE」が表示されます。
レポートでは
貼付シートに張り付けたデータ項目にずれがないかや、最終の数値確認に利用します。
条件付き書式のルール設定から
FALSEの場合はセルを着色するというルールを設定しておきます。
FALSEを一目で見つけ見逃しを防止する為です。
FALSEの場合にセルの色を変える条件付き書式のルール設定
例１）貼付けシートに張り付けたデータの正誤確認
各列1行目のEXACT関数は、2行目と4行目を参照しています。
EXACT関数による貼付シートのデータ貼付時の正誤確認
例２）最終数値確認
複数のシートのレポート数値を数式で1シートに並べ、正誤確認しています。
EXACT関数による数値確認
SUM関数で合計を求める
=SUM(セル1:セル2)
特定範囲の合計を求められる関数です。
例）レポート合計欄
セルE10からE13までのクリック数の合計を求めています。
SUM関数による合計値計算
SUMIFS関数で条件付き合計を求める
=SUMIFS(合計範囲,条件範囲１,条件１,条件範囲２,条件２……)
複数条件の特定範囲の合計を求められる関数です。
複数の条件の掛け合わせが可能なので
キャンペーン別の日別レポート、広告グループ別のクリエイティブ別レポートなど
さまざまな分析レポートに活用できます。
例）日別レポートのクリック数
「YSS貼」シートA行にある日付がセルB21の「2018/9/1」である場合に、
「YSS貼」シートF行にあるクリック数の合計を求めています。
SUMIFS関数による日別レポートのクリック数
SUMIFS関数の元データとなる貼付シート
「＆」で文字列を結合する
=セル1＆セル2＆セル3……
広告文別レポートを作成する際など、参照元となるデータの作成時に頻繁に利用します。
例）広告文別レポート
１、提出用広告文別レポートシートで、C～E行の見出し１、見出し２、説明文を結合して
一つのテキストデータにしています。
広告文別レポートの参照用テキスト結合
２、元データとなる貼付けシートで、F～H行にある見出し１、見出し２、説明文を結合し
一つのテキストデータにしています。
元媒体データの参照用テキスト結合
３、１、２で作成したテキストデータを参照して、SUMIFS関数で広告文別の各数値を求めています。
SUMIFS関数を利用した広告文別レポート
「”>=”＆日付」「”
「日付列,”>=” &【日付1】,日付列,”
「”>=”＆日付」「”<="&日付」で期間を指定できます。
例）期間別レポート
SUMIFS関数の中で、期間セルを参照し2018/4/16～2018/4/22を指定しています。
「”>=”＆日付」「”<="&日付」で期間を指定
「*～」で「～」の前にどんな文字が来ても参照する
*文字列…文字列の前にどんな文字が来ても参照する
文字列*…文字列の後にどんな文字が来ても参照する
*文字列*…文字列の前後にどんな文字が来ても参照する
例）キャンペーン別レポート
「G貼」シートQ列で、文字列「KT_サンプル名」の前後に
どんな文字が来ても参照するよう指定してます。
「*～」を使用した条件指定
「*～」を使用した条件指定の参照データQ列
VLOOKUP関数で検索値だった場合に指定した列の値を取り出す
=VLOOKUP（検索値, 範囲, 列番号, 検索の型）
指定した「範囲」で「検索値」だった場合に、左から「列番号」目の値を取り出します。
「検索の型」は「TRUE（近似値）」または「FALSE（完全一致）」で指定します。
レポートでは主に、2つのデータを1つにまとめたい時に利用します。
今回ご紹介する関数の中では一番ややこしい関数ですが、
覚えてしまえばレポートの参照用データの作成や入稿物の正誤チェックまで
さまざまなシーンで活用できる、非情に汎用性の高い関数です。
例）2つのデータを使ったデータ情報の統合
媒体によっては欲しい文字列ではなくコードなどでデータが表示される場合がありますが、
各コードに該当する文字列を記載した別シートがあれば、
VLOOKUP関数で該当する文字列に変換できます。
=VLOOKUP(A5,参照データ!$C:$G,5,FALSE)
参照データシートのC～G列を検索し、
検索値と完全一致する場合は5列目のG列の値を返すようしていしています。
VLOOKUP関数を組んでいるシート
VLOOKUP関数で参照しているデータシート
IFERROR関数でエラー表示を無くす
=IFERROR（値, エラーの場合の値）
エラーの場合に指定の文字を表示できる関数です。
エラー値が［#DIV/0!］［#N/A］［#NAME?］［#NULL!］［#NUM!］［#REF!］［#VALUE!］
のいずれかであればエラーの場合の値を返し、そうでなければ値をそのまま返します。
例）コンバージョン率（CVR）のエラー表示回避
計算元数値のコンバージョンが0件だとエラー［#DIV/0!］となってしまう為、
エラーの場合は「0」で返すよう指定しています。
IFERROR関数でCVRのエラー表示を回避
4.まとめ
- レポートの自動化は、分析軸や提出頻度が多いものほど時間短縮に有効
- 誰が見ても作成可能な自動化フォーマットで、引き継ぎ時のコミュニケーションコストを最小限に
- 提出シートとその他のシートを分けることで、提出時の不要テキストの消し忘れを防止
- 作業用シートで、作成時や配信内容変更時の更新作業を最小限に
- 確認用シートで、完成後の確認箇所やフォーマット成型作業を最小限に
- 各媒体に登録するレポート名は、貼り付けシートと同名にしてデータの貼り付け間違いを防止
- EXACT関数には条件付き書式で着色ルールを設定し、一目で正誤確認が出来るように
- 提出レポート内のCTR、CPC、CVR、CPAといった数式には全てIFERROR関数を使用し
エラー表記修正作業を0に
