エクセルで条件に一致した複数のシートセルの隣のセルを別のシートに取得したい

Question

下のような「Aさん」「Bさん」～「Gさん」という多数の名前のシートがあります。

　
「Aさん」のシートには（「日付」のセルがA1です、[ 3/1 ]のセルがA2です。）

　[ 日付 ][ 状態 ][ 備考 ]
　[ 3/1 ][ 良好 ][ 終始機嫌よい ]
　[ 3/10 ][ やや不良 ][ 眠れなかった ]
　[ 3/20 ][ 最悪 ][ 朝から機嫌悪い ]

「Bさん」のシートには

　[ 日付 ][ 状態 ][ 備考 ]
　[ 3/1 ][ 不良 ][ 昨晩転倒 ]
　[ 3/10 ][ 良好 ][ 一昨晩の転倒忘れてる？？ ]
　[ 3/20 ][ 普通 ][ 変わりなし ]
・・・

別のシートのA1セルに、「3/1」と入力すると、

　[ 3/1 ]

全てのシートから「3/1」のを見つけて、（「 Aさん」のセルがA2です、[ Bさん ]のセルがA3です。）B列C列に

列2　[ Aさん ][ 良好 ][ 終始機嫌よい ]
列3 ［Bさん」[ 不良 ][ 昨晩転倒 ]
・
・
・
列20［Gさん］
のように表示させたいのですが、B列、C列にはどのような式を書けば良いのでしょうか。
【A列は固定】



よろしくおねがい致します。

fujillin · Accepted Answer

こんにちは

抽出したいシートのＡ列の名前と、各人のシート名とが一致していると仮定してもよいものとします。
（Ａ列に「Ａさん」とある場合は、シート名「Ａさん」のシートを参照すればよいという意味）

抽出したいシートの、Ｂ2セルに
　=IFERROR(VLOOKUP($A$1,INDIRECT($A2&"!A:C"),COLUMN(B1),0),"")

の式を入れて、右方向、下方向にフィルコピーではいかがでしょうか？

※ 日付はどのセルでも同じ形式で入力されているものと仮定しています。
　（シリアル値と文字列が混在していたりしないという意味です）
※ A列の氏名（＝シート名）は事前に入力されているものと仮定しています。

tom04 · Answer

No.2・3です。

＞VBAで標準モジュールにて、上記文ペーストさせていただきました。

ちゃんとVBE画面にコピー＆ペーストされているようなので、
余計なお世話かもしれませんが・・・

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
この画面上から前回のコードをコピー＆ペースト！

これでマクロができましたので、
Excel画面に戻り（VBE画面を閉じて）マクロを実行します。

＞「問」シートか何らかのシートのセル？に何らかの入力が必要ですか？？
「問」シートのA1セルに日付のシリアル値を入力後
Alt＋F8キー　→　マクロダイアログボックスが表示されますので、そのまま「実行」

これでマクロが実行されます。（複数のマクロがある場合は実行したいマクロを選択し実行します）

これで何とかお望みの操作ができると思います。

※　「問」シートのA1セル入力　→　Enter　で実行することも可能ですが、
とりあえずは今回は手動での実行としています。m(_ _)m

tom04 · Answer

No.2です。

＞仮に「別シート」が「問」だったら・・・

とりあえず「問」シートがシート見出しのどこにあっても対応できるようにしてみました。
前回のコードは消去し↓のコードに変更してみてください。

Sub Sample2()
　Dim k As Long, lastRow As Long
　Dim c As Range, wS As Worksheet
  
　　With Worksheets("問")   '//★//
　　　lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　If lastRow > 1 Then
　　　　　Range(.Cells(2, "A"), .Cells(lastRow, "C")).ClearContents
　　　　End If
    
　　　　For k = 1 To Worksheets.Count   '//シート見出しの一番左から最後のシートまで★//
　　　　　Set wS = Worksheets(k)
　　　　　　If wS.Name <> .Name Then
　　　　　　　Set c = wS.Range("A:A").Find(what:=DateValue(.Range("A1")), LookIn:=xlFormulas, lookat:=xlWhole)
　　　　　　　　If Not c Is Nothing Then
　　　　　　　　　With .Cells(Rows.Count, "A").End(xlUp).Offset(1)
　　　　　　　　　　.Value = wS.Name
　　　　　　　　　　.Offset(, 1).Resize(, 2).Value = c.Offset(, 1).Resize(, 2).Value
　　　　　　　　　End With
　　　　　　　　End If
　　　　　　End If
　　　　Next k
　　End With
　　　MsgBox "完了"
End Sub

※　「★」の行を変更しています。

こんな感じではどうでしょうか？m(_ _)m

tom04 · Answer

こんばんは！

VBAになりますが、一例です。
「別シート」のシート名が不明なので、「別シート」とはシート見出しの一番左側に配置してあり
検索シートはシート見出しの2番目以降にあるとします。

尚、「別シート」のA1セルはシリアル値を入力、各シートのA列もシリアル値になっているという前提です。

標準モジュールにしてください。

Sub Sample1()
　Dim k As Long, lastRow As Long
　Dim c As Range, wS As Worksheet
　　With Worksheets(1)    '//←シート見出しの一番左側（別シート）//
　　　'//▼データを一旦消去//
　　　lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　If lastRow > 1 Then
　　　　　Range(.Cells(2, "A"), .Cells(lastRow, "C")).ClearContents
　　　　End If
    
　　　　For k = 2 To Worksheets.Count
　　　　　Set wS = Worksheets(k)
　　　　　Set c = wS.Range("A:A").Find(what:=DateValue(.Range("A1")), LookIn:=xlFormulas, lookat:=xlWhole)
　　　　　　If Not c Is Nothing Then
　　　　　　　With .Cells(Rows.Count, "A").End(xlUp).Offset(1)
　　　　　　　　.Value = wS.Name
　　　　　　　　.Offset(, 1).Resize(, 2).Value = c.Offset(, 1).Resize(, 2).Value
　　　　　　　End With
　　　　　　End If
　　　　Next k
　　End With
　　　MsgBox "完了"
End Sub

※　各シートのA列日付に重複はない！という前提です。

＞【A列は固定】　だというコトですが、
「別シート」のA列は各シートのシート名を表示しています。
（各シートにA1セルの日付がない場合は何も表示されません）

※　VBAの場合は、シート名や各シートのレイアウトが重要になります。
シート名が異なる！とかレイアウトが1行・1列でも違えば
まったく意図しない動きになります。

まずはこの程度で・・・m(_ _)mえば

satoumasaru · Answer

プログラムであるVBAで組まないと無理です。
VBAは後々のメンテが大変ですのでお勧めできません。

なお、個人をシート単位で整理するというのはエクセルではむいていません。
１件のデータを1列で処理するようにしないとだめでしょう。

エクセルで条件に一致した複数のシートセルの隣のセルを別のシートに取得したい

こんにちは

No.2・3です。

No.2です。

こんばんは！

プログラムであるVBAで組まないと無理です。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング