Constructing a dependence graph in Microsoft Excel. Building a dependence graph in Microsoft Excel Coordinates and simple graphs

One of the typical mathematical problems is plotting a dependence graph. It displays the dependence of the function on changes in the argument. Performing this procedure on paper is not always easy. But Excel tools, if properly mastered, allow you to complete this task accurately and relatively quickly. Let's find out how this can be done using various input data.

The dependence of a function on its argument is a typical algebraic dependence. Most often, the argument and value of a function are usually represented by symbols: “x” and “y”, respectively. Often you need to graphically display the dependence of an argument and a function that is written in a table or presented as part of a formula. Let's look at specific examples of constructing such a graph (diagram) under various given conditions.

Method 1: Create a dependency graph based on table data

First of all, let's look at how to create a dependence graph based on data previously entered into a table array. We use a table of the dependence of the distance traveled (y) on time (x).

Select the table and go to the tab "Insert". Click on the button "Schedule", which has localization in the group "Diagrams" on the tape. A selection of different chart types opens. For our purposes, we choose the simplest one. It is located first in the list. Let's click on it.

The program constructs a diagram. But, as we see, two lines are displayed on the construction area, while we only need one: displaying the dependence of the path on time. Therefore, by clicking the left mouse button, select the blue line ( "Time"), since it does not correspond to the task, and click on the button Delete.

The dedicated line will be deleted.

Actually, with this, the construction of the simplest dependence graph can be considered complete. If desired, you can also edit the names of the chart and its axes, remove the legend and make some other changes. This is covered in more detail in a separate lesson.

Method 2: Create a Dependency Plot with Multiple Lines

A more complex version of constructing a dependence graph is the case when two functions correspond to one argument. In this case, you will need to build two lines. For example, let’s take a table that shows the total revenue of an enterprise and its net profit by year.

Method 3: Plotting a graph using different units of measurement

In the previous method, we looked at constructing a chart with several lines on one plane, but all functions had the same units of measurement (thousand rubles). What should you do if you need to create dependency graphs based on one table, in which the units of measurement of the function are different? There is a way out of this situation in Excel.

We have a table that presents data on sales volume of a certain product in tons and revenue from its sales in thousands of rubles.

As in previous cases, select all the data in the table array along with the header.

Click on the button "Schedule". Again, select the first construction option from the list.

A set of graphic elements is formed on the construction area. Using the same method that was described in the previous options, we remove the extra line "Year".

As in the previous method, we should display the years on the horizontal coordinates panel. Click on the construction area and select the option in the list of actions "Select data...".

In the new window, click on the button "Change" in the block "Signatures" horizontal axis.

In the next window, performing the same actions that were described in detail in the previous method, enter the column coordinates "Year" to the region "Range of axis labels". Click on "OK".

When returning to the previous window, also click on the button "OK".

Now we need to solve a problem that has not yet been encountered in previous cases of construction, namely, the problem of inconsistency of units of quantities. After all, you must agree, division coordinates cannot be located on the same panel, which simultaneously denote both a monetary amount (thousands of rubles) and mass (tons). To solve this problem, we will need to construct an additional vertical coordinate axis.
In our case, to indicate revenue, we will leave the vertical axis that already exists, and for the line "Volume of sales" Let's create an auxiliary one. Right-click on this line and select an option from the list "Data series format...".

The Data Series Format window opens. We need to move to the section "Series parameters", if it was opened in another section. On the right side of the window there is a block "Build a Row". The switch must be set to position "Along minor axis". Click on the name "Close".

After this, the auxiliary vertical axis will be constructed, and the line "Volume of sales" reoriented to its coordinates. Thus, the work on the assigned task has been successfully completed.

Method 4: Create a Dependency Plot Based on an Algebraic Function

Now let's look at the option of constructing a dependence graph, which will be specified by an algebraic function.

We have the following function: y=3x^2+2x-15. Based on it, you should construct a graph of the dependence of the values y from x.

