How to create a two-sided diagram in Excel. There are different types of diagrams in EXCEL. Please, or show a row of data for help icons Filter diagrams

As a rule, the diagrams are drawn in one style, for example, either buti stovptsev, linear or circular diagram. If the diagram has more than one row of data, you can imagine it in a different style, creating a zmishana diagram in such a way.

On fig. 136.1 shows a diagram of how good it is to change for a change. Graph іlustruє average temperature and fall for months. Ale, problem. The two ranges of data are clearly different in size, so that the columns of the falling ice are marked on the diagram.

On fig. 136.2, a diagram is drawn after that, as I turned її to zmishana. Now the meaning of the fall of the image at the sight of the line and the other one is vertical, all right-handed. Most of you can wait, scho tsya diagram - significant improvement is equal to the output. You can її vykoristovuvaty for an hour to analyze the varsity of goods at the jewelry salon Vologda.

You can type the command to create mixed diagrams on the Excel page any day, or you don't know. If you have already created your own diagram (yakіy є has two rows of data), then you can easily transform it into a zmіshanu diagram. Watch out for the next steps.

1. Click on the ranks, yakі need to change.
2. wrap Robot with diagrams Constructor Type Change the type of diagrams.
3. In the dialog box Change the type of diagrams, select a pictogram, as it represents the type of diagrams for the selected row.
4. If you want to win a friend a vertical line for a converted row, select a row and press ctrl+1 for opening a dialogue window Low data format; go to distribution Row parameters and install jumper Prompt row at the camp On the secondary axis.

Small diagram 136.1 was a standard column diagram with two rows. Dekilka clatter with the mouse button, and one row (Falls in this butt) Having changed into a line. I also added everything to a friend and remembered the axis, so that it was clear what scale for what series of victories. To add the name of the axes, click the command Robot with diagrams Layout Axes names.

On fig. 136.3 shows another zoomed diagram. She will go in her own stovptseva and line diagram, and insulting rows of vicorist will form one vertical line. In this way, the image of the main indications of monthly sales in the line charts seemed to be easier to change months, in some cases it was reached.

On fig. 136.4 it is demonstrated that one can go far, vicarious zmіshanі diagrams. This diagram is divided into five different types of diagrams: pie, with areas, histogram (stowptsev), line and dot (XY). I don’t declare myself, in such a situation such a diagram would be pre-river, albeit to finish the demonstrative stock.

In some situations, you can't change the type of diagrams. For example, you cannot create a zmіshanu diagram, such as including bulbashkov and surface (3D) diagrams. If you choose a crazy type of chart for the series, Excel will tell you about it.

With the help of Excel, you can create a simple and volume graph, a graph with markers, a cylindrical, a finite and a part of histograms, a pukhirtsev, a pelyustkov, a dotted and a linear diagram. All the stench is easier to accept statistical data from this other sphere of human activity.

For example, the meanings of different rows are meaningfully distinguished by one type of one, to a large extent display them for additional different types of diagrams. Excel allows you to work in one room at a time. Let's take a look at the combinations (changes) with Excel charts.

How to prompt a combined diagram in Excel

Ways to encourage combined diagrams in Excel:

• transformation of obvious diagrams into a combination;
• adding an additional axis.

Let's create a table with data, which should be displayed on a combination diagram.

Seems to be within the range, including headers. On the "Insert" tab, in the "Diagrams" group, select the "Graph with markers" option.

In the region, two graphs appeared, which showed the quantity of sold units of goods and the amount of sales in rubles.

How can you combine different types diagram? Right-click on the mouse "Quantity, pcs.". At the window, select "Change type for a row".

Displays a menu of types of diagrams. At the "Histogram" section, select the "Histogram with groups" flat section.

Press OK. Behind the abbreviations, the height of the columns shows the vertical axis, sales are marked on the yak. But the histogram can reflect the quantity.

We can see the histogram by clicking the bear with it. Let's go to the "Layout" tab. Group "Stream fragment" - tool "Viewed fragment format".

Vіdkryєtsya vіkno Format for a number of data. On the "Row Parameters" tab, check the box next to "Induce the row along the auxiliary axis".

Press the "Close" button.

