１つのセル内で区切られているデータの変換について

質問

他のEXCELに関するサイトでもお聞きしているのですが、
EXCELで出来るのかわからないのですが知っている方がいらっしゃれば教えてください。

１つ目のシートの列データとして、例えば、
セルA1に、所属ID
セルB1に、1;2;3;4;5;6;7とセミコロンで続けられたデータがあるとします。

２つ目のシートには下記のような所属情報のデータ(マスタのようなもの)があるとします。
セルA1に、1
セルB1に、総務
セルA2に、2
セルB2に、経理
セルA3に、3
セルB3に、人事
：

１つ目のシート内の1;2;3;4;5;6;7のデータをわかり易いようにマスタにある総務;経理;人事などのようなデータへ変換させることは出来るのでしょうか？
==============================================
結果イメージ、１つ目のシートのセルB1の内容
（現在）1;2;3;4;5;6;7
（変換後）総務;経理;人事・・・
==============================================

すみませんが、よろしくお願いします。

回答No.6

fumufumu_2006

こんなのではどうでしょうか？

Option Explicit
Function 所属名(s As String) As String
Dim ws As Worksheet
Dim d() As String
Dim r() As String
Dim i As Integer
Dim lastRow As Integer
r = Split("", " ") 'ubound(r)=-1
Set ws = Sheets("sheet2") '所属情報のシート名
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row '所属情報の最後の行（A列で判断)
d = Split(s, ";")
For i = 0 To UBound(d)
On Error Resume Next
d(i) = WorksheetFunction.VLookup(Val(d(i)), ws.Range("A1:B" & lastRow), 2, False)
If Err.Number = 0 Then '見つかった？
ReDim Preserve r(UBound(r) + 1)
r(UBound(r)) = d(i)
End If
On Error GoTo 0
Next
所属名 = Join(r, ";")
End Function

回答No.5

fumufumu_2006

VBAでユーザー関数を作るのはどうでしょうか？
どうしても既存の関数では解決できない場合はお試しください。
２つ目のシート（所属情報）のシート名はSheet2にしてありますので、違う場合は変更してください。

[ツール][マクロ][Visual Basic Editor]で表示されるウインドウで、
[挿入][標準モジュール]で表示される画面に下をコピーしてください。
Option Explicit
Function 所属名(s As String) As String
Dim ws As Worksheet
Dim d() As String
Dim i As Integer
Dim lastRow As Integer
Set ws = Sheets("sheet2") '所属情報のシート名
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row '所属情報の最後の行（A列で判断)
d = Split(s, ";")
For i = 0 To UBound(d)
On Error Resume Next
d(i) = WorksheetFunction.VLookup(Val(d(i)), ws.Range("A1:B" & lastRow), 2, False)
If Err.Number <> 0 Then
d(i) = "未定義" '#N/Aの場合[未定義]に
End If
On Error GoTo 0
Next
所属名 = Join(d, ";")
End Function

１つ目のシートの適当な所(例えばB2)に
=所属名(B1)
と入力してください。

お礼

fumufumu_2006さん、アドバイス頂きました皆様
ありがとうございました。VBAの方法で私がイメージしていた事が出来ました。

また、一つ質問なのですが、
この未定義部分の処理を、[未定義]とするのではなく、不要なので該当データ部分については、区切り文字含めて削除するという事は出来るのでしょうか？

回答No.4

hallo-2007

取り合えず、データを区切って、縦にしませんか
B1　1;2;3;4;5;6;7　　データ　区切り位置で　セミコロンにチェック　OK
B2　に=INDEX(A$1:CZ$1,1,ROW(A1))　下フィル
２つ目のシートのデータも、コピィ　形式を指定して貼り付け　行列入れ替えるにチェック入れて貼り付け

データを縦方向にして　C2に　Vlookup関数

お礼

hallo-2007さん、回答ありがとうございます。
データの行数も多いため、縦にするのは現実ではないんです。
あまり、こういうデータの扱いはEXCELで無い方がいいのでしょうか？

回答No.3

noname#79209

100個！？
表の作り方（データ）が異様ですねぇー。
100個も羅列する意味があるのでしょうか。
100個羅列したら、横に長ーい表になり、全てを表示できないでしょうから、
意味ないのでは？

お礼

vizzarさん、回答ありがとうございます。

そうなんです。１つのセル内がそのようなデータになっています。ただ、それぞれの列データではないので、表示などは問題ない状態です。

回答No.2

noname#79209

#1です。
7つぐらいで、且つ表記だけの問題なら、手で直した方が速いような気がしますが．．．．

回答No.1

noname#79209

7つだけなら、ダサイですが．．．
=VLOOKUP(MID($A$1,1,1),Sheet2!$A$1:$B$10,2,FALSE)&":"&VLOOKUP(MID($A$1,3,1),Sheet2!$A$1:$B$10,2,FALSE)・・・
ではダメですか？

補足

vizzarさん、返信ありがとうございます。
説明不足でした。

１；２；３；４；５；６；７と書いたのは例で、
実際のデータとしては、２３４５；３４；５６７；９９９９；１などのようになっており、MIDで指定場所から取ることが出来ず、
また、
最小では、一つ（例１；）
最大では、１００個程度（例２３４５；３４；５６７；９９９９；１・・・）などのセミコロンで区切られたデータもあります。

このような場合でも可能でしょうか？