毎日、商品番号ごとの売上個数が記録されたCSVが生成されます。
-----20080601.csv------------------
商品番号1 2
商品番号2 1
商品番号3 1
商品番号4 1
商品番号5 2
商品番号6 8
-----------------------------------
個数部分のみが日ごとに変動しますが、そのほかの部分は固定です。
商品番号が増えることもありません。
これを1週間分まとめたcsvにしたいと思っています。
-----こうしたい.csv------------------
商品番号1 24
商品番号2 23
商品番号3 35
商品番号4 48
商品番号5 60
商品番号6 35
-----------------------------------
売上個数列が1週間の合計値になっています。
この作業をどうやって行うのが効率的でしょうか?
現在は、1つのcsvに他6つのcsvの売上個数列をコピーし、SUM関数で合計値を計算しています。
1週間分ならまだ何とかなるのですが、1か月分、1年分が必要になることもあり、手動で行うことに限界を感じています。
よろしくお願いいたします。
こんな感じでどうでしょうか。
CSVファイルの入っているフォルダのパスを指定してください。
コード中の商品番号1~6を実際のものに変更してください。
実行させると同じフォルダに日曜から土曜までの1週間分ずつ合成したCSVファイルを作ります。
Sub MacroCSV() 'CSVファイルの入っているフォルダのパスに変えてください Const myPath As String = "C:\Documents and Settings\hogehoge\デスクトップ\test" Dim FSO Dim myFile As File Dim strDay As String Dim myDay As Date Dim startDay As Date Dim endDay As Date Dim strDay2 As String Dim w As Worksheet Dim f As Boolean Dim i As Long Dim csvLine As Variant Set FSO = CreateObject("Scripting.FileSystemObject") For Each myFile In FSO.GetFolder(myPath).Files If Len(myFile.Name) >= 12 Then strDay = Left(myFile.Name, 4) & "/" & Mid(myFile.Name, 5, 2) & "/" & Mid(myFile.Name, 7, 2) If IsDate(strDay) Then myDay = DateValue(strDay) startDay = myDay - Weekday(myDay) + 1 endDay = startDay + 6 strDay2 = Format(startDay, "yyyymmdd") & "-" & Format(endDay, "yyyymmdd") f = False For Each w In Worksheets If w.Name = strDay2 Then f = True Exit For End If Next If f = False Then Worksheets.Add ActiveSheet.Name = strDay2 Set w = ActiveSheet w.Range("A1").Value = "商品番号" w.Range("B1").Value = "売上個数" w.Range("A2").Value = "商品番号1" w.Range("A3").Value = "商品番号2" w.Range("A4").Value = "商品番号3" w.Range("A5").Value = "商品番号4" w.Range("A6").Value = "商品番号5" w.Range("A7").Value = "商品番号6" End If With FSO.OpenTextFile(myFile.Path) .ReadLine For i = 2 To 7 csvLine = Split(.ReadLine, ",") w.Cells(i, 2).Value = w.Cells(i, 2).Value + csvLine(1) Next i End With End If End If Next For i = 1 To Worksheets.Count - 1 Worksheets(i).Activate ActiveWorkbook.SaveAs Filename:=myPath & "\" & ActiveSheet.Name & ".csv", _ FileFormat:=xlCSV, CreateBackup:=False Next i Set FSO = Nothing End Sub
実行する前にシートを二つ作ってください。
そのシートの名前を
実行
データ
にしてください。
次に 実行のシートのA列に ファイル名を フルパスで指定してください。
たとえば
c:\20080601.csv
というように
あと、ソース中に 出力するファイル名を指定しています。
Open "C:\こうしたい.csv" For Output As #f
この箇所ですが、これも好きに変更してください。
なお、計算結果が データのシートに作られます。
それを元にファイルに出力しています。
何か問題があれば データのシートを見直してみてください。
Sub Macro1() Worksheets("データ").Range("A:IV").Delete Shift:=xlUp d = 1 For a = 1 To 65536 If Worksheets("実行").Cells(a, "A") = "" Then Exit For b = Worksheets("実行").Cells(a, "A") If Dir(b) = "" Then Worksheets("実行").Cells(a, "B") = "このファイルは 見つかりません" g = 1 Else Worksheets("実行").Cells(a, "B") = "" g = 0 End If If g = 0 Then f = FreeFile Open b For Input As #f Worksheets("データ").Cells(d, "A") = b Do While Not EOF(f) Line Input #f, c If InStr(c, " ") > 0 Then Worksheets("データ").Cells(d, "B") = Left(c, InStr(c, " ") - 1) Worksheets("データ").Cells(d, "C") = Right(c, Len(c) - InStr(c, " ")) Else If InStr(c, ",") > 0 Then Worksheets("データ").Cells(d, "B") = Left(c, InStr(c, ",") - 1) Worksheets("データ").Cells(d, "C") = Right(c, Len(c) - InStr(c, ",")) Else Worksheets("データ").Cells(d, "B") = c End If End If d = d + 1 Loop Close #f End If Next a g = 0 f = FreeFile Open "C:\こうしたい.csv" For Output As #f For a = 1 To 65536 If Worksheets("データ").Cells(a, "A") <> "" And g = 1 Then Exit For Worksheets("データ").Cells(a, "D").Formula = "=SUMIF(B:C,B" & a & ",C:C)" Print #f, Worksheets("データ").Cells(a, "B") & "," & Worksheets("データ").Cells(a, "D") g = 1 Next a Close #f MsgBox "終わりました" End Sub
ご回答ありがとうございます。マクロを触るのがはじめての為、苦戦しておりました。問題なく動作しました。ありがとうございます。
ただ、質問の際には「単純化したパターンで回答をいただき、実際のパターンへのカスタマイズは自分でしよう」と思っていたのですが、マクロが全く読めず苦戦しています。実際には売上個数の入力される列が2列目ではなく4列目なのです。合計する列を2列目ではなく4列目にするにはどのようにすればいいのでしょうか?ご教授いただけると助かります。
日付が重要なキーになっていると思います。
この重要なキーがレコードに入っていないのが問題と思います。
日付を入れてしまえば、後はピボットテーブルなどで簡単に1週間毎(=日で7日を選択)、1ヶ月毎、1年毎の集計ができます。
ファイルがYYYYMMDD.csvという命名規則になっている場合に、各レコードの先頭に日付をつけて全件を1つのファイルにまとめるバッチファイルを作りました。
これで一度今までのファイルを1つにまとめてしまってはどうでしょうか?(サンプルではAllcsvDatas.txt)
その後は手動でExcelへ貼り付けるか、類似スクリプトで毎日後ろへ追加していけば大丈夫です。
下記をxxx.batで保存して動かしてみてください。
Rem YYYYMMDD.csvファイルの各レコードの先頭へYYYYMMDDを追加して一つにまとめる set ans=AllcsvDatas rem del %ans%.csv for %%f in (????????.csv) do Call :TypeWithDate %%f %ans%.txt rem ren %ans%.txt %ans%.csv Goto :EOF :TypeWithDate set FNam=%1 set FileDate=%FNam:~0,4%/%FNam:~4,2%/%FNam:~6,2% for /F "tokens=1-3" %%I in (%1) do echo %FileDate% %%I %%J >> %2
P.S.もしかしてcsvはタブ区切りですか?
途中で新製品が出たり、名称変更することはないでしょうか? → 可能性があるならば、やはり通常のレコードとして1レコードずつ扱ったほうが良さそうです。
ありがとうございます。カンマ区切りですので、コメントでいただいた方法で無事動作いたしました。
ただ、質問の際には単純化してお伝えしたのですが(すみません)、実際は集計には必要のない数値が商品番号と売上個数の間に入っていまして、その数値が0の場合、0ではなく空白が出力されています。
商品番号1,1,2,2
商品番号2,1,,1
商品番号3,1,2,1
商品番号4,,2,1
商品番号5,1,,2
商品番号6,,2,8
-----------------------------------
教えていただいた方法でできたtxtの拡張子をcsvにしてみたのですが、空白部分がつめられてしまうため、売上個数が正しく集計できなくなってしまいました。対応策がございましたらご教授ください。
> 途中で新製品が出たり、名称変更することはないでしょうか?
はい、ありません。想定して多めに商品番号を用意している、という状況です。
修正したものを回答します。
Sub MacroCSV() 'CSVファイルの入っているフォルダのパスに変えてください Const myPath As String = "C:\Documents and Settings\hogehoge\デスクトップ\test" Dim FSO Dim myFile Dim strDay As String Dim myDay As Date Dim startDay As Date Dim endDay As Date Dim strDay2 As String Dim w As Worksheet Dim f As Boolean Dim i As Long Dim csvLine As Variant Dim wCount As Integer wCount = Worksheets.Count Worksheets(1).Activate Set FSO = CreateObject("Scripting.FileSystemObject") For Each myFile In FSO.GetFolder(myPath).Files If Len(myFile.Name) >= 12 Then strDay = Left(myFile.Name, 4) & "/" & Mid(myFile.Name, 5, 2) & "/" & Mid(myFile.Name, 7, 2) If IsDate(strDay) Then myDay = DateValue(strDay) startDay = myDay - Weekday(myDay) + 1 endDay = startDay + 6 strDay2 = Format(startDay, "yyyymmdd") & "-" & Format(endDay, "yyyymmdd") f = False For Each w In Worksheets If w.Name = strDay2 Then f = True Exit For End If Next If f = False Then Worksheets.Add ActiveSheet.Name = strDay2 Set w = ActiveSheet w.Range("A1").Value = "商品番号" w.Range("B1").Value = "売上個数" End If With FSO.OpenTextFile(myFile.Path) .ReadLine i = 2 Do Until .AtEndOfStream csvLine = Split(.ReadLine, ",") If f = False Then w.Cells(i, 1).Value = csvLine(0) End If w.Cells(i, 2).Value = w.Cells(i, 2).Value + csvLine(3) i = i + 1 Loop End With End If End If Next For i = 1 To Worksheets.Count - wCount Worksheets(i).Activate ActiveWorkbook.SaveAs Filename:=myPath & "\" & ActiveSheet.Name & ".csv", _ FileFormat:=xlCSV, CreateBackup:=False Next i Set FSO = Nothing End Sub
ご回答ありがとうございます。上記はマクロと呼ばれるものですよね?マクロを触るのがはじめての為、「マクロが使用できません。」のエラー画面につまずくなどしており回答が遅れました。申し訳ないです。
さて、ようやくマクロを動かすに至りました。コメントいただいた修正を反映すると、無事に望みどおりの結果が出力できました。ありがとうございます。
ただ、質問の際には単純化してお伝えしたのですが、実際には商品番号が100以上あったり、売上個数の入力される列が2列目ではなく4列目だったりします。この2点だけカスタマイズしたいのですが、VBA辞典なるものを読んでみたもののイマイチよく分かりません。ご教授いただけると助かります。