4.1 Construction and analysis of the Pareto diagram To process the data obtained as a result of the survey on checklists, we will use the Pareto diagram. The algorithm for preparing the construction of the Pareto diagram is as follows: 1) determine the object of study; 2) choose a method

Constructing a Control Chart A control chart is a graph showing the average along with upper and lower control limits. The upper and lower control limits represent a statistical boundary. If these limits

Description of the conflict resolution diagram The name of this logical structure speaks for itself - the DRC is built to identify and resolve a certain conflict. In this case, a compromise is not accepted as a solution, since a compromise is an option in which both

Reading the DRC Conflict Resolution Diagram is read from left to right - from task to support methods. The logic of constructing a conflict resolution diagram requires voicing this logical structure using the construction “In order for... we must...” in the direction

Creating a Conflict Resolution Diagram We've discussed the building blocks of a DRC in detail, and now it's time to get familiar with the process of constructing one.

Analysis of the conflict resolution diagram The procedure for analyzing the DRC as a logical structure differs from the analysis of the current reality tree. Since the DRC represents only part of the conditions necessary to fulfill the main task of the system, the entire analysis consists of

How to choose a chart type? If you are developing step-by-step procedures and must provide justification for each step, then use a five-part change plan. If this tool is used in the process of logical thinking and

Analysis and discussion around the sticky note chart Working with sticky notes has a big advantage: this method generates a lot of information in a short time. The disadvantage is that people do not hear how ideas are created, of which there may be too many. if you

Cause-and-Effect Diagrams Consultants familiar with formal systems analysis techniques use highly sophisticated visualization strategies to reveal system dynamics and controls. If you want to learn more about this, Chapter 23 provides

33. Cross, Spider, and Comb Diagrams ToolWhat three things would you take with you to a desert island? What can you say about the cross, the spider and the comb? No, I don’t need them, although they have their own areas of application. It turns out that three diagrams named this way

Graphs and Charts Graphs and charts are excellent tools for communicating important or impactful information, and you should find ways to incorporate them into your business plan. We advise you to saturate the text with various schemes (their size should not exceed half

Affinity Diagrams PURPOSE OF THE GAME Most of us are familiar with the concept of brainstorming - a method in which, in the allotted time, a group generates as many ideas as possible on a given topic. As a result, it is possible to obtain information for further processing. However

Affinity Diagrams PURPOSE OF THE GAME Most of us are familiar with the concept of brainstorming - a method in which, in the allotted time, a group generates as many ideas as possible on a given topic. As a result, it is possible to obtain information for further processing. But

Defining a Diagram Although you'll definitely recognize a diagram when you see it, let's still give it a definition. A diagram is defined as a two-dimensional geometric symbolic representation of information. That's what this whole collection of figurines means. These are visual structures in

In Excel, the term chart is used to refer to all types of graphical representations of numerical data. The construction of a graphical image is based on a series of data. This is the name given to a group of cells with data within a single row or column. You can display multiple data series on one chart.

The diagram is an insert object embedded on one of the sheets of the workbook. It can be located on the same sheet on which the data is located, or on any other sheet (often a separate sheet is allocated for displaying the chart). The chart remains connected to the data on which it is based, and when that data is updated, it immediately changes its appearance.

To create a chart, you usually use the Chart Wizard, launched by clicking the Chart Wizard button on the standard toolbar. It is often convenient to select in advance the area containing the data that will be displayed on the chart, but you can also specify this information during the wizard.

2.1.1.Chart type.

At the first stage of the work, the wizard selects the shape of the diagram. Available forms are listed in the Type list on the Standard tab. For the selected chart type, several options for presenting data are indicated on the right (View palette), from which you should select the most suitable one. The Custom tab displays a set of fully formed chart types with ready-made formatting. After specifying the shape of the diagram, click on the Next button.

Data selection. The second stage of the wizard is used to select the data on which the chart will be built. If a data range has been selected in advance, an approximate representation of the future chart will appear in the preview area at the top of the wizard window. If the data forms a single rectangular range, then it is convenient to select them using the Data Range tab. If the data does not form a single group, then information for depicting individual data series is set on the Series tab. The chart preview automatically updates as the data set being displayed changes.

2.1.2.Diagram design. The third stage of the wizard (after clicking the Next button) is to select the design of the diagram. On the tabs of the wizard window you can set:

Chart title, axis labels (Titles tab);

Display and marking of coordinate axes (Axes tab);

Displaying a grid of lines parallel to the coordinate axes (Grid Lines tab);

Description of the constructed graphs (Legend tab);

Displaying labels corresponding to individual data elements on the chart (Data Labels tab);

Presentation of the data used to construct the graph in the form of a table (Data Table tab).

Depending on the chart type, some of the tabs listed may not be available.

2.1.3.Placement of the diagram. At the last stage of the wizard (after clicking the Next button), you specify whether you should use a new worksheet or one of the existing ones to place the diagram. Typically, this selection is only important for later printing of the document containing the diagram. After clicking the Finish button, the diagram is created automatically and inserted into the specified worksheet.

2.1.4.Editing a diagram. The finished diagram can be modified. It consists of a set of individual elements, such as the graphs themselves (data series), coordinate axes, chart title, plotting area, etc. When you click on a chart element, it is highlighted with markers, and when you hover the mouse pointer over it, it is described with a tooltip Open dialog box To format a chart element, you can use the Format menu (for a selected element) or through the context menu (Format command). Various tabs of the dialog box that opens allow you to change the display parameters of the selected data element. If you need to make significant changes to the chart, you should use the Chart Wizard again. To do this, open the worksheet with the chart or select a chart embedded in the data worksheet. By running the Chart Wizard, you can change the current settings that are treated as default in the wizard windows.

To delete a chart, you can delete the worksheet it is located on (Edit Delete Sheet), or select the chart embedded in the data worksheet and press DELETE

Building charts

Almost all modern spreadsheet processors have built-in business graphics tools. For this purpose, there is a graphical mode of operation of the spreadsheet processor. In the graphical mode, you can build diagrams of various types, which makes numerical dependencies clearer.

A diagram is a means of visually graphically depicting information, designed to compare several quantities or several values ​​of one quantity, monitor changes in their values, etc.

Most diagrams are constructed in a rectangular coordinate system. The horizontal X axis displays the values ​​of the independent variable (argument), and the vertical Y axis displays the values ​​of the dependent variable (function). Several diagrams can be displayed simultaneously on one drawing.

When graphically processing numerical information using a table processor, you should:

1) indicate the data area (block of cells) on which the diagram will be constructed;

