1145512253 Excel or 数学(?) が得意な方に質問です。


Excel2000で、B列に100行くらいデータが入っています。(空行なし)
A列に、1,2,1,2,1,2,1,2…と振りたくて、A1に1、A2に2、A3に1、A4に2と入れて4つとも選択し、
右下の四角いところをダブルクリックしてオートフィルの処理を行いました。

その結果…期待とは全然違う結果になるわけですが、
Excelは何をどう判断してこんな計算をしたのかわかりますか?
なんで0.2ずつ増えていくのでしょう…。

※試しにA1に1、A2に2、A3に1、A4に2、A5に1、A6に2と入れてオートフィルをすると
 さらに表現しづらい数字が振られました。

※ 1,2,1,2,1,2,1,2…と振る方法自体は解決しております。

回答の条件
  • 1人3回まで
  • 登録:2006/04/20 14:50:55
  • 終了:2006/04/20 18:55:58

ベストアンサー

id:aki73ix No.4

aki73ix回答回数5224ベストアンサー獲得回数272006/04/20 17:06:24

ポイント50pt

手計算で法則を見つけました

まず、オートフィルのセルがいくつであってもいいので平均値を求めます

1)1,3,2の場合

2)1,4,7,3の場合

3)2,-4,6,5,1の場合

4)9,-14,16,2,1,0の場合

それぞれ、2、3.75、2、2.333333となります

これが、先ず中央値になります

次に中央値を除く部分(選択セルが偶数の場合は全て)を対象に分子、分母について次の計算を行えば増分が求まります

1)(2-1)×2=2

2)(7-4)×1+(3-1)×3=3+6=9

3)(5-(-4))×2+(1-2)×4=18-4=14

4)(2-16)×1+(1-(-14))×3+(0-9)×5

=-14+45-45=-14

※中央値からの距離をかけたものの和です

分母については次のとおり

1)2×2=4

2)1×1+3×3=10

3)2×2+4×4=20

4)1×1+3×3+5×5=35

つまり増分が 0.5 、0.9 、0.7 、-0.4

先ほどの中央値から計算して空白セルを埋めると

1) 1, 3, 2, 3, 3.5, 4, 4.5, 5 ....

(1.5,[2],2.5,3,3.5 )

2) 1, 4, 7, 3, 6, 6.9, 7.8, 8.7 ....

(2.4,3.3,[(3.75)],4.2,5.1)

3) 2, -4, 6, 5, 1, 4.1, 4.8, 5.5, 6.2....

(0.6, 1.3, [2], 2.7, 3.4)

4) 9, -14, 16, 2, 1, 0, 0.9333, 0.5333, 0.1333, -2.6667......

(3.3333, 2.9333, 2.5333,[(2.3333)], 2.1333, 1.7333, 1.33333)

※ []は中央値 [()]は仮の中央値

のようになります

実際に手動フィルをしてみるとこのとおりになる事がわかります

この計算は、平均二乗誤差ではなく、平均誤差ですね

id:panana

回答ありがとうございます。

aki73ixさんもすごいですね!(さっきから“すごい”以外の言葉が出なくてごめんなさい)

1)1,3,2について、追ってみます。

1,3,2の平均 2

≪増分≫

>中央値を除く部分(選択セルが偶数の場合は全て)を対象に分子、分母について次の計算

選択セルが奇数だから中央値(って真ん中の数字?)を除く部分を対象に…次の計算!?

みた感じ、分子は

「選択セルが奇数だったら」(中央値の右1-中央値の左2)×2、(中央値の右2-中央値の左2)×4…

「選択セルが偶数だったら」(右の中央値-左の中央値)×1、(右の中央値の右-左の中央値の左)×3… でしょうか…?

うまく言えませんでしたが作業はわかりました。

で、今回は(2-1)×2=2

分母が…なんだこれ。2x2って何処から出てきた…。

分子の計算のときにかけた数字を二乗して足せばいいのかな??

ということにして、2×2=4が分母。

ということで、増分は2/4=0.5

>先ほどの中央値から計算して

えっと…真ん中の3のところを2とすると、左が1.5、右が2.5。その次は3、3.5、4…で、

最初にあった3つの数字1,3,2はそのまま残して、4つめから今出てきた3、3.5…を採用。

1,3,2,3,3.5,4…

おおっ!!

ちょっと1,2,1,2、でもやってみます。

(中略)

1,2,1,2

(,,[1.5],,,,)

(1.2,1.4,[1.5],1.6,1.8,2,2.2)

1,2,1,2,2,2.2,…

あ。

理解しました。ありがとうございました。

dungeon-masterさんの中央であると仮定しとか傾きの半分を…とかも理解できました。

2006/04/20 18:14:34

その他の回答(5件)

id:hiro7days No.1

hiro7days回答回数391ベストアンサー獲得回数322006/04/20 15:50:15

ポイント20pt

http://homepage2.nifty.com/kyoutei_yosou/sakusaku/excel/sous...


僕も色々やって規則性を考えましたが、手計算では分かりませんでした。苦笑。

どうやら、上記URLの038に書いてある通り、

最小二乗法で近似しているみたいですね。

id:panana

最小二乗法(笑)

…すみませんあまりに聴きなれない言葉で思わず笑ってしまいました。(←自分に対してですよ!)

最小二乗法について調べてみましたが、「いくつかの観測値をもとにして、誤差の二乗の和を最小にすることにより、最も確からしい値を求める方法。」(YAHOO!辞書「大辞泉」)

もうなんといいますか「最も確からしい値」ってなんなのかと(笑)

回答、ありがとうございました。

どうやら「最小二乗法で近似しているらしい」ということがわかりましたので、

どなたか数学が得意な方、A5が2、A6が2.2になるところまで計算してみてもらえないでしょうか…。

