Excelで関数かマクロを教えてください

質問

シフト表のようなものをつくりたく、
縦に名前、横に日付、日付の下に出勤や休みという感じの見た目にしたいです。
　　　6/1　　6/2　　6/3
山田　出勤　休み　出勤
鈴木　休み　出勤　休み
佐藤　出勤　出勤　欠勤

のような感じです。
元データがあり、
山田　6/1　出勤
山田　6/2　休み
山田　6/3　出勤
鈴木　6/1　休み
鈴木　6/2　出勤
鈴木　6/3　休み
佐藤　6/1　出勤
佐藤　6/2　出勤
佐藤　6/3　欠勤

のように並んでいるCSVファイルがあります。

けっこうな人数がいて、何か月分も作成するのでなにかいい方法はありませんでしょうか。

回答No.8

imogasi

#4です。まだ開いていたので、VBAによってやって見ました。ご参考に。
テスト・データ
「メモ帳」で作製。
山田,6/1,出勤
山田,6/2,休み
山田,6/3,出勤
鈴木,6/1,休み
鈴木,6/2,出勤
鈴木,6/3,休み
佐藤,6/1,出勤
佐藤,6/2,出勤
佐藤,6/3,欠勤
ファイルの名前「c:\temp\シフト１csv.txt」で保存。
ーー
エクセルに移って、
エクセルのシートに「Sheet1」を作り（既にあれば利用する）
第１行目のB列から右列に
2023/6/1 2023/6/2 2023/6/3 2023/6/4 2023/6/5・・・
を作成しておく（文字列でなく、日付シリアル値で）
２０２３年と仮定。
日付けに関しては、インプットデータの6/1などを、日付シリアル値に直して（年も追加し）、シート上の記入する該当列＝該当日を探している。
ーーー
標準モジュールに、下記を作製。
日付けの列位置探索は、VBAのFindを使う方法を使いました。
Sub test01()
i = 2　'Sheet1の書き出し開始行
r = 1 'レコード番号
Set sh1 = Worksheets("Sheet1")
'--
Open "c:\temp\シフト１csv.txt" For Input As #1
While Not EOF(1)
'-------
Line Input #1, t　’読みこんだとき、文字列状態を保つためLine Inputを利用
MsgBox r & "レコード=" & t ’確認用
k = Split(t, ",")　′カンマ区切りを分解　 k(0)氏名、k(1)日付、k(2)は勤務データが入る
'--氏名
sh1.Cells(i, "A") = k(0)
'--日付け
h = Split(k(1), "/")
MsgBox k(1) & "=" & h(0) & "/" & h(1)
dt = DateSerial(2023, Val(h(0)), Val(h(1)))
Set h = Range("a1:AF1").Find(dt)
hzc = h.Column
MsgBox k(1) & "=" & hzc & "列"
'--勤務
sh1.Cells(i, hzc) = k(2)
'---
If r Mod 3 = 0 Then
i = i + 1 '次は次行にセット
End If
'---
r = r + 1 '次レコード
Wend
Close #1
End Sub
本件テスト終了後・納得後は、Msgbox wo削除すべきです。
ーーー
実行結果　Sheet1のA2以下とA2以下のセルに、
2023/6/1 2023/6/2 2023/6/3
山田 出勤 休み 出勤
鈴木 休み 出勤 休み
佐藤 出勤 出勤 欠勤

回答No.7

HohoPapa

ごめんなさい、
>この膜をを配置して実行すれば、
↑誤字
>このマクロを配置して実行すれば、
↑訂正後

回答No.6

HohoPapa

時間があり、興味を惹かれたので
SQL文を使うコードを書いてみました。
よかったら試してみてください。

使い方は以下です。

ブックにSheet1,2,3のシートを用意し
Sheet1にCSVの中身を配置します。

Sample画面の1行目の列名を変更する場合は
SQL文を修正してください。
この膜をを配置して実行すれば、
Sheet3に結果が書き込まれます。

もし、
不明な点、期待と異なる点、異常、使いにくさなどがあれば
コメントしてください。
対応できるかもしれません。

Option Explicit

Sub Sample()

　Dim SQL As String
　Dim cn As Object
　Dim rs As Object
　Dim c As Long
　Dim shWK As Worksheet
　Dim shPT As Worksheet
　Dim GetRow As Long
　Dim PutRow As Long

　Set shWK = ThisWorkbook.Sheets("Sheet2")
　Set shPT = ThisWorkbook.Sheets("Sheet3")

　SQL = ""
　SQL = SQL & "transform [勤務]" & vbCrLf
　SQL = SQL & "select [氏名]" & vbCrLf
　SQL = SQL & "From [Sheet1$A1:C2000]" & vbCrLf
　SQL = SQL & "group by [氏名] ,[勤務]" & vbCrLf
　SQL = SQL & "order by [氏名]" & vbCrLf
　SQL = SQL & "pivot [日付]" & vbCrLf

　'SQL 実行
　Set cn = CreateObject("ADODB.Connection")
　Set rs = CreateObject("ADODB.Recordset")
　cn.Provider = "Microsoft.ACE.OLEDB.12.0"
　cn.Properties("Extended Properties") = "Excel 12.0;HDR=Yes;IMEX=1"
　cn.Open ThisWorkbook.FullName
　rs.Open SQL, cn

　'ヒットする行が無かったら抜ける
　If rs.EOF = True Then
　　MsgBox "データがありません"
　　Exit Sub
　End If

　With shWK
　　.Cells.ClearContents
　　For c = 1 To rs.Fields.Count
　　　.Cells(1, c).Value = rs.Fields.Item(c - 1).Name
　　Next c
　　.Cells(2, 1).CopyFromRecordset rs
　End With

