エクセルの時系列分析の問題について

質問

エクセルど素人の私に、どなたか教えていただければ幸いです。
現在、エクセルの資格のために勉強中なのですが、そのテキスト中にあった問題の求め方を知りたいのです。
問題は、ある支店の次年度(2006年度)の売上予測を求めるものです。
表の項目は、左横から順に(金額：単位は千円)

2003年度売上(17,952)、2004年度売上(17,384)、2003～2004年度伸び率《問1》　2005年度売上(16,984)、2004～2005年度伸び率《問2》、2003～2005年度伸び率平均《問3》、2006年度売上予測《問4》
という7項目からなるもので、問題文は「表を完成させ、2003～2005年の伸び率の平均から2006年度の売上を予測しなさい。」となっています。

テキストには、それぞれ《問1》～《問4》は《問1》97％、《問2》98％、《問3》97％、《問4》16,520と解答が記されてはいるのですが、なぜそうなるのかという式も解説も全くなく、数学の弱い者として大変困っております。《問1》～《問3》の求め方はかろうじて理解してはいますが、お恥ずかしながら《問4》の求め方がさっぱりわかりません。

わかりづらい表記をお許しください。
よろしくお願いいたします。

回答No.4

noocyte · ベストアンサー

《問１》～《問４》の解き方をまとめますと，

●《問１》,《問２》
　計算式は #1 さんが書いておられるとおりですが，一応解説します．

　「伸び率」の定義は，#3 の前半にも書きましたが，「前年度の売上を１とした
　場合，当該年度の売上が何倍になっているか」ということです．したがって
　計算式は次のようになります．

　　　売上の年間伸び率 ＝ 当該年度の売上 / 前年度の売上

●《問３》
　伸び率平均の計算方法については #2 について書いたとおりですが，
　《問３》の場合について書くと次のようになります．

　2003～2005年度の伸び率平均をＲとする．2003年度を基準にすると，
　その２年後 (2005年度) の売上は

　　　2005年度の売上 ＝ 2003年度の売上 * Ｒ * Ｒ

　となる．Ｒはいくらか？ … という問題です．
　Ｒの求め方は，#2 にも書いたとおり，２とおりの計算方法があります．
　(どちらも数学的には同じですが，経過年数 (ここでは 2005－2003＝２年) が
　　大きいときは方法２の方が計算が簡単．)

　・方法１
　　2003～2004年度の伸び率と，2004～2005年度の伸び率の幾何平均を求める．
　　Excel の計算式風に書くと，

　　　Ｒ ＝ GEOMEAN(2003～2004年度の伸び率, 2004～2005年度の伸び率)

　・方法２
　　上の式「2005年度の売上 ＝ 2003年度の売上 * Ｒ * Ｒ」をＲについて解くと，

　　　Ｒ ＝ (2005年度の売上 / 2003年度の売上) の２乗根 (つまり平方根)
　　　(ここで「２乗根」の２は経過年数なので，別の問題では異なる数字になる．)

Excel の計算式では，

　　　Ｒ ＝ POWER((2005年度の売上 / 2003年度の売上), 1/2)

　　POWER(x, y) は「ｘのｙ乗」を計算する関数ですが，POWER(x, 1/y) とすると
　　「ｘの(１/ｙ)乗」つまり「ｘのｙ乗根」になります．

●《問４》
　計算式は #1 さんが書いておられるとおりです．
　《問３》で2003～2005年度の平均伸び率を計算しましたが，
　2005～2006年度も同じ伸び率で売上が推移すると仮定するわけです．したがって，

　　　2006年度の売上(予測) ＝ 2005年度の売上 * Ｒ

お礼

誠実に回答していただき感激しております。
お礼が遅れましたことお許しください。
noocyteさんが丁寧に回答してくださった文章を元に
頑張ってみようと思います。

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

回答No.3

noocyte

ちなみに，ですが「伸び率」の定義について注意．

このページでは「伸び率」は，「ある年と，その翌年の売上げの比率」と
いう意味で使われています．例えばご質問の例では次のようになります．

問２：2004～2005年度伸び率 ＝ 2005年度売上 / 2004年度売上
　　　＝ 16984 / 17384 ≒ 0.98 ＝ 98％

しかし世間では，これから１(＝100％) を引いた値を伸び率という場合も
ありますので注意してください．上の例では，

16984 / 17384 - 1 ≒ -0.02 ＝ －２％

こちらの意味の伸び率の場合，増えていれば＋，減っていれば－，変化なしならば
０になります．ニュースなどで「経済成長率３％」といってるのはこれです．

回答No.2

noocyte

#1 さん
> 2003～2005年度伸び率平均=(2003～2004年度伸び率+2004～2005年度伸び率)/2

この計算方法は間違っています．
伸び率の平均は，算術平均 (足して２で割る，相加平均ともいう) ではなく，
幾何平均 (掛けて平方根をとる，相乗平均ともいう) で計算します．

