2007年11月アーカイブ

世帯主の年齢階層ごとの持ち家率と月収総額の平均がtab3-2.xlsに保存されています。このファイルを開いて、

1. セルE2列「=LN(D2/(1-D2))」と入力します。

2. セルE2をE8まで下方向へコピーします。

3. E列を被説明変数、B列を説明変数とする単回帰分析を行って下さい。

4. E列を被説明変数、B列とC列を説明変数とする重回帰分析を行って下さい。

コブ＝ダグラス型生産関数の推定を行います。

1. appendix2.xlsを保存し、Excelで開きます。

2. 付加価値額の対数値を被説明変数、労働投入額の対数値と資本設備額の対数値を説明変数とする回帰分析を行います。セルA2からC28のデータの対数を取ったものをD2からF28に作成して、作成したデータを使い、回帰分析を行ってください。
   データの対数を取る関数は「ln」です。例えば、セルA２の対数値を計算するには、
   「=ln(A2)」
   と入力します。

Microsoft Excelでは、代表的な統計手法を簡単に行うためのツールとして、「分析ツール」というものが用意されています。ここでは、「分析ツール」を利用した回帰分析の方法を説明します。

例として、被説明変数を賃金上昇率、説明変数を完全失業率と物価上昇率とする回帰分析を行います。

ワークシートにデータを入力します。データはここにあるので、リンクを右クリックし、「対象をファイルに保存」を選び、各自のフォルダに保存し、そのファイルを開いてください。

ファイルを開くと次のようになります。

次に、「ツール(T)」-「分析ツール(D)...」を選択します。

「分析ツール(D)...」を選択すると、以下のような画面が表示されます。ここで、「回帰分析」を選択し、「OK」ボタンをクリックしてください。

次のように設定して「OK」ボタンをクリックします。

「入力Y範囲」は、被説明変数が入力されている範囲を指定します。データは列方向に入力されていなければなりません。

「入力X範囲」は、説明変数が入力されている範囲を指定します。説明変数が複数個ある場合は、複数列を同時に指定します。この場合だと、A列とB列を同時に指定しています。隣り合っていない複数の列を指定することはできませんので、重回帰分析を行うときは気をつけましょう。

「ラベル」にチェックを入れると、「入力Y範囲」、「入力X範囲」の1行目が変数の説明であって、数値データではないことになります。

「一覧の出力先」をクリックして、$A$28を指定しました。これは、回帰分析の結果をセルA28を左端として出力することを意味します。

「残差」にチェックを入れると、回帰分析の残差（わかりますよね？）を出力します。

「観測値グラフの作成」にチェックを入れると、説明変数と理論値の散布図を表示します。

「OK」ボタンを押すと、いろいろな数値が表示されますが、ここでは必要な部分だけ説明します。

概要では、決定係数、自由度修正済み決定係数、重相関係数を読み取れます。ここでは、

「重相関 R」：重相関係数
「重決定 R2」：決定係数
「補正 R2」：自由度修正済み決定係数

の意味です。

次に、以下の部分に注目してください。

これが回帰係数です。これより、回帰式は

（賃金上昇率）＝16.11-5.54×（完全失業率）＋0.72×（物価上昇率）

とあらわされることがわかります。つまり、賃金上昇率は、

1. 完全失業率が上がると減少する。
2. 物価上昇率があがると増加する。

ことがわかります。

残差出力の部分は、回帰分析の結果計算できる理論値（Excelでは予測値と表示されています）と、残差を表示しています。（時間がある人は、残差の合計が0になること、また、残差と各説明変数の積の合計が0になることを確認してください。）

出力された2枚のグラフは、被説明変数と、説明変数を縦軸、横軸にとり、実際のデータと、回帰分析による理論値を散布図にしたものです。比較的、青い点とピンクの点が近い位置にプロットされており、回帰式の当てはまりがよいことがわかります。

行列を使って正規方程式を表現すると

と表せます。ここで、

、 、

です。これをについて解くと

となります。以下では実際にを求めてみましょう。以下のデータを用います。

セルB2からC5までが行列Xで、D2からD5までがベクトルｙを表しています。

まず、Xの転置行列をB9を左上にして求めてください。

次にをB12に求めます。

をB15に求めます。

をB18に求めます。

をB21に求めます。

この値は、以前求めた回帰係数と一致していることがわかります。

理論値と残差の計算

次に、求めた回帰係数を使って、理論値と残差を求めましょう。理論値は

で表されました。セルE2に適当な数式を入力し、セルE5まで下方向へコピーします。

残差は実績値ｙと理論値の差ですから、D列からE列の値を引いたものが残差になります。F2に適当な式を入力し、下方向へコピーしてください。

決定係数の計算

決定係数の定義は

で与えられます。ここで、nとkはそれぞれサンプル数と説明変数の数です。したがって、まず、

とを求めましょう。