　'SQL後処理
　rs.Close
　Set rs = Nothing
　cn.Close
　Set cn = Nothing

　GetRow = 1
　PutRow = 1
　shPT.Cells.ClearContents
　Application.ScreenUpdating = False

　Do
　　If shWK.Cells(GetRow, 1).Value = "" Then Exit Do
　　If ((shWK.Cells(GetRow, 1).Value <> shPT.Cells(PutRow, 1).Value) And _
　　　(shPT.Cells(PutRow, 1).Value <> "")) Then
　　　PutRow = PutRow + 1
　　End If
　　shWK.Select
　　shWK.Rows(GetRow).Select
　　Selection.Copy
　　shPT.Select
　　shPT.Rows(PutRow).Select
　　Selection.PasteSpecial Paste:=xlPasteAll, _
　　　　Operation:=xlNone, SkipBlanks:=True, _
　　　　Transpose:=False
　　GetRow = GetRow + 1
　Loop

　Application.ScreenUpdating = True

End Sub

回答No.5

135ok

以前、同じような質問に回答したことがあります。
何通りか回答がありますので、一度試してみてはいかがでしょうか。

https://okwave.jp/qa/q5413006.html

回答No.4

imogasi

こういうタイプの問題は、「今日のデータ組換え」に当たります。
関数でやると、式が難しい場合が多く、シートの全セルが関数含みになります。
ーー
ですからVBAがお勧めです。
本件は処理内容は簡単なのですが、付帯したことで、難しさにぶっつかるかもしれません。当方はテストでやって見て、そうでした。
・CSVファイルの問題
CSVファイルを作り拡張子　.csvで保存しても、最近はエクセルのシートデータになってしまう。
・日付の問題
システム部署で作ったデータなどとすると、日付はエクセルの「日付シリアル値」のデータとは限りません。ＣＳＶデータでは書式データは伝わりません。
この点質問文の表現ぐらいでは、十分判りません。
質問者も十分認識しておく必要があります。
・３レコードで１人１日分データであること
余剰なデータであること。
連続３レコードを１度に読んで処理するのが良いかも。

結果的には、例えば
第１行目の２列目以右列に月中日を予め入れておいて、
第2行目以下のＡ列に社員の名前と、該当日付列に、勤務形態文字を入れれば仕舞いです。
個人ごと第２データを読んだ時、第１行目の日付列をどう見つけるか、
Ｆｉｎｄなどで見つけるとき、相互の連携（セータ性格）を良く考えて（一致して考えて）おく必要があります。文字列か日付シリアル値かなど。月日データだけか？表示形式で月日だけでそう見えているのか、など。
シートデータでは、文字列は日付シリアル値に統一すべきかもしれません。後々のデータ利用のことを考えて。

回答No.3

SI299792

画像の様な表
Sheet1：結果
Sheet2：元データ
とします。

A1: =COUNTIF(Sheet2!A:A,Sheet2!A1)
B1: =IF(COLUMN()>$A$1+1,"",INDEX(Sheet2!$B:$B,COLUMN()-1)
右へコピペ。
A2: =INDEX(Sheet2!A:A,$A$1*(ROW()-2)+1)
下へコピペ。
B2: =IF(B$1="","",INDEX(Sheet2!$C:$C,$A$1*(ROW()-2)+COLUMN()-1))
右下へコピペ。

全ての人が同じ件数（同じ日）である前提です。
A1は作業域です。目障りならフォントを白にして見えなくします。
表示形式、ユーザー定義｢#｣ にすれば 0は表示されません。

この方法は、いちいちデータをExcel に読まなければなりません。
VBA でデータの読み込みから一気にやった方がいいかもしれません。
その場合
・文字コード（メモ帳で開けば、下の方に出て来ます）
・ファイルをどうやって選択するか（固定か、ダイアログボックスで選ぶか）
・元データがこの形でいいか（違っているならどのような形か）
の情報が必要です。

回答No.2

notnot

> なにかいい方法はありませんでしょうか。

シフト表のニーズは沢山あるようで、検索すると出来合の物が沢山ヒットするので、適当な物を選んで使いましょう。

回答No.1

kon555

マクロでやるならcsvを読み込んで処理して・・・という形で可能は可能です。
　ただシフト表を組むマクロとなるとそれなりに複雑になりますので、データ構造等も曖昧なこのサイト上のやり取りで組むのはかなり困難ですね。貴方自身が勉強し、自分で作成する必要があります。
(参考)
https://excel-ubara.com/excelvba5/EXCEL111.html
https://akira55.com/shifts_chedule/
https://www.iehohs.com/excel-auto-shift/

　関数でやるならCOUNTIFS関数によるクロス集計でしょうか。
　元々のデータから「休み」をフィルタするなどして消すと、出勤日のみのデータになります。
「山田　6/1　出勤，山田　6/2　休み，山田　6/3　出勤」→
「山田　6/1　出勤，山田　6/3　出勤」
　この状態でCOUNTIFSで『山田』かつ『存在する日付』を抽出すれば、それが出勤シフトになります。
　もちろん『山田』かつ『存在する日付』かつ『出勤』みたいにしてもいいです。前処理に手間をかけるか、関数を複雑にするかの違いです。
　COUNTIFSでのクロス集計については以下のサイトが参考になります。
https://forest.watch.impress.co.jp/docs/serial/exceltips/1471051.html

　クロス集計自体はピボットテーブルも得意とする集計なので、少し工夫すれば対応可能だと思いますが、個人的にはCOUNTIFSの方が分かりやすいと思います。
　試してみて、自分でやれそうな方を試してみるといいでしょう。
https://allabout.co.jp/gm/gc/297727/
https://jp.creativesurvey.com/blog/posts/aggregate_20220426/