エクセル マクロを使って日々の実績を累計したい

質問

エクセルで各社員の日々の販売実績を入力しています。
縦の列に各社員の氏名、横の列は商品名（9種類程度）の表になっています。
この表（シート名：営業実績表）に各自の実績を入力し、１営業日分を印刷、さらに別のシートに累計（シート名：実績累計表）しているのですが、
その際にわざわざ電卓をたたいて累計を入力している有様です。
そこで、この作業をマクロを使って簡単にできないものかと思いこちらに質問させていただきました。
ポイントは
(1)1営業日分に関しては紙面での報告だけを求められているので、印刷、累計後はクリアしている。次の日、同じ表を使って日付を一日進めた上で実績を入力している。
(2)累計は一週間、一ヶ月単位ではなく、商品のラインナップが変わるまで行っているため、定まった期間が無い。累計の表に関しては印刷とデータ化を求められている。
(3)エクセルのバージョンは2002です。
以上です。

わかりやすくマクロの式を例示していただければ幸いです。ご回答お待ちしています。

回答No.5

MarcoRossiItaly · ベストアンサー

＞実行時エラー'1004':　アプリケーション定義またはオブジェクト定義のエラーです。　と表示されます。「デバッグ」ボタンを押すと４行目と５行目が黄色く表示されています。　For Each c In ....

申し訳ありません。こちらで、初心者みたいなケアレスミスしてしまいました。

例えば「Range("b3")」という部分だったら、本当は「Worksheets("実績累計表").Range("b3")」と書かなければならないところでした。そうでないと、マクロ実行時のアクティブシートの位置など、幾つかの使用状況によっては、お示しのエラーが発生するケースが考えられるし、少なくとも正しい答えは計算しません。

ただ実際には、「Worksheets("実績累計表").」という記述を何度も書くとゴチャゴチャして読みづらいので、With ステートメントの導入により、繰り返しを省略するようにします。

以下に差替え版のコードを掲載しますので、再度、試していただけますか。たいへん失礼しました。

Sub SumUpTables()
　　Dim s As Worksheet
　　Dim c As Range
　　Set s = Worksheets("実績累計表")
　　With s
　　　　.Unprotect
　　　　For Each c In .Range(.Range("b3"), .Cells(.Cells(.Rows.Count, "a").End(xlUp).Row, .Cells(2, .Columns.Count).End(xlToLeft).Column))
　　　　　　c.Value = c.Value + Worksheets("営業実績表").Cells(c.Row, c.Column)
　　　　Next c
　　　　.Protect
　　End With
End Sub

お礼

記述のコードで無事動作いたしました。
何度も丁寧に答えていただきまして本当にありがとうございました。

回答No.4

MarcoRossiItaly

No.1 で言ったように、「Sheet1」というシート、あるいは「データ入力用シート」の値を別のシートに持ってくることは、ごく簡単な方法としては、次式のような感じでできます。

=sheet1!a1
=データ入力用シート!a1

このようにして、「営業実績表」シートにデータを持ってくればいいです。「営業実績表」シートのどこかに何か（例えば日付）を入力することにより、データ入力用シートのどの辺りから参照するのか、位置を指定するといったことも可能です。VLOOKUP だったり OFFSET だったり、抽出に使える様々な関数が Excel には用意されています。

＞そうです、前日までの合計に当日の値を加算したものです。
　ただ、「営業実績表」のところでも触れていますが、こちらは日々消しては入力、を
　繰り返しているので、二日前以前のデータは反映できるのものなのでしょうか？

データを消さずに蓄積していけば、簡単です。

表計算ソフトは、作業列を使うことで真の実力を発揮できます。そのためにシート上には、たくさんのセルが用意されています。1 本の数式だけで求めることができる答えには、限界があります。

作業列を置く場所は、同じシートの印刷範囲の外などでもいいし、別シートでも全く問題ありません。

次式は、作業列として用意したシート「計算用シート」の B ～ D 列に記入されている数値を合計します。

=sum(計算用シート!b:d)

例えばある日、「計算用シート」の B2:D4 というセル範囲にその日の値をコピーして貼り付けたとします。もちろん冒頭の説明のように、数式で転記してもいいです。すると、別のあるシートの、上式を記入してあるセルに、B2:D4 の合計値が算出されているはず。次の日、1 行空けて「計算用シート」の B6:D8 にその日の値を貼り付けるなどすると、上式を記入したセルの値は、前日と当日の合計値に変化しているはずです。「計算用シート」の過去の値を消さずに取っておけば、常に正しい累積値となっているはずです。

ご質問の操作を行うマクロのコードを下のとおり示します。標準モジュールに貼り付け、Alt+F8 などから実行してください。

一度計算したら、マクロですので、元に戻せません。毎回どんどん足されていくばかりです。間違えて実行した場合は、保存せずに Excel ファイルを閉じ、開き直してください。マクロの扱いに自信がない場合は、処理しようとしているファイルを丸ごとコピーして、それを実験台としてください。マクロをある程度理解するまでは、念のためファイルのバックアップを取りながら使うといいかもしれません。

