｢情報A＆数理科学Ⅰ｣　＜テキスト5＞

 

§２　Ｅｘｃｅｌ活用

　Ｅｘｃｅｌは，表計算ソフトと呼ばれている分野のソフトです．ここでは数学

での活用（「数学Ｂ」３章　統計とコンピュータ）を中心に学習していきますが，

このソフトの活用範囲は大変広く，いろいろな分野で活用されています．

 

１．Ｅｘｃｅｌ入門

・     データの作成

＜練習１＞　教科書の【資料１】～【資料６】の作成

シート名を

[資料]に変更

して，教科書

「数学Ｂ」の

６つの資料

p.106～p.121　

を右のように

入力する．

 

　　　　　  保存場所

「作業ﾎﾙﾀﾞｰへ」

　　↓

「WORK」

　　↓

「Excel」

＜新規作成＞

　　↓

統計処理

＜ファイル名＞

 

 

 

 

 

(1) 度数分布

＜練習２＞　【資料１】の度数分布表

　　　　　　シート名を[度数分布]に変更して

　　　　　次のような度数分布表を作成せよ．

　

　　　　　　※　度数の計算は，関数（countif）

　　　　　　　　を使うと楽にできます．

　　　　　　　　使い方については，メニューバー

にある〔ヘルプ(H)〕で調べてください．

 

＜問１＞        【資料３】＜硬貨１０枚を投げたときの表の数＞の度数分布表

（相対度数を付ける）をシート[度数分布]に作成せよ．

 

＜練習３＞　【資料２】の度数分布表

　シート[度数分布]に

次のような度数分布表

を作成せよ．

 

　　　　　　※　階級値は、階級

　　　　　　　　の区切りに対し

て，関数（int）

を使うようにし

てください.

また，度数については，関数（frequency）を使ってください．

使い方については，教科書p.113にも載っています.

 

 

＜問２＞        【資料４】＜大阪の最高気温（平成１３年4月）＞の度数分布表

（相対度数を付ける）シート[度数分布]に作成せよ．

 

｢情報A＆数理科学Ⅰ｣　＜テキスト6＞

 

 (2) ヒストグラム

＜練習４＞　【資料１】のヒストグラムと折れ線グラフ

　　　　　　新しいシート（シート名は[ヒストグラム]）に次のようなヒストグラムを

作成せよ．また，グラフの種類を「折れ線」に変更せよ．

 

 

 

 

 

 

 

＜問１＞        【資料３】＜硬貨１０枚を投げたときの表の数＞のヒストグラム

　　　　　をシート[ヒストグラム]に作成せよ．

 

＜練習５＞　【資料２】のヒストグラムと円グラフ

　　　　　　シート[ヒストグラム]に次のようなヒストグラムと円グラフを作成せよ．

 

 

 

 

 

 

 

 


＜問２＞        【資料４】＜大阪の最高気温（平成１３年4月）＞のヒストグラ

ムと円グラフをシート[ヒストグラム]に作成せよ．

 

(3) 散布図（相関図）

　　　　　２つのデータ系列の関係を比べるのに散布図（相関図）があります．データ

　　　　が左下から右上にかけて散らばっているとき正の相関があるといい，左上から

右下にかけて散らばっているときは負の相関があるといいます．データが万遍

なく散らばっているときは相関がないといいます．

 

＜練習６＞　【資料２】と【資料４】の相関図

　新しいシート（シート名は[相関図]）に次のような【資料２】と【資料４】

の相関図を作成せよ．

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


＜問３＞        【資料４】＜大阪の最高気温（平成１３年4月）＞と【資料５】

＜那覇の最高気温（平成１３年4月）の相関図をシート名[相関図]に作成せ

よ．また，それらの相関関係についてあるかないかを調べよ．

 

｢情報A＆数理科学Ⅰ｣　＜テキスト7＞

 

(4) 代表値

　データ全体の特徴を表す数値を代表値といいます．

