エクセルで対象日に該当するデータがある場合に別表へ全対象者を表示させたい。

氏名と休み希望が入力された表(参照データ)がある場合、
そのデータを元に別表で指定日に該当する全ての対象者を抽出・表示させたいのですが、
関数またはVBAできますでしょうか？
うまく説明ができないので画像を添付いたします…

画像上部が参照データとして、
下部の①縦表または②横表を作成して全対象者を表示させたいのです。
※①②はデータ参照する上で作りやすい方で結構です。

分かりづらくて申し訳ないですが、
どなたかご教示宜しくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2023/07/12 10:51

こんにちは

①も②も考え方は同じなので、どちらがより簡単ということはなさそうです。

例えば①の例で、ご提示のレイアウトのままで良ければ、C14セルに
=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($B$3:$F$8)/($B$3:$F$8=C$13),ROW(A1))),"")
の式を入力して、表の範囲にフィルコピーすれば可能と思います。

スピル機能が使える環境（Office365等）であれば、C14セルに
=FILTER($A3:$A8,COUNTIF(OFFSET($B3:$F3,ROW(A1:A6)-1,),C13),"")
を入力して、右方にフィルコピーすれば、下方にはスピルされます。

この回答へのお礼

ご回答ありがとうございます！
ご教示いただいた関数を使用したところやりたいことができました！FILTER関数が私の環境では利用できない様子なので、こちら是非活用させていただきます。

お礼日時：2023/07/13 09:17

No.3 回答者： Qchan1962 回答日時： 2023/07/12 12:33

＃２誤記訂正

出力はSheet1　A3セル　＞＞出力はSheet2　A3セル
補
' rng.Select　は　条件による取得範囲の確認用の残骸ですので消してください

この回答へのお礼

訂正確認いたしました。
ありがとうございました！

お礼日時：2023/07/13 09:14

No.2 回答者： Qchan1962 回答日時： 2023/07/12 12:27

VBAの例ですが　処理工程を複数作る事になりそうです

①日付　を重複しない値として取得　合わせて該当する名前を取得
②日付を昇順に並び替え
③①の処理上名前を取り出し出力

サンプルフォーマット②の配置
取得はSheet1　A2セルから空白に囲まれた範囲
*名前列の途中に空白セルが無い事
出力はSheet1　A3セル
見出し行はサンプル処理に入れていませんので加えてください

関数処理よりVBAコードは複雑に見えると思いますので　関数で行う方が
良いと思います

Sub 出力フォーマット2()
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
Dim rng As Range
'Sheet1の操作
With Worksheets("Sheet1")
With .Range("A2").CurrentRegion
Set rng = .Offset(1, 1).Resize(.Rows.Count - 1, .Columns.Count - 1)
' rng.Select
End With
'範囲より重複しない日付を取得
'合わせて名前を取得
Dim cell As Range
For Each cell In rng
If cell.Value <> "" Then
If Not Dic.exists(cell.Value) Then
Dic.Add cell.Value, .Cells(cell.Row, 1).Value
Else
'名前を仕切り文字で繋げて取得
Dic(cell.Value) = Dic(cell.Value) & "|" & .Cells(cell.Row, 1).Value
End If
End If
Next
End With
Dim myKey, myItem
Dim i As Long
myKey = Dic.keys
myItem = Dic.items
'Sheet2の操作
With Worksheets("Sheet2")
'取得した値を出力
For i = 0 To UBound(myKey)
.Cells(i + 3, 1).Value = myKey(i)
.Cells(i + 3, 2).Value = myItem(i)
Next
Set rng = Nothing
Set Dic = Nothing
'出力範囲を日付で並び替え
Set rng = .Range("A3").CurrentRegion
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=rng.Columns(1) _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange rng
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
'繋げて取得した名前を切り離し（分けて）出力
Dim r, c, vName
For r = 1 To rng.Rows.Count
vName = Split(rng(r, 2), "|")
For c = 0 To UBound(vName)
rng(r, c + 2) = vName(c)
Next
Next

End Sub

この回答へのお礼

ご回答ありがとうございました！
いただいたVBAコードなかなか複雑に見え、初心者の私にはハードルが高そうでした＾＾；
ただ大変勉強になりましたので今後の参考にさせていただきます！

お礼日時：2023/07/13 09:13