accessで移動平均する方法

質問

会社コード、品目コード、月度、出荷数の入った明細テーブルがあります
これを過去３ヶ月で出荷数を移動平均したいのです

例 元データ
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201410,10
A00000001,AAAAAAA,201408,10
B00000002,BBBBBBB,201408,10

出力したい値
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10←出荷数は同じ会社コード、品目コードの（１０月＋９月＋８月）÷３）
A00000001,AAAAAAA,201409,3.33←出荷数は同じ会社コード、品目コードの（９月＋８月＋７月）÷３）
A00000001,AAAAAAA,201408,3.33←出荷数は同じ会社コード、品目コードの（８月＋７月＋６月）÷３）
B00000002,BBBBBBB,201408,3.33←出荷数は同じ会社コード、品目コードの（８月＋７月＋６月）÷３）

このような事がしたいのですがどのようにしたら実現できるでしょうか
月度で移動平均なんて良く使いそうな気がしますが・・簡単なようで難しく悩んでおります
どうかよろしくお願い致します

回答No.15

山田 太郎（@f_a_007） · ベストアンサー

＞その計算の元ネタとなる過去３ヶ月間の移動平均出荷数が求めたかったのです。

私の最終回答で、出荷数=0の月も含めて求められていると思いますよ。

7月---10
8月---10
9月---0 ----->(10+10+0)/3・・・(7月+8月+9月)/3
10月--10 ---->(10+0+10)/3・・・(8月+9月+10月)/3
11月--10 ---->(0+10+10)/3・・・(9月+10月+11月)/3

添付図では、このように計算しています。こういう計算結果を9月にも出力するには、9月にも出荷数=0の行も必要。その為に、ユニオンクエリで求めた出荷数=0を全ての月に加えています。

１、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加してもDistinct句を使えば出力では重複することはありません。

２、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加しても、移動平均出荷数の計算結果は同じです。10+0=10ですから。

添付図の結果は、[会社コード], [品目コード], [月度],[集荷数]=0を追加した後に、次のSQL文を実行したもの。

