Construct a diagram of the number of sunny days by month. Precipitation distribution chart in Excel

A line chart is used to track the change in several quantities as you move from one point to another.

Example 4. Construct a line chart showing the change in the number of newspapers sold during the week (see previous example). The construction of a linear diagram is similar to the construction of a column diagram, but instead of columns, their height is simply marked (dots, dashes, crosses) and the resulting marks are connected by straight lines (the diagram is linear). Instead of different shading (shading) of the columns, different marks are used (diamonds, triangles, crosses, etc.), different thicknesses and types of lines (solid, dotted, etc.), different color(Fig. 7 .37).

Rice. 7.37 – Line chart.

Normalized bar chart

A normalized bar chart allows you to visually compare the sums of several quantities at several points, and at the same time show the contribution of each quantity to the total sum.

Example 5. The “Newspaper Sales” diagrams we have compiled (both columnar and line) are of interest primarily to newspaper sellers and demonstrate the success of their work. But besides sellers, other people are also interested in selling newspapers. For example, a newspaper publisher needs to know not only how many copies of the newspaper each seller sold, but also how many they sold together. At the same time, interest remains in the individual quantities that make up the total. Let's take the newspaper sales table and build a tier chart for it.

The procedure for constructing a normalized chart is very similar to the procedure for constructing a bar chart. The difference is that the bars in a tier chart are not placed next to each other, but one on top of the other. The rules for calculating the vertical and horizontal size of a chart change accordingly. The vertical size will be determined not by the largest value, but by the largest sum of values. But the number of columns will always be equal to the number of support points: at each support point there will always be exactly one multi-tiered column (Fig. 7.38).

Rice. 7.38 – Normalized diagram.

Area chart

An area chart (area chart) is a hybrid of a normalized chart and a linear chart. Allows you to simultaneously monitor the change in each of several quantities and the change in their sum at several points.

Example 6. Let's take the newspaper sales table and construct an area diagram for it. An area chart differs from a line chart in the same way that a normalized chart differs from a column chart. When constructing a normalized chart, each subsequent column is plotted not from the horizontal axis, but from the previous column. The same thing happens when constructing an area diagram. But instead of constructing bars (as was the case in the normalized chart), their height is noted, and then these marks are connected by lines (as was the case in the line chart). This is what the resulting area chart “Newspaper Trade” will look like (Fig. 7.39):

Rice. 7.39 – Area diagram.

The individual columns here merge to form continuous areas. Each area corresponds to a single value, to indicate which personal shading (coloring) is used.

It is impossible to quickly and efficiently process large volumes of the same type of information presented in text form. It is much more convenient to process such information using tables.

But the perception of bulky tables also turns out to be difficult for humans.

Let's say you're preparing for a school geography conference where you're assigned to draw a climate portrait of the month of June. Throughout the month, you collected information about air temperature, pressure, humidity, cloudiness, wind direction and speed.

You entered the relevant information into a previously prepared table, and this is what you got (part of the table):

Of course, you can redraw this table to large leaf Whatman paper and demonstrate this impressive result to your classmates. But will they be able to perceive this information, process it and form an idea about the weather in May? Most likely not.

You collected large number information, it is accurate, complete and reliable, but in tabular form it will not be interesting to listeners, since it is not at all visual.

Visual representation of the processes of changing quantities

The graph shows two coordinate axes at right angles to each other. These axes are scales on which the represented values are plotted.

Pay attention!

One quantity is dependent on the other - independent. The values of the independent quantity are usually plotted on the horizontal axis (X-axis, or abscissa axis), and the dependent quantity - on the vertical axis (Y-axis, or ordinate axis). When the independent quantity changes, the dependent quantity changes.

For example, air temperature (dependent variable) can change over time (independent variable).

Thus, a graph shows what happens to Y as X changes. A graph shows values as curves, points, or both.

The graph allows you to track the dynamics of data changes. For example, using the data contained in the \(2\)th graph, you can construct a graph of temperature changes during the month in question.

Using the schedule, you can instantly set the warmest day of the month, the coldest day of the month, quickly calculate the number of days when the air temperature exceeded twenty degrees or was around \(+15 °C\).