もちろん、途中式ありでお願いします。m(_'_)m

2006/04/20 16:29:56
id:dungeon-master No.2

dungeon-master回答回数571ベストアンサー獲得回数402006/04/20 16:25:16

ポイント20pt

例えば、A1~A5まで1,0,2,5,1と入っていたとします。

A1~A5を選択してオートフィルさせる場合、次のようになって

いると思われます。

まず、平均値を求めます。→1.8

そして、y=A1~A5に対してxが1刻みで増加するような座標の

回帰直線の傾きを(おそらく最小二情報により)求めます。

excelではSlope()が相当します。→0.5

先ほど求めた平均値がA1~A5の中央であるA3の値と仮定し、

そこから傾き0.5で数値を変化させて、オートフィルに適用

します。

選択した領域が偶数、例えばA1~A4だったら中央はA2とA3に

なりますが、平均値+傾きの半分 を A3の値だと仮定すれば

辻褄は合います。

全部仮定ですが、そんなところだと思います。


質問のケースでは

A1~A4で1,2,1,2なら平均値は1.5、傾きは0.2となり、

A3は平均値1.5+傾きの半分0.1=1.6と仮定し、

A5は1.6+2*0.2=2、A7以降0.2ずつ増加となります。

id:panana

回答ありがとうございます。

hiro7daysさんの回答と合わせてなんとなくわかりそうな気がします。がわかりません…。

> まず、平均値を求めます。→1.8

これはOKです。(1+0+2+5+1)/5=1.8

> そして、y=A1~A5に対してxが1刻みで増加するような座標の

> 回帰直線の傾きを(おそらく最小二情報により)求めます。

> excelではSlope()が相当します。→0.5

具体的にどう計算されて0.5なのかがわかりません…

Slope(既知のy,既知のX)で、既知のyはたぶんA1:A5かと思うんですが、

既知のxはどこでしょうか?

> 先ほど求めた平均値がA1~A5の中央であるA3の値と仮定し、

> そこから傾き0.5で数値を変化させて、オートフィルに適用します。

1.8が2だと仮定する??

そこからの「そこ」はどこ??

> 選択した領域が偶数、例えばA1~A4だったら中央はA2とA3に

> なりますが、平均値+傾きの半分 を A3の値だと仮定すれば

> 辻褄は合います。

なんで「平均値+傾きの半分 を A3の値だと仮定」するのかが…。

よろしければもう少しお付き合いください。

2006/04/20 17:13:06
id:kumaimizuki No.3

くまいみずき回答回数614ベストアンサー獲得回数312006/04/20 17:06:13

ポイント30pt

「どうして」という部分は分かりませんでしたが・・・。

実際に法則性を調べてみたら「なんで0.2ずつ増えていくのでしょう…」の部分の計算式が個人的に解明できましたので、回答させていただきます。

ただ「値から式を導き出した」だけなので、「どうしてこのような式が?」という疑問には答えられません。

ご了承ください。



まず、オートフィルする最初のセルの計算式は

(「1」と「2」の繰り返し回数+1)÷(セル数-1)+A1

のようです。

例えば

A1=1

A2=2

の場合、A3は

(「1」と「2」の繰り返し回数:1+1)÷(セル数2-1)+A1:1

=2÷1+1=3

になります。

また、

A1=1

A2=2

A3=1

A4=2

の場合、A5は

(「1」と「2」の繰り返し回数:2+1)÷(セル数4-1)+A1:1

=3÷3+1=2

になります。

さらに

A1=1

A2=2

A3=1

A4=2

A5=1

A6=2

の場合、A7は

(「1」と「2」の繰り返し回数:3+1)÷(セル数6-1)+A1:1

=4÷5+1=1.8

になります。


上記セル以降の加算幅は

オートフィルした最初のセルの数値÷「1」と「2」の繰り返し回数÷A1~オートフィルした最初のセルまでのセル数

のようです。

例えば

A1=1

A2=2

A3=3

の場合、加算幅は

オートフィルした最初のセルの数値:3÷「1」と「2」の繰り返し回数:1÷A1~オートフィルした最初のセルまでのセル数:3

=3÷1÷3=1

となります。

そのため、A4は4、A5は5・・・となります。

また、

A1=1

A2=2

A3=1

A4=2

A5=2

の場合、加算幅は

オートフィルした最初のセルの数値:2÷「1」と「2」の繰り返し回数:2÷A1~オートフィルした最初のセルまでのセル数:5

=2÷2÷5=0.2

となります。

そのため、A6は2.2、A7は2.4・・・となります。

さらに

A1=1

A2=2

A3=1

A4=2

A5=1

A6=2

A7=1.8

の場合、加算幅は

オートフィルした最初のセルの数値:1.8÷「1」と「2」の繰り返し回数:3÷A1~オートフィルした最初のセルまでのセル数:7

=1.8÷3÷7=0.08571428571・・・

となります。

そのため、A8は1.885714286・・・、A9は1.971428571・・・となります。


個人的に調べた範囲では、このような計算式のようです。

id:panana

回答ありがとうございます。

なんと言いますか…すごいです。とにかくすごいです。感動しました。

すみません。語彙が少ないもので、すごいとしか表現できないんですが、驚きました。

確かにその通りになりますね!見事でした!

2006/04/20 17:33:18
id:aki73ix No.4

aki73ix回答回数5224ベストアンサー獲得回数272006/04/20 17:06:24ここでベストアンサー

ポイント50pt

手計算で法則を見つけました

まず、オートフィルのセルがいくつであってもいいので平均値を求めます

1)1,3,2の場合

2)1,4,7,3の場合

3)2,-4,6,5,1の場合

4)9,-14,16,2,1,0の場合

それぞれ、2、3.75、2、2.333333となります

これが、先ず中央値になります

次に中央値を除く部分(選択セルが偶数の場合は全て)を対象に分子、分母について次の計算を行えば増分が求まります

1)(2-1)×2=2

2)(7-4)×1+(3-1)×3=3+6=9

3)(5-(-4))×2+(1-2)×4=18-4=14

4)(2-16)×1+(1-(-14))×3+(0-9)×5

=-14+45-45=-14

※中央値からの距離をかけたものの和です

分母については次のとおり

1)2×2=4

2)1×1+3×3=10

3)2×2+4×4=20

4)1×1+3×3+5×5=35

つまり増分が 0.5 、0.9 、0.7 、-0.4

先ほどの中央値から計算して空白セルを埋めると

1) 1, 3, 2, 3, 3.5, 4, 4.5, 5 ....

(1.5,[2],2.5,3,3.5 )

