How to make a data map from a data source in Excel

Source: Internet
Author: User

On this issue, the method of making the data map is not novel, in general there are a few types of scenarios:

A class of scenarios: making directly in Excel

Advantages: Personal Small Data volume application is more convenient and simple

cons : Need to be familiar with VBA, and more powerful features require high levels of VBA

1. Draw Map Graphics + VBA macro Language

idea: " freeform " draw map ; each " freeform " " freeform " assignment ; using to " freeform " " Operation ,

Prepare a picture of the map you need, available on-line, downloadable

Then use the insert to draw the polygon picture to stroke the area in the map


Selected areas can be modified in the upper-left corner of the name


The data in the data table is associated with the regions in the map, and VBA is used here .

Code:

Sub Provrefill ()      ActiveSheet.Shapes.Range (Array ("Shandong")). Select with        Selection.ShapeRange.Fill        . Visible = MsoTrue        . Forecolor.objectthemecolor = MsoThemeColorAccent1        . Forecolor.tintandshade = 0        . forecolor.brightness = -0.5        . Transparency = 0        . Solid    End with    End Sub

This code is to modify the color of the selected area of the map, other functions similar, understand the VBA will feel simple, do not understand the Baidu can be self-.

2.EXCEL Plugin Integration

There are many such plugins, recommended power Map for Excel, after installation, select the data region, start on the line



Class II Scenario: other software

Advantages: The map is integrated, can connect to the database, already has the powerful big data quantity processing has the superiority

Cons: custom development requires a higher level of staffing and is more difficult

This kind of software is usually the data visualization software, can use the EXCEL data source, now more common is the report tool and so-called Business intelligence Big Data tool.

Not much to say, people may feel unfamiliar, that is directly on the example.

The Finereport (the most powerful and practical reporting tool ) is introduced here.

Display data map is a cinch, the key is the map drilling function.

The so-called drilling is: for example, you click Shandong Province, to enter the Shandong province map, show the data of Shandong province, data display methods can use other charts, such as bar chart, bubble chart and so on.



Detailed Steps :

1. Prepare the data source

Import the data from Excel into this Report Designer, and if your Excel data is taken from the database, you can read the database data directly from the designer.

2. Merge a cell, click Insert > cell element in menu bar > Insert Chart, select Map, then click OK, such as: (This software is similar to Excel, so some operating terms are analogous to Excel)


1, choose the map type, country map, provincial map or other, or you have a custom SVG map also line. Note here that the area name of the map corresponds to the name of the area data field.


Personally feel that drilling is its highlight, so here must introduce.

Drilling:

Once you have defined the type of map, you can define the data source for the map, check the map, click the Chart property sheet - data, go to the Data Settings panel, and choose a multi-layer drill for the map presentation method, such as:


As you can see, there is a Chinese folder under the drill level, double-click to open the view of the provinces under China, select the Chinese folder, right-click, you will jump out of the Level Settings dialog box, such as:



To achieve a click in Shandong province can appear a bar chart, this is actually two charts associated with the so-called "linkage."

Linkage:

Merge a cell, click Insert > cell element > Insert Chart, select Column chart, click OK to add a column chart.

Data source for a column chart: (Here I am directly using the data source of the database)



Here, both the map and the column chart have been set up, and if you want to implement linkage, you need to set the interaction properties.

Select the map, select the effect in the Chart property sheet, click Interactive Properties, add a hyperlink, click, add a chart hyperlink - linkage cell, set such as:


To here basically finished, feel oneself do figure some low, do not ruin people's reputation, in fact, people's function is very powerful, visualization is very dazzling.

Also attached are several pictures:



Other software, as well as business intelligence visualization class tableau, Finebi, may be unfamiliar to everyone, do not do tutorial introduction.

In short, using the data source in Excel to make a map diagram method A lot of mistakes, please correct me.


How to make a data map from a data source in Excel

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.