[[20100908114823]]　『統計関数で一次関数の傾きを求める方法』（Orange）　ページの最後に飛ぶ

『統計関数で一次関数の傾きを求める方法』（Orange）

 初めて質問します。Windows XP/ Excel 2003です。

 過去3ヶ月の日別販売数実績（累計）を用いて、「微分係数」を求めてほしいと言われています。
 「LINEST関数」を使うこと、という条件が出されています。
 「微分係数」とは、指示者にきいたところ、「近似直線（一次関数）の傾き」のことらしいです。
 （一般的な用語としてそれが正しいのかはわかりません。要はy=ax+bの「a」であればいいそうです。）
 しかし何せ当方超文系にて、高校数学でも統計学や微積分は習ったことがありません。
 自分なりにやってみたのですが、合っているか自信がないので、検証いただければと思います。

 質問は2つあります。その前に、まずはシートのつくりは下記のとおりです。
 ダウンロードデータがもとになっており、シート全体の表示形式は「標準」になっています。

 	A	B	C	D	…	CO	CP	CQ		…	CU	
 1	1	2	3	4	…	93	94	95		…	99
 2	No/暦日	100601	100602	100603	…	100831	(空白)	販売開始日	…	微分係数	
 3	12345	8	23	28	…	580		質問２の式	…	質問１の式
 4	12346	0	0	3	…	1253
 5	12347	0	0	0	…	0

 ※ 1行目には単純に1から連番が振ってあります（社内ルール？でこのようなファイルが多いです）。
 ※ A列は品番です。
 ※ 2行目は過去3ヶ月間の暦日です。日付の入っている最終列は可変で、
 　 最大93列目（31日の月×2＋30日の月×1＝92日）ということになります。
 ※ 3行目以下は、5000行前後の各商品の販売数が、B列の1日目を起点として「累計」で入っています。
 ※ CQ列からCU列までを作業列や計算に使っています。

 ●質問１：関数の使い方の違いと、そもそも関数の使用方法が合っているかどうか

http://atiboh.sub.jp/t07kaikibunseki.html　

 「ど素人の「Excel 回帰分析」表の見方 (単回帰分析)」

 ヘルプや↑こちらのサイトを参考に、セルCU3にLINEST関数を入力しました。
 2パターンの式と、さらに検算のつもり（一次関数なら別にSLOPEでもいいのでは？との考え）で
 SLOPE関数を含め、全3パターンの式で試してみました。小数点以下は切り上げています。
 エラーやイレギュラーは別の作業列で処理しているので、下記では考慮にいれていません。
 なお便宜上、期間は最大の、B列からCO列まで入っているとします。

 (1) {=ROUNDUP((LINEST($B3:$CO3,$B$1:$CO$1,1,1)),0)}

 (2) =ROUNDUP(INDEX(LINEST($B3:$CO3,$B$1:$CO$1,1,1)*1,1),0)

 (3) =ROUNDUP(SLOPE($B3:$CO3,$B$1:$CO$1),0)

 ※ x＝日にち、y＝販売数累計、b（切片）＝ここでは考慮しない

 この(1)(2)(3)、エラーの場合のエラー種類が若干違う以外、数値が返るところでは
 とりあえず同じ値が返されるのですが、これらは全く同じ意味の処理になるんでしょうか？　
 たまたま同じ値になってしまっているだけでしょうか？
 もし違いがあるとしたらその違いは何でしょうか？　
 (1)(2)はLINESTの意味を理解しないまま見よう見まねでやっているので、自信ありません。
 これで合っているのか、また、どれを使うのが最も適切なのかアドバイスをお願いします。

 ●質問２：販売開始日のエラーについて

 質問１では、わかりやすくするために期間を固定して記載しましたが、実際は商品によって、
 たとえば、その3ヶ月の期間内に新発売になった商品などは、実際の販売期間が短くなります。
 例を挙げると、表の4行目の場合は、販売期間はD列からCO列までとなります。
 （販売開始日はずれますが、最終日は、累計データのため最終列で固定です。）

 販売開始日のセル番地を求めるため、下記の過去ログを見て、CQ列に数式を入れてみました。
 実際に使用するCU列でのLINEST関数には、INDIRECTを使ってこれらのセル番地を代入しています。

