「リライト原稿に該当キーワードがどれだけ増えたか」を一括で算出するシートの作成事例をご紹介します。
■ 執筆・制作のポイント
- 専門知識の翻訳：難しい部分を「接着剤」などの比喩を使って直感的に理解できるように工夫しました。
- ユーザー視点の設計： 「なぜその作業が必要か（TRIM関数など）」という理由を言語化し、読者が納得して動ける構成にしました。
- 資格を活かした関数の組み合わせ：「Microsoft Office Specialist （MOS） Excel®」「Excel® VBA スタンダード」取得、およびAIを使った関数作成の経験を活かし、使い方の難しい関数を組み合わせて実務に使える形に落とし込んでいます。
「難解な仕様を、初心者にわかりやすく伝えるマニュアル作成」や「論理的な構成が求められるSEO記事」の制作事例としてご覧ください。
このシートでできること
このシートは、修正前と修正後の文章を比べて、複数のキーワードがどれだけ増えたかを一瞬で算出できるツールです。
SEO対策などで、ニーズのあるキーワードを増やしたい場面はWebライターであればよく直面すると思います。
リライト前とリライト後でどれだけキーワードが増えたのかは、検索メニューを使えば調べられますが、キーワードが多い場合はこの作業に非常に手間がかかります。私自身も、この作業にかなりの時間を取られ、ストレスを感じていました。
そんな悩みを解決するため、作ったのがこのツールです。
このシートの使い方
使い方は、次の通りです。
1.A2セルに「修正前の原稿」をコピー＆ペーストします。
※文字カウントがB2セルに表示されます。
2.C2セルに「修正後の原稿」をコピー＆ペーストします。
※文字カウントがD2セルに表示されます。
3.A5セルから下に向かって、どれだけ増えたかを調べたいキーワードを入力します。すると、C5セル以下にキーワードがどれだけ増えたかが表示されます。
4.E4セルに数値を入れると、その数値以下になったキーワードのD列に×が付きます。例えば、E4セルに0を入れると、「増加数が0以下=増えていないキーワード」が一瞬で分かります。
このシートの仕組み（技術解説）
このシートの仕組みは、主にLEN関数とSUBSTITUTE関数によって作られています。
【準備】キーワードのトリミング
まず、B5セル以下には、A5セル以下のキーワードをトリミングする（不要なスペースを取り除く）TRIM関数が入っています。
これは、A列に張り付けられたキーワード内の不要な空白などを取り除くための処理です。
キーワードリストなどからキーワードを一気に張り付けると、一緒に前後の空白も貼り付けてしまう場合があります。この状態だと、正しくキーワードをカウントできないので、TRIM関数でキーワードを整えているのです。
キーワードのカウント
キーワードのカウントを行うC5セルには、次の関数が入力されています。
=((LEN($C$2)-LEN(SUBSTITUTE($C$2,B5,””)))/LEN(B5))-((LEN($A$2)-LEN(SUBSTITUTE($A$2,B5,””)))/LEN(B5))
まず、前半の部分を説明します。
(LEN($C$2)-LEN(SUBSTITUTE($C$2,B5,””)))/LEN(B5)
LEN関数は文字の長さをカウントする関数です。また、SUBSTITUTE関数は、ある部分の文字列を置換したいときに使います。
例えば「=SUBSTITUTE(A2, “あなた”, “私”)」と書くと、A2の中の文字列の中の「あなた」を「私」に置換して表示します。
では、式の説明をします。
- LEN($C$2)はC2（修正後）の文字数をカウントしています。※＄マークは絶対参照を表しています。式をコピーしたときも参照元のセルが動かないようにする接着剤のようなものです。
- SUBSTITUTE($C$2,B5,””)は、C2の文字列の中からB5（トリミング済みキーワードが入っているセル）内のキーワードを探し出し、空白（””）に置き換えています。具体的には、C2の中にある「金融教育」のキーワードを削除している部分です。
- LEN(SUBSTITUTE($C$2,B5,””))は、C2の中の「金融教育」のキーワードを削除したときの文字数をカウントしています。
- (LEN($C$2)-LEN(SUBSTITUTE($C$2,B5,””))は、C2からB5のキーワード「金融教育」をすべて消したとき、文字数がどれだけ減るかを計算する部分です。
- この減った文字数をLEN(B5)（金融教育の文字数=4）で割ると、C2の中に「金融教育」のキーワードがいくつ含まれているのかを算出することができます。
次に、後半の式を見てみましょう。
(LEN($A$2)-LEN(SUBSTITUTE($A$2,B5,””)))/LEN(B5)
これは、先に説明した式のターゲットをA2（修正前の文）に変えた式と言えます。
LEN($A$2)でA2セルの文字数を取得し、そこから「A2の中からB5のキーワード「金融教育」を削除したときの文字数（LEN(SUBSTITUTE($A$2,B5,””))）」を引きます。
この文字数の差をB5のキーワード「金融教育」の文字数（LEN(B5)）で割ると、A2にB5のキーワードがいくつ含まれていたのかが分かります。
では、改めて式全体を見てみましょう。
=((LEN($C$2)-LEN(SUBSTITUTE($C$2,B5,””)))/LEN(B5))-((LEN($A$2)-LEN(SUBSTITUTE($A$2,B5,””)))/LEN(B5))
これは結局、C2（修正後の文）の中に含まれているB2のキーワード「金融教育」の個数から、A2（修正前の文）の中に含まれているB2のキーワード「金融教育」の個数を引いた数を求める式なのです。
【おまけ】条件判定
もし、キーワードの増加数がE4に入力した数よりも少なかったとき、×をD列に表示する関数がこちらです。
=IF(C5<=E4,”×”,””)
このIF関数により、C5（キーワードの増加数を表示するセル）がE4以下になったとき、×を表示することができます。
このシートのメリット
Excelに複雑な作業をさせるには、通常マクロやVBAというプログラミング言語を使用します。しかし、マクロやVBAを使いこなすには知識が必要です。
また、マクロやVBAは複雑な動きができるゆえに、セキュリティリスクも大きいです。
他人が組んだマクロやVBAをそのまま仕事で使うのはとても危険です（そのため、Excelでは通常他所で作られたVBAファイルを開こうとすると警告メッセージが出るようになっています）。
しかし、このシートは関数のみで作成されているため、比較的安全に利用できます。プログラミングの知識がなくても理解がしやすく、カスタマイズも容易です。
また、このシート上の関数は、スプレッドシートなど、Excel以外の表計算ソフトでも同じように使用できます。
表計算シートをプログラミングで動かすには、ソフトに合わせたプログラミング言語を使用しなければなりませんが、関数であればご自身の環境に合わせた表計算ソフトでご利用いただけます。
ダウンロードはこちらから
このシートは、以下のボタンからご自由にダウンロードいただけます。
仮のキーワードが入ったままですので、削除してお使いください（関数が入っているセルの内容を削除すると正常に作動しなくなりますので、お気を付けください）。
キーワードの行が足りないときは、以下の動画のようにセルの右端をドラッグして中の関数をコピーしてください。
※免責事項：本ツールの使用によって生じたいかなる損害についても、著者は責任を負いかねます。ご自身の責任においてご利用ください。