Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
VLOOKUP関数について
投稿日時: 22/05/05 17:45:00
投稿者: kips

お世話になっております。
VLOOKUP関数について教えてください。
 
VLOOKUP関数は複数シートを参照して値を探し出してくれるのでしょうか?
 
例えば
 
「検索結果シート」
 ___A__ ______B______ ___C___ ____D___
   名前    年齢    住所   仕事
 1 Aさん    21    〇〇   △△
 2 Cさん    45    〇〇   △△
 3 Fさん    33    〇〇   △△
 4 Gさん    56    〇〇   △△
 5 Iさん    29    〇〇   △△
 
「〇〇会員シート」
 
 ___A__ ______B______ ___C___ ____D___
   名前    年齢    住所   仕事
 1 Aさん    21    〇〇   △△
 2 Bさん    38    〇〇   △△
 3 Cさん    45    〇〇   △△
 
「▲▲会員シート」
 
  ___B__ ______C______ ___D___ ____E___
   名前    年齢    住所   仕事
 1 Dさん    62    〇〇   △△
 2 Eさん    19    〇〇   △△
 3 Fさん    33    〇〇   △△
 
「□□会員シート」
 
  ___E__ ______F______ ___G___ ____H___
   名前    年齢    住所   仕事
 1 Gさん    56    〇〇   △△
 2 Hさん    37    〇〇   △△
 3 Iさん    29    〇〇   △△
 
上記のように検索結果シートのA列の名前に入力した人の必要な情報を3つの会員シートから検索して
それぞれのセルに反映させたいです。
 
VLOOKUPで行おうと思ったのですがこのように複数シートから探し出すときの関数の作り方が分からず
行き詰ってしまいました・・・。
 
説明が分かりづらく申し訳ありませんがお力を貸していただきたいです。
よろしくお願いします。
 
 
 

回答
投稿日時: 22/05/05 19:02:23
投稿者: WinArrow
投稿者のウェブサイトに移動

最初に質問
各シートの列が異なっていますが・・・確認してください。

回答
投稿日時: 22/05/05 20:09:16
投稿者: んなっと

F列にシート名一覧を入れておいて
 
    A   B   C   D E     F
1  名前 年齢 住所 仕事   〇〇会員
2 Aさん  21 〇〇 △△   ▲▲会員
3 Cさん  45 〇〇 △△   □□会員
4 Fさん  33 〇〇 △△       
5 Gさん  56 〇〇 △△       
6 Iさん  29 〇〇 △△       
 
B2
=IFERROR(VLOOKUP($A2,INDIRECT("'"&LOOKUP(1,0/COUNTIF(INDIRECT("'"&$F$1:$F$10&"'!A:A"),$A2),$F$1:$F$10)&"'!A:D"),COLUMN(),FALSE),"")
右方向・→下方向・↓

投稿日時: 22/05/06 09:30:02
投稿者: kips

WinArrow さんの引用:
最初に質問
各シートの列が異なっていますが・・・確認してください。

 
回答ありがとうございます。
 
説明不足ですみません。
 
各シートによって入力されている情報が違う為列が異なっております。
例えば〇〇会員シートには性別が入っているけど、▲▲会員シートには入っていません。
 
このように少しずつ列が違っています(-_-;)

投稿日時: 22/05/06 09:32:23
投稿者: kips

んなっと さんの引用:
F列にシート名一覧を入れておいて
 
    A   B   C   D E     F
1  名前 年齢 住所 仕事   〇〇会員
2 Aさん  21 〇〇 △△   ▲▲会員
3 Cさん  45 〇〇 △△   □□会員
4 Fさん  33 〇〇 △△       
5 Gさん  56 〇〇 △△       
6 Iさん  29 〇〇 △△       
 
B2
=IFERROR(VLOOKUP($A2,INDIRECT("'"&LOOKUP(1,0/COUNTIF(INDIRECT("'"&$F$1:$F$10&"'!A:A"),$A2),$F$1:$F$10)&"'!A:D"),COLUMN(),FALSE),"")
右方向・→下方向・↓

 
んなっと様
 
回答ありがとうございます。
 
試しましたがうまく反応しませんでした。。
〇〇会員シートには名前がB2に入っているけど検索結果シートには名前がA2に入っている場合でも大丈夫なのでしょうか?
 
知識不足で大変申し訳ございませんが宜しくお願い致します。

