Excel で、文字列の中に在るか無いかわからない特定の文字の位置を求める。

この記事は自分用の覚書です。

■MacBook Pro macOS Big Sur
バージョン 11.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.55 (21111400)

H.K さん。

セルに入っている文字列の中に、ある文字が在るのか無いのかわからないけれど、在るならその特定の文字の位置を在るだけ求めるやり方です。

■使っている関数について

難しく考えなくてもよいです。FIND関数のみでやっています。
FIND関数の書式は次のようです。

FIND(検索文字列,対象,[開始位置])

通常、FIND関数で特定の文字列の位置を求めるときは、「検索文字列」が「対象」の中に在ることがわかっている場合が多いですよね。

今回の場合は、「検索文字列」が「対象」の中に在るかどうかはわからない。
でも、在るなら（複数在るならそれぞれ）その位置を知りたい。ということですよね。

使い慣れたFIND関数を使っていますし、作業列を用いて処理の過程を分けているので、何をどうしているかが理解しやすいと思います。

仕事で作るファイルは、いつ誰が見ても理解しやすく作っておくべきだと私は思っています。
H.K さんや周囲の人たちにとって、他にもっと理解しやすいやり方があったら私にも教えてくださいね。

■処理

図の A 列が「検索文字列」で、ここでは大文字の A です。
B 列が「対象」です。

FIND関数の書式と見比べて何をどうしているかをより理解しやすくするために、書式に列の並びを合わせてみました。

「大文字の A」が B 列の文字列の中にあれば、その位置を C 列以降で求めています。
1つ目の A から 4つ目の A までを探しています。

「検索文字列」と「対象」の値や C 列以降の列数は、実務に合わせて変えてください。

図では「検索文字列」が複数個続いて並ぶ場合は想定していません。
実際には、もう少し列数が必要になることがあるでしょう。

6列ぐらい作っておけば、うちの業務ではたいてい対応できるのではないでしょか。

C 列がエラーなら「検索文字列」は無い。

上図の2行目では「検索文字列」が「対象」の中に3つ在るのがわかりますし、3行目では「検索文字列」は無いことがわかります。

「1つ目の検索文字列の位置」（ C 列）でエラーが起きていると、「検索文字列」は「対象」の中に在りません。

「検索文字列」を「対象」の1文字目から探していって最初に出現する位置が、 C 列に表示されるからです。
何文字目であっても、そこが「最初に現れる（1つ目の）位置」です。

C 列がエラーを起こしたら、D列以降も全部エラーになります。
「検索文字列」が「対象」の中にどこにも無いということです。

C 列に値が入って、D 列以降全部がエラーになることはあります。
ようするに、「検索文字列」が「対象」の中に現れるのは1箇所だけということです。

C 列以降でエラーを起こす列が飛び飛びになることはありません。
たとえば、「1つ目の検索文字列の位置」（ C 列）と「3つ目の検索文字列の位置」（ E 列）に値が入って、その他二つの列共にエラーを起こすなどは起きません。

「1つ目の検索文字列の位置」に値が入って、次に値が入るとしたら必ず「2つ目の検索文字列の位置」（ D 列）に入ります。
E 列も F 列も同じです。

当たり前ですが、すべて数式が正しければです。念のため。

FIND関数は、大文字と小文字を区別する。

図からわかりますよね。
「小文字の a」は無視して「大文字の A」の位置を返しています。

FIND関数の引数「開始位置」について

C 列のセルに入れている数式では、FIND関数の引数「開始位置」を省略しています。
この引数を省略すると、 1 （「対象」の1文字目から数える）ことを指定したのと同じ意味になります。

2つ目以降の A の位置を求めるには、引数「開始位置」に同じ行の左隣のセルを指定して 1 を足せばよいです。
1つ目の A が 5 文字目だとすると、5 + 1 で 6 文字目が「開始位置」になります。

6文字目から数えていって見つかる A の位置が、「対象」の中にある2つ目の A の位置です。

このやり方は、MID関数とFIND関数を組み合わせて文字列を抜き出すときに使う指定の仕方と同じです。

エラー以外のセルの数を数える。

「検索文字列」が「対象」の中にいくつ在るかが一眼でわかるようにしたいときもありますよね。

例のようにセル範囲にエラーを含む表なら、Excel 2010以降ではAGGREGATE関数が使えます。
（ Microsoft 365 サブスクライバーでなくても使える関数です。この関数は私も最近知りました。）

AGGREGATE関数は「エラーのセルを無視して計算」します。

書式は次のようです。
詳しくは、Excel のヘルプを見てください。

AGGREGATE(集計方法,オプション,参照)

次図（ G 列）では、引数「集計方法」に 2 、「オプション」に 6 を選んでいます。

「集計方法」の 2 はCOUNT関数を指定したことになります。
COUNT関数で、数値が入っているセルの個数を数えます。

「オプション」の 6 が「エラー値を無視」する指定です。

列見出しは「絞り込み用」としてみました。

オートフィルターで絞り込めば、「検索文字列」が「対象」の中に在る個数ごとの一覧を得られます。