コードは「実績累計表」シートを保護するようにしています。これを手動で解除するには、シート見出しを右クリックし「シート保護の解除」をクリックするなど。社員数・商品数の増減にも対応しているので、既存シートの行列の追加／削除をあらかじめ手動で行ってもらえば、マクロのほうは増減を気にせず使っていただけるかと思います。

補足を拝見する限りこのコードで大体うまく行くのではないかと思いますが、セルの結合など想定外のことがあると、失敗する可能性もあります。その場合は、コードのエラーの出た行や、エラーメッセージをお知らせください。

Sub SumUpTables()
　　Dim c As Range
　　Worksheets("実績累計表").Unprotect
　　For Each c In Worksheets("実績累計表"). _
　　Range(Range("b3"), Cells(Cells(Rows.Count, "a").End(xlUp).Row, Cells(2, Columns.Count).End(xlToLeft).Column))
　　　　c.Value = c.Value + Worksheets("営業実績表").Cells(c.Row, c.Column)
　　Next c
　　Worksheets("実績累計表").Protect
End Sub

補足

度々詳しいご回答、ありがとうございます。
お返事が遅くなり申し訳ございません。
下記のようにエラーメッセージが表示されます。

上記のマクロを実行すると、
実行時エラー'1004':
アプリケーション定義またはオブジェクト定義のエラーです。
と表示されます。

「デバッグ」ボタンを押すと４行目と５行目が黄色く表示されています。
For Each c In Worksheets("実績累計表"). _
　　Range(Range("b3"), Cells(Cells(Rows.Count, "a").End(xlUp).Row, Cells(2, Columns.Count).End(xlToLeft).Column))

以上です、何度もお手数をおかけいたしますがよろしくお願いいたします。

回答No.3

MackyNo1

マクロを実行すると、もしおかしな挙動をしても、ファイルを元にも出せない（Ctrl+Zの元に戻す操作ができない）ので、マクロコードをご自分で編集できないレベルなら、マクロで会社の重要なデータを管理することはやめた方がよいと思います。

ご希望の操作（どのセルの合計をしてどのセルに累計するのか）がいま一つわからないのですが、個別の帳票データをデータベースシートに追加するなら、ご自分でマクロを作成しなくても、アドインの機能で「データ追跡機能付きテンプレートウィザード」を利用することができます。

ただし、この場合でも個々のデータをデータベースに自動的に追加する機能になりますので（これもすでに回答がありますが、このような形式でデータを追加していくのがExcelのデータ処理の基本です）、各種集計はエクセルの関数やピボットテーブルの機能で集計することになります。

ひとまず、ご自分でヘルプなどを参照して、データ追跡機能付きテンプレートウィザードを利用してみて、その利用方法や作成されるデータベースからどのように希望のデータを表示すればよいかわからないところがあったら、再度ご質問してください。

回答No.2

MarcoRossiItaly

追加で、次の情報の補足をお願いします。

●毎日増えていくという「営業実績表」のシート名の一例。
　それとも、クリアすると書かれているので、シートのコピーはせずに 1 シートを消しながら使い回しているということですか？
●シート「実績累計表」のセル配置などの構造　　←重要
●「実績」（「営業実績表」のセル範囲 B3:J23 のデータ）の内容
　金額、個数などの数値でしょうか？
　それとも、文字列を入力し、何らかの方法でその個数を数えるなどしているのでしょうか？
　当日の全体の実績は、どこか別のセルに表示しているのでしょうか、いないのでしょうか？
●「累計」の意味
　前日までの合計に当日の値を加算するという意味でしょうか？
　そうであれば、その算出の方法については、マクロとは無関係に、あらかじめ「実績累計表」に SUM 関数などを記入しておくだけで済みそうです。
　当日分のみ「実績累計表」に転記すれば、自動で算出されます。
●「商品のラインナップが変わる」ときの各シートの構造の変化
　これが分からないと、マクロを作っても、すぐに使えなくなる恐れが強いです。

＞よろしければ引き続きご教授お願いします。

できれば次回からは、業務委託ではなく、ご質問をお願いします。

知識が少なすぎて何を聞いていいかも分からないという状況だとしたら、ご自分でメンテもできないということになりますから、正直、荷が重いかもしれません。

もらった回答に出てくる各コードの意味は、ザッとでも調べてください。VBE の画面で各単語にカーソルを置いてキーボードの F1 キーを押せば、ヘルプが出ます。インターネット上にも無数の情報があります。

＞……この作業をマクロを使って簡単にできないものかと思い……

使うのは簡単だとしても、作るのはそうではないということが既にお分かりかと思います。

＞とのことでしたが表の様式に規定があり、というよりは自分も含めてあまりエクセルに詳しくない社員も居り、変えることができないため、前述のような質問になりました。

理由がそれだけだとしたら、No.1 で回答しているとおり、「データ入力用シートからリンクを貼って印刷用のほうに表示させればいいだけ」です。大抵の場合、それが可能です。