Before we start plotting the chart, we will need to create a table based on the specified function. The values of the argument (x) in our table will be indicated in the range from -15 to +30 in increments of 3. To speed up the data entry procedure, we will resort to using the autocomplete tool "Progression".
Indicate in the first cell of the column "X" meaning "-15" and select it. In the tab "Home" click on the button "Fill in", located in the block "Editing". Select the option from the list "Progression…".

Window activation in progress "Progression" In the block "Location" mark the name "By columns", since we need to fill exactly the column. In Group "Type" leave the value "Arithmetic", which is installed by default. In area "Step" value should be set "3". In area "Limit value" put a number "thirty". Click on "OK".

After completing this algorithm of actions, the entire column "X" will be filled with values in accordance with the specified scheme.

Now we need to set the values Y, which would correspond to certain values X. So, recall that we have the formula y=3x^2+2x-15. You need to convert it into an Excel formula in which the values X will be replaced by references to table cells containing the corresponding arguments.
Select the first cell in the column "Y". Considering that in our case the address of the first argument X represented by coordinates A2, then instead of the formula presented above we get the following expression:

3*(A2^2)+2*A2-15

Write this expression in the first cell of the column "Y". To get the calculation result, click on the button Enter.

The function result for the first argument of the formula is calculated. But we need to calculate its values for other table arguments. Enter a formula for each value Y a very long and tedious task. It is much faster and easier to copy it. This problem can be solved using a fill handle and thanks to such a property of links in Excel as their relativity. When copying a formula to other ranges Y values X in the formula will automatically change relative to their primary coordinates.
Place the cursor over the lower right edge of the element in which the formula was previously written. In this case, a transformation should occur with the cursor. It will become a black cross, which is called the fill marker. Hold down the left mouse button and drag this marker to the lower boundaries of the table in the column "Y".

The above action caused the column "Y" was completely filled with the results of calculating the formula y=3x^2+2x-15.

Now it's time to build the diagram itself. Select all tabular data. Back in the tab "Insert" click on the button "Schedule" groups "Diagrams". In this case, let's choose from the list of options "Graph with markers".

A diagram with markers appears on the plotting area. But, as in previous cases, we will need to make some changes in order for it to acquire the correct appearance.

First of all, let's delete the line "X", which is located horizontally at the mark 0 coordinates Select this object and click on the button Delete.

We don’t need a legend either, since we only have one line ( "Y"). Therefore, select the legend and press the key again Delete.

Now we need to replace the values in the horizontal coordinates bar with those that correspond to the column "X" in the table.
Click the right mouse button to select a line in the diagram. In the menu we move by value "Select data...".

In the activated source selection window, click on the button that is already familiar to us "Change" located in the block "Horizontal Axis Labels".

A window opens "Axis Signatures". In area "Range of axis labels" indicate the coordinates of the array with the column data "X". We place the cursor in the field cavity, and then, by pressing the left mouse button as necessary, select all the values of the corresponding table column, excluding only its name. As soon as the coordinates are displayed in the field, click on the name "OK".

Returning to the data source selection window, click on the button "OK" in it, as before in the previous window.

After this, the program will edit the previously constructed diagram according to the changes that were made in the settings. The dependence graph based on the algebraic function can be considered finally ready.

As you can see, using Excel, the procedure for constructing a dependence graph is significantly simplified compared to creating it on paper. The result of the construction can be used both for educational purposes and directly for practical purposes. The specific construction option depends on what the diagram is based on: tabular values or a function. In the second case, before constructing the diagram, you will also have to create a table with arguments and function values. In addition, the graph can be constructed based on one function or several.

Information is easier to perceive if it is presented clearly. One of the ways to present reports, plans, indicators and other types of business material is graphs and diagrams. These are indispensable tools in analytics.

There are several ways to build a graph in Excel using table data. Each of them has its own advantages and disadvantages for a specific situation. Let's look at everything in order.

The simplest change schedule

A graph is needed when it is necessary to show changes in data. Let's start with a simple diagram to show events over different periods of time.

Let's say we have data on the net profit of an enterprise for 5 years:

* Figures are conditional, for educational purposes.

Go to the “Insert” tab. There are several types of charts available:

Select "Graph". The pop-up window shows its appearance. When you hover your cursor over a particular type of chart, a hint appears: where it is best to use this chart, for what data.

Selected - copied the table with data - pasted it into the diagram area. This turns out to be the following option:

Straight horizontal (blue) is not needed. Just select it and delete it. Since we have one curve, we also remove the legend (to the right of the graph). To clarify the information, sign the markers. On the “Data Signatures” tab we determine the location of the numbers. In the example - on the right.

Let's improve the image - label the axes. “Layout” – “Name of axes” – “Name of the main horizontal (vertical) axis”:

The title can be removed or moved to the chart area, above it. Change the style, fill, etc. All manipulations are on the “Chart Name” tab.

Instead of the serial number of the reporting year, we need exactly the year. Select the values of the horizontal axis. Right-click – “Select data” - “Change horizontal axis labels”. In the tab that opens, select a range. In a table with data - the first column. As below picture:

We can leave the schedule as is. Or we can make a fill, change the font, move the diagram to another sheet (“Designer” - “Move diagram”).

Graph with two or more curves

Let's say we need to show not only net profit, but also the value of assets. There is more data:

But the construction principle remained the same. Only now does it make sense to leave the legend behind. Since we have 2 curves.

Adding a Second Axis

How to add a second (additional) axis? When the units of measurement are the same, we use the instructions suggested above. If you need to show data of different types, you will need an auxiliary axis.

First, we build a graph as if we have the same units of measurement.

Select the axis for which we want to add an auxiliary one. Right mouse button – “Data series format” – “Series parameters” - “Along the auxiliary axis”.

Click “Close” - a second axis appears on the chart, which “adjusts” to the curve data.

This is one way. There is another one - changing the chart type.

Right-click on the line for which an additional axis is needed. Select “Change chart type for series.”

We decide on the type for the second row of data. The example is a bar chart.

Just a few clicks - an additional axis for another type of measurement is ready.

Building a graph of functions in Excel

All work consists of two stages:

Creating a table with data.
Building a graph.

Example: y=x(√x – 2). Step – 0.3.

Let's create a table. The first column is the X values. We use formulas. The value of the first cell is 1. The second: = (name of the first cell) + 0.3. Select the lower right corner of the cell with the formula - drag it down as much as necessary.

In the Y column we write the formula for calculating the function. In our example: =A2*(ROOT(A2)-2). Press "Enter". Excel calculated the value. “Multiply” the formula over the entire column (by pulling the lower right corner of the cell). The data table is ready.

Let's move to a new sheet (you can stay on this one - put the cursor in a free cell). “Insert” - “Chart” - “Scatter”. Choose the type you like. Right-click on the chart area and select “Select Data.”

Select the X values (first column). And click “Add”. The Edit Series window opens. Set the name of the series – function. X values are the first column of the data table. Values Y – second.

Click OK and admire the result.

The Y axis is fine. There are no values on the X axis. Only point numbers are indicated. This needs to be fixed. It is necessary to label the graph axes in Excel. Right mouse button – “Select data” - “Change horizontal axis labels”. And select the range with the required values (in the table with the data). The schedule becomes what it should be.

Overlaying and combining graphs

Creating two graphs in Excel is not difficult. Let's combine two function graphs in Excel on one field. Let's add Z=X(√x – 3) to the previous one. Data table:

Select the data and paste it into the chart field. If something is wrong (the names of the rows are wrong, the numbers on the axis are reflected incorrectly), edit them through the “Select data” tab.

And here are our 2 function graphs in one field.

Dependency graphs

The data in one column (row) depends on the data in another column (row).

You can build a graph of the dependence of one column on another in Excel like this:

Conditions: A = f (E); B = f(E); C = f(E); D = f(E).

Select the chart type. Spot. With smooth curves and markers.

Select data – “Add”. The series name is A. X values are A values. Y values are E values. “Add” again. The name of the row is B. The X values are the data in column B. The Y values are the data in column E. And according to this principle, the entire table.

Ready-made examples of graphs and charts in Excel download:

An example of how to make a dashboard template for generating a report on the CSAT customer satisfaction index. Download the ready-made C-SAT dashboard for analyzing indices and indicators.

