特定の文言が含まれていた場合、分類表に従い、分類をする

Question

以下の参照シートの検索文言①②に記載されている文言が、

以下の情報シートのA列に含まれる場合、

検索文言①の情報はE列に分類①の値を返す、検索文言②の情報はF列に分類②の値を返す。

というような対応をしたいです。

・情報シートのA列には規則性がなく、何文字目に検索文言が含まれるかまったくわからない状態です。

・参照シートの検索文言①はこれ以上増えませんが、検索文言②は今後も追加できるような状態にしたいです。

対応方法をご教授いただけますでしょうか。

Answer 1

こんにちは。

提示されているような「参照」シートと「情報」シートを手元の環境で作り、どのような数式にすればよいか考えてみました。

尚、「検索文言①」の数字部分の桁数は 3文字で統一されている場合で考えています。

（全体で統一されていれば、4文字でも 5文字でも良いのですが、統一されていないと検索結果に影響します。）

以下、その数式の一例です。

・「情報」シートの E2 セルの数式

=IFERROR(INDEX(参照!C$3:C$6,MATCH(10^6,FIND(参照!B$3:B$6,A2))),"")

・「情報」シートの F2 セルの数式

=IFERROR(INDEX(参照!F:F,MATCH(10^6,FIND(INDIRECT("参照!E3:E"&COUNTA(参照!E:E)+1),A2))+2),"")

※どちらの数式も入力後（またはコピー・貼り付け後）に、Ctrl＋Shift＋Enter キー押下で確定して配列数式にする必要があります。

※配列数式になると、数式全体が自動的に {  } で囲まれますので必ずこれを確認してください。

あとは、これらの配列数式を下方向（行方向）に必要なだけコピーしてください。

コピーする行数は、A 列の検索対象文字列が何行くらい入力されているかによりますが、将来的に 1000 行くらいになる予定があるのでしたら、1000 行分コピーしておけば良いかと思います。

尚、コピー方法はオートフィルコピーがお勧めです。

・https://dekiru.net/article/12457/

図１は、これらの数式を実際に入れてみた場合の結果です。

F 列の数式は、「参照」シートの「検索文言②」と「分類②」が増えていった場合にも対応できるようになっていますが、「検索文言②」と「分類②」に追記する場合には、間に空行を挟まないようにご注意ください。

それと、見えてはいませんが、E 列、F 列共に 1000行分の数式をコピーしています。

・図１

＜配列数式の動作概要＞

・E2 セルの配列数式について

まず、A2 セルの文字列中から、FIND 関数を使い「参照」シートの B$3:B$6 のセル範囲の値を順に検索し、見つかった文字位置の配列（文字位置／#VALUE!）を作り MATCH 関数に渡します。

MATCH 関数では、渡された配列を基にして、「参照」シートのセル範囲の中の該当の行位置を求め INDEX 関数に渡します。

INDEX 関数では、「参照」シートの C$3:C$6 のセル範囲の中から、MATCH 関数から渡された行位置のセルの値を表示します。

・F2 セルの配列数式について

まず、COUNTA 関数を使い「参照」シートの E 列の中から文字や数値などの入っているセルの数を求め、行番号に変更するためこれを +1 した値を INDIRECT 関数に渡します。

INDIRECT 関数では、渡された行番号を使い「参照」シートで参照するべきセル範囲を作り出し FIND 関数に渡します。

FIND 関数では、A2 セルの文字列中から、渡されたセル範囲の値を順に検索し、見つかった文字位置の配列（文字位置／#VALUE!）を作り MATCH 関数に渡します。

MATCH 関数では、渡された配列を基にして、「参照」シートのセル範囲の中の該当の行位置を求め INDEX 関数に渡します。

INDEX 関数では、「参照」シートの F 列のセル範囲の中から、MATCH 関数から渡された値に +2 した行位置のセルの値を表示します。

尚、どちらの配列数式も、検索文字列が見つからない場合のエラー表示を IFERROR 関数で空白に変えています。

これにより、数式の行方向へのコピーが多めであってもエラー表示されないようになっています。

以上です。

Windows10 と Excel2016 の組み合わせで動作確認しています。

ご参考になれば幸いです。

Answer 2

ご教授いただきありがとうございます。

問題なく分類ができました。

ただ、仕組みが理解しきれていないので…勉強します。

本当にありがとうございました。