2) determine the sequence of data selection (by rows or columns) from the selected block of cells.

When selecting by columns, X - coordinates are taken from the leftmost column of the selected block of cells. The remaining columns contain the Y-coordinates of the charts. The number of columns determines the number of diagrams being built. When selecting by row, the topmost row of the selected block of cells is the X-coordinate row, the remaining rows contain the Y-coordinates of the diagrams.

A diagram is a graphical representation of the dependence of one quantity on another. With the help of diagrams, the relationship between data becomes more clear. Charts make it easier to compare different data. Most charts organize data along a horizontal (category axis) and vertical axes (value axis). The individual data elements are called points. Several points form a sequence of data.

Chart elements

By default, the chart consists of the following elements:

Data series are of main value because... visualize data;

Legend – contains the names of the rows and an example of their design;

Axes – a scale with a certain value of intermediate divisions;

The plot area is the background for the data series;

Grid lines.

In addition to the objects mentioned above, the following can be added:

· Chart titles;

· Projection lines – descending from data series to the horizontal line axis;

· Trend line;

· Data labels – a numeric value for a data point in a series;

· And other infrequently used elements.

Types of charts

Types of charts.

The wizard gives recommendations for choosing a data source and chart parameters depending on the selected type. Therefore, the definition of the diagram type is offered to the user at the very first step. The Chart Type window in the first step of the Chart Wizard presents fourteen standard chart types, each with several more types. The chart type is selected in the Chart sub-type field on the Standard Types tab. After selecting the chart type and view, you can view the chart by pressing and holding the Press and hold to view sample button.

Excel also provides several custom chart templates. The custom chart type can be selected from the Custom Type tab in the Chart Type window. Selecting a chart type on this tab automatically causes the chart to appear in the Sample field.

By default, Excel sets charts as Columns. Histograms are useful for representing items that are compared within the same time period, or for illustrating changes in different items across multiple periods. They can also be used to compare annual product sales over the past few years. In the same way, you can present expenses and then compare sales and expenses over a certain period of time.

