[[20220927134419]]　『シフト表から今日出勤している人と出勤時間を詰め』（おかもと）　ページの最後に飛ぶ

『シフト表から今日出勤している人と出勤時間を詰めて表示させたい』（おかもと）

よろしくお願いいたします。

シフト表と出勤簿があります。
指定した日付に出勤となっている人だけを出勤簿に表示させと思っているのですがなかなかうまくいかずお知恵を拝借したいと思います。

■シート「シフト」

	Aさん	Bさん	Cさん
9月1日	9:00-18:00	有給	13:00-19:00
9月2日	9:00-18:00	有給	
9月3日		有給	13:00-19:00

■シート「出勤簿」
日付 9月1日
出勤 Aさん Cさん
時間 9:00-18:00 13:00-19:00

※有給などでお休みの人は出勤簿から排除したい
※出退勤時間はすべて00:00-00:00形式で入力されている

以上、何卒よろしくお願いいたします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

 行列番号がわかるように表を提示してください。
 元表が何行×何列の表なのかも明記してください。

 とりあえず･･･
 ・シフト表の名前は B1:J1、日付は A2:A50
 ・セルにコロン「:」が入力されていたら「出勤」とみなす

 ■シフト			
	A	B	C	D
1		Aさん	Bさん	Cさん
2	9/1			
3	9/2			
4	9/3			
・

 ■出勤簿			
	A	B	C	D
1		9/1		
2	出勤			
3	時間

 ■出勤簿
 B2 =IF($B$1="","",IFERROR(INDEX(シフト!$1:$1,AGGREGATE(15,6,COLUMN($B$1:$J$1)/ISNUMBER(FIND(":",INDEX(シフト!$B$2:$J$50,MATCH($B$1,シフト!$A$2:$A$50,0),0))),COLUMN(A1))),""))

 B3 =IF(B2="","",VLOOKUP($B$1,シフト!$A$2:$J$50,MATCH(B2,シフト!$A$1:$J$1,0),FALSE))

 右コピー
 ※B2の式、最後の方の COLUMN(A1)の「A1」は、実際のレイアウトがどうなっていようと変更しないように

 以上
（笑） 2022/09/27(火) 14:41

表の位置は素直に解釈しました。

シート「出勤簿」のB2:　=IFERROR(INDEX(シフト!$B$1:$D$1,AGGREGATE(15,6,COLUMN($A$1:$C$1)/(LEN(INDEX(シフト!$B$2:$D$4,MATCH($B$1,シフト!$A$2:$A$4,0),))>=3),COLUMN(A1))),"")

シート「出勤簿」のC2:　=IFERROR(INDEX(シフト!$B$2:$D$4,MATCH($B$1,シフト!$A$2:$A$4,0),MATCH(B2,シフト!$B$1:$D$1,0)),"")

両式を右にコピーします。
（メジロ） 2022/09/27(火) 14:53

 Sub Sample()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim var1() As Variant
    Dim var2() As Variant
    Dim lastColumn As Long
    Dim iRow As Variant
    Dim iColumn As Long
    Dim i As Long

    'シート定義
    Set ws1 = Worksheets("シフト")
    Set ws2 = Worksheets("出勤簿")
    'ws1の1行目の最終列を取得
    lastColumn = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
    'データを入れるための配列を定義（最大データ収納数で定義しあとで減らす）
    ReDim var1(lastColumn - 2)  '名前用
    ReDim var2(lastColumn - 2)  '時間用
    'ws2の出力範囲を空白にする
    ws2.Range("B2:B3").ClearContents
    'ws2の日付と同じものをws1のA列2行目以降で探す
    iRow = Application.Match(ws2.Range("B1").Value, ws1.Range("A2", ws1.Cells(Rows.Count, 1).End(xlUp)), 0)
    '同じ日付があった場合（無い場合iRowはエラーになる）
    If IsNumeric(iRow) Then
        '2列目から最終列まで巡回
        For iColumn = 2 To lastColumn
            'セルの値が「*#:##-*#:##」形式になっていたら
            If ws1.Cells(iRow + 1, iColumn).Value Like "*#:##-*#:##" Then
                '1行目の値を名前用配列に入れる
                var1(i) = ws1.Cells(1, iColumn).Value
                'iRow+1行目の値を時間用配列に入れる
                var2(i) = ws1.Cells(iRow + 1, iColumn).Value
                '配列インデックスを加算
                i = i + 1
            End If
        Next
        '配列インデックスが加算されていたら（配列にデータが入っていたら）
        If i > 0 Then
            '配列インデックス最大値をデータ数に合わせる
            ReDim Preserve var1(i - 1)          '名前用
            ReDim Preserve var2(i - 1)          '時間用
            '出力範囲にデータを入れる
            ws2.Range("B2").Value = Join(var1)  '名前
            ws2.Range("B3").Value = Join(var2)  '時間
        End If
    End If
End Sub

練習課題のつもりでマクロを書いてみました。
おかもとさんの考えていたものと違っていたらすみません。
（下手の横好き） 2022/09/27(火) 15:46

みなさま
お礼が遅くなり申し訳ございません。

笑さま、メジロさま
おかげ様で希望通りの処理ができました。AGGREGATE関数を知れたのも新しい発見でした。ありがとうございます。

下手の横好き様
提示してくださった方法により、こういった解決法もあるのだと自分の考え方が偏っていたことを知ることができましたありがとうございます。

みなさま本当にありがとうございました。
次回から行列番号と元表の行列を明記するようにいたします。
（おかもと） 2022/09/29(木) 10:15

コメント返信：

[ 一覧(最新更新順) ]

[[20220927134419]] 『シフト表から今日出勤している人と出勤時間を詰め』（おかもと） ページの最後に飛ぶ

[[20220927134419]]　『シフト表から今日出勤している人と出勤時間を詰め』（おかもと）　ページの最後に飛ぶ