How to show data from two or more working arches on the Excel diagram. How to create a zoomed chart in Excel Two pie charts on one chart

Golovna / Contacts

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.

As the meanings of different rows, one type of one is signifi- cantly changed, to a large extent represent them for the help of 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 of diagrams? 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 application, we were victorious in two ways to prompt the combination of 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 visualization, then one cannot do without an additional axis for creating mixed diagrams. 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.

Pobudov combined diagrams

In vipadkas, if it is necessary to display data of different scale on one diagram, combine them with diagrams. The typical rate is absolute (rubles) and vodnosn (hundreds) showings. For example, rozmir debіtorsku borgovanіst i yogo vіd realіzatsії.

Combined (otherwise mixed ) such a diagram is called, as it is folded from dekіlkoh rows of data and in such a way different types of diagrams are formed at once (for example, a histogram and a graph). To encourage combination diagrams, you need at least two rows of data. Apply the wrong diagrams placed at the book Combined

Іsnuyet deakі і obmezhennya pobudova kombinovanih diagram:

· Impossibly zmіshuvati be-yakі types of diagrams with volume types;

· Some combinations of types of diagrams look very unsatisfactory (for example, a combination of graphics and pellust diagrams);

· At zmіshanіy dіаgramі vikoristovuєtsya single area pobudov, then, it is impossible to create, for example, a combined diagram, which is composed of three circular diagrams;

· Not to be combined with line charts and line diagrams: all categories of line diagrams are straightened vertically, and all graphics - horizontally.

A diagram is presented on the little one, prompted for three by rows of figures. Moreover, the temperature value of the water is shown in the form of histograms, and the number of falls - in the graph.

A histogram is presented on the little one, prompted for two rows of data:

Change the type of diagrams for another row of data ( fall down) And vikoristovuєmo for this row I will okrem all the values.

1. We can see a number of data on the diagram fall down that viklichemo context menu row.

2. Vіdkriєmo vіkno Low data format (Format Data Series) and on the deposit Parameters row (Series Options) install jumper per supplementary axis(Secondary Axis).

3. Without knowing the sight in a row, we see the team Work with diagrams– Constructor – Type of – Change type of diagrams(chart Tools – design – type – change chart type) .

4. In the dialog box Change type, choose the type of diagrams Schedule (line) and click on the button OK.

Data that fell now is visualized with straight lines, a new one appeared on the right All value (ValueAxis).

It is important to understand that the team Work with diagrams– Constructor – Type of – Change type of diagrams (chart Tools – design – type – change chart type) Pratsyuє in a different way in the fallow land according to what is seen. If a row of diagrams is seen, then the command changes the type of that row. If another element is seen by diagrams, then the command changes the type of all diagrams.

overlay diagram (OverlayChearts)

Under the overhead diagrams, the placement of the diagrams as graphic objects is understood: one on top of the other. Diagram, ripped to the beast, ring out the area with diagrams and the area with diagrams. They have all the elements, cream markers and lines. Superimposing diagrams is a manual robot, which ensures the exact positioning of objects, adjusting the axes of the value of that category.

Sob diagrams put them “in a pile” in order, you need to see them as objects. To see the object, not the diagram, press the key ctrl and click on the diagram.

Look at a simple butt (arkush Overlay_1 books Combined). It seems that one of the borders on the need for combined diagrams is the impossibility of changing whether it is types of diagrams with general types. The method of overlaying diagrams allows you to draw a graph and a volume histogram on the archway. Let's make these short cuts:

1. Visible in the data table range A2:B7 it will be necessary to expand the histogram with groupings. We see the legend with diagrams.

2. Seemingly inconsistent ranges A2: A7і C2:C7 and we will need a graph with markers. We see the legend with diagrams.

3. On the other diagram, let's set the fill of the area with diagrams and the area with diagrams, we can see the axis. Vіdformatuєmo lines graphics and markers.

4. I will manually sum the graph and the total histogram. By changing the scope of the "Graph" object, it is possible to change the markers of the graph from the middle of the columns of the histograms.

5. After the formatting is completed, the diagram will be overlaid.

6. After seeing two diagrams as graphic objects, to carry out their grouping, then the diagram is superimposed to be copied and moved as one object. Respectfully, that trying to spread the diagram on the okremy arcus brought to the ruin of the overlay.

An overlay of volumetric circular diagrams and volumetric histograms is shown on the stepping baby. The golden frame copes with the damage, it's just one diagram. However, there are two okremі diagrams. The frame should not lie on it, but on the middle of the working sheet. Heading - write, scho freely move. Data for such an invoice are drawn on the arch Krugova _volume books Combined.

Excel histogram volumes do not allow adding additional rows to glybin. Overlapping diagrams allow for a minute of subsoiling. Enough to encourage three volumes of histograms with accumulations (according to skin roci okremo) and combine them in such a way as to “create” a third of all at the depth. The diagram allows you to visually compare the results for three parameters - regions, months and times. The visible table of data is in Arkush Three_volumes books Combined.

Here are some articles about charts in Excel:

How to make a diagram in Excel?
How to make a pie chart in Excel?
How to get a schedule from Excel?
Pobudov scatter charts in Excel

In Excel, you can see the results of the analysis as diagrams of a graph, giving them great clarity, and sometimes it is necessary to induce two graphs in order. How to induce two graphs in Excel on one field, the distance is far and clear.

For the fact that the type of diagrams in Excel is not skin type, it is possible to display the same result as we see it. For example, the results of surveys for a number of functions based on the same output data. As for these data, there will be a sizable histogram and a graph, then the data will be given nothing, if any, more than a few, between them will be given the same intervals.

We can see two results of the analysis and it will be a great histogram.

Now let's try to add one more histogram to the actual ones from the same number of results of surveys. To add a graph in Excel, it is necessary to actively open the graph, having seen it, and on the "Constructor" tab, which appeared, select "Select data". At the end, as it turned out, in the division of the "Elements of the Legend" it is pressing to add, and it is possible to add the middles "Im'ya row:" and "Meaning:" on the arc, in the same way the meaning of the rose of the function "j" will be.

Now we wonder how our diagram looks like, so we add one more to the actual histograms, which has a greater number of values. Dodamo to the graph of the value of the function "k".

As you can see, the rest of the added values are richer, and the stench of the flooring is small, which is practically not visible on the histogram.

If you change the type of diagrams from histograms to a typical graph, the result will be more accurate in a different way.

If you win the charts in Excel on a dotted chart, then taking them off the charts will be protected as a result of the surveys, and the data will be the same. bude protezhuvatisya reading zalezhnist mіzh values.

For the creation of a chiseled graph, we can see the number of cob values and a couple of result columns for two different functions. On the "Insert" tab, select a dotted pattern with smooth curves.

To add one more graph, you can see it right now and on the “Designer” tab, click “Select data”.

In the new window, at the column “Elements of the Legend”, it is pressed “Add”, and it is possible to indicate the middle for “Im'ya row:”, “Value X:” and “Value Y:”. Dodamo in this way the function "j" on the graph.

Now we add the function “k”, as we can see other output data and their quantity.

As you can see, the function “k” is practically unremarkable on the dot plot, but the value on both axes has been corrected.

An hour ago, we published the first part of our self-reader with the creation of diagrams in Excel for the first time, in which a report instruction was given, how to induce a graph in Excel. For the first power, we ask in the comments, Bulo: "How to show data on the diagrams, spread on different working arches?". I want to chitachevo chitachevi, which put tse miraculous food!

Really, do not start the data, as it is necessary to show it on the diagrams, placed on one working arch. Luckily, Microsoft Excel allows you to display data on one chart, splitting it into two or more arches. Dali mi zrobimo tse krok for krok.

We create a diagram with a number of arches in Excel
Created a diagram, created from a large number of working arches

How to create a diagram with a number of arches in Excel

Let's say that some Excel arkushas have data on the income of a few years, and it is necessary to make a diagram for these data in order to show a general trend.

1. Create a diagram for the data of the first sheet

Open the first Excel worksheet, see the data, as needed to display it on the diagram, open the tab Insert(Insert) Diagrams(Charts) Choose the desired type of charts. For our butt mi vibero Volume histogram with accumulated(Stack Column).

2. Add another row of data from another sheet

Click on the creation of the diagram, a group of tabs appeared on the menu page Work with diagrams(Chart Tools), open tab Constructor(Design) and press the button Select data(Select Data). Or click on the icon Filter by diagrams(Chart Filters) right-handed in the charts and at the very bottom of the menu, click on the message Select data(Select Data).

At the dialogue window Vibir dzherela danikh(Select Data Source) press the button Add(Add).

Now add another row of data from another worksheet. This moment is more important, respectfully read the instructions. After pressing the button Add(Add) pop up dialog box Change in a row(Edit Series), here you need to click the icon to select the range order from the field Value(Series values).

Dialogue window Change in a row(Edit Series) fire up. Click on the arkush shortcut to avenge the coming part of the tribute, as it is necessary to show it on the Excel diagram. When switching to another arkush dialogue window Change in a row(Edit Series) disappear on the screen.

On the other archway, you can see a row of data, so you need to add it to the Excel chart, and again click on the icon to select the range, so that the dialog window Change in a row(Edit Series) turned to cob expanded.

Now click on the icon to select the range of the order with the field I'm next(Series name) and choose the middle one to avenge the text, which is necessary to win as a number of data. Click again on the icon to select the range to rotate to the cob dialog box Change in a row(Edit Series).

As you can see on the little one, we tied them up in a row from the middle B1, In a kakiy mіstsya heading stoptsya. Instead of sending to the heading of the column, you can enter the name at the sight of a text row laid in paws, for example:

="Another row of data"

The names of the rows of data appear in the legend as diagrams, so it’s better to guess the understood and changed names. At this stage, the result can be something like this:

3. Add even more rows of data (for bajans)

If the diagram may show more data, lower from two working arches, then repeat the front step of the skin row of data, which is necessary to add to the diagram. When you finish, press OK at the dialogue window Vibir dzherela danikh(Select Data Source).

4. Adjust and paint the diagram (for the bajan)

When the Excel 2013 and 2016 diagrams are combined, the following elements are automatically added, as the legend called the diagrams. Before our diagrams, folded together from a large number of arches, naming the legend was not added automatically, but we quickly corrected it.

See the diagram, click on the icon Elements with diagrams Chart Elements

About those, how to adjust other parameters of the diagrams, such as how to change the signatures of these or the format of the axes, it is reported in the following article about the adjustment of the Excel diagrams.

Constructed by diagrams from the sub-bag table

Solution, shown more, visually less than that, as if given, as required to be shown on diagrams, us working arches vibudovanie in the same order, tobto. at the first row - oranges, at the other - apples and etc. Otherwise, the graphics can be changed on the basis of indistinct.

For this butt, the roztashuvannya of the data is the same on all three arches. If it is necessary to induce a schedule with more large tables, and there is no concern in the fact that the data structure of these tables is the same, then it is more reasonable to create a sub-bag table, and also create a diagram from a sub-bag table. To fill in the bag table with the required data, you can select the function VVR(VLOOKUP)

For example, as a working sheet, about how to go in your butt, to avenge data in a different order, then we can create a sub-bag table from them for the help of such a formula:

VLOOKUP(A3,"2014"!$A$2:$B$5,2,NOT)
=VLOOKUP(A3,"2014"!$A$2:$B$5,2,FALSE)

Created in Excel a diagram created from a number of working arches

It may be so that after the completion of the creation of diagrams from two or more working sheets it becomes clear that she is guilty of being prompted in a different way. Some examples of creating such diagrams in Excel are such a quick process, like creating diagrams from one arc, then it’s easy, it’s easier to remake the created diagram, lower to create a new one from a pure arc.

Zagal parameters for Excel charts, created from dekilkoh working arches, do not differ in any way from parameters for Excel charts. You can twist a group of tabs Work with diagrams(Charts Tools), or the context menu, or the icons at the top right corner are set to diagrams, to set the main elements, such as naming diagrams, name the axes, legend, style diagrams and more. Additional instructions for setting these parameters can be found in the article about setting up Excel charts.

If you need to change a number of data, indications on the diagram, then you can do it in one of three ways:

Dialogue window "Vibir dzherela danikh"
Filter Diagrams Icon
Data series formulas

We change a number of data for the help of the dialog box "Vybir dzherela danikh"

Open dialog box Vibir dzherela danikh(Select Data Source), for which on the deposit Constructor(Design) press the button Select data(Select data).

To change a row of data, click on the new one, then press the button Change(Edit) and edit the parameters I'm next(Series Name) or Value(Series Values), as we worked earlier in this article. To change the order of the rows of data on the diagram, see the row of data and move it up or down for additional arrows.

To grab a row of data, just tick off the list Elements of legend(Legend Entries) in the left part of the dialogue window. To see a row of data from the diagrams, see it and press the button visuality(Remove).

Please, or show a row of data for help icons Filter diagrams

Another way to trace rows of data that appear on Excel charts, icon Filter by diagrams(Chart Filters). If you press on the diagram, then the icon will immediately appear right-handed.

To accept data, click on the end Filter by diagrams(Chart Filters) and uncheck the checkbox next to a specific row of data or a category.
To change the low data, press the button Change row(Edit Series) right-handed in the name of the row. To show up already knowing the dialogue window Vibir dzherela danikh(Select Data Source), which can store the necessary settings. Schob button Change row(Edit Series) came to finish just pointing the mouse at their row. For whom a number of data, which the mouse was pointed at, are seen on the diagram in color, so that it would be easier to understand, which element will be changed.

We change a number of data for an additional formula

Yak Vee, singsongly, you know, skin a number of data in Excel tasks with a formula. For example, if you see one of the rows of data on the diagram, as if they did it miraculously, then the formula for a row of data looks like this:

SERIES("2013"!$B$1;"2013"!$A$2:$A$5;"2013"!$B$2:$B$5;1)
=SERIES("2013"!$B$1,"2013"!$A$2:$A$5,"2013"!$B$2:$B$5,1)

The skin formula for a number of data is composed of a number of basic elements:

ROW(;;; data_range; row_number)

Tobto, our formula can be deciphered as follows:

Im'ya row ('2013'!$B$1) taken from commercial B1 on the sheet.
Category names ('2013'!$A$2:$A$5) taken from the middle A2:A5 on the sheet.
Data ('2013'!$B$2:$B$5) taken from the middle B2:B5 on the sheet.
Row number (1) to indicate that the row is borrowed on the chart first place.

To change the song row of data, see it on the diagram and add the necessary changes to the row of formulas. Obviously, we need to be more protective, changing the formula for a number of tributes, the shards will easily receive a pardon, especially as the hour of redaguvannya vihіdnі danі mіstya on different arches, and not in front of the eye. Vtіm, yakshcho pratsyuvati s formulas you sruchnіshe, nizh zvichaynymi іinterfeysom, such sposіb іbіb іbіbіb іn'lіkі vpravlennja tsіlkom can іdіtі.

Everything for today! For respect!

Lesson of preparations for you by the team of the site office-guru.ru

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 rite.

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 її into a zmіshanu diagram. Watch out for the next steps.

Click on the ranks, yakі need to change.
wrap Robot with diagrams Constructor Type Change the type of diagrams.
In the dialog box Change the type of diagrams, select a pictogram, as it represents the type of diagrams for the selected row.
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. The dekilka clicks with the mouse button, and one of the rows (Fall in this butt) pretending to be on the 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 case, the image of the main indications of monthly sales in the line diagrams shows the improvement of the month, in which the meta bula is 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.

An hour ago, we published the first part of our self-reader with the creation of diagrams in Excel for the first time, in which a report instruction was given, how to induce a graph in Excel. First of all, we put it in the comments, Bulo: “ How to show on the data diagram, how to stitch on different working arches?“. I want to chitachevo chitachevi, which put tse miraculous food!

How to create a diagram with a number of arches in Excel

Let's say that some Excel arkushas have data on the income of a few years, and it is necessary to make a diagram for these data in order to show a general trend.

1. Create a diagram for the data of the first sheet

2. Add another row of data from another sheet

At the dialogue window Vibir dzherela danikh(Select Data Source) press the button Add(Add).

="Another row of data"

The names of the rows of data appear in the legend as diagrams, so it’s better to guess the understood and changed names. At this stage, the result can be something like this:

3. Add even more rows of data (for bajans)

4. Adjust and paint the diagram (for the bajan)

See the diagram, click on the icon Elements with diagrams Chart Elements

Constructed by diagrams from the sub-bag table

The decision, shown more, visually less than that, as if given, as it is necessary to show on the diagrams, we must work arches in order, tobto. at the first row - oranges, at the other - apples and etc. Otherwise, the graphics can be changed on the basis of indistinct.

For example, as a working sheet, about how to go in your butt, to avenge data in a different order, then we can create a sub-bag table from them for the help of such a formula:

VLOOKUP(A3,"2014"!$A$2:$B$5,2,NOT)
=VLOOKUP(A3,"2014"!$A$2:$B$5,2,FALSE)

І take the following result:

Created in Excel a diagram created from a number of working arches

It may be so that after the completion of the creation of diagrams from two or more working sheets it becomes clear that she is guilty of being prompted in a different way. Shards of creating such diagrams in Excel - it’s such a quick process, like creating diagrams from one arc, then it’s easier, it’s easier to remake the created diagram, lower to create a new one from a pure arc.

If you need to change a number of data, indications on the diagram, then you can do it in one of three ways:

We change a number of data for the help of the dialog box "Vybir dzherela danikh"

Open dialog box Vibir dzherela danikh(Select Data Source), for which on the deposit Constructor(Design) press the button Select data(Select data).

To change a row of data, click on the new one, then press the button Change(Edit) and edit the parameters I'm next(Series Name) or Value(Series Values), yak mi. To change the order of the rows of data on the diagram, see the row of data and move it up or down for additional arrows.

Please, or show a row of data for help icons Filter diagrams

Another way to trace rows of data that appear on Excel charts, icon Filter by diagrams(Chart Filters). If you press on the diagram, then the icon will immediately appear right-handed.

To accept data, click on the end Filter by diagrams(Chart Filters) and uncheck the checkbox next to a specific row of data or a category.
To change the low data, press the button Change row(Edit Series) right-handed in the name of the row. To show up already knowing the dialogue window Vibir dzherela danikh(Select Data Source), which can store the necessary settings. Schob button Change row(Edit Series) came to finish just pointing the mouse at their row. For whom a number of data, which the mouse was pointed at, are seen on the diagram in color, so that it would be easier to understand, which element will be changed.

We change a number of data for an additional formula

SERIES("2013"!$B$1;"2013"!$A$2:$A$5;"2013"!$B$2:$B$5;1)
=SERIES("2013"!$B$1,"2013"!$A$2:$A$5,"2013"!$B$2:$B$5,1)

The skin formula for a number of data is composed of a number of basic elements:

ROW([ im'ya_row];[im'ya_categories];data_range;row_number)

Tobto, our formula can be deciphered as follows:

Im'ya row ('2013'!$B$1) taken from commercial B1 on arkush 2013 .
Category names ('2013'!$A$2:$A$5) taken from the middle A2:A5 on arkush 2013 .
Data ('2013'!$B$2:$B$5) taken from the middle B2:B5 on arkush 2013 .
Row number (1) to indicate that the row is borrowed on the chart first place.

Everything for today! For respect!

How to prompt a combined diagram in Excel

Change for one row of data type diagrams

1. Create a diagram for the data of the first sheet

3. Add even more rows of data (for bajans)

4. Adjust and paint the diagram (for the bajan)

Constructed by diagrams from the sub-bag table

How to create a diagram with a number of arches in Excel

1. Create a diagram for the data of the first sheet

2. Add another row of data from another sheet

3. Add even more rows of data (for bajans)

4. Adjust and paint the diagram (for the bajan)

Constructed by diagrams from the sub-bag table

Created in Excel a diagram created from a number of working arches

We change a number of data for the help of the dialog box "Vybir dzherela danikh"

Please, or show a row of data for help icons Filter diagrams

We change a number of data for an additional formula

Choice of editors