「Microsoft Office Specialist （MOS） Excel®」「Excel® VBA スタンダード」を取得し、普段の業務、経理作業でも表計算ソフトを利用する中、様々な関数の使い方を考えてきました。
今回は、その中の一例として、「リライト原稿に該当キーワードがどれだけ増えたか」を一括で算出するシートの作成事例をご紹介します。
■ 執筆・制作のポイント
このページ自体も、私が執筆した「技術解説記事」のサンプルです。
・専門知識の翻訳： 難しい部分を「接着剤」などの比喩を使って直感的に理解できるように工夫しました。
・ユーザー視点の設計： 「なぜその作業が必要か（TRIM関数など）」という理由を言語化し、読者が納得して動ける構成にしました。
「難解な仕様を、初心者にわかりやすく伝えるマニュアル作成」や「論理的な構成が求められるSEO記事」が得意です。人材をお探しの方は、ぜひお問い合わせよりご連絡ください。
このシートでできること
このシートは、修正前と修正後の文章を比べて、複数のキーワードがどれだけ増えたかを一瞬で算出できるツールです。
SEO対策などで、ニーズのあるキーワードを増やしたい場面はWebライターであればよく直面すると思います。リライト前とリライト後でどれだけキーワードが増えたのかは、検索メニューを使えば調べられますが、キーワードが多い場合はこの作業に非常に手間がかかります。私自身も、この作業にかなりの時間を取られ、ストレスを感じていました。
そんな悩みを解決するため、作ったのがこのツールです。
このシートの使い方
使い方は、次の通りです。
1.A2セルに「修正前の原稿」をコピー＆ペーストします。
※文字カウントがB2セルに表示されます。
2.C2セルに「修正後の原稿」をコピー＆ペーストします。
※文字カウントがD2セルに表示されます。
3.A5セルから下に向かって、どれだけ増えたかを調べたいキーワードを入力します。すると、C5セル以下にキーワードがどれだけ増えたかが表示されます。
4.E4セルに数値を入れると、その数値以下になったキーワードのD列に×が付きます。
このシートの仕組み
このシートの主な仕組みは、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以外の表計算ソフトでも同じように使用できます。表計算シートをプログラミングで動かすには、ソフトに合わせたプログラミング言語を使用しなければなりませんが、関数であればご自身の環境に合わせた表計算ソフトでご利用いただけます。
ダウンロードはこちらから
このシートは、以下のボタンからご自由にダウンロードいただけます。仮のキーワードが入ったままですので、削除してお使いください（関数が入っているセルの内容を削除すると正常に作動しなくなりますので、お気を付けください）。
キーワードの行が足りないときは、以下の動画のようにセルの右端をドラッグして中の関数をコピーしてください。
※免責事項：本ツールの使用によって生じたいかなる損害についても、著者は責任を負いかねます。ご自身の責任においてご利用ください。