You can also indicate periods when the air temperature was quite stable or, conversely, underwent significant fluctuations.

Similar information is provided by graphs of changes in air humidity and atmospheric pressure, constructed on the basis of the \(3\)th and \(4\)th columns of the table.

A visual representation of the relationship between quantities

A visual representation of the relationship between certain quantities is provided by diagrams. If the compared values add up to \(100\)%, then use pie charts.

The chart does not indicate the number of days with a certain cloudiness, but it does show what percentage of total number days occur on days with some cloudiness.

Days with certain cloudiness have their own sector of the circle. The area of this sector relates to the area of the entire circle in the same way that the number of days with a certain cloudiness relates to the entire number of days in June. Therefore, if the pie chart does not show any numerical data at all, it will still give some approximate idea of the relationship between the values under consideration, in our case, days with different cloudiness.

A large number of sectors makes it difficult to perceive information in a pie chart. Therefore, a pie chart is generally not used for more than five or six data values. In our example, this difficulty can be overcome by reducing the number of cloudiness gradations: \(0-30\)%, \(40-60\)%, \(70-80\)%, \(90-100\)%.

One glance at the diagram is enough to conclude that June was dominated by clear days, A cloudy days there was very little. To provide greater clarity, we were forced to sacrifice accuracy. In many cases, it is possible to ensure both clarity and accuracy of information bar charts.

Column charts consist of parallel rectangles (bars) of the same width. Each bar shows one type of qualitative data (for example, one cloud type) and is tied to some reference point on the horizontal axis - the category axis.

In our case, the reference points on the category axis are fixed cloud values.

The height of the columns is proportional to the values of the quantities being compared (for example, the number of days of a particular cloudiness).

The corresponding values are plotted on the vertical value axis.

Neither the value axis nor the bars should have breaks: the chart is used for a more visual comparison, and the presence of breaks defeats the very purpose of presenting results in the form of a chart.

Radar chart special, it has its own axis for each point in the data series. The axes originate from the center of the chart.

Let's build a distribution chart in Excel. We’ll also take a closer look at the functions of pie charts and their creation.

How to build a distribution chart in Excel

The normal distribution graph is bell-shaped and symmetrical about the mean. Such a graphical image can only be obtained with a huge number of measurements. In Excel, it is customary to build a histogram for a finite number of measurements.

Externally, a bar graph is similar to a normal distribution graph. Let's build a bar chart of precipitation distribution in Excel and consider 2 ways to build it.

The following data are available on the amount of precipitation:

Select “Histogram”:

Set the input interval (column with numerical values). Leave the “Pocket intervals” field empty: Excel will generate it automatically. Place a check mark next to the “Graph output” entry:

After clicking OK, we get the following graph with a table:

There are not many values in the intervals, so the histogram bars are low.

Now you need to make sure that relative frequencies are displayed on the vertical axis.

Let's find the sum of all absolute frequencies (using the SUM function). Let's create an additional column “Relative frequency”. In the first cell, enter the formula:

Method two. Let's return to the table with the original data. Let's calculate the pocket intervals. First, let's find the maximum value in the temperature range and the minimum.

To find the interval of pockets, you need to divide the difference between the maximum and minimum values of the array by the number of intervals. We get the “pocket width”.

Let's represent the pocket intervals as a column of values. First, we add the pocket width to the minimum value of the data array. In the next cell - to the received amount. And so on until we reach the maximum value.

To determine the frequency, make a column next to the pocket intervals. Enter the array function:

Let's calculate the relative frequencies (as in the previous method).

Let's build a bar chart of precipitation distribution in Excel using the standard "Charts" tool.

Setpoint distribution frequency:

Pie charts to illustrate distribution

A pie chart can be used to illustrate data that is in one column or one row. A circle segment is the share of each array element in the sum of all elements.

Any pie chart can show the distribution if

there is only one data series;
all values are positive;
almost all values are above zero;
no more than seven categories;
each category corresponds to a segment of the circle.

Based on the available data on precipitation, we will construct a pie chart.

Share of “every month” in total number precipitation per year:

A pie chart of precipitation distribution by season looks better if there is less data. Let's find the average amount of precipitation in each season using the AVERAGE function. Based on the data obtained, we will construct a diagram: