EXCEL関数で部分一致同士を検索する方法

質問

まずは添付画像にてご確認ください。

A列目（黄色の網掛け部分）は、原材料の食品名
1行目（緑の網掛け部分）は、チェックしたい項目を指しています。

B2～H3のセルでは、A列に入力した内容が、それぞれB1～H1のチェック項目に
該当する場合は「○」を、該当しない場合には「-」を入力していました。

今までは手入力で何とかなっていたのですが、複数人で管理するようになったため
B2～H3部分を関数処理にて管理することが出来ないかどうかをお聞きしたいのです。

人によって入力の癖が違うので、例えば、「ざるそば、ザル蕎麦」「卵焼き、玉子焼」と
いう形で入力されていても、同じもの「そば」「たまご」として認識させされる方法があるのであれば
教えてもらえないでしょうか。

※今回は表記のゆれ等でも全て認識したいので、1行目に全てまとめて入力していたのですが、
　　COUNTIFやSEARCHなどを使用することで複数行必要になるかどうかも教えていただけると
　　幸いです。

なお、使用しているバージョンは、EXCEL2003です。
ぜひとも、よろしくお願いします

回答No.2

tom04 · ベストアンサー

こんにちは！
表のレイアウトを変えてはダメな場合、VBAになってしまいますが、
一例です。
アップされている画像の配置通りだとして・・・

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, j As Long, endRow As Long, endCol As Long, k As Long, myArray
endRow = Cells(Rows.Count, "A").End(xlUp).Row
endCol = Cells(1, Columns.Count).End(xlToLeft).Column
If endRow > 1 And endCol > 1 Then
Range(Cells(2, "B"), Cells(endRow, endCol)).ClearContents
End If
For i = 2 To endRow
For j = 2 To endCol
myArray = Split(Cells(1, j), "、")
For k = 0 To UBound(myArray)
If InStr(Cells(i, "A"), myArray(k)) > 0 Then
Cells(i, j) = "○"
Exit For
Else
Cells(i, j) = "-"
End If
Next k
Cells(i, j).HorizontalAlignment = xlCenter
Next j
Next i
End Sub 'この行まで

表のレイアウトを変更しても良いのであれば、関数でも対応できると思います。
↓の画像のように1行目の項目を各セルに一つずつ分けて入力します。

※　この場合、項目数が一番多い列の行数に合わせ、空白セルができないよう
まずA列に使用されることはないであろう、アスタリクス「*」を入れておきます）

画像ではB5セルに
=IF($A5="","",IF(OR(ISNUMBER(FIND(B$1:B$4,$A5))),"○","-"))

これは配列数式になりますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合は、
上記数式をドラッグ＆コピー　→　B5セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
最後にB5セルを列・行方向にオートフィルでコピー！
これで画像のような感じになります。m（_ _）m

お礼

ご回答ありがとうございます。

1行目に全ての項目を記入するという方法自体が不安だったこともあり、また、複数で管理するという特性上、全ての人がVBAを使えない事情からも関数で、と思っていました。
その上、スマートな関数で、しかもCOUNTIFを使用した場合の7個という制約も気にすることなく運用できることが分かり安堵しています。
今回のご回答を元に、より精度を高めたリスト作成と運用をしていきます。

上記のことからベストアンサーとさせていただきます。
改めてありがとうございました。

回答No.4

kagakusuki

御質問にある様に、チェックしたい項目に該当する文字列が、A列（黄色の網掛け部分）に入力されている原材料の食品名の中に見つかった場合にのみ「○」を、見つからなかった場合には「-」を表示させる、というだけの事でしたら回答No.2様や回答No.3様の方法でも一応は可能です。
　ですが、御質問の件は「アレルギーの原因となり得る食材が使われているのか否かの判定」を自動で行うという事ではないでしょうか？
　その場合、もし、機械に（文字通り）機械的に判定させますと、黄色の網掛け部分に書かれている食材の中に、アレルギーの原因と成り得る原料で作られていながら、緑の網掛け部分に書かれていない食材があった場合には、アレルギーの原因となる食材が使われているにも関わらず「-」が表示されてしまう事になります。
　そして、その様な誤った判定結果が表示されていても、それを見た人間は
「パソコンのリストには○印が無いから大丈夫だな」
と考えてしまい、使われている食材をチェックし直す事を怠る様になってしまう恐れがあります。
　もしも、緑の網掛け部分に入力されている食材のリストが、アレルギーの原因となる全ての食材の名称を、表記のゆれ等も含めて完全に漏らさず網羅しているという事であれば、機械任せでも構わないのですが、現実的にはその様な完璧なリストを作る事は困難です。
　アレルギーの原因となり得る食材のチェックは人命に関わる事なのですから、実際には不完全なリストに対して、完璧なリストであると誤った信頼感を持ち、人間がチェックを怠る様になりかねないシステムを使用する事は危険だと思います。
　そういった意味で、私は回答No.1において「無理だと思います」と述べた訳です。

