Excelの課題 - C

ベーシック演習
表計算ソフトウェア「Excel」の課題
課題 B1 （提出用ファイル：STARTUP）
下記は 1995-1999 年における日本の新規設立法人である．
日本の新規設立法人
合名会社
合資会社
有限会社
株式会社
1995
50
198
66,902
23,172
1996
82
341
75,986
24,341
1997
69
614
71,533
20,394
1998
88
1,142
63,952
17,320
1999
133
1,788
67,515
18,600
(1) この表を作成せよ．
注:
i)
書式設定を用いて桁区切り（,）を付けること．
ii) セル幅はすべて 10 とする．
(2) この表をもとに以下のようなグラフを作成せよ．
日本の新規設立法人
150,000
100,000
株式会社
有限会社
合資会社
合名会社
件数
50,000
0
1995
1996
1997
1
1998
1999
課題 B2 （提出用ファイル：FINANCE）
次の表は，わが国の大手電気機器メーカーである日本電気の財務諸表（単独，2005 年 3 月）で
ある．
日本電気の財務諸表（単独，2005 年 3 月）
項目
金額（百万円）
売上高
2,426,835
売上原価
1,842,885
販売費一般管理費
560,867
営業利益
営業外収益
61,476
営業外費用
43,314
経常利益
流動負債
970,657
固定負債
602,842
資本
914,251
負債・資本合計
資料：日本経済新聞社「会社年鑑 2006」をもとに加筆修正．
(1) 上記の財務諸表のうち，営業利益，経常利益，負債・資本合計を求めよ．
(2) ROA を計算せよ．ただし，分子の利益は「営業利益＋営業外収益」，分母の総資産は「固定負
債＋資本」でそれぞれ定義したうえで計算すること．なお，ROA は，小数点第 3 位まで表示する
こと．
2
課題 B3 （データファイル：CAR）
下記はわが国における乗用車新車販売台数をあらわしている．
乗用車（普通・小型）新車販売台数：2006 年
メーカー
2006
トヨタ
1,490,785
日産
538,897
ホンダ
414,874
マツダ
175,997
スバル
93,042
スズキ
79,642
三菱
71,432
ダイハツ
21,213
その他
シェア
248,252
合計
資料：日本自動車販売協会連合会（http://www.jada.or.jp/index.html）
注：「トヨタ」は，「レクサス」を含む．
(1) SUM 関数を用いて，合計を計算せよ．
(2) シェアは合計にしめる各メーカーの比率をあらわす．それぞれのメーカーのシェアを求めて表
を完成させよ．また，シェアの合計が 1（=100%）になることを確認せよ．なお，シェアは，パーセン
ト表示とし，小数点第 1 位まで表示すること．
3
課題 B4 （提出用ファイル：ACCOUNT）
経費を仕訳するための，下記のような表を完成させよ．ただし，キーボードから入力するのは，コ
ード表，および，月，日，コードだけとし，残りは VLOOKUP 関数，IF 関数により自動的に表示，計
算されるような構造にすること．
コード表
コード
項目
1 租税公課
2 通信費
3 旅費交通費
4 消耗品費
月
日 コード
項目
金額
租税公課
2
3
1 租税公課
1,000
2
6
3 旅費交通費
1,220
2
8
4 消耗品費
970
2
10
3
640
2
11
2
640
2
12
1
1,000
2
13
3
640
2
15
2
32,800
2
18
3
2,100
2
21
4
3,280
2
23
3
640
2
24
1
400
2
26
2
120
2
28
3
640
2
28
4
980
通信費
旅費交通費 消耗品費
1,000
1,220
970
合計
4
課題 B5 （提出用ファイル：MATH）
次の表は，あるクラスでの数学の成績である．以下の指示にしたがい表を完成させよ．
点数
偏差値
安藤
49
金田
65
後藤
28
中西
57
中村
46
野村
44
藤田
38
藤本
79
星野
82
村山
50
吉田
72
順位
判定
合計点
平均点
分散
標準偏差
最高点
最低点
(1) 合計点，平均点，分散，標準偏差，最高点，最低点は，それぞれの関数を用いて計算せよ．
なお，分散，標準偏差は小数点第 1 位まで表示すること．
(2) 偏差値は次の式を用いて計算せよ．なお，偏差値は小数点第 1 位まで表示すること．
y i = 50 + 10 ×
yi :
x
:
xi − x
s
学生 i の偏差値
xi :
平均点
s
:
学生 i の点数
（標本）標準偏差
(3) 順位は RANK 関数を用いて，点数の高い学生から順番に，1,2,…と順位付けせよ．
(4) 判定は IF 関数を用いて，点数が 40 点以上を Pass，40 点未満を Fail と表示せよ．ただし，順
位，判定の合計点，平均点，分散，標準偏差，最高点，最低点を計算する必要はない．
5
課題 B6 （データファイル：FOOD1，提出用ファイル：FOOD）
次の表は，1996 年秋現在，乳製品，加工肉などを取り扱う食料品製造業上場企業のデータで
ある．なお， SUM，AVG，VAR，rxyはそれぞれ合計，平均，分散，相関係数をあらわす．また，Xa，
YaはそれぞれX，Yの平均値をあらわす．
社名
総資産(X) 売上高(Y)
明治乳業
239,190
452,298
雪印乳業
322,452
539,706
森永乳業
209,887
405,903
21,404
27,410
ヤクルト本社
242,001
159,569
プリマハム
120,899
265,832
日本ハム
359,186
594,253
伊藤ハム
209,359
433,933
林兼産業
43,039
79,960
雪印食品
40,800
114,424
丸大食品
155,544
225,988
米久
34,269
64,946
福留ハム
22,699
36,124
スタミナ食品
29,902
53,140
六甲バター
(X-Xa)2
X-Xa
Y-Ya
(Y-Ya)2
(X-Xa)(Y-Ya)
SUM
AVG
VAR
rxy
注： 総資産，売上高の単位は百万円であり，総資産は簿価ベース．六甲バターの売上高は
1995 年度のデータ．
(1) 総資産と売上高の合計，平均，分散を求めよ．
(2) 総資産を横軸（ x 軸），売上高を縦軸（ y 軸）として散布図を作成せよ．
(3) 上記の表を完成させ相関係数 rxy を求めよ．
(4) さらに，表の下に CORREL 関数を用いて相関係数を求め，(3)の結果が正しいことを確認せよ．
また，散布図および相関係数からどのような相関があるかなどをコメントせよ．
6
課題 B7 （データファイル：STUDENT1）
データファイルをもとに，Excel を用いたデータベースの操作を演習する．
(1) 「PC 番号」の小さい順でソートせよ．ソート
(2) 次に，「学年」の若い順，かつ「学籍番号」の小さい順でソートせよ．ソート後は，「No.」をもとに
あらためてソートし，データベースを元に戻せ．
(3) 「会計学科」の学生だけを抽出せよ．
(4) 「会計学科」の「1 年生」だけを抽出せよ．
課題 B8 （データファイル：COOP1，提出用ファイル：COOP）
ある大学の生協窓口において，昼休みの利用者数のデータを 200 日間にわたって集計した．こ
のデータをもとに，利用者数についてのヒストグラムを作成せよ．また，累積相対度数も示せ．
生協窓口の昼休みの利用状況
40
100.00%
度数
60.00%
20
40.00%
10
20.00%
0
.00%
0
1
2
3
4
度数
5
6
利用者数
7
8
累積相対度数 [%]
7
9
10
11+
累積相対度数 [%]
80.00%
30
課題 B9 （データファイル：MOBILE1，提出用ファイル：MOBILE）
C 大学商学部と法学部の男子・女子の大学生 340 人に，N 社および P 社の携帯電話のうち，ど
ちらの商品のデザインが好きかについてアンケート調査を行った．
(1) データファイルをもとに以下のようなクロス表を作成せよ．
性別
男子
女子
合計
商学部
学部
法学部
合計
(2) 「学部」と「商品」をもとにクロス表を作成した上で，カイ 2 乗統計量を求めて，カイ 2 乗検定を行
え．
(3) 同様に，「性別」と「商品」をもとにクロス表を作成した上で，カイ 2 乗統計量を求めて，カイ 2 乗
検定を行え．
(4) 分析結果から学部の違いによる商品の好みに差異がみられると言えるか．また，性別の違い
による商品の好みに差異がみられると言えるか．(3)の分析結果をもとに，それぞれについてコ
メントを加えてみよ．
例： カイ 2 乗検定
よって，有意水準 5%で，商品の好みと所属学部とが独立であるという仮説は（棄却され
る？ 棄却されない？），商品の好みが所属学部によって（異なる？ 異ならない？）と言
える．
8
課題 B10 （データファイル：BEAN1，提出用ファイル：BEAN）
2 種類の肥料 A，B の効果を検証するために，これらの肥料により育てた 20 個の豆の質量を測
定した．肥料 A，B により育てた豆の平均は異なるか？ それぞれの分散が等しいかを検定した上
で検定せよ．
(1) 肥料 A，B により育てた豆の分散は異なるかを有意水準 5%で検定せよ．また，分析ツールに
よる分析結果の下にコメントを加えること．
例： F 検定
よって，有意水準 5%で，肥料 A，B により育てた豆の分散が等しいという仮説は（棄却さ
れる？ 棄却されない？），肥料 A，B により育てた豆の分散は（等しい？ 等しくない？）
と言える．
(2) (1)の結果をもとに，どの分析ツールを用いるかを判断し，肥料 A，B により育てた豆の平均は
異なるかを有意水準 5%で両側検定せよ．また，分析ツールによる分析結果の下にコメントを加
えること．
例： t 検定
よって，有意水準 5%の両側検定で，肥料 A，B により育てた豆の平均が等しいという仮
説は（棄却される？ 棄却されない？），肥料 A，B により育てた豆の平均は（等しい？ 等
しくない？）と言える．
注:
分析ツールの出力先は新規シートとし，DATA，F-test，t-test などシート名を変更するこ
と．
9
課題 B11 （提出用ファイル：IC）
ある電子部品メーカーでは，5 つの工場で電子部品を製造している．他の条件を同じにして，そ
れぞれの工場で製造された 6 個の電子部品の消費電流を測定したところ，以下のような結果となっ
た．この結果をもとに，電子部品の消費電流は工場によって異なるかについて分散分析を行う．
工場1
工場2
工場3
工場4
工場5
1
2.0
2.3
2.8
1.9
2.2
2
2.6
2.2
2.7
2.5
2.0
3
2.8
2.0
2.5
2.1
2.1
4
2.2
2.7
2.0
2.4
2.3
5
2.7
2.4
2.1
2.6
2.2
6
2.5
2.3
2.4
2.7
2.2
工場によって製造された電子部品の消費電流に差異が認められるかを有意水準 5%で検定せよ．
また，分析ツールによる分析結果の下にコメントを加えること．
例： 分散分析
よって，有意水準 5%で，工場によって製造された電子部品の消費電流に差異がないと
いう仮説は（棄却される？ 棄却されない？），製造される工場によって電子部品の消費
電流に差異が（認められる？ 認められない？）．
注:
分析ツールの出力先は新規シートとし，DATA，ANOVA などシート名を変更すること．
10
課題 B12 （データファイル：HOUSE1，提出用ファイル：HOUSE）
2001 年春，小田急不動産が売り出した「多摩境」および「八王子みなみ野」（以下，「みなみ野」
と略す）の新築住宅を対象に，どのような要因が住宅価格に影響を与えるかについて分析する．
(1) 土地面積を横軸（ x 軸），販売価額を縦軸（ y 軸）として散布図を作成せよ．同様に，建物面積
を横軸，販売価額を縦軸として散布図を作成せよ．
(2) 表の右の空いている列に，「エリア」ダミー，「方角」ダミー，「角地」ダミーの列を追加して，これ
らの値を各企業について求めてみよ．
「エリア」ダミー ： エリアが「多摩境」の場合には 1，それ以外を 0 とするダミー変数
「方角」ダミー
： 方角が「南側」の場合には 1，それ以外を 0 とするダミー変数
「角地」ダミー
： 「角地」の場合（○印の場合）には 1，それ以外を 0 とするダミー変数
なお，参考として，関数を用いて「エリア」ダミーを求めた場合，以下のようになる．
「エリア」ダミーの数式
=IF(エリアのセル番地=”多摩境”,1,0)
(3) 販売価額を Y，土地面積，建物面積，「エリア」ダミー，「方角」ダミー，「角地」ダミーを X として，
分析ツールを用いて回帰分析を行え．また，結果の下に，どのような要因が販売価額に影響を
与えるかについてコメントを加えよ．なお，有意水準は 10%，5%，1%のうち，「X が Y に影響を与
えない」という仮説を棄却できる最小の値を選択し，有意水準 10%でも棄却できない場合，仮説
を棄却できないとせよ．
例： 回帰分析
有意水準?%で，土地面積が大きいと販売価額は高い（or低い）ことが示された（or土地
面積と販売価額との関係がないことが示された）．土地面積が 1[m2]大きくなると販売価額
は? [万円]高くなる．
(4) さらに，販売価額，土地面積，建物面積を対数変換し，(3)と同様に，分析ツールを用いて回
帰分析を行え．また，(3)と同様に，コメントを加えよ．
注： (3), (4)はそれぞれ別のシートに作成し，また，データと散布図，回帰分析，回帰分析
（対数）など，シート名を変更すること．
11
課題 B13 （提出用ファイル：HIST）
分析ツールを用いて正規乱数（正規分布にしたがう乱数）を作成し，度数分布表およびヒストグラ
ムを作成する．
(1) 度数分布表の作成のため，次の表を作成せよ．作成の際には，数式や関数などを用いて効率
的に作成すること．
階級の下限
階級の上限
階級値
-4.25
-3.75
-4.0
-3.75
-3.25
-3.5
-3.25
-2.75
-3.0
-2.75
-2.25
-2.5
…
…
…
(2) 分析ツールを用いて，標準正規分布 N (0,1 ) （平均 0，分散 1 の正規分布）を 100 個作成せ
2
よ．なお，分析ツールによる出力先は新規シートとし，ワークシート名を RAND100 と変更せよ．
(3) 作成した乱数について，分析ツール（ヒストグラム）を用いて度数分布表とヒストグラムを作成せ
よ．
(4) (3)の実行後，作成した度数分布表のﾃﾞｰﾀ区間を階級値で置換し，最後にワークシート名を
HIST100 と変更してヒストグラムを完成せよ．
(5) 同様に，正規乱数を 1000 個作成し(2)～(5)を行ってみよ．なお，ワークシート名はそれぞれ
RAND1000，HIST1000 と変更すること．
12
課題 B14 （提出用ファイル：RANDOM）
[-1,1]の一様分布にしたがう乱数（一様乱数） x, y を発生させ，これを用いて円の面積を求めて
みよ．
y
1
-1
O
1
x
-1
No.
x
y
円内
1
-0.75494
0.660451
0
2
-0.99457
0.102145
1
3
-0.29905
-0.61467
1
4
0.447493
-0.7575
1
…
…
…
…
…
…
…
…
13
課題 B15 （提出用ファイル：HAZARD）
ある製薬メーカーの研究者が，ある微生物の生存状況について分析している．この微生物は，1
時間ごとにその一部が死亡し，一定の条件もとでは，生存している微生物のうち次の時間に死ぬ
微生物の割合（ハザードレイト）は 3%と時間に依存せずに一定の値をとることがわかった．
(1) いま，この研究者が 10,000 個の微生物についてその生存状況を観察する場合，残り 100 個
以下となるのは観察開始後，何時間後と考えられるか．スプレッドシートを用いて 200 時間までの
生存数を求めてみよ．
14
時間
生存数
死亡数
0
10000
300
1
…
…
2
…
…
3
…
…
…
…
…
…
…
…
h(t)
0.03
死亡数の数式
=ROUND(生存数のセル番地*h(t)のセル番地,0)
(2) (1)での計算結果をもとに次のようなグラフを作成せよ．
微生物の生存状況
10000
生存数
8000
6000
4000
2000
0
0
20
40
60
80
100 120 140 160 180 200
時間
(3) 微生物が残り 100 個となるのは観察開始後，何時間後となるか（理論値）を求め，(1)での結果
と理論値とを比較してみよ．なお，理論値の求め方は次のようになる．
ある時点 t まで，個体が生存している確率を生存関数 S (t ) であらわす．また，個体が既に死亡し
ている確率を F (t )(= 1 − S (t ) ) であらわし，時刻 t までに生存している個体が次の瞬間に死亡する割
合をハザードレイトとして定義する．ここで，生存関数とハザードレイトとの関係は
h(t ) =
dF (t ) dt
d
= − ln S (t )
S (t )
dt
15
⎛
S (t ) = exp⎜ −
⎝
t
⎞
∫ h(u)du ⎟⎠
0
となる．この問題では，ハザードレイトは時間に依存せずに一定の値 c をとるので
S (t ) = exp(− ct )
となる． S (t ) = 100 10000 , c = 0.03 を代入して
10 −2 = exp(− 0.03t )
t=
ln 10 −2 200
=
ln 10
− 0.03
3
を得る．これを Excel の数式を用いてあらわし，理論値を求めればよい．
16
参考文献
■ Excel によるデータ処理
荒木勤・穴沢務 (2000) 『Excel で学ぶ経営科学入門シリーズⅢ：データ解析』 実教出版．
内田治 (1996) 『よくわかる Excel による統計解析』 東京図書．
内田治 (1996) 『よくわかる Excel による多変量解析』 東京図書．
■ 統計学
猪股清二 (1990) 『統計学ハンドブック』 聖文社．
東京大学教養学部統計学教室編 (1991) 『統計学入門』 東京大学出版会.
東京大学教養学部統計学教室編 (1991) 『自然科学の統計学』 東京大学出版会.
中村隆英・新家健精・美添泰人・豊田敬 (1984) 『統計入門』 東京大学出版会．
森棟公夫 (1990) 『統計学入門』 新世社．
17