[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『勤怠管理システムに取込CSVデータを作成したい』（事務員）
あまり汎用性の高くない要望のため、同様の事例が見つけられずこちらで相談させていただきます。
要望としては以下の通りです。
▼要望
【CSVデータA】を【CSVデータB】のレイアウトに変換したい
▼前提
・勤怠管理システムに取り込む勤務情報（CSVデータB）を作成したい
・CSVデータAは打刻データで、打刻の度に１行のデータができる
→毎日、出勤退勤で2行のデータができる
・１日の中で出勤・退勤を複数回することはない
▼CSVデータAの項目 （元データ）
A列：従業員コード
B列：打刻ステータス→1または2 （1＝出勤、2＝退勤）
C列：打刻時間(yyyy/mm/dd/hh/mm)
▼CSVデータBの項目 （取込用データ）
※従業員コードと打刻時刻のyyyy/mm/ddが同じものを１つの行にマージする
A列：従業員コード
B列：日付 （yyyy/mm/dd）
C列：固定値（出勤）
E列：出勤時間 （hhmm）
F列：固定値（退勤）
G列：退勤時間（hhmm）
＜ 使用 Excel：Excel2016、使用 OS：unknown ＞
勤怠管理システムはそのようになっているのではないのですか。
（?） 2023/10/04(水) 07:56:41
サンプルデータがこんなだったとして、 パワークエリで結構簡単にできるような気がしますが、 やってみるので後ほど
従業員コード 打刻ステータス 打刻時間 1002 1 2023/09/01/08/49 1003 1 2023/09/01/08/50 1004 1 2023/09/01/08/37 1002 2 2023/09/01/17/49 1003 2 2023/09/01/17/38 1004 2 2023/09/01/17/34 1002 1 2023/09/02/08/55 1003 1 2023/09/02/08/34 1004 1 2023/09/02/08/53 1002 2 2023/09/02/17/45 1003 2 2023/09/02/17/44 1004 2 2023/09/02/17/52 1002 1 2023/09/03/08/35 1003 1 2023/09/03/08/55 1004 1 2023/09/03/08/39 1002 2 2023/09/03/17/54 1003 2 2023/09/03/17/45 1004 2 2023/09/03/17/54 （´・ω・`） 2023/10/04(水) 09:02:49
簡単ではなかったです 結果の表がなんかちょっと違っちゃいました
let
ソース = Csv.Document(File.Contents("D:\test.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
置き換えられた値 = Table.ReplaceValue(昇格されたヘッダー数,"/","",Replacer.ReplaceText,{"打刻時間"}),
位置によって分割された列 = Table.SplitColumn(置き換えられた値, "打刻時間", Splitter.SplitTextByRepeatedLengths(8), {"打刻日", "打刻時間"}),
変更された型 = Table.TransformColumnTypes(位置によって分割された列,{{"従業員コード", Int64.Type}, {"打刻ステータス", Int64.Type}, {"打刻時間", type time}, {"打刻日", type date}}),
グループ化された行 = Table.Group(変更された型, {"従業員コード", "打刻日"}, {{"日別データ", each _, type table}}),
カスタム1 = Table.TransformColumns(グループ化された行,{"日別データ", each Table.AddColumn(_, "カスタム", each if [打刻ステータス] = 1 then "出勤" else if [打刻ステータス] = 2 then "退勤" else null)}),
カスタム2 = Table.TransformColumns(カスタム1,{"日別データ", each Table.SelectColumns(_,{"カスタム","打刻時間"})}),
カスタム3 = Table.TransformColumns(カスタム2,{"日別データ", each Table.Pivot(_, List.Distinct(_[カスタム]), "カスタム", "打刻時間") }),
展開された日別データ = Table.ExpandTableColumn(カスタム3, "日別データ", {"出勤", "退勤"}, {"出勤", "退勤"}),
変更された型1 = Table.TransformColumnTypes(展開された日別データ,{{"出勤", type time}, {"退勤", type time}})
in
変更された型1
（´・ω・`） 2023/10/04(水) 09:37:27
こっちで。
let
ソース = Csv.Document(File.Contents("D:\test.csv"),[Delimiter=",", Columns=3, Encoding=932, QuoteStyle=QuoteStyle.None]),
昇格されたヘッダー数 = Table.PromoteHeaders(ソース, [PromoteAllScalars=true]),
変更された型 = Table.TransformColumnTypes(昇格されたヘッダー数,{{"従業員コード", Int64.Type}, {"打刻ステータス", Int64.Type}, {"打刻時間", type text}}),
置き換えられた値 = Table.ReplaceValue(変更された型,"/","",Replacer.ReplaceText,{"打刻時間"}),
位置によって分割された列 = Table.SplitColumn(置き換えられた値, "打刻時間", Splitter.SplitTextByRepeatedLengths(8), {"打刻日", "打刻時間"}),
変更された型1 = Table.TransformColumnTypes(位置によって分割された列,{{"打刻日", type date}, {"打刻時間", type text}}),
追加された条件列 = Table.AddColumn(変更された型1, "カスタム", each if [打刻ステータス] = 1 then "出勤,"&[打刻時間] else if [打刻ステータス] = 2 then "退勤,"&[打刻時間] else null),
グループ化された行 = Table.Group(追加された条件列, {"従業員コード", "打刻日"}, {{"カウント", each Text.Combine([カスタム],","), type text}}),
区切り記号による列の分割 = Table.SplitColumn(グループ化された行, "カウント", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"カウント.1", "カウント.2", "カウント.3", "カウント.4"}),
変更された型2 = Table.TransformColumnTypes(区切り記号による列の分割,{{"カウント.1", type text}, {"カウント.2", type time}, {"カウント.3", type text}, {"カウント.4", type time}})
in
変更された型2
（´・ω・`） 2023/10/04(水) 09:59:40
先日CSVの質問で、VBAでちょっと作ったのでそれを流用。
Sub Sample()
Const csvPath As String = "C:\test\CSVfolder\" ’フォルダのバス
Const strMold As String = "#0YY#,出勤,#2-1HH#,退勤,#2-2HH#"
Dim buf, Temp, Rec, YY, HH
Dim dicT As Object, ky
Set dicT = CreateObject("Scripting.Dictionary")
Open csvPath & "CSVデータA.csv" For Input As #1
Open csvPath & "CSVデータB.csv" For Output As #2
Do Until EOF(1)
Line Input #1, buf
Temp = Split(buf & ",,", ",")
If Temp(0) <> "" Then
YY = Left(Temp(2), 10)
HH = Replace(Right(Temp(2), 5), "/", "")
ky = Temp(0) & YY
If Not dicT.exists(ky) Then '新規
dicT(ky) = Replace(strMold, "#0YY#", Temp(0) & "," & YY)
End If
Rec = dicT(ky)
If Temp(1) = "1" Then
Rec = Replace(Rec, "#2-1HH#", HH)
Else
Rec = Replace(Rec, "#2-2HH#", HH)
End If
dicT(ky) = Rec
End If
Loop
For Each buf In dicT.Items
Print #2, buf
Next
Close #1
Close #2
dicT.RemoveAll
End Sub
（半平太） 2023/10/04(水) 10:31:01
元データを従業員コード、打刻時刻、打刻ステータスで並べ替える。
これで、従業員のある日のレコードが出勤、退勤の２行できます。
（逆に言うと、２行あることを前提としています）
でもって、こちらの方法で別シートから参照する。
https://www.excel.studio-kazu.jp/kw/20050713183257.html
もちろん、日付や時間をちょっと加工したり、退勤については１行ずらして参照とか必要です。
（ゆたか） 2023/10/04(水) 10:41:39
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.