Excelにて文字列の中から該当する言葉があった場合、マスタから抽出

Question

###前提・実現したいこと
Excelの関数もしくはマクロで実現したいです。
下記のようにＡ列に出身の大学(院)・学部(科)があり、Ｃ列には学部、Ｄ列には学部を略称した文字があります。
Ａ列の中にＣ列の学部が入っていれば、Ｂ列にＤ列の学部を略称した文字を入れたいです。もし該当するものが無ければ、『無し』とＢ列に入力したいです。

＜現状＞ （Ａ列やＣ列・Ｄ列は５００以上のデータがあります）（...は列を整えるために入れたので、実際のExcelでは入っていません）
Ａ列.....................................Ｂ列.........Ｃ列...........Ｄ列
大阪大学外国語学部卒.............................医学部..........医学
長野大学社会福祉部卒.............................国際教養学部....国際
京都大学大学院工学研究科修了...................音楽学部........音楽
北海道大学法学部卒................................外国語学部.......外国
北海道大学大学院医学研究科修了.................健康科学部.......健康
九州大学教育学部卒.................................応用化学部.......化学
福岡大学応用化学部卒...............................社会福祉学部....福祉
一橋大学法学部卒...................................人間社会学部......社会
..........................................................法学部............法学
..........................................................工学研究科.......工学
..........................................................医学研究科.......医学

↓ ↓ ↓

＜こうしたい＞
Ａ列.....................................Ｂ列.........Ｃ列...........Ｄ列
大阪大学外国語学部卒................外国........医学部..........医学
長野大学社会福祉部卒................福祉........国際教養学部....国際
京都大学大学院工学研究科修了.....工学.........音楽学部........音楽
北海道大学法学部卒...................法学........外国語学部.......外国
北海道大学大学院医学研究科修了...医学........健康科学部.......健康
九州大学教育学部卒...................教育.........応用化学部.......化学
福岡大学応用化学部卒.................化学........社会福祉学部....福祉
一橋大学法学部卒.......................法学.......人間社会学部......社会
..........................................................法学部............法学
..........................................................工学研究科.......工学
..........................................................医学研究科.......医学

様々なサイトを参考にしながら、FIND関数やVLOOK関数、ISNUMBER関数など組み合わせて色々試してみましたが、上手く表示させることができずに苦戦しております。
ですので、未熟な私ですがご教示頂ければと思います。

Comments

[szk.]

今の＜現状＞と条件からすると＜こうしたい＞のB列は全て「無し」になるかと思います。パターン化できないのでもう少し実データに基づいて記載してください。あとB列に入れる文字は、D列と同じか「無し」になるということでよいでしょうか。最後にmarkdownは表が使えるので一覧はそれを使ってください。

Answer 1

下記関数をB2セルに貼り付け、他の行にコピペすれば期待している結果になると思います。

=IFERROR(LOOKUP(0,0/FIND($C$1:$C$11,A2),$D$1:$D$11),"無し")

Comments

[sincos41]

教えてくださり、ありがとうございます。シンプルで汎用性があるのでベストアンサーにさせて頂きました。

Answer 2

なんらかのリストから別のリストを元に検索したい時、よく使われるのはindexとmatchの組み合わせです

今回のケースではこんな感じに組み合わます

=INDEX($D$1:$D$11,MATCH(TRUE,INDEX(ISNUMBER(FIND($C$1:$C$11,$A1)),),0))

これをB1に入れて下方向へコピーします

ご提示のデータではD列に「医学」が２つあって重複しているので、医学研究科を「医研」などにした方がよいかも？

「無し」を表示したい場合を考えてみましたが、ちょっと思いつかないので強引なやりかたで、、、

=IF(ISNUMBER(MATCH(TRUE,INDEX(ISNUMBER(FIND($C$1:$C$11,$A1)),),0)),INDEX($D$1:$D$11,MATCH(TRUE,INDEX(ISNUMBER(FIND($C$1:$C$11,$A1)),),0)),$E$1)

E1セルに「無し」を入れておけば、これでとりあえず欲しい結果は出てます

もっとよい方法が他の方から出るかもしれませんがご参考まで

Comments

[sincos41]

回答ありがとうございます。他の方がシンプルで使いやすい回答を頂きましたので、そちらで今回は解決致しました。takitoさんの回答もスキル向上のため勉強させて頂きます、

Answer 3

VBAで、オリジナル関数を作成して、対応する方法を推奨します。

もし、結果が、D列にあるのなら、可能性のある学部を、D列に入力しておく必要があります。

そして、下の様なFunction関数を作成しましょう。

Option Explicit

Public Function GET学部(学部 As String) As String
    Dim x As Long
    Dim i As Long
    Dim F As Long
    
    x = Workbooks("Sample1.xlsm").Worksheets("Sheet1").Range("D1").End(xlDown).Row
    
    ReDim 略称(1 To x) As String
    
    For i = 1 To x
        略称(i) = Workbooks("Sample1.xlsm").Worksheets("Sheet1").Cells(i, 4).Value
    Next i
    
    For i = 1 To x
        F = 0
        On Error Resume Next
        F = Application.WorksheetFunction.Search(略称(i), 学部, 1)
        Err.Clear
        If F > 0 Then
            GET学部 = 略称(i)
            Exit For
        End If
    Next i
    
End Function

そしたら、シートに関数を入れて結果を出します。

空白で結果がでているのは、D列に学部の登録が無いためです。
下に追加することによって、表示されるようになります。

Comments

[sincos41]

回答ありがとうございます。他の方がシンプルで使いやすい回答を頂きましたので、そちらで今回は解決致しました。kai_keitaiさんの回答もスキル向上のため勉強させて頂きます、

Answer 4

Excelのワークシート関数で正規表現を使う の記事を参考に時前の正規表現関数REGEXEC()をVBAのmodule1に用意します。

そして、例えばB2セルになら、

=regexp(A2,"(大学大学院|大学)(.+)(研究科|学部|部卒)",1)

```のように活用します。
微妙に学部や学が入るかは入らないかの基準があいまいなので、式の中の | 区切りで前後の絞り込みを工夫してみて下さい。
いざとなったらテキスト複写ののちに置き換え機能で変えることになるでしょう。

Comments

[sincos41]

回答ありがとうございます。他の方がシンプルで使いやすい回答を頂きましたので、そちらで今回は解決致しました。seastar3さんの回答もスキル向上のため勉強させて頂きます、