つまり、「営業実績表」シートは印刷用とし、入力シートは別に新しく設けるということになります。そのほうが様式の存在を無視できるので、保有データの分析、転用、加工などがしやすくなり、価値が高まります。また、1 番目の●のところに書いているように 1 シートの使い回しをしているという場合は、別シートに入力する方式に切り替えればイチイチ消す必要がなくなるので、データが蓄積されていくというメリットもあります。マクロは全く無用という仕組みも恐らく可能です。今後、ご検討ください。

補足

ご回答ありがとうございます。補足です

●毎日増えていくという「営業実績表」のシート名の一例。
　それとも、クリアすると書かれているので、シートのコピーはせずに 1 シートを消しながら使い回し　ているということですか？
→１シートを消しながら使いまわしています。日々の実績は紙面のみの報告なので印刷した後は消して次　の日データを入力しています。

●シート「実績累計表」のセル配置などの構造　　←重要
→「営業実績表」と同じ構造です。タイトルとシート名のみ変えて使っています。

●「実績」（「営業実績表」のセル範囲 B3:J23 のデータ）の内容
　金額、個数などの数値でしょうか？
　それとも、文字列を入力し、何らかの方法でその個数を数えるなどしているのでしょうか？
　当日の全体の実績は、どこか別のセルに表示しているのでしょうか、いないのでしょうか？
→数値です。各自の販売した個数になります。
　「誰がどの商品を何個売ったか」を表示する表なので全体の実績は表示していません。

●「累計」の意味
　前日までの合計に当日の値を加算するという意味でしょうか？
　そうであれば、その算出の方法については、マクロとは無関係に、あらかじめ「実績累計表」に SUM 　関数などを記入しておくだけで済みそうです。
　当日分のみ「実績累計表」に転記すれば、自動で算出されます。
→そうです、前日までの合計に当日の値を加算したものです。
　ただ、「営業実績表」のところでも触れていますが、こちらは日々消しては入力、を繰り返しているの　で、二日前以前のデータは反映できるのものなのでしょうか？

●「商品のラインナップが変わる」ときの各シートの構造の変化
　これが分からないと、マクロを作っても、すぐに使えなくなる恐れが強いです。
→扱っている商品が変わった場合、エクセルのファイルをコピーして、見出しの商品名を変えてあらためて実績の入力をしています。それ以外の配置等は変わらないので、転用できると考えています。

事細かいご説明、ありがとうございます、当方も業務の合間に学習していきますので、
引き続きご教授おねがいいたします。

回答No.1

MarcoRossiItaly

毎日、別シートに「営業実績表」というのを作っているという意味でしたら、日ごとに分けずに、縦にどんどん書き足していく表に作りかえることをお勧めします。そのほうが様々な抽出・分析もできます。

シートが分かれているとしても、私なら電卓は使わないでしょうね。マクロまで使わないとしても、セルに数式をプリセットしておいて、新しく作製したシートから数字を拾うようにする（＊）など。

＊シートが存在していない間はエラー値「#REF!」が表示され、シートが作製されてもこのエラーは自動では解消されません。
　エラーが出ているセルをダブルクリックするか、置換の機能で「!」→「!」という文字の置換をするなどして、解消します。

もっとお勧めなのは、前述のとおり、必要以上にシートを分けないことです。印刷用シートが必要なら、データ入力用シートからリンクを貼って印刷用のほうに表示させればいいだけです。多くの場合、あちこちのシートに入力する必要はなく、そうならないような手段が存在します。

マクロなら、シートのコピーは ActiveSheet.Copy after:=Worksheets("既存のシート名") といった感じでできます。コピーしたシートにある様式中の特定部分のデータのみ消したいなら、Range.ClearContents メソッドなど。印刷は、Worksheet.PrintOut で。

＞累計は一週間、一ヶ月単位ではなく、商品のラインナップが変わるまで行っているため、定まった期間が無い。

シートの構造、データ蓄積のルール、具体的な箇所などに応じて、最下行の行番号を取得したり、各種ワークシート関数を VBA 上で使用することなどによって、一連の作業を自動化します。

＞わかりやすくマクロの式を例示していただければ幸いです。

前述のとおりです。具体的な情報がないため、完全なプロシージャなどを示すことはムリです。

補足

早速のご回答ありがとうございました。
「日ごとに分けずに、縦にどんどん書き足していく表に作りかえることをお勧めします」
とのことでしたが表の様式に規定があり、というよりは自分も含めてあまりエクセルに詳しくない社員も居り、変えることができないため、前述のような質問になりました。
具体的な情報は以下の通りです。「営業実績表」、「累計実績表」各シートともに同じ構成です。
縦列は、A1セルに日付、A2は空白、A3からA23までが社員の名前、
横列は、B2、C2、D2・・・J２まで商品名の見出しです
残るB3～J23までが日々の実績を入力するセルです。

よろしければ引き続きご教授お願いします。