ClaudeでメディアプランをExcelで作成 — 予算配分とリーチ・CPMの試算

出稿前の媒体プランは、予算を媒体にどう割るかで、届く人数もインプレッションも変わります。Claude for Excelなら、媒体別の予算・CPM・想定頻度を入力セルに分け、配分を動かした複数案を同じ式のまま並べられます。

この記事では、広告予算3,000万円を4媒体に割る架空のプランを題材に、前提シート・配分案・試算シートを組む流れを扱います。最後に、媒体ごとのリーチを足し算すると母数の117%になってしまう誤りと、その検算も示します。数字はすべて説明用の架空の値です。

媒体プランで、Claudeに任せる範囲と人が決める範囲

Claude for Excelは、開いているブックについて質問し、数式の関係を保ったまま前提を変え、新しいモデルを作るExcelアドインです。回答にはセル単位の根拠が付き、クリックで該当セルへ移動できます。媒体プランで効くのは次の2点です。

• 予算比率を変えると、インプレッションとリーチが数式で再計算される
• 結果の数字を、どのセルの式から出したかたどれる

一方で、CPMや想定頻度の値を決めるのは人です。媒体社の見積もりや過去実績から持ってくる数字を、Claudeが推測で埋めるものではありません。公式のドキュメントは、人のレビューなしの最終的な顧客向け成果物と、検証なしの監査向け計算を、推奨しない使い方に挙げています。クライアントに出すプランは、人が検算してから渡します。

前提条件 — 対応バージョンとプラン

使えるのはPro、Max、Team、Enterpriseの各プランです。対応するExcelは、Excel on the web、Microsoft 365契約のWindows版(ビルド16.0.13127.20296以降)、Mac版(バージョン16.46以降、ビルド21011600以降)です。Excel 2016・2019の永続ライセンスやボリュームライセンス版、iPad、Androidでは動きません。

媒体社との打ち合わせにiPadだけを持ち込む運用だと、その場で配分を動かせません。席上で数字を変えて見せたいなら、PCのExcelを用意します。

予算からリーチを出す4つの式

媒体プランの数字は、次の順で予算から変換します。式そのものは広告業界の一般的な計算で、Claudeに頼む前にこの形を自分の手で押さえておくと、出てきた式の誤りに気づけます。

CPMは1,000インプレッションあたりの費用です。頻度は、リーチした1人が平均で何回広告に接触するかを指します。4行目の式は、媒体間で同じ人が重複しないという仮定の上に立つ近似です。この仮定の意味は後の節で扱います。

手順1: 前提シートを組む

最初の依頼は、入力セルの置き場所と書式を決める指示です。値は架空の例です。

新しいブックに「前提」シートを作ってください。
A列に項目名、B列に値を置きます。
B2 ターゲット母数(人) 10000000
B3 総予算(円) 30000000
5行目に見出し(A5 媒体 / B5 CPM(円) / C5 想定頻度)を置き、
6〜9行目に次の4媒体を入力してください。
動画広告 800 4
SNS広告 500 5
ディスプレイ広告 300 6
交通広告 1000 3
入力セルは薄い黄色にし、D列に「出どころ」の欄を作ってください。

入力セルを色分けしておくと、「黄色のセルだけ変えれば結果が動く」と説明できます。出どころの欄には、「媒体社の見積書」「前回キャンペーンの実績」のように書きます。ここが空欄のままだと、クライアントに根拠を問われたときに答えられません。

仕事の型が決まっているなら、アドインの設定画面の「Instructions」欄に書いておく手もあります。「金額は円、人数は人で表示する」「入力セルは薄い黄色にする」のような書式の約束を置くと、毎回の依頼に書かずに済みます。この設定はExcel専用で、PowerPointやWordの指示とは別に管理されます。

手順2: 配分案を比率で持つ

予算を金額で直接入力すると、総額を変えたときに全媒体を打ち直すことになります。比率で持てば、総額が動いても配分の意図が残ります。

