[[20170111140901]]　『別ブック　データ年代別』（akira115）　ページの最後に飛ぶ

『別ブック　データ年代別』（akira115）

Excel2003です。現在顧客ごとに、年齢、住所を入力しています。
新しいブックに入力している年齢を年代別に集計しようと思っています。住所も範囲を設定して集計できるようにしたいです。
フォルダをあ行、か行のように作成していて各フォルダに100ブック程あります。よい方法を教えてください。

＜使用 Excel：Excel2003、使用 OS：Windows7 ＞

こんにちは

何を集計するのですか？

足すのではなくて、データ一覧を作るという事ですか？

よい方法・・・漠然としているので答えにくいですが、マクロで1ブックに纏めてから考えてはどうですか？

（ウッシ） 2017/01/11(水) 14:24

マクロで1ブックに纏めてからとはどういうことでしょうか？
（例）1book あ様　年齢43歳　
　　　2book い様　年齢31歳
　　　3book う様　年齢53歳
　となっているものを1つのブックに年代別に分けたいです。

（akira115） 2017/01/11(水) 14:31

追記　年齢は全部同じセルではなくC3～C10までに入力されています。
（akira115） 2017/01/11(水) 14:35

こんにちは

どのような意味で「集計」という言葉を使っているのか、実際はどうしたいのか

具体的にしないと分からないです。

（例）1book あ様　年齢43歳　

 　　　2book い様　年齢31歳 
 　　　3book う様　年齢53歳

も、意味が分からないです。

フォルダ階層、ブック名、シート名、シート内のデータ位置、転記する項目とそのセル位置

をはっきりさせないと。

1ブックに纏めなくても、

年代別に顧客名と年齢だけ抽出する。

 住所も範囲？別に顧客名と年齢だけ抽出する。

だけで良いとしてもいちいち全フォルダの全ブックを検索するのは非効率的だと思います。

データは一元管理して、必要なデータを抽出する方法に変えた方がいいと思います。

＞　年齢は全部同じセルではなくC3～C10までに入力されています

そのセル範囲のどの部分が年齢なのかどうやって判断するのですか？

ロジックで判定出来ないような構成のブックなら、全て手作業でという事になりますよ。

（ウッシ） 2017/01/11(水) 14:44

名前　生年月日　年齢　保険種目　保険会社　保険料　住所
という表を作成し、それぞれの項目に入力し顧客ごとに保存しています。
この方法じゃできないってことですか？
（akira115） 2017/01/11(水) 14:55

こんにちは

フォルダをあ行、か行のように作成していて各フォルダに100ブック程

というブックは1ブック1顧客ではないのですか？

「あ」「い」「う」・・・という先頭1文字のかな名でブックを分けているのですか？

名前　生年月日　年齢　保険種目　保険会社　保険料　住所年代　郵便番号？

という項目を1行目にセットしたブックに各フォルダの100ブック程のブックのデータを

一覧表の形で纏めてはという提案です。

集計する項目が「保険料」（ここで初めて出てきましたが）なら、その一覧表から

ピボットテーブルで年代別に集計とか、郵便番号の範囲で集計とかできますから。

（ウッシ） 2017/01/11(水) 15:03

判らない事だらけな質問ですね。

・特定のフォルダの下は、同じ階層に、あ行、か行…のフォルダが並んでいますか？
・対象となるデータは、各ファイルの、どのシートにありますか？シート名は全て同じ？
・年代別、とおっしゃっていますが、データにあるのは「年齢」であり、生年月日ではないようです。全てのファイルは、同じ年度に作成されたものですか？年度が違うと、現在の年齢は違ってきますが、平気？
・年齢が入ったセルはC3～C10との事ですが、その中の何処なのかを判別できる規則性はありますか？例えば、データの入っているのはこの中のどれか1セルであり、他の7セルは必ず空欄、とか。それとも、必ずC3セルには入力されていて、1人～8人分の入力があるのでしょうか？
・保険云々の項目が後出し追加されていますが、これは後から手入力するのですね？

とりあえずは、どのようなロジックを書けば良いのかの方針だけ教えれば良さそうなので、マクロの考え方だけ書きますね。
まず、特定フォルダ（あ行、等の1階層上）を元に、サブフォルダを含めた、xlsファイルの一覧を作成します。
（私が過去に何回も書いている、DIRコマンドを利用したコードがありますので、探してください）

