エクセルで業務日報を作りたい-（2）

質問

前回
http://kikitai.teacup.com/kotaeru.php3?q=2539979
の改良質問です。

エクセルで下記のような日報を作りたいのです。
本日の欄のみ入力すると自動的に月の累計・期の累計がでるように、
また、月・期それぞれリセットできるなんらかの機能をつけたいのですが・・・

　　本日の件数　　月の累計　　期の累計
　　　　10　　　　　　　　13　　　　　　　213

本日の件数だけを入力すると月の累計欄に前日までの累計+本日の件数、期も同じく、といった感じにしたいのです。
なるべく列は長くせず、シートも２つくらいまでで
数式・関数などは複雑でも見た目シンプルにしたいのですが
何かいい方法はないでしょうか？

わかりにくくてすみません
もう数日考え込んでいるのですが、いい方法がみつかりません
ご存知の方、どうかよろしくお願いします。

回答No.6

pc_knight · ベストアンサー

#4です。「IF文で作らないと無理でしょうか？」の回答ですが、その通りで、件数入力の対象セルをA列２～４行までとD列２～３行までと指定するにはIF分が必要です。そのため数値入力が行われたセルの行番号（Target.Row）と列番号（Target. Column）の両者について条件設定を行います。
コードの変更は、次の四つです。
（１）件数入力の行われたセルから取得した列番号を代入する変数を追加し定義
この変数を仮に”clm”と定義し、二行目に「clm = Target.Column」を追加し、「rw = Target.Row: clm = Target.Column」とします。
（２）rwとclmに「A列の２行から４行までとD列の２行から３行まで」のセル条件を付与する。
その方法は前回回答の三行目のIf～からThenの手前までの条件文を、セル条件にあうよう手直しを行います。
手直し内容は、napiiさんの楽しみのために今回の回答では割愛、やられる余地を残しておきます。頑張って下さい。もしうまく行かない場合は、回答への補足欄へご記入を！！
（３）月計／期計／本日の年月日を書き込むセルの列番号を書き換える。
Cells(rw, 2) → Cells(rw, clm+1)　
Cells(rw, 3) → Cells(rw, clm+2)
Cells(rw, 4) → Cells(rw, clm \ 4+26)
（４）前回に件数入力した時の年月日取得の変更
pdate = Cells(rw, clm \ 4+26)
以上ですが、どこかのセルに、件数をインプットした本日の年月日を書き込んでおく事が月計・期計を求める上で必須で、このコードではＡＡ列２～４行、ＡＢ２～３行を使用。「～Value = Date」が本日の年月日の書きこみです。
入力できるセルを限定するため、セルのロックとシート保護もお勧めです。

参考URL：

http://www.asahi-net.or.jp/~ef2o-inue/kihon/sub01_04.html

お礼

詳しくありがとうございます。
大いに参考にして、がんばって作ってみます。
また質問すると思うのでその際にはよろしくお願いします。

補足

お世話になってます。
頑張ってみました！

(1)こんな感じで一応動きます
が、これでいいのでしょうか？

(2)　pdate = Cells(rw, clm \ 4 + 26)
で、「￥」が入ると何が違うのですか？
また、4＋26とする意味は？clm + 30 でも同じようになると思うのですが？
年月日の列が邪魔にならないように遠くの列にしたのは意味わかります。（あれ？そうですよね）

(3)どーしてもわからないのですが、
なぜ件数をインプットした本日の年月日を書き込んでおく事が月計・期計を求める上で必須になるのでしょうか？
あほですません。
なぜか教えてください。　

Private Sub Worksheet_Change(ByVal Target As Range)
rw = Target.Row
clm = Target.Column

If Target.Column = 1 And Target.Row = 2 And IsNumeric(Target.Value) = True Or _
Target.Column = 1 And Target.Row = 3 And IsNumeric(Target.Value) = True Or _
Target.Column = 1 And Target.Row = 4 And IsNumeric(Target.Value) = True Or _
Target.Column = 5 And Target.Row = 2 And IsNumeric(Target.Value) = True Or _
Target.Column = 5 And Target.Row = 3 And IsNumeric(Target.Value) = True Then
pdate = Cells(rw, clm \ 4 + 26)
If Month(Date) = Month(pdate) Then
Cells(rw, clm + 1).Value = Cells(rw, clm + 1) + Target.Value
Else
Cells(rw, clm + 1).Value = Target.Value
End If
If Year(Date) = Year(pdate) Then
Cells(rw, clm + 2).Value = Cells(rw, clm + 2) + Target.Value
Else
Cells(rw, clm + 1).Value = Target.Value
Cells(rw, clm + 2).Value = Target.Value
End If
Cells(rw, clm \ 4 + 26).Value = Date
End If
End Sub

