エクセル 文字の置き換え コードを商品名に

質問

モデル番号と色番号が書かれたセルがＡ列に1000ほどあります。
Ｂ列に社内の誰にでも分かるように、モデル番号と色番号を商品名に変えたいと思います。

Ａ列　　　　　
a100 b
a100 r
c400 y r
c400 y

モデル番号　　　　　商品名
a100　　　　　　　　　マグカップ
c400 　　　　　　　　　 グラス

色番号　　　　　　　　　色
b 　　　　　　　　　 青
r 　　　　　　　　　 赤
y 　　　　　　　　　 黄色

B列にＡ列のセルがa100 bならばマグカップ青と出るように関数を入力したいです。
上記例ではモデル番号2種類、色番号3種類ですが、それぞれ50種類ほどあります。
色々試したのですが、式が長くなるばかりで上手く行かず、お知恵を拝借できれば幸いです。
分かりにくい説明かもしれませんが、よろしくお願いいたします。

回答No.3

tom04

こんにちは！

A列データはスペースで区切られているとします。
＞c400 y r
のような場合は
マグカップ黄色赤
のように表示したいのでしょうか？
そうであれば関数では厄介ですので、VBAでの一例です。

↓の画像のように左側がSheet1・右側がSheet2とし、両Sheetとも2行目以降にデータがあるとします。

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, c As Range, r As Range
Dim str As String, buf As String, wS As Worksheet, myAry
Set wS = Worksheets("Sheet2")
With Worksheets("Sheet1")
On Error Resume Next '★←念のため
For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
myAry = Split(StrConv(.Cells(i, "A"), vbNarrow), " ")
Set c = wS.Range("A:A").Find(what:=myAry(0), LookIn:=xlValues, lookat:=xlWhole)
buf = c.Offset(, 1)
For k = 1 To UBound(myAry)
Set r = wS.Range("C:C").Find(what:=myAry(k), LookIn:=xlValues, lookat:=xlWhole)
buf = buf & "," & r.Offset(, 1)
Next k
.Cells(i, "B") = buf
Next i
End With
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

回答No.2

chie65536（@chie65535）

B列に、元のA列の「a100 b」から「モデル番号」を取り出す式を書いて下さい。

=MID(A1,1,FIND(" ",A1)-1)

C列に、元のA列の「a100 b」から「1番目の色番号」を取り出す式を書いて下さい。

=MID(A1,FIND(" ",A1)+1,1)

D列に、元のA列の「a100 b」から「2番目の色番号」を取り出す式を書いて下さい。

=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)

「2番目の色番号」が無い場合「#VALUE!」と表示されますが、気にしないで下さい。

Sheet2に「A列にモデル番号、B列に商品名」が書かれた表を、Sheet3に「A列に色番号、B列に色名」が書かれた表を作って下さい。

E列に、取り出したB列の「a100」から商品名を検索する式を書いて下さい。

=VLOOKUP(B1,Sheet2!A:B,2,FALSE)

F列に、取り出したC列の「1番目の色番号」から色名を検索する式を書いて下さい。

=VLOOKUP(C1,Sheet3!A:B,2,FALSE)

G列に、取り出したD列の「2番目の色番号」から色名を検索する式を書いて下さい。

=IF(ISERR(D1),"",VLOOKUP(D1,Sheet3!A:B,2,FALSE))

ここでD列が「#VALUE!」になっている場合は空白になりますから、D列のエラーは気にしなくて良いです。

H列に、E～G列で取り出した結果を連結して表示する式を書いて下さい。

=E1&" "&F1&" "&G1

B1～H1を、下方向に必要なだけコピーして下さい。

ここまで実行すると、添付画像の上側のようになります。

B～G列を範囲指定して、書式→列→表示しない、を実行して、B～G列を隠します。

ここまで実行すると、添付画像の下側のようになり、完成します。

なお「色番号は1個か2個」に限定しています。色番号が3個以上になる事は考えていません。

回答No.1

aokii

モデル番号 商品名がC,D列、
色番号 色がE,F列の場合、
B１セルに以下の式を入れて、下にドラッグコピー。
=VLOOKUP(LEFT(A1,4),C:D,2,FALSE)&VLOOKUP(MID(A1,6,5),E:F,2,FALSE)