まず、を求めます。そのためにyの平均を求めます。平均を求める関数AVERAGEを使って、D7にｙの平均を計算します。

G列にを計算します。G2に適当な数式を入力し、下方向へコピーしてください。絶対参照の使い方に気をつけてください。また、２乗を計算するには「＾2」を使います。たとえば、3の2乗は「3^2」です。

H列に残差の2乗を計算します。H2に適当な式を入力し、下方向へコピーです。

G列とH列の合計を求めます。G6に適当な式を入力し、右方向へコピーします。

決定係数をH7に計算しましょう。

行列

の転置行列を求めます。

B2からD3までドラッグし、「編集（E)」－「コピー（C)」をクリックします。

次に、転置行列を表示させる左上のセルを指定します。ここでは、B5にしましょう。B5にカーソルを移動させます。

「編集（E)」－「形式を選択して貼り付け（S)」をクリックすると、次のようなダイアログボックスが表示されるので、「行列を入れ替える（E)」のチェックを入れて、「OK」ボタンを押します。

すると、行と列が入れ替わったものがB5から表示されるはずです。（セルA5は手入力しました。）

行列

の逆行列を求めます。

B6からC7の範囲に答えを求めてみます。(2*2)行列の逆行列はやはり(2*2)行列なので、B6からC7の範囲をドラッグします。

数式編集バーをクリックし、次の式を入力し、SHIFT+CTRL＋ENTERキーを押します。

逆行列が得られます。

得られた逆行列とAの積が単位行列になるか各自確認してください。

行列

について、

を計算します。まず、各行列をExcelのワークシート上に入力します。

B11からC１２にA+Bを求めます。A+Bは各行列の成分をそれぞれ足せばいいので、

という計算式を入力します。右方向へコピーと下方向へコピーを使うと、A+Bが求まります。

次に、B14からC16にA*Bを計算しましょう。まず、B14からC15までドラッグします。A*Bを計算すると(2*2)行列になるので、2行2列分ドラッグするわけです。

数式編集バーをクリックし、次のように式を入力し、SHIFT+CTRL＋ENTERキーを押します。(SHIFTキーとCTRLキーを最初に押しておいて、ENTERキーを押す）

すると、ドラッグしていた範囲にA*Bが計算されます。

B*Aについても同様に計算できます。各自確認してください。

A*xを計算します。(2*2)行列と(2*1)行列の積なので、計算結果は(2*1)行列になります。ですから、ドラッグする範囲も2行1列になります。

計算式は次のようになります。前と同様にSHIFT+CTRL＋ENTERキーを押します。

計算結果が得られます。

課題

1. 各都道府県の人口と一人あたり所得がこのファイルに保存されている。このデータを使って
   (a) 人口の度数分布表とヒストグラムを作成しなさい
   (b) 所得のローレンツ曲線を作成しなさい。また、このローレンツ曲線から考えられることを述べなさい。
2. 国別の（対数をとった）人口密度と人口増加率がこのファイルに保存されている。このデータを使って
   (a) 散布図を描き、その関係を考察しなさい。
   (b) 人口密度と人口増加率の分割表を作成しなさい。人口密度と人口増加率は適当に3分割すること。
   (c) 対数をとった人口密度と人口増加率の相関係数を講義で行った方法で求めなさい。 

締め切り

次回の講義開始直前

表4-1から一人当たりGDPと人口増加率を取り出したファイルが、ここに保存されています。

ファイルを開いて、セルC2とセルC3に次のように入力します。

これら2つのセルを下方向へコピーすると、各国の所得が”低”、”高”に、人口増加が”低”、”中”、”高”に分類されます。

次に、「データ(D)」－「ピボットテーブルとピボットグラフレポート」をクリックします。すると、以下のダイアログボックスが表示されますので、「次へ(N)>」をクリックします。

さらに「次へ(N)>」をクリックします。

そして、「完了」をクリックします。

次のような画面が表示されるので、矢印のように3回ドラッグして下さい。

次のような分割表が作成されます。

データの入力

以下のデータをExcelで入力して下さい。

散布図の作成

このデータの散布図を描いて見ましょう。セルB1からC5までをドラッグして、「挿入（I)」－「グラフ（H)...」とクリックすると、グラフウイザードが表示されますので、グラフの種類を散布図に、形式を点のみに指定します。下図のようになるはずです。

「次へ＞」ボタンをクリックし、さらにもう一度「次へ＞」ボタンをクリックすると、グラフのタイトル等を決めるダイアログ・ボックスが表示されますので、次のように設定しましょう。

「次へ＞」－「完了（F)」の順でクリックすれば、ワークシート上にグラフが表示されます。グラフのサイズと位置は、通常のウインドウと同様に変更できますので、とりあえず計算の邪魔にならない位置に移動してください。

平均の計算

次に、x, y の平均、分散、標準偏差を求めます。まず、x, 合計と平均を求めましょう。B６とB7に以下の数式を入力します。

