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


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

回答の条件
  • 1人3回まで
  • 登録:2008/06/11 15:03:04
  • 終了:2008/06/18 15:05:03

回答(4件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692008/06/11 20:52:33

ポイント23pt

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

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
id:td555

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

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

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

2008/06/16 15:24:28
id:taknt No.2

きゃづみぃ回答回数13537ベストアンサー獲得回数11982008/06/11 20:53:29

ポイント23pt

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

そのシートの名前を

実行

データ

にしてください。

次に 実行のシートの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

id:td555

ご回答ありがとうございます。マクロを触るのがはじめての為、苦戦しておりました。問題なく動作しました。ありがとうございます。

ただ、質問の際には「単純化したパターンで回答をいただき、実際のパターンへのカスタマイズは自分でしよう」と思っていたのですが、マクロが全く読めず苦戦しています。実際には売上個数の入力される列が2列目ではなく4列目なのです。合計する列を2列目ではなく4列目にするにはどのようにすればいいのでしょうか?ご教授いただけると助かります。

2008/06/16 16:56:00
id:airplant No.3

airplant回答回数220ベストアンサー獲得回数492008/06/12 01:35:53

ポイント22pt

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

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

日付を入れてしまえば、後はピボットテーブルなどで簡単に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レコードずつ扱ったほうが良さそうです。

id:td555

ありがとうございます。カンマ区切りですので、コメントでいただいた方法で無事動作いたしました。

ただ、質問の際には単純化してお伝えしたのですが(すみません)、実際は集計には必要のない数値が商品番号と売上個数の間に入っていまして、その数値が0の場合、0ではなく空白が出力されています。

  • 20080601.csv------------------

商品番号1,1,2,2

商品番号2,1,,1

商品番号3,1,2,1

商品番号4,,2,1

商品番号5,1,,2

商品番号6,,2,8

-----------------------------------

教えていただいた方法でできたtxtの拡張子をcsvにしてみたのですが、空白部分がつめられてしまうため、売上個数が正しく集計できなくなってしまいました。対応策がございましたらご教授ください。

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

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

2008/06/16 16:50:17
id:SALINGER No.4

SALINGER回答回数3454ベストアンサー獲得回数9692008/06/17 18:35:58

ポイント22pt

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

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
  • id:taknt
    ファイル名の一覧の取得の仕方
    テキストファイルに
    DIR /B /S > list.txt
    と書いて ファイル名は list.bat とします。
    これを ファイルがあるフォルダ内にコピーし ダブルクリックで実行させれば list.txt が出力されます。
  • id:SALINGER
    私の回答
    Dim myFile As file → Dim myFile
    にして、一番先頭のシートをアクティブにして実行してください。
  • id:taknt
    airplantさん、タブ区切りのファイルは tsvと 呼ばれていますね

    csvのcは カンマ という意味です。

    ですが、質問には 空白で区切ってあったので 空白でも対応するようにしました。
    ただし 出力は カンマ区切りですが。
  • id:airplant
    takntさん、指摘ありがとうございました。
    csvはカンマ区切りであることは、知っていました。
    空白で書いてあったもので、、、takntさんと同じに、空白をセパレータとして回答しました。

    本来のcsvでカンマ区切りになっている場合は、私の回答もうまく動きません。
    最終行を次のように変更すれば動きます。
    for /F "delims=, tokens=1-3" %%I in (%1) do echo %FileDate%,%%I,%%J >> %2

    出力例
    2008/06/01,商品番号6,8
    2008/06/12,商品番号1,24

    なお、tsvは拡張子不定のようです。txtなどが一般的でしょうかねぇ。
    http://e-words.jp/w/E382BFE38396E58CBAE58887E3828A.html
    今回のフォーマットを無理に表すと「prn」(=空白区切り)になりますか。
  • id:airplant
    連絡ありがとうございました。
    入力のテキストフォーマットが質問とは違っていたのですね。
    日付を前につけるだけのスクリプトに変更すれば、大丈夫です。

    最終行を次のように変えてみてください。
    for /F "tokens=*" %%I in (%1) do echo %FileDate%,%%I >> %2

    例で出されていた内容は、次のようになりますので、後はヘッダに適当なタイトルをつけて、ピボットテーブルで一発集計できます。
    タイトルも固定と思うので、例えば、次のような文をforの前に入れておけば自動でできます。
    echo 売上日,商品番号,dummy1,売り上げ個数,dummy2 > %2
  • id:taknt
    CSVの列の最後の列、4列目が最後なら それを取得するには
    Worksheets("データ").Cells(d, "C") = Right(c, Len(c) - InStr(c, ","))

    の箇所を

    Worksheets("データ").Cells(d, "C") = Right(c, Len(c) - InStrRev(c, ","))

    にしてください。

    ちなみに InStrRevで 右から 最初にある "," までの位置を取得して その位置から 値を取得しています。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません