エクセルのデータ集計

質問

エクセルでデータの集計についての質問です。
商品のデータベースで「商品名」「注文日」「納品日」が入っています。
「注文日」から「納品日」までが入荷を待ち状態ということになります。
ここで、日ごとの納品待ち商品合計数の年平均を取るにはどうすれば良いでしょうか。
エクセルorアクセスで集計する方法を教えてください！！
よろしくお願いいたします。

回答No.5

happypoint · ベストアンサー

#2,#4です。

コードの内容はだいたいお分かりになれますか？

ちょっと解説しますと、
'納品待ち状態の判定と、納品待ち個数カウント
のあたりが、データの各行において、
「入荷待ち状態」であるかどうかを判断しています。
（もちろん1月1日から12月31日までの日付ごとに判断しています。）

この、If～End if文の後ろに、
商品のカテゴリが何であるかを判別する文
（Select case）を書き込んで、
カテゴリ別に処理を分岐してください。

あとは、myValueを配列にするか、別の変数を宣言して
カテゴリ別に分岐した処理の中で、
変数に+1してカウントすれば実現できます。

ここまでくれば、
あとは完成したも同然ですよ!!

それではがんばってください!!

お礼

大変助かりました。且つ、勉強になりました！
これまでも、VBAを覚えたいと思い、自分でかじったりしていたのですが、変数や条件分岐がうまくできずにいました。今回、実例を作っていただいて本当に良い勉強をさせていただきました。
頑張って使っていきたいと思います。どうもありがとうございました！

回答No.4

happypoint

#2です。

こんな感じで、いかがでしょうか。

A列に注文日、B列に納品日があるものとして、
2行目から100行目に1年分のデータが入力されているものと仮定して書いてみました。

納品待ち個数の集計結果が、10列目に出力されます。
あとは、これを元に合計や最大最小を求めてください。

なお、Const・・・の行は、実情に講じて、変更してください。（計算開始日、最終データ行など）

Option Explicit

Sub test()

'定義
Const intYearDays As Integer = 365 '1年間の日数
Const strStartDay As Date = "2003/1/1" '計算する年の初日を指定
Const lngStartRow As Long = 2 '最初のデータのある行番号（見出し行をのぞく）
Const lngEndRow As Long = 100 '最終データのある行番号
Const lngCol1 As Long = 1 '注文日の列番号（例:A列は1）
Const lngCol2 As Long = 2 '納品日の列番号（例:B列は2）
Const lngOutputCol As Long = 10 '結果を出力する列番号（空いた列を指定）

Dim i As Long
Dim lngRow As Long
Dim myDate As Date
Dim myValue As Long

'処理開始
myDate = strStartDay

'1日ずつなめる処理
For i = 1 To intYearDays

　'全データをなめる処理
　For lngRow = lngStartRow To lngEndRow
　　'納品待ち状態の判定と、納品待ち個数カウント
　　If (Cells(lngRow, lngCol1).Value <= myDate) And (Cells(lngRow, lngCol2).Value > myDate) Then
　　　myValue = myValue + 1
　　End If
　Next lngRow

　'結果の書き込み
　Cells(i, lngOutputCol).Value = myDate '日付見出し
　Cells(i, lngOutputCol + 1).Value = myValue 'カウントした個数

　'後処理
　myDate = myDate + 1 '次の日へ
　myValue = 0 '初期化

Next i

End Sub

お礼

どうもありがとうございます！
作っていただいた仮定にデータを合わせたところ、うまく集計できました。
これから、ファイルに則して書き換えて使わせていただきます！！
ここで、もう１つだけ追加で質問させてください。
C列に商品のカテゴリーが入っているとして（20種類程度）、そのカテゴリー別に集計させるにはどのようにすれば良いでしょうか。（11列目から右側へカテゴリー別に結果を表示させる。）
お手数かけて済みませんが、よろしくお願いいたします。

回答No.3

imogasi

私も＃１の方のおっしゃっている方法でやります。しかし
それほど単純ではないのでは。下記のような場合分けが要ります。
日曜・休業日をカウントしないとなると、更に面倒です。
下記は日数を計算することを意味しています。
納品日－注文日とするか納品日－注文日＋１とするかは
業界・会社慣習で決めるのでしょう。
　　　　┌－前年－－－－－読み飛ばし（処理対象外
　　　　｜　　　　　　　
　　　　｜　　　　　　　　　┌－前年－1/1から納品日
　　　　｜　　　　　　　　　｜　　　　　　　　　　　
　　　　├－今年－－注文日－｜
　　　　｜　　　　　　　　　｜　
　　　　｜　　　　　　　　　└－今年（通常＝
　　　　｜　　　　　　　　　　　　　納品日－注文日
納品日－｜
　　　　｜　　　　　　　　　┌－今年　注文日から12/31　　　　｜　　　　　　　　　｜　
　　　　｜　　　　　　　　　｜　
　　　　└－翌年－－注文日－｜
　　　　　　　　　　　　　　｜
　　　　　　　　　　　　　　└－翌年　（処理対象外
これを全件日数を加え、年間日数で割る。
これを関数式でやるのですかね。ＶＢＡでしょうね。

補足

ご意見ありがとうございます。
「各日の納品待ち数」の平均をとりたいので（最大、最小も）、やはり日ごとの合計が必要だと思います。
今のところ休日は無視。納品日は待機に含めないので+1は無しです。
関数でやるのは難しいでしょうか。（配列関数でうまくカウントできないか考えているのですが。。）
もし、ＶＢＡで簡潔に処理できるようでしたら、ご指導ください。よろしくお願いいたします。

回答No.2

happypoint

1年間の納品待ち商品数の平均をとるには、
毎日の納品待ち商品数データ（365個）が、必要になりますよね。

一番いいのは毎日入力の時点で、
「納品待ち」が何個あるか、逐一カウントして、
数値を毎日記録しておくのがいいんですけど、

これから集計するデータに、
そのようなデータが存在しないのならば、
計算してデータを生成しなければなりません。
関数ですると膨大な数の数式を入力しなければなりません。

やっぱりマクロ処理ですかねえ。

回答No.1

ymmasayan

（納品日－注文日）を総合計して年間日数で割ればいいのではありませんか。