エクセル関数応用
指数近似/対数近似/累乗近似(掲載順位とCTR)

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2020-03-31

指数近似/対数近似/累乗近似(掲載順位とCTR)


Google検索順位によってクリック率(CTR)が大きく違う事は皆さん知っている事でしょう。
「検索順位 CTR」で検索すると、1位から10位までのCTRが参考として出ています。

年度やデバイス別等で数値は違って来ますが、
大抵は、1位で21~32%位になっているはずです。

もちろん、これらの数値は正しいはずです。
しかし、CTRはキーワードによっても違ってくるはずです。
当サイトなら、「エクセル」「VBA」これらを含むキーワードが主になってきます。
そこで、当サイトのデータを基に掲載順位とCTRの関係を調査してみました。


Google Search Consoleから:掲載順位とCTRデータを取得

Google Search Consoleから:掲載順位とCTRをコピーしてシートに貼り付けて散布図を作成します。
Google Search Console「検索パフォーマンス」

エクセル Excel 掲載順位と CTR

「平均CTR」と「平均掲載順位」にチェックを付けます。

エクセル Excel 掲載順位と CTR
この数値は検索で表示されたキーワードでの順位の平均になります。
順位が低く表示回数の少ないキーワードは集計されていません。
したがって、どのサイトでも似たような数値になっているものと思います。

最初はクリック数順になっています。
そのままですと、当然掲載順位の高いものばかりのデータになってしまうので、
「表示回数」の降順にします。

エクセル Excel 掲載順位と CTR

期間は「過去3か月」「過去6か月」等から適宜選びます。

エクセル Excel 掲載順位と CTR

1ページあたりの行数は多い方が良いので500にします。

エクセル Excel 掲載順位と CTR

表示されたデータをコピーしてシートに貼り付けます。
CTRと掲載順位だけにしたものが以下になります。

エクセル Excel 掲載順位と CTR

このデータを散布図にします。

散布図と近似曲線

※以下のExcel画面はOffice365を使用しています。

散布図の作成

上記のA1:B501を選択し、
「おすすめグラフ」
散布図が出てきますので、そのまま「OK」

エクセル Excel 掲載順位と CTR

見づらいので、点の大きさを小さくしたり、タイトルは要らないので消したり等々、ある程度整形します。

エクセル Excel 掲載順位と CTR

ここは本題とは関係ないので、見やすければ良いので適当に調整します。

近似曲線の追加

グラフに近似曲線を追加します。

エクセル Excel 掲載順位と CTR

エクセル Excel 掲載順位と CTR

上記手順で、以下の3つの近似曲線を追加します。
・指数近似
・対数近似
・累乗近似
近似曲線の数式も見たいので、「グラフに数式を表示する」にチェックを入れます。
見やすいように実線にしたり、近似曲線ごとに色を変えたり、見栄えを整えます。

エクセル Excel 掲載順位と CTR

完成したグラフが以下になります。

エクセル Excel 掲載順位と CTR

緑が、累乗近似
黒が、対数近似
赤が、指数近似

近似曲線を見ると、どうも実体と一致しません。
順位1位だと、80%位になってしまったり、順位が1より小さくなったり・・・
どれも実際のデータと見比べると、近似と言える曲線になっていません。

そもそも、順位からCTRを求めたいのですから、XとYが逆になってしまっています。
そこで、グラフの軸を入れ替えてやり直しします。

X軸Y軸を入れ替えて散布図を作成

行/列の切り替え

グラフの「データ選択」→「行/列の切り替え」
普通はこれでX軸Y軸を入れ替えられますが、今回の場合はエラーが出てしまいます。

エクセル Excel 掲載順位と CTR

仕方ないので、元データの列(A列とB列)を入れ替えてグラフを作成します。

エクセル Excel 掲載順位と CTR

散布図の作成

上記のA1:B501を選択し、
「おすすめグラフ」
散布図が出てきますので、そのまま「OK」

エクセル Excel 掲載順位と CTR

先ほどと同様に見栄えを調整します。

近似曲線の追加

近似曲線も先ほどと同様に追加します。
完成したグラフが以下になります。

エクセル Excel 掲載順位と CTR

緑が、累乗近似
赤が、指数近似
黒が、対数近似

近似曲線を見ると、
赤の指数近似がかなり実態に近いように見えます。
これを使う事にしましょう。

適切な近似曲線の選択について

最適な曲線を選ぶことはとても難しいものです。
筆者はこれについて説明できるだけの専門知識を有していません。
最適な曲線を選ぶ要素としては、
・グラフの形状
・変化量
・収束するか
・どんなデータなのか
等々多岐にわたって検討する必要があります。
この辺りについて興味がある方は、別途調べてみると色々と面白いとは思います。
また、近似曲線の数式についても、その理論までは説明できません。
本稿では、エクセルの作成手順や関数の説明だけに徹しています。
近似曲線の数式について参考にしたページの中で一番分かりやすかったのは以下でした。

