2007年10月アーカイブ

ここでは、散布図上に回帰直線を書き込む便利な方法を紹介します。例として、配付資料のデータを利用します。

このデータから散布図を作成してください。次の図は作成された散布図の一例です。

散布図上の点の上で、マウスを右クリックするとメニューが表示されますので、「近似曲線の追加(R)」を選択します。

表示されるダイアログボックスに「オプション」というタブがありますから、それをクリックし、以下のように設定し、「ＯＫ」ボタンをクリックします。

散布図上に回帰直線と、回帰式、決定係数が表示されます。

散布図を作成するために使ったセルの値を変更すると、自動的に散布図上の点と回帰直線も変化します。いろいろ試してみて下さい。

以前に配布した資料のデータに関する回帰分析を行います。講義では手計算で回帰直線の切片と傾きを求めましたが、今日はExcelを使って計算してみましょう。

データの入力

まず、説明変数 x と被説明変数 y を入力します。

散布図の作成

このデータの散布図を描いて見ましょう。セル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は、のつもりで書いています。

問題

(1) セルD2に適当な数式を入力し、セルD5まで下方向へコピーしましょう。

(2) 同様にセルE2,F2,G2,G2に適当な数式を入力し、下方向へコピーしましょう。

結果は次のようになります。

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というのは平方根を計算するための関数です。

回帰直線を求める

ｘを説明変数、ｙを被説明変数としたときの回帰直線を求めましょう。まず直線の傾きｂをセルB11に求めます。

次に直線の切片ａをセルB12に求めます。

これより、回帰直線は y=2+0.7x であることがわかります。

決定係数の計算

単回帰の場合、決定係数は相関係数の2乗と等しくなりました。セルB13に決定係数を求めましょう。

決定係数は0以上1以下の値をとることを思い出してください。

演習が済んで時間が余っている人は、セルB2からC5に入力したデータの値を変更してみてください。即座に散布図や、回帰直線などが再計算されます。

データの入力

まずは分析するデータを入力します。次の図のように入力してみてください。太い枠がおかれているセルの部分にデータが入力され、Enterキーで入力が確定されます。もし、入力を間違えたら、該当するセルをダブルクリックするとそのセルが編集できます。漢字の入力の仕方がわからない人は、ローマ字でも英語でもかまいません。

合計の計算

次に各県の人口の合計を求めてみましょう。Ｂ９のセルに「=B2+B3+B4+B5+B6+B7+B8」と入力します。Bは小文字のbで構いません。

Enterキーを押せば、Ｂ９のセルに各県の人口の合計が表示されます。「=B2+B3+B4+B5+B6+B7+B8」はB2,B3,...B8のセルの値を足したものをＢ９に計算しなさいという意味になります。

もちろん、この方法でも合計は求められるのですが、合計すべき県の数が47になったらどうでしょう？「=B2+B3+...+B48」と入力しなければならなくなって面倒です。Excelには便利な関数がたくさん用意されていて、これらを使うと効率的に計算ができます。たとえば、合計を求める関数「SUM」を使えば、先ほどの方法より簡単に合計が計算できます。Ｂ９のセルに「=sum(b2:b8)」と入力してみましょう。これは、セルＢ２からＢ８の合計を計算するという意味になります。

先ほどと同じ結果が表示されるはずです。次にコンビニ、美容院、レンタルビデオの合計も求めてみましょう。C9,D9,E9のセルにそれぞれ「=SUM(C2:C8)」、「=SUM(D2:D8)」、「=SUM(E2:E8)」とタイプしてもいいのですが、もっと簡単な方法があります。まず、マウスカーソルをＢ９のところにもっていき、左ボタンを押したままＥ９のところまでマウスをずらします（このような操作をドラッグするといいます）。下の図のようにC9からE9が黒くなるはずです。

マウスボタンを押すのをやめ、「編集(E)」-「フィル(I)」－「右方向へコピー」の順でクリックします。

次のような結果になります。

相対参照と絶対参照

九州（沖縄を除く）全体の人口に占める各県の人口の割合を求めてみましょう。九州全体の人口はB9に求められているので、F2に「=B2/B9」と入力すれば、福岡の人口シェアが求まります。

次に各県の人口シェアを求めます。合計のときと同様に、F2からF9までドラッグします。

「編集(E)」-「フィル(I)」－「下方向へコピー」の順でクリックします。すると、次のように、おかしな結果が表示されるはずです。