　例えば、小麦が使われている食材の例を今思いつくままに挙げてみますと、
小麦
こむぎ
コムギ
小麦粉
こむぎ粉
コムギ粉
メリケン粉
めりけん粉
饂飩粉
うどん粉
ウドン粉
唐揚げ粉
唐揚粉
からあげ粉
醤油
しょうゆ
ショウユ
正油
しょう油
ショウ油
味噌
みそ
ミソ
麩
ふ
フ
麺麭
パン
ぱん
パスタ
ぱすた
マカロニ
スパゲッティ
スパゲッティー
ラーメン麺
ラーメンの麺
らーめん麺
ラーメン
蕎麦麺
蕎麦の麺
そば麺
ソバ麺
素麺
ソーメン
そーめん
そうめん
ソウメン
餃子
ぎょうざ
ギョウザ
カステラ
かすてら
スポンジ（ケーキ等の）
等々があり、上記にあげたもの以外にもまだまだ沢山あります。
　又、例えば、黄色の網掛け部分に記入されている食材の中に「調味料」というものがあった場合、その調味料が味噌や醤油であった場合には、小麦の所を「○」としなければなりませんが、味噌や醤油ではなくマヨネーズであった場合には小麦の所は「○」ではなく「-」とし、代わりに卵の所を「○」としなければなりませんが、その様な判断を機械が出来る訳がありません。
　同様に、「タンパク加水分解物」や「アミノ酸」の場合も、化学的に分解されたものとはいえ、分解し切れななかったタンパク質が残っていないとは限りませんから、その「タンパク加水分解物」や「アミノ酸」の原料として小麦等が使われていないかどうかを確認する必要があります。
　そのため、機械任せにはせず、どうしても人間がチェックする必要があると思います。

お礼

No.1とNo.4でのご回答ありがとうございます。

もちろん、ご指摘の件はその通りです。
表記のゆれを含めて100％網羅できるリストを作ることは不可能ということも承知しており、二重三重のチェックが必要であることも承知しています。
その上で、まずは作業の第一段階として想定しうる全ての食材を判別し、その上で、第二段階として漏れや抜け、微妙なものがあった場合は、関数ではなく直接「○」「－」をセルに打ち込むという流れを採りいれています。さらに複数人によるチェックも経ています。
だからといって、これで完璧ということも無く、「これはあくまで100％完璧なリストではない」ということも念頭に入れた上で運用するものです。

※ここまでを書いた上で質問をすればよかったですね、申し訳ありません。また当初は「－」ではなく「×」の予定でしたが、回答者様のような意見もあったうえで、「-」にしていることも補足しておきます。

上記のことから、まず第1段階のリスト作成を省力化したくて質問させていただいたことをご理解いただければと思います。そのため、あえてアレルギーということは伏せていたのですが、このような形で改めて認識をさせていただきましたこと、改めてお礼させていただきます。

回答No.3

bunjii

>※今回は表記のゆれ等でも全て認識したいので、1行目に全てまとめて入力していたのですが、COUNTIFやSEARCHなどを使用することで複数行必要になるかどうかも教えていただけると幸いです。
1行目に対象品目を全て纏めるとCOUNTIF関数で処理するのは難しいようです。
代案として対象品目を1品1セルにすればCOUNTIF関数の加算で判定できます。

=IF(COUNTIF($A6,"*"&B$1&"*")+COUNTIF($A6,"*"&B$2&"*")+COUNTIF($A6,"*"&B$3&"*")+COUNTIF($A6,"*"&B$4&"*")+COUNTIF($A6,"*"&B$5&"*"),"○","－")

お礼

COUNTIFを使った形でのご解答ありがとうございました。

やはり1行目に全ての食材をまとめるという方法がよくなかったみたいですね。
そこで取り掛かってみたのですが、COUNTIFでは8項目以上ではうまく行かないこともあるみたいです（No.4の回答者様のご指摘も踏まえ）。

それでも、方法としてこのようなやり方があるということを勉強させていただきました。ありがとうございました。

回答No.1

kagakusuki

>人によって入力の癖が違うので、例えば、「ざるそば、ザル蕎麦」「卵焼き、玉子焼」という形で入力されていても、同じもの「そば」「たまご」として認識させされる方法があるのであれば教えてもらえないでしょうか。

　蟹が使用されていない「カニカマ」、小麦が使用されていない「パンプキン」や「パンナコッタ」、蕎麦の実が使用されていない「やきそば」、乳製品ではない「豆乳」や「ココナッツミルク」、
小麦が使用されていながら「小麦」の文字が無い「うどん」、「そば」、「やきそば」、「ラーメン」、「餃子」、「焼売」、「ビスケット」、「クッキー」、「カステラ」等々、卵が使用されていながら「卵」の文字が無い「プリン」、「出汁巻き」、「マヨネーズ」、「カステラ」等々、牛乳が使用されていながら「乳」の文字が無い「バター」、「チーズ」、「アイス」、「カフェオレ」、等々、
といった様なパターンが多数存在しますので、無理だと思います。