エクセル 一定の条件に沿ってデータを変換する方法を教えてください。

質問

エクセルのデータを一定の条件に沿って変換する方法を教えてください。

　　　A　　　　　　　B　
1【顧客名】　　　【変換後】
2 (株)いろは　　　　　御社
3 (株)いろは銀行　　　貴行
4 いろは信用金庫　　貴信金
5 いろは病院　　　　貴院
※A列に元データ、B列に変換後のデータを関数で表示させる。

条件としては、顧客名に一定の文字が含まれていることを判断して変換ます。

含む文字→変換結果
銀行　→貴行
信用金庫　→貴信金
病院　→貴院
どれも含まない　→御社

『御社』以外の特殊なパターンが約10あるので、IF式で条件をひたすら連ねる・・以外の方法でお願いします。
データは内容・量ともに、毎回変わるのでA列オートフィルタでB列に手入力、という方法も避けたいのです。
よろしくお願いします。

回答No.4

imogasi · ベストアンサー

語句そのものであれば、VLOOKUP関数を使い、対応表を作ればよくて簡単です。
もちろん対応表は絶対必要なのだが。
本件は社名の１部に出てくることが、関数で捉えるのを難しくしている。
それと基準をデータの方からでなく、「組織名」の方から進めない（検索を繰り返す必要あり）といけないことが、関数では難しくしている。それで配列数式などの回答になるわけ。
ーー
別法としてユーザー関数を作って見た（VBA）
１つずつ順に、組織名語句を含んでないか繰り返して調べるだけ。
標準モジュールに
Function keisho(a)
d = Range("j65536").End(xlUp).Row
For i = 1 To d
k = Cells(i, "J")
p = InStr(a, k)
If p <> 0 Then
keisho = Cells(i, "j").Offset(0, 1)
Exit Function
End If
Next i
keisho = "御社"
End Function
ーーーーー
例データ
内容については疑問あるが自信はない。
A列　　　　　　B列（関数で出た結果）
(株)いろは 御社
(株)いろは銀行 貴行 御行？
いろは信用金庫 貴信金 貴金庫？
いろは病院 貴院
ドッグ動物病院 貴院
大田信金 貴金庫
大田区役所 貴所
金井有限会社 御社
医療法人敬人会 貴院
いろは会 貴会
B1は　=keisho(A1)
と入れて下方向に式を複写。
ーー
変換（対応）テーグル　J列＋K列　文字数順出現が望ましい。
J列　　　K列
銀行 貴行
信用金庫 貴金庫
病院 貴院
信金 貴金庫
信託銀行 貴行
区役所 貴所
医療法人 貴院
会社 御社
会 貴会（最後の方に入れること）

お礼

お礼が遅くなり申し訳ありません。
VBA初心者なので理解するのに時間がかかっていました。

ユーザー関数、本当に便利ですね！
今後勉強して使っていけるように頑張ります☆★☆
本当にありがとうございました。

回答No.3

cj_mover

こんにちは

自分ではリストにする方を選びますが、リストを用いないならば、

=INDEX({"御社";"貴行";"貴信金";"貴院"},SUMPRODUCT(COUNTIF($A1,{"*銀行*";"*信用金庫*";"*病院*"})*ROW($A$1:$A$3))+1)

とか、一応、編集し易そうな数式、ということで、、、
必ずセミコロンで区切って下さいね。
２つの配列の要素数が、ひとつずれてる点もご注意を。
２つめの配列（リスト）に該当しない場合、１つめの配列（リスト）の１番目「御社」を返す意味です。
要素が増えたら
$A$1:$A$3
の 3 の部分も増やして下さい。

シート上では確認しましたが、
題意を取り違えてたらご勘弁を。

お礼

お礼が遅くなりまして申し訳ありません。

リストを作成せずに、セミコロンを用いて数式に入れる方法があるのですね。勉強になりました☆

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

回答No.2

mu2011

次の方法は如何でしょうか。
（１）変換表を別シート（sheet2)に作成
　　Ａ列　　　　Ｂ列
１　その他　　　御社
２　*銀行*　　　貴行
３　*信用金庫*　貴信金
４　*病院*　　　貴院
～
ｎ　*ｘｘｘ*　　貴ｘｘ

（２）元シートのＢ列に次の数式を設定
=INDEX(Sheet2!B:B,MAX(SUMPRODUCT(COUNTIF($A1,Sheet2!$A$1:$A$10)*ROW(Sheet2!$A$1:$A$10)),1))

お礼

お礼遅くなりまして申し訳ありません。

MAX関数で「御社」を表示させるのですね！
これなら理解できました。
ありがとうございました☆★☆

回答No.1

mshr1962

どこかに変換結果の表を作成（別シートでも可）
行|AA|AB
1|含む文字|変換結果
2|*|御社
3|銀行|貴行
4|信用金庫|貴信金
5|病院|貴院

B2=INDEX($AB$1:$AB$5,MAX(ISNUMBER(SEARCH($AA$1:$AA$5,B2))*ROW($AA$1:$AB$5)))
と入力してCtrl+Shift+Enterで確定して配列数式にする。

補足

早々にご回答いただきながら、お礼がおそくなりまして申し訳ありません。

～SEARCH($AA$1:$AA$5,B2
は
～SEARCH($AA$1:$AA$5,A2
だと思われたので変更したところ、確かにこの式でいけました。
複雑な式なので、時間をかけて解読しようと頑張ったのですが
意味が全然わかりませんでした(´＿｀。)
・・・よろしければ、今後の勉強の為に意味を教えていただければうれしいです。