例えば次のような売上高の推移を考えます．

2000年　1.0億
2001年 1.1億　(伸び率 110％)
2002年 1.5億　(伸び率 136％)
2003年 1.3億　(伸び率 87％)
2004年 1.95億 (伸び率 150％)
2005年　2.0億　(伸び率 103％)

伸び率の算術平均は，上の５つの伸び率を足して５で割れば求まります．
その値は 117％ です．(Excel では AVERAGE 関数を使う．)

伸び率の幾何平均は同様に，５つの伸び率を掛けて５乗根をとれば求まります．
その値は 115％ です．(Excel では GEOMEAN 関数を使う．)

どちらが正しいのでしょうか？「伸び率の平均」というからには，
「毎年，その平均伸び率で伸びた場合」を考えてみるとよさそうです．
平均の伸び率をＲとし，2000年の売上 (1.0億) を基準として５年後 (2005年) の
売上を計算してみます．１年ごとに売上げはＲ倍になるので５年後の売上げは

　1.0億 * Ｒ * Ｒ * Ｒ * Ｒ * Ｒ

になるはずです．

・算術平均の場合 (Ｒ≒117％)
　1.0億 * Ｒ * Ｒ * Ｒ * Ｒ * Ｒ ≒ 2.2億
　となって，実際の2005年の売上2.0億とは異なります．

・幾何平均の場合 (Ｒ≒115％)
　1.0億 * Ｒ * Ｒ * Ｒ * Ｒ * Ｒ ＝ 2.0億
　となって，実際の2005年の売上に一致します．
　(平均伸び率は，実は次のようにしても計算できます．
　　(2005年の売上 / 2000年の売上) の５乗根をとる．)

●参考

「+"平均" +"伸び率"」で Google 検索
http://www.google.co.jp/search?sourceid=navclient-ff&ie=UTF-8&rls=GGGL,GGGL:2006-34,GGGL:ja&q=%2B%22%E5%B9%B3%E5%9D%87%22+%2B%22%E4%BC%B8%E3%81%B3%E7%8E%87%22
すると色々出てきますが，間違って算術平均で計算しているページも多いことに驚きました．

●正しいページ

ITエンジニアのスキル向上ゼミナール
【中級】仕事に役立つ統計分析　第2回　概要と内訳を調べる
http://itpro.nikkeibp.co.jp/article/COLUMN/20060410/234937/
『「足して割る」のは平均の一部』の最初の段落をご覧ください．
また，右の「図２ 算術平均と幾何平均の違い」もクリックしてご覧ください．

練習問題
某企業の年間売上高の近年の伸び率は、次のとおりです。平均伸び率を求めなさい。
http://www.wwq.jp/stacalcul/geomean1.htm

・JEITA、2004年度半導体テスター需要を42%増と見込む
http://www.sijapan.com/breaking/0412/16mr_jeita041215.html
このページでは半導体テスターの売上について書かれています．
2003年度の売上は書かれていませんが，2004年度の売上が3230億円 (前年比41.9％増)
とあることから，2003年度は2276億円であることがわかります．
「2003年度を基準に2007年度 (3045億円(予測)) までの年平均伸び率は7.5％」
とありますので，この平均伸び率は幾何平均であることがわかります．
(算術平均では9.6％になります．)

「統計学関連なんでもあり」の過去ログ---025
http://aoki2.si.gunma-u.ac.jp/lecture/mb-arc/arc025/084.html

平均 (Wikipedia，相加平均，相乗平均の説明あり)
http://ja.wikipedia.org/wiki/%E5%B9%B3%E5%9D%87

●間違っているページ

売上げの伸び率を計算して、売上げ予測を立てる
http://www.konomiti.com/Ex_yosoku.html
上の検索で見つけて，「おっ，ちょうど参考になりそうな Excel のページがある」と
思ったのですが，平均伸び率を算術平均で計算しているのを見てガックリきました．orz

↓このページも平均伸び率を算術平均で出してます．(この会社，大丈夫？)
2006年3月期 決算説明会 (情報開発株式会社)
http://www.tdi.co.jp/irjouhou/pdf/2006/kessannsetsumeikai0605.pdf

回答No.1

mshr1962

単純な掛け算ですが．．．
2003～2004年度伸び率=2004年度売上/2003年度売上　%表示で97%(0.96836)
2004～2005年度伸び率=2005年度売上/2004年度売上　%表示で98%(0.97699)
2003～2005年度伸び率平均=(2003～2004年度伸び率+2004～2005年度伸び率)/2　%表示で97%(0.97268)
2006年度売上予測=2003～2005年度伸び率平均*2005年度売上　16,520(16,519.92)

お礼

ありがとうございます。
考え方は理解していたのですが、なかなか数字が合わず
四苦八苦しておりました。
回答感謝いたします。