Correction over the old look of the combined diagram. Apparently, the area will prompt that we will go to the “Designer” tab. Let's change the style. We saw the legend (see - Delete). I will also name the signatures of the vertical axes.

For the main and additional axis, we select the option of rotting (okremo for skin) and enter the signature. Tisnemo Enter.

In this butt, we have won two ways to create combination diagrams: they changed the type of the row and added an additional weight.

Just as the datasets are significantly respected by the scale, the way of expression, then without an additional axis for creating mixed diagrams one cannot do. When there is more than one scale, one row is practically not visible. The culmination of the problem is the sustenance of another set of data on the supplementary axis.

Change for one row of data type diagrams

Let's make a change in the diagram with a way to change one of the rows of the type.

Table with output data:

Let's create a histogram based on two rows of data:

We can see the signs with histograms, which show the planned shows. On the "Designer" tab, in the "Type" group, press the button "Change the type of diagrams". Choose from the proponated options "Z regions".

We can show the planned indications as columns with histograms, and the actual ones as graphs with markers.

Respect! Not all diagrams can be seen. It is not possible to join the deacons about the tipi, the pukhirtsev to roam with other diagrams. The Excel program at times of impossible combinations looks like a pardon.

In this order, the diagram will be drawn on the basis of two and more rows of data. There are different types of diagrams for the new victor. Either one type (for example, a histogram), or else there is another type of value.

Today the article will be shorter. Let's talk about those, like on one distance diagram you see two different types at the same time graphic information, for example, stovpchastoy diagrams and graphics. A simple table with numbers is not very accurate, and graphs are often correct for analysis (taken for me).

The axis of my current butt from work. Obviously, given I guessed: it’s not for nothing that, when pracevshtuvanni, it’s necessary to sign a signature about unvoiced information.

Also, the protocol of rolling, where we have data on embossing, average rolling in the skin passage and the temperature of the metal at the exit from the roll after the skin passage. It would have been convenient to have two values ​​of the parameters on one graph: in this way, we could change these parameters at the skin passage and at once see the picture of the change in the skin parameters of the passages (it is clearly seen that the change in temperature of the metal opir deforms more).

Let's think about it, but why not just make two charts, instead of two different types of charts? Vidpovid: to those who are alone in the world and, vіdpovіdno, the very order of numbers can be different. For example, the temperature lies at the boundaries of 700-1000C, and the susilla of rolling in the boundaries is 10-70 MN. We put them on one graph - and the graph of the rolling roll is significantly lower than the temperature graph. It is necessary, for the skin parameter, the bula is vertical all over. Axis for which graphics of various types are required.

So let's go.

1. I'll be back to the diagram, as a mistimation of the information about the spread along the aisles. Way: "insert/histogram/histogram" (div. small).

2. Selectable data: on the horizontal axis - the number of passes (stand B), vertical - rolling susilla (stand C).

3. Now add another row of data - the temperature of the metal at the exit from the rolls (line D). For the time being, they didn’t take a clear picture, but the order of the numbers is different.

4. Now the most important: you need to change the type of "diagrams" of temperatures. Click the right button of the bear behind any temperature diagram, select the menu "Change the type of diagrams for the series" and select the type "dot diagram".

5. We took off the axis sho:

Bachimo, now we have two vertical and two horizontal axes, skin for your graphics.

6. Only a little bit of improvement with the maximum and minimum values ​​​​on the axes has been lost, otherwise the diagram and graph will live on their own for the time being. The minimum and maximum values ​​on the upper axis are the same as those on the lower one. Click with the right mouse button on the axis, select the menu item "axis format" and set the necessary values. You can also change the minimum value on the vertical axis (temperature axis): you can set "minimum 700".

7. You can also put values ​​on the graphs. I’m starting to click with the right mouse button for the required schedule and choose “add signatures of the data”.


The axis of what we can have for the bag:

From i all! The schedule is ready.

For ease of reading zvіtnostі, especially її analysis of data more visually. Wait, it's easier to evaluate the dynamics of any process behind the schedule, lower the number of tables.

In this article, it will be told about the blocking of diagrams in the Excel add-on, and some features of the situation for the shortest blocking will be looked at.

Insert that prompt

For example, a vikoristovuemo table of vouchers and vitrate for rіk, on the basis of which we will have a simple graph:

