人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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…と振る方法自体は解決しております。

1145512253
●拡大する

●質問者: panana
●カテゴリ:コンピュータ 学習・教育
✍キーワード:A1 A3 Excel ダブルクリック データ
○ 状態 :終了
└ 回答数 : 6/6件

▽最新の回答へ

1 ● hiro7days
●20ポイント

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


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

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

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

◎質問者からの返答

最小二乗法(笑)

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

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

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

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

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

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

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


2 ● dungeon-master
●20ポイント

例えば、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ずつ増加となります。

◎質問者からの返答

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

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の値だと仮定」するのかが…。

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


3 ● くまいみずき
●30ポイント

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

実際に法則性を調べてみたら「なんで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・・・となります。


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

◎質問者からの返答

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

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

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

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


4 ● aki73ix
●50ポイント ベストアンサー

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

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

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)

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

のようになります

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

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

◎質問者からの返答

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

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さんの中央であると仮定しとか傾きの半分を…とかも理解できました。


5 ● dungeon-master
●20ポイント

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

以下続く…となります。

◎質問者からの返答

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

…Σ。

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

> (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) ですよね?


1-5件表示/6件
4.前の5件|次5件6.
関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