・平均値　：　n個のデータ　　の平均値aは，

　　　　　　　　

 

・メジアン（中央値）：　データを小さい（大きい）順に並べたとき，順番が真中の値．

ただし，個数が偶数のときは，真中2つの平均をとる．

 

　　　　　・モード（最頻値）：　度数分布において度数が最も多い階級値．

 

＜問１＞        メジアンとモードについて，インターネットを活用して更に詳し

く調べよ．

 

＜練習７＞　【資料６】（　１０円硬貨の重量　）の平均値

【資料６】の平均値を上の式とExcelの関数（Average）を使う２通りの方

法で求めよ．このとき，資料６のデータに

名前（資料６）を付けて関数を利用せよ．

ただし，新しいシートを（シート名は

[代表値]）作成して，それに次のように

記述せよ．

　　　　　　（使用する関数：Sum，Count，Average）

　　　　　　※　セル範囲に名前を付ける方法

　　　　　　　　〔挿入(I)〕→〔名前(N)〕→「定義(D)」

 

＜問２＞        【資料２】の平均値を上の式とExcelの関数（Average）を使う

２通りの方法で求めよ．

 

＜練習８＞　度数分布より平均値を求める

　【資料１】（グリンピースのさやの中の豆粒の数）の平均値を度数分布より求

めよ．また，この値と実際の平均値とを比較せよ．

このとき，＜練習２＞の度数分布に列（計）を１つ

追加せよ．

 

 

 

 

 

＜問３＞                     【資料２】【資料３】【資料４】の平均値を度数分布より求めよ．

 

＜練習９＞　【資料６】のメジアンを求める

　　　　【資料６】（１０円硬貨の重量）のデータを並べ替えて

メジアンを求めよ．また，Excelの関数（Median）を用いて

求めた値で答え合わせをせよ．

ただし，並べ替えをするときにはシート（資料）の複写を

して（シート名：「資料 (2)」），そのシートで行うこと．

 

　　　　※　並べ替え方（ソート）

　　　　　・並べ替えるデータ範囲を指定する．

　　　　　・〔データ(D)〕→〔並べ替え(S)]

　　　　　　→〔現在選択している範囲だけを並べ替える〕

　　　　　　→〔昇順か降順を選択する〕

 

＜問４＞                     【資料１】～【資料５】についてもメジアンを求めよ．

 

＜問５＞                     　＜練習２＞と＜練習３＞で作成した度数分布表のモードを求めよ．

 

｢情報A＆数理科学Ⅰ｣　＜テキスト8＞

 

(5) 分散と標準偏差

　分散や標準偏差は，データの散らばりの度合いを測る指標として使われる．

 

n個のデータ　　について，平均値をa とすると，

・　分散：

　　　･･･　①

・　標準偏差：

 

＜練習１０＞　変量の分散と標準偏差

次の表はＡ，Ｂ２人で10点満点のゲームαを６回行った結果である．次のような

ヒストグラムを作成せよ．また，平均と分散及び標準偏差を求めよ．

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

分散を手計算（テストなど）で行うとき，平均が整数であれば①の式を用いて

計算し，整数でなければ　　で計算する方が簡単である．

＜問１＞                     　となることを示せ．

 

＜練習１１＞　変量を変換したときの分散と標準偏差

　＜練習１０＞のゲームαにおいて，得点xに対してポイントもらえると

き，Ａのもらえるポイントの平均値と分散及び標準偏差を求めよ．

 

 

 

 

 

 

 


　　　　一般に，変量xに対して，新しい変量yが（c，dは定数）である

とき，変量yの平均は　，分散は　　となる．

　ただし，，は変量x，yの平均，，は変量x，yの分散である．

＜問２＞                     　上の2つの関係式，が成り立つことを示せ．

 

Excelには，分散（Varp）と標準偏差（Stdevp）を求める関数があります．

 