Regardless of the victorious type, as a histogram, the surface is thin, the principle of creation is not fundamentally changed. On the "Insert" tab in the Excel add-on, you need to select the "Charts" section and click on the required icons.

Go to the created empty area, so that additional tabs of lines appear. One of them is called "Designer" and replace the "Daniel" area, for which the item "Select data" is sorted. Click on the new call to choose the dzherel:

Return respect in the first field "Data range for diagrams:". With this help, you can quickly create a schedule, but the program does not know how to understand, as much as you want to bachit tse coristuvach. To this, we can look at a simple way to add rows of axles.

On this day, press the "Add" button in the "Legend Elements" field. The form “Change in a row” will appear, de needing to set a message on the im’ya row (not obov’yazkovim) that value. You can enter all indications manually.

After entering the necessary information and pressing the "OK" button, new row look at the diagram. So just add one more element of the legend from our table.

Now we will automatically add signatures to the horizontal axis. In the data selection window there is a category area, and in it there is a "Change" button. Click on them and in the form add a message to the range of their signatures:

Wonder what can come:

Elements with diagrams

Behind the lock, the diagram is composed of the following elements:

• Rows of tributes - to establish the head value, tk. visualize data;
• Legend - to revenge name the rows and the butt of their design;
• Osі - a scale with a singing price of intermediate subdivisions;
• The region of awakening is ashes for the tributes;
• Network lines.

Krіm zgadannyh more objects, can be added like this:

• Call it diagrams;
• Linear projection - low rows of data on the horizontal line;
• Trend line;
• Signatures of data - the numerical value of the data point in the row;
• І Інші elements, which are infrequently vicorated.

Style change

For change sane looking Diagrams can be scribbled on the basis of locking styles. For which you can see and select the “Designer” tab, for which the “Diagram Styles” area is sorted.

Often there are enough templates, but if you want more, you have to ask Vlasny style. To do this, you can click on the object with the diagrams that are to be changed, with the right mouse button, select the item “Format of the Name_Element” in the menu and change its parameters through the dialog box.

Great respect, that changing the style does not change the structure itself, that is. the elements of the diagrams are left unchanged.

The program allows you to quickly re-arrange the structure through express layouts, so that it can be sorted out at the same depositor.

Like і зі styles, the leather element can be added or removed okremo. In the version of Excel 2007, for which the "Layout" tab has been transferred, and in the version of Excel 2013, the functionality of transferring to the line of the "Designer" tab, to the "Diagram Layout" area.

tipi diagram

Schedule

Ideally suited for displaying changes in the object at the time of that trend.
An example of the dynamics of vitrates and the infamous vitorgus of the company for the river:

Histogram

It’s good to go for the alignment of a number of objects and change the current setting by the hour.
Butt of the indicator of the efficiency of two viddiliv quarterly:

Krugova

Appointment of the proportions of objects. Dynamics does not appear.
Butt part of sales of leather category goods in global sales:

Diagram of regions

Shows the dynamics of powers between objects in an hour. For the choice of this type, it is important to trim the order of the rows, because stench perekryvayut one one.

Admissible, є nebkhіdnіst vіdobkhіdnіst vіdobraziti zavantazhennya vіddіlu sale іїї pokrittya personnel. For whom showing the potential of practitioners, that interest was brought to the global scale.

So, for us, the first bachiti has the same potential, the whole row seems to be the first. From the diagrams below, you can see that from 11 to 16 years old, the business did not cope with the flow of customers.

Krapkova

In the coordinate system, the position of the skin point is given by the values ​​along the horizontal (X) and vertical (Y) axes. It's good if the value (Y) of the object is stored in the main parameter (X).

An example of the use of trigonometric functions:

on top

The Danish type diagrams are trivial danims. You can replace the dekilkom with rows of histograms or graphics, yakbi is a special feature - you don’t need to equalize the meaning of the rows, you can give the possibility of equalizing the meaning of the singing camp. The whole range of the meaning of divisions into sub-ranges, skins from any number of species.

Birzhova

Call it clear that this type of chart is ideal for displaying the dynamics of trading on the stock exchanges, or you can use the same charts for other purposes.

