In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-12 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > IT Information >
Share
Shulou(Shulou.com)12/24 Report--
Clang clang! Hello everyone ~ I am satellite sauce!
At the end of the year, each family's year-end report is full of all kinds of charts. I can't wait to show all the patterns ~
But which charts fit in the report?
Today, Wei Mou recommends to everyone the automatic sorting bar chart (that is, the Bar Chart Race that has been on fire for a long time):
This is a great chart for big data screens!
Take a look
1. Data preparation data with many items (such as countries) and time points (such as years) is very suitable for making automatic sorting bar charts.
▲
GDP by country, 1990-2022
Source: Global Economic Database
2. Insert Scroll Bar Click Insert on the Development Tools tab and select Scroll Bar.
"Draw" it in the cell.
Right-click,[Set Control Format], select the link cell, I set A24 here, note that this cell will be the basis for data matching later.
Set the minimum and maximum values to year.
3. Data reference We need to match the corresponding country and value according to the contents of cell A24.
A formula can be used:
=SORT(CHOOSE({1,2},A2:A21,INDEX(B2:AH21,,MATCH(A24,B1:AH1,0))),2,-1)
Office 365 demo function analysis:
First match the column number corresponding to the year with MATCH function:
=MATCH(A24,B1: AH1,0) extract the column values with INDEX function:
=INDEX(B2: AH21,,MATCH(A24,B1: AH1,0)) By the way, the country name is also matched:
=CHOOSE({1,2},A2:A21,INDEX(B2: AH21,,MATCH(A24,B1: AH1,0)) Next we sort the data.
SORT function, Excel 2021 and above only have the function, it can be used to sort the contents of the selection.
Its syntax is:
=SORT(array,[sort_index],[sort_order],[by_col])
array: The region or array to sort
[sort_index]: A number indicating the row or column to sort by
[sort_order]: A number indicating the desired sort order;1 for ascending (default),-1 for descending
[by_col]: A logical value indicating the desired sort direction;False for row sort (default), True for column sort
Therefore, the final formula is:
=SORT(CHOOSE({1,2},A2: A2, -1,INDEX(B2: AH2, -1,,MATCH(A24,B1: AH1,0)),2,-1) Don't worry if you don't upgrade Excel. You can also use this function in WPS. The latest version (2023 Winter Update 16120) has opened the dynamic array function. You can directly enter to generate results ~
▲ WPS demonstration 4. Chart making Next insert the chart, select the first 10 rows of data,[Insert]-[Cluster Bar Chart];
Right click on the ordinate axis, select Set Coordinate Axis Format, and check Reverse Order Category.
At this point, the automatically updated sorting bar chart is ready:
5. Beautify the chart, but we have to beautify it again:
Remove unnecessary elements:
Let's highlight the country data we want to see:
Make a drop-down list first. Data-Data Validation-Sequence is allowed. Select the country cell for the source.
Add a formula to C25:
=IF(A25=$G$24,B25,"") Double-click the cross to fill in, so that only the countries we select in the drop-down list will have data displayed.
Right click on the chart, Select Data, Add, and select the top 10 data.
Select Series 2, right click Set Data Series Format, and the series overlap is changed to 100%, as shown in the figure below:
Then modify the gap width, adjust to their own pleasing to the eye on the line, I set 65% here.
Add a text box linking year and country:
Enter the formula at H24:
=G24&CHAR(10)&A24 Then link the text box to H24.
Note that you have to type in the formula bar to reference the cell.
Finally adjust the font and background.
It looks even more beautiful!
Data for different countries and times can be viewed at will:
6. Write at the end. Today we shared how to make automatic sorting bar charts controlled by scroll bars in Excel!
This cool chart is perfect for big data screens ~
This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), Author: Satellite Sauce
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.