excelでデータを別シートに引っ張ってきたい

質問

excelで値を検索し、返す時、返す値の横にあるデータを違うシートのセルの下に一緒に引っ張ってきたいときの関数（方法）を教えてください。

素人なので分かりにくいと思いますが、お知恵をお借りしたいと思います。
あるデータのシートの検索値を別シートに持ってきたいのですが、シート1をシート2のようにしたいのです。
／は空白
例）シート１
／A B C D E
１(1) □ ○ ▽ ◎
2 (2) ▼ ■ ◇ ◎

シート2
／ A B C D
1 (1) □
2 ／○ ▽ ◎
3 (2) ▼
4 ／■ ◇ ◎

分かりにくいかもしれませんが、
シート１の(1)（A1）を検索すると、シート2（B1）に□（これはVLOOKで引っ張ってこれるのはわかります。）
次にシート1の（C1）○をシート2の（B2）へ持ってきたいのです。シート1（D1）（E1）はシート2の（C2）（D2）へ。
しかもシート1のC列は空白セルもあり、シート1のC列が空白の場合、下にずれることなくシート1の（D1）（E1）はシート2の（C1）（D1）へ。下記＜図a＞のようになるようにしたいのです。
そして、シート2のA列に検索値として入力する(1)や(2)の値は連番ではなく、(2)の次に(5)に飛んだりします。(2)の行のC列にデータがあっても、(5)のC列にデータはないこともあります。＜図ｂ＞
＜図a＞
／A B C D
1 (1) □ ▽ ◎
2 (2) ▼ ◇ ◎

＜図ｂ＞
／ A B C D
1 (1) □
2 ／○ ▽ ◎
3 (2) ▼
4 ／■ ◇ ◎
5 (5) □ ▽ ◎
6 (7) ▼ ◇ ◎

vlookとかCLUMN関数とか考えてはみたのですが、どうもうまくいきません。
毎回作成するデータなのですが、毎回コピペで作成しています。
とても面倒なので(1)を検索したらデータが一瞬で検索できるようにしたいと試みてはみたものの、私の知識では不可能でした。
関数では無理なのでしょうか。。。
関数はあまり詳しくないので分かりやすい方法があれば、教えていただければ助かります。
関数に詳しい方、よろしくおねがいいいたします。

関数にはこだわっていません。違う方法があればそれも含めておねがいいたします。

回答No.1

kagakusuki · ベストアンサー

>シート2のA列に検索値として入力する(1)や(2)の値は連番ではなく、(2)の次に(5)に飛んだりします。

と書かれておられるという事は、Sheet2のA列の値は、関数によって表示されたデータなどではなく、全て手入力されたデータであると考えて宜しいのですね？
　それでしたら次のような方法になると思います。

　まず、Sheet2の表示は、1行目からではなく、2行目から始まる様にして下さい。
　その上で、Sheet2のB2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(VLOOKUP($A1,Sheet1!$A:$C,3,FALSE)<>"")),$A2<>"",ROWS($2:2)>1),IF(COLUMNS($B:B)=1,"【行を空けて下さい】",""),IF(IF(ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$C,3,FALSE)<>"")),$A2="",OR(AND($A2<>"",COLUMNS($B:B)>1),ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,COLUMNS($B:B)+2-($A2<>"")*(COLUMNS($B:B)=1),FALSE)<>"")))),"",VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,COLUMNS($B:B)+2-($A2<>"")*(COLUMNS($B:B)=1),FALSE)))

　次に、Sheet2のB2セルをコピーして、Sheet2のC2～D2の範囲に貼り付けて下さい。
　次に、Sheet2のB2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上で準備は完了で、後はSheet2のA2以下に検索値を入力して行きますと、御希望通りの検索結果が得られると思います。
　尚、Sheet2のA列において、Sheet1のC列の検索結果が空欄とはならない検索値が入力されている行の1つ下の行は、Sheet1のC列～E列の検索結果を表示させるために使用せねばなりませんから、そのA列のセル（御質問文の例における「／」となっているセル）は検索値を入力せずに、空欄としなければならない筈ですので、もしも間違えて検索値を入力してしまった場合には、「【行を空けて下さい】」という表示が現れる様になっております。

お礼

お礼が遅くなりました。
データを作成する時間がなく、昨日作成してみました。すごい！ちゃんと思った通りの位置に思った通りに引っ張ってこれました。
シート1の変更があった場合に自分でも関数を変更できるか試みましたが、私の頭では無理そうで(笑)
頭のいいかたの式ってすごいですね。感動しました。
これからもお願いしたいぐらいです。

本当に助かりました。有難うございました。

補足

もうひとつ教えていただきたいのですが、実はシート1のD列は数字で、それに15をかけた数字をシート2のC列へ持ってきたいのです。