「配分案」シートを作り、A列に媒体名(前提シートの6〜9行目を参照)、
B列を案1、C列を案2、D列を案3として、予算比率を入力してください。
案1: 動画40% SNS30% ディスプレイ20% 交通10%
案2: 動画50% SNS30% ディスプレイ10% 交通10%
案3: 動画30% SNS30% ディスプレイ30% 交通10%
10行目に各案の比率の合計を式で出し、100%でなければ
セルを赤くしてください。

10行目の合計チェックは、地味ですが効きます。案を何度も手直しすると、合計が99%や105%のまま試算が進む事故が起きるためです。

手順3: 試算シートに式を組ませる

試算シートでは、3案を同じ式で並べます。依頼には、参照先と計算式、上限の扱いまで書きます。

「試算」シートを作り、A列を媒体名、B〜D列を案1〜3の予算、
E〜G列を案1〜3のリーチ率、H〜J列を「1−リーチ率」にしてください。
値の直書きはしないでください。
予算 = 前提!$B$3 × 配分案の同じ案の比率
リーチ率 = MIN(1, 予算 ÷ CPM × 1000 ÷ 想定頻度 ÷ 前提!$B$2)
12行目に予算合計、13行目に単純合算リーチ率(E〜G列の合計)、
14行目に重複除去後リーチ率(1 − H〜J列の積)、
15行目に重複除去後リーチ(人)、
16行目に総インプレッション、17行目に平均CPM(総予算 ÷ 総インプレッション × 1000)
を、案ごとの式で出してください。

組み上がる式は、例えば次のような形になります。列の位置は自社の表に合わせて読み替えます。

B2 : =前提!$B$3*配分案!B2
E2 : =MIN(1,B2/前提!$B6*1000/前提!$C6/前提!$B$2)
H2 : =1-E2
E13: =SUM(E2:E5)
E14: =1-PRODUCT(H2:H5)
E15: =E14*前提!$B$2

PRODUCT(1-E2:E5) と1つの式にまとめる書き方もありますが、配列の評価に依存します。H〜J列に「1−リーチ率」を置く補助列を挟むと、式が読みやすく、どのセルの値が積に入るかを目で追えます。

MIN(1, …) はリーチ率が100%を超えないための上限です。予算を厚くした媒体で、インプレッションを頻度で割った人数が母数を超えることがあるためです。

リーチを足し算すると、母数の117%になる

媒体プランで最も多い誤りは、媒体ごとのリーチを足し算して全体のリーチとすることです。案1の4媒体で計算してみます。

リーチ率を単純に足すと、37.5 + 36.0 + 33.3 + 10.0で約116.8%、人数では約1,168万人です。母数は1,000万人なので、母数を超えています。この時点で、足し算が成り立たないとわかります。

重複を除くには、「どの媒体にも接触しなかった人の割合」を先に求めます。各媒体に接触しない確率は、動画が0.625、SNSが0.64、ディスプレイが約0.667、交通が0.9です。4つを掛けると0.24になります。1から引くと0.76で、重複除去後のリーチは760万人(76%)です。

この計算は、媒体間で接触が互いに独立しているという仮定に立っています。実際には、同じ層に重なりやすい媒体の組み合わせがあり、その場合は重複がこの式より大きくなり得ます。媒体社やプランニングツールから媒体間の重複率の実測が得られるなら、そちらを優先します。独立の式は、実測がないときの仮置きの値です。

配分を動かして、3案を比べる

試算シートが組み上がると、3案の結果が並びます。値は前提のとおりです。

案2は、CPMが高い動画に予算を寄せたため、インプレッションも重複除去後リーチも案1を下回ります。案3は、CPMが安いディスプレイを厚くしたぶん、重複除去後リーチが793万人に伸びます。

