情報処理技法（リテラシ）II 第2回

索引

Excel（1）

Excelによる順位付け

レポート作成や論文作成では、統計データが重要です。ただし、うまく統計データが見つかっても、そのまま使えるわけではありません。データの取り出し、組み合わせ、合計や割合の計算といった、「データ加工」とよばれる作業が必要になります。

ここで、過疎化対策についてレポートを書いているとします。具体的にどこが過疎化しているかを知るために、都道府県の人口データと面積データを入手し、人口密度と人口増減率を計算し、それらを分かりやすくまとめてみます。

一般的な統計データの探し方は、回を改めて説明します。今回は、総務省統計局の「社会生活統計指標−都道府県の指標−2022」を利用します。

https://www.stat.go.jp/data/shihyou/naiyou.html

を開き、「II 基礎データ」の右の「e-Stat」アイコンをクリックすると、ダウンロードできるExcelファイルの一覧が表示されます。「人口・世帯」の「EXCEL」アイコンをクリックすると、Excelファイルがダウンロードされます。「自然環境」の「EXCEL」アイコンをクリックすると、もう1つExcelファイルがダウンロードされます。

都道府県の人口データは、「人口・世帯」のExcelファイルの「総人口」の部分を使います。 2010年、2015年、2019年の3年分のデータがありますが、2019年は推計なので使いません。都道府県の面積データは、「自然環境」のExcelファイルの「総面積（北方地域及び竹島を除く）」の部分を使います。 2010年、2015年、2020年の3年分のデータがありますが、2015年の人口に対しては2015年の面積を使います。

一般的に、ダウンロードしたExcelファイルを直接いじると、うまくいきません。作り込まれた表ほど、セルの書式やセルの結合が多用されて、思ったようには直せないものです。それよりは、新しくExcelファイルを開き、そこに必要なデータをコピー・アンド・ペーストしたほうが、うまくいきます。そのとき、「形式を選択して貼り付け」を選んで、余計な書式情報を取り除いたほうがよいです。

それでは、ファイル→新規→空白のブック、とクリックして、新しくExcelファイルを開いてください。セルA1に「都道府県の人口密度」と入力し、セルA2からF2に、「都道府県」、「人口（2015年）」、「面積（2015年）」、「人口密度」、「順位」、「5段階評価」と入力します。

順位表の作成（1）

「人口・世帯」のExcelファイルの「北海道」から「沖縄県」までをドラッグし、右クリックして「コピー」をクリックし、新しいExcelファイルのセルA3を右クリックして、「形式を選択して貼り付け」をクリックしてください。「形式を選択して貼り付け」ウィンドウが開くので、「値」ラジオボタンをクリックし、OKボタンをクリックします。

順位表の作成（2）

これで、余計な書式情報が取り除かれ、文字データのみが貼り付けられます。

順位表の作成（3）

次に、人口データをコピー・アンド・ペーストします。「人口・世帯」のExcelファイルの「総人口」の2015年のデータ（一番上は全国なので注意）をドラッグし、右クリックして「コピー」をクリックし、新しいExcelファイルのセルB3を右クリックして、「形式を選択して貼り付け」をクリックしてください。「形式を選択して貼り付け」ウィンドウが開くので、「値」ラジオボタンをクリックし、OKボタンをクリックします。これで、余計な書式情報が取り除かれ、数値データのみが貼り付けられます。

順位表の作成（4）

貼り付けられた数値には、3桁ごとのコンマがないので、書式を設定します。セルB3からセルB49までドラッグし、右クリックして「セルの書式設定」をクリックしてください。「セルの書式設定」ウィンドウが開くので、「表示形式」タブをクリックし、「数値」をクリックし、「小数点以下の桁数」を0にし、「桁区切り（,）を使用する」をオンにして、OKボタンをクリックしてください。

順位表の作成（5）

すると、3桁ごとのコンマが表示されます。

順位表の作成（6）

同じようにして、面積データをコピー・アンド・ペーストします。「自然環境」のExcelファイルの「総面積（北方地域及び竹島を除く）」の2015年のデータ（一番上は全国なので注意）をドラッグし、右クリックして「コピー」をクリックし、新しいExcelファイルのセルC3を右クリックして、「形式を選択して貼り付け」をクリックしてください。「形式を選択して貼り付け」ウィンドウが開くので、「値」ラジオボタンをクリックし、OKボタンをクリックします。これで、余計な書式情報が取り除かれ、数値データのみが貼り付けられます。

順位表の作成（7）

貼り付けられた数値には、3桁ごとのコンマがないので、書式を設定します。セルC3からセルC49までドラッグし、右クリックして「セルの書式設定」をクリックしてください。「セルの書式設定」ウィンドウが開くので、「表示形式」タブをクリックし、「数値」をクリックし、「小数点以下の桁数」を0にし、「桁区切り（,）を使用する」をオンにして、OKボタンをクリックしてください。すると、3桁ごとのコンマが表示され、小数点以下が表示されなくなります。

順位表の作成（8）

次に、人口密度を計算します。人口密度は「人口÷面積」で求められます。 Excelは、セルに数式を入力すると、その数式を計算してくれますが、掛け算は「*」割り算は「/」を使います。では、セルD3に「=B3/C3」と入力してください。これで、北海道の人口密度、すなわち1平方キロメートルあたりの人口が求められます。

順位表の作成（9）

セルD3をクリックし、右下のフィルハンドルを下にドラッグすると、数式がコピーされます。これで、他の都道府県の人口密度も求められます。

順位表の作成（10）