「COUNT(B2:B5)」は、セルB2からB5の間にいくつのセルがあるか数える関数です。この場合、COUNT(B2:B5)＝４ですね。次のような計算結果になります。

セルB6からC7までドラッグし、「編集（E)」－「フィル（I)」をクリックし、右方向へコピーしてください。ｙについての合計、平均が計算されます。

分散、共分散の計算

次に、x, yの分散、共分散を求める作業を行います。1行目に次の文字列を入力してください。

(x-xBAR)は、(x-xBAR)*(y-yBAR)は、(x-xBAR)^2は、のつもりで書いています。

2行目に数式を入力しなければならないセルは、

D2：「????」

F2：「=D2*E2」

G2：「D2*D2」

の3つです。セルD2に入力しなければならない数式は自分で考えてみてください。正しい数式が入力できたら、

1. セルD2をE2に右方向へコピー

2. セルG2をH2に右方向へコピー

3. セルD2からH5までドラッグして下方向へコピー

によって、次のようになります。

D列からH列の合計を6行目に求めましょう。セルC6からH6までドラッグして右方向へコピーしてください。

D列とE列の合計は必ず0になることは統計学で学習しました。覚えていますか？

次に、F列、G列、H列の合計を（データ数－1）で割ることによって、x,yの共分散と、x,yの分散を求めましょう。

セルF8に「=F6/(COUNT(F2:F5)-1)」と入力し、H2まで右方向にコピーしてください。

相関係数の計算

次にセルB10に相関係数を計算します。次の式を入力してください。

SQRTというのは平方根を計算するための関数です。

Excelの関数を使った計算

Excelには相関係数を計算するための関数CORRELが用意されています。次のように入力して下さい。

結果が一致することが分かります。

1990年の各国の人口を使って、分布の代表値とばらつきの特性値を計算します。

Excelには、

平均： AVERAGE
四分位点： QUARTILE
中央値： MEDIAN
分散： VAR
標準偏差： STDEV

が用意されています。

ファイルを開いて、セルB81以下に次のように入力します。

以上で、求めたい値すべてが求まりました。

表4-1から一人あたりGDPと1990年人口を取り出したファイルがtab4-1-3.xlsにあります。このファイルを使ってローレンツ曲線を作成しましょう。

まず、ファイルを開き、一人あたりGDPで各項目を並び替えてください。次のようになります。

D列に各国のGDPを計算します。桁数が大きくなるので、1000000で割った値を計算しましょう。

これを下方向へコピーして各国について計算します。

次に、1990年人口とGDPの合計をそれぞれ、セルC80とD80に計算してください。そして、E列とF列に相対人口と相対所得（所得とGDPは同じ意味だと考えてください）を計算してください。次のようになります。

さらに、G列とH列に累積相対人口と、累積相対所得を計算してください。次のようになります。

セルG2からH79を選択し、「挿入(I)」－「グラフ(H)」を選択します。そして、次のように設定して「完了(F)」をクリックします。

次のようなローレンツ曲線が表示されます。目盛り軸などを調整して、きれいなローレンツ曲線に仕上げてみてください。

テキスト表4-1から国名と1990年人口を抜き出したデータがtab4-1-1.xlsにあります。このデータを使って度数分布表とヒストグラムを作成します。

ファイルを開くと次のようなデータが表示されます。

度数分布表の階級の数と幅を決めるために、人口の最大値と最小値を求めます。セルB81,B82に次の数式を入力します。

最大値と最小値がそれぞれ2,039と1,139,060と分かったので、階級を6個とし、階級の上限をそれぞれ

10000

50000

100000

500000

1000000

1500000

としましょう。セルA84以下に次のように入力して下さい。

つぎに、「ツール(T)」－「分析ツール(D)」を選択します。次のようなメニューが現れますので、「ヒストグラム」を選択して「OK」をクリックします。

もし、「ツール(T)」に「分析ツール(D)」が表示されない場合はこのページを参照して下さい。

次のようなダイアログボックスが現れますから、以下のように設定して「OK」をクリックします。

次のようなヒストグラムと、未完成の度数分布表が現れます。

次に度数分布表を完成させましょう。

A列に列を挿入します。

同様にＣ列に列を挿入します。

次のように表を書き換えます。

C列、E列、Ｆ列、Ｇ列を計算式を入力することで埋めて下さい。結果は次のようになります。

これで度数分布表が完成しました。

テキストの表4-1のデータがtab4-1.xlsにExcel形式で保存されています。これを利用して、

1. 1990年、および2025年の人口の上位10カ国を求めて比較しなさい。
2. 人口増加率の上位10カ国を求めて下さい。
3. アジアおよびアフリカの国で、人口増加率が1％以上で一人当たりGDPが1000ドル未満の国の国名、地域、人口増加率、一人当たりGDPを抽出して下さい。