2023/7/10
イントロダクション
いわゆるコロプレスマップライクな地図グラフをExcelで作りたいとき,比較的あたらしいExcelでは,“塗り分けマップ”という名の組み込み機能が利用できます。
しかし,この“塗り分けマップ”がユーザーの意図を酌んでくれるのは,国内に限れば都道府県レベルのみ(本記事公開時点)。
したがって,たとえば名古屋市の「区」の水準にて昼夜間人口比を区別してみたいとき,具体的に次のようなコロプレスマップを欲したとすれば,必然的に他のツールやWeb技術に頼るしかありません。
平成27年国勢調査 名古屋の昼間人口(従業地・通学地集計結果) 統計表(第1表,第2表)をもとに計算
もっともたいていの場合,こうした手段の方が見た目により秀麗であったり,インタラクティブなやりとりに卓抜するなど一日の長があるのが現実ですが,ここではそれらの手段を視野に入れられない場合の対応を考えてみたいと思います。
言い換えればExcel周辺のみで実現する方法を試行するのがこの頁の趣旨となります。
免責事項:
- 掲載のコードは一例として提示しています。諸般のデータに対応するものでも,すべての環境で正常に動作することを保証するものでもありません。
- このコードおよびマクロを利用されたこと,あるいはそれに付随する行為により生じた損害,トラブル等につきまして筆者は一切の責任を負いません。
その他特記の必要なこと:
- 座標系をまったく考慮していません。ゆえにユーザーの目測,というか感覚に大きく依存する性格のグラフとなります。
- ここではシーケンシャルなカラースキームのみ扱います。階級にする場合は別の工夫を要します。
以下,サブスクリプション版Excel(ver.2003)を使った具体的な手続きです。
境界データを確保する
今回は,組み込みの「塗り分けマップ」を利用してグラフが描けないケースです。つまり正攻法はムリなので,いくつかの図形(以下「フォーム」)をまとめ,結果としてグラフに見えるよう取り繕うのが無難な選択かと思います。
とはいえ「塗り分けマップ」が利用できないということは,行政区画の境界データも自分で用意する必要があることを意味します。
さて,最初に掲げたとおり,例としてここで描きたいのは,
- 名古屋市
の
- (行政)区別
の
- 昼夜間人口比
といった設定があります。したがってこの場合,少なくとも2. の境界を網羅して1. が構成されるようなデータが必要です。
具体的には,geojsonと呼ばれる形式のそうしたデータを(利用条件等をクリアしたうえで)使用します。
広く公開されている任意のものが利用できますが,ここでは,「国土数値情報ダウンロードサービス(http://nlftp.mlit.go.jp/ksj/index.html)」の,「行政区域データ」から愛知県を対象とした(本記事公開時点で)最新のそれをダウンロードして用いたいと思います。
直前で得たzip形式のファイルを解凍すると,次のようなファイル群を得ることができます。
- KS-META-N03-19_23_190101.xml
- N03-19_23_190101.dbf
- N03-19_23_190101.geojson
- N03-19_23_190101.prj
- N03-19_23_190101.shp
- N03-19_23_190101.shx
- N03-19_23_190101.xml
ここでいう,拡張子がgeojsonのファイル,すなわち
- N03-19_23_190101.geojson
が目的のファイルです。
geojsonの内容をExcelで使えるかたちに
このファイルを任意のテキストエディタで開いてみると,下の図のような構造になっていることが確認できます。
コトをExcelで強行する以上,このgeojsonフォーマットのままでは都合が良くはありません。
極端な話,作図に必要な区画の名称とXY座標のみをExcelで扱いやすい形式,すなわち
こうしたかたちに簡略化して利用したいと思います。
上の図では見えませんが,各区画(ここでいう「名古屋市千種区」「名古屋市中区」など)の末端には1行の空白行を挟んでいます。
いわゆる「前処理」が必要なわけですが,他のツールが使えなければ,Excelによるこれはかなりの面倒を強いられるであろうことが予期できます。
とはいえネットで手に入れられるgeojsonファイルの多くは,ユーザーサイドから明瞭かつコンパクトに設計されていることが多いので,テキストエディタを使って正規表現での文字列置換を繰り返すような,一聞すると乱暴なふうの整形作業が強行できたりもします。したがって,ここではそうした手続き,すなわち正規表現の扱えるテキストエディタを利用した前処理をおこなっていくことにします。
ということで,ここより
- 区画の名称
- 境界のXY座標
の2つの情報のみを,必要な区画(名古屋市16区)においてのみ保持するように言い方を変えれば,不要な文字列をごっそり除きつつデータを整形していきます。なお次の手順からの解説は当該geojsonファイルを使った場合の参考であって,ファイルによって"features"下の"properties"の形容が異なれば,正規表現の記述のしかたもそれらに適応させることが必要となってきます。
以下,整形作業はテキストエディタ「Mery」を使用しての例示になります。
「名古屋市」以外の情報部分を削除します。
- 検索する文字列^(?!.*"名古屋市).*
- 置換後の文字列何も入力しない
"properties"の記述から「名古屋市○○区」以外の文字列を削除します。
- 検索する文字列\{ "type": "Feature", "properties": { "N03_001": "愛知県", "N03_002": null, "N03_003": "(.*?)", "N03_004": "(.*?)",.*?( \[ \[ \[ )
- 置換後の文字列\1\2,
\], \[ 記号を改行と置き換えます。
- 検索する文字列( \], \[ )
- 置換後の文字列\n,
残る雑多な記号を削除し
- 検索する文字列( \] \] \] \} \},)|^\n
- 置換後の文字列何も入力しない
文字列"名古屋市"の前に改行を挿入します。
- 検索する文字列名古屋市
- 置換後の文字列\n名古屋市
これにより,データを下のようなかたちに整形することができました。
直前に加工したテキストエディタの文字列すべてを選択・コピーし,Excel側の任意のシート・セルA1にテキストファイルウイザードを使用を使ってペーストします。
これによりウイザードが立ち上がるので,そのまま次へ進み
区切り文字のカンマにチェックを入れて処理を完了します。
意図したとおりシート上にペーストできたので,
セルB1, C1に見出しを書き加えておきます(順に経度,緯度)。
コードのコピペ
下のコードをすべて選択し,コピーします。
Option Explicit Option Base 1 Sub Step1_DrawFreeForms() ' *** hitorimarketing.net/tools/vba/choroplethmap-macro.html ' *** by hawcas 2020, b.200412 Application.ScreenUpdating = False Dim x As Long, y As Long ' Counter ActiveSheet.UsedRange.Select Dim rowEnd As Long ' 最終行番号 rowEnd = Selection.Rows().Count Dim gNL() As Long ' 行政区画名の格納場所(行目) Dim nOG As Integer ' 行政区画名の総数(重複含む) nOG = rowEnd - Application.WorksheetFunction.Count(Range("b:b")) ReDim gNL(nOG) Range("a1").Select For y = 1 To nOG Selection.End(xlDown).Select gNL(y) = Selection.Row Range("e5").Offset(y, 0).Value = Selection.Value Next y Dim tgtRange Dim n As Long ' XYデータのサイズ For x = 1 To nOG If x <> nOG Then Range(Cells(gNL(x), 2), Cells(gNL(x + 1) - 2, 3)).Select Else Range(Cells(gNL(x), 2), Cells(rowEnd, 3)).Select End If Set tgtRange = Selection n = tgtRange.Rows.Count With ActiveSheet.Shapes.BuildFreeform(msoEditingAuto, tgtRange(1, 1), tgtRange(1, 2)) For y = 2 To n .AddNodes msoSegmentLine, msoEditingAuto, tgtRange(y, 1), tgtRange(y, 2) Next y .ConvertToShape.Select End With Selection.Name = Cells(gNL(x), 1).Value Next x Range("A1").Select Application.ScreenUpdating = True End Sub Sub Step2_Coloring() ' *** hitorimarketing.net/tools/vba/choroplethmap-macro.html ' *** by hawcas 2020, b.200412 Application.ScreenUpdating = False ActiveSheet.Shapes.SelectAll Selection.Ungroup Dim tgt, obj Range("e5").CurrentRegion.Select Set tgt = Selection Dim n As Long, i As Long n = tgt.Rows.Count - 1 Dim rgbR As Integer, rgbG As Integer, rgbB As Integer ' rgb(rgbR, rgbG, rgbB) For i = 1 To n ActiveSheet.Shapes.Range(Array(Range("e5").Offset(i, 0).Value)).Select Set obj = Selection rgbR = Range("h5").Offset(i, 0).Value rgbG = Range("i5").Offset(i, 0).Value rgbB = Range("j5").Offset(i, 0).Value With Selection.ShapeRange.Fill .ForeColor.RGB = RGB(rgbR, rgbG, rgbB) End With Set obj = Nothing Next i ActiveSheet.Shapes.SelectAll Selection.Group Application.ScreenUpdating = True End Sub
VBEで標準モジュールを追加し,コードをコードウインドウにペーストします。
コードが導入出来たので,VBEを閉じます。
では,ここからマクロの具体的な使い方です。
“DrawFreeForms”
マクロ「Step1_DrawFreeForms」を実行します。
一連の処理を終えると,下図の位置に行政区画の名称(元データによっては例のように重複もあり)が並べられ,
このあたりの位置に,名称と同じ個数のフリーフォームが吐き出されます。極めて小さいですが。
これら複数個のフォームを選択するために,オブジェクトの選択から,
当該フォーム群を囲むすこし大きな四角形を描くように,マウスでフォーム周辺をドラッグします。
下図,強調部分のハンドルを右下方にドラッグしながら,
適度な大きさを得てドラッグを停止します。
これらフォームは,この時点で天地が逆になっています。したがって,上下反転にて修正を加えます。
シートの上のフォームを,重ならないよう適度にばらします。
これらをジグソーパズルのピースに見立て,組み合わせていきます(念のため,ネタじゃないです)。
このとき,大きな移動をともなう作業はマウスで,微調整的作業は方向キーでおこなうとやりやすいかと思います。
とはいえ,土地勘などをもたない地域の地図をつくるときには,“ジグソーパズル”も難易度が上がります。そんなときは,BC列をソースに直線散布図を描いてやると,グラフを青写真代わりに使うことができます。
組み立てを終えると,このようになります。
この名古屋市の場合,「港区」に限っては4つのフォームが存在します。
このように同一の行政区画が異なるフォームで構成されているときは,同じ名前のフォームをすべてグループ化して,グループ名には元のフォームと同じ名前を付けるようにしておきます(下図参照)。
あまりに微細なパーツは趣旨と照らして影響なければ削除してしまうのもひとつの手です。
その上で,すべてのフォームをグループ化しておきます(色を塗るときにグループ化の解除と再設定を繰り返すので,このグループの名前は次の工程で都度変わります。よってデフォルトの名称でOKです)。
シートE列の行政区画の重複を解決します。
具体的には,4つある「港区」のうち3つを削除し,以下に続く区画名を上方向に詰めていきます。
シートの上に,あらたにいくつかの見出しを設置しておきます。
彩色時のグラデーションについて,はじまり・おわりの2色を決めます。COLOR1に“はじまり”色を,COLOR2に“おわり”色をRGBごとに指定します。
cf. グラデーション配色の計算方法
ここで例として指定した色は,実際には下図のようになります。
「VALUE」列に,そもそものグラフにしたいデータを入力します。
このデータとグラデーションの範囲の色とを関連づけていく方法がいくつかあります。具体的には,COLOR1とCOLOR2は,データの
- MIN, MAX
とするか,
- 0, MAX
とするか,あるいは基準値など任意の値と対応させて
- 0, 5
とするなどが考えられます。
この例ではいちばん最初の選択肢を採るとして,元データを「RATIO」列で相対化します。
- CELL G6 =(F6-MIN($F$6:$F$21))/(MAX($F$6:$F$21)-MIN($F$6:$F$21))
「RATIO」データの別にカラーをRGBで用意します。
- CELL H6 =ROUND((H$3-H$2)*$G6+H$2, 0)
- CELL I6 =ROUND((I$3-I$2)*$G6+I$2, 0)
- CELL J6 =ROUND((J$3-J$2)*$G6+J$2, 0)
“Coloring”
これまでの設定を済ませたあと,マクロ「Step2_Coloring」を実行すると,
シートの上のRGBデータを参照して,既存のフォームにカラーを反映させることができます。
任意に書式や図形の大きさ,縦横比などを修正したのち,
「四角形」などで別途凡例を添付すれば目的とするグラフの完成です。
なお,「VALUE」あるいは「RATIO」列や,「COLOR1」「COLOR2」のデータを変更(つまり更新)したい場合,再び“ジグソーパズル”から始める必要はありません。再度マクロ「Step2_Coloring」を実行すればフォームの塗り替えは可能です。
また作成したフォームは,PowerPointやWordにコピペして使用することもできます。