Create your own mapping templates - Excel Add-In

<p>With this add in it is possible to create map templates from GIS files in KML format, and create choropleths with them.</p>
<p>Providing you have access to KML format map boundary files, it is possible to create your own quick and easy choropleth maps in Excel. The KML format files can be converted from 'shape' files. Many shape files are available to download for free from the web, including from Ordnance Survey and the London Datastore. Standard mapping packages such as QGIS (<a href="https://www.qgis.org/en/site/forusers/download.html">free to download</a>) and ArcGIS can convert the files to KML format.</p>
<p>A sample of a KML file (London wards) can be downloaded from this page, so that users can easily test the tool out.</p>
<p>Macros must be enabled for the tool to function.</p>
<p>When creating the map using the Excel tool, the 'unique ID' should normally be the area code, the 'Name' should be the area name and then if required and there is additional data in the KML file, further 'data' fields can be added. These columns will appear below and to the right of the map. If not, data can be added later on next to the codes and names.</p>
<p>In the add-in version of the tool the final control, 'Scale (% window)' should not normally be changed. &nbsp;With the default value 0.5, the height of the map is set to be half the total size of the user's Excel window.</p>
<p>To run a choropleth, select the menu option 'Run Choropleth' to get this form.</p>
<p>To specify the colour ramp for the choropleth, the user needs to enter the number of boxes into which the range is to be divided, and the colours for the high and low ends of the range, which is done by selecting coloured option boxes as appropriate. &nbsp;If wished, hit the 'Swap' button to change which colours are for the different ends of the range. Then hit the 'Choropleth' button.</p>
<p>The default options for the colours of the ends of the choropleth colour range are saved in the add in, but different values can be selected but setting up a column range of up to twelve cells, anywhere in Excel, filled with the option colours wanted. &nbsp;Then use the 'Colour range' control to select this range, and hit apply, having selected high or low values as wished. &nbsp;The button 'Copy' sets up a sheet 'ColourRamp' in the active workbook with the default colours, which can just be extended or deleted with just a few cells, so saving the user time.</p>
<p>The add-in was developed entirely within the Excel VBA IDE by <a href="http://lund.co.uk/category/it/" target="_blank">Tim Lund</a>. He is kindly distributing the tool for free on the Datastore but suggests that users who find the tool useful make a donation to the <a href="http://lund.co.uk/cycling-for-shelter-with-annie/">Shelter</a> charity.&nbsp;It is not intended to keep the actively maintained, but if any users or developers would like to add more features, email the author.&nbsp;</p>
<p><strong>Acknowledgments</strong></p>
<p>Calculation of Excel freeform shapes from latitudes and longitudes is done using calculations from the <a href="https://www.ordnancesurvey.co.uk/business-and-government/help-and-support/navigation-technology/os-net/coordinate-spreadsheet.html">Ordnance Survey</a>.&nbsp;</p>

借助此Excel加载项，可基于KML格式的地理信息系统（GIS）文件创建地图模板，并以此生成分级统计图。
若您已获取KML格式的地图边界文件，即可在Excel中快速便捷地自制分级统计地图。KML格式文件可由形状文件（Shapefile）转换而来。网络上有大量形状文件可免费下载，来源包括英国地形测量局（Ordnance Survey）与伦敦数据仓库（London Datastore）。标准制图软件如QGIS（可免费下载）与ArcGIS，均可将文件转换为KML格式。
您可从本页面下载伦敦选区KML示例文件，以便快速测试该工具的使用效果。
使用此工具需启用宏。
使用Excel工具制作地图时，通常需将"唯一标识符"设为区域编码，"名称"设为区域名称；若KML文件中包含额外数据，还可添加更多"数据"字段。这些列将显示在地图的下方与右侧。若未自动生成，也可后续在编码与名称旁手动添加数据。
在该工具的加载项版本中，最终控件"缩放比例（窗口占比）"通常无需修改。默认值0.5表示将地图高度设置为Excel窗口总尺寸的一半。
如需生成分级统计地图，选择菜单中的"生成分级统计地图"选项即可调出对应界面。
如需指定分级统计地图的色阶，用户需输入数据区间划分的分段数量，并选择数据区间高低两端对应的颜色，可通过勾选对应颜色的复选框完成设置。若有需要，可点击"交换"按钮调换高低两端的配色，随后点击"分级统计"按钮即可生成地图。
分级统计地图色阶两端的默认配色已保存至加载项中，用户也可自定义配色：在Excel任意位置设置最多包含12个单元格的列区域，填入所需的配色，随后通过"色阶范围"控件选择该区域，并根据需求选择高低值后点击应用。点击"复制"按钮可在当前工作簿中新建名为"ColourRamp"的工作表，内置默认配色，用户仅需少量单元格即可对其进行扩展或修改，以此节省操作时间。
本加载项由蒂姆·伦德（Tim Lund）完全基于Excel VBA集成开发环境（IDE）编写。他将该工具免费发布于伦敦数据仓库，并建议认为工具实用的用户向Shelter慈善机构捐赠。本工具不会持续维护，若有用户或开发者希望添加更多功能，可通过邮件联系作者。
致谢
基于经纬度计算Excel自由形状的相关算法，源自英国地形测量局公开的计算方法。