2) 1, 4, 7, 3, 6, 6.9, 7.8, 8.7 ....

(2.4,3.3,[(3.75)],4.2,5.1)

3) 2, -4, 6, 5, 1, 4.1, 4.8, 5.5, 6.2....

(0.6, 1.3, [2], 2.7, 3.4)

4) 9, -14, 16, 2, 1, 0, 0.9333, 0.5333, 0.1333, -2.6667......

(3.3333, 2.9333, 2.5333,[(2.3333)], 2.1333, 1.7333, 1.33333)

※ []は中央値 [()]は仮の中央値

のようになります

実際に手動フィルをしてみるとこのとおりになる事がわかります

この計算は、平均二乗誤差ではなく、平均誤差ですね

id:panana

回答ありがとうございます。

aki73ixさんもすごいですね!(さっきから“すごい”以外の言葉が出なくてごめんなさい)

1)1,3,2について、追ってみます。

1,3,2の平均 2

≪増分≫

>中央値を除く部分(選択セルが偶数の場合は全て)を対象に分子、分母について次の計算

選択セルが奇数だから中央値(って真ん中の数字?)を除く部分を対象に…次の計算!?

みた感じ、分子は

「選択セルが奇数だったら」(中央値の右1-中央値の左2)×2、(中央値の右2-中央値の左2)×4…

「選択セルが偶数だったら」(右の中央値-左の中央値)×1、(右の中央値の右-左の中央値の左)×3… でしょうか…?

うまく言えませんでしたが作業はわかりました。

で、今回は(2-1)×2=2

分母が…なんだこれ。2x2って何処から出てきた…。

分子の計算のときにかけた数字を二乗して足せばいいのかな??

ということにして、2×2=4が分母。

ということで、増分は2/4=0.5

>先ほどの中央値から計算して

えっと…真ん中の3のところを2とすると、左が1.5、右が2.5。その次は3、3.5、4…で、

最初にあった3つの数字1,3,2はそのまま残して、4つめから今出てきた3、3.5…を採用。

1,3,2,3,3.5,4…

おおっ!!

ちょっと1,2,1,2、でもやってみます。

(中略)

1,2,1,2

(,,[1.5],,,,)

(1.2,1.4,[1.5],1.6,1.8,2,2.2)

1,2,1,2,2,2.2,…

あ。

理解しました。ありがとうございました。

dungeon-masterさんの中央であると仮定しとか傾きの半分を…とかも理解できました。

2006/04/20 18:14:34
id:dungeon-master No.5

dungeon-master回答回数571ベストアンサー獲得回数402006/04/20 17:06:56

ポイント20pt

2の回答、最後ちょっと訂正します。

>A5は1.6+2*0.2=2、A7以降0.2ずつ増加となります。

正しくは

A5は1.6+2*0.2=2、以降0.2ずつ増加となります。

です。


最小二乗法については

http://www5d.biglobe.ne.jp/~tomoya03/shtml/algorithm/Linear....

でも詳しくアルゴリズムが紹介されていますが、実際に手計算でやってみましょう。


URLでは、回帰直線の式 y=ax+b において 傾きaは

a = (∑(xi * yi) - (∑xi * ∑yi) / n) / (∑(xi^2) - (∑xi * ∑xi) / n)

で求められるとありますので、そのまま使います。

(x,y)=(1,1),(2,2),(3,1),(4,2)の4点が対象です。

nは点の個数なので4です。


では、各項についてそれぞれ計算

∑(xi * yi) → 1*1,2*2,1*3,2*4 の合計→1+4+3+8=16

(∑xi * ∑yi) / n→ (1+2+3+4)*(1+2+1+2)/4 = 60/4=15

∑(xi^2) → 1*1+2*2+3*3+4*4 = 1+4+9+16=30

(∑xi * ∑xi) / n → (1+2+3+4)*(1+2+3+4)/4 = 10*10/4 = 25


というわけで

a = (16-15)/(30-25) = 1/5 → 0.2

が傾きになります。


後は、平均 1+2+1+2/4=1.5 に 0.2の半分 0.1 を足した1.6を

A3の値と仮定し、A5は 1.6+2*0.2=2、A6は1.6*3+0.2=2.2