＜問３＞                     　＜練習１０＞＜練習１１＞の分散と標準偏差を関数Varpと

Stdevpを用いて求めよ．

 

｢情報A＆数理科学Ⅰ｣　＜テキスト9＞

 

(6) 相関係数

相関係数は，２つの変量x，yの関係の度合いを測る指標として使われる．

変量xのn個のデータ　　の平均値を，

変量yのn個のデータ　　の平均値をとすると，

・共分散：　

　　　　　

・相関係数：　

 

不等式　≦

をシュワルツの不等式という．

＜証明＞　すべての実数tに対して，≧0　が成立する．

　　　　　

　　　　　判別式 D ≦ 0 より　≦ 0　　［証明終］

 

　　　　　シュワルツの不等式より，相関係数rのとる範囲は，-1 ≦ r ≦ 1 となる．

また，rが 1に近いほど正の相関が強いといい，-1に近いほど負の相関が強い

という．

 

＜練習１２＞　＜練習１０＞のＡの得点を変量x，Bの得点を変量yとして，xと

yの共分散と相関係数求めよ．また，xとyの相関表を作成せよ．

（新しいシート：「相関係数」を作成する）

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

　＜問１４＞　相関係数を求めるExcelの関数（Correl）を用いて＜練習１１＞の

ポイントの相関係数を求めると＜練習１０＞の相関係数と同じとなる．

　この理由を答えよ．

　　　＜問１５＞　４人の生徒a，b，c，d の

身長 x（cm）と体重 y（kg）が右のよう

であった．

xとyの共分散と相関係数を求めよ．

また，xとyの相関表を作成せよ．

 

 

｢情報A＆数理科学Ⅰ｣　＜テキスト10＞

 

１．ＶＢＡ入門

Excelでは，Visual Basic for Application(VBA)というプログラミング言語が

使えます．これを使ってプログラミングの基礎を学習するとともに，その活用法

についても学習していきます．

 

(1)    コマンドボタンとプログラム

コマンドボタンをクリックするとそれに割り当てられているプログラムが実

行します．

 

＜練習１３＞　コマンドボタンにプログラムを設定する

　・コマンドボタンをワークシート上に設定する．

　　［表示(V)］→[ツールバー(T)]→[フォーム]

　　にチェックを入れる．

　　コマンドボタン　　　をクリックして，ボタン

を設置する場所を指定すると，マクロの登録の

ウインドが表示されるので　　　　　　ボタン

を押す．

　　　　・モジュール（プログラムの単位）を記述する画

面が表示される．ここにプログラムを記述する．

 

角丸四角形吹き出し: コマンドボタンに設定するモジュールをここに記述 Cells(k, 1) は，1行k列目 　　　　　

 

 

 

 

※　繰り返し文（For ～Next）

　　　　　　　一定回数を繰り返すときに使用する．kをループ変数という．

 

＜問１６＞　コマンドボタン上の名前を[ボタン1]から[計算]に変更せよ．

　また，モジュール名も[ボタン1_Click]から[計算]とせよ．

 

＜練習１４＞　関数のグラフ（y = sin x）を表示

セル(2,1)に[=-PI()]，セル(22,1)に[=PI()]と入力すると各セルの値を自動的に入力

する．

　コマンドボタン上の名前と

モジュール名をともに[関数の

値]とする．

グラフはグラフウィザード

より折れ線を選択し，データ

範囲は[B2:B22]，項目軸ラベ

ルは[A2:A22]とする．

＜モジュール＞

 


Sub 関数の値()

  s = Cells(2, 1)

  e = Cells(22, 1)

  d = (e - s) / 20

  For k = 2 To 22　　　　　　※　関数（Function）

    x = s + d * (k - 2)　　　　　ModuleとFunctionの違いは，戻り値を取るか

    Cells(k, 1) = x　　　　　　　取らないかの違いのみです．

    Cells(k, 2) = f(x)　　　　　 戻り値は，関数名＝[戻り値]で設定します．