SELECT DISTINCT [会社コード], [品目コード], [月度], Format(Rounds(DSum("出荷数", "出荷履歴", WHERE 会社コード='" & [会社コード] & "' AND 品目コード='" & [品目コード] & "' AND 月度 BETWEEN ' MoveMonth([月度],-3) & "' AND '" & [月度] & "'")/3,0,2),"0.00") AS 平均出荷数
FROM 出荷履歴
WHERE 月度 BETWEEN "201408" AND "201410"
ORDER BY [会社コード], [品目コード], [月度];

動かないとすれば、単なる転記ミスです。

１、DISTINCT句を使っていること。
　　これで、ダミー行を出力から追放することができます。
２、表示する期間を指定していること。
　　表示する期間は、まあ、実際に運用する際に全期間の結果を求めないだろうと考えたからに過ぎません。

なお、MoveMonth([月度],-3) でBETWENNを簡略化、あるいはRounds()で四捨五入していること。これらは、枝葉の問題。

要は、[会社コード], [品目コード], [月度],[集荷数]=0を追加すればよいだけと思いますが・・・。

お礼

ありがとうございます

＞＞１、ユニオンクエリーで集荷数=0を全ての組み合わせを全ての月に追加してもDistinct句を使えば出力では重複することはありません

この考え方は目からうろこです
そうですね、足りない月のレコードだけを追加する事にこだわって難しく考えていたかもしれません
全部の月に対して出荷数０のレコードを追加した後で
select 会社コード、品目コード、sum(出荷数） from hoge group by 会社コード、品目コード

グルーピングしてあげればもともとレコードのない月は出荷数０のレコードとして出力されますね

回答No.17

山田 太郎（@f_a_007）

【疑問】今のアプローチは正しいのか？

＞毎月の在庫回転期間を求めたいのです。

商品名｜月次出庫数｜月次期首在庫｜月次期末在庫｜月次在庫回転率｜月次在庫回転期間
ＡＡ１｜＿＿１００｜＿＿＿１００｜＿＿＿＿＿０｜＿＿２．０００｜＿＿＿０．５ヶ月
ＡＡ２｜＿＿＿５０｜＿＿＿１００｜＿＿＿＿５０｜＿＿０．６６６｜＿＿＿１．５ヶ月
ＡＡ３｜＿＿＿＿０｜＿＿＿１００｜＿＿＿１００｜＿＿０．０００｜＿＿＿＿＿＿＿＿

商品ＡＡ１は、月次平均在庫数は５０。
これを一ヶ月で２倍の１００個出庫。
ですから、在庫回転期間は０．５ヶ月。

商品ＡＡ２は、月次平均在庫数は７５。
これを一ヶ月で５０個出庫。
ですから、在庫を出庫しきるにはもう半月を必要とします。
よって、在庫回転期間は１．５ヶ月。

敢えて、単月で「在庫回転期間]を出すとすれば上図のようです。

この結果を、更に、次のようにレポートしたい。

商品名｜７月度在庫回転期間｜８月度在庫回転期間｜９月度在庫回転期間｜平均在庫回転期間｜
ＡＡ１｜＿＿＿＿１／２ヶ月｜＿＿＿＿１／２ヶ月｜＿＿＿＿１／２ヶ月｜＿＿＿１／２ヶ月｜

ならば、
課題は、単月での「在庫回転期間]を求めるだけで事足りるのではと思いますよ。「商品別在庫回転期間推移表」は、単に、レポート出力の手の問題。

【在庫回転期間の定義は横に置いて】

要は、「商品別在庫回転期間推移表」をレポートすれば事足りる訳ですので・・・

<<テーブル「在庫回転台帳」>>

ID=1
会社コード=XXXX
商品コード=XXXX

<<テーブル「在庫回転明細」>>

ID=1
在庫回転台帳_ID=1
月次=201405
月次月次出庫数=100
月次期首在庫=100
月次期末在庫=0
月次在庫回転率=2.00

※列[月次在庫回転期間]は、月次在庫回転率の逆数なので必要ありません。
※もちろん、質問者のいう「在庫回転率」でも同じ設計ですよ。

ID=1
在庫回転台帳_ID=1
月次=201405
前々月出庫数=XXX
前月出庫数=XXX
当月出庫数=XXX
月次在庫回転期間=XXX
↑
あくまでも雛形。私には、月次在庫回転期間の求め方がよく理解できていませんので当てずっぽうで書いています。

という二つのテーブルを生成すれば簡単に目的は達成できるかと思いますよ。「商品別在庫回転期間推移表」の出力は小一時間で終了します。ですから、レポート出力のことは一切考える必要はありません。

回答No.16

山田 太郎（@f_a_007）

＞要は、[会社コード], [品目コード], [月度],[集荷数]=0を追加すればよいだけと思いますが・・・。

もちろん、追加するユニオンは集計期間によって適宜作成するひつようがあります。

＜例＞

201406・・・201404,201405に登場する全てetc.
201407・・・201405,201406に登場する全てetc.
201408・・・201406,201407に登場する全てetc.
201409・・・201407,201408に登場する全てetc.
201410・・・201409,201410に登場する全てetc.

なお、

201404に登場したのは201405,201406にも発生させる
201405に登場したのは201406,201407にも発生させる
201406に登場したのは201407,201408にも発生させる

という考えもあります。

一体、どれが最も欲するものか？それは、実践をもって確認して下さい。それによっては、独自のユニオンになるかも知れません。要は、そのアイデアにのみ着目してもらえれば幸いです。

PS, MoveMonyh(XXXX,-3)→MoveMonyh(XXXX,-2)

回答No.14

山田 太郎（@f_a_007）

INSERT INTO 出荷履歴
SELECT *
FROM クエリ19;

これをやりたい意味が判りました。やっぱり移動平均を出したかったということでしょう。

思うに、やられていることはExcel的な処理にAccessを利用しているだけ。データも[出荷履歴]というより[月次出荷合計明細票]の類。だったら、INSERT文で解決も手です。ダミー行を追加しても用が済んだら削除すれば済む話。

お礼

ありがとうございます
説明不足な点があり混乱させてしまったようです
やりたかった事はＮＯ８さんのところに書いたのですが
在庫回転期間を求めたかったのです
その計算の元ネタとなる過去３ヶ月間の移動平均出荷数が求めたかったのです
入手できるデータは他に月末在庫データ

会社コード、品目コード、月度、数量の項目です
この月末在庫データと出荷履歴データを使って各月の在庫回転期間をＮＯ８さんのところに書いた
計算式で求めたかったのが最終的な目的です
最初にこれを説明していればもっと簡単な方法があったかもしれないです
ごめんなさい

回答No.13

hatena1989

データがない月度も表示させたいということですね。

表示したい月度の格納されたマスターテーブルを作成して、それと明細テーブルをクエリで外部結合すれば表示させることができます。

会社マスター、品目マスターテーブルも必要になりますが、ありますよね。

具体的な方法の提示は、現状のマスターテーブル等の名前、フィールド構成等が不明なのでできませんので、
とりあえずやってみて、できなかったら、上記の詳細の情報を追加して、どこまでできて、どこで躓いたのか提示して、返信してください。

回答No.12

山田 太郎（@f_a_007）

【補足の補足】移動平均法と出荷日

A商品の出荷は6、7月のみ。
B商品の出荷は7、8月のみ。
C商品の出荷は8、9月のみ。
D商品の出荷は10月のみ。

現実には、こういう状況が一般的。だとすれば、各々の商品の3ヶ月の移動平均を出力しても実に評価が難しいデータになるかと思いますよ。回答１で問うたのは、

＞本当に移動平均ですか？
＞単なる過去３ヶ月間の平均ですか？

このこと。ちょーっと、最初から喉にトゲがひかかったままなので補足して確認しておきます。

回答No.11

山田 太郎（@f_a_007）

【疑問】移動平均法と出荷日との関係

提示されているデータの出荷日は月で管理されています。つまり、過去３ヶ月を正確に指定できません。11月01日の伝票があった場合には09/01～11/01の約2ヶ月の平均出荷数になります。先の回答で、

＞11月初旬の処理に問題があって・・・

と書いていますが、その意味を補足しておきます。出荷日が日付で管理されていればと思っています。

回答No.10

30246kiku

できるというだけで、使えるのかわかりませんけど

テーブル名を「★★」と仮定します。
連続した月度生成用のテーブル「T月度」を新設します。
フィールドは「月度」（長整数）のみ

標準モジュールに以下を記述しておきます。
やっている内容は、テーブル「★★」内の最小月度、最大月度を求めて
その範囲の連続した yyyymm を作成します

Public Function myInit() As Boolean
　　Dim rs As New ADODB.Recordset
　　Dim sSql As String
　　Dim dtS As Date, dtE As Date

　　sSql = "DELETE * FROM T月度;"
　　CurrentProject.Connection.Execute sSql

　　rs.Source = "SELECT Min(月度), Max(月度) FROM ★★;"
　　rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
　　If (Not rs.EOF) Then
　　　　dtS = CDate(Format(rs(0), "0000/00"))
　　　　dtE = CDate(Format(rs(1), "0000/00"))
　　　　rs.Close

　　　　rs.Open "T月度", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
　　　　While (dtS <= dtE)
　　　　　　rs.AddNew
　　　　　　rs(0) = Int(Format(dtS, "yyyymm"))
　　　　　　rs.Update
　　　　　　dtS = DateAdd("m", 1, dtS)
　　　　Wend
　　　　rs.Close
　　End If
　　myInit = True
End Function

この関数をSQLから呼び出して利用します。
以下の内容をSQLビューに記述した場合と結果は

SELECT Q1.会社コード, Q1.品目コード, Q1.月度, Round(Sum(Q2.出荷数)/3,2) AS 平均 FROM
(SELECT * FROM
(SELECT DISTINCT 会社コード, 品目コード FROM ★★), T月度) AS Q1
, ★★ AS Q2
WHERE myInit() AND
Q1.会社コード=Q2.会社コード AND Q1.品目コード=Q2.品目コード AND
Q2.月度 IN (SELECT TOP 3 月度 FROM T月度 WHERE 月度<=Q1.月度 ORDER BY 月度 DESC)
GROUP BY Q1.会社コード, Q1.品目コード, Q1.月度;

会社コード　品目コード　月度　　平均
A00000001　　AAAAAAA　　201408　　3.33
A00000001　　AAAAAAA　　201409　　3.33
A00000001　　AAAAAAA　　201410　　10
B00000002　　BBBBBBB　　201408　　3.33
B00000002　　BBBBBBB　　201409　　3.33
B00000002　　BBBBBBB　　201410　　3.33

B00000002　　BBBBBBB　の 201409 / 201410 は要らないのなら

SELECT Q1.会社コード, Q1.品目コード, Q1.月度, Round(Sum(Q2.出荷数)/3,2) AS 平均 FROM
(SELECT * FROM
(SELECT DISTINCT 会社コード, 品目コード FROM ★★) AS T1, T月度 AS T2
WHERE T2.月度<=(SELECT Max(月度) FROM ★★ WHERE 会社コード=T1.会社コード AND 品目コード=T1.品目コード)) AS Q1
, ★★ AS Q2
WHERE myInit() AND
Q1.会社コード=Q2.会社コード AND Q1.品目コード=Q2.品目コード AND
Q2.月度 IN (SELECT TOP 3 月度 FROM T月度 WHERE 月度<=Q1.月度 ORDER BY 月度 DESC)
GROUP BY Q1.会社コード, Q1.品目コード, Q1.月度;

会社コード　品目コード　月度　　平均
A00000001　　AAAAAAA　　201408　　3.33
A00000001　　AAAAAAA　　201409　　3.33
A00000001　　AAAAAAA　　201410　　10
B00000002　　BBBBBBB　　201408　　3.33

※ サンプルでできたというだけですので・・・

回答No.9

山田 太郎（@f_a_007）

１、[会社コード]+[品目コード]+[月度]
　　の組合せで全ての月に出荷数=0を求めるクエリを作成。
２、INSERT INTO XXX SELECT XXX 同クエリ
　　を実行する。

以上で確かに現象的には目的を達成出来ますが・・・。これは、ちょっと非現実的かと思いますね。テーブル[出荷履歴]には、主キーもあるでしょう。また、他のテーブルとのリンクも張ってあるでしょう。そこら辺りに十分に配慮してとなると、INSERT INTO 出荷履歴 SELECT * FROM クエリ19;と単純にいけるものか？VBAを推す理由です。

【出荷数=0を発生させるクエリ】

SELECT DISTINCT
　　[会社コード],
　　[品目コード],
　　"201408" AS [月度],
　　0 AS [出荷数]
FROM 出荷履歴 WHERE 月度 BETWEEN "201408" AND "201410"

UNION

SELECT DISTINCT
　　[会社コード],
　　[品目コード],
　　"201409" AS [月度],
　　0 AS [出荷数]
FROM 出荷履歴 WHERE 月度 BETWEEN "201408" AND "201410"

UNION

SELECT DISTINCT
　　[会社コード],
　　[品目コード],
　　"201410" AS [月度],
　　0 AS [出荷数]
FROM 出荷履歴 WHERE 月度 BETWEEN "201408" AND "201410";

【INSERT文】

INSERT INTO 出荷履歴
SELECT *
FROM クエリ19;

回答No.8

hatena1989

データのない月度も出力したいということですね。

回答の前にもう少し確認させてください。

> 追加したいデータは
>
> A00000001,AAAAAAA,201409,0
> B00000002,BBBBBBB,201410,0
> B00000002,BBBBBBB,201409,0

A00000001,BBBBBB,201410,0
A00000001,BBBBBB,201409,0
A00000001,BBBBBB,201408,0

は出力しなくていいのですか。
会社A00000001の取り扱う品目はAAAAAAだけなんでしょうか。

同様に、B00000002 の場合はAAAAAAは出力しないのですか。

会社のマスターテーブルは存在しますか。
また、それぞれの会社の取り扱い品目の情報はどのように管理してますか。

> これを一気に追加することが追加クエリでできるでしょうか？

追加クエリでデータを追加しなくても、マスターテーブルがあれば外部結合を使うことで実現はできます。

ただ、具体的な方法は、上記のマスターテーブルや取り扱い品目のテーブルの構成がどうなっいてるかが判明しないと提示できないですね。

お礼

コメントありがとうございます会社のマスターテーブルは存在しますが
自分はそのデータを入手する権限がありません

説明不足で申し訳ありません
何がしたいかというと毎月の在庫回転期間を求めたいのです
月末在庫のデータと出荷履歴のデータは入手できます

１０月末の在庫数÷（１０月の出荷数＋９月の出荷数＋８月の出荷数）÷３＝１０月の在庫回転期間
９月末の在庫数÷（９月の出荷数＋８月の出荷数＋７月の出荷数）÷３＝９月の在庫回転期間
８月末の在庫数÷（８月の出荷数＋７月の出荷数＋６月の出荷数）÷３＝８月の在庫回転期間

上記の値を会社コード、品目コード別に出力したかったのです
なので
月末在庫は存在しても出荷した事が一度もない不良在庫の物もありますので
９月に出荷した履歴がなくても（９月の出荷数＋８月の出荷数＋７月の出荷数）÷３で
３ヶ月の平均出荷数は求められると思います
なので出荷した履歴がない月も出荷数の平均を求めたかったのです
Vlookupで在庫と出荷平均を後で結合したデータを作って計算したかったので
出荷０の月もデータを出したいという事です

回答No.7

山田 太郎（@f_a_007）

【お詫びと補足】

補足質問の意味をやっと理解できました。つまり。添付図のような出力で対象月を全て出力したいということですね。私なら、VBAで期間を設定して出荷数=0の月があればInsert文を実行します。

理由：SQL文を考えるのが面倒臭いから！

[会社コード+品目コード+月次]で対象月に発生しているの全てに追加しないと目的は達成できないでしょう。ということは、添付図のクエリの結果を配列に取り込んで欠落月を挿入するというやり方が一番最初に思い付きます。ですから、それをVBAにて記述します。