VLOOKUPで行き詰まったので、教えてください。 下記のような会社の様々な種類の電話データがありま

VLOOKUPで行き詰まったので、教えてください。
下記のような会社の様々な種類の電話データがあります。

A列:社員番号
B列:氏名
C列:電話の種類(外線・内線・携帯)(例:携帯)
D列:C列の種別が混雑した電話番号(例:070-1234-5678)
※各行のC列とD列は種別が合致している。

これを下記のようにVLOOKUP等で別シートに種別ごとに並べ替え、社員番号別電話番号一覧を作成したいです。

A列:社員番号
B列:氏名
C列:携帯番号
D列:内線
E列:外線

C列で種別にフィルタをかけて別シートにコピペしてVLOOKUPをかければ希望通りのデータが取得出来ますが、なるべく元データを加工せず取得出来るような関数にしたいです。
何かVLOOKUPにひと手間かけるのかもしれませんが、一歩及びません。
どうぞよろしくお願いいたします。

No.4 回答者： tatsu99 回答日時： 2016/10/18 09:40

No3です。

1行訂正します。
MaxRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める
上記を
MaxRow = Sh1.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める
に変えてください。(sheet1をSh1に変える）

この回答へのお礼

わざわざ書いていただき、ありがとうございます！
マクロの中身を見ながら後ほど動作確認してみます。

お礼日時：2016/10/18 20:05

No.3 回答者： tatsu99 回答日時： 2016/10/18 09:29

以下のマクロを登録し、実行してください。

（標準モジュールへ登録）
社員番号別電話番号一覧のシート名は、sheet2でつくっています。
必要であれば、適宜、名前を変えてください。
-----------------------------------------------
Option Explicit

Public Sub Macro()
Dim Sh1, Sh2 As Worksheet
Dim MaxRow As Long ' 最終行
Dim key As String ' 検索キー
Dim row1 As Long 'sheet1の行番号
Dim row2 As Long 'sheet2の行番号
Dim row As Long '行番号(sheet2一時作業用)
Dim dicT As Object '連想配列
Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義

Set Sh1 = Worksheets("alldata") ' 元の電話番号一覧
Set Sh2 = Worksheets("sheet2") ' 社員番号別電話番号一覧
Sh2.Cells(1, 1).Value = "社員番号"
Sh2.Cells(1, 2).Value = "氏名"
Sh2.Cells(1, 3).Value = "携帯"
Sh2.Cells(1, 4).Value = "内線"
Sh2.Cells(1, 5).Value = "外線"
row2 = 2
MaxRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める

' 元の電話番号一覧を読み込む
With Sh1
For row1 = 2 To MaxRow ' 最終行まで行う
key = .Cells(row1, 1).Value
If dicT.exists(key) = False Then
dicT(key) = row2
row = row2
row2 = row2 + 1
Sh2.Cells(row, 1).Value = .Cells(row1, 1).Value '社員番号
Sh2.Cells(row, 2).Value = .Cells(row1, 2).Value '氏名
Else
row = dicT(key)
End If
Select Case .Cells(row1, 3).Value
Case "携帯"
Sh2.Cells(row, 3).Value = .Cells(row1, 4).Value '携帯の電話番号
Case "内線"
Sh2.Cells(row, 4).Value = .Cells(row1, 4).Value '内線の電話番号
Case "外線"
Sh2.Cells(row, 5).Value = .Cells(row1, 4).Value '外線の電話番号
End Select
Next
End With
End Sub
------------------------------------------------------

No.2 回答者： tatsu99 回答日時： 2016/10/18 05:40

マクロで作成しても良いならそのマクロ提供することは可能です。

会社の様々な種類の電話データのシート名は何でしょうか？
社員番号別電話番号一覧のシート名は何でしょうか？

一回のマクロの呼び出しで、会社の様々な種類の電話データのシートを基に、
社員番号別電話番号一覧のシートを一気に作成します。
従って、会社の様々な種類の電話データのシートの一部を修正した場合は、
その都度、あなたがマクロを実行する必要がありますが、それでも良いならマクロを提供します。

この回答へのお礼

ありがとうございます。
マクロでも問題ありません。
シート名はalldataです。
シートは番号に変動がある度に更新する必要があります。
どうぞよろしくお願いいたします。

お礼日時：2016/10/18 08:00

No.1 回答者： 銀鱗 回答日時： 2016/10/18 00:27

無理。

むしろMATCH関数とINDEX関数の組み合わせで拾ってきたほうが良いと思います。

1行の中に、
「携帯番号」（あるいは「携帯」）と書かれたセルが何列目にあるのかMATCH関数で調べて、
INDEX関数で次の列にある値を拾ってくる。
のような手順のほうが簡単だろうと思います。

　＝MATCH(”携帯”,A1:D1)

これで1行目に「携帯」と書かれたセルが、A1セルからD1セルの中の何番目にあるかが分かります。

　＝INDEX(A1:D1,MATCH(”携帯”,A1:D1)+1,0)

これで”携帯”と書かれた次の列の値を参照できます。

・・・
エラーに関する処理は行っていませんので、「携帯」と書かれたセルが無い場合はエラーになります。
エラーになったら空白を表示するような処理を別途行ってください。

IF関数とISERROR関数の組み合わせ、
または、IFERROR関数（Excel2007から追加された関数）
で処理できると思います。

この回答へのお礼

ありがとうございます。
教えていただいた方法で試してみます。

お礼日時：2016/10/18 08:01