指数近似を数式で求める

グラフに表示された指数近似の数式は、

y = 0.712e-0.451x

これらの係数値を定数値で計算しても良いのですが、せっかくなのでこれも求めてみましょう。
一般的な数式で書くと、

y = ae^bx

この係数aと係数bを求める数式は以下になります。

係数a = EXP(INTERCEPT(LN(既知のY), 既知のX))
係数b = SLOPE(LN(既知のY), 既知のX)

関数の説明

EXP関数

eを底とする数値のべき乗を返します。
定数eは自然対数の底で、2.71828182845904となります。

Exp(数値)

数値はeを底とするべき乗の指数を指定します。
他の数値を底としてべき乗の計算を行う場合は、指数演算子(^)を使用します。
EXP関数は数値の自然対数を返すLN関数の逆関数です。

LN関数

数値の自然対数を返します。
自然対数とは、定数e(2.71828182845904)を底とする対数のことです。

LN(数値)

数値は自然対数を求める正の実数を指定します。
LNは、EXP関数の逆関数です。

INTERCEPT関数

既知のxと既知のyを通過する線形回帰直線の切片を計算します。
切片は、既知のxと既知のyを通過する最適な回帰直線に基づいています。
独立変数が0(ゼロ)のときの従属変数の値を決める場合にINTERCEPT関数を使用します。

INTERCEPT(既知のy,既知のx)

既知のyは、観測またはデータの従属範囲を指定します。
既知のxは、観測またはデータの独立範囲を指定します。

SLOPE関数

既知のyと既知のxのデータ要素を通じて回帰直線の傾きを返します。
傾きとは、直線上の2点の垂直方向の距離を水平方向の距離で除算した値で、回帰直線の変化率に対応します。

SLOPE(既知のy,既知のx)

既知のyは、従属変数の値を含む数値配列またはセル範囲を指定します。
既知のxは、独立変数の値を含む数値配列またはセル範囲を指定します。

掲載順位に対するCTRを求める

係数a = EXP(INTERCEPT(LN(既知のY), 既知のX))
係数b = SLOPE(LN(既知のY), 既知のX)
Y = 係数a*EXP(係数b*X)

これらの数式を各セルに入れます。

エクセル Excel 掲載順位と CTR

E1=EXP(INTERCEPT(LN($B$2:$B$501), $A$2:$A$501))
E2=SLOPE(LN($B$2:$B$501), $A$2:$A$501)
E5= $E$1*EXP($E$2*D5)

最新のOffice365であればスピルするので、
D5を範囲指定してD5:D24のように指定すれば、スピルして20位まで一気に出力されます。
Office365以外の場合は、D5セルの数式をコピーしてください。
作成された掲載順位とCTR
順位 CTR
1 45.34%
2 28.87%
3 18.38%
4 11.71%
5 7.45%
6 4.75%
7 3.02%
8 1.92%
9 1.23%
10 0.78%
11 0.50%
12 0.32%
13 0.20%
14 0.13%
15 0.08%
16 0.05%
17 0.03%
18 0.02%
19 0.01%
20 0.01%

Google検索では、1ページ目の前半にランクされないと極端にクリック率が落ちてしまいます。
2ページ目の後半になってしまうと、もうほとんどクリックされないことになります。

指数近似/対数近似/累乗近似(掲載順位とCTR)の最後に

本稿では、理論は抜きにして実際の作成手順についてのみ説明しました。
もし、これらを説明したらとても1記事で済む量では収まらないと思います。
何より、筆者にはこれらをしっかり説明できるだけの知識もありません。
従って、実践的な手順として、
・データのとり方
・グラフの作成方法
・関数の使い方
これらの説明だけの記事としました。



同じテーマ「エクセル関数応用」の記事

VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
エクセルの日付と時刻のまとめ
連続数値部分を取り出し記号で連結
指数近似/対数近似/累乗近似(掲載順位とCTR)
ジャンプ・セル選択の応用(空欄を埋める、1行置きに挿入)


新着記事NEW ・・・新着記事一覧を見る

VBA100本ノック 18本目:名前定義の削除|VBA練習問題100(11月6日)
VBA100本ノック 17本目:重複削除(ユニーク化)|VBA練習問題100(11月6日)
VBA100本ノック 16本目:無駄な改行を削除|VBA練習問題100(11月5日)
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題100(11月4日)
VBA100本ノック 14本目:社外秘シート削除|VBA練習問題100(11月3日)
VBA100本ノック 13本目:文字列の部分フォント|VBA練習問題100(11月1日)
VBA100本ノック 12本目:セル結合を解除|VBA練習問題100(10月31日)
VBA100本ノック 11本目:セル結合の警告|VBA練習問題100(10月30日)
VBA100本ノック 10本目:行の削除|VBA練習問題100(10月29日)
VBA100本ノック 9本目:フィルターコピー|VBA練習問題100(10月28日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