Come on, China! Come on Wuhan !
As of February 4, 2020, the total number of confirmed cases nationwide was 20,502, 671 were cured and 426 died.
From the data, we can see that although the number of confirmed cases is still gradually increasing, the number of cured cases is also gradually increasing. In order to analyze the epidemic data more clearly, I have made a dynamic Excel epidemic data analysis chart to share with you.
1. Reproduction diagram
2. Prepare source data
At present, major websites have opened real-time epidemic update pages. The following data is extracted from the website of the National Health Commission until 2020-2-4 16:00 cumulative data:
3. Methods and steps (Note: This example is operated under Excel 2016 version)
Step 1. Add a column of difference column (number of cured people - deaths) to the statistical table, such as column E data = column C - column D in this example, and change the number format of column E to: [green][0]↑0; [red][=0]↓0:
Step 2. [Development Tools]---Insert control---Set the step size to 1, the maximum value is 14, the minimum value is 1--- and set G1 as cell link---Confirm:
Step 3. Create another table header to set dynamic data: Enter formula in cell I3: =CHOOSE(G1,A3,A4,A5,A6,A7,A8,A9,A10,A11,A12,A13,A14,A15,A16):
Step 4. Use the VLOOKUP formula to find the corresponding dynamic data: Enter formula in cell J3: =VLOOKUP($I$3,$A$2:$F$16,2,FALSE):
Step 5. Add a column of auxiliary column F columns in the data summary table, And enter the formula: =IF(A3=$I$3,IF(E3=$M$3,$M$3,0),0), and copy the formula downward to get the highlight data:
Step 6, Insert the chart to the data:
Step 7, Change the [Difference] and [Auxiliary Column] chart to cluster column chart :
Step 8, Adjust the chart format: Adjust the maximum value of the axis, and set the date axis number color to no fill:
Step 9. Adjust the overlap rate of the column chart to 100%:
Step 10. Adjust the fill color of the bar chart: Change the fill color to [representing negative values with complementarity]:
Step 11. Adjust the final rendering:
The epidemic is serious. All I can do is to pass the latest information to more people through the Excel carrier, and learn to prevent the epidemic without missing a problem!