ここで見るべきなのは、順位より「頻度の仮定」です。この試算は、予算を増やしても各媒体の想定頻度が変わらないとして計算しています。ディスプレイに予算を寄せれば、実際には同じ人に何度も当たって頻度が上がる可能性があり、そのときリーチは試算ほど伸びません。案3の優位は、頻度が据え置きになる条件つきの数字です。

比較のあとは、Claudeに感度を聞くと使いやすくなります。

案3で、ディスプレイの想定頻度を6から8に変えたとき、
重複除去後リーチと平均CPMがどう変わるか、
前提シートの値だけ書き換えて教えてください。
変えた前後の値を並べて、どのセルを変えたかも書いてください。

「どのセルを変えたか」を書かせておくと、あとで元に戻せます。作業は必ず、元のブックのコピーで行います。

人が手で検算する

ブックができたら、1媒体を電卓で計算して一致するかを確かめます。動画広告なら、1,200万円 ÷ 800円 × 1,000 = 1,500万インプレッション、1,500万 ÷ 4 = 375万人です。試算シートのE2が37.5%になっていれば、式の組み方は合っています。

次に、全体の整合を3点見ます。

• 予算合計が総予算の3,000万円に一致している(12行目)
• 配分案の比率が各案で100%になっている(配分案の10行目)
• 重複除去後リーチ率が、媒体別リーチ率の最大値以上で、100%以下に収まっている

3点目は見落としやすい項目です。重複を除いたリーチは、どの1媒体のリーチ率よりも小さくなりません。案1なら、最大の動画37.5%を76.0%が上回っています。これが逆転していたら、積の計算か補助列の参照がずれています。

公式のドキュメントも、変更は確定前に必ず確認することと、広い範囲を編集させる前に信頼できるコピーで始めることを勧めています。Claudeは既存データを上書きする前に警告を出しますが、警告に頼らず、配分案シートと前提シートは別名で保存してから動かします。

つまずきやすい点

Excelのデータテーブルに頼れない。公式の未対応機能に、データテーブル(What-If分析)とマクロ・VBAが挙がっています。配分の比較を「データテーブルで一気に出して」と頼んでも使えないため、この記事のように案ごとの列で持ちます。

媒体社の単価表を、そのまま開かない。媒体社から届いたレートカードや見積もりのファイルには、Claudeを操作する隠れた指示が入っている可能性があります。公式は、信頼できるスプレッドシートでだけ使うよう求めています。CPMの数値だけを自分のブックに写してから、作業を始めます。

出稿後の再配分とは別の作業。この記事は、まだ配信していないプランの試算です。配信が始まってからの成果を見て予算を動かす手順は、Claudeでキャンペーンの効果測定と予算再配分をする手順にあります。出稿前に置いた頻度やCPMの仮定は、配信結果と見比べて直す対象になります。

連携面もあわせて使える。広告媒体の実績をClaudeに集めたいときは、ClaudeとFunnelを連携する方法が入口になります。媒体プランの前提値を、前回キャンペーンの実績から取る場面で効きます。

3案の比較を、提案に仕立てる

試算が合ったら、クライアントに示す形に整えます。結果の表だけでなく、前提を同じブックに残すのが要点です。

Claude for ExcelでROI試算を組む手順のように、保守・標準・積極の3ケースで幅を持たせる考え方は、媒体プランでも通用します。頻度とCPMを動かした範囲を、重複除去後リーチの幅として添えると、1つの数字を断定せずに済みます。

また、結果のセルには「CPMと頻度は仮置き」「媒体間の独立を仮定」と注記を付けます。提案を受ける側が、どの数字が見積もりで、どの数字が仮定かを区別できます。

まとめ

媒体プランの試算で、Claudeに任せて効くのは、予算比率を動かしたときのインプレッションとリーチの再計算です。CPMと頻度の値、そして媒体間の重複の扱いは、人が決めて残す部分になります。

足し算したリーチが100%を超えたら、重複を数えている合図です。独立を仮定した式で近似し、実測の重複率があればそちらに置き換えます。クライアントに出す前に、1媒体を電卓で検算します。