An example of creating a template for a dynamic pie chart displaying KPI indicators on a dashboard. Download a pie chart of KPI indicators in percentages for the dashboard.

How to make a template, dashboard, chart or graph to create a beautiful report convenient for visual analysis in Excel? Select graph chart examples to interactively visualize data from smart Excel spreadsheets and use them to make better decisions quickly. Download ready-made dynamic chart templates for free to use in dashboards, reports, or presentations.

In the same way, you can build donut and bar charts, histograms, bubble charts, stock charts, etc. Excel's capabilities are varied. This is enough to visualize the different types of data.

CHAPTER VIII

COORDINATES AND SIMPLE GRAPHICS

§ 42. Direct proportional dependence y = ah and her schedule.

1272. One kilogram of goods costs 2 rubles.

1) Create a formula expressing the relationship between at - cost of goods, price of 1 kg of goods and quantity X purchased goods.

2) Draw a graph of the resulting formula.

3) Determine the cost according to the schedule: 2 kg 500 g; 4 kg 250 g; 3 kg 750 g product.

4) Determine from the schedule the quantity of goods that can be bought for 7 rubles; 11 rub.; 9 rub.

5) How does the cost of a product change depending on the change in the quantity of the product?

1273. A body moves uniformly at a speed of 4 km per hour.

1) Write a formula expressing the path s this body for t hours.

2) Make a table of values s at t , equal to 0; 1; 2; 3; 4.

3) Using the table data, construct a graph of changes in the path of a given body depending on changes in the time of movement.

4) Find from the graph the path traveled by the body in 1 hour 30 minutes; in 3.5 hours.

5) Find from the graph how long it will take the body to travel 10 km; 6 km.

6) Prove that the ratio of the ordinate of any point in the resulting graph to its abscissa is equal to 4.

7) Prove that if a point does not lie on a given graph, then the ratio of its ordinate to the corresponding abscissa is not equal to 4.

8) What is the name of the relationship between s And t ?

9) What is a direct proportional graph?

1274. 1) Knowing that the quantity at X , fill out the following table:

2) Write a formula expressing the dependence at from X .

1275. Water is poured into the tank through a tap. The number of liters of water poured in every minute was recorded. The result is the following entry:

1) Find: the ratio of the number of liters of water pouring into the tank in 6 minutes to the number of liters of water pouring into the tank in 4 minutes; the ratio of the number of minutes of operation of the crane in the first case to the number of minutes of operation of the crane in the second case and compare these ratios.

2) Check whether the statement is true that the ratio of any two values v equal to the ratio of the corresponding values t .

3) Define the relation of any value v to the corresponding value t .

4) Express the dependence as a formula v from t .

5) Draw a change graph in your notebook v - the number of liters of water in the tank depending on t - operating time of the tap (Fig. 36).

6) Calculate the number of liters of water in the tank after 5 1/2 minutes; in 1 1/2 min.

7) Give examples of directly proportional quantities.

1276. The average height of a child under 10 years of age varies as follows:

1) Show that the ratio of any two values l not equal to the ratio of the corresponding values t .

2) Show that a relation of any value l to the corresponding value t not equal to the same number.

3) Why can’t we say that a child’s height is directly proportional to his age?

4) Draw a graph of changes in the child’s growth depending on changes in age (Fig. 37).

5) What line does the drawn graph represent? How does this graph differ from a direct proportionality graph?

1277. There is water in the vessel at a temperature of 0 o. When the water was heated, its temperature began to change depending on the duration of heating as follows:

1) Find the ratio of any two values X and compare this ratio with the ratio of the corresponding values at .

2) Compile according to the values in the table X And at several proportions.

3) Write a formula expressing at depending on the X.

4) Calculate the temperature of the water in the vessel at X = 15 (assuming uniform heating).

5) Using the table data, draw a graph of changes in the temperature of water in the vessel.

1278. A self-propelled harvester at an average operating speed harvests 2 hectares of wheat per hour.

1) Find the area S , harvested by a combine for t hours.

2) Calculate S with the following values t :

3) Draw a graph of change S depending on change t .

