エクセル2003の過去データの取得方法

質問

職場で、成績表を作成しています。
毎日その日のデータを入力し、週間合計、月間合計、年間合計を算出してますが、このファイルは月ごとにデータを入れ替えているので、過去の月間合計データが残っていません。
現在は、月締めに年間合計シートにその月のデータを貼り付けています。

この貼り付け処理を関数やマクロを使い、自動にするベストな方法をご教授ください。

例

シート１(1週から6週のシートがあります)

＊毎月入れ替えています

3/1 3/2 3/3 3/4 3/5 3/6 週合計
aさん 1 2 3 4 5 6 21
bさん 1 2 3 4 5 6 21

シート７(月間)

今日の日 月間合計
aさん 1 21
bさん 1 21

シート８(年間)＊このシートに毎月データを入れたい

1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
aさん
bさん

＊日々データが大量にあるため、日々データを残すとシート数が増えてしまう。
＊カレンダーを作成し、月が変わった時の日付も関数で自動入力にした。
＊今日の日も検索関数を使用し、自動入力にした。
＊慣れない人が使用するため、汎用性をもちできるだけ自動化したい。

年間合計で行き詰ってしまいました。

回答No.1

keithin · ベストアンサー

はい，先のご質問のように何の制約もなく「ベストな方策」を問われれば，「これがベストです」という回答が寄せられます。
既にここまで作り込んである，「あとここをこうしたい」と詳しくご相談を書いてみると，それならこうしてみましょうといった具体的なアドバイスも寄せられやすくなりますね。

＃先のご相談投稿は「解決」で閉じる操作を行い，マルチポストを解消しておいてください。

さてさて。
今回は詳細が寄せられましたので，煎じ詰めると
１．シート７の月合計欄の値を取り出し
２．シート８の目的とする列を調べて
３．そこに値で記入する
という動作をさくっとマクロに行わせたい，というご質問になります。

＃余談としては，たとえば締め日の翌日以降に最初にブックを開くと
　自動で年履歴シートに転記を行い，更にシート１～７をクリアして，
　今月の仕事始めに備える
　といった具合にアイデア次第で「どんな事でもできます」。
　まぁ，そういったアドバンスはまた次の機会にでも。

準備：
モノゴトを簡単にするため，シート７の「Ｂ１セル」に，今一体「何月」のデータを集積中なのか「１月２月３月…と表示を出させてみましょう」。
その際に重要なのは，B1に計算で表示する言葉は「シート８の年間履歴表の横項目として書いてある１月２月３月…の表記と完全に同じ言葉に一致」させておきます。

また，うっかり翌月に持ち越してしまっても「今何月です」の表示が「勝手に変わってしまう」のを避けるため，「TODAY()関数などで自動計算はさせない」ことにします。

その他準備：
シート７の(転記したい)月間集計欄は，仮に「シート７のＤ３：Ｄ１２」のセル範囲だとする
シート８に持って行きたい先の年間履歴表は，仮に「シート８のＣ３からＮ１２」のセル範囲にあって当月の該当する列だとする

その他準備：
シート８のＣ２からＮ２セルに「１月，２月，３月…」の表記があるとする
シート７のＢ１セルに，データメニューの入力規則でリストを選び，下の空欄に
=INDIRECT("Sheet8!C2:N2")
と設定する
B1セルのプルダウンで，いま何月を入れているのか選んで使う。

サンプルマクロ
sub Macro1()
dim r1

if worksheets("Sheet7").Range("B1") = "" then
msgbox "月が選ばれていません"
exit sub
end if

r1 = application.match(worksheets("Sheet7").range("B1"), worksheets("Sheet8").range("C2:N2"), 0)

if application.count(worksheets("Sheet8").range("B3:B12").offset(0, r1)) <> 0 then
msgbox "選ばれている月のデータは記入済みです"
exit sub
end if

worksheets("Sheet8").range("B3:B12").offset(0, r1).value = worksheets("Sheet7").range("D3:D12").value

end sub

以上です。

お礼

ご丁寧なアドバイス本当にありがとうございました。

今回は、シート内に毎月のデータを残す方法で
作成する事になりましたので、関数で対応していきます。

マクロ使用もこれから増えていくので、
上記内容もこれからの参考にさせて頂きます。

回答No.2

Nouble

私は過去に似たような経験を得て関数を組んだことがあるのですが
処理速度が我慢の限界を超えましたので
一連の作業を裏座技など御含めてマクロに登録させて使っていたのですが
それでも処理終了に2時間ほど掛かっていました
アクセスの導入を検討されるか
最低でも1ブック内にもっと多くのシート
もっと多くのデータを排するように
変えられた方が得策だと思います

Excelはブック内参照はそこそこの速度で行いますが
それに比べれて相対的にブック間参照は途轍もなく遅くなりますのでね

この投稿がお役に立てば幸いです