F3のセルをマウスでクリックすると、上のほうに「=B3/B10」と表示されています。これが、F3にコピーされた計算式です。私たちが求めたい計算式は「=B3/B9」なのですが、うまくコピーされていません。これは、Excelにおいて、ほかのセルの値を参照する場合、相対参照が標準で使われるからです。相対参照というのは、式を入力しているセルから見て、左（右）にいくつ分、下（上）にいくつ離れているセルを参照するという情報を記憶する方法です。具体的には、F2に「=B2/B9」と入力したわけですが、この場合、このセルは「F2から見て左に4つ目のセルの値を、左に4つ下に7つ離れたセルの値で割る」ということを意味しています。この情報がF3にコピーされたわけですから、「F3から見て左に4つ目のセル（B3)を左に4つ下に7つ離れたセル(B10)で割る」ということになります。

私達が望んでいる式を作るためには、セルを絶対参照する必要があります。絶対参照とは参照するセルを相対的に記憶するのではなく、Ａ列1行から見てどの位置のセルなのかを記憶する方法です。ですから、式の中であるセルが絶対参照された場合、コピーされても参照するセルの場所は変化しません。この絶対参照をするためには、絶対参照したい行または列のはじめに「$」記号をつけて相対参照と区別します。今回の場合、B9をコピーしても変化しないようにしたいので、行を絶対参照します。F2に「=B2/B$9」と入力し、下方向へコピーしてやればいいのです。

結果は次のようになります。

同じように、コンビニ、美容院などについても各県のシェアを計算してみてください。右方向へコピーと下方向へコピーを使うことで、すぐに計算できます。

次に、このデータを使ってグラフを作成します。プルダウンメニュー「挿入(I)」-「グラフ(H)...」の順でクリックします。

すると、次のようなダイアログボックスが表示されます。

ここで、グラフの種類と、その形式を選ぶわけですが、ここでは、縦棒グラフで、３次元の棒がのびるグラフにします。

該当する形式を選択したら「次へ＞」ボタンを押します。次にどのデータの部分を使ってグラフを作成するかを指定します。ここでは、A1からB9までのデータを使うので、マウスをA1からB8までドラッグします。データ範囲の部分が以下のようになり、サンプルグラフが表示されるはずです。

次に「完了(F)」をクリックすると、ワークシート上にグラフが表示されます。

グラフの上を一度クリックして、画面上の棒グラフの小さなボタンをクリックすると、グラフの種類と形式を選ぶ画面が再び現れますから、いろいろなグラフを試してみてください。

例えば、

などが作成できます。

課題：

国税庁のホームページから取得した都道府県別の販売（消費）数量shuzei.xlsがExcel形式で保存されています。このデータから、各都道府県別に

1. 清酒
2. しょうちゅう（計）
3. ビール
4. ウイスキー類

を取り出し、47都道府県の酒類別特化係数を計算しなさい。また、計算された特化係数から読み取れる傾向について論じなさい。

締め切り
　次回講義開始直前

あるグループ(3人)の、1ヶ月あたりの収入と、その使途が次の表に与えられています。

この表を基に、

特化係数を計算して下さい。計算結果は次のようになります。

Aさん、Bさん、Ｃさんの収入の使途の傾向が分かるように、レーダーチャートを作成して下さい。次のようなグラフになるはずです。

次に、オートフィルタを使って、食費に関する特化係数が1以上の人をリストアップしてみましょう。次のような結果になります。

1. 九州全体の人口、病院数、一般診療所数の合計を計算しなさい。
2. 九州各県の千人当たり一般診療所数を計算しなさい。
   
3. 九州全体に占める各県の人口の割合を計算しなさい。同様に病院数、一般診療所数についても割合を計算しなさい。
4. 九州各県の人口を棒グラフで表しなさい。
5. オートフィルタを使って一般診療所数が1000以上の県をリストアップしなさい。
6. 並べ替えを使って、一人当たり一般診療所数が多い県の順にデータを並び替えなさい。

ただいま論文執筆中です。論文では、よく推定された係数の標準誤差を推定値の下段に括弧つきで表示することが多いのですが、これをExcelで手作業で行うと非常に煩雑です。こういうときにはセルの書式設定を上手に使いましょう。

たとえば、次のようなデータがワークシート上にあって、セルB2をカッコつきで表示したいとしましょう。

セルB2で右クリックし、「セルの書式設定」選択します。すると、下のダイアログボックスが開くので、分類(C)でユーザー定義を選択し、種類(T)の下のボックスに「(0.00)」と入力します。

「OK」ボタンをクリックすると、セルB2の数値が小数点以下2桁で表示されます。