1279. It is known that the value at changes directly proportional to the value X , and the proportionality coefficient is 4.

1) Write a formula expressing at through X .

2) Fill in the blanks in the following table:

3) Build a change graph at depending on change X .

1280. Knowing that the magnitude at changes directly proportional to the value X , fill in the blanks in the following tables:

1281. It is known that the value at is directly proportional to the value X and the proportionality factor is 2.

1) Write a formula expressing the dependence at from X .

2) Fill out the following table:

3) Construct a graph of this dependence.

1282. Construct (on one drawing) graphs: y = 3X ; at = -3X .

Indicate the difference in the location of the resulting graphs relative to the coordinate axes (Fig. 38).

1283. 1) Using the same coordinate axes and on the same scale, construct graphs of the following equations:

y = 1 / 3 x ; y = - 1 / 3 x

at = 2X ; y = - 2x .

2) Find out the change in the position of the straight line relative to the coordinate axes when the coefficient changes X .

Construct graphs expressing direct proportionality using the following equations:

1284. 1) at = 4X ; 2) at = 1 / 2 X

3) at = X ;

4) at = 2 1 / 2 X

5) at = 1 / 4 X ;

6) at = 1 / 3 X ;

7) at = 0,6X ;

8) at = 1,5X

Show graphs 1, 3, 5 on drawing 39.

1285. 1) at = - X ;

2) at = - 1 / 2 X ;

3) at = -2X ;

4) at = -3X .

Show in drawing 39 graphics 1, 2, 4,

1286. Circumference WITH calculated by the formula WITH = π d , Where d is the diameter of the circle, and π - a constant value approximately equal to 3.142 (with an accuracy of 0.001)

1) How will the circumference of a circle change if its diameter is increased by 10; 100; 1000 times? reduce by 10; 100; 1000 times?

2) Determine the circumference of a circle whose diameter is expressed in the following numbers:

15; 24; 0,8; 0,12; 2,6; 230; 530; 780;

1 / 2 ; 1 / 4 ; 5 / 8 ; 2 1 / 4 ; 8 3 / 4 ;

1287. 1) While making a hiking trip, a detachment of pioneers left the city for the village at a speed of 3 km per hour. An hour later, a detachment of Komsomol members left this city along the same road at a speed of 4.5 km per hour. Construct a movement schedule for both units. Find from the graph at what distance from the city the second detachment will catch up with the first.

2) From two cities A and B, the distance between which is 160 km, two trains departed simultaneously towards each other. The train leaving from A was traveling at 48 km per hour, and the train leaving from B was traveling at 32 km per hour. Draw a schedule for both trains. Determine from the schedule how long after leaving the trains they will meet and at what distance from A.

1288. When taking into account the work of tractor drivers, all tractor work is converted into the number of hectares of so-called “conditional plowing”.

1) Draw up a formula for converting the work of raising ploughland into conditional plowing, knowing that for performing this work on an area of 1 hectare, 1.4 hectares of conditional plowing are awarded.

Note: Designate by X number of hectares of plowed land, through at - number of hectares of conditional plowing.

2) Construct a schedule for converting the tractor driver’s work of raising plowed land into conditional plowing.

3) Determine from the graph how many tectars of conditional plowing are credited for raising 8 hectares of plowland? 11 hectares? 15 hectares?

4) Determine from the schedule how many hectares of plowed land need to be plowed in order to fulfill the daily work requirement equal to 4.5 hectares of conditional plowing.

1289. When taking into account the work of a tractor driver. The following data was received for the month:

1) Construct graphs for converting tractor work into conditional plowing using the same coordinate axes and on the same scale (1 cm corresponds to 10 hectares).

2) Determine from the graphs the volume of each type of work in hectares of conditional plowing with an accuracy of 1 hectare, filling in the corresponding spaces in the table.

3) Knowing that, according to the plan, the tractor driver was supposed to develop 150 hectares of conditional plowing in a month, determine, based on the results obtained, the implementation of the plan as a percentage (with an accuracy of 1%)

1290. The problem of finding R % of this number A is solved by the formula b = ap / 100 where A - given number, R - number of percent, b - the required number.