Bar charts are the same as bar charts, but with a different axis orientation. Unlike bar charts, the x-axis (or category axis) is vertical and the y-axis (or value axis) is horizontal. Bar charts are used in the same cases as histograms. The horizontal position of the axis of dependent variables makes them especially convenient for the comparative presentation of different quantities within the same time period. Let's say, sales of goods over a month by various sellers are best demonstrated by a bar chart.

Line Graphs are used to show the progression of a process over time or by category. Moreover, equal intervals are always plotted along the category axis, so graphs are used to display changes that occur over regular periods of time (for example, days, weeks or months).

Pie charts show the relationship between the whole and its parts. They best show what part of the whole one or another of its components is (for example, the entire budget and its individual items, the entire investment portfolio and the investments included in it). The principle of constructing a pie chart is as follows: first, all data from the selected range is summed up, and then it is determined what part of this whole is the content of each cell. Some types of pie charts allow you to highlight one or more parts of a whole.

Doughnut charts are a type of pie chart. They also show the relationship of the parts as a whole. But they have one significant difference: donut charts, unlike pie charts, can represent different types of data.

Radar charts are hardly convenient to use except in some very special cases. They show relationships between different data sequences, as well as between each sequence and all sequences at the same time. As a result, everything ends up being lumped together, and getting a clear idea of ​​the essence of the subject being presented is usually very problematic. Radar charts are sometimes used in the administration of complex projects.

XY Scatter charts are widely used in statistics. Their advantage is that they can illustrate the degree of coherence of data elements (represented by points), as well as the degree of proximity of data elements to the average value. Scatter plots show changes in data that occur over a period of time. This makes them similar to graphs. What are the specifics of using scatter plots? Graphs always have equal intervals along the category axis, whereas scatter plots can represent data for which the time intervals have different sizes. So, for example, if you want to analyze the daily sales of detergents, then, given that on some days certain types of detergents were not sold at all, it is better to use a scatter plot. This presentation of data will clearly demonstrate changes at irregular intervals. A similar representation on a graph would give an inadequate picture.

Scatter plots are also used if you want to plot a graph of some ambiguous function or a function whose argument has a non-constant step. Let's build, for example, a graph of the function y = x2, -5<= x <= 5. Причем от -5 до 0 представим аргумент с шагом 1, а от 1 до 5 - с шагом 2. Если Вы выберете тип диаграммы Line (График), то по оси категорий будут отложены не значения аргумента, а некоторые равные интервалы и, следовательно, график функции будет отображен неверно. Если же Вы определите тип диаграммы как XY Scatter (Точечная), Вам будет предоставлена возможность выбора значений для оси категорий, и, указав в этом качестве строку или столбец, в которых хранится ряд аргументов, Вы корректно построите требуемый график.

Volumetric diagrams look somewhat more complicated, but they are used in almost the same cases as flat ones. In the family of volumetric diagrams, Surface diagrams stand apart. They are used to show the relationship between several variables or to visually present large amounts of data that are otherwise difficult to interpret. This chart looks like a relief map, showing the highs and lows of a large data set.

Excel. Task

Initially, we are presented with a blank sheet called sheet 1. We rename it “Issue of shares”

1.Create a table. In the table with headings column, put a checkmark, otherwise the columns will be named by default.

2.Change the names of the columns by right-clicking on the column name and entering the appropriate name.

3. Set the font to Times New Roman, height 9, for the headings. Apply text alignment in cells with word wrapping.

4.Fill in the first column using progression. When you select cells with values ​​A2 and A3, in the lower right corner of cell A3 the pointer takes the form of a cross, which is called an autofill marker. We need a minimum of 40 values, so let's enter this cross before cell A41.

4.1. by analogy, fill in the other columns

4.2. In the column with the selling price, insert the formula En*Fn (=[@[Quantity, pcs.]]*[@[Nominal value, rub.]])

5. In the cell under the name dividends, insert a formula using the interest rate with the absolute address of the cell.

6.Change the format of cells, 6, 7,8 column in the form of money.

7. Sort the table “Issue of shares” by the column “Quantity, pcs.” Ascending.

8. We build a graph using the columns “Issuer” and “Quantity, pcs.”

Insert the column “Quantity, pcs.” into the elements of the vertical axis (rows).

Insert the value of the “Issuer” column into the horizontal axis label

