Create your own Data Map

Create your own Data Map

In the next three sections, we will introduce three methods for creating color-filled data maps. These three methods have their own technical characteristics and performance, and can be applied to different analysis scenarios.

Typical practices of filename coloring

The most common form of Data Map is the fill-color thermal map (heatmap ). As there is no authoritative translation name in China, this book translates heatmap into a thermal map. As a visualization technology, it is sometimes called heatmap directly. The color-filling thermal map divides each area into several levels by value. Different Levels of graphs fill in different colors.

Plotting ideas

Divide the indicator data range into several sub-intervals to prepare different fill colors. Match the value of each indicator with the specified range to obtain the corresponding fill color. Then, use a macro to fill the corresponding map image with this color.

Drawing steps

1. Prepare the Data Map model as shown in Figure 3-1.

B ~ Column C is the indicator data of each province. The rest will be explained later.

2. Set the sub-file threshold and legend.

Assume that the map is divided into five colors by data size.

In column I, enter the lower limit value of each sub-range. For example, the value range of the 2nd value range is 0.2 ~ 0.4, enter 0.2 in cell i12, and color1 ~ In column J ~ 5. I11: the J20 area is used as the color file search table. You can define its name as color_table to facilitate reference. To name a cell or cell area, enter a name in the Name box in the upper-left corner of the Excel worksheet and press enter to finish the name.

 

 

 

Fill in the f column with a color from shortest to deep, which indicates that the value increases from small to large and is named color1 ~ respectively ~ 5. The color gradient should be proportional to the gap to reflect the change in quantity. For more information about how to set map colors scientifically and reasonably, see Appendix A.1.

Fill in the text tag of the range in the G column. In this example, the formula is used for automatic conversion, for example:

G11: = concatenate (i11 ,"~ ", I12)

Cell area F11: the G20.

3. Search for provincial color numbers.

After the preceding settings, use the formula in column D to find the color numbers for each province, for example:

D11: = Vlookup (C11, color_table, 2, 1)

Note that the last parameter value of Vlookup is "1", that is, the fuzzy search mode is used, which means that the value of C11 is in the color_table area (that is, i11: J20) search for "no greater than the maximum value of C11" and return the value of Column 2nd of the corresponding row. Drag down the formula in the cell and copy it to the entire column D.

Take the data in 3-1 as an example. If the data in C12 Beijing is 58.0%, the maximum value not greater than 58.0% in color_table is 0.4, the D12 formula returns the 0.4 column of the 3rd row where 2nd is located in color_table, that is, color3. Color3 points to the cell f13 as the name, so it uses the f13 color to fill the Beijing image.

4. Insert a map image.

Copy the named map image prepared in section 2.5 to the model and place it in the u11: ab32 area in this example (not shown in Figure 3-1, see the sample file ). Make sure that the names of the provinces are the same as those of the provinces in column B. If you select a chart in Hubei Province, "Hubei" should be displayed in the Name box ". To adjust the map size, you can first combine provincial images, then press and hold the shfit key to adjust to the appropriate size, and then uncombine them into independent images.

5. Write the filled macro code.

Obtain key code by recording macro 1, and modify and compile the macro code shown in 3-2.

 

 

 

 

This macro is actually very simple. The key code has only one sentence, that is, to fill the provincial graphics with the value of the d column color column as the fill color of the cell pointed to by the name.

Use the form control or rectangular box to draw a button, enter the text "fill color", right-click it, and specify the macro as fill_color.

6. click the button to test the function.

Click the button to run the macro, and you will find that the graph of each province is filled with the corresponding color based on the matching between the data and the sub-threshold. Check the data to see if the color is correct to confirm that the model is correct.

7. Integrate and improve the map.

To improve the map, take a picture of the F11: G20 Region 2, place the picture in the lower left corner of the map as a legend, and then take a picture of the u11: ab32 region where the graphic in each province is located, obtain an integrated map and place it at L11: s32. When exporting a map, you only need to copy the photo object.

The original idea of this practice comes from a log on the jorgecamoes blog. For details, see References 1. I was so excited when I tried to create a map of China Based on his video tutorial. After the blog was published, it was welcomed and liked by many readers, encouraging me to continue to ponder over more map practices. Of course, the practices introduced here have been greatly improved compared with the video, which is more concise and clear, and more conducive to understanding and making for beginners.

Knowledge points involved in this practice include: Vlookup function fuzzy search, defining name, recording and writing macros, taking photos, etc. Of course, the most important thing is the subtle idea of building a model.

 

 

Direct use of lazy

Now, you only need to take three steps to use the filename Data Map. In the example package, find and open the file, and enable the macro.

In column C, enter the index values of each province. For efficiency, see Y. We recommend that you use the Vlookup function to search for references from other places.

Set the threshold and Legend of the sub-file. Set the lower limit of the sub-range in column I. If necessary, change the fill color of column F (LEGEND. Five Colors are preset in the example. For more color schemes, see Appendix A.1.

Click the fill button to refresh the heat map. OK. Copy the image taken at L11: s32 when exporting the map.

The model of the sample file can support 10 sub-files. If there are less than 10 sub-files, you can leave them empty. We do not recommend that you use more sub-files.

To add the region name and indicator value on the map, you can draw a text box on the provincial graph and link the value to the corresponding cell. It is generally not recommended to add these items. It is clearer to put less content on the map.

If you need to perform data analysis in large regions such as East China and South China, you can combine the images of several provinces before naming them. At this time, your data sources will also be organized by China East, China South, and other regions. In short, the region is used as a zone.

 

 

 

-- This text is excerpted from "speaking with map: Using Excel Data Map in business analysis and demonstration"

Book details: http://blog.csdn.net/broadview2006/article/details/7205817