次に、該当する全ファイルを順に開いていき、特定シートの特定セル範囲を調べます。空欄以外ならば、自シートにデータを抜き出します。これを繰り返す事で、自シートに、ブック名、全員の名前、年齢、住所の一覧を作成します。

1シートに全てのデータを集めてしまえば、後はどうにでもできるでしょう。
（???） 2017/01/11(水) 15:08

　A B　　　C 　D　　　　E　　　　F　　　　　　　　G
1名　前生年月日年齢種目保険会社月払保険料年間保険料
2信博様昭和23年1月31日 68自動車三井住友海上 34,660円

こんな感じで作成しています。
世帯ごとに保存しているので、他にご家族がいれば下に追加されます。
なので入力があるブックとないブックがあります。

（akira115） 2017/01/11(水) 15:20

年齢ではなく生年月日だし、C3からだと言っておきながらC2セルに情報があるようだし…。事実を説明してもらわないと、ますます不安になっただけですよ。
それともこれは、元ブックではなく、新たに作成するまとめシートのレイアウトでしょうか？説明は、元ブックのように見えますが…。

それと、家族分は下に続くのならば、C3～C10セルなんて使う必要はなく、C2セルからだけ抜き出せば良いと思いますが、なぜ年齢がC3～C10だという説明があったのでしょうか？
（???） 2017/01/11(水) 15:38

あぁ、68自動車ではなく、年齢が68歳なんですね。で、年間保険料の列は空欄？
全てのブックに生年月日があるならば、年齢なんて列は無視して、生年月日を使うべきかと思います。

シートの構成に関しては、どうなのでしょう？全て同じシート名ですか？それとも、全て1シートだけ使えば良いのですか？まだまだ判らない事だらけです。
（???） 2017/01/11(水) 15:45

月払保険料が空欄で年間保険料が入力されています。月払の方は月払保険料にも入力があります。
すべてのブックに生年月日はあります。
シートは全て違うシートです。
全て1シートだけしか入力していません。
（akira115） 2017/01/11(水) 15:54

こんにちは

ザックリしたマクロだと、

Sub test()

    Dim t As String
    Dim w As Workbook
    Dim v As Variant
    Dim r As Range
    'ブックの入っているフォルダ名は「あ行」「か行」とかとする
    'あ行、か行とかのフォルダがまとまっているフォルダ名
    Const f As String = "C:\temp\DATA\"
    Application.ScreenUpdating = False
    For Each v In Array("あ行", "か行", "さ行", _
                        "た行", "な行", "は行", _
                        "ま行", "や行", "ら行", _
                        "わ行", "を行", "ん行")
        If Dir(f & v, vbDirectory) <> "" Then
            t = Dir(f & v & "\*.xls", vbNormal)
            Do While t <> ""
                Set w = Workbooks.Open(f & v & "\" & t)
                Set r = w.Worksheets(1).Range("A1").CurrentRegion.Offset(1)
                r.Copy ThisWorkbook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1)
                w.Close
                t = Dir()
            Loop
        End If
    Next
    Application.ScreenUpdating = True
End Sub

こんな感じです。

フォルダ名とかパスは適宜変更して試して下さい。

（ウッシ） 2017/01/11(水) 15:59