9. Create a pivot table on a new sheet.

9.1 In the list of fields in the pivot table, put a checkmark in the line dividends and issuer. It turns out this mini table.

10. We build a diagram of the total accrual of dividends depending on the issuer.

11. By analogy, we create a summary table of the dependence of the type of shares on their nominal value.

11.1We also create a dependency diagram by analogy.

12. On a new sheet, we have inserted a copy of the Share Issue table. And they called it an autofilter.

12.1. Filter by two issuer names.

12.2 Set the filter parameters in the “Sales price, rubles” column, more than 35,000.

12.3 In the “Date of issue” column, we apply a filter so that transactions completed only in February are displayed.

Bibliography

1. Akhmetov A. N., Borzenko A. V. Modern personal computer. – M.: Computer Press, 2003.-317 p.

2. Bank V.R., Zverev V.S. Information systems in economics: Textbook. – 2003

3. Borzenko A.V. IBM PC: device, repair, modernization. - M., Computer Press, 1996. - 344 p.

4. Zverev V.S. Informatics: Textbook for university students. Astrakhan, 2003

5. Computer Press//M.: Computer Press – 2002.

6. Computerra//M.: Press LLC – 2001.

7. Kuznetsov E. Yu., Osman V. M. Personal computers and programmable microcalculators: Textbook. manual for technical colleges - M.: Higher. school -1991 160 p.

8. Figurnov A.E. IBM-PC for the user. M., 1998

9. Technical means and methods of information security: Textbook for universities / Zaitsev A.P., Shelupanov A.A., Meshcheryakov R.V. and etc.; ed. A.P. Zaitsev and A.A. Shelupanova. – M.: Publishing House Mashinostroenie LLC, 2009 – 508 p. Source - http://window.edu.ru/window_catalog/pdf2txt?p_id=33810

10. Melnikov V. Information protection in computer systems. M.: Finance and Statistics, Elektroninform, 1997 – 368 p.

11. A. Seregin, A. Chudnovskaya. "Computer device". Moscow, 2006.

12. D. Habraken. "The computer and its capabilities." Moscow, 2004.

13. A. Vaskovsky. "Excel. Fundamentals and its capabilities." Minsk. 2003

Statistical graphs by direction of use are characterized by significant diversity. their scientific classification provides for such features as general purpose, types, shapes and types of basic elements. Traditionally, the theory of statistics considers the classification of graphs according to the types of their field. According to this principle, graphic images are divided into diagrams, cartograms and map diagrams.

Diagrams - These are conventional images of numerical quantities and their relationships using geometric signs.

Cartograms- depiction of numerical values ​​and their relationships by applying conventional shading or coloring to a map - diagram.

Map diagrams - This is a combination of a diagram and a map - a diagram. When constructing a diagram, a certain scale is established, that is, the relationship between the size of the quantities on the graph and the actual magnitude of the depicted phenomenon in kind.

The most common type of statistical graphs are charts. Depending on the method of depicting statistical data, they can be in one dimension, when this data is depicted in the form of straight lines or stripes of the same width, and in two dimensions (plane), in which the data is depicted using the areas of geometric shapes (rectangles, squares, circles) .).

The first type of charts includes line, bar, strip, etc.; to the second - rectangular (square, “Sign of the Barbarian”), circular, sector, radial, figured.

Line chart displays the size of the indicator in the form of lines of different lengths, which are formed as a result of connecting points in the coordinate field. One of the types of linear diagrams is a linear schedule of plan implementation and an accounting plan schedule (Fig. 27, 28).

Rice. 27. Linear graph of the dynamics of the number of horses on the farm

Rice. 28. Accounting and planning schedule for the enterprise’s implementation of the production plan during the month: a- for a decade; b - on a cumulative basis

Line diagrams are used mainly to study the development of phenomena over time.

The following requirements apply to the construction of line diagrams:

2) the ordinate is necessarily affected by a zero value. In cases where compliance with this rule is associated with a significant reduction in scale and deterioration in visibility, a break should be made along all ordinates (while the zero line is preserved.)

3) the segments on the abscissa axis must correspond to intervals (for time series - time period);

4) the zero line should be sharply different from other parallel lines;

5) when constructing a chart using a percentage scale, you need to clearly highlight the line that means 100%;

6) the curved line of the diagram should differ sharply from the grid lines

7) digital indicators are placed on the graph in such a way that they can be easily read;

8) the area of ​​the graph should be square or rectangular. Column of the chart. In this type of chart, statistical

The data is depicted in the form of rectangles (columns) of the same width. They are placed vertically or horizontally. The magnitude of the phenomena is characterized by the height of the column (Fig. 29).

Rice. 29. Bar chart of the dynamics of gross production of products by the enterprise

Column diagrams are used: 1) when comparing different phenomena; 2) to depict phenomena in time; 3) to display the structure of phenomena.

Let's look at the basic rules for constructing bar charts:

1) the width of the columns and the distance between them must be the same;

2) the columns are arranged from smallest to largest or vice versa (spatial model);

3) at the base of the columns, a baseline is drawn and highlighted;

4) the name and digital data of the columns are indicated;

5) the scale must have divisions, the main ones indicated by numbers;

6) indicate the unit of measurement.

A type of bar chart is a histogram, which depicts variation series of distributions.

Strip charts. Unlike bar charts, when constructing strip charts, the rectangles that depict the size of phenomena are placed not vertically, but horizontally (Fig. 33). The requirements for constructing this type of chart are similar to the requirements for bar charts.

Rice. 30. Strip chart of daily wages at enterprises

Pie charts represent a circle divided into sectors, the sizes of which correspond (in proportions) to the depicted sizes of the phenomena. Sector diagrams are constructed to display the structure of phenomena (Fig. 31).

Rice. 31. Sector diagram of the structure of sown areas of an agricultural enterprise

Rectangular diagrams. This type of diagram depicts the magnitude of the phenomena under study in the form of areas. Rectangular diagrams are used to depict phenomena that change over time, as well as to compare different quantities in space.

Rectangular diagrams include square diagrams and the "Barbarian Sign".

Square diagrams used when comparing absolute values. To determine the side of a square, you need to obtain the square root of the tested (diagrammed) quantities. Based on the data in Table 95, we carry out the corresponding calculations, taking a scale of 30 = 1 cm. We convert the indicators obtained after taking the square root of the area of ​​agricultural land into scale units: 81.2: 30 = 2.7 cm; 76.8: 30 = 2.6 cm; 72.8: 30 = 2.4 cm The resulting numerical values ​​are taken as the side of the square (Fig. 32).

Table 95

Output and calculation data for constructing square and pie charts

"The Sign of Varzar." Used to compare three related quantities. It is a rectangle in

in which the length reflects the magnitude of one phenomenon, the width - of another, and its area characterizes the product of these in a two-scale comparison: one scale - for the base of the rectangle, the second - for its height.

The “Varzar sign” simultaneously compares, as already mentioned, three interconnected quantities, that is, the diagrammatic indicator is the product of the other two. For example, if the area of ​​the rectangle of the diagram illustrates the harvest, then one of its lengths is the sown area, the second is its height - the yield. This type of diagram is shown in Figure 33.

Rice. 32. Square diagram of the size of the enterprise's agricultural land areas

Fig.33. Rectangular diagram "Sign of Varzar".

Circular diagrams of their area reflect the magnitude of the phenomena being studied. They are based on the use of the area of ​​a circle to illustrate comparable homogeneous quantities. When constructing them, it is taken into account that the areas of circles are related to each other as the squares of their radii. To determine the radius of a circle, it is necessary to obtain the square root of the diagram value; on this basis, outline it on a certain scale and describe a circle using its size. Figure 34 shows a pie chart based on the data in Table 95.

Radial diagrams. This type of diagram is used to graphically depict phenomena that change within a closed calendar period. Their construction is based on a polar coordinate system, where a circle is taken along the abscissa axis, and its radii are taken as all ordinates.

Depending on which cycle of the diagrammed phenomenon is depicted - closed or continuous (from period to period) - radial diagrams are distinguished closed and spiral. For example, if the entire cycle of change of the depicted phenomenon covers the summer period, the radial diagram is constructed in a closed form.

Rice. 34. Pie chart of agricultural land area sizes

enterprises

Rice. 35. Radial diagram of man-hours worked at the enterprise during the year

If the change in a phenomenon is studied during the cycle of the charted period (for example, December of one year connects with January of the second year, etc.), a series of dynamics is depicted as a solid curve, which visually looks like a spiral.

When constructing radial diagrams, the origin (pole) can be the center of the circle. If the center of the circle is taken as the pole, then the radial diagram is constructed in the following sequence: the circle is divided into as many parts as the number of periods the diagramming cycle has (for example, a year is 12 months), and the radii are constructed accordingly (in this case - 12). The periods are placed clockwise and at each radius in the scale dimension, segments are laid (from the center of the circle) proportional to the size of the phenomena. The ends of the segments at the radii are connected, resulting in the formation of a concentric broken line. An example of a closed radial diagram with the origin starting from the center of the circle is shown in Fig. 35.

Method of figures - signs. This method of depicting diagramming phenomena involves replacing geometric figures with drawings that correspond to the content of statistical data (Fig. 36). That is, the value of the indicator is depicted using figures (symbols, drawings): for example, the number of horses - in the form of a silhouette of a horse, car production - in the form of a drawing of a car, etc. The advantages of this type of diagrams over geometric ones are their clarity and intelligibility. The symbolic image makes the diagram expressive and attractive.

Rice. 36. Dynamics of book publications on market economics in the district library

The figure-sign method (the so-called Viennese) has its own characteristics and is characterized by a richer content, is of fundamental importance and requires compliance with certain rules for constructing such diagrams, namely:

1) symbols should be understandable on their own and not require detailed explanations. As a rule, they depict the outline or silhouette of diagrammed objects;

2) ensure unambiguous interpretation;

3) unambiguity of the topic;

4) group characteristics are arranged vertically, and indicators that characterize them are placed horizontally;

5) images of signs and symbols must comply with the principles of good drawing;

6) excessive detailing and decoration are considered exceptional;

7) standardization of signs - symbols. The layout of the diagram should be carried out using standardized signs - symbols made in a printing house and mounted using the appliqué method. There are special examples of such signs;

8) obligatory title of the diagram and text designations of individual aggregates (groups), which are depicted by a certain figure; scale designation indicating the numerical value of each sign - symbol.

Semi-logarithmic plots. This type of statistical graph is plotted in a coordinate system. The numbers characterizing the diagrammed phenomenon are on a logarithmic scale. Logarithms of points are placed on the ordinate axis, and the date of the phenomenon (year) is placed on the abscissa axis (Fig. 37).

Rice. 37. semi-logarithmic graph of the dynamics of daily wages at the enterprise

Cartograms and map diagrams. Cartograms are a contour geographic map or diagram on which the comparative intensity of any indicator within each unit of the territorial division plotted on the map is depicted with shading of varying density, dots or colors of varying degrees of saturation. Cartograms, as a rule, depict phenomena characterized by relative or average values ​​(for example, the number of working pensioners in the total number of workers by region, land reclamation as a percentage of the total area, average wages at enterprises by region, etc.).

According to the method of depicting diagrammatic phenomena, they are distinguished point and background cartograms.

In the first, the level of the phenomenon is shown using points located on the contour map of the territorial unit. For clarity, the image of the density or frequency of occurrence of a certain characteristic is indicated by a dot to indicate one or more units of the population.

On background cartograms, shading of varying thickness or paint of varying degrees of saturation depicts the intensity of any indicator within a territorial unit. One of the cases of cartograms is shown in Figure 38.

Rice. 38. Cartogram of the density of cows per 100 hectares of agricultural land in the district’s farms

If statistical data in the form of diagrams is plotted on a contour map, a map diagram is obtained. A striking example of this is a geographical map on which the population of large cities is depicted in the form of circles of various sizes.

In addition to the considered methods of graphically depicting the phenomena under study, there are others. Their practical use in reflecting the dynamics of phenomena, their structure and relationships is discussed in previous chapters.

Let's assume the user has data in absolute values. He needs to display the information on a diagram. For better clarity, it is necessary to show relative data values. For example, what percent of the plan was completed, how much product was sold, what part of the students completed the task, what percentage of employees have higher education, etc.

It's not that difficult to do. But if you lack Excel skills, some difficulties may arise. Let's take a closer look at how to make a percentage chart in Excel.

Pie percentage chart

Let's build a pie chart of the percentage distribution. For example, let’s take the official tax analytics “Receipts by type of taxes to the consolidated budget of the Russian Federation for 2015” (information from the Federal Tax Service website):

Select the entire table, including the column names. On the “Insert” tab, in the “Charts” group, select a simple pie.

Immediately after clicking on the tab of the selected type, a diagram like this appears on the sheet:

A separate segment of the circle is the share of each tax in the total revenues to the consolidated budget in 2015.

Now let's show on the diagram the percentage of types of taxes. Let's right-click on it. In the dialog box that opens, select the “Add data signatures” task.

The values ​​from the second column of the table will appear on parts of the circle:

Right-click on the diagram again and select “Format Data Labels”:

In the menu that opens, in the “Signature Options” subgroup, you need to uncheck the box next to “Include values ​​in signatures” and put it next to “Include shares in signatures.”

In the “Number” subgroup, change the general format to percentage. Remove the decimal places and set the format code to “0%”.

If you need to display percentages with one decimal place, set “0.0%” in the “Format code” field. With two decimal places – “0.00%”. And so on.

Standard settings allow you to change the location of labels on the diagram. Possible options:

• “In the center” - captions will be displayed in the center of the segments;
• “At the top, inside” - the labels will be displayed on the inside of the circle;
• “At the top, outside” - the labels will appear on the outside of the circle; when you select this option, the diagram itself will be slightly smaller, but if there is small data, readability improves;
• “Fit to Width” - this option allows Excel to set signatures in the most optimal way.

To change the direction of labels, in the Alignment subgroup, you can use the Text Direction tool. The angle of inclination is also set here.

Select the horizontal direction of the data labels and the “Width” position.

The pie chart with percentages is ready. The chart shows the percentage distribution of tax revenue.

Column histogram

Let's add auxiliary columns to the table: 1 – with percentages (percentage contribution of each type of tax to the total); 2 – 100%.

Click on any table cell. Go to the “Insert” tab. In the “Charts” group, select “Normalized stacked histogram”.

An automatically generated diagram does not solve the problem. Therefore, on the “Design” tab, in the “Data” group, go to the “Select data” item.

Using the arrow, we change the order of the rows so that the percentages are at the bottom. The series showing absolute values ​​is deleted. In “Categories”, remove the “Type of tax” cell. The title should not be a horizontal axis label.

Select any column of the created chart. Go to the “Layout” tab. In the “Current fragment” group, click “Format selected fragment”.

In the menu that opens, go to the “Series Parameters” tab. Set the value for row overlap to 100%.

As a result of the work done, we get a diagram like this:

This diagram gives a general idea of ​​the percentage of tax types in the consolidated budget of the Russian Federation.

| Data Analysis Tools

Lesson 5
§4. Data Analysis Tools

4.1. Diagrams

As a rule, spreadsheets contain a large amount of numerical data that needs to be compared, evaluate their changes over time, determine the relationship between them, etc. Carrying out such an analysis of a large amount of numerical data is much easier if you depict them graphically (visualize). Charts are used to present numerical data graphically.

A chart is a graphical representation of numerical data that allows you to quickly evaluate the relationship between several quantities.

Table processors allow you to build the following types of charts:

Bar chart;
area chart;
bar chart;
surface diagram;
pie chart;
schedule;
radar chart, etc.

To Microsoft Excel view all available chart types, explore the group Diagrams on the tab Insert(Fig. 1.8).

Rice. 1.8. Types of Charts in Microsoft Excel

Find out what types of charts can be created in the spreadsheet processor at your disposal.

In a diagram of any type, the following objects can be distinguished (Fig. 1.9):

1 - diagram area (all diagram objects are placed in it);
2 - the title of the diagram, which clearly describes what is presented in the diagram;
3 - diagram construction area (the diagram itself is located directly in it);
4 - value axis (vertical, Y axis). There is a scale on it with a certain step, which is set automatically, depending on the smallest and largest data values ​​​​shown in the diagram. It is on this scale that the data presented in the diagram can be assessed;
5 - data series - sets of numerical data, interconnected in some way and placed in a spreadsheet in one row or column. In a diagram, a series of data is represented by geometric figures of the same type and color;
6 - category axis (horizontal, X axis). It displays the values ​​of a certain data property;
7 - a legend explaining the correspondence between the names of the series and the colors used in the diagram. By default, series names are the names of the rows (or columns) of the data range from which the chart is drawn;
8 - names of the axes.

Rice. 1.9. Basic elements of a diagram

Reproduce the diagram presented in Figure 1.9 in a spreadsheet processor. Use the context menu to explore the properties of each object in this diagram.

In different types of charts, numerical data can be represented by points, line segments, rectangles, sectors of a circle, cuboids, cylinders, cones and other geometric shapes. In this case, the sizes of geometric figures or the distances from them to the axes are proportional to the numerical data that they display.

Charts created in spreadsheets are dynamic - when you edit data in the table, the size or number of shapes representing this data automatically changes.

Remember the basic techniques for constructing diagrams that you know from your primary school computer science course.

Let's look at the most common types of charts.

Histograms It is advisable to create when you need to compare the values ​​of several data sets, graphically depict the differences between the values ​​of one data set and another, and show changes in data over time.

The following types of histograms are distinguished::

Histogram with grouping;
stacked histogram;
normalized stacked histogram;
volumetric histogram.

In a clustered histogram, rectangles, which are graphical representations of numeric data from different sets, are placed next to each other (see Figure 1.9). In a stacked histogram, rectangles representing numeric data are stacked on top of each other (Figure 1.10). This makes it possible to evaluate the total data and the contribution of each component to the total.

In a normalized stacked histogram, the vertical axis is scaled as a percentage. This makes it possible to estimate the share (percentage) of data in the total amount (Fig. 1.11).

Think about which of the three diagrams is easiest to determine:

1) the sale of which drinks has been steadily increasing;
2) the sale of which drinks brought the greatest profit in July;
3) the dynamics of changes in the total revenue from the sale of all three drinks;
4) contribution from the sale of each drink to total revenue.

Rice. 1.10. Example of a stacked histogram

Rice. 1.11. Normalized stacked histogram

Bar charts are similar to histograms and differ from them only in the horizontal arrangement of geometric shapes.

To type Pie Chart These include flat and three-dimensional pie charts. It is advisable to use them when you need to display parts of one whole, compare the relationship of parts to each other and the relationship of parts to the whole.

Pie charts allow you to display only one series of data. They become less clear if they contain many data elements. Multiple pie charts can be replaced, for example, by a single normalized stacked histogram.

Think about how many different pie charts you can construct from the information contained in the chart shown in Figure 1.9.

How many pie charts would be needed to depict the information presented in a stacked bar chart (see Figure 1.10)?

Graph type charts It is advisable to use if the amount of data in the set is large enough, if you need to display the dynamics of changes in data over time, compare changes in several data series (Fig. 1.12).

Rice. 1.12. Example chart Graph with markers

Scatter plots with smooth curves can be used to plot function graphs by first populating a range of cells with argument values ​​and corresponding function values. You can plot two functions on one diagram and use them to approximately solve the equation.

Example. Let's find the root of the equation on the segment , by constructing graphs of functions corresponding to the left and right sides of the equality in a table processor.

For this:

1) using the standard functions COS and SQRT, we will build a table of function values ​​for X, changing in steps of 0.1:

Rice. 1.13. Function graphs

2) using the values ​​of the range B2:N3, we will construct graphs of the functions COS(x) and ROOT(x) (Fig. 1.13);

3) replace the numbers of points marked along the horizontal axis with the values ​​of the argument x of the functions under consideration. To do this, call the context menu of the horizontal axis and select Select data. The Select Data Source window will appear (Figure 1.14).

Rice. 1.14. Window Select data source

In the window that opens, click on the button for changing the horizontal axis labels and select the range with the argument values ​​(Fig. 1.15).

Rice. 1.15. Axis Labels Window

After editing (aligning) the intersection point of the axes and adding vertical grid lines, the graph will take the form shown in Figure 1.16.

Rice. 1.16. Chart after editing

As a result of plotting the functions, it can be seen that the root of the equation is approximately 0.64.

The constructed diagram can be edited:

Change the way a data series is formed: from row data or from column data;
change the range of cells for which the chart is built;
change the chart type, appearance, or layout;
insert, move, delete or change the name of a chart, legend, data labels;
change the display of axes and grid lines, etc. The generated diagram can be formatted. In this case, you can apply style formatting to the entire chart at once, using one of the chart styles. In addition, you can format individual diagram objects that must first be selected.

Some chart objects, such as a series, consist of several parts. To select only one part, for example a single point in a row, you must first select the entire object and then select the desired part.