Excel関数いろいろ

こんにちは、C言語のNULLが大文字であることに嫌悪感を抱くかささぎです。

今回はプログラミングではなくExcel関数について書いておきます。
今後プログラムを作るうえで使うかもしれないデータを作ろうと思ったら、割と苦戦しました。
今回は地図帳にあるような都道府県の人口や面積などをまとめた統計データを作りました。

1.はじめに

統計データは下記のHPからいただいてきました。表番号1のやつです。
統計表一覧 政府統計の総合窓口 GL08020103

こちらはCSVなので、Excel形式で保存します。
データはこんな感じ。

自治体ごとにまとめられているので、都道府県のデータにまとめます。

先に完成図を見せます。

海の有無のデータは最後に手入力したので別ですが、それ以外は関数で自動的に入力しています。
その関数の説明をします。

2. VLOOKUP関数

VLOOKUP関数は列で範囲検索を行うものです。行バージョンのHLOOKUP関数というものもあります。
VLOOKUP関数は以下のように定義されています。

=VLOOKUP(検索値 , 範囲 , 列番号 , 検索条件)

検索値は検索する値を入れます(小並感)。
範囲は検索値を検索する列から指定します。返したい値のある列まで指定する必要があります。
列番号は、範囲の先頭を1として、返す値の列の番号を入れます。
検索条件は、一致する値が見つからなかった際に、最も近い値を返すときはTRUE、n/aエラーを返す時はFALSEです。
値が必ず見つかることがわかっているときは、エラー判断しやすいため、後者を使ったほうがいいと思います。

今回の北海道の場合は以下の通りです。

=VLOOKUP("a" , '001_00'!D$14:M$6363 , 4 , FALSE)

都道府県か、自治体かという区分データがあるためそれを利用します。
県のデータはaなので、これを検索条件とします。
県名は統計データのG列に格納されているため、D列から4番目ということで列番号は4です。
列番号を、人口は5、面積は9、人口密度は10に変えると北海道のデータは完成です。
人口密度が単純に人口/面積で計算したのと違うのはなぜですかね？

これで、あとは範囲選択して引っ張って終わりとはなりません。
それをすると以下のようになります。

これは、VLOOKUP関数は検索で最初に見つかった値に対応する値を返すという特性のためです。
これを解決するためには、検索範囲の先頭を移動させてやる必要があります。
それに利用した関数を紹介していきます。

3. MATCH関数

MATCH関数は、値を範囲検索しその相対的なセル位置を返す関数です。
以下のように定義されています。

MATCH(検査値 , 検査範囲 , 照合の種類)

照合の種類は、検索値と一致が0、検索値以下が1、以上が-1です。
例を出すとこんな感じです。

B2から数えて4番目なので合ってますね。

4. OFFSET関数

電子回路...赤点...ｳｯｱﾀﾏｶﾞ
OFFSET関数は基準のセルから指定した分ずらしたセル範囲を返す特殊な関数です。
以下のように定義されています。

=OFFSET(基準 , 行数 , 列数)

基準はずらす前の基準となるセルです。
行数はずらす行数、列数はずらす列数です。
例は以下の通りです。

左上の1から1行2列ずらした8になります。

5. データの完成

2.・3.・4.を元に、検索条件をずらしていって別の都道府県データを取得していきます。
青森県(北海道の一つ下)のデータはこのようになりました。

=VLOOKUP("a", OFFSET('001_00'!D$14, MATCH(B3, '001_00'!G$14:G$6363, 0), 0):'001_00'!M$6363, 5, FALSE)

VLOOKUP関数の検索範囲以外は2.と同じです。
範囲は先頭を、ひとつ前の県のデータとマッチする14行から数えた相対的な値(この場合は1)を、14行(北海道のデータ)にオフセットした値にしています。(説明むずすぎ)
簡潔に言うと、青森県のデータは北海道の次の行から検索、という感じでやっています。
これで完成です。

6. 感想

VLOKUP関数は使ったことがあったのですが、MATCH関数とOFFSET関数は初めてでした。
特にOFFSET関数は汎用性が高そうだと感じました。
しかし、実装より説明のほうが難しい...。
Excelについても、今後便利な関数や機能を見つけたらWriteUPしていくかもしれません。

それでは。