回答No.8

pc_knight

#7の回答にて、(1)の回答が不適切でした。
「～変更すればＤ／Ｅさん分も完璧です。」と回答しましたが、「enapiiさんのＶＢＡは完璧です。」と回答すべきでした。
笑って、お許しを・・・。

お礼

早速のご回答ありがとうございます
お礼が遅くなり失礼しました。

よくわかりました。
そして、すごく勉強になりました。
感謝　感謝！！

また、お世話になることあると思います。
今後もよろしくです。

回答No.7

pc_knight

#6です。良く頑張りましたね。質問にお答え致します。
(1)こんな感じで一応動きますが、これでいいのでしょうか？
pdate = Cells(rw, clm \ 4 + 26)をpdate = Cells(rw, clm \ 5 + 26)や pdate = Cells(rw, clm + 30)などに変更。Cells(rw, clm \ 4 + 26).Value = DateをCells(rw, clm \ 5 + 26).Value = Dateや、Cells(rw, clm +30).Value = Dateなどに変更すればＤ／Ｅさん分も完璧です。
(2)pdate = Cells(rw, clm \ 4 + 26)で、「￥」が入ると何が違うのですか？
\ 演算子は、2 つの数値の商を計算し結果を整数で返します。小学校の時の割り算で、７割る４＝１余り３としましたが、この式の右辺の「１」を求める演算子です。「clm \ 4 + 26」は、「clm \ 4」に26を加えるという意味です。
ご理解されている通り、年月日の列が邪魔にならないように遠くの列にしたのであり、「clm+30」も、その目的は同じですから、「clm+30」でＯＫです。
(3)どーしてもわからないのですが、なぜ件数をインプットした本日の年月日を書き込んでおく事が月計・期計を求める上で必須になるのでしょうか？
それは、月の累計列（Ｂ／Ｆ列）に表示の件数が何月分の累計なのか、期の累計列（Ｃ／Ｇ列）に表示の件数が何年度の件数なのかが分らないと、インプットした本日（＝Ｄａｔｅ）の件数（Ａ／Ｄ列）が月累計列の件数と同じ月なのか別の月なのかの判定が出来ないからです。同様に期の累計列の件数と同じ期なのか別の期なのかの判定もできないからです。そのため本日の年月日を, 「clm +30」列など、どこかに書き込んでおく必要があります。

回答No.5

pc_knight

