人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

エクセルについての質問です。

毎日、商品番号ごとの売上個数が記録された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年分が必要になることもあり、手動で行うことに限界を感じています。
よろしくお願いいたします。

1213164182
●拡大する


●質問者: td555
●カテゴリ:ビジネス・経営 コンピュータ
✍キーワード:23 24 CSV エクセル コピー
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● SALINGER
●23ポイント

こんな感じでどうでしょうか。

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
◎質問者からの返答

ご回答ありがとうございます。上記はマクロと呼ばれるものですよね?マクロを触るのがはじめての為、「マクロが使用できません。」のエラー画面につまずくなどしており回答が遅れました。申し訳ないです。

さて、ようやくマクロを動かすに至りました。コメントいただいた修正を反映すると、無事に望みどおりの結果が出力できました。ありがとうございます。

ただ、質問の際には単純化してお伝えしたのですが、実際には商品番号が100以上あったり、売上個数の入力される列が2列目ではなく4列目だったりします。この2点だけカスタマイズしたいのですが、VBA辞典なるものを読んでみたもののイマイチよく分かりません。ご教授いただけると助かります。


2 ● きゃづみぃ
●23ポイント

実行する前にシートを二つ作ってください。

そのシートの名前を

実行

データ

にしてください。

次に 実行のシートの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列目にするにはどのようにすればいいのでしょうか?ご教授いただけると助かります。


3 ● airplant
●22ポイント

日付が重要なキーになっていると思います。

この重要なキーがレコードに入っていないのが問題と思います。

日付を入れてしまえば、後はピボットテーブルなどで簡単に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にしてみたのですが、空白部分がつめられてしまうため、売上個数が正しく集計できなくなってしまいました。対応策がございましたらご教授ください。

> 途中で新製品が出たり、名称変更することはないでしょうか?

はい、ありません。想定して多めに商品番号を用意している、という状況です。


4 ● SALINGER
●22ポイント

修正したものを回答します。

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
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