Sound similar to the diagrams to show the corridor of coliving (maximum and minimum values) and last value of the song period.

Pelustochna

The peculiarity of this diagram lies in the fact that all the values ​​are horizontally sorted according to the stake. In this rank, it allows you to more visually visualize the difference between the objects for the number of categories.

On the lower hover diagram, the ranking of three organizations for each direction is shown: Availability; Price policy; Quality of products; Customer orientation. It can be seen that company X leads the first and last straight, company Y for the quality of products, and company Z gives better prices.

It can also be said that company X is the leader, because the area of ​​\u200b\u200bthe figure on the diagram is the largest.

Change type diagram

Excel allows you to move in one diagram of a number of types. As an example of a summ_sny type of graphics and histograms.

For the cob, all rows will be from zastosuvannyam of one type, then we will change for the dermal row of okremo. By right-clicking the mouse on the required row, from the list, select the item “Change the type of diagrams for the row ...”, then “Histogram”.

Sometimes, due to the strong vіdminnosti, the meaning of the rows in the diagrams, it is impossible to use a single scale. Ale, you can add an alternative scale. Go to the menu "Format for a row of data ..." and in the "Parameters for a row" section, move the ensign to the item "By additional axis".

Now the diagram looks like this:

Excel trend

You can set your own trend for the skin series of diagrams. The stench is necessary to identify the main directivity (trends). Ale, for a skin-smooth look, it is necessary to win your model.

See a number of data, for whom you want to induce a trend, and press the mouse with the right mouse button. From the menu, select the item "Add trend line ...".

For appointment different models vikoristovuyut raznomanіtnі matematichnі methods. We will briefly review the situation, if it is better to stop the singing type of the trend:

• Exponential trend. The same value of the vertical axis (Y) grows with the skin snake of the horizontal axis (X).
• The linear trend is reversed, so that the Y value may be approximately the same as for the skin X value.
• Logarithmic. How to change the Y axis is upgraded with the skin change of the X axis.
• The polynomial trend is stagnant, as the change in Y changes, as in the case of the increase, so in the case of the change. Tobto. The data describe the cycle. Good to go for the analysis of the great set of data. The steps of the trend are selected in terms of fallow time according to the number of cycle peaks:
  • Stage 2 - one peak, tobto. half cycle;
  • Step 3 - one repeat cycle;
  • Stage 4 - second cycle;
  • and etc.
• Stop trend. How change Y grows with approximately the same firmness with skin change X.

Line filtration. Chi is not stagnant for the forecast. Vykoristovuetsya for smoothing change Y. Change change between points. If in adjusting the trend to the set point parameter 2, then the average is carried out between the critical values ​​of the X axis, like 3, through one, 4 through - two, etc.

Zvedena diagram

May all the paradoxes of the great diagrams and the stars of the tables, with which it is not obligatory to create the rest.

The principle of prompting zvedenih diagrams is little noticed by the creation of zvedenih tables. Therefore, the whole process cannot be described here, just read the article about table links on our website. Before that, you can prompt the diagram from the already prompted table in 3 clicks:

• See the star table;
• Go to the "Analysis" tab (in Excel 2007, the "Parameters" tab);
• In the "Service" group, click on the icon "Started diagram".

To create a star chart from scratch, it is necessary on the Insert tab to select the default icon. For the 2013 addendum, the wines are in the “Diagrams” group, for the 2007 addendum, in the table group, the list item “Table is shown”, which is shown.

• < Назад
• Next >

If the materials of the site helped you, then support, be kind, a project so that I can develop it in a moment.

Diagrams and graphs in MS Excel (before stock office package MS Office) to serve for graphic display of data, which is exactly from the point of view of the coristuvacha. For help diagrams manually watch out for dynamics change the value of the remaining values, carry out the comparison of different data, the presentation of the graphical deposit of some values ​​with others.

Reading that assessment of a great number of data, like visualization for additional graphs and diagrams, will be meaningfully simple. Excel is more efficient rich functional a tool for the purpose of visualization, which can be inspired by diagrams and graphics of various types and assignments. Simply irreplaceable in analytical studies.

On the little bachim is a standard graph of fallow in Excel, on the new one it is shown that the main elements are signed.

At the moment, versions of the appendix 2003, 2007, 2010, 2013, 2016 are selected. The main ones will be explained below.