なんせ15をかければよかったので、別シートを作成し、シート2を入力フォームとして入力フォームのC2
を別シートで参照し、参照したセルに15をかけることにしました。
しかし、コピーしたときに＃VALUE！が出てしまいます。
ISERROR関数等使用しても消すことができませんでした。
これは消すことはできないのでしょうか。教えていただいた式に関数を入れようとしたのですが、うまくいきませんでした。

もし、いい方法があれば教えていただきたいなと思います。
無理なら、後から＃VALUE！だけ消せばいいかなとも思っていますが、見た目が良くないなと少し気になってます。

おわかりになられましたらよろしくお願いします。

回答No.6

kagakusuki

>参照したセルに15をかけることにしました。
>しかし、コピーしたときに＃VALUE！が出てしまいます。

>C2のセルへコピーしたのですが、うまく15でかけた数で表示がされませんでした。

　もしかしますと、参照先であるSheet1のD列のデータが、実際には数値データではなく、改行等の何らかの「印刷出来ない文字」を含んでいる文字列データになってはいないでしょうか？
　試しに、Sheet1において、D列に数値（の様に見えるデータ）が入力されている行の中の、未使用の列の所にあるセルに、次の関数を入力してみて下さい。

=LEN(INDEX($D:$D,ROW()))

　その際、表示される数値が、「D列に入力されている数値の桁数」よりも多い数になってはいないでしょうか？
　もし、上記の関数によって表示される数値が、「D列に入力されている数値の桁数」よりも大きい場合には、D列に入力されているデータは数値データではなく、何らかの「印刷出来ない文字」を含んでいる文字列データという事になります。（上記の関数は、D列に入力されているデータが、本当に数字のみから成り立っているのかどうかを確認するためだけのものですから、確認し終えた後では、削除してしまって構いません）
　もし、D列に入力されているデータに「印刷出来ない文字」が含まれている場合には、本来であれば、元データであるD列に入力されているデータの中から、「印刷出来ない文字」を削除してしまった方が良いのですが、それが困難である場合には、Sheet2のC2セルに入力する関数を少し変更して、次の様な関数にされると良いと思います。

=IF(AND(ISNUMBER(1/(VLOOKUP($A1,Sheet1!$A:$C,3,FALSE)<>"")),$A2<>"",ROWS($2:2)>1),"",IF(IF(ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$C,3,FALSE)<>"")),$A2="",OR($A2<>"",ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE)<>"")))),"",IF(ISNUMBER(CLEAN(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE))+0),CLEAN(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE))*15,VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE))))

回答No.5

tom04

No.2・3です。

No.3の数式がちゃんと表示されない！というコトですので・・・
もしかして配列数式になっていないのではないでしょうか？
No.2の操作方法と一緒で　Shift＋Ctrl＋Enterで確定すればおそらくお望み通りの表示になるはずです。

しかし、No.1さんの補足に
＞実はシート1のD列は数字で、それに15をかけた数字をシート2のC列へ持ってきたいのです。
とありましたので、
もう一度数式を載せてそれに15を掛け算する方法もありますが、手っ取り早くVBAでやってみました。

↓の画像で左側がSheet1・右側がSheet2とします。
尚、Sheet1で空白セルがあるのはC列だけ！と前提です。

画面左下のSheet2のSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストし、Sheet2のA列にデータを入力してみてください。
（Sheet2のC列は15倍した数値にしています）

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim i As Long, c As Range, wS As Worksheet
Set wS = Worksheets("Sheet1")
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count <> 1 Then Exit Sub
If Target <> "" Then
With Target
Set c = wS.Range("A:A").Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
i = c.Row
If .Offset(, 1) <> "" Then
MsgBox "このセルは入力できません"
.Value = ""
.Offset(1).Select
Exit Sub
Else
.Offset(, 1) = wS.Cells(i, "B")
If WorksheetFunction.CountBlank(wS.Cells(i, "C").Resize(, 3)) = 0 Then
.Offset(1, 1) = wS.Cells(i, "C")
.Offset(1, 2) = wS.Cells(i, "D") * 15
.Offset(1, 3) = wS.Cells(i, "E")
Else
.Offset(, 2) = wS.Cells(i, "D") * 15
.Offset(, 3) = wS.Cells(i, "E")
End If
End If
Else
MsgBox "該当データなし"
.Value = ""
.Select
End If
End With
End If
End Sub 'この行まで

※　Sheet2のB～D列に数式が入っていると数式も消えてしまいますので、
新しいSheetで試してみてください。m(_ _)m

お礼

お礼が遅くなり申し訳ございません。有難うございました。

VBEすごいですね。修正したいとき、VBEがわかる人があまりいないので、関数の方がいいかなと個人的に思いました。

シート1枚だけを日付をつけてフォルダ指定し保存したいのでそこはVBEでしたいと思っています。
また質問すると思いますので、よろしくお願いいたします。
有難うございました。

回答No.4

kagakusuki

>もうひとつ教えていただきたいのですが、実はシート1のD列は数字で、それに15をかけた数字をシート2のC列へ持ってきたいのです。