回答
投稿日時: 22/05/06 12:38:38
投稿者: んなっと

シートによって配置が違うんですね。気が付きませんでした。

引用:
各シートによって入力されている情報が違う為列が異なっております

まずは列の配置を揃えましょう。話はそれから。

回答
投稿日時: 22/05/06 13:32:49
投稿者: WinArrow
投稿者のウェブサイトに移動

シートごとに列位置が異なっている
という条件では、1つの数式で対応することは無理でしょう。
  
  
少し長くなるけど
検索結果用シートのセルB2に
=IF(COUNTIF(○○会員!$A:$A,$A2)>0,VLOOKUP($A2,○○会員!$A:$D,COLUMN(B1),FALSE),IF(COUNTIF(▲▲会員!$B:$B,$A2)>0,VLOOKUP($A2,▲▲会員!$B:$E,COLUMN(B1),FALSE),IF(COUNTIF(□□会員!$E:$E,$A2)>0,VLOOKUP($A2,□□会員!$E:$H,COLUMN(B1),FALSE),"NG")))
  
右へ、下へ、フィルドラッグします。
  
検索順序、○○会員→▲▲会員→□□会員
数式の条件
名前は、全シートで一意であること。重複した場合、最初のシートにヒットします。
どのシートにも存在しない名前は「NG」が返ります。
COUNYIF関数は、名前の存在有無確認用
VLOOKUP関数:必要項目取得用
 

回答
投稿日時: 22/05/06 14:23:11
投稿者: Mike

シート 検索結果        シート 〇〇会員
  A  B  C  D       A  B  C  D
1 名前 年齢 住所 仕事    1 名前 年齢 住所 仕事
2 Aさん  21 〇〇 △△    2 Aさん  21 〇〇 △△
3 Cさん  45 〇〇 △△    3 Bさん  38 〇〇 △△
4 Fさん  33 〇〇 △△    4 Cさん  45 〇〇 △△
5 Gさん  56 〇〇 △△
6 Iさん  29 〇〇 △△
 
シート ▲▲会員        シート □□会員
 A  B  C  D  E     A B C D  E  F  G  H
1  名前 年齢 住所 仕事   1     名前 年齢 住所 仕事
2  Dさん  62 〇〇 △△   2     Gさん  56 〇〇 △△
3  Eさん  19 〇〇 △△   3     Hさん  37 〇〇 △△
4  Fさん  33 〇〇 △△   4     Iさん  29 〇〇 △△
 
下準備として、次の手順で、3枚のシート 〇〇会員、▲▲会員、□□会員 の下記の各範囲にその右側に示した範囲名を設定
〇〇会員!A1:D4 〇〇会員
▲▲会員!B1:E4 ▲▲会員
□□会員!E1:H4 □□会員
 
最後は、式 ↓
=IFERROR(IFERROR(IFERROR(IFERROR(VLOOKUP($A2,〇〇会員,COLUMN(B2),FALSE),VLOOKUP($A2,▲▲会員,COLUMN(B2),FALSE)),VLOOKUP($A2,〇〇会員,COLUMN(B2),FALSE)),VLOOKUP($A2,□□会員,COLUMN(B2),FALSE)),"")
を入力したシート 検索結果のセル B2 を右2列&下にズズーッと(空白セルが表示されるまで)オートフィル
オ・シ・マ・イ

回答
投稿日時: 22/05/06 18:47:48
投稿者: WinArrow
投稿者のウェブサイトに移動

名前定義とIFERROR関数を使うと数式がすっきりしますね・・・・
掲示の数式を検証してみましたが、うまく結果が出ないようです。
なにか、考えすぎのような気がします。
   
代案
=IFERROR(VLOOKUP($A2,○○会員,COLUMN(B2),FALSE),IFERROR(VLOOKUP($A2,▲▲会員,COLUMN(B2),FALSE),IFERROR(VLOOKUP($A2,□□会員,COLUMN(B2),FALSE),"")))

回答
投稿日時: 22/05/06 19:56:31
投稿者: Mike