How to make a schedule in excel

Excel support different types graphs for the most reasonable and complete display of information. Schedules will be behind the dots, yakі zadnuyutsya vіdrіzkami The more dots, the less spootvoren at the graphic, moreover, the function of dynamics smoothly changes.

To create a graph (like a diagram) in MS Excel, it is necessary for us enter numerical data on a sheet, on the basis of such wines, there will be awakenings. Call for graphics two hundred is enough, one of which can be used for the axis X (argument), the other - for Y (functions) - may be expressed by the formula or simply by recasting the fallow types of the data argument.

Vidіlіt data range. Let's see, having selected the required type of chart on the deposit Insert at the group Diagrams- Press Insert chart(To review the dynamics of changes in data). If you want to call the chart for the points - next take dot diagram(for example, you can have 2 rows of data, one of which should lie in front of the other).

Microsoft Excel 2003

Microsoft Excel 2013

The schedule can be placed both on one arkush with tribute, and on the other.

How to induce a diagram

Similarly to the graphs, the diagrams will be based on the data at the table columns, but for certain types (circular, kіltsev, pukhirtsev and іn) it is necessary, so that the data were roztashovuvalis in a song rank. To encourage the diagram, you need to go to the tab Diagrams. For example, we can look at it, like a robiti circular.

For such diagrams one stovpets - cemetki danikh, other- The number row itself.

See the range of comics with notes and denim. Potim Insert, squeeze Diagrams and choose the one that suits your needs type diagrams(To our vipadik Krugov).

Automatically create a diagram, if necessary, you can change at the authorities on vlasny rozsud. You can change style, layout, captions, axis, background and richly improved.

Diagrams of the circular type demonstrate the proportions of the parts of a good whole and are served at the sight of the totality of the sectors, which enter the warehouse of the stake with the fermentation of the relevant values. It’s even better, if it’s necessary to equalize the deeds of the given hundred of total value.

We will be sinusoid

It is permissible for you to induce the schedule of the function that you represent sinusoid. For whom is it necessary enterdata sinusіv kuіv.

To check the values ​​of the sinuses, it is necessary to have low data in the first quarter Sin enter formula= SIN(RADIAN(A3)), de A3- Vіdpovіdny argument. After what stretch the stitch for the right lower cut. We take away the range of values.

Dali future schedule, pushing Insert, Schedule, just like before.

Like a bachimo graph, what a viishov, not sufficiently similar to a sinusoid. For more beautiful sinusoidal fallow, it is necessary to enter Great number value kutiv (argumentiv) and what is more, then more beautifully.

How to add the name to the diagrams

If you want to change the name, make it more sensible, or see it with your own mind, then you need to make it offensive dії. In the version of Excel 2003, you need to click on the diagrams, after which you click on the panel Work with diagrams, with tabs Layout, Formatі Constructor. At the group Layout/Signatures wrap Name diagrams. Change required parameters.

The name can be said with some kind of table commissure, denoting the power on it. Calling the value of the name is automatically changed when the tables are changed.

How to fix the axis and add the name

Let's look at other functions you have - the ability to adjust the axis - scale, gaps between categories and values. You can add a field on the axis and indicate the values ​​​​of the values ​​\u200b\u200bbetween them, add and format the names of the axes, adjust fermentation or attach grid.

What do you need to fix names, signatures, axes and others in Office 2013 , then there is more simpler And more conveniently: it is enough to bet clicks on the changed visual components and the selection of the context menu linked to them.

Dodaemo or change the legend

The legends on the chart indicate the validity of the parameter before the next setting.

For excel charts option nalashtuvannya of the legend - change the roztashuvannya, imagining it or prihovat.

Go to tab Constructor/Select data for version 2003 or context menu Select data for version 2013.

Vіdkriєtsya vikno dzherel danih choice, who can change the range of victorious data, change the signatures of the axes, and the elements of the legend (row), parameters for the skin row of okremo.

As a bachimo for the function in excel, the obov'yazykovo presence of two factors is tabular and graphic part. The addendum of MS Excel of the office package can be an excellent element of the visual presentation of tabular data in the form of graphs and diagrams, which can be successfully typified for an impersonal task.