エクセルで、異なる複数のブック、シートに対して計算式を使う方法について教えていただきたいです。

業務でエクセルを使っているのですが、現在作成しようとしている資料がうまく作れず困っています。
色々と検索しましたが解決せず、またマクロの知識も乏しいもので...お教えいただけると幸いです。


[元データとなるブックの内容]
縦軸＝時間、横軸＝商品ジャンル、セルには金額、という内容の時間帯別売上推移を入力したものです。
シートは1日毎になっており、31日ある月ではSheet1～31まで作成しています。

[状況]
上記内容のブック（仮に店舗bookとします。）が約30ほどあり、それぞれ別のフォルダ内に格納され管理されています。
今回は、それらを合計するために別のブック（全店bookとします。）を作成し、各店舗bookの各日、各時間帯別の売上金額を算出したいと思っています。
（全店bookの書式構成は店舗bookと全く同じです。）

＿ジャンルＡ｜ジャンルＢ｜ジャンルＣ...
１￥１０００｜￥２０００｜￥３０００
２＿＿＿＿＿｜＿＿＿＿＿｜＿＿＿＿＿
３＿＿＿＿＿｜＿＿＿＿＿｜＿＿＿＿＿
.
.

↑イメージです。


全店bookに『=[店舗book1.xls]Sheet1!B2+[店舗book2.xls]Sheet1!B2+[店舗book3.xls]Sheet1!B2+...』といったように手作業で数式を入力してオートフィルを使う方法では、その日（シート）だけなら何とかできますが、それが31日分となると膨大な作業量となってしまいます。
かといって数式をコピーしても２日目はSheet1ではなくSheet2を参照しなければいけないので、結局手打ちで数式を弄らなければならず困っています。

このような条件ではマクロが有効かと思いマクロの記録を使ってみましたが、あくまでも指定のセルを計算するようなコードしか保存されずうまくいきませんでした。

作業量を減らし効率的に全店bookを作成する方法はないでしょうか。
ご教示いただけると幸いです。

質問者からの補足コメント

• 現在このような形で既に半年分のデータを蓄積してしまっており、
  今後の分析方法は見直すとしても、この半年分だけは何とかしなくてはいけません。
  良い方法はありませんでしょうか。

    補足日時：2015/07/25 22:39

No.3 ベストアンサー 回答者： LuckyX 回答日時： 2015/07/26 19:14

手作業の延長ですが、こんな方法は如何でしょうか？

ちょっと手数がかかりますが、簡単です。

１．お考えのとおり、全店bookの1日目のシートのジャンルAに、集計の式、『=[店舗book1.xls]Sheet1!B2+[店舗book2.xls]Sheet1!B2+[店舗book3.xls]Sheet1!B2+...』といったように手作業で数式を入力し、次に、オートフィルを使って、ジャンルB、ジャンルC、・・・と、1日目の分を全部完成させます。

２．2日目以降のシートの作成方法は、以下のように、「検索・置換」機能を使います。

（１）1日目のシートの数式を、そのまま全てコピーして、貼り付けます。
この貼り付けた段階では、1日目のシートと同じ計算式、『=[店舗book1.xls]Sheet1!B2+[店舗book2.xls]Sheet1!B2+[店舗book3.xls]Sheet1!B2+...』が、各時間帯と各ジャンルのセルの入っています。次に、

（２）2日目以降のシートに対して、「検索・置換」機能により、
【検索する文字列】に、「Sheet1」と入力し、
【置換後の文字列】に、「Sheet２」と入力し、
「すべて置換」のボタンを押せば、一挙に、2日目（ここでは、「Sheet２」）になります。

同様に、3日目のシート名「Sheet3」、4日目のシート名「Sheet4」、・・・・と、31日分を繰り返し行えば、完了です。

（３）念のために、日にちのシート名を正しく「置換」していることを確認してください。

これなら、簡単に完成します。

No.2 回答者： hallo-2007 回答日時： 2015/07/26 09:31

少しだけ楽になると思いますが

新しいブックを作成
VBエディターを起動して
Sub ボタン1_Click()
Dim buf As String
buf = Dir(ThisWorkbook.Path & "\*.xls*")
j = 2
With ThisWorkbook.ActiveSheet
Do While Len(buf) > 0
If buf <> ThisWorkbook.Name Then
Workbooks.Open ThisWorkbook.Path & "\" & buf
For i = 1 To Worksheets.Count
Sheets(i).Activate
For k = 1 To Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & j).Value = buf
.Range("B" & j).Value = Sheets(i).Name
.Range("C" & j & ":E" & j).Value = Range("A" & k & ":C" & k).Value
j = j + 1
Next
Next
ActiveWorkbook.Close SaveChanges:=False
End If
buf = Dir()
Loop
End With
End Sub

を張り付け閉じる
データのあるフォルダーへ一旦、保存します。
上記のマクロを実行すると
同じフォルダ内のファイルを順次開いて
A列に　ファイル名
B列に　シート名
C~E列に　A~C列に値を
一枚のシートにまとめます。
.Range("C" & j & ":E" & j).Value = Range("A" & k & ":C" & k).Value
の部分をジャンルの数だけ広げてみてください。

No.1 回答者： hallo-2007 回答日時： 2015/07/25 21:38

これでは、なかなか回答が付くのは難しいと思います。

基本的なデータの流れが日々の作業を難しくしています。
一案ですが
＿ジャンルＡ｜ジャンルＢ｜ジャンルＣ...
１￥１０００｜￥２０００｜￥３０００
２＿＿＿＿＿｜＿＿＿＿＿｜＿＿＿＿＿
３＿＿＿＿＿｜＿＿＿＿＿｜＿＿＿＿＿
これも、各店舗で誰かが手入力されているわけですよね。
出来れば、レジのシステム　或いは　会社のシステムで
１　日付　時刻　ジャンル名　金額
２　7/1　9:00　ジャンルA　１０００
３　：：：
と云ったようなデータは出来ないでしょうか。
無理であって、レジのシートを見ながら手入力しているにしても
こちらの方が入力しやすいし、入力のミスも少なくなると思います。
一枚のシートにひたすら縦方向にデータを入力してもらいます。
そして、各店舗でピボットテーブルでも使って
日付　時間帯　ジャンル名　金額
と云った風に集計してもらいます。
これで各店舗で集計・分析の責任を担ってもらいます。
あなたには、その集計結果をメールででも送ってもらいます。
あなたは、各店のからのデータを開いて
１　店舗名　日付　時間帯　ジャンル名　金額
といった内容で縦方向にデータを張り付けていきます。
最後に、ピボットテーブルで
横列に　ジャンル名
盾列に　日付
で集計して報告書を作成します。
一度、上司の方と相談してみてください。
各店舗で時間帯別に手計算して入力する作業
貴方が集計する作業含めても楽になります。
又、ピボットテーブルで集計する内容などで
色々な集計が可能になり経営の役に立つ方法が色々と見つかると思います。
又、各店舗でも集計できますので、各店舗でも分析の役割を果たしてくれます。

この回答へのお礼

ありがとうございます。
そうですね...仰られる通り、もともとのデータの蓄積方法と集計の方法を
根本からやり直して効率よくするのが先決だと思います。
今後の分析については、教えていただいた方法を参考に上司と相談します。

お礼日時：2015/07/25 22:37