A series of axis values is a plurality of values that must be plotted and displayed on the diagram. Categories(x) - specify the position of specific values in a series (usually this is time, month, year). Legend- set of tags, collection in separate frames and arranged. next to the diagram. Diagram has a block page, which allows you to edit each of its elements separately: 1 click - selection; 2 clicks - editing; clicking outside the diagram takes you out of the diagram's action field.
Types: Histrogram- to compare individual values with each other, you can set any number of parameters, not included in one hysterogram. current values and totals. Ruled- inverted histograms, use. according to the same rules. Circular- demonstrates the relationship between the whole and its parts and can have only 1 parameter. Kolonovaya - similar to circular and used in similar cases. , but has a principle. differences: you can set several parameters in it. Spot- use to indicate the degree of closeness of data items to the mean. Petal - Rarely used, usually in administrative tasks of managing complex projects.
Superficial. completed the same functions as planar, but due to their volume they have a more pleasant visual perception.
7.7. Excel capabilities for working with lists (databases): work. with a form, sorting, filtering, summarizing, creating pivot tables. Any database is allowed. quickly search it, make changes, sum up. For this purpose, created. specialist. DBMS software. However, with use. table process., there are also some. possibilities, implementation DBMS functions, incl. in Excel. To use it as a table, it can be viewed as lists. List- continuous rectangular range of cells containing additional. def. structures, special organized way. table using the following rules: 1) Any column must contain information of the same type; 2) the 1st top line should contain a header, where each column is written in 1 line; 3) The list cannot include empty lines and will be divided. lines. Lists can be: sort, filter, use forms to enter and view data, summarize, create pivot tables.
In case of confusion table rows physically rearranged. Filtration designed to simplify data search, records are not physically reordered, unnecessary data is hidden, they can be restored, the result can be copied to a separate area as a new table.
Filing methods: 1) Autofilter (Data-filter-autofilter); 2) Advanced filter (data-filter-advanced filter) - you can create difficult conditions, indicating them in the additionally created table range of conditions .
Form allows you to work with a separate record (Data-form). Obtaining results is intended for obtaining partial and general results.
Linked table-auxiliary table for automatic summing up and analysis of lists, allowed. quickly change the way data is displayed on the screen and consider it in the dynamics of development.
Pivot table- interactive table. on the worksheet, allowed. summarize large amounts of data by choosing an appropriate calculation method. To build ST requests to external bases data. ST is created using. Pivot table wizards (menu command Data > ST). Master mode consists of the following stages, during the cat. the user sets the parameters for constructing the ST: selecting a data source, creating a layout of the ST, determining placement. ST.
8.1.Classification computer graphics : 1) by image formation method: -raster. The image will be represented by a multitude of points (pixels) placed along fixed lines (raster). Each pixel has a corresponding color, brightness, and transparency value. The parameters of each point are described in a file, so the corresponding files are large. It is used when working with pictures obtained from photography and scanning. Example: Adobe Photoshop,Paint. For scanned images, the .tiff format is used, and for transmitting raster images over the network - gif, jpg; Disadvantage: when transforming a graph image, clarity may suffer. - vector(designed for creating images in the form of scoops of points, lines (vectors), circles, rectangles, etc.). any vector image can be represented as a set of objects located in a certain way relative to others. Such cards are used in editorial, design, and drawing work. Advantages: scaling and transfing do not affect the quality, V is smaller than analog raster ones, printing is faster. For example: Adobe Illustrator, AutoCAD, CorelDraw. (eps, dcs, pdf, cmx). - Fractal. Based on image programming, used for creating graphs and diagrams. Excellent features: the image is formed according to equations; not objects, but their equations are stored in memory. 2) according to the size of the resulting image:-two-dimensional; three-dimensional3) according to purpose: business, scientific, illustrative, engineering, computer painting, advertising.
Construction of charts and graphs A remarkable property of ET is the ability to graphically represent numerical information. Charts and graphs are used to visually display table data. Diagrams allow you to quickly comprehend and interpret large amounts of information contained in ET. Excel allows you to create charts on separate worksheets and embedded charts located on the same worksheet as the table. In both cases, the charts change when the spreadsheet data is updated.
Excel supports creating charts and graphs Various types diagrams, each of which has several types. The choice of type and appearance of charts depends on its purpose and should provide the best presentation of the data. Basic chart types: histogram, bar chart, graph, pie, scatter, area, donut, radar, surface, bubble, stock, cone. A histogram is a set of vertical bars whose height is determined by the data values. A histogram is used to display one or more data series and is used to compare data or change data over time.
Building Charts and Graphs A bar chart is a bar chart whose bars are arranged horizontally rather than vertically. The graph is a smoothed curve or broken line connecting points corresponding values data. A pie chart is used to represent a single series of data. It demonstrates the relationship between the whole and its parts. Each value is represented as a sector of a circle, the angle of which is proportional to the share of the represented value in the total sum of all values. To make it easier to work with small shares, they can be combined into one element in the main diagram, and then presented side by side as a separate pie chart.
Creating Charts and Graphs A scatter plot displays the relationship between numerical values in multiple series in XY coordinates. When preparing data, the argument values are placed in the first row, and the function values in subsequent rows. This chart displays odd intervals—or clusters—of data and is often used in scientific research. An area chart shows the amount of change in a specific factor over a specified period of time. It also displays the contribution of individual values to the total.
Radar Chart In a radar chart, each category has its own coordinate axis originating from the origin. Lines connect all values from a certain series. It allows you to compare common values from multiple data sets.
Surface chart In a surface chart, like a topographic map, areas with the same value interval are highlighted in the same color.
A bubble chart is a type of scatter chart in which the size of the marker indicates the value of a third variable.
Other types of charts A donut chart shows the contribution of each element to the total, but unlike a pie chart, it can contain several series of data. Conical (cylindrical, pyramidal) are a type of volumetric histograms that use data markers in the form of a cone, cylinder, or pyramid. The built-in non-standard chart types are combinations standard types. Excel allows you to create and use your own chart types. Excel allows you to use geographic Maps to display ET data. The cards are OLE objects. They must first be activated.
Formulas in EXCEL Formula is a combination of constant values, addresses, names, functions and operators that defines the rules for calculating the result in a cell. EXCEL uses the following operator symbols: +, -, *, /, %, ^, &, =, =, and the address symbols are colon, semicolon, and space. A colon specifies a range, a semicolon specifies a union of ranges, and a space defines their intersection. In descending order of priority, the operations are arranged in the following order: range - ":", intersection - "", union - "; ", negation - "-", percentage - "%", exponentiation - "^", multiplication and division - "*", "/", addition and subtraction - "+", "-", text concatenation - " &", comparison operations - =, =, .
Formulas in EXCEL When EXCEL cannot calculate a value using a formula, an error message is displayed: ##### - the cell width is not sufficient; #DIV/0! – division by 0; #N/A – undefined data; #NAME? – the name is not recognized; #EMPTY! – the presence of two non-overlapping areas; #Number! – problems with numbers; #LINK! – incorrect cell reference; #VALUE! – value of the wrong type.
IN Excel program term diagram used to refer to all types of graphical representation of numerical data. Construction graphic image produced at 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 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 construct a diagram, you usually use Chart Wizard, triggered by clicking a button Chart Wizard 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
Chart type. At the first stage of the work, the wizard selects the shape of the diagram. Available forms listed in the Type list on the tab Standard. For the selected chart type, several options for presenting data are indicated on the right (palette View), from which the most suitable one should be selected. On the tab Non-standard displays a set of fully formed chart types with ready-made formatting. After specifying the shape of the diagram, click on the button Further.
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 tab Range data. If the data does not form a single group, then information for depicting individual data series is set on the tab Row. The chart preview automatically updates as the data set being displayed changes.
Design of the diagram. The third stage of the wizard (after clicking the button Further) consists in choosing the design of the diagram. On the tabs of the wizard window you can set:
* chart title, axes labels (tab Headings);
* display and marking of coordinate axes (tab Axles);
* display a grid of lines parallel to the coordinate axes (tab Grid lines);
* description of the constructed graphs (tab Legend);
* displaying labels corresponding to individual data elements on the chart (tab Data Signatures);
* presentation of the data used in constructing the graph in the form of a table (tab Data table).
Depending on the chart type, some of the listed tabs may not be available.
Chart placement. At the last stage of the wizard (after clicking the button Further) indicates whether to 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 button Ready the diagram is built automatically and inserted into the specified worksheet.
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 by a tooltip Open Dialog box for formatting a chart element can be accessed through the menu Format(for the selected element) or via context menu(team Format) Various tabs in the dialog box that opens allow you to change the display options for the selected data item. 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. Launching chart wizard, you can change the current settings, which are considered as default in the wizard windows.
To delete a chart, you can delete the worksheet on which it is located ( Edit Delete sheet), or select a chart embedded in a data worksheet and press DELETE
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 button mouse – “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 make 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. We “multiply” the formula throughout 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. Add to the previous one Z=X(√x – 3). 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.
Data selection – “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.
In the same way, you can build donut and bar charts, histograms, bubble charts, stock charts, etc. Excel's capabilities are varied. Enough to clearly depict different types data.
Lesson objectives:
- Show the capabilities of MS Excel for a visual (graphical) presentation of numerical data.
- Learn to build diagrams various types.
- Develop computer skills.
- To cultivate neatness, precision, and respect for nature.
Visual and technical means training:
- handout with text for practical work;
- computers, multimedia projector;
- stickers (leaflets with an adhesive strip) to summarize the lesson.
During the classes
I. Organizational moment.
At the beginning of the lesson, 1 slide is shown on the screen (see Appendix 1).
Teacher: Topic of our lesson: "Business Graphics in MS Excel"(2 slide). Today we will look at the capabilities of MS Excel for a visual (graphical) presentation of numerical data and learn how to build various types of charts. These skills will be useful to you in your further studies, and for some in your professional activities.
Each of you will receive several grades during the lesson, which will be recorded on evaluation sheets (they are on your desks). Then will be exhibited average rating per lesson, taking into account the teacher's assessment in the journal.
To construct the diagrams, we will use some data about the environmental situation in Sverdlovsk region. These data are taken from the State Report “On the State of the Natural Environment and the Impact of Environmental Factors on the Health of the Population of the Sverdlovsk Region” (website of the Ministry of Natural Resources of the Sverdlovsk Region - http://www.mprso.ru/ohrana_ap.htm).
And I would like to start the lesson with a poetic introduction (3rd slide):
You, man, loving nature,
At least sometimes feel sorry for her.
On pleasure trips
Don't trample its fields.
In the station bustle of the century
Hurry to evaluate it.
She is your old, kind doctor,
She is the ally of the soul.
Don't burn it recklessly
And don’t exhaust it to the bottom,
And remember the simple truth:
There are many of us, but she is alone.
(V. Shefner.)
II. Updating students' knowledge.
1) Teacher: In order to remember the material from past lessons, we will conduct a Blitz survey (4 slide). Students answer the questions posed. Click the mouse to reveal the answers.
- What is the main purpose of spreadsheets? (Processing of numerical data.)
- What is a book? (Document developed in the ET environment.)
- What is a leaf? ( Single element books - table.)
- What is a cell? (A single minimal table element.)
- What is a range/block of cells? (Selected rectangular part of the table.)
- Basic types of calculations solved in spreadsheets? (Statistical data processing; obtaining payroll statements; sorting tables; constructing graphs and diagrams; tabulating functions.)
- Main types of data contained in ET cells? (Text, number, formula, logical expression, date of.)
2) Teacher: Name the main elements of the MS Excel window, marked with numbers on slide 5. The student who wants to answer is called.
3) Teacher: Complete the test work in pairs by answering the questions in the electronic test. One performs the task of option I (see Appendix 2), the other - task of option II (see Appendix 3).
The test work must first be copied onto students' computers. First, one answers, the other records his result on the evaluation sheet, then vice versa.
Theoretical material of the lesson.
Teacher: When solving various types of problems using MS Excel, the form in which the results are displayed is very important. The more visual this display is, the easier it is for a person to perceive the results. You and I know that computer graphics are divided into several types: illustrative, engineering, scientific, business. Business graphics consists of visualization, that is, the presentation in a visual form of large arrays of numerical information. Spreadsheet processors contain advanced business graphics tools. WITH using Excel You can build bar charts - histograms (single and multiple), pie charts, graphs, etc.
Charts in Excel are created using the Chart Wizard. First, a block of data is selected in the table for which you need to build a chart, and then the “Chart Wizard” is called using the command >> Insert >> Chart.
Let's look at examples of various diagrams (slides 6-10).
Practical work.
Teacher(slide 11): The purpose of the practical work is to master the basic techniques of working with the “Chart Wizard” in MS Excel.
Your task: using tables containing the results of observations of atmospheric air and surface water pollution, create: a single histogram, a multiple diagram and a pie chart or graph.
Students sit down at their computers, read the text of the practical work, and begin to complete it if there are no questions. The work is given in 2 versions.
I option.
To complete the task, open the file Contamination environment xls located on your desktop. Open Sheet 1.
Exercise 1. Based on the given data, construct a single histogram reflecting atmospheric air pollution with dust in the city of Pervouralsk during a given period of time.
Task 2. Construct a multiple diagram reflecting air pollution in Pervouralsk by various substances during the specified period.
Task 3. Based on the given data, construct a pie chart reflecting air pollution by various substances in 2007.
Task 4. In cell F2, enter a formula that calculates the average of the row. Using copying, fill in the remaining cells. Based on the data obtained, construct a graph illustrating the average levels of air pollution by various substances.
Option II.
To complete the task, open the Environmental Pollution xls file located on your Desktop. Open Sheet 2.
Exercise 1. Based on the given data, construct a single histogram reflecting the pollution of surface waters with suspended substances in various rivers in 2007.
Task 2. Construct a multiple diagram showing the pollution of surface waters by various substances in 2007.
Task 3. Construct a pie chart showing the pollution of the Chusovaya River with various elements in 2007.
Task 4. In cell B10, enter a formula that calculates the amount in the column. Using copying, fill in the remaining cells. Using the data obtained, construct a graph illustrating the overall pollution of surface waters.
To complete practical work in option I, students open sheet 1, in option II - sheet 2 (see Appendix 4).
III. Homework.
- §20, §21.
IV. Lesson summary.
The teacher uses a multimedia projector to demonstrate diagrams and graphs made by students.
Teacher: What type of diagram have we learned to build today? When is a pie chart or graph more appropriate? Evaluate the lesson by writing your rating on a sticky note (they are on the tables). On slide 12 on the screen, attach your sticker with your rating in the appropriate sector.
The teacher collects completed student assessment sheets with grades for oral work (blitz survey), for verification work in the form of an electronic test, and student self-assessment of practical work.
References:
- Computer Science and ICT. Basic course: Textbook for grade 9 / I.G.Semakin, L.A.Zalogova, S.V.Rusakov, L.V.Shestakova. – M.: BINOM. Knowledge Laboratory, 2006.
- Computer science. Problem book-workshop in 2 volumes. / Ed. I.G. Semakina, E.K. Henner. – M.: BINOM. Knowledge Laboratory, 2002.
- Semakin I.G. Teaching basic course computer science in secondary school: Methodological manual - M.: BINOM. Knowledge Laboratory, 2006.
Loading...