No.4です。
Ａさん、Ｂさん、・・毎に件数カウントを分けて行うには“cells(2”の”2”の所を件数入力をした行番号“rw”に置き換えれば可です。そして入力した行番号を取得するため二行目に“ rw = Target.Row”を入れます。入力するセルをお好みの列に変えるには３行目の“If Target.Column = 1”（←件数を１列目にインプットした時のみ処理を行うよう制限を加る。）の値を変更して更にrw,の次の数字も変更して累積数の書込列番号を指定し直します。これにより、Ａ列を担当者名欄にすること等も出来ます。
「日付が必要ではない場合は～～教えてください。」ですがＤ列の日付のことでしょうか。それでしたら必須です。目ざわりでしたら列幅＝ゼロに設定を・・。
Private Sub Worksheet_Change(ByVal Target As Range)
rw = Target.Row
If Target.Column = 1 And IsNumeric(Target.Value) = True Then
pdate = Cells(rw, 4)
If Month(Date) = Month(pdate) Then
Cells(rw, 2).Value = Cells(rw, 2) + Target.Value
Else
Cells(rw, 2).Value = Target.Value
End If
If Year(Date) = Year(pdate) Then
Cells(rw, 3).Value = Cells(rw, 3) + Target.Value
Else
Cells(rw, 2).Value = Target.Value
Cells(rw, 3).Value = Target.Value
End If
Cells(rw, 4).Value = Date
End If
End Sub

お礼

書くところないので、ここに書きます。

No.6の補足での(3)の質問の件
「件数をインプットした本日の年月日を書き込んでおく事が月計・期計を求める上で必須になるのでしょうか？」
の件、なぞがわかりました。
年月が変わると、件数が改めて累計されるようになるわけですね。

引き続き、他の質問にも回答頂ければ幸いです。
よろしくおねがいします。

補足

ありがとうございます。
VBの知識がほんのちょっぴりあるので参考にし、自分でいろいろと動かしているところです。
が、どうしてもわからないので教えてください。

例えば、というか具体的に
　　　　A列　　　　　　　B列　　　C列 　　　　D列 　　　　　E列　　　F列
1行　今日の件数　　月計　　期計　　　今日の件数　　月計　　期計
2行　Aさんの件数　　　　　　　　　　　　　Dさんの件数
3行　Bさんの件数　　　　　　　　　　　　　Eさんの件数
4行　Cさんの件数
5行　A～Cさんの合計　　　　　　　　　　DEさんの合計

のようにするにはどうしたらいいでしょうか？
5行目は普通にサム関数ででるのはわかります。
A列すべてを担当者欄にするのではなくA列の２行から４行まで
また
D列の２行から３行まで
と指定する場合はそれぞれ一セルぶんずつIF文で作らないと無理でしょうか？
よろしくおねがいします。

回答No.4

pc_knight

Ａ２セルに本日の件数をインプットすると自動的にＢ２、Ｃ２セルに月累計、期累計が表示されるという風に解釈しこれを自動的に行うコード（末尾記載）を作成しました。
シート名「Sheet1」の所を右クリックし、コードの表示（Ｖ）を選んだときに表示される白い画面にこれを貼り付れば完了です。

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 2 And Target.Column = 1 And IsNumeric(Target.Value) = True Then
pdate = Cells(2, 4)
If Month(Date) = Month(pdate) Then
Cells(2, 2).Value = Cells(2, 2) + Target.Value
Else
Cells(2, 2).Value = Target.Value
End If
If Year(Date) = Year(pdate) Then
Cells(2, 3).Value = Cells(2, 3) + Target.Value
Else
Cells(2, 2).Value = Target.Value
Cells(2, 3).Value = Target.Value
End If
Cells(2, 4).Value = Date
rw = Sheets(2).Range("a65536").End(xlUp).Row
Sheets(2).Cells(rw + 1, 1).Value = Date: Sheets(2).Cells(rw + 1, 2).Value = Target.Value
End If
End Sub

補足

ありがとうございます。回答どうりやってみました。確かにそのとうりになりました。

実際使うには、セルの場所が変わったり（例えば、A2セル以外が本日の件数になったり）
複数行にわたって、同じ集計が必要になる（例えば、Aさんの件数・Bさんの件数・・・）のですが
どのようにこのコードを変えればいいのでしょうか？

また、日付が必要ではない場合はどうしたらいいのかも教えてください。

自分でも出来るだけやってみますが、限界があるとおもいますので。
VBAほとんどわからないので。

よろしくお願いします。

回答No.3

shinkami

見当違いかも分かりませんが、入力セル（日付）を先に１年分用意するのはどうでしょう

A列：日付、B列：本日の件数、C列：月の累計、D列：期の累計
１行目を項目名として、２行目以降がデータ行期初を１月１日とする
１、セルA2の表示書式を曜日付き（ユーザー定義でge.m.d(aaa)）
＿セルA2をオートフィルで12/31までコピー
２、セルC2の計算式：=IF(B2>0,SUM($B$2:B2),"")
＿セルC2をオートフィルで2/1までコピー
＿セルC33(2/1)の計算式：=IF(B2>0,SUM($B$33:B33),"")
＿セルC33をオートフィルで3/1までコピー
＿…
３、セルD2の計算式：=IF(B2>0,SUM($B$2:B2),"")
＿セルC2をオートフィルで12/31までコピー

以上ですが、土日等データの発生していない日をプリントから外すには表示しないとしてください（削除でない）
入力列はB列だけをロックを外し、シート保護をかけて運用するとバカよけになります。

回答No.2

max4919

単純に解読すると、以下となるような気がしますが・・・。
　　Ａ　　　　　　　　　Ｂ　　　　　　　　Ｃ
本日の件数　　月の累計　　期の累計
　　　　　　　　　　=B1+A2 　　　 =C1+A2
必要に応じて下に延ばせば良いと思います。

要求している答えと違いましたら、すいません。

回答No.1

Turbo415

イマイチ、作りたい表のイメージがわきません。
　　本日の件数　　月の累計　　期の累計
　　　　10　　　　　13　　　　　213
　　　　12　　　　　25　　　　　225
　　　　11　　　　　36　　　　　236
と言うイメージなのでしょうか？
それとも本日の件数を入れる部分、累計を表示する部分は固定で1カ所なのでしょうか？後者だと、前日までの数値はどこに有るのでしょうか？ですから後者のようなイメージの場合は、マクロとワークエリアみたいな物を使わないとむりですよ。前者なら基礎的なExcelの関数です。 質問の意味がイマイチわからないので、的確ではないかもしれませんが。

補足

ご回答ありがとうございます。
表のイメージは後者になります。
どこかに前日までの数値がないと無理ですよね。

マクロを使って作る方法はどんなのでしょうか？

前日までの数値が２か月分３か月分・・・と、ずっと長い列で残ってしまうと困るのですが、矛盾しているでしょうか？

ちなみに、作成した私が使うのではなく、エクセルを知らない不特定の人が簡単に日々入力・作成できるように作らなくてはならないのです。

よろしくお願いします。