角丸四角形吹き出し: 引数  Next k　　　　　　　　　　　　 また，モジュールや関数に引き継ぐ変数を括

End Sub　　　　　　　　　　　　　弧()の中に記述し，これを引数といいます.

角丸四角形吹き出し: 戻り値Function f(x)　　　　　　　　　　す．

  f = Sin(x)

End Function　　　　　　　　　＜問１７＞　と（0≦x≦2π）

の２つのグラフを同時に表示せよ．

 

 

｢情報A＆数理科学Ⅰ｣　＜テキスト11＞

 

(1)    条件文

プログラムは，繰り返し文と条件文の組み合わせで作られています．ここでは

　　　　条件文を中心に学習します．

 

＜練習１５＞　組立除法

・ワークシートに組立除法の仕組みの表を

作成する．

　　　・セル( B2:E2 )には，３次の整式の係数を

入力する．

　　　・セル( B4 ) を「 = IF(A2="","",B2)」とすると，セル( A2 ) が空白のときは

空白角丸四角形吹き出し: 余りとなり，そうでないときはセル( B2 )の値となる．

・セル( C3:E4 ) の各セルには式を記述する．

　(ⅰ)　 を  で割った商と余りを求めよ．

　(ⅱ)　 が  で割り切れるようにaの値を定めよ．

 

四角形吹き出し: Sub kai() r = 6 Solve2 End Sub ＜練習１６＞　２次方程式の実数解

・コマンドボタン[２次方程式の実数解] を作成し，

モジュールを作成する．

　　　・セル( B6:D6 )に２次方程式の

係数a，b，cを入力する．

　　　・グローバル変数を設定する．

Dim a, b, c As Double

Dim r As Integer

 

 

　　　・２次方程式の実数解を求めるモジュール（Solve2）を作成する．

 

 

 

 

Sub Solve2()

雲形吹き出し: 判別式が0以上のとき    　　　a = Cells(r, 2): '２次の係数

    　　　b = Cells(r, 3): '１次の係数

    　　　c = Cells(r, 4): '定数項

    　　　d = b ^ 2 - 4 * a * c

    　　　If d >= 0 Then

        　　　Cells(8, 3) = (-b - Sqr(d)) / (2 * a)

        　　　Cells(8, 4) = (-b + Sqr(d)) / (2 * a)

雲形吹き出し: ２次方程式の解の公式    　　　Else

        　　　Cells(8, 3) = ""

        　　　Cells(8, 4) = ""

    　　　End If

End Sub

 

四角形吹き出し: Function f3(x) f3 = a * x ^ 3 + b * x ^ 2 + c * x + d End Function
 


＜練習１７＞　３次方程式の実数解

・グローバル変数にdを追加する．

・関数（f3）を作成する．

・コマンドボタン[３次方程式の実数解]を作成し，モジュールを作成する．

 

Sub Solve1()

    　　　　　a = Cells(2, 2):b = Cells(2, 3):c = Cells(2, 4):d = Cells(2, 5)

雲形吹き出し: 解の1つが見つかったとき    　　　　　For k = 1 To Abs(d)

        　　　　　If f3(k) = 0 Then

            　　　　　Cells(2, 1) = k: k = Abs(d)

雲形吹き出し: 絶対値を求める関数        　　　　　ElseIf f3(-k) = 0 Then

            　　　　　Cells(2, 1) = -k: k = Abs(d)

        　　　　　End If

    　　　　　Next k

    　　　　　r = 4: Solve2

End Sub

 

 

｢情報A＆数理科学Ⅰ｣　＜テキスト12＞

 

(2)    グローバル変数

モジュール全体で使用できる変数をグローバル変数といいます．

この変数を設定するときは，Public文で(Declarations)に記述します．

 

＜練習１８＞　３次関数とその導関数のグラフ

・表示する区間をセル（B1:C1）に設定する．

・分割するときの増分をセル（B2）に設定する．

・３次関数の係数を次数の高いほうからセル（B4:E4）に入力する．

・導関数の係数をセル（B5:D5）に数式で入力する．

・関数の値をワークシート（シート名：「Work」）に入力するプログラムを作成し，

それにコマンドボタン[関数のグラフ]を割り当てる．

　　　・グラフウィザードで折れ線グラフを選択し，データ範囲等を設定する．

 

角丸四角形吹き出し: グローバル配列変数

 

 


Sub 関数の値()

    　　　s = Cells(1, 2): e = Cells(1, 3): z = Cells(2, 2)

    　　　    For k = 0 To 3: a(k) = Cells(4, k + 2): Next k

    　　　n = 1

角丸四角形吹き出し: ＜3次関数の係数＞ a(0)は３次の係数 a(1)は２次の係数 a(2)は1次の係数 a(3)は定数項     　　　For x = s To e Step z

        　　　Sheets("Work").Cells(n, 1) = x

        　　　Sheets("Work").Cells(n, 2) = f3(x)

        　　　Sheets("Work").Cells(n, 3) = df(x)

角丸四角形吹き出し: ワークシート（Work） のセルに代入         　　　n = n + 1

    　　　Next x

End Sub

 

 

(3)    キーマクロ

Excelにはキー操作を登録する機能があります．これをキーマクロといいます．

これを編集してプログラムの中に組み入れることができます．

 

＜練習１９＞　増減表とキーマクロ

・増減表を作成する．

・セル（B10:F12）を消すキーマクロを作成する．

　[ツール(T)]→[マクロ(M)]→[新しいマクロの記録(R)...]

・操作が終わったら　　　　　[記録終了(R)]とする．

 

　　　＜問１８＞　＜練習１８＞の導関数 f’ (x) = 0 となる実数値xをセル（B8:C8）に

　　　表示するプログラムを作成せよ．（プログラム名：「導関数の解」）

 

＜練習２０＞　３次関数の増減表とグラフ

　３次関数の係数を与えると増減表とグラフ（導関数を含む）を表示する．

角丸四角形吹き出し: ＜練習１８＞Sub 増減表()

角丸四角形吹き出し: ＜問１８＞で作成済み    　関数の値

    　導関数の解

    　x1 = Cells(8, 2): x2 = Cells(8, 3)

    　Cells(10, 2) = "･･･"

    　Cells(10, 3) = x1: Cells(10, 4) = "･･･"

Cells(10, 5) = x2: Cells(10, 6) = "･･･"

    　Cells(11, 3) = 0: Cells(11, 5) = 0

    　If a0 > 0 Then

      　  Cells(11, 2) = "＋": Cells(11, 4) = "－": Cells(11, 6) = "＋"

    　Else

      　  Cells(11, 2) = "－": Cells(11, 4) = "＋": Cells(11, 6) = "－"

    　End If

End Sub

 

 

 

【課題1】　次の問題をExcelのツール「課題（東大2004）.xls」を利用して解け．

 

 

 

 

 

 

 

 

 

 

 


｢情報A＆数理科学Ⅰ｣　＜テキスト13＞

 

(1)    With～End With

同じ対象に対していくつもの設定をするときに，その対象をそのつど指定す

　　　　るのを省略するときに使用します．

 

　　　＜例＞　　With Selection.Font

                        .Name = "ＭＳ 明朝"

                        .Size = 11

                        .Strikethrough = False

                        .Superscript = False

                        .Subscript = False

                        .OutlineFont = False

                        .Shadow = False

                        .Underline = xlUnderlineStyleNone

                        .ColorIndex = xlAutomatic

                End With

 

　　　＜問１９＞　＜練習２０＞のワークシート（増減表）のセル（B10:F12）の内容を

消して，フォントを「ＭＳ 明朝」とするキーマクロを作成せよ．

 

＜練習２１＞　キーマクロを組み込む

Range("B10:F12").Select