ふむ、レイアウトがさっぱり判らないので、ウッシさんは丸ごとコピーですか。
では私は、2行目だけ抜き出す例なぞ。シートモジュール用。
使うかどうか判りませんが、A列とB列には、フォルダ名とファイル名を書き出してます。

 Sub test()
    Const cPATH = "C:\temp\DATA\"
    Dim cFiles As Variant
    Dim vw As Variant
    Dim iR As Long
    Dim i As Long

    Application.ScreenUpdating = False

    iR = Cells(Rows.Count, "A").End(xlUp).Row
    If 1 < iR Then
        Rows("2:" & iR).Delete
    End If
    iR = 1

    cFiles = Split(CreateObject("WScript.Shell").Exec("CMD /C DIR /A:-D/B/S """ & cPATH & "*.xls""").StdOut().ReadAll(), vbNewLine)
    For i = 0 To UBound(cFiles) - 1
        If LCase(Right(cFiles(i), 4)) = ".xls" Then
            iR = iR + 1
            vw = Split(cFiles(i), "\")
            Me.Cells(iR, "A").Value = vw(UBound(vw) - 1)
            Me.Cells(iR, "B").Value = vw(UBound(vw))
            With Workbooks.Open(cFiles(i), False, True)
                With .Sheets(1)
                    .Range("A2:G2").Copy Me.Cells(iR, "C")
                End With
                .Close
            End With
        End If
    Next i

    Application.ScreenUpdating = True
 End Sub
（???） 2017/01/11(水) 16:39

 	A　　　　　　　B　　　　　　　C　　　　　D　　　　　　E　　　　　　F　　　　　　G
 1	名前　　　　生年月日　　　　年齢　　　　種目　　　保険会社　　月払保険料　　年間保険料
 2　　○○子様	　昭和30年6月7日　　　61	自動車	三井住友海上　　4,540円　　　54,480円
 3　　　　　　　　　　　　　　　　　　　　　　　火災　　三井住友海上　　　　　　　　830,710円

ブックに顧客名がそれぞれついていて、あ行のお客様は、あ行というフォルダに入っています。

マクロを入力いただいたのですが、うまく表示されないです。
（すみません、私があまりよく理解していないのでセルとか自分で入力するんでしょうがわからないので）

別シートに一度名前や生年月日の一覧を作成してそれから集計した方が良いですか？

よろしければその方法を教えていただけると有難いです。

何度もわかりにくい質問で申し訳ございません。

（akira115） 2017/01/11(水) 17:59

こんにちは

A3～C3が空白になっていますね。
A2～C2と同じと考えていいのですか？

家族分が追加されている場合はどのように入力されているのですか？

先に提示したマクロはA～G列の1行目には項目名がセットされていて
その下の行のセルはデータで埋まっている事が前提になっています。
保険料のセルは空白であればゼロと捉えるので問題は無いはずですが
集計時のグループ化する対象項目である生年月日の欄は必須です。
また最終行を判定しているのはA列なので、名前が入っていないセルが
有るなら生年月日が入っている事が前提ならB列、でなければ必須入力
となっている列、例えばD列とかで最終行を判定するように変更しないと
ダメです。

年代を判定するための生年月日は必須なので、マクロ上で空白セルを
埋めるロジックが必要になります。

ついでに、住所の話はどうなっているのですか？

（ウッシ） 2017/01/12(木) 08:26

＞　年別シートに一度名前や生年月日の一覧を作成してそれから集計した方が良いですか？

年別シートではなくて、マクロを設定したブックのシートに
それを実現するためのマクロを提示したつもりですけど？

（ウッシ） 2017/01/12(木) 08:30

うまく表示されない、だけでは、状況が判りません。何がどうなっているのか、具体的に書くようお願いします。

データの入っている、元のブックは現状のままです。ここにマクロを入れてはいけません。マクロ実行時は、全てブックを閉じておいてください。
新しいブックを開いて、このシートモジュールにマクロを貼ってから実行してください。
（ALT+F11を押下後、左側のSheet1をダブルクリックし、表示された領域に貼る）

今後、ご自分で修正、作成していく気はありますか？丸投げにしても、使い方くらいは調べて、学んでください。
（マクロを使う気が無いならば、今まで通り手作業でやれば良いだけです）

でもって、当人以外の家族の情報は、コピーする必要があるのですか？データは2行目以降ということで、C3～と書いてあったのは間違いですか？
（???） 2017/01/12(木) 09:11

すみません、マクロを使用したことがなく、こちらのサイトや色々見て調べてはいるのですがいまいちどれを見ても意味がわからずなので、こちらでは私のレベルだと話にならないみたいなので、エクセルから勉強してみます。訳の分からない質問に答えていただきありがとうございました。
（akira115） 2017/01/12(木) 10:29

コメント返信：

[ 一覧(最新更新順) ]

[[20170111140901]] 『別ブック データ年代別』（akira115） ページの最後に飛ぶ

[[20170111140901]]　『別ブック　データ年代別』（akira115）　ページの最後に飛ぶ