以下続く…となります。

id:panana

手計算、ありがとうございます!

…Σ。

あ、いえ大丈夫です。Σ、わかります。

> (x,y)=(1,1),(2,2),(3,1),(4,2)の4点が対象です。

えっと…yはセルの数字を上から順に、でxは1から1ずつ増えていくんですね。

a = (∑(xi * yi) - (∑xi * ∑yi) / n) / (∑(xi^2) - (∑xi * ∑xi) / n) に当てはめて…

>では、各項についてそれぞれ計算 ~ というわけで

> a = (16-15)/(30-25) = 1/5 → 0.2 が傾きになります。

とてもよくわかりました。

>後は、平均 1+2+1+2/4=1.5 に 0.2の半分 0.1 を足した1.6を

>A3の値と仮定し、A5は 1.6+2*0.2=2、A6は1.6*3+0.2=2.2  以下続く…となります。

「0.2の半分 0.1 を足した」は、セルの個数が偶数で真ん中が二つあるからですね?

A2の値も仮定すると「1.5 - 0.2/2 = 1.4」ですよね! で、A1は1.2、A4が1.8、A5が2.0…。

でもこれらはあくまで仮定の数字で、A1~A4には正しい数字が入っているからそれを採用。

A5以降は何も入ってないからこの仮の数字を埋めていきますね~(by Excel) ですよね?

2006/04/20 18:38:01
id:owl No.6

owl回答回数165ベストアンサー獲得回数152006/04/20 17:20:30

ポイント20pt

数学は得意ではありませんが考えることは大好きです。

さて、御質問の件ですが、試してみたところ、 Excel97 でも再現しました。で、さらにいろいろ試みたのですが、どうやら最小二乗法で直線を引っ張り、外挿しているようですね。TREND() 関数を使用して値を再現することができました。

http://www010.upp.so-net.ne.jp/catwalk/lib/Answer1145512253....(約26KB)

(Excel97 にて作成、ウィルスセキュリティ最新定義パターンにてチェック済み)

視覚的にしたほうがわかりやすいと思います(というか私がわからかっただけですが)ので、上記ファイルを御覧になり、数字をいじって試してみてください。

 

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

id:panana

回答ありがとうございます。

> 数学は得意ではありませんが考えることは大好きです。

私もです。でもこれは挫折しました。皆さんホントにすごいです。(またすごいって言ってしまった…)

ファイルを作っていただいてありがとうございました。

ばらばらの点1,2,1,2…(1,1),(2,2),(3,1),(4,2)の真ん中を通る直線を引いて、

(5,y)のyがA5になってる感じですね。

皆さんが答えてくれたことを、イメージとして捉えることができました!

ということでこれで解決にします。ありがとうございました。

2006/04/20 18:54:46
  • id:panana
    いるか賞、とても悩みましたが、最初に手計算で(1,2,1,2)以外にも対応できる法則を
    見つけてくださった、aki73ixさんに贈らせていただきました。

    皆様ありがとうございました。またよろしくお願いいたします。
  • id:dungeon-master
    >Slope(既知のy,既知のX)で、既知のyはたぶんA1:A5かと思うんですが、
    >既知のxはどこでしょうか?
    申し訳ありません。
    その説明では、傾き計算はSlopeが相当するということを示したかった
    だけで、実際に使う場合の説明までは気が回りませんでした。
    補足しますと、後の回答で
    > (x,y)=(1,1),(2,2),(3,1),(4,2)の4点が対象です。
    とした通り、既知のxは選択したセルの連番(ここでは行番号)に対応
    するような、例えば1,2,3,4,5のように順に1増加する値となります。
    A1~A4に1,2,1,2を並べ、B列のB1~B4に1,2,3,4を並べて、
    =Slope(A1:A4,B1:B4)を計算させると傾き0.2が求まります。
    (実際にExcel内部でSlopeを使っているかは不明です。)
  • id:panana
    補足コメントありがとうございました。

    >その説明では、傾き計算はSlopeが相当するということを示したかっただけ
    そうだったんですね。
    聞いたことのない関数だったので気になってしまいました…。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません