Difference between revisions of "Maps"

 
Line 1: Line 1:
 
==Introduction==
 
==Introduction==
  
XLCubed Maps are an optional extension which allow plotting geographic data directly in Excel and publishing to the XLCubed Web server.
+
XLCubed Maps allow plotting geographic data directly in Excel and publishing to the XLCubed Web server.
  
 
You can plot the data locations, and optionally set the colour and size of the plotted points to show values.
 
You can plot the data locations, and optionally set the colour and size of the plotted points to show values.
  
[[File:MapSample.png|center|thumb|300px]]
+
[[File:Map1.PNG|center|600px]]
  
==UI==
+
==Setup==
  
 
To create a Map from Excel simply highlight the relevant data and select Maps from the Visualise drop-down button in the XLCubed menu.
 
To create a Map from Excel simply highlight the relevant data and select Maps from the Visualise drop-down button in the XLCubed menu.
  
  
[[File:Ribbon.png|400px]]
+
[[File:Map-ribbon.PNG|center|600px]]
  
 +
You will then be presented with the map properties dialog.
 +
 +
[[File:Map-dialog.PNG|center|400px]]
 +
 +
===Map Range===
  
 
XLCubed expects your data to be arranged in a particular way to create maps.
 
XLCubed expects your data to be arranged in a particular way to create maps.
Line 19: Line 24:
 
Generally there should be several columns of data as described below.
 
Generally there should be several columns of data as described below.
  
===Known Latitude and Longitude===
+
====Known Latitude and Longitude====
  
 
The columns should contain the following.
 
The columns should contain the following.
Line 48: Line 53:
 
This would be a typical set of mapping data:
 
This would be a typical set of mapping data:
  
[[File:MapData1.png|450px]]
+
[[File:Map-data.PNG|450px]]
 +
 
 +
====Look Up Latitude/Longitude====
  
 
If you do not know the latitude/longitude you can look it up using the {{Code|Extras -> Lookup Latitude/Longitude}} menu item.
 
If you do not know the latitude/longitude you can look it up using the {{Code|Extras -> Lookup Latitude/Longitude}} menu item.
Line 56: Line 63:
 
[[File:LookupLatLng.png]]
 
[[File:LookupLatLng.png]]
  
===Named places===
+
====Named places====
 +
 
  
 
Named places uses a predefined set of codes to identify locations. You can then plot either the outline of the location of a point in its center.
 
Named places uses a predefined set of codes to identify locations. You can then plot either the outline of the location of a point in its center.
Line 83: Line 91:
 
|}
 
|}
  
====Display====
+
For named places you can choose to display the outline of the place rather than just a marker at its centre. As of [[Version 7.6]] you can customise the transparency of the shape and border.
 +
 
 +
[[File:Map-place-names-properties.PNG|center|450px]]
 +
 
 +
 
 +
[[File:Map-place-names.PNG|center|600px]]
  
For named places you can choose to display the outline of the place rather than just a marker at its centre. As of [[Version 7.6]] you can customise the transparency of the shape and border.
 
  
===Map controls===
+
==Map controls==
 
{| class="wikitable"
 
{| class="wikitable"
 
! Button
 
! Button
Line 108: Line 120:
 
(Requires IE9+ in Excel, and IE9+, Firefox or Chrome on the web)
 
(Requires IE9+ in Excel, and IE9+, Firefox or Chrome on the web)
 
|}
 
|}
 +
 +
 +
==Properties==
 +
Right clicking on the map and selecting 'Edit Map' brings up the map properties dialog.
 +
 +
===Colours===
 +
 +
If your data includes a colour column, this column can contain numbers or text, and there are three ways the values could drive the colour.
 +
 +
'''Colour Gradient''':  if you have numbers the colours can be from a colour gradient. E.g. Red-Green shading for negative/positive margins.
 +
 +
'''Categorise''': you can categorise each point with a number or text. For example, if mapping national stores, the colour column could contain the store type. Each store type would then be plotted a particular colour.
 +
 +
'''Pie Charts''': see [[Maps#Pie charts|below]].
 +
 +
'''Named Colour''': the column could contain specific colours, using [[Colours| named colours]] or Html syntax such as {{code|#ff0000}}. This allows you maximum flexibility to drive the colour using any criteria you choose.
 +
 +
'''Icon''': you can map each location with an icon. The colour column would need to contain the file name of the icon to be used.
 +
 +
===Sizes===
 +
 +
If no size column is found then all markers will be the default size specified in the map properties.
 +
 +
If a size column is included then the size of the marker will be between the Low and High value sizes specified in the properties screen. The exact marker size is calculated by interpolating between these values.
  
 
===Output cells===
 
===Output cells===
Line 116: Line 152:
 
If you turn on multi-select and pick an output range rather than a cell you can output multiple values. Drawing tools appear on the map to allow you to pick many points, and OK the selection.
 
If you turn on multi-select and pick an output range rather than a cell you can output multiple values. Drawing tools appear on the map to allow you to pick many points, and OK the selection.
  
==Colours==
 
 
This column can contain numbers or text, and there are three ways the values could drive the colour.
 
 
Firstly, if you have numbers the colours can be from a colour gradient. E.g. Red-Green shading.
 
 
Secondly, you can categorise the point. For example with stores the colour column could contain the store type. Each store type would then be plotted a particular colour.
 
 
Finally the column could contain specific colours, using [[Colours| named colours]] or Html syntax such as {{code|#ff0000}}. This allows you maximum flexibility to drive the colour using any criteria you choose.
 
  
All of these options are configured in the map properties screen. Right click the map to bring up the screen.
 
  
 
===Pie charts===
 
===Pie charts===
Line 134: Line 160:
  
 
[[File:MapPie.png|center|thumb|300px]]
 
[[File:MapPie.png|center|thumb|300px]]
 
==Sizes==
 
 
If no size column is found then all markers will be the default size specified in the map properties.
 
 
If a size column is included then the size of the marker will be between the Low and High value sizes specified in the properties screen. The exact marker size is calculated by interpolating between these values.
 
  
 
==View==
 
==View==

Latest revision as of 14:30, 23 May 2018

Introduction

XLCubed Maps allow plotting geographic data directly in Excel and publishing to the XLCubed Web server.

You can plot the data locations, and optionally set the colour and size of the plotted points to show values.

Map1.PNG

Setup

To create a Map from Excel simply highlight the relevant data and select Maps from the Visualise drop-down button in the XLCubed menu.


Map-ribbon.PNG

You will then be presented with the map properties dialog.

Map-dialog.PNG

Map Range

XLCubed expects your data to be arranged in a particular way to create maps.

Generally there should be several columns of data as described below.

Known Latitude and Longitude

The columns should contain the following.

Column Description
Map Name Title of the map, a map will be created for each different name. (For example to create a map for each year.)
Location Name Title of the data point, shown when you hover over the point.
Latitude Latitude of the point.
Longitude Longitude of the point.
Colour (optional) Defines the colour of the point plotted, can be a number or text. If no column is selected then all points will be the same colour. See Colours for more information.
Size (optional) Defines the size of the point, this must be a number. If no column is selected then all points will be the same size.

This would be a typical set of mapping data:

Map-data.PNG

Look Up Latitude/Longitude

If you do not know the latitude/longitude you can look it up using the Extras -> Lookup Latitude/Longitude menu item.

Highlight the place names and select the menu item. This can take some time, so is not available as a function. It is recommended you look up the locations once and then reference them, for example using the VLookup() formula.

LookupLatLng.png

Named places

Named places uses a predefined set of codes to identify locations. You can then plot either the outline of the location of a point in its center.

XLCubed comes with defined outlines for many common requirements.

You may also add custom sets of locations, to do this please contact support@xlcubed.com.

The columns for the report should contain the following.

Column Description
Map Name Title of the map, a map will be created for each different name. (For example to create a map for each year.)
Location Name Name of the place to plot.
Colour (optional) Defines the colour of the point plotted, can be a number or text. If no column is selected then all points will be the same colour. See Colours for more information.
Size (optional) Defines the size of the point, this must be a number. If no column is selected then all points will be the same size. (Does not apply if plotting polygons.)

For named places you can choose to display the outline of the place rather than just a marker at its centre. As of Version 7.6 you can customise the transparency of the shape and border.

Map-place-names-properties.PNG


Map-place-names.PNG


Map controls

Button Description
Zoom in.png Zoom in.
Zoom out.png Zoom out.
Zoom tofit.png Zoom to Fit.
Earth.png Toggle map display type. Switch between road and satellite views.
FadeBW.png Toggle fade map. Fading the map makes the data points more prominent.

(Requires IE9+ in Excel, and IE9+, Firefox or Chrome on the web)


Properties

Right clicking on the map and selecting 'Edit Map' brings up the map properties dialog.

Colours

If your data includes a colour column, this column can contain numbers or text, and there are three ways the values could drive the colour.

Colour Gradient: if you have numbers the colours can be from a colour gradient. E.g. Red-Green shading for negative/positive margins.

Categorise: you can categorise each point with a number or text. For example, if mapping national stores, the colour column could contain the store type. Each store type would then be plotted a particular colour.

Pie Charts: see below.

Named Colour: the column could contain specific colours, using named colours or Html syntax such as #ff0000. This allows you maximum flexibility to drive the colour using any criteria you choose.

Icon: you can map each location with an icon. The colour column would need to contain the file name of the icon to be used.

Sizes

If no size column is found then all markers will be the default size specified in the map properties.

If a size column is included then the size of the marker will be between the Low and High value sizes specified in the properties screen. The exact marker size is calculated by interpolating between these values.

Output cells

In the properties screen you can set up "output cells" for the map and location names.

With these selected clicking a data point will update Excel. This can then be used to drive a detailed report for the selected point.

If you turn on multi-select and pick an output range rather than a cell you can output multiple values. Drawing tools appear on the map to allow you to pick many points, and OK the selection.


Pie charts

Starting in Version 7.6 you can plot pie charts instead of simple points. In this case, columns after the location column will be used as data values for the pie segments.

By default the size of the pie chart will be based on the total of all the data values. You can optionally set the last column to be the pie chart size. This allows you to control the size based on another parameter, or make all the charts the same size (by filling the final column with 1s).

MapPie.png

View

These options are new in Version 7.6.

Initial view

Defines the startup view of the map. The options are

  • Zoom to fit - Matches existing behaviour, map is zoomed and scrolled to show all data points.
  • Save on close - The zoom and scroll are saved with the workbook.
  • Custom view - You decide the zoom and scroll, click the "Set" button to select the current view.

Custom style

These settings allow you to customise the map layers, such as road, water and place names.

You can adjust all the layers at once, or individually. Click the "Apply" to see a preview of what the map will look like.

Security

In some corporate environments the following urls may need to be added to sites trusted for javascript

  • xlcubedweb.com
  • xlcubedmapping.com
  • maps.googleapi.com