　15を掛けなければならないのはSheet1のD列の値だけであり、その他の列の値に関しては、そのまま表示すると考えれば宜しいのでしょうか？
　それでしたらまず、Sheet2のB2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(VLOOKUP($A1,Sheet1!$A:$C,3,FALSE)<>"")),$A2<>"",ROWS($2:2)>1),"【行を空けて下さい】",IF(IF(ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$C,3,FALSE)<>"")),$A2="",ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,2+($A2=""),FALSE)<>""))),"",VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,2+($A2=""),FALSE)))

　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(VLOOKUP($A1,Sheet1!$A:$C,3,FALSE)<>"")),$A2<>"",ROWS($2:2)>1),"",IF(IF(ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$C,3,FALSE)<>"")),$A2="",OR($A2<>"",ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE)<>"")))),"",IF(ISNUMBER(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE)),VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE)*15,VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,4,FALSE))))

　次に、Sheet2のD2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/(VLOOKUP($A1,Sheet1!$A:$C,3,FALSE)<>"")),$A2<>"",ROWS($2:2)>1),"",IF(IF(ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$C,3,FALSE)<>"")),$A2="",OR($A2<>"",ISERROR(1/(VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,COLUMNS($B:D)+2,FALSE)<>"")))),"",VLOOKUP(IF($A2="",$A1,$A2),Sheet1!$A:$E,COLUMNS($B:D)+2,FALSE)))

　そして、Sheet2のB2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　以上です。

お礼

お礼が遅くなりました。

C2のセルへコピーしたのですが、うまく15でかけた数で表示がされませんでした。
なぜかは分かりません。質問に丁寧に答えていただき感謝しております。

最初の関数が求めていたものそのものでしたので、ベストアンサーにさせていただきました。
本当にありがとうございました。

回答No.3

tom04

No.2です！
たびたびごめんなさい。

前回の数式では↓Sheet1に空白がある場合
右側画像の上のような感じになります。

下側の画像のような表示をご希望だったのでしょうかね？

そうであればSheet2のB2セルに入れる数式は少し長くなりますが↓の数式に変更してください。
=IFERROR(IF(COUNTA(OFFSET(Sheet1!$B$1:$E$1,MATCH(IF($A2="",$A1,$A2),Sheet1!$A:$A,0)-1,,1))=4,IF($A2<>"",IF(COLUMN()=2,VLOOKUP($A2,Sheet1!$A:$B,2,0),""),VLOOKUP($A1,Sheet1!$A:$E,COLUMN(C1),0)),INDEX(OFFSET(Sheet1!$B$1:$E$1,MATCH($A2,Sheet1!$A:$A,0)-1,,1),,SMALL(IF(OFFSET(Sheet1!$B$1:$E$1,MATCH($A2,Sheet1!$A:$A,0)-1,,1)<>"",COLUMN($A$1:$D$1)),COLUMN(A1)))),"")

やはり配列数式となりますのでShift＋Ctrl＋Enterで確定してください。
今回も外していたらごめんなさいね。m(_ _)m

お礼

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

コピーした時にうまく参照されませんでした。
分かりにくい説明に丁寧にお答えいただき感謝しております。

回答No.2

tom04

こんばんは！
こういうコトでしょうか？

↓の画像で説明します。
左側がSheet1・右側がSheet2とします。
両Sheetとも、1行目は項目行として何らかの名目を入れておいてください。
データは2行目からあるという前提の数式です。
Excel2007以降のバージョンだという前提です。

Sheet2のB2セルに
=IFERROR(IF($A2<>"",IF(COLUMN()=2,VLOOKUP($A2,Sheet1!$A:$B,2,0),""),INDEX(OFFSET(Sheet1!$C$1:$E$1,MATCH($A1,Sheet1!$A:$A,0)-1,,1),,SMALL(IF(OFFSET(Sheet1!$C$1:$E$1,MATCH($A1,Sheet1!$A:$A,0)-1,,1)<>"",COLUMN($A$1:$C$1)),COLUMN(A1)))),"")
これは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面から数式をコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のB2セルを選択　→　数式バー内に貼り付け　
→　そのまま（編集可能なまま）Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列・行方向にオートフィルでコピーすると
画像のような感じになります。

仮にSheet1のC～E列に空白セルがある場合は左詰めで表示されます。

※　Excel2003までの場合は上記数式は利用できませんので、別の方法を考える必要があります。

外していたらごめんなさいね。m(_ _)m

お礼

お礼が遅くなりました。昨日作成してみました。
kagakushikiさんの関数とは違うのにちゃんと思った通りの位置に引っ張ってこれました。
皆さん頭が良くて感動です！

わたしの分かりにくい説明でここまで的確に回答いただけて感謝しております。
ありがとうございました。

補足

お伝え忘れていましたが、最初に回答いただいたものが求めていたものになります。
質問内容が分かりにくく申し訳ございません。
有難うございました。