計算結果には、3桁ごとのコンマがないので、書式を設定します。セルD3からセルD49までドラッグし、右クリックして「セルの書式設定」をクリックしてください。「セルの書式設定」ウィンドウが開くので、「表示形式」タブをクリックし、「数値」をクリックし、「小数点以下の桁数」を0にし、「桁区切り（,）を使用する」をオンにして、OKボタンをクリックしてください。すると、3桁ごとのコンマが表示され、小数点以下が表示されなくなります。

順位表の作成（11）

都道府県の人口密度が計算できましたが、47個の数値を眺めても、東京や大阪は人口密度か高い、北海道や岩手は人口密度が低い、くらいしか理解できません。データを理解しやすくする1つの方法は、順位付けをすることです。

ExcelにはRANKという関数があり、これで順位付けができます。使い方は、

=RANK(数値,参照,順序)

で、「参照」の範囲の中で「数値」が何位かを求めてくれます。「順序」を0にすると大きい順、1にすると小さい順になります。「参照」の範囲は、普通は絶対参照にします。

それでは、セルE3に

=RANK(D3,$D$3:$D$49,0)

と入力してください。絶対参照のドルマークは、範囲指定をしてからF4キーを押すと付きます。（Macの場合は、commandキーを押しながらTキーを押します。） 47と表示されれば、北海道の人口密度は47位ということです。

順位表の作成（12）

セルE3をクリックし、右下のフィルハンドルを下にドラッグすると、数式がコピーされます。これで、他の都道府県の順位も求められます。人口密度そのものより、順位を見るほうが、理解しやすくなります。

順位表の作成（13）

Excelによる5段階評価

順位付けの他に、データを理解しやすくする方法は、5段階評価をすることです。 5という数字に特別な意味はありませんが、人口密度の場合は、「低い」、「やや低い」、「普通」、「やや高い」、「高い」、となります。ここで問題になるのが、区切りの値です。これは、最小値、中間値、最大値を参考にして、分かりやすく区切ります。ここで中間値とは、47個のデータの場合は24位のデータです。また、区切りの幅は必ずしも等間隔とは限りません。何倍、何倍という区切りもあり得ます。

この場合は、最小値（47位）が69、中間値（24位）が270、最大値（1位）が6,169なので、以下のように区切ってみます。およそ3倍、3倍という区切りです。

人口密度の区切りと評価

区切り	評価
30以上100未満	低い
100以上300未満	やや低い
300以上1,000未満	普通
1,000以上3,000未満	やや高い
3,000以上10,000未満	高い

セルH2に「区切り」、セルJ2に「評価」と入力します。そして、セルH3より下に、区切りの数値を小さい順に入力します。セルI3より下に、「以上」を繰り返し入力します。セルJ3より下に、評価の文字列を入力します。

順位表の作成（14）

ExcelにはVLOOKUPという関数があり、これで区切りと評価の表から、数値の評価を探すことができます。使い方は、

=VLOOKUP(検索値,範囲,列番号,検索方法)

で、「検索値」を「範囲」の1列目から探し、見つかればその範囲の「列番号」の値を返します。「検索方法」を0にすると、見つからなかったらエラーです。「検索方法」を1にすると、見つからなかったら、それ未満の最も近い区切り値を探します。「範囲」は、普通は絶対参照にします。

注意: VLOOKUP関数で「検索方法」を1にする場合、「範囲」の数値は小さい順に並べてください。

それでは、セルF3に

=VLOOKUP(D3,$H$3:$J$7,3,1)

と入力してください。「低い」と表示されれば、北海道の人口密度は低いということです。

順位表の作成（15）

セルF3をクリックし、右下のフィルハンドルを下にドラッグすると、数式がコピーされます。これで、他の都道府県の評価も求められます。順位と同様に、評価を見るほうが、理解しやすいです。

順位表（1）

人口密度の次は、人口増減率です。まず、セルL1に「都道府県の人口増減率」と入力し、セルL2からQ2に、「都道府県」、「人口（2010年）」、「人口（2015年）」、「人口増減率」、「順位」、「5段階評価」と入力します。人口密度のときと同じように、セルL3から下に都道府県名を貼り付け、セルM3から下に2010年の人口データを貼り付け、セルN3から下に2015年の人口データを貼り付けます。

順位表の作成（16）

次に、人口増減率を計算します。人口増減率は「（2015年の人口−2010年の人口）÷2010年の人口」で求められます。セルO3に「=(N3-M3)/M3」と入力してください。これで、北海道の人口増減率、すなわち5年間で人口がどのくらい増えたかが求められます。

順位表の作成（17）

セルO3をクリックし、右下のフィルハンドルを下にドラッグして、他の都道府県の人口増減率も求めます。人口増減率の書式設定は、パーセンテージの小数点以下2桁にします。「セルの書式設定」ウィンドウで、「表示形式」タブをクリックし、「パーセンテージ」をクリックし、「小数点以下の桁数」を2にして、OKボタンをクリックしてください。

順位表の作成（18）

すると、パーセンテージの小数点以下2桁になります。

順位表の作成（19）

順位付けも、人口密度のときと同じようにします。

順位表の作成（20）

5段階評価をするために、区切りの値を考えます。最小値（47位）が−5.79%、中間値（24位）が−2.26%、最大値（1位）が2.93%なので、以下のように区切ってみます。この場合は、パーセンテージの2ポイント間隔です。

人口増減率の区切りと評価

区切り	評価
−7%以上−5%未満	とても減少
−5%以上−3%未満	減少
−3%以上−1%未満	やや減少
−1%以上1%未満	変わらない
1%以上3%未満	やや増加