Selection.Font.Name = "ＭＳ 明朝"

Selection.ClearContents
 
　　　・＜問１９＞で作成したキーマクロを修正し

て＜練習２０＞のプログラムに組み込む．

　　　・セル（D1）「%」を複写するキーマクロを

　　　　　　作成する．

　　　・＜練習２０＞のプログラムに増減表の３行目を表示するプログラムを追加する．

 

　　　　※　 Range("D1").Select ⇔ Cells(1, 4).Select　…　セルを指定する

 

 

　　　　　　　〔　＜練習２０＞に追加するプログラム　〕

    For k = 2 To 6

        If Cells(11, k) = "＋" Then

            Range("D1").Select: Selection.Copy

            Cells(12, k).Select: ActiveSheet.Paste

雲形吹き出し: キーマクロで作成する．        ElseIf Cells(11, k) = "－" Then

            Range("E1").Select: Selection.Copy

            Cells(12, k).Select: ActiveSheet.Paste

        Else

            Cells(12, k) = f3(Cells(10, k))

        End If

    Next k

 

 

　　　＜問２０＞　導関数が0と

　　　なる値が重解をとるときに

　　　も増減表が正しくを表示す

るようにプログラム修正せ

よ．

 

　　　＜問２１＞　次の３次関数

　　　の増減表を表示させよ．

　　　(1)　

(2)　

 

 

 

 

 

 

｢情報A＆数理科学Ⅰ｣　＜テキスト14＞

 

(2)    文字列操作

変数には数値変数と文字列変数の２種類の変数があります．文字列を操作する

関数としては，Left，Right，Midなどがあります．これらは，文字列の一部分

を切り取る関数ですが，ここでは２つの文字列を連結させます．

 


　＜練習２２＞　区分求積法

　　放物線とx軸と直線  とで

囲まれる部分を次のように長方形に細分割して

面積を求める方法を区分求積法といいます．

　

　　

　　　　　　　　　　　　　　　　　分割数をnとすると，

　　　　　　　　　　　　　　　　　面積 　となります．

                                  ただし，，

　　　　・関数の値を表示するセルとして，ワークシート（「Work」）を作成する．

　　　　・ワークシート（「区分求積法」）に

区間，分割数，面積をセルに設定する．

　　　　・マクロボタン（「面積の計算」）を作成

　　　　　して，それに面積を求めるプログラム

を割り当てる．

　　　　・グラフウィザードでヒストグラムを表

示させる．

　　　　　このとき，このグラフの番号を確認す

る．

 

 

 

〔面積を求めるプログラム〕

Sub 面積の計算()

    s = Cells(1, 2): e = Cells(1, 3): n = Cells(2, 2)

    d = (e - s) / n

    Menseki = 0

    For k = 1 To n

        x = s + d * (k - 1)

        Sheets("Work").Cells(k, 1) = x

        Sheets("Work").Cells(k, 2) = f(x)

        Menseki = Menseki + d * f(x)

    Next k

    Cells(3, 2) = Menseki: '面積

    b$ = "=Work!R1C1:R" & Cells(2, 2) & "C1"

    ActiveSheet.ChartObjects("グラフ 1").Activate

    ActiveChart.SetSourceData Source:=Sheets("Work").Range(a$)

End Sub

Function f(x)

    f = x ^ 2

End Function
 
　　

 

 

角丸四角形吹き出し: 長方形の横の長さ
角丸四角形吹き出し: 文字列を連結する
 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


　　　＜問２２＞　関数  ( n = １，2，3，… )　のグラフとx軸と直線

 とで囲まれる部分の面積を求め，これらの値をnで表すとどうな

るかを推定せよ．

 


　　　　　＜　課　題　＞

　＜問２２＞において，区間を [0，x ]としたときの面積をSn(x)とするとき，

Sn(x)をnとxを用いてどのように表せるかを推定し，この推定が正しいこと

を証明せよ．