WinArrow さんの引用:
掲示の数式を検証してみましたが、うまく結果が出ないようです。
なにか、考えすぎのような気がします
WinArrowさん、お世話になります。
私も気になったので、今まで長い時間試行錯誤して、漸く原因を突き止めました。
結論を先に申せば、記号を使うべきところに私が漢数字を使っちまった、ということでした。
ちなみに私のIMEでは、「れい」の読みでは漢数字が変換候補の一つですが、使用した「まる」の読みでは記号だけでなく、漢数字も表示されることに気付きました。私のモニタ上では区別が困難という詭弁が付きます。(*^_^*)
お蔭様で勉強になりました。m(_._)m

回答
投稿日時: 22/05/06 20:38:04
投稿者: WinArrow
投稿者のウェブサイトに移動

>記号○と漢数字〇
なるほど・・・確かに見た目では区別が難しいですね・・・・
私もよく変換間違いすることがあります。
 
間違えやすい・・というか、見た目で区別できない文字
「ー」:HEX(30FC)と「−」:HEX(FF0D)
 
どちらかが「ダッシュ」であることはわかるが、他の一方は読みかたがわからない。

回答
投稿日時: 22/05/06 21:14:31
投稿者: んなっと

現在のままというなら、少し式が長くなりますが...
 
さらにG列にデータ範囲も追加して
 
    A   B   C   D E     F    G
1  名前 年齢 住所 仕事   〇〇会員 A2:D100
2 Aさん  21 〇〇 △△   ▲▲会員 B2:E100
3 Cさん  45 〇〇 △△   □□会員 C2:F100
4 Fさん  33 〇〇 △△           
5 Gさん  56 〇〇 △△           
6 Iさん  29 〇〇 △△           
 
B2
=IFERROR(VLOOKUP($A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDEX(INDIRECT($F$1:$F$10&"!"&$G$1:$G$10),,1),$A2),$F$1:$F$10&"!"&$G$1:$G$10)),COLUMN(),FALSE),"")
右方向・→下方向・↓

回答
投稿日時: 22/05/08 14:19:17
投稿者: んなっと

こうすればもっと短い式になります。
 
    A   B   C   D E         F
1  名前 年齢 住所 仕事   〇〇会員!A2:D100
2 Aさん  21 〇〇 △△   ▲▲会員!B2:E100
3 Cさん  45 〇〇 △△   □□会員!C2:F100
4 Fさん  33 〇〇 △△           
5 Gさん  56 〇〇 △△           
6 Iさん  29 〇〇 △△           
 
B2
=IFERROR(VLOOKUP($A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDEX(INDIRECT($F$1:$F$10),,1),$A2),$F$1:$F$10)),COLUMN(),FALSE),"")

回答
投稿日時: 22/05/10 18:03:22
投稿者: WinArrow
投稿者のウェブサイトに移動

VLOOKUP関数の第2引数:「検索範囲」は連続したセル範囲を指定することになるので、
複数のシートを指定するとはできないと思います。
 
検索範囲は、例示では3つありますから
数式だけで対応するには、検索キー(名前)が存在する「検索範囲」を取得して
VLOOKUP関数の第2引数に指定するような、数式の組立が必要です。→既にレスがあります。
 
データシートの増減があることを想定すると、
数式のメンテナンスが必要になります。
 
作業用シートを用意して、
複数シートのデータを1つにまとめる方法を紹介します。
 
1つにまとめるVBAコードを掲示しておきます。
標準モジュールに次をコピペします。

Sub データ統合()
Dim data
Dim aName, a As Long

    aName = Array("○○", "▲▲", "□□")   '各シートの対象班に名前を定義しておく

    For a = LBound(aName) To UBound(aName)
        With ThisWorkbook.Names(aName(a)).RefersToRange
            data = Application.Intersect(.Cells, .Offset(1)).Value
        End With
        With ThisWorkbook.Sheets("Work")
            .Cells.ClearContents
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(data), UBound(data, 2)).Value = data
        End With
    Next
End Sub

 
セルB2の数式
=IFERROR(VLOOKUP($A2,Work!$A:$D,COLUMN(B2),FALSE),"")
 
各シートの名前定義
1行目から名前に含めてください。
マクロの実行は、いつでもOK
名前定義の名前データは、シートに用意してもよい。
→最初のコードを変更します。
 

回答
投稿日時: 22/05/10 20:30:32
投稿者: WinArrow
投稿者のウェブサイトに移動

コードを一部修正します。
 
誤)
> .Cells.ClearContents
正)

             If a = LBound(aName) Then .Cells.ClearContents

トピックに返信