[[20060714155649]]　『どの月から支払いが始まるかを別のセルに表示したい』

 {=ADDRESS(ROW(),MIN(IF($B3:$CO3>0,COLUMN($B$1:$CO$1),"")),1,1)}

 真の場合はこれで正しいセル番地が返されるのですが、
 偽の場合（表の5行目のように販売がなく最終列がゼロ、または返品等で最終列がマイナス）に
 空白セルが返らず、#VALUEエラーになってしまいます。
 ヘルプや「計算の過程を表示」機能をみても、いまいち理由がわかりませんでした。
 何か式に不備があると思うのですが、改善案や、別の良い方法があればご教授願います。

 以上です。長くてすみません。うまく伝わってますでしょうか…。
 いずれも当方が統計関数や配列を理解しきれていないため、
 式の訂正や説明をお願いする質問となるかと思います。よろしくお願いします。

 >しかし何せ当方超文系にて、
 私も大差ありませんが、レスがなかなか付かないようなので、以下、呼び水になればベースです。

 >「微分係数」とは、指示者にきいたところ、「近似直線（一次関数）の傾き」のことらしいです。
 >（一般的な用語としてそれが正しいのかはわかりません。要はy=ax+bの「a」であればいいそうです。）
 ネットで 「微分係数」を調べてみましたが、
 　微分係数における『y=ax+bの「a」』と
 　今回求める販売実績のグラフの『y=ax+bの「a」』
 とは別物と云う気がします。

 今回のは、実績グラフが近似的にその一次式で表現できるかどうか、と云うのに対して、
 微分係数は、当該実績グラフのある地点における接線がその一次式で表現できるかどうか、
 と云うものの様な気がします。

 一次式の一点における接線の傾きなんて一定ですから、
 微分係数を一次式で表した場合、その傾き「a」はゼロでしかないと思います。『 y=0x+b (一定) 』
 しかし、それではナンセンス過ぎるので『微分係数』と云う言葉が間違っているのではないでしょうか？

 > (1) {=ROUNDUP((LINEST($B3:$CO3,$B$1:$CO$1,1,1)),0)}
 > (2) =ROUNDUP(INDEX(LINEST($B3:$CO3,$B$1:$CO$1,1,1)*1,1),0)
 > (3) =ROUNDUP(SLOPE($B3:$CO3,$B$1:$CO$1),0)
 > この(1)(2)(3)、エラーの場合のエラー種類が若干違う以外、数値が返るところでは
 > とりあえず同じ値が返されるのですが、これらは全く同じ意味の処理になるんでしょうか？
 (1)と(2)は同じだと思います。
 (3)も理屈は同じだとは思いますが、ヘルプに以下の記述があるので、
   「LINEST関数」を使わなければならない条件があるなら、君子危うきに近寄らずです。

 ヘルプ→『SLOPE 関数および INTERCEPT 関数と、LINEST 関数とでは、基になっているアルゴリズムが異なります。
   　　　　アルゴリズムの違いにより、データに不確定さや共線性が存在した場合に、異なる結果が得られる場合があります。』

 ちなみに、カッコが一組み多いのでは？
 　　　　　　　↓　　　　　　　　　　　　　　　↓
  (1) {=ROUNDUP((LINEST($B3:$CO3,$B$1:$CO$1,1,1)),0)}
  (2) =ROUNDUP(INDEX(LINEST($B3:$CO3,$B$1:$CO$1,1,1)*1,1),0)
 　　　　　　　　　　　　　　　　　　　　　　　　　 ↑
 　　　　　　　　　　　　　　　　　　　　　　　　　*1は要らないのでは？

 > {=ADDRESS(ROW(),MIN(IF($B3:$CO3>0,COLUMN($B$1:$CO$1),"")),1,1)}
 > 真の場合はこれで正しいセル番地が返されるのですが、
 > 偽の場合（表の5行目のように販売がなく最終列がゼロ、または返品等で最終列がマイナス）に
 > 空白セルが返らず、#VALUEエラーになってしまいます
 その式は空白が返るようにはなっていません。
 MIN関数の引数に空白が入るようになっているだけです。

 こんなのでどうでしょうか？
 　↓
 {=IF(OR(B3:CO3<>0),ADDRESS(ROW(),MIN(IF($B3:$CO3<>0,COLUMN($B$1:$CO$1),"")),1,1),"")}

 (半平太) 2010/09/09 11:23

 半平太様

 ありがとうございます!! 丸一日近くレスがつかなかったのでどうしようかと思っていました。

 SLOPEとLINESTはアルゴリズム？が違うのですね。
 ならば、やはり見た目が同じでも、指示された通りLINESTを使うことにします。
 もやもやがすっきりしました。(1)(2)とも同じというのも安心しました。
 上司にINDEXの意味をきかれると説明が面倒なので、配列を採用することにします。

 >  ちなみに、カッコが一組み多いのでは？
 >　　　　　　　↓　　　　　　　　　　　　　　　↓
 > (1) {=ROUNDUP((LINEST($B3:$CO3,$B$1:$CO$1,1,1)),0)}
 > (2) =ROUNDUP(INDEX(LINEST($B3:$CO3,$B$1:$CO$1,1,1)*1,1),0)
 >　　　　　　　　　　　　　　　　　　　　　　　　　 ↑
 >　　　　　　　　　　　　　　　　　　　　　　　　　*1は要らないのでは？

 これらはいずれもその通りでした。お恥ずかしい限りです…。

 >{=IF(OR(B3:CO3<>0),ADDRESS(ROW(),MIN(IF($B3:$CO3<>0,COLUMN($B$1:$CO$1),"")),1,1),"")}

 こちら大変助かりました。<>0の使い方は、慣れないとまだなかなか自分では思いつきません。
 最終的には、いちばん最後の""の部分を応用させていただたいて、空白ではなく、
 最終列を求めた作業列CR列（実際は最終列も月によって可変なので…）を入れたところ、
 LINEST関数のエラーも消えたため、エラー調整用の作業列も不要になりました。嬉しい副次的効果です♪

 完成形はこんな感じです！ 本当に助かりました。ありがとうございました。
 {=IF(OR($B3:$CO3<>0),ADDRESS(ROW(),MIN(IF($B3:$CO3<>0,COLUMN($B$1:$CO$1),"")),1,1),$CR3)}
 {=ROUNDUP(LINEST(INDIRECT($CQ3):INDIRECT($CR3),INDIRECT($CS3):INDIRECT($CT3),1,1),0)}

 ところで、

 > 『微分係数』と云う言葉が間違っているのではないでしょうか？

 おっしゃるとおりで、私も正直そう思ってます。指示者が、どこからかきいてきた難しそうな
 関数と単語が気に入って使っているだけなのではないかと…（笑）。

 （Orange）14:25

コメント返信：

[ 一覧(最新更新順) ]

[[20100908114823]] 『統計関数で一次関数の傾きを求める方法』（Orange） ページの最後に飛ぶ

[[20100908114823]]　『統計関数で一次関数の傾きを求める方法』（Orange）　ページの最後に飛ぶ