プロが教えるわが家の防犯対策術!

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

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

出力したい値
会社コード、品目コード、月度、出荷数
A00000001,AAAAAAA,201410,10←出荷数は同じ会社コード、品目コードの(10月+9月+8月)÷3)
A00000001,AAAAAAA,201409,3.33←出荷数は同じ会社コード、品目コードの(9月+8月+7月)÷3)
A00000001,AAAAAAA,201408,3.33←出荷数は同じ会社コード、品目コードの(8月+7月+6月)÷3)
B00000002,BBBBBBB,201408,3.33←出荷数は同じ会社コード、品目コードの(8月+7月+6月)÷3)

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

A 回答 (17件中1~10件)

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



私の最終回答で、出荷数=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を全ての月に加えています。

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

2、ユニオンクエリーで集荷数=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 [会社コード], [品目コード], [月度];

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

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

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

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

ありがとうございます

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

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

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

お礼日時:2014/10/25 12:52

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



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

商品名|月次出庫数|月次期首在庫|月次期末在庫|月次在庫回転率|月次在庫回転期間
AA1|__100|___100|_____0|__2.000|___0.5ヶ月
AA2|___50|___100|____50|__0.666|___1.5ヶ月
AA3|____0|___100|___100|__0.000|________

商品AA1は、月次平均在庫数は50。
これを一ヶ月で2倍の100個出庫。
ですから、在庫回転期間は0.5ヶ月。

商品AA2は、月次平均在庫数は75。
これを一ヶ月で50個出庫。
ですから、在庫を出庫しきるにはもう半月を必要とします。
よって、在庫回転期間は1.5ヶ月。

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

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

商品名|7月度在庫回転期間|8月度在庫回転期間|9月度在庫回転期間|平均在庫回転期間|
AA1|____1/2ヶ月|____1/2ヶ月|____1/2ヶ月|___1/2ヶ月|

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

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

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

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

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

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

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

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

ID=1
在庫回転台帳_ID=1
月次=201405
前々月出庫数=XXX
前月出庫数=XXX
当月出庫数=XXX
月次在庫回転期間=XXX

あくまでも雛形。私には、月次在庫回転期間の求め方がよく理解できていませんので当てずっぽうで書いています。

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

>要は、[会社コード], [品目コード], [月度],[集荷数]=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)
    • good
    • 0

INSERT INTO 出荷履歴


SELECT *
FROM クエリ19;

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

思うに、やられていることはExcel的な処理にAccessを利用しているだけ。データも[出荷履歴]というより[月次出荷合計明細票]の類。だったら、INSERT文で解決も手です。ダミー行を追加しても用が済んだら削除すれば済む話。
「accessで移動平均する方法」の回答画像14
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2014/10/24 22:03

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



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

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

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

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



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

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

>本当に移動平均ですか?
>単なる過去3ヶ月間の平均ですか?

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

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



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

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

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

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



テーブル名を「★★」と仮定します。
連続した月度生成用のテーブル「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


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

1、[会社コード]+[品目コード]+[月度]


  の組合せで全ての月に出荷数=0を求めるクエリを作成。
2、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;
「accessで移動平均する方法」の回答画像9
    • good
    • 0

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



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

> 追加したいデータは
>
> 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は出力しないのですか。

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


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

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

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

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

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

10月末の在庫数÷(10月の出荷数+9月の出荷数+8月の出荷数)÷3=10月の在庫回転期間
9月末の在庫数÷(9月の出荷数+8月の出荷数+7月の出荷数)÷3=9月の在庫回転期間
8月末の在庫数÷(8月の出荷数+7月の出荷数+6月の出荷数)÷3=8月の在庫回転期間

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

お礼日時:2014/10/24 21:56

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

関連するカテゴリからQ&Aを探す